Java Code Examples for java.sql.Statement#getUpdateCount()

/** Test remote Statement insert. */
@Test void testInsert() throws Exception {
  final Connection connection = DriverManager.getConnection(
      "jdbc:avatica:remote:factory="
          + LocalServiceModifiableFactory.class.getName());
  assertThat(connection.isClosed(), is(false));
  Statement statement = connection.createStatement();
  assertThat(statement.isClosed(), is(false));

  String sql = "insert into \"foo\".\"bar\" values (1, 1, 'second', 2, 2)";
  boolean status = statement.execute(sql);
  assertThat(status, is(false));
  ResultSet resultSet = statement.getResultSet();
  assertThat(resultSet, nullValue());
  int updateCount = statement.getUpdateCount();
  assertThat(updateCount, is(1));
  connection.close();
}
 

public List<String> getAllExistingDevices() throws SQLException {
    List<String> devices = new ArrayList<>();
    String selectDevicesStatementString = "SELECT * FROM " + TABLE_DEVICES;
    Statement selectDevicesStatement = getServerDatabaseConnection().createStatement();

    boolean isResultSet = selectDevicesStatement.execute(selectDevicesStatementString);

    while(true) {
        if(isResultSet) {
            ResultSet resultSet = selectDevicesStatement.getResultSet();
            while(resultSet.next()) {
                String deviceId = resultSet.getString(COLUMN_DEVICE_ID);
                devices.add(deviceId);
            }
            resultSet.close();
        } else {
            if(selectDevicesStatement.getUpdateCount() == -1) {
                break;
            }
        }
        isResultSet = selectDevicesStatement.getMoreResults();
    }
    selectDevicesStatement.close();

    return devices;
}
 

private ResultSetWrapper getFirstResultSet(Statement stmt) throws SQLException {
  ResultSet rs = stmt.getResultSet();
  //HSQLDB2.1特殊情况处理
  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();
    } else {
      if (stmt.getUpdateCount() == -1) {
        // no more results. Must be no resultset
        break;
      }
    }
  }
  return rs != null ? new ResultSetWrapper(rs, configuration) : null;
}
 

private ResultSetWrapper getFirstResultSet(Statement stmt) throws SQLException {
  ResultSet rs = stmt.getResultSet();
  //HSQLDB2.1特殊情况处理
  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();
    } else {
      if (stmt.getUpdateCount() == -1) {
        // no more results. Must be no resultset
        break;
      }
    }
  }
  return rs != null ? new ResultSetWrapper(rs, configuration) : null;
}
 

@Override
public ResultSet execute(Statement statement, String sql) {
	sqlStatementLogger.logStatement( sql );
	try {
		final ResultSet rs;
		try {
			jdbcExecuteStatementStart();
			if ( !statement.execute( sql ) ) {
				while ( !statement.getMoreResults() && statement.getUpdateCount() != -1 ) {
					// do nothing until we hit the resultset
				}
			}
			rs = statement.getResultSet();
		}
		finally {
			jdbcExecuteStatementEnd();
		}
		postExtract( rs, statement );
		return rs;
	}
	catch (SQLException e) {
		throw sqlExceptionHelper.convert( e, "could not execute statement" );
	}
}
 

@Override
public int executeStatement(String statementStr, int resultFlags) throws SQLException {
	if (resultFlags == DatabaseConnection.DEFAULT_RESULT_FLAGS) {
		resultFlags = ResultSet.TYPE_FORWARD_ONLY;
	}
	Statement statement = connection.createStatement(resultFlags, ResultSet.CONCUR_READ_ONLY);
	statement.execute(statementStr);
	return statement.getUpdateCount();
}
 

/**
 * <p> Dump all results produced by the execution of a statement, including
 * update counts, resultsets and generated keys if the statement has been
 * executed using one of the {@link Statement#execute()} that directs the
 * driver to return generated keys. </p>
 *
 * @param statement
 *    {@link Statement} object used for execution
 */
public void dumpAll( Statement statement )
   throws SQLException
{
   int uc = statement.getUpdateCount();
   boolean isResult = uc == -1;

   do
   {
      if( isResult )
      {
         ResultSet res = statement.getResultSet();

         if( res != null )
         {
            dump( res );
         }
      }
      else
      {
         System.out.println( "update count: " + uc );
      }

      // maybe the update created keys
      dumpKeys( statement );
   }
   while( ( isResult = statement.getMoreResults() ) || ( uc = statement.getUpdateCount() ) != -1 );
}
 

private int accumulate() throws SQLException {
    long result = 0;
    boolean hasResult = false;
    for (Statement each : getRoutedStatements()) {
        int updateCount = each.getUpdateCount();
        if (updateCount > -1) {
            hasResult = true;
        }
        result += updateCount;
    }
    if (result > Integer.MAX_VALUE) {
        result = Integer.MAX_VALUE;
    }
    return hasResult ? Long.valueOf(result).intValue() : -1;
}
 

/**
 * Executes the given sql string.
 * @param sqlStr A String containing sql to execute.
 * @return The result of the execution, either a ResultSet or update count (Integer)
 * @throws DBException Thrown if anything goes wrong during execution.
 */
public Object executeSql(String sqlStr) throws DBException {
  try {
    Statement stmt = getConnection().createStatement();
    boolean result = stmt.execute(sqlStr);
    if (result) { 
      return stmt.getResultSet();
    } else {
      return stmt.getUpdateCount();
    }
  } catch (SQLException e) {
    String s = "Problem executing:" + sqlStr;
    throw new DBException(s, e);
  }
}
 

/**
 * Execute the given SQL string checking that the execution should return at
 * least one result i.e. either at least one row in {@link ResultSet} for
 * queries or at least one affected row for updates. The optional
 * <code>usePrepStmt</code> specifies whether to use {@link PreparedStatement}
 * or to use {@link Statement} for SQL execution.
 */
public static int sqlExecuteVerify(String jdbcSQL, Boolean usePrepStmt)
    throws SQLException {
  setupConnection();
  getLogger().info(
      "For user '" + jdbcConn.getMetaData().getUserName() + '['
          + currentUserName + "]' executing SQL with verification: "
          + jdbcSQL);
  Statement stmt;
  boolean resultType;
  if (usePrepStmt.booleanValue()) {
    stmt = jdbcConn.prepareStatement(jdbcSQL);
    resultType = ((PreparedStatement)stmt).execute();
  }
  else {
    stmt = jdbcConn.createStatement();
    resultType = stmt.execute(jdbcSQL);
  }
  int count = 0;
  try {
    if (resultType) {
      ResultSet rs = stmt.getResultSet();
      while (rs.next()) {
        ++count;
      }
    }
    else {
      count = stmt.getUpdateCount();
    }
  } finally {
    stmt.close();
  }
  getLogger().info(
      "sqlExecuteVerify: got the number of changes/results as: " + count);
  // Assert.assertTrue("Expected at least one result/change", count > 0);
  return count;
}
 

@Override
public int executeStatement(String statementStr, int resultFlags) throws SQLException {
	if (resultFlags == DatabaseConnection.DEFAULT_RESULT_FLAGS) {
		resultFlags = ResultSet.TYPE_FORWARD_ONLY;
	}
	Statement statement = connection.createStatement(resultFlags, ResultSet.CONCUR_READ_ONLY);
	statement.execute(statementStr);
	return statement.getUpdateCount();
}
 

/**
 * Execute the given SQL string checking that the execution should return at
 * least one result i.e. either at least one row in {@link ResultSet} for
 * queries or at least one affected row for updates. The optional
 * <code>usePrepStmt</code> specifies whether to use {@link PreparedStatement}
 * or to use {@link Statement} for SQL execution.
 */
public static int sqlExecuteVerify(String jdbcSQL, Boolean usePrepStmt)
    throws SQLException {
  setupConnection();
  getLogger().info(
      "For user '" + jdbcConn.getMetaData().getUserName() + '['
          + currentUserName + "]' executing SQL with verification: "
          + jdbcSQL);
  Statement stmt;
  boolean resultType;
  if (usePrepStmt.booleanValue()) {
    stmt = jdbcConn.prepareStatement(jdbcSQL);
    resultType = ((PreparedStatement)stmt).execute();
  }
  else {
    stmt = jdbcConn.createStatement();
    resultType = stmt.execute(jdbcSQL);
  }
  int count = 0;
  try {
    if (resultType) {
      ResultSet rs = stmt.getResultSet();
      while (rs.next()) {
        ++count;
      }
    }
    else {
      count = stmt.getUpdateCount();
    }
  } finally {
    stmt.close();
  }
  getLogger().info(
      "sqlExecuteVerify: got the number of changes/results as: " + count);
  // Assert.assertTrue("Expected at least one result/change", count > 0);
  return count;
}
 

void unlockCa() throws CaMgmtException {
  final String sql = "DELETE FROM SYSTEM_EVENT WHERE NAME='LOCK'";
  Statement stmt = null;
  try {
    stmt = createStatement();
    stmt.execute(sql);
    if (stmt.getUpdateCount() == 0) {
      throw new CaMgmtException("could not unlock CA");
    }
  } catch (SQLException ex) {
    throw new CaMgmtException(datasource.translate(sql, ex));
  } finally {
    datasource.releaseResources(stmt, null);
  }
}
 

@Test
public void testPreparedStatementConfig() throws SQLException {
    Statement statement = swConnection.createStatement();
    statement.cancel();
    statement.getUpdateCount();
    statement.setFetchDirection(1);
    statement.getFetchDirection();
    statement.getResultSetConcurrency();
    statement.getResultSetType();
    statement.isClosed();
    statement.setPoolable(false);
    statement.isPoolable();
    statement.getWarnings();
    statement.clearWarnings();
    statement.setCursorName("test");
    statement.setMaxFieldSize(11);
    statement.getMaxFieldSize();
    statement.setMaxRows(10);
    statement.getMaxRows();
    statement.setEscapeProcessing(true);
    statement.setFetchSize(1);
    statement.getFetchSize();
    statement.setQueryTimeout(1);
    statement.getQueryTimeout();
    Connection connection = statement.getConnection();

    statement.execute("SELECT * FROM test");
    statement.getMoreResults();
    statement.getMoreResults(1);
    statement.getResultSetHoldability();
    statement.getResultSet();

    statement.close();
    verify(mysqlStatement).getUpdateCount();
    verify(mysqlStatement).getMoreResults();
    verify(mysqlStatement).setFetchDirection(anyInt());
    verify(mysqlStatement).getFetchDirection();
    verify(mysqlStatement).getResultSetType();
    verify(mysqlStatement).isClosed();
    verify(mysqlStatement).setPoolable(anyBoolean());
    verify(mysqlStatement).getWarnings();
    verify(mysqlStatement).clearWarnings();
    verify(mysqlStatement).setCursorName(anyString());
    verify(mysqlStatement).setMaxFieldSize(anyInt());
    verify(mysqlStatement).getMaxFieldSize();
    verify(mysqlStatement).setMaxRows(anyInt());
    verify(mysqlStatement).getMaxRows();
    verify(mysqlStatement).setEscapeProcessing(anyBoolean());
    verify(mysqlStatement).getResultSetConcurrency();
    verify(mysqlStatement).getResultSetConcurrency();
    verify(mysqlStatement).getResultSetType();
    verify(mysqlStatement).getMoreResults(anyInt());
    verify(mysqlStatement).setFetchSize(anyInt());
    verify(mysqlStatement).getFetchSize();
    verify(mysqlStatement).getQueryTimeout();
    verify(mysqlStatement).setQueryTimeout(anyInt());
    verify(mysqlStatement).getResultSet();
    assertThat(connection, CoreMatchers.<Connection>is(swConnection));

    TraceSegment traceSegment = segmentStorage.getTraceSegments().get(0);
    List<AbstractTracingSpan> spans = SegmentHelper.getSpans(traceSegment);
    assertThat(spans.size(), is(1));
    assertDBSpan(spans.get(0), "Mysql/JDBI/Statement/execute", "SELECT * FROM test");
}
 

protected boolean test(Statement aStatement) {

        try {
            try {

                //execute the SQL
                aStatement.execute(getSql());
            } catch (SQLException s) {
                throw new Exception("Expected a ResultSet, but got the error: "
                                    + s.getMessage());
            }

            //check that update count != -1
            if (aStatement.getUpdateCount() != -1) {
                throw new Exception(
                    "Expected a ResultSet, but got an update count of "
                    + aStatement.getUpdateCount());
            }

            //iterate over the ResultSet
            ResultSet    results  = aStatement.getResultSet();
            StringBuffer printVal = new StringBuffer();

            while (results.next()) {
                for (int j = 0; j < results.getMetaData().getColumnCount();
                        j++) {
                    if (j != 0) {
                        printVal.append(',');
                    }

                    printVal.append(results.getString(j + 1));
                }

                printVal.append(LS);
            }

            throw new Exception(printVal.toString());
        } catch (Exception x) {
            message = x.toString();

            return false;
        }
    }
 

private void processSQL (Connection conn, PrintWriter writer, 
        OutputType outputType, CommitMode commitMode, 
        ProcessStatement stmt) throws SQLException {
    boolean useSubArrays = (outputType == OutputType.OBJECT);
    AkibanAppender appender = AkibanAppender.of(writer);
    int nresults = 0;
    commitMode.begin(conn);
    outputType.begin(appender);
    
    Statement s = stmt.processStatement(nresults);
    while (s != null) {
        if(useSubArrays) {
            beginResultSetArray(appender, nresults == 0, nresults);
        }
        JDBCResultSet results = (JDBCResultSet) s.getResultSet();
        int updateCount = s.getUpdateCount();
        
        if (results != null && !results.isClosed()) {
            collectResults(results, appender, options);
            // Force close the result set here because if you execute "SELECT...;INSERT..." 
            // the call to s.getResultSet() returns the (now empty) SELECT result set
            // giving bad results
            results.close();
        } else {
            appender.append("\n{\"update_count\":");
            appender.append(updateCount);
            results = (JDBCResultSet) s.getGeneratedKeys();
            if (results != null) {
                appender.append(",\n\"returning\":[");
                collectResults(results, appender, options);
                appender.append("]\n");
            }
            appender.append("}\n");
        }
        if(useSubArrays) {
            endResultSetArray(appender);
        }
        ++nresults;
        s = stmt.processStatement(nresults);
    }
    
    commitMode.end(conn);
    outputType.end(appender);

}
 

/**
 * Called when the batch is to be executed via a "go".
 * 
 * @return true if it worked, false otherwise.
 */
private boolean insertGo() {
    
    /*
     * If there is no connection, then just print the
     * word "go" to the screen.
     */
    if (conn == null) {
        
        session.out.println(batchTerminator);
        return true;
    }
    
    /*
     * If our batch is empty then nothing to do.
     */
    if (insertBatch.length() == 0) {
        
        return true;
    }
    
    /*
     * Otherwise, attempt to execute.
     */
    try {
        
        Statement statement = conn.createStatement();
        statement.execute(insertBatch.toString());
        statement.getUpdateCount();
        statement.close();
        
        conn.commit();
    }
    catch (SQLException e) {
        
        SQLTools.printException(session, e);
        
        insertBatch.setLength(0);
        return false;
    }
    
    insertBatch.setLength(0);
    return true;
}
 

public Result execStatement( String rawsql, IRowMeta params, Object[] data ) throws HopDatabaseException {
  Result result = new Result();

  // Replace existing code with a class that removes comments from the raw
  // SQL.
  // The SqlCommentScrubber respects single-quoted strings, so if a
  // double-dash or a multiline comment appears
  // in a single-quoted string, it will be treated as a string instead of
  // comments.
  String sql = databaseMeta.getIDatabase().createSqlScriptParser().removeComments( rawsql ).trim();
  try {
    boolean resultSet;
    int count;
    if ( params != null ) {
      PreparedStatement prep_stmt = connection.prepareStatement( databaseMeta.stripCR( sql ) );
      setValues( params, data, prep_stmt ); // set the parameters!
      resultSet = prep_stmt.execute();
      count = prep_stmt.getUpdateCount();
      prep_stmt.close();
    } else {
      String sqlStripped = databaseMeta.stripCR( sql );
      // log.logDetailed("Executing SQL Statement: ["+sqlStripped+"]");
      Statement stmt = connection.createStatement();
      resultSet = stmt.execute( sqlStripped );
      count = stmt.getUpdateCount();
      stmt.close();
    }
    String upperSql = sql.toUpperCase();
    if ( !resultSet ) {
      // if the result is a resultset, we don't do anything with it!
      // You should have called something else!
      // log.logDetailed("What to do with ResultSet??? (count="+count+")");
      if ( count > 0 ) {
        if ( upperSql.startsWith( "INSERT" ) ) {
          result.setNrLinesOutput( count );
        } else if ( upperSql.startsWith( "UPDATE" ) ) {
          result.setNrLinesUpdated( count );
        } else if ( upperSql.startsWith( "DELETE" ) ) {
          result.setNrLinesDeleted( count );
        }
      }
    }

    // See if a cache needs to be cleared...
    if ( upperSql.startsWith( "ALTER TABLE" )
      || upperSql.startsWith( "DROP TABLE" ) || upperSql.startsWith( "CREATE TABLE" ) ) {
      DbCache.getInstance().clear( databaseMeta.getName() );
    }
  } catch ( SQLException ex ) {
    throw new HopDatabaseException( "Couldn't execute SQL: " + sql + Const.CR, ex );
  } catch ( Exception e ) {
    throw new HopDatabaseException( "Unexpected error executing SQL: " + Const.CR, e );
  }

  return result;
}
 

public void testTxnUpdateBehaviorForLocking() throws Exception {

    final CountDownLatch latch = new CountDownLatch(1);
    final CountDownLatch barrier = new CountDownLatch(2);
    final java.sql.Connection childConn = getConnection();
    Connection conn = getConnection();
    conn.setTransactionIsolation(getIsolationLevel());
    Statement stmt = conn.createStatement();
    stmt.execute("Create table t1 (c1 int not null primary key, "
        + "qty int not null, c3 int not null, exchange varchar(20), "
        + "c4 int not null, constraint C3_Unique unique (c3), "
        + "constraint exc_ch check (exchange in ('nasdaq', 'nye', 'amex')), "
        + "constraint qty_ck check (qty >= 5))"+getSuffix());

    // stmt.execute("create index idx on t1(c4)");
    stmt.execute("insert into t1 values(1, 10, 1, 'nye', 4), "
        + "(2, 20, 2, 'amex', 4)");

    conn.commit();

    MemHeapScanController.setWaitObjectAfterFirstQualifyForTEST(latch);
    MemHeapScanController.setWaitBarrierBeforeFirstScanForTEST(barrier);
    MemHeapScanController.setWaitForLatchForTEST(0);

    try {
      final Exception[] ex = new Exception[] { null };
      Runnable r = new Runnable() {

        @Override
        public void run() {
          try {
            childConn.setTransactionIsolation(getIsolationLevel());
            Statement cstmt = childConn.createStatement();
            MemHeapScanController.setWaitForLatchForTEST(3);
            cstmt.execute("update t1 set c4 = 40 where c4 = 4");
            updateCnt = cstmt.getUpdateCount();
          } catch (Exception e) {
            ex[0] = e;
          } finally {
            MemHeapScanController.setWaitForLatchForTEST(0);
          }
        }
      };

      Thread t = new Thread(r);
      t.start();

      // set stop after first qualify
      // start second txn and fire update such that to change the columns which
      // will no more satisfy the above condition
      Connection conn2 = getConnection();
      conn2.setTransactionIsolation(getIsolationLevel());
      Statement stmt2 = conn2.createStatement();
      stmt2.execute("update t1 set c4 = 10");
      conn2.commit();
      latch.countDown();
      // move forward with the first txn and it should not update the row
      t.join();
      assertEquals(0, updateCnt);
      // expect a conflict in other thread since it takes SH lock before qualify
      // that will block commit, and then it will try to upgrade to EX_SH which
      // will conflict with this thread
      assertNotNull(ex[0]);
      if (!(ex[0] instanceof SQLException)) {
        throw ex[0];
      }
      if (!"X0Z02".equals(((SQLException)ex[0]).getSQLState())) {
        throw ex[0];
      }
    } finally {

      MemHeapScanController.setWaitBarrierBeforeFirstScanForTEST(null);
      MemHeapScanController.setWaitObjectAfterFirstQualifyForTEST(null);
    }
    childConn.commit();
  }
 

/**
 * Take the received Statement--on which a query has been
 * executed--and fetch all rows of all result sets (if any)
 * returned from execution.  The rows themselves are
 * discarded.  This is useful when we expect there to be
 * an error when processing the results but do not know
 * (or care) at what point the error occurs.
 *
 * @param st     An already-executed statement from which
 *               we get the result set to process (if there is one).
 * @param haveRS Whether or not the the statement's
 *               first result is a result set (as opposed to an
 *               update count).
 */
private static void fetchAndDiscardAllResults(Statement st,
                                              boolean haveRS) throws SQLException {
    ResultSet rs = null;
    while (haveRS || (st.getUpdateCount() != -1)) {
        // If we have a result set, iterate through all
        // of the rows.
        if (haveRS)
            JDBC.assertDrainResults(st.getResultSet(), -1);
        haveRS = st.getMoreResults();
    }
}