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

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;
}
 

@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" );
	}
}
 

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 boolean getMoreResults(int current) throws SQLException {
	for (Statement stmt : statements) {
		if (stmt.getMoreResults(current)) {
			return true;
		}
	}
	return false;
}
 

@Override
public boolean getMoreResults() throws SQLException {
	for (Statement stmt : statements) {
		if (stmt.getMoreResults()) {
			return true;
		}
	}
	return false;
}
 

/**
 * @throws Exception If failed.
 */
@org.junit.Test
public void testExecuteQueryMultipleOnlyDml() throws Exception {
    conn.setSchema(null);

    Statement stmt0 = conn.createStatement();

    int stmtCnt = 10;

    StringBuilder sql = new StringBuilder("drop table if exists test; create table test(ID int primary key, NAME varchar(20)); ");

    for (int i = 0; i < stmtCnt; ++i)
        sql.append("insert into test (ID, NAME) values (" + i + ", 'name_" + i + "'); ");

    assertFalse(stmt0.execute(sql.toString()));

    // DROP TABLE statement
    assertNull(stmt0.getResultSet());
    assertEquals(0, stmt0.getUpdateCount());

    stmt0.getMoreResults();

    // CREATE TABLE statement
    assertNull(stmt0.getResultSet());
    assertEquals(0, stmt0.getUpdateCount());

    for (int i = 0; i < stmtCnt; ++i) {
        assertTrue(stmt0.getMoreResults());

        assertNull(stmt0.getResultSet());
        assertEquals(1, stmt0.getUpdateCount());
    }

    assertFalse(stmt0.getMoreResults());
}
 

/**
 * <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 ResultSetWrapper getNextResultSet(Statement stmt) throws SQLException {
  // Making this method tolerant of bad JDBC drivers
  try {
    if (stmt.getConnection().getMetaData().supportsMultipleResultSets()) {
      // Crazy Standard JDBC way of determining if there are more results
      if (!((!stmt.getMoreResults()) && (stmt.getUpdateCount() == -1))) {
        ResultSet rs = stmt.getResultSet();
        return rs != null ? new ResultSetWrapper(rs, configuration) : null;
      }
    }
  } catch (Exception e) {
    // Intentionally ignored.
  }
  return null;
}
 

@Override
public final boolean getMoreResults() throws SQLException {
    boolean result = false;
    for (Statement each : getRoutedStatements()) {
        result = each.getMoreResults();
    }
    return result;
}
 

@Test
public void testExecuteBatchReadOnly() throws SQLException, NoSuchFieldException,
    SecurityException, IllegalArgumentException, IllegalAccessException {
  for (int testRun = 0; testRun < 2; testRun++) {
    final int numberOfPartitions = 6;
    BatchClient batchClient = mock(BatchClient.class);
    BatchReadOnlyTransaction tx = mock(BatchReadOnlyTransaction.class);
    List<Partition> partitions = new ArrayList<>(numberOfPartitions);
    for (int i = 0; i < numberOfPartitions; i++)
      partitions.add(mock(Partition.class));
    when(tx.partitionQuery(any(), any())).then(new Returns(partitions));
    when(batchClient.batchReadOnlyTransaction(TimestampBound.strong())).then(new Returns(tx));
    Field field = CloudSpannerTransaction.class.getDeclaredField("batchClient");
    field.setAccessible(true);
    field.set(connection.getTransaction(), batchClient);
    connection.setBatchReadOnly(true);
    Statement statement;
    if (testRun % 2 == 0) {
      statement = connection.createStatement();
      assertTrue(statement.execute(SELECT_ALL_FROM_FOO));
    } else {
      PreparedStatement ps = connection.prepareStatement(SELECT_ALL_FROM_FOO);
      assertTrue(ps.execute());
      statement = ps;
    }
    List<ResultSet> resultSets = new ArrayList<>();
    do {
      resultSets.add(statement.getResultSet());
    } while (statement.getMoreResults());
    assertEquals(numberOfPartitions, resultSets.size());
  }
}
 

private ResultSetWrapper getNextResultSet(Statement stmt) throws SQLException {
  // Making this method tolerant of bad JDBC drivers
  try {
    if (stmt.getConnection().getMetaData().supportsMultipleResultSets()) {
      // Crazy Standard JDBC way of determining if there are more results
      if (!((!stmt.getMoreResults()) && (stmt.getUpdateCount() == -1))) {
        ResultSet rs = stmt.getResultSet();
        return rs != null ? new ResultSetWrapper(rs, configuration) : null;
      }
    }
  } catch (Exception e) {
    // Intentionally ignored.
  }
  return null;
}
 

/**
 * {@inheritDoc}
 */
@Override
public RowSet getNextResultSet(int cursorId, byte otherResultSetBehaviour,
    ByteBuffer token) throws GFXDException {
  ResultSet rs = null;
  try {
    StatementHolder stmtHolder = getStatementForResultSet(token, cursorId,
        "getNextResultSet");
    Statement stmt = stmtHolder.getStatement();
    if (stmt == null) {
      return null;
    }
    final boolean moreResults;
    if (otherResultSetBehaviour == 0) {
      moreResults = stmt.getMoreResults();
    }
    else {
      final int current;
      switch (otherResultSetBehaviour) {
        case gfxdConstants.NEXTRS_CLOSE_CURRENT_RESULT:
          current = EmbedStatement.CLOSE_CURRENT_RESULT;
          break;
        case gfxdConstants.NEXTRS_KEEP_CURRENT_RESULT:
          current = EmbedStatement.KEEP_CURRENT_RESULT;
          break;
        default:
          current = EmbedStatement.CLOSE_ALL_RESULTS;
          break;
      }
      moreResults = stmt.getMoreResults(current);
    }
    if (moreResults) {
      ConnectionHolder connHolder = stmtHolder.getConnectionHolder();
      rs = stmt.getResultSet();
      return getRowSet(stmt, stmtHolder, rs, INVALID_ID,
          connHolder.getConnectionId(), stmtHolder.getStatementAttrs(), 0,
          false, false, 0, connHolder, true, null /*already set*/);
    }
    else {
      return null;
    }
  } catch (Throwable t) {
    cleanupResultSet(rs);
    throw gfxdException(t);
  }
}
 

/**
 * Tests that <code>ResultSet.isClosed()</code> returns the
 * correct value in different situations.
 *
 * @throws SQLException	Thrown if some unexpected error happens
 */
public void testIsClosed() throws SQLException{
    
    Statement stmt = createStatement();
    
    // simple open/read/close test
    ResultSet rs = stmt.executeQuery("values(1)");
    assertFalse("rs should be open", rs.isClosed());
    while (rs.next());
    assertFalse("rs should be open", rs.isClosed());
    rs.close();
    assertTrue("rs should be closed", rs.isClosed());
    
    // execute and re-execute statement
    rs = stmt.executeQuery("values(1)");
    assertFalse("rs should be open", rs.isClosed());
    ResultSet rs2 = stmt.executeQuery("values(1)");
    assertTrue("rs should be closed", rs.isClosed());
    assertFalse("rs2 should be open", rs2.isClosed());
    
    // re-execute another statement on the same connection
    Statement stmt2 = createStatement();
    rs = stmt2.executeQuery("values(1)");
    assertFalse("rs2 should be open" ,rs2.isClosed());
    assertFalse("rs should be open", rs.isClosed());
    
    // retrieve multiple result sets
    stmt.execute("create procedure retrieve_result_sets() " +
            "parameter style java language java external name " +
            "'org.apache.derbyTesting.functionTests.tests." +
            "jdbc4.ResultSetTest.threeResultSets' " +
            "dynamic result sets 3 reads sql data");
    stmt.execute("call retrieve_result_sets()");
    ResultSet[] rss = new ResultSet[3];
    int count = 0;
    do {
        rss[count] = stmt.getResultSet();
        assertFalse("rss[" + count + "] should be open", rss[count].isClosed());
        
        if (count > 0) {
            assertTrue("rss[" + (count-1) + "] should be closed", rss[count-1].isClosed());
        }
        ++count;
    } while (stmt.getMoreResults());
    assertEquals("expected three result sets", 3, count);
    stmt.execute("drop procedure retrieve_result_sets");
    
    // close statement
    rs = stmt2.executeQuery("values(1)");
    stmt2.close();
    assertTrue("rs should be closed", rs.isClosed());
    
    // close connection
    Connection conn2 = openDefaultConnection();
    stmt2 = conn2.createStatement();
    rs = stmt2.executeQuery("values(1)");
    conn2.close();
    assertTrue("rs should be closed", rs.isClosed());
    
    stmt.close();
    stmt2.close();
}
 

/**
 * Tests that <code>ResultSet.isClosed()</code> returns the
 * correct value in different situations.
 *
 * @throws SQLException	Thrown if some unexpected error happens
 */
public void testIsClosed() throws SQLException{
    
    Statement stmt = createStatement();
    
    // simple open/read/close test
    ResultSet rs = stmt.executeQuery("values(1)");
    assertFalse("rs should be open", rs.isClosed());
    while (rs.next());
    assertFalse("rs should be open", rs.isClosed());
    rs.close();
    assertTrue("rs should be closed", rs.isClosed());
    
    // execute and re-execute statement
    rs = stmt.executeQuery("values(1)");
    assertFalse("rs should be open", rs.isClosed());
    ResultSet rs2 = stmt.executeQuery("values(1)");
    assertTrue("rs should be closed", rs.isClosed());
    assertFalse("rs2 should be open", rs2.isClosed());
    
    // re-execute another statement on the same connection
    Statement stmt2 = createStatement();
    rs = stmt2.executeQuery("values(1)");
    assertFalse("rs2 should be open" ,rs2.isClosed());
    assertFalse("rs should be open", rs.isClosed());
    
    // retrieve multiple result sets
    stmt.execute("create procedure retrieve_result_sets() " +
            "parameter style java language java external name " +
            "'com.splicemachine.dbTesting.functionTests.tests." +
            "jdbc4.ResultSetTest.threeResultSets' " +
            "dynamic result sets 3 reads sql data");
    stmt.execute("call retrieve_result_sets()");
    ResultSet[] rss = new ResultSet[3];
    int count = 0;
    do {
        rss[count] = stmt.getResultSet();
        assertFalse("rss[" + count + "] should be open", rss[count].isClosed());
        
        if (count > 0) {
            assertTrue("rss[" + (count-1) + "] should be closed", rss[count-1].isClosed());
        }
        ++count;
    } while (stmt.getMoreResults());
    assertEquals("expected three result sets", 3, count);
    stmt.execute("drop procedure retrieve_result_sets");
    
    // close statement
    rs = stmt2.executeQuery("values(1)");
    stmt2.close();
    assertTrue("rs should be closed", rs.isClosed());
    
    // close connection
    Connection conn2 = openDefaultConnection();
    stmt2 = conn2.createStatement();
    rs = stmt2.executeQuery("values(1)");
    conn2.close();
    assertTrue("rs should be closed", rs.isClosed());
    
    stmt.close();
    stmt2.close();
}
 

/**
 * {@inheritDoc}
 */
@Override
public RowSet getNextResultSet(int cursorId, byte otherResultSetBehaviour,
    ByteBuffer token) throws GFXDException {
  ResultSet rs = null;
  try {
    StatementHolder stmtHolder = getStatementForResultSet(token, cursorId,
        "getNextResultSet");
    Statement stmt = stmtHolder.getStatement();
    if (stmt == null) {
      return null;
    }
    final boolean moreResults;
    if (otherResultSetBehaviour == 0) {
      moreResults = stmt.getMoreResults();
    }
    else {
      final int current;
      switch (otherResultSetBehaviour) {
        case gfxdConstants.NEXTRS_CLOSE_CURRENT_RESULT:
          current = EmbedStatement.CLOSE_CURRENT_RESULT;
          break;
        case gfxdConstants.NEXTRS_KEEP_CURRENT_RESULT:
          current = EmbedStatement.KEEP_CURRENT_RESULT;
          break;
        default:
          current = EmbedStatement.CLOSE_ALL_RESULTS;
          break;
      }
      moreResults = stmt.getMoreResults(current);
    }
    if (moreResults) {
      ConnectionHolder connHolder = stmtHolder.getConnectionHolder();
      rs = stmt.getResultSet();
      return getRowSet(stmt, stmtHolder, rs, INVALID_ID,
          connHolder.getConnectionId(), stmtHolder.getStatementAttrs(), 0,
          false, false, 0, connHolder, true, null /*already set*/);
    }
    else {
      return null;
    }
  } catch (Throwable t) {
    cleanupResultSet(rs);
    throw gfxdException(t);
  }
}
 

public List<String> getAccounts() throws SQLException {
    List<String> accounts = new ArrayList<>();

    String selectAccountsStatementString = "SELECT " + COLUMN_MESSAGE_ACCOUNT + ", " + COLUMN_MESSAGE_ROWID + " FROM (SELECT " + COLUMN_MESSAGE_ACCOUNT + ", " + COLUMN_MESSAGE_ROWID + " FROM " + MESSAGE_TABLE + " ORDER BY " + COLUMN_MESSAGE_ROWID + " DESC LIMIT 500) ORDER BY " + COLUMN_MESSAGE_ROWID + " ASC";
    Statement selectAccounts = getDatabaseManager().getChatDatabaseConnection().createStatement();

    boolean isResultSet = selectAccounts.execute(selectAccountsStatementString);

    while(true) {
        if(isResultSet) {
            ResultSet resultSet = selectAccounts.getResultSet();
            while(resultSet.next()) {
                String accountLogin = resultSet.getString(COLUMN_MESSAGE_ACCOUNT);
                String account;
                boolean useContains = false;

                try {
                    account = accountLogin.split(":")[1];
                }catch (Exception ex){
                    useContains = true;
                    try {
                        account = accountLogin.split(":")[0];
                    }catch (Exception exc){
                        account = accountLogin;
                    }
                }

                if (!StringUtils.isEmpty(account) && !accounts.contains(account.toLowerCase())){
                    accounts.add(account.toLowerCase());
                }
            }
            resultSet.close();
        } else {
            if(selectAccounts.getUpdateCount() == -1) {
                break;
            }
        }
        isResultSet = selectAccounts.getMoreResults();
    }
    selectAccounts.close();

    return accounts;
}
 

@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");
}
 

private void executeAsStatement() throws cfmRunTimeException {
	Connection dataConnection = null;
	Statement stmt = null;
	ResultSet rs = null;

	try {
		dataConnection = setupDataConnection();
		stmt = dataConnection.createStatement();

		if (dataConnection instanceof WrappedConnection)
			((WrappedConnection) dataConnection).setLastQuery("STATEMENT: " + queryString);

		boolean oracleDriver = stmt.getClass().getName().equals("oracle.jdbc.driver.T4CStatement");

		if (!oracleDriver) {
			// the Oracle JDBC driver has a bug: stmt.getUpdateCount() always
			// returns a positive number, instead of -1 when there are no more
			// results; this bug puts the following code into an infinite loop

			// If retrieveGenKeys is true then force it to false if the driver
			// doesn't support the retrieval of generated keys.
			if (retrieveGenKeys)
				retrieveGenKeys = supportsGetGeneratedKeys(dataConnection);

			if (retrieveGenKeys)
				stmt.execute(queryString, Statement.RETURN_GENERATED_KEYS);
			else
				stmt.execute(queryString);

			// With MySQL we need to retrieve the generated keys before
			// stmt.getMoreResults() and stmt.getUpdateCount() are called.
			ResultSet rsGK = initGeneratedKeys(dataConnection, stmt);
			recordsUpdated = 0;

			do { // there may be multiple result sets and/or update counts
				rs = stmt.getResultSet();
				
				if (rs != null && rs != rsGK) { // return the first result set (if any)
					resultSet = true;
					populate(rs, maxRows);
					rs.close();
					break;
				}
				
				recordsUpdated += stmt.getUpdateCount();
			} while (stmt.getMoreResults() || ( (stmt.getUpdateCount()) >= 0));
			
		} else {
			// this is the workaround for the Oracle bug mentioned above
			boolean hasResultSet;
			if (retrieveGenKeys)
				hasResultSet = stmt.execute(queryString, Statement.RETURN_GENERATED_KEYS);
			else
				hasResultSet = stmt.execute(queryString);
			if (hasResultSet) { // Oracle only returns a single result
				rs = stmt.getResultSet();
				if (rs != null) {
					resultSet = true;
					populate(rs, maxRows);
				}
			}
			initGeneratedKeys(dataConnection, stmt);
		}
	} catch (SQLException e) {
		throw new cfmRunTimeException(catchDataFactory.databaseException(dataSourceName, "sql.execution", new String[] { com.naryx.tagfusion.cfm.tag.tagUtils.trimError(e.getMessage()) }, queryString, e));
	} finally {
		queryRun = true;
		closeConnections(dataConnection, stmt, rs);
	}
}
 

/**
 * 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();
    }
}