转载自:http://www.itpub.net/thread-1621319-1-1.html
一、简介
数据库连接池的基本原理是,在内部对象池中维护一定数量的数据库连接,并对外暴露从连接池获取数据库连接和释放连接的方法。
数据库连接池技术的优点:
² 资源重用。避免频繁创建连接、释放连接带来的性能开销。
² 更快的系统响应速度。减少了初始化数据库连接的时间,因为数据库连接池在初始化时,已经创建了一定数量的数据库连接。
² 统一的连接管理。避免数据库连接的内存泄漏。
本文教你如何在Tomcat 7.0.27应用服务器上配置Jakarta Commons Database Connection Pool(DBCP连接池),配置的连接池在Servlet 3.0项目中运行正常。
DBCP是一个数据库连接池工具,而Tomcat Servlet引擎中包含了该技术。
注意:在Tomcat 5.x和Tomcat 6.x等版本下配置JNDI数据源有很多不同。
二、JAR包
MySQL的JDBC驱动包是必须的,下载mysql-connector-java-5.1.19.zip,并解压;
再把文件mysql-connector-java-5.1.19-bin.jar复制到Tomcat\lib目录;
DBCP连接池依赖于两个包:commons-dbcp.jar和commons-pool.jar。但这两个库都已经被封装到Tomcat/lib/tomcat-dbcp.jar文件中,而且,对类的包名重新命名,以避免与应用程序发生干扰。
Tomcat的lib目录已经包含了tomcat-dbcp.jar库。
三、Tomcat配置MySQL数据源
Tomcat配置数据源,不同的数据库有不同的配法,基本上相同,仅仅是具体的参数略有不同。这里配置MySQL数据源。
在Tomcat的conf子目录内,打开context.xml配置文件,看这里:
<?xml version='1.0' encoding='utf-8'?> <Context> <!-- Default set of monitored resources --> <WatchedResource>WEB-INF/web.xml</WatchedResource> <!-- Uncomment this to disable session persistence across Tomcat restarts --> <!-- <Manager pathname="" /> --> <!-- Uncomment this to enable Comet connection tacking (provides events on session expiration as well as webapp lifecycle) --> <!-- <Valve className="org.apache.catalina.valves.CometConnectionManagerValve" /> --> </Context>
在<context>标签内增加一个<Resource>标签,并去除注释,内容如下:
<?xml version='1.0' encoding='utf-8'?> <Context> <WatchedResource>WEB-INF/web.xml</WatchedResource> <Resource name="jdbc/mysql5" auth="Container" type="javax.sql.DataSource" maxActive="80" maxIdle="20" maxWait="10000" removeAbandoned="true" username="guan" password="guan123456" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/webt3" /> </Context>
注意:黄色强调的内容为新增内容。四、验证
构建一个简单的Web项目,项目使用了Tomcat 7的DBCP连接池,从MySQL数据库读写数据。具体如下:
1、创建MySQL数据库
创建了数据库、表、用户,并对用户的访问权限进行授权,最后插入一点数据
mysql> CREATE DATABASE webt3; mysql> CREATE USER 'guan'@'%' IDENTIFIED BY 'guan123456'; mysql> GRANT ALL ON webt3.* TO 'guan'@'%'; mysql> USE webt3; mysql> DROP TABLE IF EXISTS `student`; mysql> CREATE TABLE `student`( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) DEFAULT NULL, `record` INT(11) DEFAULT NULL, PRIMARY KEY(`id`) ) ENGINE=INNODB DEFAULT CHARSET=utf8;
向表中插入几条数据:
mysql> INSERT INTO student VALUES(NULL, 'hello', 12345); mysql> INSERT INTO student VALUES(NULL, '张素钠', 98373); mysql> INSERT INTO student VALUES(NULL, '万科', 33238);
修改web.xml部署描述符
打开项目下WebContent/WEB-INF目录的web.xml文件,修改内容:
<?xml version="1.0" encoding="UTF-8"?> <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0"> <display-name>webt3</display-name> <welcome-file-list> <welcome-file>index.jsp</welcome-file> </welcome-file-list> <resource-ref> <description>Tomcat 7 DBCP</description> <res-ref-name>jdbc/mysql5</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref> </web-app>
注:黄色强调部分是新增内容,要与Tomcat的server.xml的配置保持一致。
4、编写代码
4.1、创建实体类ch.entity.Student.java,如下:
package ch.entity; import java.io.Serializable; public class Student implements Serializable { private static final long serialVersionUID = -2851451446149648542L; private int id; private String name; private int record; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getRecord() { return record; } public void setRecord(int record) { this.record = record; } @Override public String toString() { return "Student [id=" + id + ", name=" + name + ", record=" + record + "]"; } }
4.2、创建数据库访问类ch.dao.DBconn.java,如下:
package ch.dao; import java.sql.*; import java.util.List; import java.util.ArrayList; import javax.naming.Context; import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource; import ch.entity.Student; public class DBconn { Context context = null; DataSource ds = null; Connection conn = null; List<Student> students; public DBconn(){ initConnection(); this.students = new ArrayList<Student>(); } private void initConnection(){ try { context = new InitialContext(); } catch (NamingException e) { System.err.println("连接池上下文不存在! " + e.getMessage()); } try { ds = (DataSource)context.lookup("java:comp/env/jdbc/mysql5"); } catch (NamingException e) { System.err.println("数据源没发现! " + e.getMessage()); } try { conn = ds.getConnection(); } catch (SQLException e) { System.err.println("获取连接失败! " + e.getMessage()); } } private void freeConnection() { try { conn.close(); } catch (Exception e) { System.err.println("释放连接出错! "); e.printStackTrace(); } } public List<Student> getAllStudents() { Statement stmt = null; ResultSet rs = null; String sql = "SELECT id, name, record FROM student"; try { stmt = conn.createStatement(); rs = stmt.executeQuery(sql); rs = stmt.getResultSet(); while (rs.next()) { Student tmp = new Student(); tmp.setId(rs.getInt(1)); tmp.setName(rs.getString(2)); tmp.setRecord(rs.getInt(3)); students.add(tmp); } if (rs != null) rs.close(); if (stmt != null) stmt.close(); } catch (SQLException ex) { System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); System.out.println("VendorError: " + ex.getErrorCode()); } finally{ freeConnection(); } return students; } }
4.3、在WebContent目录下创建JSP文件:index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page import="ch.entity.Student, ch.dao.DBconn, java.util.*" %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <title>测试连接池</title> </head> <body> <% DBconn conn = new DBconn(); List<Student> students = conn.getAllStudents(); if(students!=null && students.size()>0){ for(Student student : students){ out.println(student.toString()); } } %> </body> </html>