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

public static void getSelectService(String sql) {
    try {

        Class.forName("org.sqlite.JDBC");
        // 连接到数据库Dxy.db
        Connection connection = DriverManager.getConnection("jdbc:sqlite:SquirrelAI.db");
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            String strUserName = resultSet.getString("username");
            String strNickName = resultSet.getString("nickname");
            String strRemarkname = resultSet.getString("remarkname");
            String strProvince = resultSet.getString("province");
            String strCity = resultSet.getString("city");

            System.out.println(strUserName + "," + strNickName + "," + strRemarkname + "," + strProvince + "," + strCity);

        }

        statement.close();
        connection.close();

    } catch (Exception e) {
        e.printStackTrace();
    }
}
 

private void helpTestQueryForViewOnTableThatHasIndex(Statement s1, Statement s2, String tableName, String viewName, String indexName)
        throws SQLException {
    // Create a table
    s1.execute("create table " + tableName + " (col1 varchar primary key, col2 varchar)");

    // Create a view on the table
    s1.execute("create view " + viewName + " (col3 varchar) as select * from " + tableName);
    s1.executeQuery("select * from " + viewName);
    // Create a index on the table
    s1.execute("create index " + indexName + " ON " + tableName + " (col2)");

    try (ResultSet rs =
            s2.executeQuery("explain select /*+ INDEX(" + viewName + " " + indexName
                    + ") */ * from " + viewName + " where col2 = 'aaa'")) {
        String explainPlan = QueryUtil.getExplainPlan(rs);

        // check if the query uses the index
        assertTrue(explainPlan.contains(indexName));
    }
}
 

/**
 * Checks that the current SQL statement returns the expected result.
 */
public TestDataConnection returns(String expected) throws Exception {
  Connection connection = null;
  Statement statement = null;
  try {
    connection = adapter.createConnection();
    statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(sql);
    expected = expected.trim();
    String result = JdbcAssert.toString(resultSet).trim();
    resultSet.close();

    if (!expected.equals(result)) {
      Assert.fail(String.format("Generated string:\n%s\ndoes not match:\n%s", result, expected));
    }
    return this;
  } finally {
    if (statement != null) {
      statement.close();
    }
    if (connection != null) {
      connection.close();
    }
  }
}
 

/**
 * Positive tests, result set metadata
 */
public void testMetadata() throws SQLException {
    Statement stm = createStatement();

    ResultSet rs = stm.executeQuery("select * from t1 offset 1 rows");
    ResultSetMetaData rsmd= rs.getMetaData();
    int cnt = rsmd.getColumnCount();

    String[] cols = new String[]{"A","B"};
    int[] types = {Types.INTEGER, Types.BIGINT};

    for (int i=1; i <= cnt; i++) {
        String name = rsmd.getColumnName(i);
        int type = rsmd.getColumnType(i);

        assertTrue(name.equals(cols[i-1]));
        assertTrue(type == types[i-1]);
    }

    rs.close();
    stm.close();
}
 

private static void assertLocks(int[] expectedValues, Connection conn)
        throws SQLException {
    Statement s = conn.createStatement();
    ResultSet rs = s.executeQuery(
            "SELECT XID, sum(cast (LOCKCOUNT AS INT)) " +
                    "FROM SYSCS_DIAG.LOCK_TABLE AS L GROUP BY XID");

    // Don't output actual XID's as they tend for every catalog change
    // to the system.
    int xact_index = 0;
    while (rs.next()) {
        if (expectedValues != null)
            assertEquals(expectedValues[xact_index], rs.getInt(2));
        else
            fail("expected no locks");
        xact_index++;
    }
    if (expectedValues != null)
        assertEquals(expectedValues.length, xact_index);

    rs.close();
    s.close();
}
 

/**
 * Select from base table.
 * 
 * @throws SQLException
 */
public void testSelect() throws SQLException {
	Statement s = createStatement();
	ResultSet rs;
	ResultSetMetaData rsmd;
	
	rs = s.executeQuery("select x,z from tab1");
	rsmd = rs.getMetaData();
	
	assertFalse("Column count doesn't match.", rsmd.getColumnCount() != 2);
	assertFalse("Column 1 is NOT ai.", rsmd.isAutoIncrement(1));
	assertFalse("Column 2 is NOT ai.", rsmd.isAutoIncrement(2));
	
	rs.close();
	
	rs = s.executeQuery("select y, x,z from tab1");
	rsmd = rs.getMetaData();
	
	assertFalse("Column count doesn't match.", rsmd.getColumnCount() != 3);
	assertFalse("Column 1 IS ai.", !rsmd.isAutoIncrement(1));
	assertFalse("Column 2 is NOT ai.", rsmd.isAutoIncrement(2));
	assertFalse("Column 3 is NOT ai.", rsmd.isAutoIncrement(3));
	
	rs.close();
	s.close();
}
 

/**
 * Test that computation of transitive closure of equi-join does not give
 * rise to eternal loop in a case where a predicate of type T1.x = T1.y is
 * added to the closure.
 * @throws SQLException
 */
public void testDerby4387() throws SQLException {
    setAutoCommit(false);
    Statement s = createStatement();
    ResultSet rs;


    s.executeUpdate("create table c (a int, b int, c int)");
    s.executeUpdate("create table cc (aa int)");

    // Compiling this query gave an infinite loop (would eventually run out
    // of memory though) before the fix:
    rs = s.executeQuery("select * from cc t1, c t2, cc t3 " +
                        "    where t3.aa = t2.a and " +
                        "          t3.aa = t2.b and " +
                        "          t3.aa = t2.c");

    // After the fix the correct joinClauses table should look like this
    // when done (see PredicateList#joinClauseTransitiveClosure variable
    // joinClauses), where EC is equivalence class assigned, and a *
    // denotes a predicate added by the closure computation.
    //
    // [0]: (t1)
    // [1]: (t2)
    //    [0]: 2.1 = 1.1 EC: 0     i.e.  t3.aa == t2.a
    //    [1]: 1.1 = 1.3 EC: 0           t2.a  == t2.c *
    //    [2]: 1.1 = 1.2 EC: 0           t2.a  == t2.b *
    //    [3]: 2.1 = 1.2 EC: 0           t3.aa == t2.b
    //    [4]: 2.1 = 1.3 EC: 0           t3.aa == t2.c
    // [2]: (t3)
    //    [0]: 2.1 = 1.1 EC: 0           t3.aa == t2.a
    //    [1]: 2.1 = 1.2 EC: 0           t3.aa == t2.b
    //    [2]: 2.1 = 1.3 EC: 0           t3.aa == t2.c
    //
    // Before the fix, the derived predicates (e.g. t2.a == t2.b) were
    // added twice and caused an infinite loop.

    rollback();
}
 

SQLException executeQuery(Connection conn, String query)
{
    util.DEBUG("executeQuery: " + query);
    try
    {
        Statement s = conn.createStatement();
        ResultSet rs = s.executeQuery(query);
        return null;        
    }
    catch (SQLException se)
    {
        return se;
    }
}
 

public static void selectRows(String table, ResultSet[] rs)
        throws SQLException
{
        Connection conn = DriverManager.getConnection("jdbc:default:connection");
        Statement stmt = conn.createStatement();
        rs[0] = stmt.executeQuery("SELECT * FROM " + table);
        conn.close();
        selectRowsCount++;
}
 

@Test
@ConditionalIgnoreRule.ConditionalIgnore(condition = RunningOnGithubAction.class)
public void testNegativeWithClosedResultSet() throws Throwable
{
  try (Connection connection = init())
  {
    Statement statement = connection.createStatement();

    statement.execute(
        "create or replace table table_basic " +
        " (int_c int, string_c string(128))");

    int rowCount = 300;
    statement.execute(
        "insert into table_basic select " +
        "seq4(), " +
        "'arrow_1234567890arrow_1234567890arrow_1234567890arrow_1234567890'" +
        " from table(generator(rowcount=>" + rowCount + "))");


    String sqlSelect = "select * from table_basic ";
    ResultSet rs = statement.executeQuery(sqlSelect);
    rs.close();

    // The getResultSetSerializables() can only be called for unclosed
    // result set.
    try
    {
      List<SnowflakeResultSetSerializable> resultSetSerializables =
          ((SnowflakeResultSet) rs)
              .getResultSetSerializables(100 * 1024 * 1024);
      fail("error should happen when accessing closed result set.");
    }
    catch (SQLException ex)
    {
      System.out.println("Negative test hits expected error: " + ex.getMessage());
    }
  }
}
 

/**
 * setup for restart recovery test which will require the use of correct
 * Collator object during recovery of territory based database that was 
 * created and crashed in this col_rec1
 **/
private void test1(
Connection  conn,
String      test_name,
String      table_name)
    throws SQLException
{
    beginTest(conn, test_name);
    Statement s = conn.createStatement();
    ResultSet rs = s.executeQuery("VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY" + 
                "('db.database.collation')");
    rs.next();
    String collation = rs.getString(1);
    if (!collation.equals("TERRITORY_BASED"))
        logError("Collation should have been territory based but it is "
        		+ collation);

    rs = s.executeQuery("select count(*) from t");
    rs.next();
    int numberOfRows = rs.getInt(1);
    if (numberOfRows > 1)
    	 logError("Expected 1 row in T but found " + numberOfRows +
    			 " rows");
    rs.close();
    s.close();
    endTest(conn, test_name);
}
 

public void testImportOneIndexTable(String tableName, boolean localIndex) throws Exception {

        String indexTableName = String.format("%s_IDX", tableName);
        Statement stmt = conn.createStatement();
        stmt.execute("CREATE TABLE " + tableName + "(ID INTEGER NOT NULL PRIMARY KEY, "
                + "FIRST_NAME VARCHAR, LAST_NAME VARCHAR)");
        String ddl =
                "CREATE " + (localIndex ? "LOCAL" : "") + " INDEX " + indexTableName + " ON "
                        + tableName + "(FIRST_NAME ASC)";
        stmt.execute(ddl);

        FileSystem fs = FileSystem.get(getUtility().getConfiguration());
        FSDataOutputStream outputStream = fs.create(new Path("/tmp/input4.csv"));
        PrintWriter printWriter = new PrintWriter(outputStream);
        printWriter.println("1,FirstName 1,LastName 1");
        printWriter.println("2,FirstName 2,LastName 2");
        printWriter.close();

        CsvBulkLoadTool csvBulkLoadTool = new CsvBulkLoadTool();
        csvBulkLoadTool.setConf(getUtility().getConfiguration());
        int exitCode = csvBulkLoadTool.run(new String[] {
                "--input", "/tmp/input4.csv",
                "--table", tableName,
                "--index-table", indexTableName,
                "--zookeeper", zkQuorum });
        assertEquals(0, exitCode);

        ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName);
        assertFalse(rs.next());
        rs = stmt.executeQuery("SELECT FIRST_NAME FROM " + tableName + " where FIRST_NAME='FirstName 1'");
        assertTrue(rs.next());
        assertEquals("FirstName 1", rs.getString(1));

        rs.close();
        stmt.close();
    }
 

/**
 * Tests that <code>Statement.executeQuery()</code> succeeds when
 * one result set is returned from a stored procedure.
 * @exception SQLException if a database error occurs
 */
public void testExecuteQueryWithOneDynamicResultSet() throws SQLException {
    Statement stmt = createStatement();
    ResultSet rs = stmt.executeQuery("CALL RETRIEVE_DYNAMIC_RESULTS(1)");
    assertNotNull("executeQuery() returned null.", rs);
    assertSame(stmt, rs.getStatement());
    JDBC.assertDrainResultsHasData(rs);
}
 

/**
 * 馆藏书籍分页功能
 * 
 * @author Huyuxi
 * @return total
 */
public int getTotal(String isbn) {
	int total = 0;
	Connection conn = null;
	Statement st = null;
	ResultSet rs;
	Book book = null;
	Collection bookColle = new ArrayList();
	String sql = null;
	try {

		conn = DatabaseUtil.getInstance().getConnection();
		st = conn.createStatement();
		sql = "select count(*),GROUP_CONCAT(distinct author.author_name SEPARATOR ',') from (((book join book_in_library on book.isbn=book_in_library.isbn) "
				+ " left join publisher on publisher.publisher_id=book.publisher_id) "
				+ " left join writes on writes.isbn=book.isbn "
				+ " left join author on author.author_id=writes.author_id) " + " where book.isbn = " + "\'" + isbn
				+ "\'  group by book_id";
		rs = st.executeQuery(sql);
		while (rs.next()) {
			total = total + rs.getInt(1);
		}

		System.out.println("total:" + total);
		st.close();
		rs.close();
		conn.close();
	} catch (Exception e) {
		e.printStackTrace();
	}
	return total;
}
 

/**
 * Test that you get an exception when attempting to update a
 * ResultSet which has been downgraded to a read only ResultSet.
 */
public void testFailOnUpdateOfReadOnlyResultSet3()
    throws SQLException 
{
    Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                      ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs =
        s.executeQuery("select * from t1 for read only");
    
    rs.next();
    assertFailOnUpdate(rs);
    s.close();
}
 

/**
 * Search by ISBN
 * 
 * @author Hu Yuxi
 * @param isbn
 * @return search book information by isbn
 */
public Book searchByIsbn(String isbn) {
	Connection conn = null;
	Statement st = null;
	ResultSet rs;
	Book book = null;
	String sql = null;
	if (isbn != "all" || isbn != null || isbn != "") {
		sql = "select book_id,book.isbn,book_name,book_price,book_description,publisher.publisher_id,publisher_name,publisher_description,book_location,state,GROUP_CONCAT(author.author_name SEPARATOR ',') from (((book join book_in_library on book.isbn=book_in_library.isbn) "
				+ " left join publisher on publisher.publisher_id=book.publisher_id) "
				+ " left join writes on writes.isbn=book.isbn "
				+ " left join author on author.author_id=writes.author_id) " + " where book_in_library.isbn=" + isbn
				+ "group by book_id ";
	} else {
		return book;
	}
	System.out.println("SearchByID sql:" + sql);
	try {
		conn = DatabaseUtil.getInstance().getConnection();
		st = conn.createStatement();
		rs = st.executeQuery(sql);
		while (rs.next()) {
			book = new Book();
			book.setISBN(rs.getString("isbn"));
			book.setName(rs.getString("book_name"));
			book.setPrice(rs.getBigDecimal("book_price"));
			book.setDescription(rs.getString("book_description"));
			// set publisher
			Publisher publisher1 = new Publisher();
			publisher1.setId(rs.getInt("publisher_id"));
			publisher1.setName(rs.getString("publisher_name"));
			publisher1.setDescription(rs.getString("publisher_description"));
			book.setPublisher(publisher1);
			// set author
			book.setAuthors(rs.getString("GROUP_CONCAT(author.author_name SEPARATOR ',')"));

			book.setLocation(rs.getString("book_location"));
			book.setState(rs.getString("state"));

		}
		conn.close();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return book;
}
 

public void testDBSynchronizerWithTruncateTable_Replicate() throws Exception {
  Statement derbyStmt = null;
  Connection derbyConn = null;
  NetworkServerControl server = null;
  try {
    String derbyDbUrl = getDerbyURL(this.netPort);
    server = startNetworkServer();
    createDerbyValidationArtefacts();
    derbyConn = DriverManager.getConnection(derbyDbUrl);
    derbyStmt = derbyConn.createStatement();
    
    startClientVMs(1, 0, null);
    startServerVMs(2, -1, "SG1");
    
    clientSQLExecute(1,
        "create table TESTTABLE (ID int not null primary key , "
            + "DESCRIPTION varchar(1024) , ADDRESS varchar(1024), ID1 int )"
            + " REPLICATE AsyncEventListener (WBCL1) ");

    Runnable createWBCLConfig = getExecutorForWBCLConfiguration("SG1", "WBCL1",
        "com.pivotal.gemfirexd.callbacks.DBSynchronizer",
        "org.apache.derby.jdbc.ClientDriver", derbyDbUrl, true,
        Integer.valueOf(1), null, Boolean.FALSE, null, null, null, 100000,
        "org.apache.derby.jdbc.ClientDriver," + derbyDbUrl, false);
    clientExecute(1, createWBCLConfig);
    Runnable startWBCL = startAsyncEventListener("WBCL1");
    clientExecute(1, startWBCL);
    
    // Do an insert in gfxd
    clientSQLExecute(1, "Insert into TESTTABLE values(1,'desc1','Add1',1)");
    clientSQLExecute(1, "Insert into TESTTABLE values(2,'desc2','Add2',2)");
    clientSQLExecute(1, "Insert into TESTTABLE values(3,'desc3','Add3',3)");
    clientSQLExecute(1, "Insert into TESTTABLE values(4,'desc4','Add4',4)");
    
    //truncate deletes complete data from the table
    clientSQLExecute(1, "truncate table TESTTABLE");
    
    Connection conn = TestUtil.jdbcConn;
    Statement stmt = conn.createStatement();
    ResultSet gfxdRs = stmt.executeQuery("select count(*) from TESTTABLE");
    JDBC.assertSingleValueResultSet(gfxdRs, "0");
    
    //wait for AsyncQueue flush
    serverSQLExecute(1, "call SYS.WAIT_FOR_SENDER_QUEUE_FLUSH('WBCL1', 1, 30)");
    
    //derby should have 4 rows because truncate table as DDL won't be propagated to derby
    ResultSet derbyRS = derbyStmt.executeQuery("select count(*) from TESTTABLE");
    JDBC.assertSingleValueResultSet(derbyRS, "4");

  }
  finally {
    derbyCleanup(derbyStmt, derbyConn, server);
  }
}
 

@Test
public void testResultSet() {

    String driverClass = "com.impetus.eth.jdbc.EthDriver";
    try {

        Class.forName(driverClass);

        Connection conn = DriverManager.getConnection(url, null);
        Statement stmt = conn.createStatement();

        LOGGER.info("*****************SELECT * FROM A BLOCK ***************");

        ResultSet rs = stmt.executeQuery("select * from transactions where blocknumber=1652339");
        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++)
            LOGGER.info(rs.getMetaData().getColumnLabel(i) + " | ");
        while (rs.next()) {
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++)
                if (i == 15)
                    LOGGER.info(rs.getInt(i) + " | ");
                else
                    LOGGER.info(rs.getObject(i) + " | ");
        }

        LOGGER.info("************SELECT MULTIPLE COLUMNS WITH MULTIPLE BLOCKS *************   ");
        rs = stmt.executeQuery(
                "select blocknumber, blockhash,to,value,gasprice from transactions where blocknumber=1652339 or blocknumber=1652340");
        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++)
            LOGGER.info(rs.getMetaData().getColumnLabel(i) + " | ");

        while (rs.next()) {
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++)
                if (i == 15)
                    LOGGER.info(rs.getInt(i) + " | ");
                else
                    LOGGER.info(rs.getObject(i) + " | ");
        }

        LOGGER.info("************SELECT ORDER BY COLUMNS*************   ");
        rs = stmt.executeQuery(
                "select blocknumber, blockhash,to,value,gasprice from transactions where blocknumber=1652339 or blocknumber=1652340 order by gasprice desc");
        for (int i = 0; i < rs.getMetaData().getColumnCount(); i++)
            LOGGER.info(rs.getMetaData().getColumnLabel(i) + " | ");

        while (rs.next()) {
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++)
                if (i == 15)
                    LOGGER.info(rs.getInt(i) + " | ");
                else
                    LOGGER.info(rs.getObject(i) + " | ");
        }

        LOGGER.info("********************SELECT GROUP BY COLUMNS ************");
        rs = stmt.executeQuery(
                "select count(to), to from transactions where blocknumber=1652339 or blocknumber=1652340 group by to");
        ResultSetMetaData rsMetaData = rs.getMetaData();
        for (int i = 0; i < rsMetaData.getColumnCount(); i++)
            LOGGER.info(rsMetaData.getColumnLabel(i) + " | ");
        System.out.println();
        while (rs.next()) {
            LOGGER.info(String.valueOf(rs.getInt(0)));
            LOGGER.info(" | " + rs.getString(1));
        }
        conn.close();
        assertEquals(true, conn.isClosed());
        assertEquals(true, rs.isClosed());
        assertEquals(true, stmt.isClosed());
    } catch (Exception e) {

        e.printStackTrace();
    }
}
 

private ResultSet getAll() throws SQLException {
    String sqlStatement = String.format("SELECT * FROM %s.time_table", schema);

    Statement statement = connection.createStatement();

    return statement.executeQuery(sqlStatement);
}
 

/**
 * A single connection and 1 phase commit.
 * 
 * 
 * Original "SQL" from xaSimplePositive.sql <code>
 xa_connect ;
 xa_start xa_noflags 0;
 xa_getconnection;
 drop table foo;
 create table foo (a int);
 insert into foo values (0);
 select * from foo;
 run resource '/org/apache/derbyTesting/functionTests/tests/store/global_xactTable.view';
 select * from global_xactTable where gxid is not null order by gxid;
 xa_end xa_success 0;
 xa_commit xa_1phase 0;
 
 xa_datasource 'wombat' shutdown;
 </code>
 * 
 * @throws SQLException
 * @throws XAException
 * @throws XAException
 */
public void testSingleConnectionOnePhaseCommit() throws SQLException,
        XAException {

    XADataSource xads = J2EEDataSource.getXADataSource();
    J2EEDataSource.setBeanProperty(xads, "databaseName", "wombat");

    XAConnection xac = xads.getXAConnection();

    XAResource xar = xac.getXAResource();

    Xid xid = XATestUtil.getXid(0, 32, 46);

    xar.start(xid, XAResource.TMNOFLAGS);

    Connection conn = xac.getConnection();
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, conn.getHoldability());

    Statement s = conn.createStatement();
    assertEquals(ResultSet.CLOSE_CURSORS_AT_COMMIT, s
            .getResultSetHoldability());

    s.execute("create table foo (a int)");
    s.executeUpdate("insert into foo values (0)");

    ResultSet rs = s.executeQuery("select * from foo");
    JDBC.assertDrainResults(rs, 1);

    String[][] expectedRows = { { "(0", "ACTIVE", "false", "SPLICE",
            "UserTransaction" } };

    XATestUtil.checkXATransactionView(conn, expectedRows);

    s.close();
    xar.end(xid, XAResource.TMSUCCESS);

    // 1 phase commit
    xar.commit(xid, true);

    conn.close();
    xac.close();

}