由于基于HTTP协议的Web程序是无状态的,因此,在应用程序中使用JDBC时,每次处理客户端请求时都会重新建立数据库连接。如果客户端的请求频繁的话,这将会消耗很多的话资源。因此,tomcat提供了数据库连接池技术。数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个数据库连接。在使用一个数据库连接后,将其归还数据库连接池,以备其他程序使用。
1. 将JDBC mysql的驱动程序(mysql-connector-java-5.1.30-bin.jar)放入WEB-INF/lib文件夹。
2. 在META/inf文件夹新建context.xml文件。
写入
<Context path="/samples" docBase="samples" reloadable="true" crossContext="true">
<Resource
name="jdbc/sitename" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="root" password="helloworld" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/sitename?characterEncoding=GBK"/>
</Context>
3.在WEB-INF/web.xml文件中写入
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/sitename</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
4. 使用示例
package servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class ConnPool
*/
@WebServlet("/ConnPool")
public class ConnPool extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public ConnPool() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
response.setContentType("text/html");
response.setCharacterEncoding("GBK");
PrintWriter out =response.getWriter();
try{
javax.naming.Context ctx =new javax.naming.InitialContext();
javax.sql.DataSource ds=(javax.sql.DataSource) ctx.lookup("java:comp/env/jdbc/sitename");
Connection conn=ds.getConnection();
PreparedStatement pstmt =((java.sql.Connection) conn).prepareStatement("SELECT user_id ,last_name FROM users");
ResultSet rs=pstmt.executeQuery();
StringBuilder table=new StringBuilder();
table.append("<table border='1'>");
table.append("<tr><td>用户编号</td><td>名字</td></tr>");
while(rs.next()){
table.append("<tr><td>"+rs.getString("user_id")+"</td><td>");
table.append(rs.getString("last_name")+"</td></tr>");
}
table.append("</table>");
out.println(table.toString());
}catch(Exception e){
out.println(e.getMessage());
}
out.flush();
out.close();
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
}
}