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

/**
 * Tests updating a Blob from a scollable resultset, using
 * result set update methods.
 * @exception SQLException causes test to fail with error
 * @exception IOException causes test to fail with error
 */
public void testUpdateBlobFromScrollableResultSetUsingResultSetMethods()
    throws SQLException, IOException
{
    final Statement stmt = 
        createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                            ResultSet.CONCUR_UPDATABLE);
    final ResultSet rs = 
        stmt.executeQuery("SELECT * from " + 
                          BLOBDataModelSetup.getBlobTableName());
    println("Last");
    rs.last();
    
    final int newVal = rs.getInt(1) + 11;
    final int newSize = rs.getInt(2) / 2;
    testUpdateBlobWithResultSetMethods(rs, newVal, newSize);
    
    println("Verify updated blob using result set");
    verifyBlob(newVal, newSize, rs.getBlob(3));
    
    rs.close();
    stmt.close();
}
 

/**
 * Tests updating a Blob from a scollable resultset, produced by 
 * a select query with projection. Updates are made using
 * positioned updates
 * @exception SQLException causes test to fail with error
 * @exception IOException causes test to fail with error
 */
public void testUpdateBlobFromScrollableResultSetWithProjectUsingPositionedUpdates()
    throws SQLException, IOException
{
    final Statement stmt = 
        createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                            ResultSet.CONCUR_UPDATABLE);
    final ResultSet rs = 
        stmt.executeQuery("SELECT data from " + 
                          BLOBDataModelSetup.getBlobTableName() + 
                          " WHERE val= " + BLOBDataModelSetup.bigVal);
    println("Last");
    rs.last();
    
    final int newVal = BLOBDataModelSetup.bigVal * 2;
    final int newSize = BLOBDataModelSetup.bigSize / 2;
    testUpdateBlobWithPositionedUpdate(rs, newVal, newSize);

    rs.relative(0); // Necessary after a positioned update
    
    println("Verify updated blob using result set");
    verifyBlob(newVal, newSize, rs.getBlob("DATA"));
    
    rs.close();
    stmt.close();
}
 

/**
 * Tests updating a Blob from a scollable resultset, using
 * result set update methods.
 * @exception SQLException causes test to fail with error
 * @exception IOException causes test to fail with error
 */
public void testUpdateBlobFromScrollableResultSetUsingResultSetMethods()
    throws SQLException, IOException
{
    final Statement stmt = 
        createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                            ResultSet.CONCUR_UPDATABLE);
    final ResultSet rs = 
        stmt.executeQuery("SELECT * from " + 
                          BLOBDataModelSetup.getBlobTableName());
    println("Last");
    rs.last();
    
    final int newVal = rs.getInt(1) + 11;
    final int newSize = rs.getInt(2) / 2;
    testUpdateBlobWithResultSetMethods(rs, newVal, newSize);
    
    println("Verify updated blob using result set");
    verifyBlob(newVal, newSize, rs.getBlob(3));
    
    rs.close();
    stmt.close();
}
 

/**
 * Updates the result set in this model with the given ResultSet object.
 *
 * @param resultset the new result set.
 * @throws SQLException if there is a problem with the result set.
 */
public void updateResultSet( final ResultSet resultset )
  throws SQLException {
  if ( this.resultset != null ) {
    close();
  }

  this.resultset = resultset;
  this.dbmd = resultset.getMetaData();
  final int colcount = dbmd.getColumnCount();
  AttributeMap<Object>[] columnMeta = new AttributeMap[colcount];
  for ( int i = 0; i < colcount; i++ ) {
    columnMeta[i] = ResultSetTableModelFactory.collectData( dbmd, i, getColumnName( i ) );
  }

  this.metaData = new ImmutableTableMetaData( ImmutableDataAttributes.EMPTY,
                                              ResultSetTableModelFactory.map( columnMeta ) );

  if ( resultset.last() ) {
    rowCount = resultset.getRow();
  } else {
    rowCount = 0;
  }

  fireTableStructureChanged();
}
 

/**
 * Tests a sequence of operations on a scrollable result set.
 *
 * @param table the table to query
 * @param rsConcurrency the result set concurrency
 */
private void scrollableTest(String table, int rsConcurrency)
        throws SQLException {
    final String sql = "select dBlob, dClob from " + table;
    getConnection().setAutoCommit(false);
    Statement stmt = createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                                     rsConcurrency);
    ResultSet rs = stmt.executeQuery(sql);
    // Just iterate through and close.
    while (rs.next()) {}
    rs.close();

    // Do some random navigation.
    rs = stmt.executeQuery(sql);
    rs.next();
    rs.beforeFirst();
    rs.first();
    rs.relative(3);
    rs.previous();
    rs.last();
    rs.absolute(5);
    rs.afterLast();
    rs.next();
}
 

@Override
public int[] getUsedIDs() {
	PreparedStatement statement = DB.prepareStatement("SELECT DISTINCT id FROM houses", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

	try {
		ResultSet rs = statement.executeQuery();
		rs.last();
		int count = rs.getRow();
		rs.beforeFirst();
		int[] ids = new int[count];
		for (int i = 0; i < count; i++) {
			rs.next();
			ids[i] = rs.getInt(1);
		}
		return ids;
	}
	catch (SQLException e) {
		log.error("Can't get list of id's from houses table", e);
	}
	finally {
		DB.close(statement);
	}

	return new int[0];
}
 

@Override
public DBRow.Column getRandomColumnForDML(String query, Object value, int type)
     {
  
  try {
  PreparedStatement stmt = gConn.prepareStatement(query,
      ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
  PrepareStatementSetter ps = new PrepareStatementSetter(stmt);
  ps.setValues(value, type);
  ResultSet rs = stmt.executeQuery();
  if (rs.next()) {
    rs.last();
    int randomRow = GenericDML.rand.nextInt(rs.getRow());
    rs.absolute(randomRow == 0 ? 1 : randomRow);
    return new DBRow.Column(rs.getMetaData().getColumnName(1), rs
        .getMetaData().getColumnType(1), rs.getObject(1));
  }
  else {
    return new DBRow.Column(null, 0, value);
  }
  
  } catch (  SQLException  se) {
    throw new TestException (" Error while retrieving a row from database " + se.getSQLState() + TestHelper.getStackTrace(se));
  }
}
 

/**
 * get consumption progress
 * @param jobId
 * @param props
 * @param log
 * @return
 */
String getOffset(int jobId, Properties props, Logger log){
    String sqlForReadMsgID = "SELECT msg_id FROM event_status WHERE receiver=? AND topic=? AND msg_name=?";
    PreparedStatement pstmtForGetID = null;
    Connection msgConn = null;
    ResultSet rs = null;
    boolean flag = false;
    String lastMsgId = "0";
    try {
        msgConn = getEventCheckerConnection(props,log);
        pstmtForGetID = msgConn.prepareCall(sqlForReadMsgID);
        pstmtForGetID.setString(1, receiver);
        pstmtForGetID.setString(2, topic);
        pstmtForGetID.setString(3, msgName);
        rs = pstmtForGetID.executeQuery();
        lastMsgId = rs.last()==true ? rs.getString("msg_id"):"0";
    } catch (SQLException e) {
        throw new RuntimeException("get Offset failed " + e);
    }finally {
        closeQueryStmt(pstmtForGetID,log);
        closeConnection(msgConn,log);
        closeQueryRef(rs,log);
    }
    log.info("The last record id was " + lastMsgId);
    return lastMsgId;
}
 

protected int getResultNumber(ResultSet resultSet) throws SQLException {
	logger.debug("IN");
	LogMF.debug(logger, "Moving into last record for statement:\n{0}", resultSet.getStatement().toString());
	int rowcount = 0;
	if (resultSet.last()) {
		rowcount = resultSet.getRow();
		resultSet.beforeFirst(); // not rs.first() because the rs.next()
									// below will move on, missing the first
									// element
	}
	LogMF.debug(logger, "Moved into last record for statement:\n{0}", resultSet.getStatement().toString());
	return rowcount;
}
 

private long runQueryGetId(PreparedStatement stmt, boolean checkconnection)
{
    long id = 0;

    ResultSet rs = null;
    try
    {
        if (checkconnection)
            checkConnection();
        stmt.executeUpdate();

        rs = stmt.getGeneratedKeys();
        if (rs.last())
        {
            id = rs.getLong(1);
        }
    }
    catch (Exception e)
    {
        throw new RuntimeException(e);
    }
    finally
    {
        close(rs);
    }
    return id;
}
 

/**
 * Test update indexed records using scrollable updatable resultset 
 */
public void testSecondaryIndexKeyUpdate1()
    throws SQLException 
{
    
    Statement s = createStatement
        (ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_UPDATABLE);
    s.setCursorName(getNextCursorName());
    ResultSet rs = s.executeQuery("select * from t1");
    
    rs.last();
    rs.next();
    int newKey = 0;
    while(rs.previous()) {
        // Update the secondary key of all rows
        rs.updateInt(2, newKey--);
        rs.updateRow();
    }
    PreparedStatement ps = prepareStatement
        ("select * from t1 where a=?");
    for (int i=0; i<recordCount; i++) {
        int key = -i;
        ps.setInt(1, key);
        ResultSet rs2 = ps.executeQuery();
        assertTrue("Expected query to have 1 row", rs2.next());
        println("T1: Read Tuple:(" + rs2.getInt(1) + "," +
                rs2.getInt(2) + "," +
                rs2.getInt(3) + ")");
        assertEquals("Unexpected value of id", key, rs2.getInt(2));
        assertTrue("Did not expect more than 1 row, " +
                   "however rs2.next returned another row",
                   !rs2.next());
    }
    
    s.close();
    ps.close();
}
 

/**
 * Consistent entrance to consumer message
 * @param jobId
 * @param props
 * @param log
 * @param params   params[startQueryTime,endQueryTime,vMsgID]
 * @return
 */
String[] getMsg(Properties props, Logger log,String ... params){
    String sqlForReadTMsg = "SELECT * FROM event_queue WHERE topic=? AND msg_name=? AND send_time >=? AND send_time <=? AND msg_id >? ORDER BY msg_id ASC LIMIT 1";
    PreparedStatement pstmt = null;
    Connection msgConn = null;
    ResultSet rs = null;
    String[] consumedMsgInfo = null;
    try {
        msgConn = getEventCheckerConnection(props,log);
        pstmt = msgConn.prepareCall(sqlForReadTMsg);
        pstmt.setString(1, topic);
        pstmt.setString(2, msgName);
        pstmt.setString(3, params[0]);
        pstmt.setString(4, params[1]);
        pstmt.setString(5, params[2]);
        log.info("param {} StartTime: " + params[0] + ", EndTime: " + params[1]
                + ", Topic: " + topic + ", MessageName: " + msgName + ", LastMessageID: " + params[2]);
        rs = pstmt.executeQuery();

        if(rs.last()){
            consumedMsgInfo = new String[4];
            String[] msgKey = new String[]{"msg_id","msg_name","sender","msg"};
            for (int i = 0;i <= 3;i++) {
                consumedMsgInfo[i] = rs.getString(msgKey[i]);
            }
        }
    } catch (SQLException e) {
        throw new RuntimeException("EventChecker failed to receive message" + e);
    } finally {
        closeQueryStmt(pstmt, log);
        closeConnection(msgConn, log);
        closeQueryRef(rs, log);
    }
    return consumedMsgInfo;
}
 

protected void assertResultSetLength(ResultSet rset, int len) throws Exception {
    int oldRowPos = rset.getRow();
    rset.last();
    assertEquals("Result set length", len, rset.getRow());
    if (oldRowPos > 0) {
        rset.absolute(oldRowPos);
    } else {
        rset.beforeFirst();
    }
}
 

/**
 * Get the total number of rows present in the current result set without disturbing the cursor.
 *
 * @return Total Number of Rows
 * @throws SQLException
 *             Exception thrown by any corrupted result set
 */
public int getRowCount() throws SQLException {
    int totalCount = 0;
    if (results.size() > 0) {
        for (LabeledResultSet result : results) {
            ResultSet set = result.getResultSet();
            set.last();
            totalCount += set.getRow();
            set.beforeFirst();
        }
    }
    return totalCount;
}
 

/**
 * Returns an array of the primary keys found in a given table. An array of size
 * 0 is returned in the event of a db access error.
 * 
 * @param table The entity node representing the table from which we wish to get
 *              the primary IDs
 * @return The primary IDs of all records found in the given table
 */
public static int[] getPKs(final EntityNode table) {
	final JDBCDriver dbd;
	final ColumnNaming cn;
	final ResultSet result;

	try {
		dbd = table.getMModel().getDatabase().getJDBCDriver();
		cn = new ColumnNaming(dbd);
		result = dbd.executeQuery("SELECT * FROM " + table.getName());

		result.last(); // move to last row to get row count

		final int[] pKs = new int[result.getRow()];

		// move back to first row
		result.beforeFirst();
		result.next();

		final String pIdName = cn.tablePK(table);

		for (int i = 0; i < pKs.length; i++) {
			pKs[i] = result.getInt(pIdName);

			result.next();
		}

		return pKs;
	} catch (SQLException e) {
		System.err.println("Error in DatabaseUtil: " + e.getMessage());

		return new int[0];
	}
}
 

public void testSimpleScrollCursors() throws SQLException {
    Connection conn = getConnection();
    Statement s = conn.createStatement();
    s.executeUpdate("create table t (a int)");
    PreparedStatement ps = conn.prepareStatement("insert into t values (?)");
    for (int i = 1; i <=5; i++)
    {
        ps.setInt(1, i);
        ps.executeUpdate();
    }
    ps.close();
    PreparedStatement ps_c1 = conn.prepareStatement("select * from t", ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = ps_c1.executeQuery();
    rs.first();
    assertEquals(1,rs.getInt(1));
    rs.next();
    assertEquals(2,rs.getInt(1));
    rs.previous();
    assertEquals(1,rs.getInt(1));
    rs.last();
    assertEquals(5,rs.getInt(1));
    rs.absolute(2);
    assertEquals(2, rs.getInt(1));
    rs.relative(2);
    assertEquals(4,rs.getInt(1));
    rs.close();
    // since JCC gets 64 results and then scrolls within them
    // lets try each p ositioning command as the first command for the cursor.
    rs = ps_c1.executeQuery();
    rs.next();
    assertEquals(1,rs.getInt(1));
    rs.close();
    rs = ps_c1.executeQuery();
    rs.last();
    assertEquals(5,rs.getInt(1));
    rs.close();
    rs = ps_c1.executeQuery();
    rs.absolute(3);
    assertEquals(3,rs.getInt(1));
    rs.next();
    assertEquals(4,rs.getInt(1));
    rs.close();
    ps_c1.close();
    // lets try a table with more than 64 rows.
    s.executeUpdate("create table tab1 (a int)");
    PreparedStatement is = conn.prepareStatement("insert into tab1 values (?)");
    for (int i = 1; i <= 70; i++) {
        is.setInt(1, i);
        is.executeUpdate();
    }
    ps_c1 = conn.prepareStatement("select * from tab1",ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_READ_ONLY);
    rs = ps_c1.executeQuery();
    rs.first();
    assertEquals(1,rs.getInt(1));
    rs.last();
    assertEquals(70,rs.getInt(1));
    rs.absolute(65);
    assertEquals(65,rs.getInt(1));
    rs.absolute(-1);
    assertEquals(70,rs.getInt(1));
    rs.close();
    ps_c1.close();
    // try sensitive scroll cursors bug 4677
    ps_c1 = conn.prepareStatement("select * from t1",ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
    rs = ps_c1.executeQuery();
    rs.close();
    ps_c1.close();
    ps_c1 = conn.prepareStatement("select * from t1 for update",ResultSet.TYPE_SCROLL_SENSITIVE,
            ResultSet.CONCUR_UPDATABLE);
    rs.close();
    s.executeUpdate("drop table tab1");
    // defect 5225, outer joins returning NULLS
    s.executeUpdate("create table tab1(i1 bigint not null, c1 varchar(64) not null)");
    s.executeUpdate("create table tab2 (i2 bigint not null, c2 varchar(64) not null)");
    s.executeUpdate("insert into tab1 values (1, 'String 1')");
    s.executeUpdate("insert into tab1 values (2, 'String 2')");
    s.executeUpdate("insert into tab2 values (1, 'String 1')");
    s.executeUpdate("insert into tab2 values (3, 'String 3')");
    rs = s.executeQuery("select c1 from tab1 right outer join tab2 on (i1=i2)");
    JDBC.assertFullResultSet(rs, new String[][] {{"String 1"},{null}});
    rs = s.executeQuery("select c2 from tab1 right outer join tab2 on (i1=i2)");
    JDBC.assertFullResultSet(rs, new String[][] {{"String 1"},{"String 3"}});
    // left outer join
    rs = s.executeQuery("select c1 from tab1 left outer join tab2 on (i1=i2)");
    JDBC.assertFullResultSet(rs, new String[][] {{"String 1"},{"String 2"}});
    rs = s.executeQuery("select c2 from tab1 left outer join tab2 on (i1=i2)");
    JDBC.assertFullResultSet(rs, new String[][] {{"String 1"},{null}});
    s.executeUpdate("drop table t");
    s.executeUpdate("drop table tab1");
    s.executeUpdate("drop table tab2");            
}
 

private void compareResultSets(ResultSet expected, ResultSet actual) throws Exception {
    if (expected == null) {
        if (actual != null) {
            fail("Expected null result set, actual was not null.");
        } else {
            return;
        }
    } else if (actual == null) {
        fail("Expected non-null actual result set.");
    }

    expected.last();

    int expectedRows = expected.getRow();

    actual.last();

    int actualRows = actual.getRow();

    assertEquals(expectedRows, actualRows);

    ResultSetMetaData metadataExpected = expected.getMetaData();
    ResultSetMetaData metadataActual = actual.getMetaData();

    assertEquals(metadataExpected.getColumnCount(), metadataActual.getColumnCount());

    for (int i = 0; i < metadataExpected.getColumnCount(); i++) {
        assertEquals(metadataExpected.getColumnName(i + 1), metadataActual.getColumnName(i + 1));
        assertEquals(metadataExpected.getColumnType(i + 1), metadataActual.getColumnType(i + 1));
        assertEquals(metadataExpected.getColumnClassName(i + 1), metadataActual.getColumnClassName(i + 1));
    }

    expected.beforeFirst();
    actual.beforeFirst();

    StringBuilder messageBuf = null;

    while (expected.next() && actual.next()) {

        if (messageBuf != null) {
            messageBuf.append("\n");
        }

        for (int i = 0; i < metadataExpected.getColumnCount(); i++) {
            if (expected.getObject(i + 1) == null && actual.getObject(i + 1) == null) {
                continue;
            }

            if ((expected.getObject(i + 1) == null && actual.getObject(i + 1) != null)
                    || (expected.getObject(i + 1) != null && actual.getObject(i + 1) == null)
                    || (!expected.getObject(i + 1).equals(actual.getObject(i + 1)))) {
                if ("COLUMN_DEF".equals(metadataExpected.getColumnName(i + 1))
                        && (expected.getObject(i + 1) == null && actual.getString(i + 1).length() == 0)
                        || (expected.getString(i + 1).length() == 0 && actual.getObject(i + 1) == null)) {
                    continue; // known bug with SHOW FULL COLUMNS, and we
                             // can't distinguish between null and ''
                             // for a default
                }

                if ("CHAR_OCTET_LENGTH".equals(metadataExpected.getColumnName(i + 1))) {
                    if (((com.mysql.jdbc.ConnectionImpl) this.conn).getMaxBytesPerChar(
                            CharsetMapping.getJavaEncodingForMysqlCharset(((com.mysql.jdbc.Connection) this.conn).getServerCharset())) > 1) {
                        continue; // SHOW CREATE and CHAR_OCT *will* differ
                    }
                }

                if (messageBuf == null) {
                    messageBuf = new StringBuilder();
                } else {
                    messageBuf.append("\n");
                }

                messageBuf.append("On row " + expected.getRow() + " ,for column named " + metadataExpected.getColumnName(i + 1) + ", expected '"
                        + expected.getObject(i + 1) + "', found '" + actual.getObject(i + 1) + "'");

            }
        }
    }

    if (messageBuf != null) {
        fail(messageBuf.toString());
    }
}
 

@Test(expected = SQLFeatureNotSupportedException.class)
public void assertLast() throws SQLException {
    for (ResultSet each : resultSets) {
        each.last();
    }
}
 

public static int countRecords(ResultSet resultSet)
{
    int rowCount = 0;

    //Determine rs size
    if (resultSet != null)
    {
        try
        {
            int currentIndex = resultSet.getRow();

            //Go to the end and get that row number
            if (resultSet.last())
            {
                rowCount = resultSet.getRow();
            }

            //Put the cursor back
            if (currentIndex > 0)
            {
                resultSet.absolute(currentIndex);
            }
            else
            {
                resultSet.beforeFirst();
            }
        }
        catch (SQLException ex)
        {
            //TODO: Decide whether if absolute() not be supported, try first() as a last resort??
            try
            {
                resultSet.first();
            }
            catch (SQLException se)
            {
                //we won't try anymore.
            }
        }
    }

    return rowCount;
}
 

/**
 * Position on the last row of the specified ResultSet
 * and return that row to the user.
 *
 * @param rs	The specified ResultSet.
 *
 * @return	The last row of the ResultSet.
 *
 * @exception	SQLException thrown on error.
 *				(last() not supported pre-JDBC2.0)
 */
ijResult last(ResultSet rs)
	throws SQLException
{
       checkScrollableCursor(rs, "LAST");
	return new ijRowResult(rs, rs.last());
}