Java Code Examples for java.sql.PreparedStatement#getResultSet()

@Override
public boolean start() throws IOException {
    connection = source.createConnection(container);
    result = new Result();
    this.dieOnError = properties.dieOnError.getValue();
    try {
        if (source.usePreparedStatement()) {
            statement = connection.prepareStatement(source.getQuery());
            PreparedStatement pstmt = (PreparedStatement) statement;
            SnowflakePreparedStatementUtils.fillPreparedStatement(pstmt, properties.preparedStatementTable);
            pstmt.execute();
            rs = pstmt.getResultSet();
            pstmt.clearParameters();
        } else {
            statement = connection.createStatement();
            rs = statement.executeQuery(source.getQuery());
        }
    } catch (SQLException e) {
        if (dieOnError) {
            throw new IOException(e);
        }
        LOGGER.warn(I18N_MESSAGES.getMessage("error.queryExecution"), e);
        handleReject(e);
    }
    return advance();
}
 

/** Test that returns all result sets in one go. */
@Test void testLocalPreparedStatementFetch() throws Exception {
  Connection conn = makeConnection();
  assertThat(conn.isClosed(), is(false));
  String sql = "select * from \"foo\".\"bar\"";
  PreparedStatement preparedStatement = conn.prepareStatement(sql);
  assertThat(conn.isClosed(), is(false));
  boolean status = preparedStatement.execute();
  assertThat(status, is(true));
  ResultSet resultSet = preparedStatement.getResultSet();
  assertThat(resultSet, notNullValue());
  int count = 0;
  while (resultSet.next()) {
    assertThat(resultSet.getObject(1), notNullValue());
    count += 1;
  }
  assertThat(count, is(101));
}
 

@Test
public void testMultiRouterResult15() throws Exception {
	DataSource ds = (DataSource) context.getBean("zebraDS");
	Connection conn = null;
	try {
		conn = ds.getConnection();
		PreparedStatement stmt = conn
				.prepareStatement("select distinct score from test order by score asc limit ?,?");
		stmt.setInt(1, 16);
		stmt.setInt(2, 3);
		stmt.execute();
		ResultSet rs = stmt.getResultSet();
		while (rs.next()) {
			Assert.fail();
		}
		Assert.assertTrue(true);
	} catch (Exception e) {
		Assert.fail();
	} finally {
		if (conn != null) {
			conn.close();
		}
	}
}
 

@Test
public void testSingleRuleLimit() throws SQLException {
    DataSource ds = (DataSource) context.getBean("zebraDS");
    Connection conn = null;
    try {
        conn = ds.getConnection();
        PreparedStatement stmt = conn.prepareStatement("select * from test where id = 0 order by id limit ?, ?");
        stmt.setInt(1, 1);
        stmt.setInt(2, 3);
        stmt.execute();
        ResultSet rs = stmt.getResultSet();
        List<TestEntity> popResult = popResult(rs);
        Assert.assertEquals(0, popResult.size());
    } catch (Exception e) {
        Assert.fail();
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
}
 

@Test
public void testMultiRouterResult2() throws Exception {
    DataSource ds = (DataSource) context.getBean("zebraDS");
    Connection conn = null;
    try {
        conn = ds.getConnection();
        PreparedStatement stmt = conn.prepareStatement("select score from test where id!=1 order by score");
        stmt.execute();
        ResultSet rs = stmt.getResultSet();
        List<Integer> rows = new ArrayList<Integer>();
        while (rs.next()) {
            rows.add(rs.getInt("score"));
        }
        Assert.assertEquals(14, rows.size());
        List<Integer> expectedResult = Arrays.asList(new Integer[]{1,1,3,3,4,5,5,6,6,7,7,8,8,20});
        for (int i = 0; i < expectedResult.size(); i++) {
            Assert.assertEquals(expectedResult.get(i).intValue(), rows.get(i).intValue());
        }
    } catch (Exception e) {
        Assert.fail();
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
}
 

@Override
public void transactionMarker() throws Exception {
    StringBuilder sql = new StringBuilder("select * from employee where name like ?");
    for (int i = 0; i < 200; i++) {
        sql.append(" and name like ?");
    }
    PreparedStatement preparedStatement = connection.prepareStatement(sql.toString());
    try {
        for (int i = 1; i < 202; i++) {
            preparedStatement.setString(i, "john%");
        }
        preparedStatement.execute();
        ResultSet rs = preparedStatement.getResultSet();
        while (rs.next()) {
            rs.getString(1);
        }
    } finally {
        preparedStatement.close();
    }
}
 

@Override
public ResultSet execute(PreparedStatement statement) {
	// sql logged by StatementPreparerImpl
	try {
		final ResultSet rs;
		try {
			jdbcExecuteStatementStart();
			if ( !statement.execute() ) {
				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" );
	}
}
 

/**
 * Check if an app is already stored in the database
 * 
 * @param app
 *          the app to check
 * @return true if an app by that packagename and versioncode is already
 *         stored.
 * @throws SQLException
 */
public boolean isStored(AndroidApp app) throws SQLException {
	Connection c = manager.connect();
	PreparedStatement st = null;
	ResultSet res = null;

	try {
		st = c
				.prepareStatement("SELECT * FROM androidapps WHERE packagename = ? AND versioncode = ?");
		st.setString(1, app.getPackageName());
		st.setInt(2, app.getVersionCode());
		st.execute();
		res = st.getResultSet();
		return res.next();
	}
	finally {
		if (st != null)
			st.close();
		if (res != null) {
			res.close();
		}
		manager.disconnect(c);
	}
}
 

@Test
public void testMultiRouterResult18() throws Exception {
	DataSource ds = (DataSource) context.getBean("zebraDS");
	Connection conn = null;
	try {
		conn = ds.getConnection();
		PreparedStatement stmt = conn
				.prepareStatement("select distinct score from test where score in (?,?,?,?) ");
		stmt.setInt(1, 1);
		stmt.setInt(2, 10001);
		stmt.setInt(3, 10002);
		stmt.setInt(4, 10003);
		stmt.execute();
		ResultSet rs = stmt.getResultSet();
		rs.getMetaData();
	} catch (Exception e) {
		Assert.fail();
	} finally {
		if (conn != null) {
			conn.close();
		}
	}
}
 

@Test
public void testMultiRouterResult0() throws Exception {
    DataSource ds = (DataSource) context.getBean("zebraDS");
    Connection conn = null;
    try {
        conn = ds.getConnection();
        PreparedStatement stmt = conn.prepareStatement("select sum(score) score from test");
        stmt.execute();
        ResultSet rs = stmt.getResultSet();
        List<Long> rows = new ArrayList<Long>();
        while (rs.next()) {
            rows.add(rs.getLong("score"));
        }
        Assert.assertEquals(1, rows.size());
        Assert.assertEquals(89, rows.get(0).intValue());
    } catch (Exception e) {
        Assert.fail();
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
}
 

public Serializable executeAndExtract(PreparedStatement insert) throws SQLException {
	if ( !insert.execute() ) {
		while ( !insert.getMoreResults() && insert.getUpdateCount() != -1 ) {
			// do nothing until we hit the rsult set containing the generated id
		}
	}
	ResultSet rs = insert.getResultSet();
	try {
		return IdentifierGeneratorFactory.getGeneratedIdentity( rs, persister.getIdentifierType() );
	}
	finally {
		rs.close();
	}
}
 

@Test
public void testLimitOffset1() throws Exception {
	DataSource ds = (DataSource) context.getBean("zebraDS");
	Connection conn = null;
	try {
		conn = ds.getConnection();
		PreparedStatement stmt1 = conn.prepareStatement("select name from test where id in (0,1) limit 0, ?");
		stmt1.setInt(1, 3);
		stmt1.execute();
		ResultSet rs1 = stmt1.getResultSet();

		List<String> rows1 = new ArrayList<String>();
		while (rs1.next()) {
			rows1.add(rs1.getString("name"));
		}

		PreparedStatement stmt2 = conn.prepareStatement("select name from test where id in (0,1) limit ? offset 0");
		stmt2.setInt(1, 3);
		stmt2.execute();
		ResultSet rs2 = stmt2.getResultSet();

		List<String> rows2 = new ArrayList<String>();
		while (rs2.next()) {
			rows2.add(rs2.getString("name"));
		}

		Assert.assertEquals(rows1.size(), rows2.size());

		stmt1.close();
		stmt2.close();
	} catch (Exception e) {
		Assert.fail();
	} finally {
		if (conn != null) {
			conn.close();
		}
	}
}
 

@Test
public void testMultiRouterResult7() throws Exception {
    DataSource ds = (DataSource) context.getBean("zebraDS");
    Connection conn = null;
    try {
        conn = ds.getConnection();
        PreparedStatement stmt = conn
                .prepareStatement("select distinct score from test order by score asc limit ?,?");
        stmt.setInt(1, 1);
        stmt.setInt(2, 3);
        stmt.execute();
        ResultSet rs = stmt.getResultSet();
        List<Integer> rows = new ArrayList<Integer>();
        while (rs.next()) {
            rows.add(rs.getInt("score"));
        }
        Assert.assertEquals(3, rows.size());
        Assert.assertEquals(2, rows.get(0).intValue());
        Assert.assertEquals(3, rows.get(1).intValue());
        Assert.assertEquals(4, rows.get(2).intValue());
    } catch (Exception e) {
        Assert.fail();
    } finally {
        if (conn != null) {
            conn.close();
        }
    }
}
 

/**
 * Test functions of grounding a predicate and store
 * the resulting atoms to database table.
 */
@Test
public final void testGroundAndStoreAtom() throws Exception {
	MarkovLogicNetwork mln = new MarkovLogicNetwork();
	Predicate p = new Predicate(mln, "dummy", false);
	mln.registerPred(p);
	
	//Config.test.flushTestConfiguration();
	RDB db = RDB.getRDBbyConfig();
	db.resetSchema(Config.db_schema);
	Type type = new Type("xx");
	type.addConstant(1);
	type.addConstant(2);
	type.addConstant(3);
	type.storeConstantList(db);
	p.appendArgument(type);
	p.appendArgument(type);
	p.prepareDB(db);
	assertEquals(0, db.countTuples(p.getRelName()));
	ArrayList<Integer> list = new ArrayList<Integer>();
	list.add(2);
	list.add(3);
	Atom atom = new Atom(p, list, true);
	p.groundAndStoreAtom(atom);
	assertEquals(1, db.countTuples(p.getRelName()));
	list = new ArrayList<Integer>();
	list.add(3);
	list.add(-1);
	atom = new Atom(p, list, true);
	p.groundAndStoreAtom(atom);
	assertEquals(4, db.countTuples(p.getRelName()));
	p.closeFiles();
	
	list = new ArrayList<Integer>();
	list.add(-1);
	list.add(-1);
	atom = new Atom(p, list, true);
	p.groundAndStoreAtom(atom);
	assertEquals(6, db.countTuples(p.getRelName()));
	p.closeFiles();
	
	
	list = new ArrayList<Integer>();
	list.add(-1);
	list.add(-1);
	atom = new Atom(p, list, true);
	
	atom.type = Atom.AtomType.QUERY;
	p.groundAndStoreAtom(atom);
	assertEquals(6, db.countTuples(p.getRelName()));
	
	PreparedStatement ps = db.getPrepareStatement("SELECT COUNT(*) AS CT FROM " +
			p.getRelName() + " WHERE club = 1 OR club = 3");
	ps.execute();
	ResultSet rss = ps.getResultSet();
	rss.next();
	int rs = rss.getInt("CT");
	assertEquals(3, rs);
	
	//TODO: CHANGE FOR ``SAME''
	//test getPredicateByName
	// assertTrue(mln.getPredByName("same")!=null);
	
}
 

public ChatBase getChatByGroupID(String groupID) throws SQLException {
    String selectChatStatementString = "SELECT * FROM " + CHAT_TABLE + " WHERE " + COLUMN_CHAT_GROUP_ID + " = ?";
    PreparedStatement selectChatStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatStatementString);
    selectChatStatement.setString(1, groupID);

    ResultSet chatResultSet = selectChatStatement.executeQuery();

    if (!chatResultSet.isBeforeFirst()){
        chatResultSet.close();
        selectChatStatement.close();
        return null;
    }

    List<Handle> handles = new ArrayList<>();
    String selectChatHandleStatementString = "SELECT * FROM " + CHAT_HANDLES_TABLE + " WHERE " + COLUMN_CHAT_HANDLE_CHAT_ID + " = ?";
    PreparedStatement selectChatHandleStatement = getDatabaseManager().getChatDatabaseConnection().prepareStatement(selectChatHandleStatementString);
    selectChatHandleStatement.setLong(1, chatResultSet.getLong(COLUMN_CHAT_ROWID));

    boolean isResultSet = selectChatHandleStatement.execute();

    while(true) {
        if(isResultSet) {
            ResultSet resultSet = selectChatHandleStatement.getResultSet();
            while(resultSet.next()) {
                long resultInt = resultSet.getLong(COLUMN_CHAT_HANDLE_HANDLE_ID);
                handles.add(getHandleByRow(resultInt));
            }
            resultSet.close();
        } else {
            if(selectChatHandleStatement.getUpdateCount() == -1) {
                break;
            }
        }
        isResultSet = selectChatHandleStatement.getMoreResults();
    }

    ChatBase chat = buildChat(chatResultSet, handles);

    chatResultSet.close();
    selectChatStatement.close();
    selectChatHandleStatement.close();

    return chat;
}
 

public void testAssociatedParams() throws SQLException
{
    Statement stmt = createStatement();
    int i;
    println("Positive Statement: testing associated parameters");
    PreparedStatement checkps = prepareStatement(
        "select x from assocout order by x");
    PreparedStatement ps = prepareStatement(
        "insert into assoc values (?, 'hello')");
    for ( i = 10; i < 60; i++)
    {
        ps.setString(1, new Integer(i).toString());
        ps.executeUpdate();
    }
    ps.close();

    ps = prepareStatement(
        "insert into assocout select x from assoc where x like ?");
    ps.setString(1, "33%");
    ps.addBatch();
    ps.setString(1, "21%");
    ps.addBatch();
    ps.setString(1, "49%");
    ps.addBatch();

    assertBatchUpdateCounts(new int[] {1,1,1}, ps.executeBatch());
    ps.close();
    checkps.execute();
    ResultSet rs = checkps.getResultSet();
    JDBC.assertFullResultSet(
        rs, new String[][] {{"21"},{"33"},{"49"}}, true);

    stmt.executeUpdate("delete from assocout");

    ps = prepareStatement(
            "insert into assocout select x from assoc where x like ?");
    ps.setString(1, "3%");
    ps.addBatch(); // expectedCount 10: values 10-19
    ps.setString(1, "2%");
    ps.addBatch(); // values 20-29
    ps.setString(1, "1%");
    ps.addBatch(); // values 30-39

    // set up expected values for check
    String expectedStrArray[][] = new String[30][1];
    for (i=10 ; i < 40 ; i++)
    {
        expectedStrArray[i-10][0] = String.valueOf(i);
    }

    assertBatchUpdateCounts( new int[] {10,10,10}, ps.executeBatch());
    ps.close();
    checkps.execute();
    rs = checkps.getResultSet();
    JDBC.assertFullResultSet(rs, expectedStrArray, true);

    stmt.executeUpdate("delete from assocout");
    ps = prepareStatement(
        "insert into assocout select x from assoc where x like ?");
    ps.setString(1, "%");// values 10-59
    ps.addBatch();
    ps.setString(1, "666666");
    ps.addBatch();
    ps.setString(1, "%");// values 10-59
    ps.addBatch();

    // set up expected values for check
    String expectedStrArray2[][] = new String[100][1];
    int j = 0;
    for (i=10 ; i < 60 ; i++)
    {
        for (int twice = 0; twice < 2; twice++)
        {
            expectedStrArray2[j][0] = String.valueOf(i);
            j++;
        }
    }

    assertBatchUpdateCounts (new int[] {50,0,50}, ps.executeBatch());
    ps.close();
    checkps.execute();
    rs = checkps.getResultSet();
    JDBC.assertFullResultSet(rs, expectedStrArray2, true);
    checkps.close();
    stmt.close();
}
 

/**
 * Execute the query against the database
 * 
 * @param database
 *            open database
 * @param sql
 *            SQL statement
 * @param maxRows
 *            max rows
 * @return results
 * @throws SQLException
 *             upon SQL error
 */
private static SQLExecResult executeQuery(GeoPackage database, String sql,
		int maxRows) throws SQLException {

	SQLExecResult result = new SQLExecResult();

	if (!sql.equals(";")) {

		PreparedStatement statement = null;
		try {

			statement = database.getConnection().getConnection()
					.prepareStatement(sql);
			statement.setMaxRows(maxRows);

			result.setMaxRows(maxRows);

			boolean hasResultSet = statement.execute();

			if (hasResultSet) {

				ResultSet resultSet = statement.getResultSet();

				ResultSetMetaData metadata = resultSet.getMetaData();
				int numColumns = metadata.getColumnCount();

				int[] columnWidths = new int[numColumns];
				int[] columnTypes = new int[numColumns];

				for (int col = 1; col <= numColumns; col++) {
					result.addTable(metadata.getTableName(col));
					String columnName = metadata.getColumnName(col);
					result.addColumn(columnName);
					columnTypes[col - 1] = metadata.getColumnType(col);
					columnWidths[col - 1] = columnName.length();
				}

				while (resultSet.next()) {

					List<String> row = new ArrayList<>();
					result.addRow(row);
					for (int col = 1; col <= numColumns; col++) {

						String stringValue = resultSet.getString(col);

						if (stringValue != null) {

							switch (columnTypes[col - 1]) {
							case Types.BLOB:
								stringValue = BLOB_DISPLAY_VALUE;
								break;
							default:
								stringValue = stringValue.replaceAll(
										"\\s*[\\r\\n]+\\s*", " ");
							}

							int valueLength = stringValue.length();
							if (valueLength > columnWidths[col - 1]) {
								columnWidths[col - 1] = valueLength;
							}

						}

						row.add(stringValue);
					}

				}

				result.addColumnWidths(columnWidths);

			} else {

				int updateCount = statement.getUpdateCount();
				if (updateCount >= 0) {
					result.setUpdateCount(updateCount);
				}

			}

		} finally {
			SQLUtils.closeStatement(statement, sql);
		}
	}

	return result;
}
 

public static DUnitRun generateNewDUnitRun() throws SQLException, IOException {

    Properties gfvp = new Properties();
    gfvp
        .load(GemFireCacheImpl.class
            .getResourceAsStream("/com/gemstone/gemfire/internal/GemFireVersion.properties"));

    PreparedStatement ps = Database
        .prepareStatement("INSERT INTO dunit_run(user_name,path,sites,revision,branch,os_name,os_version,java_version,java_vm_version,java_vm_vendor) values(?,?,?,?,?,?,?,?,?,?);SELECT currval('dunit_run_id_seq');");
    String user_name = System.getProperty("user.name");
    String path = System.getProperty("user.dir");
    int sites = Integer.parseInt((String)TestConfig.getInstance()
        .getSystemProperties().get("dunitSites"));
    String revision = gfvp.getProperty("Source-Revision");
    String branch = gfvp.getProperty("Source-Repository");
    String os_name = System.getProperty("os.name");
    String os_version = System.getProperty("os.version");
    String java_version = System.getProperty("java.version");
    String java_vm_version = System.getProperty("java.vm.version");
    String java_vm_vendor = System.getProperty("java.vm.vendor");

    ps.setString(1, user_name);
    ps.setString(2, path);
    ps.setInt(3, sites);
    ps.setString(4, revision);
    ps.setString(5, branch);
    ps.setString(6, os_name);
    ps.setString(7, os_version);
    ps.setString(8, java_version);
    ps.setString(9, java_vm_version);
    ps.setString(10, java_vm_vendor);

    ps.execute();
    ps.getMoreResults();
    ResultSet rsi = ps.getResultSet();
    rsi.next();
    DUnitRun du = new DUnitRun(rsi.getInt(1), user_name, path, sites, revision,
        branch, os_name, os_version, java_version, java_vm_version,
        java_vm_vendor, new Date());
    return du;
  }
 

private static SQLResponse executeAndConvertResult(PreparedStatement preparedStatement) throws SQLException {
    if (preparedStatement.execute()) {
        ResultSetMetaData metaData = preparedStatement.getMetaData();
        ResultSet resultSet = preparedStatement.getResultSet();
        List<Object[]> rows = new ArrayList<>();
        List<String> columnNames = new ArrayList<>(metaData.getColumnCount());
        DataType[] dataTypes = new DataType[metaData.getColumnCount()];
        for (int i = 0; i < metaData.getColumnCount(); i++) {
            columnNames.add(metaData.getColumnName(i + 1));
        }
        while (resultSet.next()) {
            Object[] row = new Object[metaData.getColumnCount()];
            for (int i = 0; i < row.length; i++) {
                Object value;
                String typeName = metaData.getColumnTypeName(i + 1);
                value = getObject(resultSet, i, typeName);
                row[i] = value;
            }
            rows.add(row);
        }
        return new SQLResponse(
            columnNames.toArray(new String[0]),
            rows.toArray(new Object[0][]),
            dataTypes,
            rows.size()
        );
    } else {
        int updateCount = preparedStatement.getUpdateCount();
        if (updateCount < 0) {
            /*
             * In Crate -1 means row-count unknown, and -2 means error. In JDBC -2 means row-count unknown and -3 means error.
             * See {@link java.sql.Statement#EXECUTE_FAILED}
             */
            updateCount += 1;
        }
        return new SQLResponse(
            new String[0],
            new Object[0][],
            new DataType[0],
            updateCount
        );
    }
}
 

/**
 * Executes the given sql statement and returns the resulting ResultSet which will be SCROLL_INSENITIVE and CONCUR_READ_ONLY
 * 
 * @param sqlString the sql query to execute
 * @return the ResultSet that resulted from the given query which will be SCROLL_INSENITIVE and CONCUR_READ_ONLY
 */
protected final ResultSet executeScrollInsensitiveQuery(String sqlString) throws SQLException {
    final PreparedStatement ps = prepareStatement(sqlString, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ps.execute();
    return ps.getResultSet();
}