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

The following examples show how to use java.sql.PreparedStatement#getResultSet() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may want to check out the right sidebar which shows the related API usage.
Example 1
Source Project: components   File: SnowflakeRowReader.java    License: Apache License 2.0 6 votes vote down vote up
@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();
}
 
Example 2
Source Project: calcite   File: CalciteRemoteDriverTest.java    License: Apache License 2.0 6 votes vote down vote up
/** 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));
}
 
Example 3
Source Project: Zebra   File: DistinctTest.java    License: Apache License 2.0 6 votes vote down vote up
@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();
		}
	}
}
 
Example 4
@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();
        }
    }
}
 
Example 5
@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();
        }
    }
}
 
Example 6
Source Project: glowroot   File: PreparedStatementIT.java    License: Apache License 2.0 6 votes vote down vote up
@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();
    }
}
 
Example 7
@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" );
	}
}
 
Example 8
Source Project: raccoon4   File: AndroidAppDao.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 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);
	}
}
 
Example 9
@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();
        }
    }
}
 
Example 10
Source Project: Zebra   File: DistinctTest.java    License: Apache License 2.0 6 votes vote down vote up
@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();
		}
	}
}
 
Example 11
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();
	}
}
 
Example 12
@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();
        }
    }
}
 
Example 13
Source Project: Zebra   File: LimitTest.java    License: Apache License 2.0 5 votes vote down vote up
@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();
		}
	}
}
 
Example 14
Source Project: crate   File: SQLTransportExecutor.java    License: Apache License 2.0 4 votes vote down vote up
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
        );
    }
}
 
Example 15
Source Project: gemfirexd-oss   File: Swarm.java    License: Apache License 2.0 4 votes vote down vote up
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;
  }
 
Example 16
Source Project: geopackage-java   File: SQLExec.java    License: MIT License 4 votes vote down vote up
/**
 * 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;
}
 
Example 17
Source Project: gemfirexd-oss   File: BatchUpdateTest.java    License: Apache License 2.0 4 votes vote down vote up
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();
}
 
Example 18
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;
}
 
Example 19
Source Project: tuffylite   File: PredicateTest.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * 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);
	
}
 
Example 20
/**
 * 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();
}