org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted

原因：资源连接耗尽，连接未有释放导致的；

在一个servlet类里，涉及多个有关联的增删改查语句的时候，一定要分开，以免程序出错，导致连接不能释放；

例子说明：

1、查询yh表中当天的所有编号；（查询语句1）

2、根据编号来查询当天最新一条记录的时间，如果查询出来的时间距离当前时间不超过30分钟的话；执行下一条；

（查询语句2）

3、根据编号来查询姓名（查询语句3）

代码如下：

package cn.com.servlet;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import cn.com.jdbc.JdbcUtils_DBCP;
import cn.com.pack.DoubleStr;
public class Underperson_status extends HttpServlet {
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		doPost(request, response);

	}
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		Connection conn = null;
		Connection conn1 = null;
		Connection conn2 = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		ResultSet rs2 = null;
		PreparedStatement st = null;
		PreparedStatement st1 = null;
		PreparedStatement st2 = null;
		List<DoubleStr> list = new ArrayList<DoubleStr>();
		int count1 = 0, count2 = 0, count3 = 0, count4 = 0, count5 = 0, count6 = 0, count7 = 0;
		String str1 = "";
		String str2 = "";
		String str3 = "";
		String str4 = "";
		String str5 = "";
		String str6 = "";
		String str7 = "";
		Date da = new Date();
		SimpleDateFormat sim = new SimpleDateFormat("yyyy-MM-dd");
		String time = sim.format(da);
		// 第一部分、 查询不重复编号的名称
		List<String> nums = new ArrayList<String>();
		try {
			conn = JdbcUtils_DBCP.getConnection();
			String sql = "SELECT DISTINCT  number  FROM yh WHERE TIME LIKE '"
					+ time + "%' and state=1 ";
			st = conn.prepareStatement(sql);
			rs = st.executeQuery();
			while (rs.next()) {
				// 根据编号查询名称
				nums.add(rs.getString(1));

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils_DBCP.release(conn, st, rs);
		}
		// 第二部分、根据num查询时间
		List<DoubleStr> list_n = new ArrayList<DoubleStr>();
		String bs = "";
		String name = "";
		String times = "";
		// 对每个编号进行最近时间的查询基站

		try {
			conn2 = JdbcUtils_DBCP.getConnection();
			// 获取当前的时间
			Date date = new Date();
			SimpleDateFormat formatter = new SimpleDateFormat(
					"yyyy-MM-dd HH:mm:ss");
			for (String num : nums) {
				String sqls = "SELECT * FROM yh WHERE number='" + num
						+ "' ORDER BY TIME DESC LIMIT 1";
				st2 = conn2.prepareStatement(sqls);
				rs2 = st2.executeQuery();
				if (rs2.next()) {
					times = rs2.getString(8);// 这个是时间
					// 判断最后一条记录的时间跟当前时间的距离
					Date date1 = formatter.parse(times);
					long l = date.getTime() - date1.getTime();
					long min = ((l / (60 * 1000)));
					if (min > 30) {

					} else {
						// 添加满足条件的不重复的num集合
						DoubleStr str = new DoubleStr(rs2.getString(1),
								rs2.getString(2));
						list_n.add(str);
					}

				}
			}

		} catch (SQLException | ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils_DBCP.release(conn2, st2, rs2);

		}
		// 第二部分、根据num查询姓名
		for (DoubleStr num : list_n) {
			try {
				conn1 = JdbcUtils_DBCP.getConnection();
				String nsql = "SELECT * FROM person_info WHERE NUMBER='"
						+ num.getOne() + "'";
			     bs=num.getTwo();
				st1 = conn1.prepareStatement(nsql);
				rs1 = st1.executeQuery();
				if (rs1.next()) {
					name = rs1.getString(1);
					if (bs.equals("030") || bs.equals("017")
							|| bs.equals("028") || bs.equals("014")
							|| bs.equals("023") || bs.equals("029")) {
						count1++;
						str1 = str1 + "    " + name;

					}

					// -130m
					if (bs.equals("045") || bs.equals("020")
							|| bs.equals("021") || bs.equals("015")) {
						count2++;
						str2 = str2 + "    " + name;
					}
					// 斜井
					if (bs.equals("012") || bs.equals("013")
							|| bs.equals("019")) {
						count3++;
						str3 = str3 + "    " + name;

					}
					// -240m
					if (bs.equals("024") || bs.equals("027")
							|| bs.equals("042")) {
						count4++;
						str4 = str4 + "    " + name;
					}

					// -290m
					if (bs.equals("040") || bs.equals("031")
							|| bs.equals("034") || bs.equals("038")
							|| bs.equals("033") || bs.equals("039")
							|| bs.equals("041")) {
						count5++;
						str5 = str5 + "    " + name;

					}
					// -340m
					if (bs.equals("037") || bs.equals("025")
							|| bs.equals("032")) {
						count6++;
						str6 = str6 + "    " + name;

					}
					// -400m
					if (bs.equals("016") || bs.equals("036")
							|| bs.equals("026") || bs.equals("035")
							|| bs.equals("011")) {
						count7++;
						str7 = str7 + "    " + name;
					}

				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				JdbcUtils_DBCP.release(conn1, st1, rs1);
			}
		}
		DoubleStr dou1 = new DoubleStr(Integer.toString(count1), str1);
		list.add(dou1);
		DoubleStr dou2 = new DoubleStr(Integer.toString(count2), str2);
		list.add(dou2);
		DoubleStr dou3 = new DoubleStr(Integer.toString(count3), str3);
		list.add(dou3);
		DoubleStr dou4 = new DoubleStr(Integer.toString(count4), str4);
		list.add(dou4);
		DoubleStr dou5 = new DoubleStr(Integer.toString(count5), str5);
		list.add(dou5);
		DoubleStr dou6 = new DoubleStr(Integer.toString(count6), str6);
		list.add(dou6);
		DoubleStr dou7 = new DoubleStr(Integer.toString(count7), str7);
		list.add(dou7);
		int sum = count1 + count2 + count3 + count4 + count5 + count6 + count7;
		String sm = Integer.toString(sum);
		request.setAttribute("list", list);
		request.setAttribute("sm", sm);
		     request.getRequestDispatcher("Underperson_status.jsp").forward(request,
				response);

	}

}

 效果图如下：