public void testScrollablePreparedStatement() throws Exception { Statement stmt = con.createStatement(); makeTestTables(stmt); makeObjects(stmt, 10); stmt.close(); PreparedStatement pstmt = con.prepareStatement( "SELECT * FROM #test", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = pstmt.executeQuery(); assertTrue(rs.isBeforeFirst()); while (rs.next()) {} // assertTrue( rs.isAfterLast() ); // This currently fails because the PreparedStatement // Doesn't know it needs to create a cursored ResultSet. // Needs some refactoring!! while (rs.previous()) {} // assertTrue( rs.isBeforeFirst() ); rs.close(); }
public static String getUser() { String s = ""; try { r = stmnt.executeQuery("SELECT * FROM log"); r.next(); s = r.getString(2); r.previous(); } catch (SQLException exepti) { } return s; }
public static int getItem() { int i = 0; try { r = stmnt.executeQuery("SELECT * FROM iflag"); r.next(); i = r.getInt(2); r.previous(); } catch (SQLException exepti) { } return i; }
public static int getCustId(String s) { ResultSet rs; int i = 0; try { rs = stmnt.executeQuery("Select c_id FROM customer WHERE c_name='" + s + "'"); rs.next(); i = rs.getInt(1); rs.previous(); rs.close(); } catch (SQLException e) { } return i; }
public Map<String, List> fetchHoldingItem(boolean cursor) throws SQLException { String holdingId = ""; String tempHoldingId = ""; List itemIds = new ArrayList(); Map<String, List> map = null; if (cursor) { while (holdingItemResultSet.next()) { holdingId = "who-" + holdingItemResultSet.getString("HOLDINGS_ID"); if (StringUtils.isNotEmpty(tempHoldingId) && !tempHoldingId.equals(holdingId)) { if (itemIds.size() > 0) { map = new HashMap<>(); map.put(tempHoldingId, itemIds); return map; } } itemIds.add("wio-" + holdingItemResultSet.getString("ITEM_ID")); tempHoldingId = holdingId; } } else { holdingItemResultSet.previous(); // while(holdingItemResultSet.previous()) { // break; // } while (holdingItemResultSet.next()) { holdingId = "who-" + holdingItemResultSet.getString("HOLDINGS_ID"); if (StringUtils.isNotEmpty(tempHoldingId) && !tempHoldingId.equals(holdingId)) { if (itemIds.size() > 0) { map = new HashMap<>(); map.put(tempHoldingId, itemIds); return map; } } itemIds.add("wio-" + holdingItemResultSet.getString("ITEM_ID")); tempHoldingId = holdingId; } } if (itemIds.size() > 0) { map = new HashMap<>(); map.put(tempHoldingId, itemIds); return map; } else { map = null; } holdingItemResultSet.close(); return map; }
/** 为借用 查询所有记录 */ public String[][] searchAllForUse() { Database DB = new Database(); AssetsBean aBean = new AssetsBean(); PersonBean pBean = new PersonBean(); String[][] sn = null; int row = 0; int i = 0; sql = "select * from AssetsTrjn where FromAcc ='设备借用' order by JourNo"; try { DB.OpenConn(); rs = DB.executeQuery(sql); if (rs.last()) { row = rs.getRow(); } if (row == 0) { sn = new String[1][6]; sn[0][0] = " "; sn[0][1] = " "; sn[0][2] = " "; sn[0][3] = " "; sn[0][4] = " "; sn[0][5] = " "; } else { sn = new String[row][6]; rs.first(); rs.previous(); while (rs.next()) { sn[i][0] = rs.getString("JourNo"); sn[i][1] = aBean.getAssetsName(rs.getString("AssetsID")); sn[i][2] = rs.getString("RegDate"); sn[i][3] = pBean.getPersonName(rs.getString("PersonID")); sn[i][4] = rs.getString("Use"); sn[i][5] = rs.getString("Other"); i++; } } } catch (Exception e) { } finally { DB.closeStmt(); DB.closeConn(); } return sn; }
public static void main(String args[]) { try { // Step 2: load driver Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); // Step 3: define the connection URL String url = "jdbc:odbc:personDSN"; // Step 4: establish the connection Connection con = DriverManager.getConnection(url); // Step 5: creating PrepareStatement by passing sql and // ResultSet’s constants so that the ResultSet that will // produce as a result of executing query will be // scrollable & updatable String sql = "SELECT * FROM Person"; PreparedStatement pStmt = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); // Step 6: execute the query ResultSet rs = pStmt.executeQuery(); // moving cursor forward i.e. first row rs.next(); // printing column "name" value of current row (first) System.out.println("moving cursor forward"); String name = rs.getString("Name"); System.out.println(name); // moving cursor forward i.e. on to second row rs.next(); // moving cursor backward i.e to first row rs.previous(); // printing column "name" value of current row (first) System.out.println("moving cursor backward"); name = rs.getString("Name"); System.out.println(name); // Step 8: close the connection con.close(); } catch (Exception sqlEx) { System.out.println(sqlEx); } } // end main
public void actionPerformed(ActionEvent e) { String a, b, c, f, g, h, j, k, l, m, n, o, p, tol = ""; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); con = DriverManager.getConnection("jdbc:odbc:db2"); stm = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); } catch (Exception error) { System.out.println(error); } if (e.getSource() == student) { name = "Student"; cl.show(pnc, "cstu"); try { rs = stm.executeQuery("Select * from Student"); rs.next(); t1.setText(rs.getString(1)); t2.setText(rs.getString(2)); t3.setText(rs.getString(3)); t4.setText(rs.getString(4)); t5.setText(rs.getString(5)); t6.setText(rs.getString(6)); } catch (Exception error) { System.out.println(error); } } if (e.getSource() == teacher) { name = "Teacher"; cl.show(pnc, "ctea"); try { rs = stm.executeQuery("Select * from Teacher"); rs.next(); t11.setText(rs.getString(1)); t12.setText(rs.getString(2)); t13.setText(rs.getString(3)); t14.setText(rs.getString(4)); } catch (Exception error) { System.out.println(error); } } if (e.getSource() == course) { name = "Course"; cl.show(pnc, "ccou"); try { rs = stm.executeQuery("Select * from Course"); rs.next(); t7.setText(rs.getString(1)); t8.setText(rs.getString(2)); t9.setText(rs.getString(3)); t0.setText(rs.getString(4)); } catch (Exception error) { System.out.println(error); } } if (e.getSource() == result) { name = "Result"; cl.show(pnc, "cres"); try { rs = stm.executeQuery("Select * from Result"); rs.next(); t15.setText(rs.getString(1)); t16.setText(rs.getString(2)); t17.setText(rs.getString(3)); t18.setText(rs.getString(4)); t19.setText(rs.getString(5)); t20.setText(rs.getString(6)); t21.setText(rs.getString(7)); t22.setText(rs.getString(8)); t23.setText(rs.getString(9)); t24.setText(rs.getString(10)); t25.setText(rs.getString(11)); t26.setText(rs.getString(12)); t27.setText(rs.getString(13)); } catch (Exception error) { System.out.println(error); } } if (e.getSource() == b6) { Vector cols = new Vector(); Vector rows = new Vector(); if (z == 1) { String sql = "Select * from " + co; try { rs = stm.executeQuery(sql); meta = rs.getMetaData(); for (int i = 1; i <= meta.getColumnCount(); i++) cols.addElement(meta.getColumnName(i)); while (rs.next()) { Vector currow = new Vector(); for (int i = 1; i <= meta.getColumnCount(); i++) currow.addElement(rs.getString(i)); rows.addElement(currow); } } catch (Exception ex) { System.out.print(ex); } tb = new JTable(rows, cols); js = new JScrollPane(tb); pne2.remove(js); pne2.add(blk, "bb"); cl2.show(pne2, "bb"); pne2.add(js, "jjs"); cl2.show(pne2, "jjs"); pne2.remove(blk); z = 0; } } if (e.getSource() == b1) // **** ADD BUTTON ****// { if (name == "Student") { a = t1.getText(); b = t2.getText(); c = t3.getText(); f = t4.getText(); g = t5.getText(); h = t6.getText(); tol = "Insert into Student values ('" + a + "','" + b + "','" + c + "','" + f + "','" + g + "','" + h + "')"; } if (name == "Teacher") { a = t11.getText(); b = t12.getText(); c = t13.getText(); f = t14.getText(); tol = "Insert into Teacher values ('" + a + "','" + b + "','" + c + "','" + f + "')"; } if (name == "Course") { a = t7.getText(); b = t8.getText(); c = t9.getText(); f = t0.getText(); tol = "Insert into Course values ('" + a + "','" + b + "','" + c + "','" + f + "')"; } if (name == "Result") { a = t15.getText(); b = t16.getText(); c = t17.getText(); f = t18.getText(); g = t19.getText(); h = t20.getText(); j = t21.getText(); k = t22.getText(); l = t23.getText(); m = t24.getText(); n = t25.getText(); o = t26.getText(); p = t27.getText(); tol = "Insert into Result values ('" + a + "','" + b + "','" + c + "','" + f + "','" + g + "','" + h + "','" + j + "','" + k + "','" + l + "','" + m + "','" + n + "','" + o + "','" + p + "')"; } try { stm.executeUpdate(tol); } catch (Exception error) { System.out.println(error); } } if (e.getSource() == b2) // **** DELETE BUTTON ****// { if (name == "Student") { b = t2.getText(); tol = "Delete from Student where Id = '" + b + "'"; } if (name == "Teacher") { b = t12.getText(); tol = "Delete from Teacher where Id = '" + b + "'"; } if (name == "Course") { b = t8.getText(); tol = "Delete from Course where Id = '" + b + "'"; } if (name == "Result") { b = t16.getText(); tol = "Delete from Result where Code = '" + b + "'"; } try { stm.executeUpdate(tol); } catch (Exception error) { System.out.println(error); } } if (e.getSource() == b3) // **** UPDATE BUTTON ****// { if (name == "Student") { a = t1.getText(); b = t2.getText(); c = t3.getText(); f = t4.getText(); g = t5.getText(); h = t6.getText(); tol = "Update Student set Name = '" + a + "', ID = '" + b + "', Dept = '" + c + "', CGPA = '" + f + "', Address = '" + g + "', Cell = '" + h + "' where ID = '" + b + "'"; } if (name == "Teacher") { a = t11.getText(); b = t12.getText(); c = t13.getText(); f = t14.getText(); tol = "Update Teacher set Name = '" + a + "', ID = '" + b + "', Dept = '" + c + "', Course = '" + f + "' where ID = '" + b + "'"; } if (name == "Course") { a = t7.getText(); b = t8.getText(); c = t9.getText(); f = t0.getText(); tol = "Update Course set Name = '" + a + "', Code = '" + b + "', Credit = '" + c + "', Prerecusite = '" + f + "' where Code = '" + b + "'"; } if (name == "Result") { a = t15.getText(); b = t16.getText(); c = t17.getText(); f = t18.getText(); g = t19.getText(); h = t20.getText(); j = t21.getText(); k = t22.getText(); l = t23.getText(); m = t24.getText(); n = t25.getText(); o = t26.getText(); p = t27.getText(); tol = "Update Result set Course = '" + a + "', Code = '" + b + "', Credit = '" + c + "', A = '" + f + "', B+ = '" + g + "', B = '" + h + "', C+ = '" + j + "', C = '" + k + "', D+ = '" + l + "', D = '" + m + "', F = '" + n + "', I = '" + o + "', W = '" + p + "' where Code = '" + b + "'"; // JOptionPane.showMessageDialog(null,tol,null,JOptionPane.PLAIN_MESSAGE); // tol = "Update Result set Course = '"+a+"', Code = '"+b+"', Credit = '"+c+"' where Code = // '"+b+"'"; } try { stm.executeUpdate(tol); } catch (Exception error) { System.out.println(error); } } if (e.getSource() == b4) // **** NEXT BUTTON ****// { if (name == "Student") { try { if (rs.next()) { t1.setText(rs.getString(1)); t2.setText(rs.getString(2)); t3.setText(rs.getString(3)); t4.setText(rs.getString(4)); t5.setText(rs.getString(5)); t6.setText(rs.getString(6)); } } catch (Exception error) { System.out.println(error); } } if (name == "Teacher") { try { if (rs.next()) { t11.setText(rs.getString(1)); t12.setText(rs.getString(2)); t13.setText(rs.getString(3)); t14.setText(rs.getString(4)); } } catch (Exception error) { System.out.println(error); } } if (name == "Course") { try { if (rs.next()) { t7.setText(rs.getString(1)); t8.setText(rs.getString(2)); t9.setText(rs.getString(3)); t0.setText(rs.getString(4)); } } catch (Exception error) { System.out.println(error); } } if (name == "Result") { try { if (rs.next()) { t15.setText(rs.getString(1)); t16.setText(rs.getString(2)); t17.setText(rs.getString(3)); t18.setText(rs.getString(4)); t19.setText(rs.getString(5)); t20.setText(rs.getString(6)); t21.setText(rs.getString(7)); t22.setText(rs.getString(8)); t23.setText(rs.getString(9)); t24.setText(rs.getString(10)); t25.setText(rs.getString(11)); t26.setText(rs.getString(12)); t27.setText(rs.getString(13)); } } catch (Exception error) { System.out.println(error); } } } if (e.getSource() == b5) // **** PREVIOUS BUTTON ****// { if (name == "Student") { try { if (rs.previous()) { t1.setText(rs.getString(1)); t2.setText(rs.getString(2)); t3.setText(rs.getString(3)); t4.setText(rs.getString(4)); t5.setText(rs.getString(5)); t6.setText(rs.getString(6)); } } catch (Exception error) { System.out.println(error); } } if (name == "Teacher") { try { if (rs.previous()) { t11.setText(rs.getString(1)); t12.setText(rs.getString(2)); t13.setText(rs.getString(3)); t14.setText(rs.getString(4)); } } catch (Exception error) { System.out.println(error); } } if (name == "Course") { try { if (rs.previous()) { t7.setText(rs.getString(1)); t8.setText(rs.getString(2)); t9.setText(rs.getString(3)); t0.setText(rs.getString(4)); } } catch (Exception error) { System.out.println(error); } } if (name == "Result") { try { if (rs.previous()) { t15.setText(rs.getString(1)); t16.setText(rs.getString(2)); t17.setText(rs.getString(3)); t18.setText(rs.getString(4)); t19.setText(rs.getString(5)); t20.setText(rs.getString(6)); t21.setText(rs.getString(7)); t22.setText(rs.getString(8)); t23.setText(rs.getString(9)); t24.setText(rs.getString(10)); t25.setText(rs.getString(11)); t26.setText(rs.getString(12)); t27.setText(rs.getString(13)); } } catch (Exception error) { System.out.println(error); } } } }
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); } }