@Override public Project getProject(int id) throws IlligalInputException { Project project = null; Statement stmt = null; try { stmt = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = stmt.executeQuery("SELECT * FROM project WHERE category_id=" + this.id + ";"); // TODO need refactor rs.relative(id + 1); String name = rs.getString("name"); String description = rs.getString("description"); int cost = rs.getInt("cost"); int collected = rs.getInt("collected"); Date date = rs.getDate("Deadline"); String history = rs.getString("history"); String demo = rs.getString("demo"); String faq = rs.getString("faq"); project = new Project(id, name, description, cost, collected, date, history, demo, faq); stmt.close(); } catch (SQLException e) { e.printStackTrace(); } finally { try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } return project; }
@Override public boolean relative(int rows) throws SQLException { try { return _res.relative(rows); } catch (SQLException e) { handleException(e); return false; } }
@Override public Attraction getAttractionById(int id) { Connection con = MySQLConnection.getWebInstance(); try (PreparedStatement pstm = con.prepareStatement(GET_ATTRACTION_BY_ID)) { pstm.setInt(1, id); ResultSet rs = pstm.executeQuery(); rs.relative(1); return extractAtraction(rs); } catch (SQLException ex) { rollback(con); throw new DBLayerException("Failed to get attraction by id=" + id, ex); } finally { commit(con); } }
@Override public void traceMarker() throws Exception { Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); try { statement.execute("select * from employee"); ResultSet rs = statement.getResultSet(); // need to position cursor on a valid row before calling relative(), at least for // sqlserver jdbc driver rs.next(); rs.getString(1); while (rs.relative(1)) { rs.getString(1); } } finally { statement.close(); } }
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(); } }
/** * 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 moveRelative(int num) throws SQLException { return resultSet.relative(num); }
public boolean relative(int rows) throws SQLException { return rs.relative(rows); }
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(); }
public static void main(String args[]) { try { String url; if (args.length == 0) url = "jdbc:virtuoso://localhost:1111"; else url = args[0]; Class.forName("virtuoso.jdbc3.Driver"); System.out.println("--------------------- Test of scrollable cursor -------------------"); System.out.print("Establish connection at " + url); Connection connection = DriverManager.getConnection(url, "dba", "dba"); if (connection instanceof virtuoso.jdbc3.VirtuosoConnection) System.out.println(" PASSED"); else { System.out.println(" FAILED"); System.exit(-1); } System.out.print("Create a Statement class attached to this connection"); Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); if (stmt instanceof virtuoso.jdbc3.VirtuosoStatement) System.out.println(" PASSED"); else { System.out.println(" FAILED"); System.exit(-1); } try { stmt.executeUpdate("drop table EX..DEMO"); } catch (Exception e) { } System.out.print("Execute CREATE TABLE"); if (stmt.executeUpdate("create table EX..DEMO (ID integer,FILLER integer,primary key(ID))") == 0) System.out.println(" PASSED"); else { System.out.println(" FAILED"); System.exit(-1); } System.out.print("Create a PStatement class attached to this connection"); PreparedStatement pstmt = connection.prepareStatement("INSERT INTO EX..DEMO(ID,FILLER) VALUES (?,?)"); System.out.println(" PASSED"); System.out.print("Execute INSERT INTO"); for (int i = 0; i < 100; i++) { pstmt.setInt(1, i); pstmt.setInt(2, i); if (pstmt.executeUpdate() != 1) { System.out.println(" FAILED"); System.exit(-1); } } System.out.println(" PASSED"); pstmt.close(); System.out.print("Execute SELECT"); stmt.setMaxRows(100); stmt.setFetchSize(10); stmt.execute("SELECT * from EX..DEMO"); System.out.println(" PASSED"); System.out.print("Get the result set"); ResultSet rs = stmt.getResultSet(); if (rs instanceof virtuoso.jdbc3.VirtuosoResultSet) { System.out.println(" PASSED"); } else { System.out.println(" FAILED"); System.exit(-1); } System.out.print("Execute the resultset.beforeFirst()"); rs.beforeFirst(); System.out.println(" PASSED"); System.out.print("Execute the resultset.next()"); for (int i = 0; i < 100; i++) { rs.next(); if (rs.getInt(2) != i) { System.out.println(" FAILED"); System.exit(-1); } } System.out.println(" PASSED"); System.out.print("Execute the resultset.afterLast()"); rs.afterLast(); System.out.println(" PASSED"); System.out.print("Execute the resultset.previous()"); for (int i = 99; i >= 0; i--) { rs.previous(); if (rs.getInt(2) != i) { System.out.println(" FAILED"); System.exit(-1); } } System.out.println(" PASSED"); System.out.print("Execute the resultset.first()"); rs.first(); System.out.println(" PASSED"); System.out.print("Execute the resultset.next()"); for (int i = 0; i < 100; i++) { if (rs.getInt(2) != i) { System.out.println(" FAILED"); System.exit(-1); } rs.next(); } System.out.println(" PASSED"); System.out.print("Execute the resultset.last()"); rs.last(); System.out.println(" PASSED"); System.out.print("Execute the resultset.previous()"); for (int i = 99; i >= 0; i--) { if (rs.getInt(2) != i) { System.out.println(" FAILED"); System.exit(-1); } rs.previous(); } System.out.println(" PASSED"); System.out.print("Execute the resultset.absolute(>0)"); for (int i = 0; i != 100; i++) { rs.absolute(i + 1); if (rs.getInt(2) != i) { System.out.println(" FAILED"); System.exit(-1); } } System.out.println(" PASSED"); System.out.print("Execute the resultset.absolute(<0)"); for (int i = -1, j = 99; i != -101; i--, j--) { rs.absolute(i); if (rs.getInt(2) != j) { System.out.println(" FAILED"); System.exit(-1); } } System.out.println(" PASSED"); System.out.print("Execute the resultset.absolute(50)"); rs.absolute(50); System.out.println(" PASSED"); System.out.print("Execute the resultset.relative(>0)"); for (int i = 50; i != 90; i++) { if (rs.getInt(2) != i - 1) { System.out.println(" FAILED"); System.exit(-1); } rs.relative(1); } System.out.println(" PASSED"); System.out.print("Execute the resultset.absolute(50)"); rs.absolute(50); System.out.println(" PASSED"); System.out.print("Execute the resultset.relative(<0)"); for (int i = 50; i != 10; i--) { if (rs.getInt(2) != i - 1) { System.out.println(" FAILED"); System.exit(-1); } rs.relative(-1); } System.out.println(" PASSED"); System.out.print("Execute the resultset.first()"); rs.first(); System.out.println(" PASSED"); System.out.print("Update rows in the table"); for (int i = 0; i != 2; i++) { rs.updateInt("FILLER", i * 2); rs.updateRow(); rs.refreshRow(); if (rs.getInt(2) != i * 2) { System.out.println(" FAILED"); System.exit(-1); } rs.next(); } System.out.println(" PASSED"); System.out.print("Execute DELETE"); pstmt = connection.prepareStatement("DELETE FROM EX..DEMO WHERE ID=?"); for (int i = 0; i < 100; i++) { pstmt.setInt(1, i); if (pstmt.executeUpdate() != 1) { System.out.println(" FAILED"); System.exit(-1); } } System.out.println(" PASSED"); pstmt.close(); stmt.close(); stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); System.out.print("Execute DROP TABLE"); if (stmt.executeUpdate("DROP TABLE EX..DEMO") == 0) System.out.println(" PASSED"); else { System.out.println(" FAILED"); System.exit(-1); } System.out.print("Close statement at " + url); stmt.close(); System.out.println(" PASSED"); System.out.print("Close connection at " + url); connection.close(); System.out.println(" PASSED"); System.out.println("-------------------------------------------------------------------"); System.exit(0); } catch (Exception e) { System.out.println(" FAILED"); e.printStackTrace(); System.exit(-1); } }