1.描述问题
将查询数据库操作,抽成一个公共方法,如下:
public class DBPoolConnection {
private static ResultSet resultSet;
private static DruidPooledConnection conn = null;
private PreparedStatement ps = null;
private static DBPoolConnection dbPoolConnection = null;
private static DruidDataSource druidDataSource = null;
static {
Properties properties = new Properties();
try {
properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("database.properties"));
druidDataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties); //DruidDataSrouce工厂模式
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 数据库连接池单例
* @return
*/
public static synchronized DBPoolConnection getInstance(){
if (null == dbPoolConnection){
dbPoolConnection = new DBPoolConnection();
}
return dbPoolConnection;
}
/**
* 返回druid数据库连接
* @return
* @throws SQLException
*/
public DruidPooledConnection getConnection() throws SQLException {
return druidDataSource.getConnection();
}
/**
* @Description 查询的公共方法
*/
public ResultSet query(String sql,Object [] objects){
try {
DBPoolConnection dbPoolConnection = DBPoolConnection.getInstance();
conn = dbPoolConnection.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < objects.length; i++) {
ps.setObject(i,objects[i]);
}
resultSet = ps.executeQuery();
} catch (SQLException e) {
System.out.println("有异常");
e.printStackTrace();
}finally {
try {
if (null != ps) {
ps.close();
}
if (null != conn) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return resultSet;
}
}
当我写一个测试方法,如:
@Test
public void Test(){
Object [] Object = {};
DBPoolConnection dbPoolConnection = new DBPoolConnection();
ResultSet rs = null;
for (int i = 0; i < 2; i++) {
rs = dbPoolConnection.query("select id from logtable", Object);
}
String id = "";
if (null != rs) {
try {
if (rs.next()) {
rs.previous();
while (rs.next()){
id = rs.getString("id");
}
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if (null != rs) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
System.out.println("-------------获取数据:"+id);
}
执行后就提示:
java.sql.SQLException: Operation not allowed after ResultSet closed
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
at com.mysql.jdbc.ResultSet.checkClosed(ResultSet.java:666)
at com.mysql.jdbc.ResultSet.next(ResultSet.java:7274)
百度了一下,这个是因为提前关闭了连接。