List of usage examples for java.sql Statement getUpdateCount
int getUpdateCount() throws SQLException;
ResultSet object or there are no more results, -1 is returned. From source file:herddb.cli.HerdDBCLI.java
private static ExecuteStatementResult reallyExecuteStatement(final Statement statement, boolean resultSet, boolean verbose, boolean getResults, boolean prettyPrint) throws SQLException { if (resultSet) { try (ResultSet rs = statement.getResultSet()) { List<Map<String, Object>> results = new ArrayList<>(); TextTableBuilder tb = new TextTableBuilder(); ResultSetMetaData md = rs.getMetaData(); List<String> columns = new ArrayList<>(); int ccount = md.getColumnCount(); for (int i = 1; i <= ccount; i++) { columns.add(md.getColumnName(i)); }//from www. j a va 2 s . c om if (!getResults) { if (prettyPrint) { tb.addIntestation(columns); } else { System.out.println(columns.stream().collect(Collectors.joining(";"))); } } while (rs.next()) { List<String> values = new ArrayList<>(); for (int i = 1; i <= ccount; i++) { String value = rs.getString(i); if (value == null) { value = "<NULL>"; } values.add(value); } if (getResults) { Map<String, Object> row = new LinkedHashMap<>(); // Preserving order int i = 0; for (String col : columns) { row.put(col, values.get(i++)); } results.add(row); } else { if (prettyPrint) { tb.addRow(values); } else { System.out.println(values.stream().collect(Collectors.joining(";"))); } } } if (getResults) { return new ExecuteStatementResult(results); } if (prettyPrint) { System.out.println("\n" + tb.toString()); } return null; } } else { int updateCount = statement.getUpdateCount(); if (verbose) { System.out.println("UPDATE COUNT: " + updateCount); } return new ExecuteStatementResult(updateCount >= 0 ? updateCount : 0); } }
From source file:lib.JdbcTemplate.java
@Override public int[] batchUpdate(final String... sql) throws DataAccessException { Assert.notEmpty(sql, "SQL array must not be empty"); if (logger.isDebugEnabled()) { logger.debug("Executing SQL batch update of " + sql.length + " statements"); }/*from w w w .java 2 s. co m*/ class BatchUpdateStatementCallback implements StatementCallback<int[]>, SqlProvider { private String currSql; @Override public int[] doInStatement(Statement stmt) throws SQLException, DataAccessException { int[] rowsAffected = new int[sql.length]; if (JdbcUtils.supportsBatchUpdates(stmt.getConnection())) { for (String sqlStmt : sql) { this.currSql = appendSql(this.currSql, sqlStmt); stmt.addBatch(sqlStmt); } try { rowsAffected = stmt.executeBatch(); } catch (BatchUpdateException ex) { String batchExceptionSql = null; for (int i = 0; i < ex.getUpdateCounts().length; i++) { if (ex.getUpdateCounts()[i] == Statement.EXECUTE_FAILED) { batchExceptionSql = appendSql(batchExceptionSql, sql[i]); } } if (StringUtils.hasLength(batchExceptionSql)) { this.currSql = batchExceptionSql; } throw ex; } } else { for (int i = 0; i < sql.length; i++) { this.currSql = sql[i]; if (!stmt.execute(sql[i])) { rowsAffected[i] = stmt.getUpdateCount(); } else { throw new InvalidDataAccessApiUsageException("Invalid batch SQL statement: " + sql[i]); } } } return rowsAffected; } private String appendSql(String sql, String statement) { return (StringUtils.isEmpty(sql) ? statement : sql + "; " + statement); } @Override public String getSql() { return this.currSql; } } return execute(new BatchUpdateStatementCallback()); }
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/* w w w .j av a 2s . co m*/ 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.andromda.dbtest.H2.java
/** * @param url/*ww w . ja va 2s . c o m*/ * @param sqlFile */ public static void initDb(String url, String sqlFile) //throws SQLException, ClassNotFoundException { long now = System.currentTimeMillis(); try { Class.forName("org.h2.Driver"); // IF there are any errors in SQL Files, the connection throws an Exception List<String> sqls = new ArrayList<String>(); if (StringUtils.isNotBlank(sqlFile)) { // List of files separated by ; will be appended to URL and executed String[] files = StringUtils.split(sqlFile, ';'); // Files contain SQL statements separated by ; if (files.length > 0) { for (int i = 0; i < files.length; i++) { LOGGER.info("Parsing SQL file " + files[i]); sqls.addAll(fileToStrings(files[i], ';')); /*if (i==0) { url += ";INIT="; } else { // Separator for multiple files in the same init command url += "\\;"; } url += "RUNSCRIPT FROM '" + files[i] + "'";*/ } } } // Starts H2, allowing TCP connections also, to jdbc:h2:tcp://localhost/${sql.database} // Must use id/password with embedded persisted mode Connection conn = DriverManager.getConnection(url, "sa", "sa"); LOGGER.info("Connected to DB " + url + "\r in " + (System.currentTimeMillis() - now) + " ms"); long now1 = System.currentTimeMillis(); Statement stat = conn.createStatement(); int stmtCount = 0; int successfulCount = 0; for (String sql : sqls) { if (StringUtils.isNotBlank(sql)) { stmtCount++; boolean resultSet = false; try { resultSet = stat.execute(sql); if (resultSet) { ResultSet rset = stat.getResultSet(); LOGGER.info(sql + ";\r" + "ResultSet: " + rset + "\r"); try { rset.close(); } catch (Exception e) { LOGGER.error(e); } } else if (stat.getUpdateCount() > 0) { LOGGER.info(sql + ";\r" + "Updated rows: " + stat.getUpdateCount() + "\r"); } else if (StringUtils.containsIgnoreCase(sql, "INSERT INTO ") || StringUtils.containsIgnoreCase(sql, "UPDATE ") || StringUtils.containsIgnoreCase(sql, "DELETE ")) { LOGGER.info(sql + ";\rExecuted Successfully, no update or result\r"); } else { LOGGER.info(sql + ";\rExecuted Successfully\r"); } successfulCount++; } catch (SQLException e) { LOGGER.error(e); } } } LOGGER.info("Executed " + successfulCount + " out of " + stmtCount + " sql statements in " + (System.currentTimeMillis() - now1) + " ms, total time = " + (System.currentTimeMillis() - now) + " ms"); // Keep in-memory DB connection open after executing SQL statements int i = 1; while (i > 0) { // There is no Daemon option when starting DB through connection, just have to go into an infinite loop } } catch (IOException e) { LOGGER.error(e); } catch (ClassNotFoundException e) { LOGGER.error(e); } catch (SQLException e) { LOGGER.error(e); } }
From source file:org.apache.airavata.registry.tool.DBMigrator.java
private static void executeSQL(String sql, Connection conn) throws Exception { if ("".equals(sql.trim())) { return;/*from ww w . j a v a 2 s. co m*/ } Statement statement = null; try { logger.debug("SQL : " + sql); boolean ret; int updateCount = 0, updateCountTotal = 0; statement = conn.createStatement(); ret = statement.execute(sql); updateCount = statement.getUpdateCount(); do { if (!ret) { if (updateCount != -1) { updateCountTotal += updateCount; } } ret = statement.getMoreResults(); if (ret) { updateCount = statement.getUpdateCount(); } } while (ret); logger.debug(sql + " : " + updateCountTotal + " rows affected"); SQLWarning warning = conn.getWarnings(); while (warning != null) { logger.warn(warning + " sql warning"); warning = warning.getNextWarning(); } conn.clearWarnings(); } catch (SQLException e) { if (e.getSQLState().equals("X0Y32")) { logger.info("Table Already Exists", e); } else { throw new Exception("Error occurred while executing : " + sql, e); } } finally { if (statement != null) { try { statement.close(); } catch (SQLException e) { logger.error("Error occurred while closing result set.", e); } } } }
From source file:org.apache.camel.component.jdbc.JdbcProducer.java
/** * Execute sql of exchange and set results on output *///from www . j a v a 2 s . co m public void process(Exchange exchange) throws Exception { String sql = exchange.getIn().getBody(String.class); Connection conn = null; Statement stmt = null; ResultSet rs = null; try { conn = dataSource.getConnection(); stmt = conn.createStatement(); if (parameters != null && !parameters.isEmpty()) { IntrospectionSupport.setProperties(stmt, parameters); } if (LOG.isDebugEnabled()) { LOG.debug("Executing JDBC statement: " + sql); } if (stmt.execute(sql)) { rs = stmt.getResultSet(); setResultSet(exchange, rs); } else { int updateCount = stmt.getUpdateCount(); exchange.getOut().setHeader(JdbcConstants.JDBC_UPDATE_COUNT, updateCount); } } finally { try { if (rs != null) { rs.close(); } if (stmt != null) { stmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException e) { LOG.warn("Error closing JDBC resource: " + e, e); } } // populate headers exchange.getOut().getHeaders().putAll(exchange.getIn().getHeaders()); }
From source file:org.apache.cayenne.migration.Migrator.java
int executeSqlWithUpdateCount(String sql) throws SQLException { Statement st = null; JdbcEventLogger logger = node.getJdbcEventLogger(); try {//from w w w . j a v a 2 s. c o m logger.log(sql); st = getConnection().createStatement(); try { st.execute(sql); } catch (SQLException e) { getConnection().rollback(); throw new RuntimeException("SQL statement failed \"" + sql + "\": " + e.getMessage(), e); } return st.getUpdateCount(); } finally { closeStatement(st); } }
From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java
private void doTestSelectAll(String tableName, int maxRows, int fetchSize) throws Exception { boolean isPartitionTable = tableName.equals(partitionedTableName); Statement stmt = con.createStatement(); if (maxRows >= 0) { stmt.setMaxRows(maxRows);/*from w ww. jav a 2 s .co m*/ } if (fetchSize > 0) { stmt.setFetchSize(fetchSize); assertEquals(fetchSize, stmt.getFetchSize()); } // JDBC says that 0 means return all, which is the default int expectedMaxRows = maxRows < 1 ? 0 : maxRows; assertNotNull("Statement is null", stmt); assertEquals("Statement max rows not as expected", expectedMaxRows, stmt.getMaxRows()); assertFalse("Statement should not be closed", stmt.isClosed()); ResultSet res; // run some queries res = stmt.executeQuery("select * from " + tableName); assertNotNull("ResultSet is null", res); assertTrue("getResultSet() not returning expected ResultSet", res == stmt.getResultSet()); assertEquals("get update count not as expected", 0, stmt.getUpdateCount()); int i = 0; ResultSetMetaData meta = res.getMetaData(); int expectedColCount = isPartitionTable ? 3 : 2; assertEquals("Unexpected column count", expectedColCount, meta.getColumnCount()); String colQualifier = ((tableName != null) && !tableName.isEmpty()) ? tableName.toLowerCase() + "." : ""; boolean moreRow = res.next(); while (moreRow) { try { i++; assertEquals(res.getInt(1), res.getInt(colQualifier + "under_col")); assertEquals(res.getString(1), res.getString(colQualifier + "under_col")); assertEquals(res.getString(2), res.getString(colQualifier + "value")); if (isPartitionTable) { assertEquals(res.getString(3), partitionedColumnValue); assertEquals(res.getString(3), res.getString(colQualifier + partitionedColumnName)); } assertFalse("Last result value was not null", res.wasNull()); assertNull("No warnings should be found on ResultSet", res.getWarnings()); res.clearWarnings(); // verifying that method is supported // System.out.println(res.getString(1) + " " + res.getString(2)); assertEquals("getInt and getString don't align for the same result value", String.valueOf(res.getInt(1)), res.getString(1)); assertEquals("Unexpected result found", "val_" + res.getString(1), res.getString(2)); moreRow = res.next(); } catch (SQLException e) { System.out.println(e.toString()); e.printStackTrace(); throw new Exception(e.toString()); } } // supposed to get 500 rows if maxRows isn't set int expectedRowCount = maxRows > 0 ? maxRows : 500; assertEquals("Incorrect number of rows returned", expectedRowCount, i); // should have no more rows assertEquals(false, moreRow); assertNull("No warnings should be found on statement", stmt.getWarnings()); stmt.clearWarnings(); // verifying that method is supported assertNull("No warnings should be found on connection", con.getWarnings()); con.clearWarnings(); // verifying that method is supported stmt.close(); assertTrue("Statement should be closed", stmt.isClosed()); }
From source file:org.apache.hive.jdbc.TestJdbcDriver2.java
private void doTestSelectAll(String tableName, int maxRows, int fetchSize) throws Exception { boolean isPartitionTable = tableName.equals(partitionedTableName); Statement stmt = con.createStatement(); if (maxRows >= 0) { stmt.setMaxRows(maxRows);/*from w w w. j a v a 2 s .c om*/ } if (fetchSize > 0) { stmt.setFetchSize(fetchSize); assertEquals(fetchSize, stmt.getFetchSize()); } // JDBC says that 0 means return all, which is the default int expectedMaxRows = maxRows < 1 ? 0 : maxRows; assertNotNull("Statement is null", stmt); assertEquals("Statement max rows not as expected", expectedMaxRows, stmt.getMaxRows()); assertFalse("Statement should not be closed", stmt.isClosed()); ResultSet res; // run some queries res = stmt.executeQuery("select * from " + tableName); assertNotNull("ResultSet is null", res); assertTrue("getResultSet() not returning expected ResultSet", res == stmt.getResultSet()); assertEquals("get update count not as expected", -1, stmt.getUpdateCount()); int i = 0; ResultSetMetaData meta = res.getMetaData(); int expectedColCount = isPartitionTable ? 3 : 2; assertEquals("Unexpected column count", expectedColCount, meta.getColumnCount()); boolean moreRow = res.next(); while (moreRow) { try { i++; assertEquals(res.getInt(1), res.getInt(tableName + ".under_col")); assertEquals(res.getInt(1), res.getInt("under_col")); assertEquals(res.getString(1), res.getString(tableName + ".under_col")); assertEquals(res.getString(1), res.getString("under_col")); assertEquals(res.getString(2), res.getString(tableName + ".value")); assertEquals(res.getString(2), res.getString("value")); if (isPartitionTable) { assertEquals(res.getString(3), partitionedColumnValue); assertEquals(res.getString(3), res.getString(partitionedColumnName)); assertEquals(res.getString(3), res.getString(tableName + "." + partitionedColumnName)); } assertFalse("Last result value was not null", res.wasNull()); assertNull("No warnings should be found on ResultSet", res.getWarnings()); res.clearWarnings(); // verifying that method is supported // System.out.println(res.getString(1) + " " + res.getString(2)); assertEquals("getInt and getString don't align for the same result value", String.valueOf(res.getInt(1)), res.getString(1)); assertEquals("Unexpected result found", "val_" + res.getString(1), res.getString(2)); moreRow = res.next(); } catch (SQLException e) { System.out.println(e.toString()); e.printStackTrace(); throw new Exception(e.toString()); } } // supposed to get 500 rows if maxRows isn't set int expectedRowCount = maxRows > 0 ? maxRows : 500; assertEquals("Incorrect number of rows returned", expectedRowCount, i); // should have no more rows assertEquals(false, moreRow); assertNull("No warnings should be found on statement", stmt.getWarnings()); stmt.clearWarnings(); // verifying that method is supported assertNull("No warnings should be found on connection", con.getWarnings()); con.clearWarnings(); // verifying that method is supported stmt.close(); assertTrue("Statement should be closed", stmt.isClosed()); }
From source file:org.apache.ibatis.executor.resultset.FastResultSetHandler.java
public List<Object> handleResultSets(Statement stmt) throws SQLException { final List<Object> multipleResults = new ArrayList<Object>(); final List<ResultMap> resultMaps = mappedStatement.getResultMaps(); int resultMapCount = resultMaps.size(); int resultSetCount = 0; ResultSet rs = stmt.getResultSet(); while (rs == null) { // move forward to get the first resultset in case the driver // doesn't return the resultset as the first result (HSQLDB 2.1) if (stmt.getMoreResults()) { rs = stmt.getResultSet();/* w w w . ja v a 2 s . com*/ } else { if (stmt.getUpdateCount() == -1) { // no more results. Must be no resultset break; } } } validateResultMapsCount(rs, resultMapCount); while (rs != null && resultMapCount > resultSetCount) { final ResultMap resultMap = resultMaps.get(resultSetCount); ResultColumnCache resultColumnCache = new ResultColumnCache(rs.getMetaData(), configuration); handleResultSet(rs, resultMap, multipleResults, resultColumnCache); rs = getNextResultSet(stmt); cleanUpAfterHandlingResultSet(); resultSetCount++; } return collapseSingleResultList(multipleResults); }