private void assertState( ResultSet rs, boolean beforeFirst, boolean first, boolean last, boolean afterLast) throws SQLException { assertEquals(beforeFirst, rs.isBeforeFirst()); assertEquals(first, rs.isFirst()); assertEquals(last, rs.isLast()); assertEquals(afterLast, rs.isAfterLast()); }
/** @throws Exception If failed. */ public void testNavigation() throws Exception { ResultSet rs = stmt.executeQuery("select * from TestObject where id > 0"); assertTrue(rs.isBeforeFirst()); assertFalse(rs.isAfterLast()); assertFalse(rs.isFirst()); assertFalse(rs.isLast()); assertEquals(0, rs.getRow()); assertTrue(rs.next()); assertFalse(rs.isBeforeFirst()); assertFalse(rs.isAfterLast()); assertTrue(rs.isFirst()); assertFalse(rs.isLast()); assertEquals(1, rs.getRow()); assertTrue(rs.next()); assertFalse(rs.isBeforeFirst()); assertFalse(rs.isAfterLast()); assertFalse(rs.isFirst()); assertFalse(rs.isLast()); assertEquals(2, rs.getRow()); assertTrue(rs.next()); assertFalse(rs.isBeforeFirst()); assertFalse(rs.isAfterLast()); assertFalse(rs.isFirst()); assertTrue(rs.isLast()); assertEquals(3, rs.getRow()); assertFalse(rs.next()); assertFalse(rs.isBeforeFirst()); assertTrue(rs.isAfterLast()); assertFalse(rs.isFirst()); assertFalse(rs.isLast()); assertEquals(0, rs.getRow()); }
/** @return lista wszystkich wiadomosci (nieposortowana) */ public List<News> getAllNews() { List<News> news = new ArrayList<News>(); try { ResultSet prs = DB.prepareStatement("SELECT * FROM news").executeQuery(); prs.first(); while (!prs.isAfterLast()) { News temp = News.factory(); int newsId = prs.getInt(1); temp.setId(prs.getInt(1)); temp.setContent(prs.getString(2)); temp.setDate(prs.getDate(3)); temp.setPosition(prs.getInt(4)); System.out.println(temp.getId()); System.out.println(temp.getContent()); news.add(temp); prs.next(); } } catch (SQLException e) { e.printStackTrace(); } return news; }
/** ensures two tables (or views, that is) have the same content */ private void ensureEqualContent(String tableNameLHS, String tableNameRHS) throws SQLException { ResultSet lhs = m_statement.executeQuery("SELECT * FROM \"" + tableNameLHS + "\""); ResultSet rhs = m_statement.executeQuery("SELECT * FROM \"" + tableNameRHS + "\""); ResultSetMetaData meta = lhs.getMetaData(); while (lhs.next() && rhs.next()) { for (int col = 1; col <= meta.getColumnCount(); ++col) { assertEquals( "table content does not match: cp. " + tableNameLHS + "-" + tableNameRHS + ", row " + lhs.getRow() + ", col " + col, lhs.getObject(col), rhs.getObject(col)); } } // lhs should be after last, rhs still on last assertTrue( "row count does not match: " + tableNameLHS + "-" + tableNameRHS, lhs.isAfterLast() && rhs.isLast()); }
/** * Retrieves a List of Discussion Posts for a given Thread Id * * @param threadId The Id of the Thread to query * @return A List of Discussion Posts for the Thread */ public List<DiscussionPost> getPosts(int threadId) { ArrayList<DiscussionPost> posts = new ArrayList<>(); try { // Create a prepared statement PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM DiscussionPosts WHERE ThreadId = ?"); // Set the required parameters adn execute pstmt.setInt(1, threadId); ResultSet rs = pstmt.executeQuery(); // Retrieve the results and add to the list if (rs.isBeforeFirst()) { while (!rs.isAfterLast()) { DiscussionPost post = DiscussionPost.fromResultSet(rs); if (post != null) posts.add(post); } } } catch (Exception e) { logger.log(Level.SEVERE, "SQL Error", e); } return posts; }
/** * Retrieves all of the Discussion Threads associated with a Group Id * * @param groupId The Group Id to get Threads for * @return A List of Discussion Threads that belong to the Group */ public List<DiscussionThread> getThreads(int groupId) { ArrayList<DiscussionThread> threads = new ArrayList<>(); try { // Create a prepared statement PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM DiscussionThreads WHERE GroupId = ?"); // Set the required parameters and execute pstmt.setInt(1, groupId); ResultSet rs = pstmt.executeQuery(); // Get the results and add to the list if (rs.isBeforeFirst()) { while (!rs.isAfterLast()) { DiscussionThread thread = DiscussionThread.fromResultSet(rs); if (thread != null) { threads.add(thread); } } } } catch (Exception e) { logger.log(Level.SEVERE, "SQL Error", e); } return threads; }
@Override public boolean isAfterLast() throws SQLException { try { return _res.isAfterLast(); } catch (SQLException e) { handleException(e); return false; } }
private void testBeforeFirstAfterLast() throws SQLException { stat.executeUpdate("create table test(id int)"); stat.executeUpdate("insert into test values(1)"); // With a result ResultSet rs = stat.executeQuery("select * from test"); assertTrue(rs.isBeforeFirst()); assertFalse(rs.isAfterLast()); rs.next(); assertFalse(rs.isBeforeFirst()); assertFalse(rs.isAfterLast()); rs.next(); assertFalse(rs.isBeforeFirst()); assertTrue(rs.isAfterLast()); rs.close(); // With no result rs = stat.executeQuery("select * from test where 1 = 2"); assertFalse(rs.isBeforeFirst()); assertFalse(rs.isAfterLast()); rs.next(); assertFalse(rs.isBeforeFirst()); assertFalse(rs.isAfterLast()); rs.close(); stat.execute("drop table test"); }
public static void main(String[] args) { try { try (Connection conn = getConnection()) { String sql = "select id, name from student"; // TYPE_SCROLL_SENSITIVE == 结果集可以滚动且对数据库变化不敏感; // CONCUR_UPDATABLE == 且结果集能够应用于更新数据库; Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); // get 可更新的 结果集 ResultSet rs = stat.executeQuery(sql); int rowno; while (true) { rowno = rs.getRow(); if (rowno < 1) { rs.absolute(1); // 将游标设置到指定的行号上 } if (rs.getRow() % 2 != 0) { rs.updateString("name", rs.getString("name") + "_odd"); } else { rs.updateString("name", rs.getString("name") + "_even"); } rs.updateRow(); // attention for rs.getString not changing row cursor System.out.println("row[" + rs.getRow() + "] = " + rs.getString(2)); // 将游标向后或向前移动多行: rs.relative(n); n为正数, 向前移动;或负数, 向后移动; n为0, 不移动; rs.relative(1); if (rs.isAfterLast()) { break; } } stat.close(); conn.close(); } } catch (Exception e) { e.printStackTrace(); } }
@Override public Collection<JLGGroupData> extractData(ResultSet rs) throws SQLException, DataAccessException { List<JLGGroupData> groups = new ArrayList<>(); JLGGroupData group = null; int groupIndex = 0; boolean isEndOfRecords = false; // move cursor to first row. final boolean isNotEmtyResultSet = rs.next(); if (isNotEmtyResultSet) { while (!isEndOfRecords) { group = groupSavingsDataMapper.mapRowData(rs, groupIndex++); groups.add(group); isEndOfRecords = rs.isAfterLast(); } } return groups; }
// Load languages public void loadLanguages(javax.swing.JComboBox... cboLanguages) { logManager.logInfo("Entering 'loadUserRoles(javax.swing.JComboBox... cboUserRoles)' method"); String language = ""; try { sqlString = queryBuilder.sqlSelectLanguages(); resultset = executeQuery.executeSelect(sqlString); if (resultset.next()) { while (!resultset.isAfterLast()) { language = resultset.getString("lang"); // Insert language into combo box for (JComboBox cboLanguage : cboLanguages) { cboLanguage.addItem(language); } resultset.next(); } } } catch (Exception exception) { logManager.logError( "Exception was thrown and caught in 'loadUserRoles(javax.swing.JComboBox... cboUserRoles)' method"); } logManager.logInfo("Exiting 'loadUserRoles(javax.swing.JComboBox... cboUserRoles)' method"); }
public JLGGroupData mapRowData(ResultSet rs, int rowNum) throws SQLException { final List<JLGClientData> clients = new ArrayList<>(); final JLGGroupData group = this.mapRow(rs, rowNum); final Long previousGroupId = group.getGroupId(); // first client row of new group JLGClientData client = clientSavingsDataMapper.mapRowData(rs, rowNum); clients.add(client); // if its not after last row loop while (!rs.isAfterLast()) { final Long groupId = JdbcSupport.getLong(rs, "groupId"); if (previousGroupId != null && groupId.compareTo(previousGroupId) != 0) { // return for next group details return JLGGroupData.withClients(group, clients); } client = clientSavingsDataMapper.mapRowData(rs, rowNum); clients.add(client); } return JLGGroupData.withClients(group, clients); }
public void connect() { try { String driver = "sun.jdbc.odbc.JdbcOdbcDriver"; Class.forName(driver); String dbase = "jdbc:odbc:db1"; con = DriverManager.getConnection(dbase); st = con.createStatement(); String sql = "select * from Table1"; rs = st.executeQuery(sql); // displays everything in the database while (rs.isAfterLast()) { String fname = rs.getString("Fname"); String lname = rs.getString("Lname"); String book = rs.getString("Book"); System.out.println(fname + " " + lname + " " + book); } } catch (Exception ex) { } }
// Load languages public void loadOrganisations(javax.swing.JComboBox... cboOrganisations) { logManager.logInfo( "Entering 'loadOrganisations(javax.swing.JComboBox... cboOrganisations)' method"); String organisation = ""; try { sqlString = queryBuilder.sqlSelectOrganisations(); resultset = executeQuery.executeSelect(sqlString); if (resultset.next()) { while (!resultset.isAfterLast()) { organisation = resultset.getString("org"); // Insert organisations into combo box for (JComboBox cboOrganisation : cboOrganisations) { cboOrganisation.addItem(organisation); } resultset.next(); } } } catch (Exception exception) { logManager.logError( "Exception was thrown and caught in 'loadOrganisations(javax.swing.JComboBox... cboOrganisations)' method"); } logManager.logInfo( "Exiting 'loadOrganisations(javax.swing.JComboBox... cboOrganisations)' method"); }
/** Compute split-per-bucket (single split for all shards in a bucket). */ private BucketShards computeMerged() throws SQLException { if (resultSet.isAfterLast()) { return endOfData(); } if (first) { first = false; if (!resultSet.next()) { return endOfData(); } } int bucketNumber = resultSet.getInt("bucket_number"); ImmutableSet.Builder<ShardNodes> shards = ImmutableSet.builder(); do { UUID shardUuid = uuidFromBytes(resultSet.getBytes("shard_uuid")); int bucket = resultSet.getInt("bucket_number"); Set<String> nodeIdentifiers = ImmutableSet.of(getBucketNode(bucket)); shards.add(new ShardNodes(shardUuid, nodeIdentifiers)); } while (resultSet.next() && resultSet.getInt("bucket_number") == bucketNumber); return new BucketShards(OptionalInt.of(bucketNumber), shards.build()); }
@Override public long size() { if (size != -1) { return size; } try { // save cursor pos int old = rs.isBeforeFirst() ? -1 : rs.isAfterLast() ? -2 : rs.getRow(); // find size rs.last(); size = rs.getRow(); // set back cursor if (old == -1) { rs.beforeFirst(); } else if (old == -2) { rs.afterLast(); } else if (old != 0) { rs.absolute(old); } return size; } catch (SQLException e) { throw new RuntimeException(e); } }
// Load all users public void loadUsers(javax.swing.JTable tblUsers, javax.swing.table.DefaultTableModel tbmUsers) { logManager.logInfo( "Entering 'loadUsers(javax.swing.JTable tblUsers, javax.swing.table.DefaultTableModel tbmUsers)' method"); String firstName = ""; String lastName = ""; String middleName = ""; String userRole = ""; String organisation = ""; String language = ""; int userNumber = 0; int userIndex = 0; int userId = 0; boolean isActive = false; try { sqlString = queryBuilder.sqlSelectUsers(); resultset = executeQuery.executeSelect(sqlString); // Add columns to model insertColumnsToModel( tblUsers, tbmUsers, "Number", "First Name", "Last Name", "Middle Name", "User Role", "Organisation", "Language", "Active"); // Set row count to 0 tbmUsers.setRowCount(0); if (resultset.next()) { while (!resultset.isAfterLast()) { firstName = resultset.getString("fname"); // index 1 lastName = resultset.getString("lname"); // index 2 middleName = resultset.getString("mname"); // index 3 userRole = resultset.getString("rname"); // index 4 organisation = resultset.getString("org"); // index 5 language = resultset.getString("langname"); // index 6 isActive = resultset.getInt("active") != 0 ? true : false; // index 7 userId = resultset.getInt("user_id"); // index 0 // Insert row tbmUsers.insertRow(userIndex, new Object[] {null}); // Populate rows tblUsers.setValueAt(userId, userIndex, 0); tblUsers.setValueAt(firstName, userIndex, 1); tblUsers.setValueAt(lastName, userIndex, 2); tblUsers.setValueAt(middleName, userIndex, 3); tblUsers.setValueAt(userRole, userIndex, 4); tblUsers.setValueAt(organisation, userIndex, 5); tblUsers.setValueAt(language, userIndex, 6); tblUsers.setValueAt(new JCheckBox("", isActive), userIndex, 7); resultset.next(); } } } catch (Exception exception) { logManager.logError( "Exception was thrown and caught in 'loadUsers(javax.swing.JTable tblUsers, javax.swing.table.DefaultTableModel tbmUsers)' method"); } logManager.logInfo( "Exiting 'loadUsers(javax.swing.JTable tblUsers, javax.swing.table.DefaultTableModel tbmUsers)' method"); }
/** * General test of scrollable cursor functionality. * * <p>When running on SQL Server this test will exercise MSCursorResultSet. When running on Sybase * this test will exercise CachedResultSet. */ public void testCachedCursor() throws Exception { try { dropTable("jTDS_CachedCursorTest"); Statement stmt = con.createStatement(); stmt.execute( "CREATE TABLE jTDS_CachedCursorTest " + "(key1 int NOT NULL, key2 char(4) NOT NULL," + "data varchar(255))\r\n" + "ALTER TABLE jTDS_CachedCursorTest " + "ADD CONSTRAINT PK_jTDS_CachedCursorTest PRIMARY KEY CLUSTERED" + "( key1, key2)"); for (int i = 1; i <= 16; i++) { assertEquals( 1, stmt.executeUpdate( "INSERT INTO jTDS_CachedCursorTest VALUES(" + i + ", 'XXXX','LINE " + i + "')")); } stmt.close(); stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery("SELECT * FROM jTDS_CachedCursorTest ORDER BY key1"); assertNotNull(rs); assertEquals(null, stmt.getWarnings()); assertTrue(rs.isBeforeFirst()); assertTrue(rs.first()); assertEquals(1, rs.getInt(1)); assertTrue(rs.isFirst()); assertTrue(rs.last()); assertEquals(16, rs.getInt(1)); assertTrue(rs.isLast()); assertFalse(rs.next()); assertTrue(rs.isAfterLast()); rs.beforeFirst(); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); rs.afterLast(); assertTrue(rs.previous()); assertEquals(16, rs.getInt(1)); assertTrue(rs.absolute(8)); assertEquals(8, rs.getInt(1)); assertTrue(rs.relative(-1)); assertEquals(7, rs.getInt(1)); rs.updateString(3, "New line 7"); rs.updateRow(); // assertTrue(rs.rowUpdated()); // MS API cursors appear not to support this rs.moveToInsertRow(); rs.updateInt(1, 17); rs.updateString(2, "XXXX"); rs.updateString(3, "LINE 17"); rs.insertRow(); rs.moveToCurrentRow(); rs.last(); // assertTrue(rs.rowInserted()); // MS API cursors appear not to support this Statement stmt2 = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs2 = stmt2.executeQuery("SELECT * FROM jTDS_CachedCursorTest ORDER BY key1"); rs.updateString(3, "NEW LINE 17"); rs.updateRow(); assertTrue(rs2.last()); assertEquals(17, rs2.getInt(1)); assertEquals("NEW LINE 17", rs2.getString(3)); rs.deleteRow(); rs2.refreshRow(); assertTrue(rs2.rowDeleted()); rs2.close(); stmt2.close(); rs.close(); stmt.close(); } finally { dropTable("jTDS_CachedCursorTest"); } }
public boolean isAfterLast() throws SQLException { return rs.isAfterLast(); }
private void testScroll() throws SQLException { Connection conn = getConnection(); Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)"); stat.execute("INSERT INTO TEST VALUES(1, 'Hello'), (2, 'World'), (3, 'Test')"); ResultSet rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID"); assertTrue(rs.isBeforeFirst()); assertFalse(rs.isAfterLast()); assertEquals(0, rs.getRow()); rs.next(); assertFalse(rs.isBeforeFirst()); assertFalse(rs.isAfterLast()); assertEquals(1, rs.getInt(1)); assertEquals(1, rs.getRow()); rs.next(); assertThrows(ErrorCode.RESULT_SET_READONLY, rs).insertRow(); assertFalse(rs.isBeforeFirst()); assertFalse(rs.isAfterLast()); assertEquals(2, rs.getInt(1)); assertEquals(2, rs.getRow()); rs.next(); assertFalse(rs.isBeforeFirst()); assertFalse(rs.isAfterLast()); assertEquals(3, rs.getInt(1)); assertEquals(3, rs.getRow()); assertFalse(rs.next()); assertFalse(rs.isBeforeFirst()); assertTrue(rs.isAfterLast()); assertEquals(0, rs.getRow()); assertTrue(rs.first()); assertEquals(1, rs.getInt(1)); assertEquals(1, rs.getRow()); assertTrue(rs.last()); assertEquals(3, rs.getInt(1)); assertEquals(3, rs.getRow()); assertTrue(rs.relative(0)); assertEquals(3, rs.getRow()); assertTrue(rs.relative(-1)); assertEquals(2, rs.getRow()); assertTrue(rs.relative(1)); assertEquals(3, rs.getRow()); assertFalse(rs.relative(100)); assertTrue(rs.isAfterLast()); assertFalse(rs.absolute(0)); assertEquals(0, rs.getRow()); assertTrue(rs.absolute(1)); assertEquals(1, rs.getRow()); assertTrue(rs.absolute(2)); assertEquals(2, rs.getRow()); assertTrue(rs.absolute(3)); assertEquals(3, rs.getRow()); assertFalse(rs.absolute(4)); assertEquals(0, rs.getRow()); // allowed for compatibility assertFalse(rs.absolute(0)); assertTrue(rs.absolute(3)); assertEquals(3, rs.getRow()); assertTrue(rs.absolute(-1)); assertEquals(3, rs.getRow()); assertFalse(rs.absolute(4)); assertTrue(rs.isAfterLast()); assertFalse(rs.absolute(5)); assertTrue(rs.isAfterLast()); assertTrue(rs.previous()); assertEquals(3, rs.getRow()); assertTrue(rs.previous()); assertEquals(2, rs.getRow()); conn.close(); }