public static void queryBookAuthor(Connection conn) {
   RowSetFactory factory;
   try {
     // Create a new RowSetFactory
     factory = RowSetProvider.newFactory();
     // Create a CachedRowSet object using the factory
     bookAuthors = factory.createCachedRowSet();
     // Alternatively opulate the CachedRowSet connection settings
     // crs.setUsername(createConn.getUsername());
     // crs.setPassword(createConn.getPassword());
     // crs.setUrl(createConn.getJdbcUrl());
     // Populate a query that will obtain the data that will be used
     bookAuthors.setCommand("SELECT ID, LASTNAME, FIRSTNAME FROM BOOK_AUTHOR");
     bookAuthors.execute(conn);
     // You can now work with the object contents in a disconnected state
     while (bookAuthors.next()) {
       System.out.println(
           bookAuthors.getString(1)
               + ": "
               + bookAuthors.getString(2)
               + ", "
               + bookAuthors.getString(3));
     }
   } catch (SQLException ex) {
     ex.printStackTrace();
   }
 }
Example #2
0
 public CachedRowSet query(String sql, int pageSize, int page) throws Exception {
   // 加载驱动
   Class.forName(driver);
   try (
   // 获取数据库连接
   Connection conn = DriverManager.getConnection(url, user, pass);
       Statement stmt = conn.createStatement();
       ResultSet rs = stmt.executeQuery(sql)) {
     // 使用RowSetProvider创建RowSetFactory
     RowSetFactory factory = RowSetProvider.newFactory();
     // 创建默认的CachedRowSet实例
     CachedRowSet cachedRs = factory.createCachedRowSet();
     // 设置每页显示pageSize条记录
     cachedRs.setPageSize(pageSize);
     // 使用ResultSet装填RowSet,设置从第几条记录开始
     cachedRs.populate(rs, (page - 1) * pageSize + 1);
     return cachedRs;
   }
 }
Example #3
0
 public static CachedRowSet executePreparedQuery(
     final PreparedStatementBuilder builder, String dataSourceName, String repositoryName)
     throws Exception {
   Connection conn = null;
   PreparedStatement ps = null;
   try {
     conn = getConnection(dataSourceName, repositoryName);
     RowSetFactory rowSetFactory = RowSetProvider.newFactory();
     CachedRowSet crs = rowSetFactory.createCachedRowSet();
     ps = builder.build(conn);
     // FIXME: transition this log statement to DEBUG level when appropriate
     if (logger.isInfoEnabled()) {
       logger.info("prepared statement=" + ps.toString());
     }
     try (ResultSet resultSet = ps.executeQuery()) {
       crs.populate(resultSet);
     }
     return crs;
   } catch (SQLException sqle) {
     SQLException tempException = sqle;
     while (null != tempException) { // SQLExceptions can be chained. Loop to log all.
       logger.debug("SQL Exception: " + sqle.getLocalizedMessage());
       tempException = tempException.getNextException();
     }
     throw new RuntimeException("SQL Exception in executePreparedQuery: ", sqle);
   } finally {
     try {
       if (ps != null) {
         ps.close();
       }
       if (conn != null) {
         conn.close();
       }
     } catch (SQLException sqle) {
       logger.debug(
           "SQL Exception closing statement/connection in executePreparedQuery: "
               + sqle.getLocalizedMessage());
       return null;
     }
   }
 }
Example #4
0
  public static CachedRowSet executeQuery(String dataSourceName, String repositoryName, String sql)
      throws Exception {
    Connection conn = null;
    Statement stmt = null;
    try {
      conn = getConnection(dataSourceName, repositoryName);
      stmt = conn.createStatement();

      RowSetFactory rowSetFactory = RowSetProvider.newFactory();
      CachedRowSet crs = rowSetFactory.createCachedRowSet();

      stmt = conn.createStatement();
      try (ResultSet resultSet = stmt.executeQuery(sql)) {
        crs.populate(resultSet);
      }
      return crs;
    } catch (SQLException sqle) {
      SQLException tempException = sqle;
      while (null != tempException) { // SQLExceptions can be chained. Loop to log all.
        logger.debug("SQL Exception: " + sqle.getLocalizedMessage());
        tempException = tempException.getNextException();
      }
      throw new RuntimeException("SQL Exception in executeQuery: ", sqle);
    } finally {
      try {
        if (stmt != null) {
          stmt.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (SQLException sqle) {
        logger.debug(
            "SQL Exception closing statement/connection in executeQuery: "
                + sqle.getLocalizedMessage());
        return null;
      }
    }
  }
  public static void queryAuthorWork(Connection conn) {
    RowSetFactory factory;
    try {

      // Create a new RowSetFactory
      factory = RowSetProvider.newFactory();
      // Create a CachedRowSet object using the factory
      authorWork = factory.createCachedRowSet();
      // Alternatively opulate the CachedRowSet connection settings
      // crs.setUsername(createConn.getUsername());
      // crs.setPassword(createConn.getPassword());
      // crs.setUrl(createConn.getJdbcUrl());
      // Populate a query that will obtain the data that will be used
      authorWork.setCommand("SELECT ID, AUTHOR_ID, BOOK_ID FROM AUTHOR_WORK");
      authorWork.execute(conn);
      // You can now work with the object contents in a disconnected state
      while (authorWork.next()) {
        System.out.println(
            authorWork.getString(1) + ": " + authorWork.getInt(2) + " - " + authorWork.getInt(3));
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    }
  }
Example #6
0
 // FIXME: This method's code significantly overlaps that of executePrepareQuery(), above,
 // and the two could be refactored into a single method, if desired.
 public static List<CachedRowSet> executePreparedQueries(
     final List<PreparedStatementBuilder> builders,
     String dataSourceName,
     String repositoryName,
     Boolean executeWithinTransaction)
     throws Exception {
   Connection conn = null;
   PreparedStatement ps = null;
   List<CachedRowSet> results = new ArrayList<>();
   try {
     conn = getConnection(dataSourceName, repositoryName);
     if (executeWithinTransaction) {
       conn.setAutoCommit(false);
     }
     RowSetFactory rowSetFactory = RowSetProvider.newFactory();
     CachedRowSet crs = rowSetFactory.createCachedRowSet();
     int statementCount = 0;
     for (PreparedStatementBuilder builder : builders) {
       ps = builder.build(conn);
       // FIXME: transition this log statement to DEBUG level when appropriate
       if (logger.isInfoEnabled()) {
         statementCount++;
         logger.info("prepared statement " + statementCount + "=" + ps.toString());
       }
       // Try executing each statement, first as a query, then as an update
       try {
         ResultSet resultSet = ps.executeQuery();
         if (resultSet != null) {
           crs.populate(resultSet);
           results.add(crs);
         }
       } catch (Exception e) {
         int rowcount = ps.executeUpdate();
         // Throw uncaught exception here if update attempt also fails
       }
     }
     return results;
   } catch (SQLException sqle) {
     if (executeWithinTransaction && conn != null) {
       conn.rollback();
     }
     SQLException tempException = sqle;
     while (null != tempException) { // SQLExceptions can be chained. Loop to log all.
       logger.debug("SQL Exception: " + sqle.getLocalizedMessage());
       tempException = tempException.getNextException();
     }
     throw new RuntimeException("SQL Exception in executePreparedQueries: ", sqle);
   } finally {
     try {
       if (ps != null) {
         ps.close();
       }
       if (conn != null) {
         if (executeWithinTransaction) {
           conn.commit();
         }
         conn.close();
       }
     } catch (SQLException sqle) {
       logger.debug(
           "SQL Exception closing statement/connection in executePreparedQueries: "
               + sqle.getLocalizedMessage());
       return null;
     }
   }
 }