Java Code Examples for java.sql.ResultSet#close()

public void serverCheckBitemporalTerminated(int balanceId)
        throws SQLException
{
    Connection con = this.getServerSideConnection();
    String sql = "select count(*) from TINY_BALANCE where BALANCE_ID = ? and " +
            " OUT_Z = ? and THRU_Z = ?";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setInt(1, balanceId);
    ps.setTimestamp(2, InfinityTimestamp.getParaInfinity());
    ps.setTimestamp(3, InfinityTimestamp.getParaInfinity());
    ResultSet rs = ps.executeQuery();
    assertTrue(rs.next());
    int count = rs.getInt(1);
    assertFalse(rs.next());
    rs.close();
    ps.close();
    con.close();
    assertEquals(0, count);
}
 

/**
 * Deletes row from a test table that is expected to be empty.
 *
 * @throws SQLException if a database operation fails
 */
private void cleanTableExceptedToBeEmpty()
        throws SQLException {
    Statement stmt = createStatement();
    ResultSet rs = stmt.executeQuery("select * from stmtpooldata");
    int rowCount = 0;
    while (rs.next()) {
        rowCount++;
    }
    rs.close();
    // Delete rows if any, and print a warning if verbosity is on.
    if (rowCount > 0) {
        println("Expected empty table, got " + rowCount + " rows.");
        assertEquals(rowCount,
                stmt.executeUpdate("delete from stmtpooldata"));
    }
}
 

/**
 * 保存Custom配置文件信息
 * 
 * @param Config
 * @throws Exception
 */
public static int saveCustomConfig(CustomConfig config, String name) throws Exception {
	Connection conn = null;
	Statement stat = null;
	ResultSet rs = null;
	try {
		conn = getConnection();
		stat = conn.createStatement();
		String jsonStr = config.toJsonString();
		String sql = String.format("replace into CustomConfig (name,value) values('%s', '%s')", name, jsonStr);
		int result = stat.executeUpdate(sql);
		return result;
	} finally {
		if (rs != null)
			rs.close();
		if (stat != null)
			stat.close();
		if (conn != null)
			conn.close();
	}
}
 

public String getDeviceIdByName(String deviceName) throws SQLException {
    String selectStatementString = "SELECT * FROM " + TABLE_DEVICES + " WHERE " + COLUMN_DEVICE_NAME + " = ?";
    PreparedStatement findStatement = getServerDatabaseConnection().prepareStatement(selectStatementString);
    findStatement.setString(1, deviceName);
    ResultSet resultSet = findStatement.executeQuery();

    if (!resultSet.isBeforeFirst()){
        resultSet.close();
        findStatement.close();
        return null;
    }
    String deviceId = resultSet.getString(COLUMN_DEVICE_ID);

    resultSet.close();
    findStatement.close();

    return deviceId;
}
 

private final static int getRegID(String username) {
	try {
		PreparedStatement stmt = DBUtils.createStatement("SELECT id FROM registrations R WHERE R.username = ?");
		try {
			stmt.setString(1, username);
			ResultSet result = stmt.executeQuery();
			try {
				result.next();
				return result.getInt("id");
			} finally {
				result.close();
			}
		} finally {
			stmt.getConnection().close();
		}
	} catch (SQLException e) {
		MatchmakingServer.getLogger().throwing(DBInterface.class.getName(), "private getRegID", e);
		throw new RuntimeException(e);
	}
}
 

@Test
public void testJoinWithNonCoveringIndexInDTAndOuterJoin() throws Exception {
    String sql = format("select * from --splice-properties joinOrder=fixed\n" +
            "t1 left join \n" +
            "(select * from\n" +
            "  t2  --splice-properties index=ix_t2\n" +
            " where b2=4) dt --splice-properties joinStrategy=%s\n" +
            "on a1=a2", this.joinStrategy);

    String expected = "A1 |B1 |C1 | A2  | B2  | C2  |\n" +
            "------------------------------\n" +
            " 1 | 1 | 1 |NULL |NULL |NULL |\n" +
            " 2 | 2 | 2 |NULL |NULL |NULL |\n" +
            " 3 | 3 | 3 |NULL |NULL |NULL |\n" +
            " 4 | 4 | 4 |  4  |  4  |  4  |";
    ResultSet rs = spliceClassWatcher.executeQuery(sql);
    assertEquals(expected, TestUtils.FormattedResult.ResultFactory.toString(rs));
    rs.close();
}
 

public void testCallableStatementExec8()
   throws Exception
{
   dropProcedure( "test" );

   Statement stmt;

   stmt = con.createStatement();
   stmt.execute( "create procedure test as SELECT COUNT(*) FROM sysobjects" );
   stmt.close();

   CallableStatement cstmt = con.prepareCall( "execute test" );

   ResultSet rs = cstmt.executeQuery();
   dump( rs, SILENT );

   rs.close();
   cstmt.close();
}
 

public void testTransactionalMethod() throws SQLException
{
    int orderId = 1;
    int newValue = 7;
    String description = "new long description";
    Order order = OrderFinder.findOne(OrderFinder.orderId().eq(orderId));
    order.setUserIdAndDescription(newValue,description);

    Connection con = this.getConnection();
    String sql = "select USER_ID, DESCRIPTION from APP.ORDERS where ORDER_ID = ?";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setInt(1, orderId);
    ResultSet rs = ps.executeQuery();
    assertTrue(rs.next());
    assertEquals(newValue, rs.getInt(1));
    assertEquals(description, rs.getString(2));
    rs.close();
    ps.close();
    con.close();
}
 

/**
 * Look up the current match criteria for all threads in this JVM to use.
 */
private void getMatchCriteria() throws SQLException {
  if (matchCriterias == null) {
    Log.getLogWriter().info("Looking up current match criteria");
    matchCriterias = new ArrayList<MatchCriteria>();
    PreparedStatement matchCriteriaPS = this.connection.prepareStatement("select CURRENCY, AMOUNT, CLIENT_REF_NO, VALUE_DATE, CLIENT_ACCOUNT, COMPANY_ID from SEC_OWNER.SECT_CHANNEL_DATA");
    ResultSet rs = matchCriteriaPS.executeQuery();
    while (rs.next()) {
      matchCriterias.add(new MatchCriteria(rs));
    }
    rs.close();
    rs = null;
    Log.getLogWriter().info("Looked up " + matchCriterias.size()
                                         + " current match criteria");
  } else {
    Log.getLogWriter().info("Found " + matchCriterias.size()
                                     + " current match criteria");
  }
}
 

public List<Object[]> query(Connection conn, String sql) throws SQLException{
	if(conn.isClosed()){
		return null;
	}
	
	Statement state = null;
	ResultSet rs = null;
	
	try{
		state = conn.createStatement();
		rs = state.executeQuery(sql);
		ResultSetMetaData rsmd = rs.getMetaData();
		return extractData(rsmd, rs);
	}finally{
		if(rs != null){
			rs.close();
		}
		
		if(state != null){
			state.close();
		}
	}
}
 

/**
 * Test that you can scroll forward and read all records in the
 * ResultSet
 */
public void testForwardOnlyReadOnly1()
    throws SQLException 
{
    Statement s = createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                      ResultSet.CONCUR_READ_ONLY);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery("select * from t1");
    
    scrollForward(rs);
    rs.close();
    s.close();
}
 

/**
 * Tests for ServerPreparedStatement.setNCharacterSteam()
 * 
 * @throws Exception
 */
public void testSetNCharacterStreamServer() throws Exception {
    createTable("testSetNCharacterStreamServer", "(c1 NATIONAL CHARACTER(10)) ENGINE=InnoDB");
    Properties props1 = new Properties();
    props1.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true"); // use server-side prepared statement
    props1.setProperty(PropertyKey.characterEncoding.getKeyName(), "latin1"); // ensure charset isn't utf8 here
    Connection conn1 = getConnectionWithProps(props1);
    PreparedStatement pstmt1 = conn1.prepareStatement("INSERT INTO testSetNCharacterStreamServer (c1) VALUES (?)");
    try {
        pstmt1.setNCharacterStream(1, new StringReader("aaa"), 3);
        fail();
    } catch (SQLException e) {
        // ok
        assertEquals("Can not call setNCharacterStream() when connection character set isn't UTF-8", e.getMessage());
    }
    pstmt1.close();
    conn1.close();

    createTable("testSetNCharacterStreamServer", "(c1 LONGTEXT charset utf8) ENGINE=InnoDB");
    Properties props2 = new Properties();
    props2.setProperty(PropertyKey.useServerPrepStmts.getKeyName(), "true"); // use server-side prepared statement
    props2.setProperty(PropertyKey.characterEncoding.getKeyName(), "UTF-8"); // ensure charset is utf8 here
    Connection conn2 = getConnectionWithProps(props2);
    PreparedStatement pstmt2 = conn2.prepareStatement("INSERT INTO testSetNCharacterStreamServer (c1) VALUES (?)");
    pstmt2.setNCharacterStream(1, new StringReader(new String(new char[81921])), 81921); // 10 Full Long Data Packet's chars + 1 char
    pstmt2.execute();
    ResultSet rs2 = this.stmt.executeQuery("SELECT c1 FROM testSetNCharacterStreamServer");
    rs2.next();
    assertEquals(new String(new char[81921]), rs2.getString(1));
    rs2.close();
    pstmt2.close();
    conn2.close();
}
 

/**
 * Execute a prepared statement with a single double argument
 * and return the double value from the single row returned.
 */
private double getValue(PreparedStatement ps, double value)
        throws SQLException {
    ps.setDouble(1, value);
    ResultSet rs = ps.executeQuery();
    rs.next(); // we know a single value will be returned.
    double rValue = rs.getDouble(1);
    rs.close();
    return rValue;
}
 

@Test
public void testTruncTimestampValues_Default() throws Exception {
    // defaults to DAY
    String sqlText = "values truncate(timestamp('2011-12-26', '17:13:30'))";

    ResultSet rs = spliceClassWatcher.executeQuery(sqlText);

    String expected =
        "1           |\n" +
            "-----------------------\n" +
            "2011-12-26 00:00:00.0 |";
    assertEquals("\n"+sqlText+"\n", expected, TestUtils.FormattedResult.ResultFactory.toStringUnsorted(rs));
    rs.close();
}
 

@Test
public void testGetBoolean() throws Exception {
	PreparedStatement preparedStatement = createMock(PreparedStatement.class);
	ResultSet resultSet = createMock(ResultSet.class);
	int colN = 120;
	boolean val = true;
	expect(resultSet.getMetaData()).andReturn(null);
	expect(resultSet.getBoolean(colN + 1)).andReturn(val);
	resultSet.close();
	replay(preparedStatement, resultSet);
	JdbcDatabaseResults results = new JdbcDatabaseResults(preparedStatement, resultSet, null, false);
	assertEquals(val, results.getBoolean(colN));
	results.close();
	verify(preparedStatement, resultSet);
}
 

@Test
public void testTruncTimstampColumn_Year() throws Exception {
    String sqlText =
        String.format("select trunc(t, 'year') as \"truncd\", d, t, n from %s", QUALIFIED_TABLE_NAME);

    ResultSet rs = spliceClassWatcher.executeQuery(sqlText);

    String expected =
        "truncd         |     D     |          T           |      N       |\n" +
            "-------------------------------------------------------------------------\n" +
            "2000-01-01 00:00:00.0 |1988-12-26 |2000-06-07 17:12:30.0 |12345.6789000 |";
    assertEquals("\n"+sqlText+"\n", expected, TestUtils.FormattedResult.ResultFactory.toStringUnsorted(rs));
    rs.close();
}
 

@Test
public void testTruncDecimalValue1() throws Exception {
    String sqlText =  "values truncate(12345.6789, 1)";

    ResultSet rs = spliceClassWatcher.executeQuery(sqlText);

    String expected =
        "1     |\n" +
            "------------\n" +
            "12345.6000 |";
    assertEquals("\n"+sqlText+"\n", expected, TestUtils.FormattedResult.ResultFactory.toStringUnsorted(rs));
    rs.close();
}
 

/**
 * Test positioned update of a scrollable resultset (with FOR UPDATE) 
 */
public void testScrollablePositionedUpdateWithForUpdate1()
    throws SQLException 
{
    Statement s = createStatement
        (ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_READ_ONLY);
    s.setCursorName("MYCURSOR");
    ResultSet rs = s.executeQuery("select * from t1 for update");
    
    rs.next();
    int pKey = rs.getInt(1);
    rs.previous();
    rs.next();
    assertEquals("Expecting to be on the same row after previous() " + 
                 "+ next() ", pKey, rs.getInt(1));
    rs.next();
    rs.previous();
    assertEquals("Expecting to be on the same row after next() + " + 
                 "previous()", pKey, rs.getInt(1));
    final int previousA = rs.getInt(2);
    final int previousB = rs.getInt(3);
    println(rs.getCursorName());
    PreparedStatement ps = prepareStatement
        ("update T1 set a=?,b=? where current of " + rs.getCursorName());
    ps.setInt(1, 666);
    ps.setInt(2, 777);
    ps.executeUpdate();
    rs.next();
    rs.previous();
    assertEquals("Expected to be on the same row after next() + previous()",
                 pKey, rs.getInt(1));
    assertEquals("Expected row to be updated by own change, " + 
                 " however did not get updated value for column a", 
                 666, rs.getInt(2));
    assertEquals("Expected row to be updated by own change, however did " +
                 "not get updated value for column b", 777, rs.getInt(3));
    rs.close();
    s.setCursorName(getNextCursorName());
    rs = s.executeQuery("select * from t1 order by b");
    
    while (rs.next()) {
        if (rs.getInt(1)==pKey) {
            assertEquals("Expected row with primary key = " + pKey + 
                         " to be updated", 666, rs.getInt(2));
            assertEquals("Expected row with primary key = " + pKey + 
                         " to be updated", 777, rs.getInt(3));
        } else {
            println("Got tuple (" + rs.getInt(1) + "," + rs.getInt(2) + 
                    "," + rs.getInt(3) + "," + rs.getString(4)+ ")");
        }
    }

    s.close();
    ps.close();
}
 

/**
 * 
 * @param query
 *            - search msg
 * @return List of Twitter2
 */
public List<Twitter2> search(String query) {
	if (query == null) {
		return null;
	}

	List<Twitter2> tweets = new ArrayList<>();
	try {
		logger.error("Query: " + query);
		Connection connection = dataSource.getConnection();
		PreparedStatement ps = connection
				.prepareStatement("select * from sparktwitterorc WHERE msg like ? LIMIT ?");
		ps.setString(1, "%" + query + "%");
		ps.setInt(2, Integer.parseInt(querylimit));
		ResultSet res = ps.executeQuery();
		Twitter2 tweet = null;
		while (res.next()) {
			tweet = new Twitter2();
			tweet.setMsg(res.getString("msg"));
			tweet.setHandle(res.getString("handle"));
			tweet.setLocation(res.getString("location"));
			tweet.setCoordinates(res.getString("coordinates"));
			tweet.setFollowers_count(res.getString("followers_count"));
			tweet.setGeo(res.getString("geo"));
			tweet.setHashtags(res.getString("hashtags"));
			tweet.setLanguage(res.getString("language"));
			tweet.setPlace(res.getString("place"));
			tweet.setProfile_image_url(res.getString("profile_image_url"));
			tweet.setRetweet_count(res.getString("retweet_count"));
			tweet.setSentiment(res.getString("sentiment"));
			tweet.setSource(res.getString("source"));
			tweet.setTag(res.getString("tag"));
			tweet.setTime(res.getString("time"));
			tweet.setTag(res.getString("tag"));
			tweet.setTime_zone(res.getString("time_zone"));
			tweet.setTweet_id(res.getString("tweet_id"));
			tweet.setUnixtime(res.getString("unixtime"));
			tweet.setUser_name(res.getString("user_name"));
			tweets.add(tweet);
		}

		res.close();
		ps.close();
		connection.close();
		res = null;
		ps = null;
		connection = null;
		tweet = null;

		logger.error("Size=" + tweets.size());
	} catch (Exception e) {
		logger.error("Error in search", e);
	}

	return tweets;
}
 

/**
 * Tests the ClobOutputStream.write(byte  b[], int off, int len) method
 **/
public void testClobAsciiWrite3Param() throws Exception {
    InputStream streamIn = new LoopingAlphabetStream(streamSize[0]);
    assertTrue("FAIL -- file not found", streamIn != null);

    PreparedStatement stmt3 = prepareStatement(
        "SELECT c FROM testBlobX1 WHERE a = 1");
    ResultSet rs3 = stmt3.executeQuery();
    rs3.next();
    Clob clob = rs3.getClob(1);

    assertTrue("FAIL -- clob is NULL", clob != null);

    int count = 0;
    byte[] buffer = new byte[1024];
    OutputStream outstream = clob.setAsciiStream(1L);
    while ((count = streamIn.read(buffer)) != -1) {
        outstream.write(buffer, 0, count);
    }
    outstream.close();
    streamIn.close();

    PreparedStatement stmt4 = prepareStatement(
        "UPDATE testBlobX1 SET c = ? WHERE a = 1");
    stmt4.setClob(1,  clob);
    stmt4.executeUpdate();
    stmt4.close();

    rs3.close();
    rs3 = stmt3.executeQuery();

    assertTrue("FAIL -- clob not found", rs3.next());

    long new_length = rs3.getClob(1).length();
    assertEquals("FAIL -- wrong clob length",
            streamSize[0], new_length);
    // Check contents ...
    InputStream fStream = new LoopingAlphabetStream(streamSize[0]);
    InputStream lStream = rs3.getClob(1).getAsciiStream();
    assertTrue("FAIL - Clob and file contents do not match",
            compareLob2File(fStream, lStream));

    fStream.close();
    lStream.close();
    rs3.close();
    stmt3.close();
}