コード例 #1
1
 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());
 }
コード例 #2
1
  /** @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());
  }
コード例 #3
0
ファイル: News.java プロジェクト: adaniluk/presentationJava
  /** @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;
  }
コード例 #4
0
  /** 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());
  }
コード例 #5
0
  /**
   * 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;
  }
コード例 #6
0
  /**
   * 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;
  }
コード例 #7
0
 @Override
 public boolean isAfterLast() throws SQLException {
   try {
     return _res.isAfterLast();
   } catch (SQLException e) {
     handleException(e);
     return false;
   }
 }
コード例 #8
0
 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");
 }
コード例 #9
0
  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();
    }
  }
コード例 #10
0
    @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;
    }
コード例 #11
0
ファイル: ManageUser.java プロジェクト: kchemweno/parable
 // 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");
 }
コード例 #12
0
    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);
    }
コード例 #13
0
ファイル: ab.java プロジェクト: abvvu/AdvancedSoftwareEng
  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) {

    }
  }
コード例 #14
0
ファイル: ManageUser.java プロジェクト: kchemweno/parable
 // 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");
 }
コード例 #15
0
ファイル: ShardIterator.java プロジェクト: cdma/presto
  /** 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());
  }
コード例 #16
0
 @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);
   }
 }
コード例 #17
0
ファイル: ManageUser.java プロジェクト: kchemweno/parable
  // 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");
  }
コード例 #18
0
 /**
  * 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");
   }
 }
コード例 #19
0
	public boolean isAfterLast() throws SQLException {
		return rs.isAfterLast();
	}
コード例 #20
0
  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();
  }