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