List of usage examples for java.sql ResultSet getDate
java.sql.Date getDate(String columnLabel) throws SQLException;
ResultSet object as a java.sql.Date object in the Java programming language. From source file:mom.trd.opentheso.bdd.helper.TermHelper.java
/** * Cette fonction permet de rcuprer l'historique d'un terme une date prcise * * @param ds//from ww w .java2 s.c om * @param idTerm * @param idThesaurus * @param idLang * @param date * @return Objet class Concept */ public ArrayList<Term> getTermsHistoriqueFromDate(HikariDataSource ds, String idTerm, String idThesaurus, String idLang, Date date) { Connection conn; Statement stmt; ResultSet resultSet; ArrayList<Term> nodeTermList = null; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT lexical_value, modified, source, status, username FROM term_historique, users" + " WHERE id_term = '" + idTerm + "'" + " and id_thesaurus = '" + idThesaurus + "'" + " and lang ='" + idLang + "'" + " and term_historique.id_user=users.id_user" + " and modified <= '" + date.toString() + "' order by modified DESC, lexical_value ASC"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { nodeTermList = new ArrayList<>(); resultSet.next(); Term t = new Term(); t.setId_term(idTerm); t.setId_thesaurus(idThesaurus); t.setLexical_value(resultSet.getString("lexical_value")); t.setModified(resultSet.getDate("modified")); t.setSource(resultSet.getString("source")); t.setStatus(resultSet.getString("status")); t.setIdUser(resultSet.getString("username")); t.setLang(idLang); nodeTermList.add(t); } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting date historique of Term : " + idTerm, sqle); } return nodeTermList; }
From source file:com.wso2telco.dep.reportingservice.dao.BillingDAO.java
/** * Prints the api subscriber table./*from w w w .ja v a2 s.c om*/ * * @throws Exception the exception */ @Deprecated public void printAPISubscriberTable() throws Exception { StringBuilder sql = new StringBuilder(); sql.append("select * from ").append(ReportingTable.AM_SUBSCRIBER.getTObject()); Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; try { conn = DbUtils.getDbConnection(DataSourceNames.WSO2AM_DB); ps = conn.prepareStatement(sql.toString()); rs = ps.executeQuery(); while (rs.next()) { System.out.println("Results for SUBSCRIBER_ID :" + rs.getInt("SUBSCRIBER_ID") + " , USER_ID : " + rs.getString("USER_ID") + " , DATE_SUBSCRIBED : " + rs.getDate("DATE_SUBSCRIBED")); } } catch (Exception e) { handleException("printAPISubscriberTable", e); } finally { DbUtils.closeAllConnections(ps, conn, rs); } }
From source file:ca.sqlpower.matchmaker.address.AddressPool.java
public void load(Logger engineLogger) throws SQLException, SQLObjectException { setCancelled(false);//from w w w .ja v a 2s. c o m setStarted(true); setFinished(false); setProgress(0); SQLTable resultTable = project.getResultTable(); Connection con = null; Statement stmt = null; ResultSet rs = null; setJobSize(getNumRowsToProcess()); try { con = project.createResultTableConnection(); stmt = con.createStatement(); StringBuilder sql = new StringBuilder("SELECT * FROM "); appendFullyQualifiedTableName(sql, resultTable); rs = stmt.executeQuery(sql.toString()); while (rs.next()) { List<Object> keyValues = new ArrayList<Object>(); int numKeys = project.getSourceTableIndex().getChildCount(); // We need to convert the column types to the base set of // String, Boolean, BigDecimal, and Date that we use in the // Transformations. Otherwise, when we cannot properly compare // the key values of these loaded. Addresses with the ones // coming through the transformations. for (int i = 0; i < numKeys; i++) { int type = project.getSourceTableIndex().getChild(i).getColumn().getType(); Class c = TypeMap.typeClass(type); if (c == BigDecimal.class) { keyValues.add(rs.getBigDecimal(SOURCE_ADDRESS_KEY_COLUMN_BASE + i)); } else if (c == Date.class) { /* * KLUDGE. DateTime types are converted to Date's, thus losing * the Time portion of the value. When paging through results * and a DateTime column is used as part of the key, then inconsistent * paging will occur as the comparison logic will be comparing just * Date values. To avoid breaking any other parts of the application * as it is only the paging that is affected by this change, * explicitly check for the Timestamp type, and retrieve the right * type from the ResultSet here, instead of altering TypeMap.typeClass(). */ if (type == Types.TIMESTAMP) { keyValues.add(rs.getTimestamp(SOURCE_ADDRESS_KEY_COLUMN_BASE + i)); } else { keyValues.add(rs.getDate(SOURCE_ADDRESS_KEY_COLUMN_BASE + i)); } } else if (c == Boolean.class) { keyValues.add(rs.getBoolean(SOURCE_ADDRESS_KEY_COLUMN_BASE + i)); } else { keyValues.add(rs.getString(SOURCE_ADDRESS_KEY_COLUMN_BASE + i)); } } String addressLine1 = rs.getString(INPUT_ADDRESS_LINE1); String addressLine2 = rs.getString(INPUT_ADDRESS_LINE2); String municipality = rs.getString(INPUT_MUNICIPALITY); String province = rs.getString(INPUT_PROVINCE); String country = rs.getString(INPUT_COUNTRY); String postalCode = rs.getString(INPUT_POSTAL_CODE); Address address = new Address(); address.setCountry(rs.getString(OUTPUT_COUNTRY)); String deliveryInstallName; try { deliveryInstallName = rs.getString(OUTPUT_DELIVERY_INSTALLATION_NAME); } catch (SQLException e) { deliveryInstallName = rs.getString(OLD_OUTPUT_DELIVERY_INSTALLATION_NAME); } address.setDeliveryInstallationName(deliveryInstallName); String deliveryInstallType; try { deliveryInstallType = rs.getString(OUTPUT_DELIVERY_INSTALLATION_TYPE); } catch (SQLException e) { deliveryInstallType = rs.getString(OLD_OUTPUT_DELIVERY_INSTALLATION_TYPE); } address.setDeliveryInstallationType(deliveryInstallType); address.setDirectionPrefix(rs.getBoolean(OUTPUT_DIRECTION_PREFIX)); address.setFailedParsingString(rs.getString(OUTPUT_FAILED_PARSING_STRING)); address.setGeneralDeliveryName(rs.getString(OUTPUT_GENERAL_DELIVERY_NAME)); address.setLockBoxNumber(rs.getString(OUTPUT_LOCK_BOX_NUMBER)); address.setLockBoxType(rs.getString(OUTPUT_LOCK_BOX_TYPE)); address.setMunicipality(rs.getString(OUTPUT_MUNICIPALITY)); address.setPostalCode(rs.getString(OUTPUT_POSTAL_CODE)); address.setProvince(rs.getString(OUTPUT_PROVINCE)); address.setRuralRouteNumber(rs.getString(OUTPUT_RURAL_ROUTE_NUMBER)); address.setRuralRouteType(rs.getString(OUTPUT_RURAL_ROUTE_TYPE)); address.setStreet(rs.getString(OUTPUT_STREET_NAME)); address.setStreetDirection(rs.getString(OUTPUT_STREET_DIRECTION)); address.setStreetNumberSuffix(rs.getString(OUTPUT_STREET_NUMBER_SUFFIX)); String streetNumSuffix; try { streetNumSuffix = rs.getString(OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE); } catch (SQLException e) { streetNumSuffix = rs.getString(OLD_OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE); } address.setStreetNumberSuffix(streetNumSuffix); address.setStreetNumber(rs.getInt(OUTPUT_STREET_NUMBER)); address.setStreetType(rs.getString(OUTPUT_STREET_TYPE)); address.setStreetTypePrefix(rs.getBoolean(OUTPUT_STREET_TYPE_PREFIX)); address.setSuite(rs.getString(OUTPUT_SUITE)); address.setSuitePrefix(rs.getBoolean(OUTPUT_SUITE_PREFIX)); address.setSuiteType(rs.getString(OUTPUT_SUITE_TYPE)); String typeString = rs.getString(OUTPUT_TYPE); if (typeString != null) { address.setType(RecordType.valueOf(rs.getString(OUTPUT_TYPE))); } address.setUnparsedAddressLine1(rs.getString(OUTPUT_UNPARSED_ADDRESS)); address.setUrbanBeforeRural(rs.getBoolean(OUTPUT_URBAN_BEFORE_RURAL)); Boolean valid = rs.getBoolean(OUTPUT_VALID); AddressResult result = new AddressResult(keyValues, addressLine1, addressLine2, municipality, province, postalCode, country, address, valid); result.markClean(); addresses.put(keyValues, result); incrementProgress(); } engineLogger.debug("Loaded " + addresses.size() + " addresses from the result table"); } finally { setFinished(true); if (rs != null) rs.close(); if (stmt != null) stmt.close(); if (con != null) con.close(); } }
From source file:com.erbjuder.logger.server.rest.util.ResultSetConverter.java
private JSONArray toJSONArray(ResultSet rs, JSONArray json) throws Exception { String temp = null;// ww w .jav a 2s .c o m try { // we will need the column names, this will save the table meta-data like column nmae. java.sql.ResultSetMetaData rsmd = rs.getMetaData(); //loop through the ResultSet while (rs.next()) { //figure out how many columns there are int numColumns = rsmd.getColumnCount(); //each row in the ResultSet will be converted to a JSON Object JSONObject obj = new JSONObject(); // loop through all the columns and place them into the JSON Object for (int i = 1; i < numColumns + 1; i++) { String column_name = rsmd.getColumnName(i); if (rsmd.getColumnType(i) == java.sql.Types.ARRAY) { obj.put(column_name, rs.getArray(column_name).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BIGINT) { obj.put(column_name, rs.getBigDecimal(column_name).toBigInteger().toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BOOLEAN) { obj.put(column_name, ((Boolean) rs.getBoolean(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.BLOB) { obj.put(column_name, rs.getBlob(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.DOUBLE) { obj.put(column_name, ((Double) rs.getDouble(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.FLOAT) { obj.put(column_name, ((Float) rs.getFloat(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.INTEGER) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.NVARCHAR) { obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.VARCHAR) { // temp = rs.getString(column_name); //saving column data to temp variable // temp = ESAPI.encoder().canonicalize(temp); //decoding data to base state // temp = ESAPI.encoder().encodeForHTML(temp); //encoding to be browser safe // obj.put(column_name, temp); //putting data into JSON object // obj.put(column_name, rs.getNString(column_name)); } else if (rsmd.getColumnType(i) == java.sql.Types.TINYINT) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.SMALLINT) { obj.put(column_name, ((Integer) rs.getInt(column_name)).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.DATE) { obj.put(column_name, rs.getDate(column_name).toString()); } else if (rsmd.getColumnType(i) == java.sql.Types.TIME) { obj.put(column_name, TimeStampUtils.dateTimeToString(rs.getTime(column_name))); } else if (rsmd.getColumnType(i) == java.sql.Types.TIMESTAMP) { obj.put(column_name, TimeStampUtils.timeStampToString(rs.getTimestamp(column_name))); } else if (rsmd.getColumnType(i) == java.sql.Types.NUMERIC) { obj.put(column_name, rs.getBigDecimal(column_name).toString()); } else { obj.put(column_name, rs.getObject(column_name)); } } //end foreach json.add(obj); } //end while } catch (Exception e) { e.printStackTrace(); } return json; //return JSON array }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
@Test public void testDataTypes() throws Exception { Statement stmt = con.createStatement(); ResultSet res = stmt.executeQuery("select * from " + dataTypeTableName + " order by c1"); ResultSetMetaData meta = res.getMetaData(); // row 1//from w w w. j a v a 2 s .c o m assertTrue(res.next()); // skip the last (partitioning) column since it is always non-null for (int i = 1; i < meta.getColumnCount(); i++) { assertNull("Column " + i + " should be null", res.getObject(i)); } // getXXX returns 0 for numeric types, false for boolean and null for other assertEquals(0, res.getInt(1)); assertEquals(false, res.getBoolean(2)); assertEquals(0d, res.getDouble(3), floatCompareDelta); assertEquals(null, res.getString(4)); assertEquals(null, res.getString(5)); assertEquals(null, res.getString(6)); assertEquals(null, res.getString(7)); assertEquals(null, res.getString(8)); assertEquals(0, res.getByte(9)); assertEquals(0, res.getShort(10)); assertEquals(0f, res.getFloat(11), floatCompareDelta); assertEquals(0L, res.getLong(12)); assertEquals(null, res.getString(13)); assertEquals(null, res.getString(14)); assertEquals(null, res.getString(15)); assertEquals(null, res.getString(16)); assertEquals(null, res.getString(17)); assertEquals(null, res.getString(18)); assertEquals(null, res.getString(19)); assertEquals(null, res.getString(20)); assertEquals(null, res.getDate(20)); assertEquals(null, res.getString(21)); assertEquals(null, res.getString(22)); // row 2 assertTrue(res.next()); assertEquals(-1, res.getInt(1)); assertEquals(false, res.getBoolean(2)); assertEquals(-1.1d, res.getDouble(3), floatCompareDelta); assertEquals("", res.getString(4)); assertEquals("[]", res.getString(5)); assertEquals("{}", res.getString(6)); assertEquals("{}", res.getString(7)); assertEquals("{\"r\":null,\"s\":null,\"t\":null}", res.getString(8)); assertEquals(-1, res.getByte(9)); assertEquals(-1, res.getShort(10)); assertEquals(-1.0f, res.getFloat(11), floatCompareDelta); assertEquals(-1, res.getLong(12)); assertEquals("[]", res.getString(13)); assertEquals("{}", res.getString(14)); assertEquals("{\"r\":null,\"s\":null}", res.getString(15)); assertEquals("[]", res.getString(16)); assertEquals(null, res.getString(17)); assertEquals(null, res.getTimestamp(17)); assertEquals(null, res.getBigDecimal(18)); assertEquals(null, res.getString(19)); assertEquals(null, res.getString(20)); assertEquals(null, res.getDate(20)); assertEquals(null, res.getString(21)); assertEquals(null, res.getString(22)); assertEquals(null, res.getString(23)); // row 3 assertTrue(res.next()); assertEquals(1, res.getInt(1)); assertEquals(true, res.getBoolean(2)); assertEquals(1.1d, res.getDouble(3), floatCompareDelta); assertEquals("1", res.getString(4)); assertEquals("[1,2]", res.getString(5)); assertEquals("{1:\"x\",2:\"y\"}", res.getString(6)); assertEquals("{\"k\":\"v\"}", res.getString(7)); assertEquals("{\"r\":\"a\",\"s\":9,\"t\":2.2}", res.getString(8)); assertEquals(1, res.getByte(9)); assertEquals(1, res.getShort(10)); assertEquals(1.0f, res.getFloat(11), floatCompareDelta); assertEquals(1, res.getLong(12)); assertEquals("[[\"a\",\"b\"],[\"c\",\"d\"]]", res.getString(13)); assertEquals("{1:{11:12,13:14},2:{21:22}}", res.getString(14)); assertEquals("{\"r\":1,\"s\":{\"a\":2,\"b\":\"x\"}}", res.getString(15)); assertEquals("[{\"m\":{},\"n\":1},{\"m\":{\"a\":\"b\",\"c\":\"d\"},\"n\":2}]", res.getString(16)); assertEquals("2012-04-22 09:00:00.123456789", res.getString(17)); assertEquals("2012-04-22 09:00:00.123456789", res.getTimestamp(17).toString()); assertEquals("123456789.0123456", res.getBigDecimal(18).toString()); assertEquals("abcd", res.getString(19)); assertEquals("2013-01-01", res.getString(20)); assertEquals("2013-01-01", res.getDate(20).toString()); assertEquals("abc123", res.getString(21)); assertEquals("abc123 ", res.getString(22)); byte[] bytes = "X'01FF'".getBytes("UTF-8"); InputStream resultSetInputStream = res.getBinaryStream(23); int len = bytes.length; byte[] b = new byte[len]; resultSetInputStream.read(b, 0, len); for (int i = 0; i < len; i++) { assertEquals(bytes[i], b[i]); } // test getBoolean rules on non-boolean columns assertEquals(true, res.getBoolean(1)); assertEquals(true, res.getBoolean(4)); // test case sensitivity assertFalse(meta.isCaseSensitive(1)); assertFalse(meta.isCaseSensitive(2)); assertFalse(meta.isCaseSensitive(3)); assertTrue(meta.isCaseSensitive(4)); // no more rows assertFalse(res.next()); }
From source file:mom.trd.opentheso.bdd.helper.GroupHelper.java
/** * Permet de retourner un NodeConceptGroup par identifiant, par thsaurus et * par langue / ou null si rien cette fonction ne retourne pas les dtails * et les traductions//from w ww. java2 s. com * * @param ds le pool de connexion * @param idConceptGroup * @param idThesaurus * @param idLang * @return Objet Class NodeConceptGroup */ public NodeGroup getThisConceptGroup(HikariDataSource ds, String idConceptGroup, String idThesaurus, String idLang) { Connection conn; Statement stmt; ResultSet resultSet; NodeGroup nodeConceptGroup = null; ConceptGroup conceptGroup = null; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT * from concept_group where " + " idgroup = '" + idConceptGroup + "'" + " and idthesaurus = '" + idThesaurus + "'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { resultSet.next(); if (resultSet.getRow() != 0) { conceptGroup = new ConceptGroup(); conceptGroup.setIdgroup(idConceptGroup); conceptGroup.setIdthesaurus(idThesaurus); conceptGroup.setIdARk(resultSet.getString("id_ark")); conceptGroup.setIdtypecode(resultSet.getString("idtypecode")); conceptGroup.setNotation(resultSet.getString("notation")); } } if (conceptGroup != null) { query = "SELECT * FROM concept_group_label WHERE" + " idgroup = '" + conceptGroup.getIdgroup() + "'" + " AND idthesaurus = '" + idThesaurus + "'" + " AND lang = '" + idLang + "'"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { nodeConceptGroup = new NodeGroup(); resultSet.next(); if (resultSet.getRow() == 0) { // cas du Group non traduit nodeConceptGroup.setLexicalValue(""); nodeConceptGroup.setIdLang(idLang); } else { nodeConceptGroup.setLexicalValue(resultSet.getString("lexicalvalue")); nodeConceptGroup.setIdLang(idLang); nodeConceptGroup.setCreated(resultSet.getDate("created")); nodeConceptGroup.setModified(resultSet.getDate("modified")); } nodeConceptGroup.setConceptGroup(conceptGroup); } } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while adding element : " + idThesaurus, sqle); } return nodeConceptGroup; }
From source file:mom.trd.opentheso.bdd.helper.TermHelper.java
/** * Cette fonction permet de rcuprer les termes synonymes suivant un * id_term et son thsaurus et sa langue sous forme de classe NodeEM * * @param ds/*from w w w . j a v a 2 s . c om*/ * @param idTerm * @param idThesaurus * @return Objet class Concept */ public ArrayList<NodeEM> getAllNonPreferredTerms(HikariDataSource ds, String idTerm, String idThesaurus) { Connection conn; Statement stmt; ResultSet resultSet; ArrayList<NodeEM> nodeEMList = null; try { // Get connection from pool conn = ds.getConnection(); try { stmt = conn.createStatement(); try { String query = "SELECT non_preferred_term.lexical_value," + " non_preferred_term.created," + " non_preferred_term.modified," + " non_preferred_term.source," + " non_preferred_term.status," + " non_preferred_term.hiden," + " non_preferred_term.lang" + " FROM term, non_preferred_term WHERE" + " term.id_term = non_preferred_term.id_term AND" + " term.lang = non_preferred_term.lang AND" + " term.id_thesaurus = non_preferred_term.id_thesaurus" + " and non_preferred_term.id_term = '" + idTerm + "'" + " and non_preferred_term.id_thesaurus = '" + idThesaurus + "'" + " order by lexical_value ASC"; stmt.executeQuery(query); resultSet = stmt.getResultSet(); if (resultSet != null) { nodeEMList = new ArrayList<>(); while (resultSet.next()) { NodeEM nodeEM = new NodeEM(); nodeEM.setLexical_value(resultSet.getString("lexical_value")); nodeEM.setCreated(resultSet.getDate("created")); nodeEM.setModified(resultSet.getDate("modified")); nodeEM.setSource(resultSet.getString("source")); nodeEM.setStatus(resultSet.getString("status")); nodeEM.setHiden(resultSet.getBoolean("hiden")); nodeEM.setLang(resultSet.getString("lang")); nodeEMList.add(nodeEM); } } } finally { stmt.close(); } } finally { conn.close(); } } catch (SQLException sqle) { // Log exception log.error("Error while getting NonPreferedTerm of Term : " + idTerm, sqle); } return nodeEMList; }
From source file:migration.Helper.java
/** * Creates the journal array./*from ww w.jav a2 s . co m*/ * * @param rs * the rs * @param laenge * the laenge * @param factory * the factory * @param stringPakets * the string pakets * @param stringKonsortien * the string konsortien * @return the journal[] * @throws SQLException * the sQL exception */ public Journal[] createJournalArray(final ResultSet rs, final int laenge, final BeanFactory factory, final String[] stringPakets, final String[] stringKonsortien) throws SQLException { final Journal[] ret = new Journal[laenge]; this.journals = new int[laenge]; final PaketDao pdao = (PaketDao) factory.getBean("paketDao"); final KonsortiumDao kdao = (KonsortiumDao) factory.getBean("konsortiumDao"); final BibliotheksmitarbeiterDao bdao = (BibliotheksmitarbeiterDao) factory .getBean("bibliotheksmitarbeiterDao"); final InstitutionDao idao = (InstitutionDao) factory.getBean("institutionDao"); final FachDao fdao = (FachDao) factory.getBean("fachDao"); boolean try_again = false; // Hilfsvariablen: long jId = -1; String Verlag = ""; String Provider = ""; String Konsortium = ""; String Paket = ""; int Bibliothek = -1; String Titel = ""; String Kurztitel = ""; String ISSN = ""; String ISSNPrint = ""; String Kommentar = ""; String KommentarAdmin = ""; String KommentarIntranet = ""; Date Anmeldedatum = null; Date Freischaltdatum = null; String ZugangsID = ""; String ZugangsPasswort = ""; String Nutzungsbestimmungen = ""; Date Rotschaltungsdatum = null; String Rotschaltungsbemerkungen = ""; String Status = ""; Date Aenderungsdatum = null; String ZdbNummer = ""; Long EZBID = (long) -1; String Anker = ""; Boolean Readmetitelbezogen = false; String Herausgeber = ""; String ZugangUeber = ""; Date termin = null; int Fach1 = -1; int Fach2 = -1; int Fach3 = -1; this.journalfaecher = new HashSet[laenge]; for (int i = 0; i < laenge; i++) { try { if (!try_again) { rs.next(); jId = rs.getLong(1); this.journals[i] = (int) jId; Verlag = rs.getString("Verlag"); Provider = rs.getString("Provider"); Konsortium = rs.getString("Konsortium"); Paket = rs.getString("Paket"); Bibliothek = rs.getInt("Bibliothek"); Titel = rs.getString("Titel"); Kurztitel = rs.getString("Kurztitel"); ISSN = rs.getString("ISSN"); ISSNPrint = rs.getString("ISSNPrint"); Kommentar = rs.getString("Kommentar"); KommentarAdmin = rs.getString("KommentarAdmin"); KommentarIntranet = rs.getString("KommentarIntranet"); Anmeldedatum = rs.getDate("Anmeldedatum"); Freischaltdatum = rs.getDate("Freischaltdatum"); ZugangsID = rs.getString("ZugangsId"); ZugangsPasswort = rs.getString("ZugangsPasswort"); Nutzungsbestimmungen = rs.getString("Nutzungsbestimmungen"); Rotschaltungsdatum = rs.getDate("Rotschaltungsdatum"); Rotschaltungsbemerkungen = rs.getString("Rotschaltungsbemerkungen"); Status = rs.getString("Status"); Aenderungsdatum = rs.getDate("Aenderungsdatum"); ZdbNummer = rs.getString("ZdbNummer"); EZBID = rs.getLong("EZBID"); Anker = rs.getString("Anker"); Readmetitelbezogen = rs.getBoolean("Readmetitelbezogen"); Herausgeber = rs.getString("Herausgeber"); ZugangUeber = rs.getString("ZugangUeber"); Fach1 = rs.getInt("Fach1"); Fach2 = rs.getInt("Fach2"); Fach3 = rs.getInt("Fach3"); termin = rs.getDate("Termin"); } ret[i] = new Journal(); ret[i].setId(jId); int tmp = this.getIdFromStringArray(this.institutionen, Verlag); if (tmp > 0) { ret[i].setVerlag(idao.get((long) tmp)); // Institution } tmp = this.getIdFromStringArray(this.institutionen, Provider); if (tmp > 0) { ret[i].setProvider(idao.get((long) tmp)); // Institution } tmp = this.getIdFromStringArray(stringKonsortien, Konsortium); if (tmp > 0) { ret[i].setKonsortium(kdao.get((long) tmp)); // Konsortium } tmp = this.getIdFromStringArray(stringPakets, Paket); if (tmp > 0) { ret[i].setPaket(pdao.get((long) tmp)); // Paket } tmp = this.getIdFromIntArray(this.bibmitarb, Bibliothek); if (tmp > 0) { ret[i].setBibliotheksmitarbeiter(bdao.get((long) tmp)); // Bibliotheksmitarbeiter } ret[i].setTitel(Titel); ret[i].setKurztitel(Kurztitel); ret[i].setIssn(ISSN); ret[i].setIssnPrint(ISSNPrint); ret[i].setKommentar(Kommentar); ret[i].setKommentarAdmin(KommentarAdmin); // ret[i].setKommentarIntranet(KommentarIntranet); TODO // Einkommentieren wenn Martin ihn auf 2000 Zeichen gestellt hat // ! ret[i].setAnmeldedatum(Anmeldedatum); ret[i].setFreischaltdatum(Freischaltdatum); ret[i].setZugangsId(ZugangsID); ret[i].setZugangsPasswort(ZugangsPasswort); ret[i].setNutzungsbestimmungen(Nutzungsbestimmungen); ret[i].setRotschaltungsdatum(Rotschaltungsdatum); ret[i].setRotschaltungsbemerkungen(Rotschaltungsbemerkungen); ret[i].setStatus(Status); ret[i].setAenderungsdatum(Aenderungsdatum); ret[i].setZdbNummer(ZdbNummer); ret[i].setEzbId(EZBID); ret[i].setAnker(Anker); ret[i].setReadMeTitelbezogen(Readmetitelbezogen); ret[i].setHerausgeber(Herausgeber); ret[i].setZugangUeber(ZugangUeber); ret[i].setBearbeitungsdatum(termin); this.journalfaecher[i] = new HashSet(); tmp = this.getIdFromIntArray(this.faecher, Fach1); if (tmp != 0) { this.journalfaecher[i].add(fdao.get((long) tmp)); } tmp = this.getIdFromIntArray(this.faecher, Fach2); if (tmp != 0) { this.journalfaecher[i].add(fdao.get((long) tmp)); } tmp = this.getIdFromIntArray(this.faecher, Fach3); if (tmp != 0) { this.journalfaecher[i].add(fdao.get((long) tmp)); } if (!this.journalfaecher[i].isEmpty()) { ret[i].setFaecher(this.journalfaecher[i]); } // journalfaecher = null; try_again = false; } catch (final org.springframework.dao.DataAccessResourceFailureException e) { // e.printStackTrace(); // System.err.println("i: " + i); try_again = true; i--; } } return ret; }
From source file:edu.ku.brc.specify.web.SpecifyExplorer.java
/** * @param response// w w w . j a v a 2 s .c o m */ protected void generateDateChart(final HttpServletRequest request, final HttpServletResponse response, final String sql, final String title, final String x_AxisTitle, final String y_AxisTitle) { String type = request.getParameter("type"); SimpleDateFormat sdf = new SimpleDateFormat("yyyy"); Hashtable<String, NameId> alphaHash = new Hashtable<String, NameId>(); Vector<NameId> alphaList = null; Connection connection = null; Statement stmt = null; ResultSet rs = null; try { connection = DBConnection.getInstance().createConnection(); stmt = connection.createStatement(); rs = stmt.executeQuery(sql); while (rs.next()) { Date date = rs.getDate(1); String dateStr = sdf.format(date); int year = Integer.parseInt(dateStr); int decade = (year / 10) * 10; dateStr = Integer.toString(decade); NameId nis = alphaHash.get(dateStr); if (nis == null) { nis = new NameId(dateStr, 0); alphaHash.put(dateStr, nis); } nis.add(); } } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (connection != null) { connection.close(); } } catch (Exception e) { e.printStackTrace(); } } alphaList = new Vector<NameId>(alphaHash.values()); Collections.sort(alphaList); createChart(response, type, alphaList, title, x_AxisTitle, y_AxisTitle); }
From source file:edu.ku.brc.specify.toycode.mexconabio.BuildFromRecovery.java
/** * //ww w . j a va 2 s . c om */ public void process() throws SQLException { buildTags = new BuildTags(); buildTags.setDbConn(dbConn); buildTags.setDbConn2(dbConn); buildTags.initialPrepareStatements(); BasicSQLUtils.setDBConnection(dbConn); IdMapperMgr idMapperMgr = IdMapperMgr.getInstance(); idMapperMgr.setDBs(srcDBConn2, dbConn); geoStmt1 = dbConn.prepareStatement( "SELECT GeographyID FROM geography WHERE RankID = ? AND ParentID = ? AND LOWER(Abbrev) = ?"); geoStmt2 = dbConn .prepareStatement("SELECT GeographyID FROM geography WHERE RankID = ? AND LOWER(Abbrev) = ?"); agentStmt = dbConn .prepareStatement("SELECT AgentID FROM agent WHERE LOWER(FirstName) = ? AND LOWER(LastName) = ?"); tagStmt = dbConn.prepareStatement( "SELECT CollectionObjectID FROM collectionobject WHERE CollectionID = 4 AND LOWER(FieldNumber) = ?"); BasicSQLUtils.update(srcDBConn, "UPDATE recovery SET r_date = null WHERE r_date = '0000-00-00'"); boolean doTags = true; if (doTags) { int divId = 2; int dspId = 3; int colId = 32768; String sql = "SELECT tagid, " + "r_city, r_state, r_zip, r_country, r_date, r_lat, r_long, " + "reporter_first, reporter_last, reporter_city, reporter_state, reporter_country, reporter_zip, " + "dir, dist, gender, " + "t_first, t_middle, t_last, t_city, t_state, t_country, t_postalcode, t_org, t_lat, t_long, t_date FROM recovery ORDER BY recovid ASC"; Statement stmt = srcDBConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); log.debug("Querying for Tags..."); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; log.debug("Done querying for Tags..."); Calendar cal = Calendar.getInstance(); Timestamp ts = new Timestamp(cal.getTime().getTime()); String common = "TimestampCreated, Version, CreatedByAgentID"; String coStr = String.format( "INSERT INTO collectionobject (CatalogNumber, FieldNumber, Text1, Text2, Remarks, CollectionID, CollectionMemberId, CollectingEventID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?)", common); String ceStr = String.format( "INSERT INTO collectingevent (StartDate, DisciplineID, LocalityID, %s) VALUES(?,?,?,?,?,?)", common); String lcStr = String.format( "INSERT INTO locality (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, LocalityName, GeographyID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)", common); String clStr = String.format( "INSERT INTO collector (OrderNumber, IsPrimary, CollectingEventID, DivisionID, AgentID, %s) VALUES(?,?,?,?,?,?,?,?)", common); String rlStr = String.format( "INSERT INTO collectionrelationship (collectionRelTypeID, LeftSideCollectionID, RightSideCollectionID, %s) VALUES(?,?,?,?,?,?)", common); String agStr = String .format("INSERT INTO agent (AgentType, FirstName, LastName, %s) VALUES(?,?,?,?,?,?)", common); String adStr = String.format( "INSERT INTO address (City, State, PostalCode, Country, AgentID, %s) VALUES(?,?,?,?, ?,?,?,?)", common); String lcUpdateStr = "UPDATE locality SET Latitude1=?, Longitude1=?, SrcLatLongUnit=?, Lat1text=?, Long1text=?, LatLongType=? WHERE LocalityID = ?"; String lcStr2 = "SELECT LocalityID FROM locality WHERE LocalityName LIKE ? AND LocalityName LIKE ?"; PreparedStatement coStmt = dbConn.prepareStatement(coStr); PreparedStatement ceStmt = dbConn.prepareStatement(ceStr); PreparedStatement lcStmt = dbConn.prepareStatement(lcStr); PreparedStatement clStmt = dbConn.prepareStatement(clStr); PreparedStatement rlStmt = dbConn.prepareStatement(rlStr); PreparedStatement agStmt = dbConn.prepareStatement(agStr); PreparedStatement adStmt = dbConn.prepareStatement(adStr); PreparedStatement lcUpStmt = dbConn.prepareStatement(lcUpdateStr); PreparedStatement lcStmt2 = dbConn.prepareStatement(lcStr2); int recNum = 1; while (rs.next()) { String tag = rs.getString(1); String city = rs.getString(2); String state = rs.getString(3); String zip = rs.getString(4); String country = rs.getString(5); Date date = rs.getDate(6); double lat = rs.getDouble(7); boolean isLatNull = rs.wasNull(); double lon = rs.getDouble(8); boolean isLonNull = rs.wasNull(); String dir = rs.getString(9); String dist = rs.getString(10); String gender = rs.getString(11); String rep_first = rs.getString(12); String rep_last = rs.getString(13); String rep_city = rs.getString(14); String rep_state = rs.getString(15); String rep_country = rs.getString(16); String rep_zip = rs.getString(17); String t_first = rs.getString(18); //String t_middle = rs.getString(19); String t_last = rs.getString(20); String t_city = rs.getString(21); String t_state = rs.getString(22); String t_country = rs.getString(23); String t_zip = rs.getString(24); //String t_org = rs.getString(25); double t_lat = rs.getDouble(26); boolean isTLatNull = rs.wasNull(); double t_lon = rs.getDouble(27); boolean isTLonNull = rs.wasNull(); //String oldState = state; city = condense(rep_city, t_city, city); state = condense(rep_state, state, t_state); country = condense(rep_country, country, t_country); zip = condense(rep_zip, zip, t_zip); rep_first = condense(rep_first, t_first); rep_last = condense(rep_last, t_last); /*boolean debug = ((rep_state != null && rep_state.equals("IA")) || (t_state != null && t_state.equals("IA")) || (oldState != null && oldState.equals("IA"))); if (debug && (state == null || !state.equals("IA"))) { System.out.println("ouch"); }*/ if (rep_first != null && rep_first.length() > 50) { rep_first = rep_first.substring(0, 50); } lat = isLatNull && !isTLatNull ? t_lat : lat; lon = isLonNull && !isTLonNull ? t_lon : lon; try { // (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, MaxElevation, LocalityName, GeographyID Integer geoId = buildTags.getGeography(country, state, null); // Latitude varies between -90 and 90, and Longitude between -180 and 180. if (lat < -90.0 || lat > 90.0) { lcStmt.setObject(1, null); lcStmt.setObject(4, null); } else { lcStmt.setDouble(1, lat); lcStmt.setString(4, Double.toString(lat)); lcUpStmt.setDouble(1, lat); lcUpStmt.setString(4, Double.toString(lat)); } if (lon < -180.0 || lon > 180.0) { lcStmt.setObject(2, null); lcStmt.setObject(5, null); } else { lcStmt.setDouble(2, lon); lcStmt.setString(5, Double.toString(lon)); lcUpStmt.setDouble(2, lon); lcUpStmt.setString(5, Double.toString(lon)); } String locName = null; String fullName = null; Integer locId = null; geoId = buildTags.getGeography(country, state, null); if (geoId != null) { fullName = geoFullNameHash.get(geoId); if (fullName == null) { fullName = BasicSQLUtils .querySingleObj("SELECT FullName FROM geography WHERE GeographyID = " + geoId); geoFullNameHash.put(geoId, fullName); } if (StringUtils.isNotEmpty(city)) { locName = city + ", " + fullName; } else { locName = fullName; } locId = localityHash.get(locName); if (locId == null) { lcStmt2.setString(1, "%" + city); lcStmt2.setString(2, country + "%"); ResultSet lcRS = lcStmt2.executeQuery(); if (lcRS.next()) { locId = lcRS.getInt(1); if (!lcRS.wasNull()) { localityHash.put(locName, locId); } } lcRS.close(); } } else { //unknown++; fullName = "Unknown"; locName = buildTags.buildLocalityName(city, fullName); geoId = 27507; // Unknown locId = localityHash.get(locName); //log.error("Couldn't find matching geography["+country+", "+state+", "+county+"]"); } if (locId == null) { lcStmt.setByte(3, (byte) 0); lcStmt.setString(6, "Point"); lcStmt.setInt(7, dspId); lcStmt.setString(8, getLocalityName(country, state, null, city)); lcStmt.setObject(9, geoId); lcStmt.setTimestamp(10, ts); lcStmt.setInt(11, 1); lcStmt.setInt(12, 1); lcStmt.executeUpdate(); locId = BasicSQLUtils.getInsertedId(lcStmt); } else if (!isLatNull && !isLonNull) { int count = BasicSQLUtils.getCountAsInt( "SELECT COUNT(*) FROM locality WHERE Latitude1 IS NULL AND Longitude1 IS NULL AND LocalityID = " + locId); if (count == 1) { lcUpStmt.setByte(3, (byte) 0); lcUpStmt.setString(6, "Point"); lcUpStmt.setInt(7, locId); lcUpStmt.executeUpdate(); } } // (StartDate, Method, DisciplineID, LocalityID ceStmt.setDate(1, date); ceStmt.setInt(2, dspId); ceStmt.setInt(3, locId); ceStmt.setTimestamp(4, ts); ceStmt.setInt(5, 1); ceStmt.setInt(6, 1); ceStmt.executeUpdate(); Integer ceId = BasicSQLUtils.getInsertedId(ceStmt); //(CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId coStmt.setString(1, String.format("%09d", recNum++)); coStmt.setString(2, tag); coStmt.setString(3, gender); coStmt.setString(4, dir); coStmt.setString(5, dist); coStmt.setInt(6, colId); coStmt.setInt(7, colId); coStmt.setInt(8, ceId); coStmt.setTimestamp(9, ts); coStmt.setInt(10, 1); coStmt.setInt(11, 1); coStmt.executeUpdate(); //Integer coId = BasicSQLUtils.getInsertedId(coStmt); Integer agentId = getAgentId(agentStmt, rep_first, rep_last); if (agentId == null) { agStmt.setInt(1, 0); agStmt.setString(2, rep_first); agStmt.setString(3, rep_last); agStmt.setTimestamp(4, ts); agStmt.setInt(5, 1); agStmt.setInt(6, 1); agStmt.executeUpdate(); agentId = BasicSQLUtils.getInsertedId(agStmt); if (agentId != null) { adStmt.setString(1, rep_city); adStmt.setString(2, rep_state); adStmt.setString(3, rep_zip); adStmt.setString(4, rep_country); adStmt.setInt(5, agentId); adStmt.setTimestamp(6, ts); adStmt.setInt(7, 1); adStmt.setInt(8, 1); adStmt.executeUpdate(); } else { log.error("agentId is null after being created: " + rep_first + ", " + rep_last); } } // OrderIndex, IsPrimary, CollectingEventID, DivisionID, AgentID clStmt.setInt(1, 0); clStmt.setBoolean(2, true); clStmt.setInt(3, ceId); clStmt.setInt(4, divId); clStmt.setInt(5, agentId); clStmt.setTimestamp(6, ts); clStmt.setInt(7, 1); clStmt.setInt(8, 1); clStmt.executeUpdate(); } catch (Exception ex) { log.debug(recNum + " tag[" + tag + "]"); ex.printStackTrace(); } cnt++; if (cnt % 100 == 0) { System.out.println("Col Obj: " + cnt); } } coStmt.close(); ceStmt.close(); lcStmt.close(); clStmt.close(); rlStmt.close(); agStmt.close(); adStmt.close(); lcUpStmt.close(); buildTags.cleanup(); } }