下面列出了java.sql.PreparedStatement#getResultSet ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
@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 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();
}
}
}
@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();
}
}
}
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 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
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();
}
}
}
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
);
}
}
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;
}
/**
* 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 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();
}
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;
}
/**
* 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);
}
/**
* 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();
}