List of usage examples for java.sql PreparedStatement toString
public String toString()
From source file:dk.netarkivet.harvester.datamodel.extendedfield.ExtendedFieldDBDAO.java
@Override public synchronized void update(ExtendedField aExtendedField) { ArgumentNotValid.checkNotNull(aExtendedField, "aExtendedField"); Connection connection = HarvestDBConnection.get(); PreparedStatement statement = null; try {//from w ww .j a v a 2s . c om final Long extendedfieldId = aExtendedField.getExtendedFieldID(); if (!exists(connection, extendedfieldId)) { throw new UnknownID("Extended Field id " + extendedfieldId + " is not known in persistent storage"); } connection.setAutoCommit(false); statement = connection.prepareStatement("" + "UPDATE extendedfield " + "SET extendedfield_id = ?, " + " extendedfieldtype_id = ?, " + " name = ?, " + " format = ?, " + " defaultvalue = ?, " + " options = ?, " + " datatype = ?, " + " mandatory = ?, " + " sequencenr = ?, " + " maxlen = ? " + "WHERE extendedfield_id = ? "); statement.setLong(1, aExtendedField.getExtendedFieldID()); statement.setLong(2, aExtendedField.getExtendedFieldTypeID()); statement.setString(3, aExtendedField.getName()); statement.setString(4, aExtendedField.getFormattingPattern()); statement.setString(5, aExtendedField.getDefaultValue()); statement.setString(6, aExtendedField.getOptions()); statement.setInt(7, aExtendedField.getDatatype()); // the following conversion from boolean to int is necessary, // because the database column 'mandatory' is a int field // and not a boolean (NAS-2127) statement.setInt(8, aExtendedField.isMandatory() ? 1 : 0); statement.setInt(9, aExtendedField.getSequencenr()); statement.setInt(10, aExtendedField.getMaxlen()); statement.setLong(11, aExtendedField.getExtendedFieldID()); //TODO log.debug(statement.toString()); statement.executeUpdate(); connection.commit(); } catch (SQLException e) { String message = "SQL error updating extendedfield " + aExtendedField + " in database" + "\n" + ExceptionUtils.getSQLExceptionCause(e); log.warn(message, e); throw new IOFailure(message, e); } finally { DBUtils.closeStatementIfOpen(statement); DBUtils.rollbackIfNeeded(connection, "update extendedfield", aExtendedField); HarvestDBConnection.release(connection); } }
From source file:de.ingrid.importer.udk.strategy.v1.IDCStrategyDefault1_0_2.java
protected void processSysList() throws Exception { String entityName = "sys_list"; if (log.isInfoEnabled()) { log.info("Importing " + entityName + "..."); }/*from w w w. ja va 2 s . c om*/ // set up mapping of old syslist 100 to new syslist 100 via map<oldValue, newKey> HashMap<String, Integer> mapOldValueToNewKeyList100 = new HashMap<String, Integer>(); mapOldValueToNewKeyList100.put("EPSG:4178 / Pulkovo 1942(83) / geographisch", 4178); mapOldValueToNewKeyList100.put("EPSG:4230 / ED50 / geographisch ", 4230); mapOldValueToNewKeyList100.put("EPSG:4258 / ETRS89 / geographisch", 4258); mapOldValueToNewKeyList100.put("EPSG:4284 / Pulkovo 1942 / geographisch", 4284); mapOldValueToNewKeyList100.put("EPSG:4314 / DHDN / geographisch", 4314); mapOldValueToNewKeyList100.put("EPSG:4326 / WGS 84 / geographisch", 4326); mapOldValueToNewKeyList100.put("EPSG:23031 / ED50 / UTM Zone 31N", 23031); mapOldValueToNewKeyList100.put("EPSG:23032 / ED50 / UTM Zone 32N", 23032); mapOldValueToNewKeyList100.put("EPSG:23033 / ED50 / UTM Zone 33N", 23033); mapOldValueToNewKeyList100.put("EPSG:32631 / WGS 84 / UTM Zone 31N", 32631); mapOldValueToNewKeyList100.put("EPSG:32632 / WGS 84 / UTM Zone 32N/33N", 32632); mapOldValueToNewKeyList100.put("EPSG:25831 / ETRS89 / UTM Zone 31N ", 25831); mapOldValueToNewKeyList100.put("EPSG:25832 / ETRS89 / UTM Zone 32N", 25832); mapOldValueToNewKeyList100.put("EPSG:25833 / ETRS89 / UTM Zone 33N", 25833); mapOldValueToNewKeyList100.put("EPSG:28463 / Pulkovo 1942 / Gauss-Krger 2N/3N ", 28463); mapOldValueToNewKeyList100.put("EPSG:31466 / DHDN / Gauss-Krger Zone 2", 31466); mapOldValueToNewKeyList100.put("EPSG:31467 /DHDN / Gauss-Krger Zone 3", 31467); mapOldValueToNewKeyList100.put("EPSG:31468 / DHDN / Gauss-Krger Zone 4", 31468); mapOldValueToNewKeyList100.put("EPSG:31469 / DHDN / Gauss-Krger Zone 5", 31469); mapOldValueToNewKeyList100.put("EPSG:31492 /DHDN / Germany zone 2", 31466); mapOldValueToNewKeyList100.put("EPSG:31493 / DHDN / Germany zone 3", 31467); mapOldValueToNewKeyList100.put("EPSG:31494 / DHDN / Germany zone 4", 31468); mapOldValueToNewKeyList100.put("EPSG:31495 / DHDN / Germany zone 5", 31469); mapOldValueToNewKeyList100.put("DE_42/83 / GK_3", 9000001); mapOldValueToNewKeyList100.put("DE_DHDN / GK_3", 9000002); mapOldValueToNewKeyList100.put("DE_ETRS89 / UTM", 9000003); mapOldValueToNewKeyList100.put("DE_PD/83 / GK_3", 9000005); mapOldValueToNewKeyList100.put("DE_RD/83 / GK_3", 9000006); // set up mapping of old syslist 101 to new syslist 101 via map<oldValue, newKey> HashMap<String, Integer> mapOldValueToNewKeyList101 = new HashMap<String, Integer>(); mapOldValueToNewKeyList101.put("Baltic Sea", 5105); mapOldValueToNewKeyList101.put("Normaal Amsterdams Peil", 900002); mapOldValueToNewKeyList101.put("European Vertical Reference Frame 2000", 5129); mapOldValueToNewKeyList101.put("Kronstdter Pegel (HN)", 900004); mapOldValueToNewKeyList101.put("DE_AMST / NH", 900002); mapOldValueToNewKeyList101.put("DE_AMST / NOH", 900003); mapOldValueToNewKeyList101.put("DE_KRON / NH", 900004); pSqlStr = "INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, description, maintainable) " + "VALUES (?, ?, ?, ?, ?, ?, ?)"; PreparedStatement p = jdbc.prepareStatement(pSqlStr); sqlStr = "DELETE FROM sys_list"; jdbc.executeUpdate(sqlStr); for (Iterator<Row> i = dataProvider.getRowIterator(entityName); i.hasNext();) { Row row = i.next(); if (row.getInteger("lst_id") == 1000) { // ignore list with id==1000, codelist 505 will be used instead } else if (row.getInteger("lst_id") == 3571 && row.getInteger("entry_id") == 4) { // ignore list with id==3571 and entry_id==4, codelist 505 will // be used instead } else { int cnt = 1; p.setInt(cnt++, row.getInteger("primary_key")); // id p.setInt(cnt++, row.getInteger("lst_id")); // lst_id p.setInt(cnt++, row.getInteger("entry_id")); // entry_id p.setString(cnt++, IDCStrategyHelper.transLanguageCode(row.get("lang_id"))); // lang_id p.setString(cnt++, row.get("name")); // name p.setString(cnt++, null); // description JDBCHelper.addInteger(p, cnt++, row.getInteger("maintainable")); // maintainable try { p.executeUpdate(); } catch (Exception e) { log.error("Error executing SQL: " + p.toString(), e); throw e; } } } if (log.isInfoEnabled()) { log.info("Importing " + entityName + "... done."); } entityName = "sys_codelist_domain"; if (log.isInfoEnabled()) { log.info("Importing " + entityName + "..."); } for (Iterator<Row> i = dataProvider.getRowIterator(entityName); i.hasNext();) { Row row = i.next(); if (row.getInteger("codelist_id") == 100) { // list with id==100 has to be mapped from old to new values ! // set up mapping ! Integer oldKey = row.getInteger("domain_id"); if (mapOldKeyToNewKeyList100.get(oldKey) == null) { String oldValue = row.get("name"); Integer newKey = mapOldValueToNewKeyList100.get(oldValue); if (newKey != null) { mapOldKeyToNewKeyList100.put(oldKey, newKey); } } } else if (row.getInteger("codelist_id") == 101) { // list with id==101 has to be mapped from old to new values ! // set up mapping ! Integer oldKey = row.getInteger("domain_id"); if (mapOldKeyToNewKeyList101.get(oldKey) == null) { String oldValue = row.get("name"); Integer newKey = mapOldValueToNewKeyList101.get(oldValue); if (newKey != null) { mapOldKeyToNewKeyList101.put(oldKey, newKey); } } } else { int cnt = 1; p.setInt(cnt++, row.getInteger("primary_key")); // id p.setInt(cnt++, row.getInteger("codelist_id")); // lst_id p.setInt(cnt++, row.getInteger("domain_id")); // entry_id p.setString(cnt++, IDCStrategyHelper.transLanguageCode(row.get("lang_id"))); // lang_id p.setString(cnt++, row.get("name")); // name p.setString(cnt++, row.get("description")); // description p.setInt(cnt++, 0); // maintainable try { p.executeUpdate(); } catch (Exception e) { log.error("Error executing SQL: " + p.toString(), e); throw e; } } } if (log.isInfoEnabled()) { log.info("Importing " + entityName + "... done."); } if (log.isInfoEnabled()) { log.info("Importing special values..."); } dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1400, 1, 'de', 'Daten und Karten', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1400, 2, 'de', 'Konzeptionelles', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1400, 3, 'de', 'Rechtliches', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1400, 4, 'de', 'Risikobewertungen', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1400, 5, 'de', 'Statusberichte', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1400, 6, 'de', 'Umweltzustand', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 1, 'de', 'Abfall', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 2, 'de', 'Altlasten', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 3, 'de', 'Bauen', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 4, 'de', 'Boden', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 5, 'de', 'Chemikalien', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 6, 'de', 'Energie', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 7, 'de', 'Forstwirtschaft', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 8, 'de', 'Gentechnik', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 9, 'de', 'Geologie', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 10, 'de', 'Gesundheit', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 11, 'de', 'Lrm und Erschtterungen', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 12, 'de', 'Landwirtschaft', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 13, 'de', 'Luft und Klima', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 14, 'de', 'Nachhaltige Entwicklung', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 15, 'de', 'Natur und Landschaft', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 16, 'de', 'Strahlung', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 17, 'de', 'Tierschutz', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 18, 'de', 'Umweltinformationen', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 19, 'de', 'Umweltwirtschaft', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 20, 'de', 'Verkehr', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 1410, 21, 'de', 'Wasser', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2000, 3100, 'de', 'Methode / Datengrundlage', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2000, 3210, 'de', 'Basisdaten', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2000, 3345, 'de', 'Basisdaten', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2000, 3515, 'de', 'Herstellungsprozess', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2000, 3520, 'de', 'Fachliche Grundlage', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2000, 3535, 'de', 'Schlsselkatalog', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2000, 3555, 'de', 'Symbolkatalog', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2000, 3570, 'de', 'Datengrundlage', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2000, 5066, 'de', 'Verweis zu Dienst', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2010, 3360, 'de', 'Standort', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2010, 3360, 'en', 'Location', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2010, 3400, 'de', 'Projektleiter', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2010, 3400, 'en', 'Project Manager', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2010, 3410, 'de', 'Beteiligte', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2010, 3410, 'en', 'Participants', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 5100, 1, 'de', 'WMS', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 5100, 2, 'de', 'WFS', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 5110, 1, 'de', 'GetCapabilities', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 5110, 2, 'de', 'GetMap', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 5110, 3, 'de', 'GetFeatureInfo', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 5120, 1, 'de', 'DescribeFeatureType', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 5120, 2, 'de', 'GetFeature', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 5120, 3, 'de', 'GetFeature', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 5120, 4, 'de', 'LockFeature', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 5120, 5, 'de', 'Transaction', 0)"); // remove old values jdbc.executeUpdate("DELETE FROM sys_list WHERE lst_id=2240"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2240, 1, 'de', 'HTML', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2240, 2, 'de', 'JPG', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2240, 3, 'de', 'PNG', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2240, 4, 'de', 'GIF', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2240, 5, 'de', 'PDF', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2240, 6, 'de', 'DOC', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2240, 7, 'de', 'PPT', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2240, 8, 'de', 'XLS', 0)"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name, maintainable) VALUES (" + dataProvider.getId() + ", 2240, 9, 'de', 'ASCII/Text', 0)"); if (log.isInfoEnabled()) { log.info("Importing special values... done."); } if (log.isInfoEnabled()) { log.info("Importing new syslist 100 (Raumbezugsystem), 101 (Vertikaldaten)..."); } // syslist 100 ! for (Object key : mapNewKeyToNewValueList100.keySet()) { Object value = mapNewKeyToNewValueList100.get(key); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name) VALUES (" + dataProvider.getId() + ", 100, " + key + ", 'de', '" + value + "')"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name) VALUES (" + dataProvider.getId() + ", 100, " + key + ", 'en', '" + value + "')"); } // syslist 101 ! for (Object key : mapNewKeyToNewValueList101.keySet()) { Object value = mapNewKeyToNewValueList101.get(key); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name) VALUES (" + dataProvider.getId() + ", 101, " + key + ", 'de', '" + value + "')"); dataProvider.setId(dataProvider.getId() + 1); jdbc.executeUpdate("INSERT INTO sys_list (id, lst_id, entry_id, lang_id, name) VALUES (" + dataProvider.getId() + ", 101, " + key + ", 'en', '" + value + "')"); } if (log.isInfoEnabled()) { log.info("Importing new syslist 100 (Raumbezugsystem), 101 (Vertikaldaten)... done"); } }
From source file:com.flexoodb.engines.FlexJAXBDBDataEngine.java
public Collection<Object> runQuery2(String query, Class c, boolean usedefaultimplementation) throws Exception { Vector v = new Vector(); Connection conn = null;// ww w . j a v a 2 s.c o m try { conn = (Connection) _pool.getConnection(); String tablename = query.split("\\s")[3]; // always search the index! if (checkTable(tablename, conn, false)) { StringBuffer q = new StringBuffer("where "); boolean hasid = false; if (query.toUpperCase().indexOf("WHERE") > 0) { String sub = query.substring(query.toUpperCase().indexOf("WHERE") + 5); sub = sub.replaceAll("<=", " <eq; "); sub = sub.replaceAll(">=", " >eq; "); sub = sub.replaceAll("<>", " &nteq; "); sub = sub.replaceAll("=", " = "); sub = sub.replaceAll(">", " > "); sub = sub.replaceAll("<", " < "); sub = sub.replaceAll("<eq;", "<="); sub = sub.replaceAll(">eq;", ">="); sub = sub.replaceAll("&nteq;", "<>").trim(); //System.out.println("from:"+sub); boolean done = false; boolean id = false; int seq = 0; String col = null; String condition = null; while (!done) { int x = sub.indexOf(" "); String word = sub.substring(0, x < 0 ? sub.length() : x); int wlen = word.length(); if (word.startsWith("'")) { word = sub.substring(1, sub.indexOf("'", 1)); wlen = word.length() + 2; } //System.out.println("w:"+word+"< "+wlen+" wl:"+word.length()); // check if its a predicate if (":like:=:>:<:<=:>=:<>:".indexOf(":" + word.toLowerCase() + ":") > -1) { condition = word; seq = 2; } else if (":and:or:not:".indexOf(":" + word.toLowerCase() + ":") > -1) { q.append(" " + word.trim() + " "); seq = 0; } else if (seq == 0)// it must be a field! { seq = 1; // fields sequence if (word.trim().equalsIgnoreCase("parentid") || word.trim().equalsIgnoreCase("id")) { q.append(" _a." + word.trim()); id = true; hasid = true; } else if (word.trim().equalsIgnoreCase("order")) { String[] order = sub.split("\\s"); if (!order[2].equalsIgnoreCase("id") && !order[2].equalsIgnoreCase("parentid")) { // get the 3rd word -- ie the field if (!q.toString().toUpperCase().endsWith("WHERE")) { q.append(" and "); } q.append(" (_b.element='" + order[2] + "')"); q.append(" " + order[0] + " by _b.value " + sub.substring(sub.indexOf(order[2]) + order[2].length()).trim()); } else { q.append(" " + sub); } done = true; } else if (word.trim().equalsIgnoreCase("element") || word.trim().equalsIgnoreCase("limit") || word.trim().equalsIgnoreCase("desc") || word.trim().equalsIgnoreCase("asc")) { q.append(" " + sub); done = true; } else { word = word.replaceAll("'", "\'").trim(); //q.append(" (element='"+word.trim().replaceAll("'","")+"'"); q.append(" (_b.element='" + word + "'"); //col = word.trim().replaceAll("'",""); col = word; } } else if (seq == 2) { //word = word.replaceAll("'"," "); word = word.replaceAll("'", "\'"); if (id) { q.append("" + condition + "'" + word.trim() + "' and _a.id=_b.id "); } else { boolean valchanged = false; try { // we look for dates! if (col != null) { Method met = c.getMethod( "get" + col.substring(0, 1).toUpperCase() + col.substring(1), (Class[]) null); Class c1 = (Class) met.getGenericReturnType(); if (c1.getSimpleName().equalsIgnoreCase("XMLGregorianCalendar") && !word.isEmpty()) { //q.append(" and str_to_date(value,\"%Y-%m-%d\") "+condition+" '"+word.trim().replaceAll("'","")+"')"); q.append(" and str_to_date(_b.value,\"%Y-%m-%d\") " + condition + " '" + word.trim() + "')"); valchanged = true; } } } catch (Exception e) { e.printStackTrace(); } if (!valchanged) { //q.append(" and value "+condition+" '"+word.trim().replaceAll("'","")+"')"); q.append(" and _b.value " + condition + " '" + word.trim() + "')"); } col = null; } seq = 0; condition = null; id = false; } sub = sub.substring(wlen).trim(); if (x < 0 || sub.length() == 0) { done = true; } } // restructure query with parenthesis int i = q.toString().indexOf("_a.id=_b.id and"); if (i > 0) { String qf = q.toString(); qf = qf.substring(0, i + 16) + " (" + qf.substring(i + 17).trim() + ")"; q = new StringBuffer(qf); } } else { int tl = tablename.length(); q = new StringBuffer(query.substring(query.indexOf(tablename) + tl)); } PreparedStatement ps = null; boolean searchindex = false; System.out.println(">>>>111"); String stmt = "select distinct " + (hasid ? "_a" : "_b") + ".id from " + tablename.toLowerCase() + " _a, " + tablename.toLowerCase() + "_index _b " + q.toString(); System.out.println(">>>>" + stmt); ps = (PreparedStatement) conn.prepareStatement(stmt); if (!usedefaultimplementation) { //ps = (PreparedStatement) conn.prepareStatement("select distinct a.id from "+tablename.toLowerCase()+" a, "+tablename.toLowerCase()+"_index b "+q.toString()); } else { //ps = (PreparedStatement) conn.prepareStatement("select distinct a.id from "+tablename.toLowerCase()+"_index a"+q.toString()); searchindex = true; } if (_showsql) { System.out.println(this.getClass().getName() + " SQL Query:>" + ps.toString() + "<<"); } ResultSet rec = ps.executeQuery(); // check if a record was found while (rec != null && !rec.isClosed() && rec.next()) { String id = rec.getString("id"); try { Object o = null; PreparedStatement ps2 = (PreparedStatement) conn .prepareStatement("select id,parentid,content from " + tablename.toLowerCase() + " where id='" + id + "'"); ResultSet res = ps2.executeQuery(); // check if a record was found if (res != null && res.next()) { String i = res.getString("id"); String p = res.getString("parentid"); o = new FlexContainer(_flexutils.getObject(res.getString("content"), c)); ((FlexContainer) o).setId(i); ((FlexContainer) o).setParentId(p); ps2.close(); } else { ps2.close(); if (searchindex) { // then the values found must be orphans! we delete the index contents removeValues(id, tablename, conn); } } if (o != null) { v.add(o); Enumeration en = v.elements(); while (en.hasMoreElements()) { en.nextElement(); } } } catch (Exception g) { throw g; } } } } catch (Exception f) { throw f; } finally { try { if (conn != null) { _pool.releaseConnection(conn); } } catch (Exception g) { } } return v; }
From source file:org.forgerock.openidm.repo.jdbc.impl.query.TableQueries.java
/** * Execute a query, either a pre-configured query by using the query ID, or * a query expression passed as part of the params. * * The keys for the input parameters as well as the return map entries are * in QueryConstants.//from w ww . j a va 2s . c om * * @param type * the resource component name targeted by the URI * @param params * the parameters which include the query id, or the query * expression, as well as the token key/value pairs to replace in * the query * @param con * a handle to a database connection newBuilder for exclusive use * by the query method whilst it is executing. * @return The query result, which includes meta-data about the query, and * the result set itself. * @throws BadRequestException * if the passed request parameters are invalid, e.g. missing * query id or query expression or tokens. * @throws InternalServerErrorException * if the preparing or executing the query fails because of * configuration or DB issues */ public List<Map<String, Object>> query(final String type, Map<String, Object> params, Connection con) throws ResourceException { List<Map<String, Object>> result = null; params.put(ServerConstants.RESOURCE_NAME, type); // If paged results are requested then decode the cookie in order to determine // the index of the first result to be returned. final int requestPageSize = (Integer) params.get(PAGE_SIZE); final String offsetParam; final String pageSizeParam; if (requestPageSize > 0) { offsetParam = String.valueOf((Integer) params.get(PAGED_RESULTS_OFFSET)); pageSizeParam = String.valueOf(requestPageSize); } else { offsetParam = "0"; pageSizeParam = String.valueOf(Integer.MAX_VALUE); } params.put(PAGED_RESULTS_OFFSET, offsetParam); params.put(PAGE_SIZE, pageSizeParam); QueryFilter<JsonPointer> queryFilter = (QueryFilter) params.get(QUERY_FILTER); String queryExpression = (String) params.get(QUERY_EXPRESSION); String queryId = (String) params.get(QUERY_ID); if (queryId == null && queryExpression == null && queryFilter == null) { throw new BadRequestException("Either " + QUERY_ID + ", " + QUERY_EXPRESSION + ", or " + QUERY_FILTER + " to identify/define a query must be passed in the parameters. " + params); } logger.debug("Querying " + params); final PreparedStatement foundQuery; try { if (queryFilter != null) { foundQuery = parseQueryFilter(con, queryFilter, params); } else if (queryExpression != null) { foundQuery = resolveInlineQuery(con, queryExpression, params); } else if (queries.queryIdExists(queryId)) { foundQuery = queries.getQuery(con, queryId, type, params); } else { throw new BadRequestException("The passed query identifier " + queryId + " does not match any configured queries on the JDBC repository service."); } } catch (SQLException ex) { final String queryDescription; if (queryFilter != null) { queryDescription = queryFilter.toString(); } else if (queryExpression != null) { queryDescription = queryExpression; } else { queryDescription = queries.getQueryInfo(queryId).getQueryString(); } throw new InternalServerErrorException("DB reported failure preparing query: " + queryDescription + " with params: " + params + " error code: " + ex.getErrorCode() + " sqlstate: " + ex.getSQLState() + " message: " + ex.getMessage(), ex); } Name eventName = getEventName(queryId); EventEntry measure = Publisher.start(eventName, foundQuery, null); ResultSet rs = null; try { rs = foundQuery.executeQuery(); result = resultMapper.mapQueryToObject(rs, queryId, type, params, this); measure.setResult(result); } catch (SQLException ex) { throw new InternalServerErrorException("DB reported failure executing query " + foundQuery.toString() + " with params: " + params + " error code: " + ex.getErrorCode() + " sqlstate: " + ex.getSQLState() + " message: " + ex.getMessage(), ex); } catch (IOException ex) { throw new InternalServerErrorException("Failed to convert result objects for query " + foundQuery.toString() + " with params: " + params + " message: " + ex.getMessage(), ex); } finally { CleanupHelper.loggedClose(rs); CleanupHelper.loggedClose(foundQuery); measure.end(); } return result; }
From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java
private AtomicFeed getAtomicFeed(long id) { AtomicFeed feed = null;/*from ww w . j a va 2 s .c o m*/ try { checkOpenness(); String feedIdFeed = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_ID; String relFeedId = IotHubDataHandler.TABLE_FEED_FEATURE_REL + "." + IotHubDataHandler.KEY_FEED_FEATURE_REL_FEED_ID; String relFeaturedId = IotHubDataHandler.TABLE_FEED_FEATURE_REL + "." + IotHubDataHandler.KEY_FEED_FEATURE_REL_FEATURE_ID; String attr1 = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_NAME; String attr2 = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_METADATA; String attrType = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_TYPE; String sql = "SELECT " + relFeaturedId + ", " + attr1 + ", " + attr2 + " FROM " + IotHubDataHandler.TABLE_FEED + " INNER JOIN " + IotHubDataHandler.TABLE_FEED_FEATURE_REL + " ON " + feedIdFeed + " = " + relFeedId + " WHERE " + feedIdFeed + " = ?" + " AND " + attrType + " = '" + IotHubDataHandler.ATOMIC_FEED + "'"; PreparedStatement ps = connection.prepareStatement(sql); ps.setLong(1, id); ResultSet rs = ps.executeQuery(); if (rs.next()) { long featureId = rs.getLong(1); Feature feature = getFeature(featureId); if (feature != null) { String feedName = rs.getString(2); String feedMetadata = rs.getString(3); List<String> keywords = getFeedKeywords(id); feed = new AtomicFeed(id, feedName, feedMetadata, keywords, feature); } else { Log.e(TAG, "The feature does not exist"); } } else { Log.e(TAG, "No results for this request: " + ps.toString()); } rs.close(); ps.close(); return feed; } catch (SQLException | IotHubDatabaseException e) { e.printStackTrace(); return null; } }
From source file:it.cnr.icar.eric.server.persistence.rdb.SlotDAO.java
/** * @param parentInsert It should be set to true if Slot insert is part of new * RegistryObject insert (i.e. in the case of SubmitObjectsRequest). It should * be set to false in the case of AddSlotsRequest because the parent of the * slot is expected to be already submitted by previous SubmitObjectRequest. * In the latter case whether the parents of the slots exist will be checked. */// w w w . ja v a 2s .c o m public void insert(List<?> slots, boolean parentInsert) throws RegistryException { PreparedStatement pstmt = null; String parentId = (String) parent; if (slots.size() == 0) { return; } try { String sql = "INSERT INTO " + getTableName() + " (sequenceId, " + "name_, slotType, value, parent)" + " VALUES(?, ?, ?, ?, ?)"; pstmt = context.getConnection().prepareStatement(sql); List<String> duplicateSlotsNames = getDuplicateSlots(slots); if (duplicateSlotsNames.size() > 0) { // Some slots have duplicate name throw new DuplicateSlotsException(parentId, duplicateSlotsNames); } RegistryObjectDAO roDAO = new RegistryObjectDAO(context); // Check whether the parent exist in database, in case the parent // has been inserted by the previous SubmitObjectsRequest // (i.e. in the case of AddSlotsRequest) if (!parentInsert && !roDAO.registryObjectExist(parentId)) { // The parent does not exist throw new SlotsParentNotExistException(parentId); } List<String> slotsNamesAlreadyExist = slotsExist(parentId, slots); if (slotsNamesAlreadyExist.size() > 0) { // Some slots for this RegistryObject already exist throw new SlotsExistException(parentId, slotsNamesAlreadyExist); } Iterator<?> iter = slots.iterator(); @SuppressWarnings("unused") Vector<Object> slotNames = new Vector<Object>(); while (iter.hasNext()) { SlotType1 slot = (SlotType1) iter.next(); String slotName = slot.getName(); String slotType = slot.getSlotType(); List<String> values = slot.getValueList().getValue(); int size = values.size(); for (int j = 0; j < size; j++) { String value = values.get(j); pstmt.setInt(1, j); pstmt.setString(2, slotName); pstmt.setString(3, slotType); pstmt.setString(4, value); pstmt.setString(5, parentId); log.trace("stmt = " + pstmt.toString()); pstmt.addBatch(); } } if (slots.size() > 0) { @SuppressWarnings("unused") int[] updateCounts = pstmt.executeBatch(); } } catch (SQLException e) { log.error(ServerResourceBundle.getInstance().getString("message.CaughtException1"), e); throw new RegistryException(e); } finally { closeStatement(pstmt); } }
From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java
private ExecutableFeed getExecutableFeed(long id) { ExecutableFeed executableFeed = null; try {// w w w. ja v a 2 s.co m checkOpenness(); String feedIdFeed = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_ID; String attr1 = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_NAME; String attr2 = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_METADATA; String attrType = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_TYPE; String attr3 = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_READABLE; String attr4 = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_WRITABLE; String sql = "SELECT " + attr1 + ", " + attr2 + ", " + attr3 + ", " + attr4 + " FROM " + IotHubDataHandler.TABLE_FEED + " WHERE " + feedIdFeed + " = ?" + " AND " + attrType + " = '" + IotHubDataHandler.EXECUTABLE_FEED + "'"; PreparedStatement ps = connection.prepareStatement(sql); ps.setLong(1, id); ResultSet rs = ps.executeQuery(); if (rs.next()) { String feedName = rs.getString(1); String feedMetadata = rs.getString(2); boolean feedReadable = rs.getInt(3) != 0; boolean feedWritable = rs.getInt(4) != 0; //TODO make a table for the feed description and get the data from it ExecutableFeedDescription description = new ExecutableFeedDescription(new JSONObject()); List<String> keywords = getFeedKeywords(id); executableFeed = new ExecutableFeed(id, feedName, feedMetadata, keywords, feedReadable, feedWritable, description); } else { Log.e(TAG, "No results for this request: " + ps.toString()); } rs.close(); ps.close(); return executableFeed; } catch (SQLException | IotHubDatabaseException e) { e.printStackTrace(); return null; } }
From source file:otn.mobile.bl.TrackServicesHandler.java
private void insertGeometryPoints(int trackId, List<OtnServiceGeometryPoints> geomPoints, List<OtnServiceGeometryPoints> startPoint, List<OtnServiceGeometryPoints> endPoint, String start_date, String end_date, String urlFile) { java.sql.Connection conpg;/*from w w w . j a v a2 s .co m*/ List<Point> points = new ArrayList<Point>(); org.postgis.LineString linesToAdd = null; try { // log.info("4"); System.out.println("********************"); System.out.println("Insert Geometry points"); /* * Load the JDBC driver and establish a connection. */ Class.forName("org.postgresql.Driver"); String url = "jdbc:postgresql://localhost:5432/otn_web_app"; conpg = DriverManager.getConnection(url, username, password); // conpg = DriverManager.getConnection(url, "postgres", "admin"); // conpg = DriverManager.getConnection(url, "mobile_service", "otn_ck@n"); /* * Add the geometry types to the connection. Note that you * must cast the connection to the pgsql-specific connection * implementation before calling the addDataType() method. */ ((org.postgresql.PGConnection) conpg).addDataType("geometry", Class.forName("org.postgis.PGgeometry")); /* * Create a statement and execute a select query. */ conpg.setAutoCommit(false); if (geomPoints != null) { for (OtnServiceGeometryPoints p : geomPoints) { // System.out.println("long " + p.getLongitude()); // System.out.println("lat " + p.getLatitude()); // log.info("latidude " + p.getLatitude()); points.add(new Point(p.getLongitude(), p.getLatitude())); // points.add(new Point(30.123, 10.13)); // points.add(new Point(10.123, 30.123)); // points.add(new Point(40.13, 40.123)); } linesToAdd = new org.postgis.LineString(points.toArray(new Point[] {})); linesToAdd.setSrid(4326); } org.postgis.Point startPointToAdd = new org.postgis.Point(); for (OtnServiceGeometryPoints sp : startPoint) { // System.out.println("start long " + sp.getLongitude()); // System.out.println("start lat " + sp.getLatitude()); // log.info("start lat " + sp.getLatitude()); startPointToAdd.setX(sp.getLongitude()); startPointToAdd.setY(sp.getLatitude()); } startPointToAdd.setSrid(4326); org.postgis.Point endPointToAdd = new org.postgis.Point(); for (OtnServiceGeometryPoints ep : endPoint) { // System.out.println("end long " + ep.getLongitude()); // System.out.println("end lat " + ep.getLatitude()); // log.info("start lat " + ep.getLatitude()); endPointToAdd.setX(ep.getLongitude()); endPointToAdd.setY(ep.getLatitude()); } endPointToAdd.setSrid(4326); // log.info("trackId : " + trackId); // log.info("start_date : " + start_date); // log.info("end_date : " + end_date); PreparedStatement psSE = conpg.prepareStatement( "INSERT INTO otn_web_app.track_spatial (track_id,geom,start_point,end_point,start_time,end_time,url_file) VALUES (?, ?, ?, ?, ?, ?, ?)"); psSE.setInt(1, trackId); psSE.setObject(2, new org.postgis.PGgeometry(linesToAdd)); psSE.setObject(3, new org.postgis.PGgeometry(startPointToAdd)); psSE.setObject(4, new org.postgis.PGgeometry(endPointToAdd)); psSE.setObject(5, start_date); psSE.setObject(6, end_date); psSE.setObject(7, urlFile); System.out.println("psSE before: " + psSE.toString()); log.info("psSE before: " + psSE.toString()); psSE.execute(); // log.info("psSE after: " + psSE.toString()); // log.info("5"); psSE.close(); conpg.commit(); conpg.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:fi.helsinki.cs.iot.kahvihub.database.sqliteJdbc.IotHubDatabaseSqliteJDBCImpl.java
private ComposedFeed getComposedFeed(long id) { ComposedFeed composedFeed = null;// ww w . j a v a 2 s . com try { checkOpenness(); String feedIdFeed = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_ID; String fieldIdFeed = IotHubDataHandler.TABLE_FIELD + "." + IotHubDataHandler.KEY_FIELD_FEED_ID; String attr1 = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_NAME; String attr2 = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_METADATA; String attrType = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_TYPE; String attr3 = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_STORAGE; String attr4 = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_READABLE; String attr5 = IotHubDataHandler.TABLE_FEED + "." + IotHubDataHandler.KEY_FEED_WRITABLE; String attr6 = IotHubDataHandler.TABLE_FIELD + "." + IotHubDataHandler.KEY_FIELD_ID; String attr7 = IotHubDataHandler.TABLE_FIELD + "." + IotHubDataHandler.KEY_FIELD_NAME; String attr8 = IotHubDataHandler.TABLE_FIELD + "." + IotHubDataHandler.KEY_FIELD_METADATA; String attr9 = IotHubDataHandler.TABLE_FIELD + "." + IotHubDataHandler.KEY_FIELD_TYPE; String attr10 = IotHubDataHandler.TABLE_FIELD + "." + IotHubDataHandler.KEY_FIELD_OPTIONAL; String sql = "SELECT " + attr1 + ", " + attr2 + ", " + attr3 + ", " + attr4 + ", " + attr5 + ", " + attr6 + ", " + attr7 + ", " + attr8 + ", " + attr9 + ", " + attr10 + " FROM " + IotHubDataHandler.TABLE_FEED + " INNER JOIN " + IotHubDataHandler.TABLE_FIELD + " ON " + feedIdFeed + " = " + fieldIdFeed + " WHERE " + feedIdFeed + " = ?" + " AND " + attrType + " = '" + IotHubDataHandler.COMPOSED_FEED + "'"; PreparedStatement ps = connection.prepareStatement(sql); ps.setLong(1, id); ResultSet rs = ps.executeQuery(); if (rs.next()) { String feedName = rs.getString(1); String feedMetadata = rs.getString(2); boolean feedStorage = rs.getInt(3) != 0; boolean feedReadable = rs.getInt(4) != 0; boolean feedWritable = rs.getInt(5) != 0; Map<String, Field> fieldList = new HashMap<>(); do { long fieldId = rs.getLong(6); String fieldName = rs.getString(7); String fieldMetadata = rs.getString(8); String fieldType = rs.getString(9); boolean fieldOptional = rs.getInt(10) != 0; List<String> keywords = getFieldKeywords(fieldId); Field field = new Field(fieldId, fieldName, fieldType, fieldMetadata, fieldOptional, keywords); fieldList.put(fieldName, field); } while (rs.next()); List<String> keywords = getFeedKeywords(id); composedFeed = new ComposedFeed(id, feedName, feedMetadata, keywords, feedStorage, feedReadable, feedWritable, fieldList); } else { Log.e(TAG, "No results for this request: " + ps.toString()); } rs.close(); ps.close(); return composedFeed; } catch (SQLException | IotHubDatabaseException e) { e.printStackTrace(); return null; } }
From source file:org.deegree.metadata.persistence.ebrim.eo.EbrimEOMDStore.java
@Override public int getRecordCount(MetadataQuery query) throws MetadataStoreException { PreparedStatement stmt = null; ResultSet rs = null;/*from w w w . jav a 2s. c om*/ ConnectionProvider prov = workspace.getResource(ConnectionProviderProvider.class, connId); Connection conn = getConnection(true); try { EOPropertyNameMapper propMapper = new EOPropertyNameMapper(query.getQueryTypeNames(), useLegacyPredicates); if (query.getFilter() instanceof IdFilter) { throw new MetadataStoreException("ID filters are currently not supported."); } AbstractWhereBuilder wb = new PostGISWhereBuilder(null, propMapper, (OperatorFilter) query.getFilter(), query.getSorting(), false, useLegacyPredicates); AliasedRIMType returnType = propMapper.getReturnType(query.getReturnTypeNames()); StringBuilder sql = new StringBuilder("SELECT COUNT(DISTINCT("); sql.append(propMapper.getTableAlias(returnType)); sql.append(".internalId)) FROM "); sql.append(propMapper.getTable(returnType)); sql.append(' '); sql.append(propMapper.getTableAlias(returnType)); boolean first = true; for (AliasedRIMType queryType : propMapper.getQueryTypes()) { if (queryType != returnType) { if (first) { sql.append(" LEFT OUTER JOIN "); } else { sql.append(" FULL OUTER JOIN "); } sql.append(propMapper.getTable(queryType).name()); sql.append(' '); sql.append(propMapper.getTableAlias(queryType)); sql.append(" ON TRUE"); first = false; } } // cope with rim:RegistryPackage -> rim:RegistryObjectList/* join for (Join additionalJoin : propMapper.getAdditionalJoins()) { if (first) { sql.append(" LEFT OUTER JOIN "); } else { sql.append(" FULL OUTER JOIN "); } sql.append(additionalJoin.getToTable()); sql.append(' '); sql.append(additionalJoin.getToTableAlias()); sql.append(" ON "); sql.append(additionalJoin.getSQLJoinCondition()); first = false; } if (wb.getWhere() != null) { sql.append(" WHERE ").append(wb.getWhere().getSQL()); } stmt = conn.prepareStatement(sql.toString()); int i = 1; if (wb.getWhere() != null) { for (SQLArgument argument : wb.getWhere().getArguments()) { argument.setArgument(stmt, i++); } } LOG.debug("Execute: " + stmt.toString()); rs = executeQuery(stmt, prov, queryTimeout); rs.next(); return rs.getInt(1); } catch (Throwable t) { JDBCUtils.close(rs, stmt, conn, LOG); LOG.debug(t.getMessage(), t); throw new MetadataStoreException(t.getMessage(), t); } finally { JDBCUtils.close(rs, stmt, conn, LOG); } }