Example #1
1
  @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);
   }
 }
Example #4
0
 @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();
  }
Example #10
0
  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);
    }
  }