错误信息:org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting for idle object
Tomcat与数据库使用连接池,封装在一个类里面:
public class BaseDAO {
private Connection conn = null;
private ResultSet rs = null;
private Statement stmt = null;
private PreparedStatement pstmt = null;
//1)、获取数据库连接对象
private Connection getConnection(){
try {
Context context = new InitialContext();
DataSource dataSource = (DataSource)context.lookup("java:comp/env/jdbc/sms");
conn = dataSource.getConnection();
} catch (NamingException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return this.conn;
}
//2)、获得结果集
public ResultSet executeQuery(String sql){
try {
conn = getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
/*
* 执行一个查询操作,在这个查询中,对象数组中的每个元素值被用来作为查询语句的置换参数
*/
public ResultSet executeQuery(String sql,Object ... paramValues){
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
if(paramValues != null){
for (int i = 0; i < paramValues.length; i++) {
pstmt.setObject((i+1), paramValues[i]);
}
}
rs = pstmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
//3)、用来执行一个不需要置换参数的更新(插入、更新或删除)操作。
public int executeUpdate(String sql){
int affectedRows = 0;
try {
conn = getConnection();
stmt = conn.createStatement();
affectedRows = stmt.executeUpdate(sql);
} catch (Exception e) {
e.printStackTrace();
}finally{
closeAll(null,stmt,null,conn);
}
return affectedRows;
}
//用来执行一个需要置换参数的更新(插入、更新或删除)操作。
public int executeUpdate(String sql,Object ... paramValues){
int affectedRows = 0;
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
if(paramValues != null){
for (int i = 0; i < paramValues.length; i++) {
pstmt.setObject((i+1), paramValues[i]);
}
}
affectedRows = pstmt.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
closeAll(null,null,pstmt,conn);
}
return affectedRows;
}
//(5)通用查询工具方法
public List<Map<Object,Object>> query(String sql,Object ...paramValues){
List<Map<Object,Object>> list = new ArrayList<Map<Object,Object>>();
try {
conn = getConnection();
pstmt = conn.prepareStatement(sql);
if(paramValues != null){
for (int i = 0; i < paramValues.length; i++) {
pstmt.setObject((i+1), paramValues[i]);
}
}
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData(); //获取元数据
while(rs.next()){
Map<Object,Object> map = new HashMap<Object,Object>();
for (int j = 1; j <= rsmd.getColumnCount(); j++) {
//获取列名
String columnLabel = rsmd.getColumnLabel(j);
//根据列名从结果集中获取对应的列值
Object columnValue = rs.getObject(columnLabel);
map.put(columnLabel, columnValue);
} //end for
list.add(map);
}//end while
} catch (SQLException e) {
e.printStackTrace();
}finally{
closeAll(rs,null,pstmt,conn);
}
return list;
}
//4)、释放资源
public void closeAll(ResultSet rs,Statement stmt,PreparedStatement pstmt,Connection conn){
if(rs!=null){
try{
//关闭存储查询结果的ResultSet对象
rs.close();
}catch (Exception e) {
e.printStackTrace();
}
rs = null;
}
if(stmt!=null){
try{
//关闭负责执行SQL命令的Statement对象
stmt.close();
}catch (Exception e) {
e.printStackTrace();
}
}
if(pstmt!=null){
try{
//关闭负责执行SQL命令的PreparedStatement对象
pstmt.close();
}catch (Exception e) {
e.printStackTrace();
}
}
if(conn!=null){
try{
//将Connection连接对象还给数据库连接池
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
}
执行查询时ResultSet的实例rs在调用后就关闭了,但是无法关闭Connection的实例conn 和Statement的实例stmt