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);
}
}
