public static ResultSet Read() throws ResponseException { ResultSet rs = null; PreparedStatement statement = null; String query = "select * from unlocked;"; Connection connect = null; CachedRowSet crs = null; IConnection c = MiscUtil.getIConnection(); try (CachedRowSet crs2 = RowSetProvider.newFactory().createCachedRowSet()) { connect = c.init(); statement = connect.prepareStatement(query); rs = statement.executeQuery(); crs2.populate(rs); crs = crs2.createCopy(); } catch (SQLException e) { logger.error("SQL Error", e); throw new ResponseException("SQL Error", HttpServletResponse.SC_INTERNAL_SERVER_ERROR); } catch (ResponseException e) { throw e; } finally { try { if (statement != null) statement.close(); if (connect != null) connect.close(); } catch (SQLException e) { logger.error("SQL Error", e); } } return (crs); }
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(); } }
private void initiateJDBCRowSet() { try { jrs = RowSetProvider.newFactory().createJdbcRowSet(); jrs.setCommand(GET_EMPLOYEES); jrs.setUrl(url); jrs.setUsername(user); jrs.setPassword(password); jrs.execute(); jrs.addRowSetListener(new RowSetListenerImpl()); } catch (SQLException e) { System.out.println(e.getMessage()); } }
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; } }
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; } } }
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(); } }
// 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; } } }