List of usage examples for java.sql Statement getConnection
Connection getConnection() throws SQLException;
Connection object that produced this Statement object. From source file:herddb.cli.HerdDBCLI.java
private static ExecuteStatementResult executeStatement(boolean verbose, boolean ignoreerrors, boolean frommysqldump, boolean rewritestatements, String query, final Statement statement, final TableSpaceMapper tableSpaceMapper, boolean getResults, boolean prettyPrint) throws SQLException, ScriptException { query = query.trim();/*from www . ja v a 2 s. c om*/ if (query.isEmpty() || query.startsWith("--")) { return null; } String formattedQuery = query.toLowerCase(); if (formattedQuery.endsWith(";")) { // mysqldump formattedQuery = formattedQuery.substring(0, formattedQuery.length() - 1); } if (formattedQuery.equals("exit") || formattedQuery.equals("quit")) { System.out.println("Connection closed."); System.exit(0); } if (frommysqldump && (formattedQuery.startsWith("lock tables") || formattedQuery.startsWith("unlock tables"))) { // mysqldump return null; } Boolean setAutoCommit = null; if (formattedQuery.startsWith("autocommit=")) { String value = ""; if (formattedQuery.split("=").length > 1) { value = formattedQuery.split("=")[1]; } switch (value) { case "true": setAutoCommit = true; break; case "false": setAutoCommit = false; break; default: System.out.println("No valid value for autocommit. Only true and false allowed."); return null; } } if (verbose) { System.out.println("Executing query:" + query); } try { if (setAutoCommit != null) { statement.getConnection().setAutoCommit(setAutoCommit); System.out.println("Set autocommit=" + setAutoCommit + " executed."); return null; } if (formattedQuery.equals("commit")) { statement.getConnection().commit(); System.out.println("Commit executed."); return null; } if (formattedQuery.equals("rollback")) { statement.getConnection().rollback(); System.out.println("Rollback executed."); return null; } QueryWithParameters rewritten = null; if (rewritestatements) { rewritten = rewriteQuery(query, tableSpaceMapper, frommysqldump); } if (rewritten != null) { if (rewritten.schema != null) { HerdDBConnection connection = statement.getConnection().unwrap(HerdDBConnection.class); if (connection != null && !connection.getSchema().equalsIgnoreCase(rewritten.schema)) { commitAndChangeSchema(connection, rewritten.schema); } } try (PreparedStatement ps = statement.getConnection().prepareStatement(rewritten.query);) { int i = 1; for (Object o : rewritten.jdbcParameters) { ps.setObject(i++, o); } boolean resultSet = ps.execute(); return reallyExecuteStatement(ps, resultSet, verbose, getResults, prettyPrint); } } else { boolean resultSet = statement.execute(query); return reallyExecuteStatement(statement, resultSet, verbose, getResults, prettyPrint); } } catch (SQLException err) { if (ignoreerrors) { println("ERROR:" + err); return null; } else { throw err; } } }
From source file:org.springframework.jdbc.core.JdbcTemplateTests.java
public void testNativeJdbcExtractorInvoked() throws Exception { MockControl ctrlResultSet = MockControl.createControl(ResultSet.class); final ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock(); mockResultSet.close();//from w ww. j a v a2 s. co m ctrlResultSet.setVoidCallable(2); MockControl ctrlStatement = MockControl.createControl(Statement.class); final Statement mockStatement = (Statement) ctrlStatement.getMock(); if (debugEnabled) { mockStatement.getWarnings(); ctrlStatement.setReturnValue(null); } mockStatement.close(); ctrlStatement.setVoidCallable(); MockControl ctrlStatement2 = MockControl.createControl(Statement.class); final Statement mockStatement2 = (Statement) ctrlStatement2.getMock(); mockStatement2.executeQuery("my query"); ctrlStatement2.setReturnValue(mockResultSet, 1); MockControl ctrlPreparedStatement = MockControl.createControl(PreparedStatement.class); final PreparedStatement mockPreparedStatement = (PreparedStatement) ctrlPreparedStatement.getMock(); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); MockControl ctrlPreparedStatement2 = MockControl.createControl(PreparedStatement.class); final PreparedStatement mockPreparedStatement2 = (PreparedStatement) ctrlPreparedStatement2.getMock(); mockPreparedStatement2.executeQuery(); ctrlPreparedStatement2.setReturnValue(mockResultSet, 1); MockControl ctrlReturnResultSet = MockControl.createControl(ResultSet.class); final ResultSet mockReturnResultSet = (ResultSet) ctrlReturnResultSet.getMock(); mockReturnResultSet.next(); ctrlReturnResultSet.setReturnValue(false); mockReturnResultSet.close(); ctrlReturnResultSet.setVoidCallable(2); MockControl ctrlCallableStatement = MockControl.createControl(CallableStatement.class); final CallableStatement mockCallableStatement = (CallableStatement) ctrlCallableStatement.getMock(); if (debugEnabled) { mockCallableStatement.getWarnings(); ctrlCallableStatement.setReturnValue(null); } mockCallableStatement.close(); ctrlCallableStatement.setVoidCallable(); MockControl ctrlCallableStatement2 = MockControl.createControl(CallableStatement.class); final CallableStatement mockCallableStatement2 = (CallableStatement) ctrlCallableStatement2.getMock(); mockCallableStatement2.execute(); ctrlCallableStatement2.setReturnValue(true); mockCallableStatement2.getUpdateCount(); ctrlCallableStatement2.setReturnValue(-1); mockCallableStatement2.getResultSet(); ctrlCallableStatement2.setReturnValue(mockReturnResultSet); mockCallableStatement2.getMoreResults(); ctrlCallableStatement2.setReturnValue(false); mockCallableStatement2.getUpdateCount(); ctrlCallableStatement2.setReturnValue(-1); ctrlResultSet.replay(); ctrlStatement.replay(); ctrlStatement2.replay(); ctrlPreparedStatement.replay(); ctrlPreparedStatement2.replay(); ctrlReturnResultSet.replay(); ; ctrlCallableStatement.replay(); ctrlCallableStatement2.replay(); mockConnection.createStatement(); ctrlConnection.setReturnValue(mockStatement, 1); replay(); JdbcTemplate template = new JdbcTemplate(mockDataSource); template.setNativeJdbcExtractor(new NativeJdbcExtractor() { public boolean isNativeConnectionNecessaryForNativeStatements() { return false; } public boolean isNativeConnectionNecessaryForNativePreparedStatements() { return false; } public boolean isNativeConnectionNecessaryForNativeCallableStatements() { return false; } public Connection getNativeConnection(Connection con) { return con; } public Connection getNativeConnectionFromStatement(Statement stmt) throws SQLException { return stmt.getConnection(); } public Statement getNativeStatement(Statement stmt) { assertTrue(stmt == mockStatement); return mockStatement2; } public PreparedStatement getNativePreparedStatement(PreparedStatement ps) { assertTrue(ps == mockPreparedStatement); return mockPreparedStatement2; } public CallableStatement getNativeCallableStatement(CallableStatement cs) { assertTrue(cs == mockCallableStatement); return mockCallableStatement2; } public ResultSet getNativeResultSet(ResultSet rs) { return rs; } }); template.query("my query", new ResultSetExtractor() { public Object extractData(ResultSet rs2) { assertEquals(mockResultSet, rs2); return null; } }); template.query(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection conn) { return mockPreparedStatement; } }, new ResultSetExtractor() { public Object extractData(ResultSet rs2) { assertEquals(mockResultSet, rs2); return null; } }); template.call(new CallableStatementCreator() { public CallableStatement createCallableStatement(Connection con) { return mockCallableStatement; } }, new ArrayList()); ctrlStatement.verify(); ctrlStatement2.verify(); ctrlPreparedStatement.verify(); ctrlPreparedStatement2.verify(); ctrlCallableStatement.verify(); ctrlCallableStatement2.verify(); }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
@Test public void testParentReferences() throws Exception { /* Test parent references from Statement */ Statement s = this.con.createStatement(); ResultSet rs = s.executeQuery("SELECT * FROM " + dataTypeTableName); assertTrue(s.getConnection() == this.con); assertTrue(rs.getStatement() == s);/*from ww w . ja va 2 s.c om*/ rs.close(); s.close(); /* Test parent references from PreparedStatement */ PreparedStatement ps = this.con.prepareStatement("SELECT * FROM " + dataTypeTableName); rs = ps.executeQuery(); assertTrue(ps.getConnection() == this.con); assertTrue(rs.getStatement() == ps); rs.close(); ps.close(); /* Test DatabaseMetaData queries which do not have a parent Statement */ DatabaseMetaData md = this.con.getMetaData(); assertTrue(md.getConnection() == this.con); rs = md.getCatalogs(); assertNull(rs.getStatement()); rs.close(); rs = md.getColumns(null, null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getFunctions(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getImportedKeys(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getPrimaryKeys(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getProcedureColumns(null, null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getProcedures(null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getSchemas(); assertNull(rs.getStatement()); rs.close(); rs = md.getTableTypes(); assertNull(rs.getStatement()); rs.close(); rs = md.getTables(null, null, null, null); assertNull(rs.getStatement()); rs.close(); rs = md.getTypeInfo(); assertNull(rs.getStatement()); rs.close(); }
From source file:org.openconcerto.sql.model.SQLDataSource.java
/** * Execute la requete avec le statement pass. Attention cette mthode ne peut fermer le * statement car elle retourne directement le resultSet. * /*from www. jav a2 s. co m*/ * @param query le requte excuter. * @param stmt le statement. * @return le rsultat de la requte, should never be null according to the spec but Derby don't * care. * @throws SQLException si erreur lors de l'excution de la requte. */ private ResultSet execute(String query, Statement stmt) throws SQLException, RTInterruptedException { // System.err.println("\n" + count + "*** " + query + "\n"); if (State.DEBUG) State.INSTANCE.beginRequest(query); // test before calling JDBC methods and creating threads boolean interrupted = false; if (QUERY_TUNING > 0) { try { Thread.sleep(QUERY_TUNING); } catch (InterruptedException e1) { interrupted = true; } } else { interrupted = Thread.currentThread().isInterrupted(); } if (interrupted) { throw new RTInterruptedException("request interrupted : " + query); } final long t1 = System.currentTimeMillis(); ResultSet rs = null; try { // MAYBE un truc un peu plus formel if (query.startsWith("INSERT") || query.startsWith("UPDATE") || query.startsWith("DELETE") || query.startsWith("CREATE") || query.startsWith("ALTER") || query.startsWith("DROP") || query.startsWith("SET")) { // MS SQL doesn't support UPDATE final boolean returnGenK = query.startsWith("INSERT") && stmt.getConnection().getMetaData().supportsGetGeneratedKeys(); stmt.executeUpdate(query, returnGenK ? Statement.RETURN_GENERATED_KEYS : Statement.NO_GENERATED_KEYS); rs = returnGenK ? stmt.getGeneratedKeys() : null; } else { // TODO en faire qu'un seul par Connection final ExecutorThread thr = new ExecutorThread(stmt, query); // on lance l'excution thr.start(); // et on attend soit qu'elle finisse soit qu'on soit interrompu try { rs = thr.getRs(); } catch (InterruptedException e) { thr.stopQuery(); throw new InterruptedQuery("request interrupted : " + query, e, thr); } } } finally { if (State.DEBUG) State.INSTANCE.endRequest(query); } long t2 = System.currentTimeMillis(); // obviously very long queries tend to last longer, that's normal so don't warn if (t2 - t1 > 1000 && query.length() < 1000) { System.err.println("Warning:" + (t2 - t1) + "ms pour :" + query); } count++; return rs; }
From source file:org.ramadda.repository.database.DatabaseManager.java
/** * _more_//from www . j a v a 2s . c o m * * @param statement _more_ * * @throws SQLException _more_ */ public void closeAndReleaseConnection(Statement statement) throws SQLException { if (statement == null) { return; } Connection connection = null; try { connection = statement.getConnection(); statement.close(); } catch (Throwable ignore) { } if (connection != null) { // System.err.println( "CONNECTION: Closing connection"); closeConnection(connection); } else { // Misc.printStack("statement with no connection"); // new IllegalArgumentException()); // getLogManager().logError( // "CONNECTION: Tried to close a statement with no connection", // new IllegalArgumentException()); } }
From source file:com.alibaba.wasp.jdbc.TestJdbcStatement.java
@Test public void testStatement() throws SQLException, IOException, InterruptedException { Statement stat = conn.createStatement(); assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, conn.getHoldability()); conn.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT); assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, conn.getHoldability()); // ignored//from w w w . ja va 2 s .c om stat.setCursorName("x"); // fixed return value assertEquals(stat.getFetchDirection(), ResultSet.FETCH_FORWARD); // ignored stat.setFetchDirection(ResultSet.FETCH_REVERSE); // ignored stat.setMaxFieldSize(100); assertEquals(conf.getInt(FConstants.WASP_JDBC_FETCHSIZE, FConstants.DEFAULT_WASP_JDBC_FETCHSIZE), stat.getFetchSize()); stat.setFetchSize(10); assertEquals(10, stat.getFetchSize()); stat.setFetchSize(0); assertEquals(conf.getInt(FConstants.WASP_JDBC_FETCHSIZE, FConstants.DEFAULT_WASP_JDBC_FETCHSIZE), stat.getFetchSize()); assertEquals(ResultSet.TYPE_FORWARD_ONLY, stat.getResultSetType()); Statement stat2 = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT); assertEquals(ResultSet.TYPE_SCROLL_SENSITIVE, stat2.getResultSetType()); assertEquals(ResultSet.HOLD_CURSORS_OVER_COMMIT, stat2.getResultSetHoldability()); assertEquals(ResultSet.CONCUR_READ_ONLY, stat2.getResultSetConcurrency()); assertEquals(0, stat.getMaxFieldSize()); assertTrue(!((JdbcStatement) stat2).isClosed()); stat2.close(); assertTrue(((JdbcStatement) stat2).isClosed()); ResultSet rs; int count; boolean result; stat.execute("CREATE TABLE TEST {REQUIRED INT64 ID;" + "REQUIRED STRING VALUE; }PRIMARY KEY(ID), " + "ENTITY GROUP ROOT,ENTITY GROUP KEY(ID);"); TEST_UTIL.waitTableEnabled(Bytes.toBytes("TEST"), 5000); ResultInHBasePrinter.printMETA(conf, LOG); ResultInHBasePrinter.printFMETA(conf, LOG); ResultInHBasePrinter.printTable("test", "WASP_ENTITY_TEST", conf, LOG); conn.getTypeMap(); // this method should not throw an exception - if not supported, this // calls are ignored assertEquals(ResultSet.CONCUR_READ_ONLY, stat.getResultSetConcurrency()); // stat.cancel(); stat.setQueryTimeout(10); assertTrue(stat.getQueryTimeout() == 10); stat.setQueryTimeout(0); assertTrue(stat.getQueryTimeout() == 0); // assertThrows(SQLErrorCode.INVALID_VALUE_2, stat).setQueryTimeout(-1); assertTrue(stat.getQueryTimeout() == 0); trace("executeUpdate"); count = stat.executeUpdate("INSERT INTO TEST (ID,VALUE) VALUES (1,'Hello')"); assertEquals(1, count); count = stat.executeUpdate("INSERT INTO TEST (VALUE,ID) VALUES ('JDBC',2)"); assertEquals(1, count); count = stat.executeUpdate("UPDATE TEST SET VALUE='LDBC' WHERE ID=1"); assertEquals(1, count); count = stat.executeUpdate("DELETE FROM TEST WHERE ID=-1"); assertEquals(0, count); count = stat.executeUpdate("DELETE FROM TEST WHERE ID=1"); assertEquals(1, count); count = stat.executeUpdate("DELETE FROM TEST WHERE ID=2"); assertEquals(1, count); result = stat.execute("INSERT INTO TEST(ID,VALUE) VALUES(1,'Hello')"); assertTrue(!result); result = stat.execute("INSERT INTO TEST(VALUE,ID) VALUES('JDBC',2)"); assertTrue(!result); result = stat.execute("UPDATE TEST SET VALUE='LDBC' WHERE ID=2"); assertTrue(!result); result = stat.execute("DELETE FROM TEST WHERE ID=1"); assertTrue(!result); result = stat.execute("DELETE FROM TEST WHERE ID=2"); assertTrue(!result); result = stat.execute("DELETE FROM TEST WHERE ID=3"); assertTrue(!result); // getMoreResults rs = stat.executeQuery("SELECT ID,VALUE FROM TEST WHERE ID=1"); assertFalse(stat.getMoreResults()); assertThrows(SQLErrorCode.OBJECT_CLOSED, rs).next(); assertTrue(stat.getUpdateCount() == -1); count = stat.executeUpdate("DELETE FROM TEST WHERE ID=1"); assertFalse(stat.getMoreResults()); assertTrue(stat.getUpdateCount() == -1); WaspAdmin admin = new WaspAdmin(TEST_UTIL.getConfiguration()); admin.disableTable("TEST"); stat.execute("DROP TABLE TEST"); admin.waitTableNotLocked("TEST".getBytes()); stat.executeUpdate("DROP TABLE IF EXISTS TEST"); assertTrue(stat.getWarnings() == null); stat.clearWarnings(); assertTrue(stat.getWarnings() == null); assertTrue(conn == stat.getConnection()); admin.close(); stat.close(); }
From source file:org.fao.geonet.services.dataprep.ChangeColumnType.java
public Element exec(Element params, ServiceContext context) throws Exception { GeonetContext gc = (GeonetContext) context.getHandlerContext(Geonet.CONTEXT_NAME); // context.getUserSession(). DataManager dataMan = gc.getDataManager(); // Return response // Element results = new Element("results"); JSONObject responseJSONObj = new JSONObject(); if (cmd != null && "change_type".equals(cmd)) { String dbInfo = Util.getParam(params, "dbInfo"); String tableName = Util.getParam(params, "tableName"); String selectedcolumns = Util.getParam(params, "selectedcolumns"); String target_type = Util.getParam(params, "target_type"); JSONArray selectedcolumnsJSONArray = new JSONArray(selectedcolumns); JSONObject dbInfoJSONObj = new JSONObject(dbInfo); Connection candidatedb_conn = jeeves.util.jdbc.DirectJDBCConnection.getNewConnection( dbInfoJSONObj.getString("ip"), dbInfoJSONObj.getString("port"), dbInfoJSONObj.getString("database_name"), dbInfoJSONObj.getString("username"), dbInfoJSONObj.getString("password")); Integer success_geocode_count = 0; Integer failure_geocode_count = 0; JSONArray failureRecords = new JSONArray(); JSONArray successRecords = new JSONArray(); Statement stmt = null; String integer_rg_exp = "^\\\\d+$"; String double_rg_exp = "^\\\\d+\\\\.\\\\d+$"; String negative_double_rg_exp = "^\\\\-\\\\d+\\\\.\\\\d+$"; try {/*from w w w. j a v a 2 s. c o m*/ stmt = candidatedb_conn.createStatement(); int size = selectedcolumnsJSONArray.length(); for (int i = 0; i < size; i++) { JSONObject columnJSONObj = (JSONObject) selectedcolumnsJSONArray.get(i); String columnName = columnJSONObj.getString("name"); // ResultSet anomalyRS = null; if ("Integer".equals(target_type)) { anomalyRS = stmt.executeQuery(String.format( "select t.\"%s\" from \"%s\" t where t.\"%s\"::text !~ '%s' limit 1 ", columnName, tableName, columnName, integer_rg_exp)); } else if ("Double".equals(target_type)) { anomalyRS = stmt.executeQuery(String.format( "select t.\"%s\" from \"%s\" t where ( (t.\"%s\"::text !~ '%s') and (t.\"%s\"::text !~ '%s') and (t.\"%s\"::text !~ '%s') ) limit 1 ", columnName, tableName, columnName, integer_rg_exp, columnName, double_rg_exp, columnName, negative_double_rg_exp)); } if (anomalyRS.next()) { String anomalyValue = anomalyRS.getString(columnName); failure_geocode_count++; JSONObject _jsonObject = new JSONObject(); _jsonObject.put("columnName", columnName); _jsonObject.put("failureReason", "column is not " + target_type + " (value:" + anomalyValue + ") "); failureRecords.put(_jsonObject); } else { try { if ("Integer".equals(target_type)) { stmt.executeUpdate(String.format( "ALTER TABLE \"%s\" ALTER COLUMN \"%s\" TYPE integer USING \"%s\"::INTEGER;", tableName, columnName, columnName)); JSONObject _jsonObject = new JSONObject(); _jsonObject.put("columnName", columnName); successRecords.put(_jsonObject); success_geocode_count++; } else if ("Double".equals(target_type)) { ResultSet anyDoubleRS = stmt.executeQuery(String.format( "select t.\"%s\" from \"%s\" t where ( t.\"%s\"::text ~ '%s' or t.\"%s\"::text ~ '%s' ) limit 1 ", columnName, tableName, columnName, double_rg_exp, columnName, negative_double_rg_exp)); if (anyDoubleRS.next()) { stmt.executeUpdate(String.format( "ALTER TABLE \"%s\" ALTER COLUMN \"%s\" TYPE double precision USING \"%s\"::double precision;", tableName, columnName, columnName)); JSONObject _jsonObject = new JSONObject(); _jsonObject.put("columnName", columnName); successRecords.put(_jsonObject); success_geocode_count++; } else { failure_geocode_count++; JSONObject _jsonObject = new JSONObject(); _jsonObject.put("columnName", columnName); _jsonObject.put("failureReason", "no double value found in this column to set its type to double"); failureRecords.put(_jsonObject); } } } catch (Exception e) { e.printStackTrace(); failure_geocode_count++; JSONObject _jsonObject = new JSONObject(); _jsonObject.put("columnName", columnName); _jsonObject.put("Exception Message", e.getMessage()); failureRecords.put(_jsonObject); } } System.out.println(i + " out of " + size + ", Column change processing wad done, column name:" + columnName); } } finally { if (stmt != null && stmt.getConnection() != null) stmt.close(); } responseJSONObj.put("success_count", success_geocode_count); responseJSONObj.put("failure_count", failure_geocode_count); responseJSONObj.put("success_records", successRecords); responseJSONObj.put("failure_records", failureRecords); } Element root = new Element(Jeeves.Elem.ROOT); // Element success = new Element("success"); // success.setText("OK"); // root.addContent(results); // root.addContent(success); if (BooleanUtils.isTrue(this.jsonOutput)) { XMLOutputter xx = new XMLOutputter(); // String jsonContent = org.json.XML.toJSONObject( // xx.outputString(root)).toString(); Element jsonElement = new Element("jsonElement"); jsonElement.setText(responseJSONObj.toString(10)); return jsonElement; } else return root; }
From source file:com.flexive.core.storage.GenericDivisionImporter.java
/** * Import data from a zip archive to a database table * * @param stmt statement to use * @param zip zip archive containing the zip entry * @param ze zip entry within the archive * @param xpath xpath containing the entries to import * @param table name of the table * @param executeInsertPhase execute the insert phase? * @param executeUpdatePhase execute the update phase? * @param updateColumns columns that should be set to <code>null</code> in a first pass (insert) * and updated to the provided values in a second pass (update), * columns that should be used in the where clause have to be prefixed * with "KEY:", to assign a default value use the expression "columnname:default value", * if the default value is "@", it will be a negative counter starting at 0, decreasing. * If the default value starts with "%", it will be set to the column following the "%" * character in the first pass * @throws Exception on errors//from www. j a va2 s .c o m */ protected void importTable(Statement stmt, final ZipFile zip, final ZipEntry ze, final String xpath, final String table, final boolean executeInsertPhase, final boolean executeUpdatePhase, final String... updateColumns) throws Exception { //analyze the table final ResultSet rs = stmt.executeQuery("SELECT * FROM " + table + " WHERE 1=2"); StringBuilder sbInsert = new StringBuilder(500); StringBuilder sbUpdate = updateColumns.length > 0 ? new StringBuilder(500) : null; if (rs == null) throw new IllegalArgumentException("Can not analyze table [" + table + "]!"); sbInsert.append("INSERT INTO ").append(table).append(" ("); final ResultSetMetaData md = rs.getMetaData(); final Map<String, ColumnInfo> updateClauseColumns = updateColumns.length > 0 ? new HashMap<String, ColumnInfo>(md.getColumnCount()) : null; final Map<String, ColumnInfo> updateSetColumns = updateColumns.length > 0 ? new LinkedHashMap<String, ColumnInfo>(md.getColumnCount()) : null; final Map<String, String> presetColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null; //preset to a referenced column (%column syntax) final Map<String, String> presetRefColumns = updateColumns.length > 0 ? new HashMap<String, String>(10) : null; final Map<String, Integer> counters = updateColumns.length > 0 ? new HashMap<String, Integer>(10) : null; final Map<String, ColumnInfo> insertColumns = new HashMap<String, ColumnInfo>( md.getColumnCount() + (counters != null ? counters.size() : 0)); int insertIndex = 1; int updateSetIndex = 1; int updateClauseIndex = 1; boolean first = true; for (int i = 0; i < md.getColumnCount(); i++) { final String currCol = md.getColumnName(i + 1).toLowerCase(); if (updateColumns.length > 0) { boolean abort = false; for (String col : updateColumns) { if (col.indexOf(':') > 0 && !col.startsWith("KEY:")) { String value = col.substring(col.indexOf(':') + 1); col = col.substring(0, col.indexOf(':')); if ("@".equals(value)) { if (currCol.equalsIgnoreCase(col)) { counters.put(col, 0); insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); sbInsert.append(',').append(currCol); } } else if (value.startsWith("%")) { if (currCol.equalsIgnoreCase(col)) { presetRefColumns.put(col, value.substring(1)); insertColumns.put(col, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); sbInsert.append(',').append(currCol); // System.out.println("==> adding presetRefColumn "+col+" with value of "+value.substring(1)); } } else if (!presetColumns.containsKey(col)) presetColumns.put(col, value); } if (currCol.equalsIgnoreCase(col)) { abort = true; updateSetColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), updateSetIndex++)); break; } } if (abort) continue; } if (first) { first = false; } else sbInsert.append(','); sbInsert.append(currCol); insertColumns.put(currCol, new ColumnInfo(md.getColumnType(i + 1), insertIndex++)); } if (updateColumns.length > 0 && executeUpdatePhase) { sbUpdate.append("UPDATE ").append(table).append(" SET "); int counter = 0; for (String updateColumn : updateSetColumns.keySet()) { if (counter++ > 0) sbUpdate.append(','); sbUpdate.append(updateColumn).append("=?"); } sbUpdate.append(" WHERE "); boolean hasKeyColumn = false; for (String col : updateColumns) { if (!col.startsWith("KEY:")) continue; hasKeyColumn = true; String keyCol = col.substring(4); for (int i = 0; i < md.getColumnCount(); i++) { if (!md.getColumnName(i + 1).equalsIgnoreCase(keyCol)) continue; updateClauseColumns.put(keyCol, new ColumnInfo(md.getColumnType(i + 1), updateClauseIndex++)); sbUpdate.append(keyCol).append("=? AND "); break; } } if (!hasKeyColumn) throw new IllegalArgumentException("Update columns require a KEY!"); sbUpdate.delete(sbUpdate.length() - 5, sbUpdate.length()); //remove trailing " AND " //"shift" clause indices for (String col : updateClauseColumns.keySet()) { GenericDivisionImporter.ColumnInfo ci = updateClauseColumns.get(col); ci.index += (updateSetIndex - 1); } } if (presetColumns != null) { for (String key : presetColumns.keySet()) sbInsert.append(',').append(key); } sbInsert.append(")VALUES("); for (int i = 0; i < insertColumns.size(); i++) { if (i > 0) sbInsert.append(','); sbInsert.append('?'); } if (presetColumns != null) { for (String key : presetColumns.keySet()) sbInsert.append(',').append(presetColumns.get(key)); } sbInsert.append(')'); if (DBG) { LOG.info("Insert statement:\n" + sbInsert.toString()); if (updateColumns.length > 0) LOG.info("Update statement:\n" + sbUpdate.toString()); } //build a map containing all nodes that require attributes //this allows for matching simple xpath queries like "flatstorages/storage[@name='FX_FLAT_STORAGE']/data" final Map<String, List<String>> queryAttributes = new HashMap<String, List<String>>(5); for (String pElem : xpath.split("/")) { if (!(pElem.indexOf('@') > 0 && pElem.indexOf('[') > 0)) continue; List<String> att = new ArrayList<String>(5); for (String pAtt : pElem.split("@")) { if (!(pAtt.indexOf('=') > 0)) continue; att.add(pAtt.substring(0, pAtt.indexOf('='))); } queryAttributes.put(pElem.substring(0, pElem.indexOf('[')), att); } final PreparedStatement psInsert = stmt.getConnection().prepareStatement(sbInsert.toString()); final PreparedStatement psUpdate = updateColumns.length > 0 && executeUpdatePhase ? stmt.getConnection().prepareStatement(sbUpdate.toString()) : null; try { final SAXParser parser = SAXParserFactory.newInstance().newSAXParser(); final DefaultHandler handler = new DefaultHandler() { private String currentElement = null; private Map<String, String> data = new HashMap<String, String>(10); private StringBuilder sbData = new StringBuilder(10000); boolean inTag = false; boolean inElement = false; int counter; List<String> path = new ArrayList<String>(10); StringBuilder currPath = new StringBuilder(100); boolean insertMode = true; /** * {@inheritDoc} */ @Override public void startDocument() throws SAXException { counter = 0; inTag = false; inElement = false; path.clear(); currPath.setLength(0); sbData.setLength(0); data.clear(); currentElement = null; } /** * {@inheritDoc} */ @Override public void processingInstruction(String target, String data) throws SAXException { if (target != null && target.startsWith("fx_")) { if (target.equals("fx_mode")) insertMode = "insert".equals(data); } else super.processingInstruction(target, data); } /** * {@inheritDoc} */ @Override public void endDocument() throws SAXException { if (insertMode) LOG.info("Imported [" + counter + "] entries into [" + table + "] for xpath [" + xpath + "]"); else LOG.info("Updated [" + counter + "] entries in [" + table + "] for xpath [" + xpath + "]"); } /** * {@inheritDoc} */ @Override public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException { pushPath(qName, attributes); if (currPath.toString().equals(xpath)) { inTag = true; data.clear(); for (int i = 0; i < attributes.getLength(); i++) { String name = attributes.getLocalName(i); if (StringUtils.isEmpty(name)) name = attributes.getQName(i); data.put(name, attributes.getValue(i)); } } else { currentElement = qName; } inElement = true; sbData.setLength(0); } /** * Push a path element from the stack * * @param qName element name to push * @param att attributes */ private void pushPath(String qName, Attributes att) { if (att.getLength() > 0 && queryAttributes.containsKey(qName)) { String curr = qName + "["; boolean first = true; final List<String> attList = queryAttributes.get(qName); for (int i = 0; i < att.getLength(); i++) { if (!attList.contains(att.getQName(i))) continue; if (first) first = false; else curr += ','; curr += "@" + att.getQName(i) + "='" + att.getValue(i) + "'"; } curr += ']'; path.add(curr); } else path.add(qName); buildPath(); } /** * Pop the top path element from the stack */ private void popPath() { path.remove(path.size() - 1); buildPath(); } /** * Rebuild the current path */ private synchronized void buildPath() { currPath.setLength(0); for (String s : path) currPath.append(s).append('/'); if (currPath.length() > 1) currPath.delete(currPath.length() - 1, currPath.length()); // System.out.println("currPath: " + currPath); } /** * {@inheritDoc} */ @Override public void endElement(String uri, String localName, String qName) throws SAXException { if (currPath.toString().equals(xpath)) { if (DBG) LOG.info("Insert [" + xpath + "]: [" + data + "]"); inTag = false; try { if (insertMode) { if (executeInsertPhase) { processColumnSet(insertColumns, psInsert); counter += psInsert.executeUpdate(); } } else { if (executeUpdatePhase) { if (processColumnSet(updateSetColumns, psUpdate)) { processColumnSet(updateClauseColumns, psUpdate); counter += psUpdate.executeUpdate(); } } } } catch (SQLException e) { throw new SAXException(e); } catch (ParseException e) { throw new SAXException(e); } } else { if (inTag) { data.put(currentElement, sbData.toString()); } currentElement = null; } popPath(); inElement = false; sbData.setLength(0); } /** * Process a column set * * @param columns the columns to process * @param ps prepared statement to use * @return if data other than <code>null</code> has been set * @throws SQLException on errors * @throws ParseException on date/time conversion errors */ private boolean processColumnSet(Map<String, ColumnInfo> columns, PreparedStatement ps) throws SQLException, ParseException { boolean dataSet = false; for (String col : columns.keySet()) { ColumnInfo ci = columns.get(col); String value = data.get(col); if (insertMode && counters != null && counters.get(col) != null) { final int newVal = counters.get(col) - 1; value = String.valueOf(newVal); counters.put(col, newVal); // System.out.println("new value for " + col + ": " + newVal); } if (insertMode && presetRefColumns != null && presetRefColumns.get(col) != null) { value = data.get(presetRefColumns.get(col)); // System.out.println("Set presetRefColumn for "+col+" to ["+value+"] from column ["+presetRefColumns.get(col)+"]"); } if (value == null) ps.setNull(ci.index, ci.columnType); else { dataSet = true; switch (ci.columnType) { case Types.BIGINT: case Types.NUMERIC: if (DBG) LOG.info("BigInt " + ci.index + "->" + new BigDecimal(value)); ps.setBigDecimal(ci.index, new BigDecimal(value)); break; case java.sql.Types.DOUBLE: if (DBG) LOG.info("Double " + ci.index + "->" + Double.parseDouble(value)); ps.setDouble(ci.index, Double.parseDouble(value)); break; case java.sql.Types.FLOAT: case java.sql.Types.REAL: if (DBG) LOG.info("Float " + ci.index + "->" + Float.parseFloat(value)); ps.setFloat(ci.index, Float.parseFloat(value)); break; case java.sql.Types.TIMESTAMP: case java.sql.Types.DATE: if (DBG) LOG.info("Timestamp/Date " + ci.index + "->" + FxFormatUtils.getDateTimeFormat().parse(value)); ps.setTimestamp(ci.index, new Timestamp(FxFormatUtils.getDateTimeFormat().parse(value).getTime())); break; case Types.TINYINT: case Types.SMALLINT: if (DBG) LOG.info("Integer " + ci.index + "->" + Integer.valueOf(value)); ps.setInt(ci.index, Integer.valueOf(value)); break; case Types.INTEGER: case Types.DECIMAL: try { if (DBG) LOG.info("Long " + ci.index + "->" + Long.valueOf(value)); ps.setLong(ci.index, Long.valueOf(value)); } catch (NumberFormatException e) { //Fallback (temporary) for H2 if the reported long is a big decimal (tree...) ps.setBigDecimal(ci.index, new BigDecimal(value)); } break; case Types.BIT: case Types.CHAR: case Types.BOOLEAN: if (DBG) LOG.info("Boolean " + ci.index + "->" + value); if ("1".equals(value) || "true".equals(value)) ps.setBoolean(ci.index, true); else ps.setBoolean(ci.index, false); break; case Types.LONGVARBINARY: case Types.VARBINARY: case Types.BLOB: case Types.BINARY: ZipEntry bin = zip.getEntry(value); if (bin == null) { LOG.error("Failed to lookup binary [" + value + "]!"); ps.setNull(ci.index, ci.columnType); break; } try { ps.setBinaryStream(ci.index, zip.getInputStream(bin), (int) bin.getSize()); } catch (IOException e) { LOG.error("IOException importing binary [" + value + "]: " + e.getMessage(), e); } break; case Types.CLOB: case Types.LONGVARCHAR: case Types.VARCHAR: case SQL_LONGNVARCHAR: case SQL_NCHAR: case SQL_NCLOB: case SQL_NVARCHAR: if (DBG) LOG.info("String " + ci.index + "->" + value); ps.setString(ci.index, value); break; default: LOG.warn("Unhandled type [" + ci.columnType + "] for column [" + col + "]"); } } } return dataSet; } /** * {@inheritDoc} */ @Override public void characters(char[] ch, int start, int length) throws SAXException { if (inElement) sbData.append(ch, start, length); } }; handler.processingInstruction("fx_mode", "insert"); parser.parse(zip.getInputStream(ze), handler); if (updateColumns.length > 0 && executeUpdatePhase) { handler.processingInstruction("fx_mode", "update"); parser.parse(zip.getInputStream(ze), handler); } } finally { Database.closeObjects(GenericDivisionImporter.class, psInsert, psUpdate); } }