常见三种数据库连接池在WEB程序的应用总结

来源：互联网发布：家居设计软件手机编辑：程序博客网时间：2024/05/22 01:47

一. C3P0数据库连接池

# 目录结构

#连接池配置文件

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?><c3p0-config><default-config><property name="driverClass">com.mysql.jdbc.Driver</property><property name="jdbcUrl">jdbc:mysql://localhost:3306/friend</property><property name="user">root</property><property name="password">123456</property><property name="acquireIncrement">5</property><property name="initialPoolSize">10</property><property name="minPoolSize">5</property><property name="maxPoolSize">20</property></default-config></c3p0-config>

# 连接工具类

C3P0Conn.java

package com.connect;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import com.mchange.v2.c3p0.ComboPooledDataSource;public class C3P0Conn {   static ComboPooledDataSource ds = null;   static {   ds = new ComboPooledDataSource();   }      /*    * 连接数据库    */   public static Connection getConnection(){   try {return ds.getConnection();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();return null;}   }      /*    * 关闭数据库    */   public static void close(Connection conn,PreparedStatement pst,ResultSet rs){         if(rs!=null){             try {                 rs.close();             } catch (SQLException e) {                 e.printStackTrace();             }         }         if(pst!=null){             try {                 pst.close();             } catch (SQLException e) {                 e.printStackTrace();             }         }          if(conn!=null){             try {                 conn.close();             } catch (SQLException e) {                 e.printStackTrace();             }         }     }  }

# 前端测试页面

<%@page import="com.connect.C3P0Conn"%><%@page import="java.sql.SQLException"%><%@page import="java.sql.ResultSet"%><%@page import="java.sql.Statement"%><%@page import="java.sql.Connection"%><%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><!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>C3P0数据源测试</title></head><body>   <%     Connection conn =null;     Statement stmt = null;     ResultSet rs =null;     try{     conn = C3P0Conn.getConnection();         stmt = conn.createStatement();         rs = stmt.executeQuery("select * from message");         //遍历结果集         while(rs.next()){         out.println(rs.getString(2) + ",");         out.println(rs.getString(3)+",");         out.println(rs.getString(4)+",");         out.println(rs.getString(5));         out.println("<br />");         }     }catch(SQLException e){     e.printStackTrace();     }     finally{     if(stmt != null){     try{     stmt.close();     }catch(SQLException e){     e.printStackTrace();     }     }     if(conn != null){     try{     conn.close();     }catch(SQLException e){     e.printStackTrace();     }     }     }   %></body></html>

二.JNDI数据库连接池

# 目录结构

# 配置文件

context.xml

<?xml version="1.0" encoding="UTF-8"?><Context path="/JNDI" docBase="JNDI"        reloadable="true" crossContext="true">  <Resource name="jdbc/db" auth="Container" type="javax.sql.DataSource"               maxActive="100" maxIdle="30" maxWait="10000"               username="root" password="123456" driverClassName="com.mysql.jdbc.Driver"               url="jdbc:mysql://localhost:3306/friend"/></Context>

web.xml

<?xml version="1.0" encoding="UTF-8"?><web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">  <display-name>JNDI</display-name>  <welcome-file-list>    <welcome-file>index.html</welcome-file>    <welcome-file>index.htm</welcome-file>    <welcome-file>index.jsp</welcome-file>    <welcome-file>default.html</welcome-file>    <welcome-file>default.htm</welcome-file>    <welcome-file>default.jsp</welcome-file>  </welcome-file-list>     <resource-ref>    <description>DB Connection</description>    <res-ref-name>jdbc/db</res-ref-name>    <res-type>javax.sql.DataSource</res-type>    <res-auth>Container</res-auth>  </resource-ref></web-app>

# 连接工具类

DButil.java

package com.connect;import java.sql.Connection;import java.sql.SQLException;import javax.naming.Context;import javax.naming.InitialContext;import javax.sql.DataSource;public class DButil {   //建立到数据库的连接   public static Connection connect(){   try {Context context = new InitialContext();DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/db");Connection conn = ds.getConnection();System.out.println("数据库连接成功！");return conn;} catch (Exception ex) {// TODO Auto-generated catch blockSystem.out.println("数据库连接失败！");return null;}   }    //关闭链接 public static void closeConnection(Connection con) { try { con.close(); }catch(SQLException e) {  } }}

# 前端测试页面

index.jsp

<%@page import="java.sql.SQLException"%><%@page import="com.connect.DButil"%><%@page import="java.sql.ResultSet"%><%@page import="java.sql.Statement"%><%@page import="java.sql.Connection"%><%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><!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>JNDI数据源测试</title></head><body>   <%     Connection conn =null;     Statement stmt = null;     ResultSet rs =null;     try{     conn = DButil.connect();         stmt = conn.createStatement();         rs = stmt.executeQuery("select * from message");         //遍历结果集         while(rs.next()){         out.println(rs.getString(2) + ",");         out.println(rs.getString(3)+",");         out.println(rs.getString(4)+",");         out.println(rs.getString(5));         out.println("<br />");         }     }catch(SQLException e){     e.printStackTrace();     }     finally{     if(stmt != null){     try{     stmt.close();     }catch(SQLException e){     e.printStackTrace();     }     }     if(conn != null){     try{     conn.close();     }catch(SQLException e){     e.printStackTrace();     }     }     }   %></body></html>

三.DBCP数据库连接池(WEB连接其实和JNDI差不多，但是单纯Java连接就不一样了)

# 目录结构

# 配置文件

context.xml

<?xml version="1.0" encoding="UTF-8"?><Context path="/DBCP" docBase="DBCP"        reloadable="true" crossContext="true">  <Resource name="jdbc/db" auth="Container" type="javax.sql.DataSource"               maxActive="100" maxIdle="30" maxWait="10000"               username="root" password="123456" driverClassName="com.mysql.jdbc.Driver"               url="jdbc:mysql://localhost:3306/friend"/></Context>

web.xml

<?xml version="1.0" encoding="UTF-8"?><web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">  <display-name>DBCP</display-name>  <welcome-file-list>    <welcome-file>index.html</welcome-file>    <welcome-file>index.htm</welcome-file>    <welcome-file>index.jsp</welcome-file>    <welcome-file>default.html</welcome-file>    <welcome-file>default.htm</welcome-file>    <welcome-file>default.jsp</welcome-file>  </welcome-file-list>   <resource-ref>    <description>DB Connection</description>    <res-ref-name>jdbc/db</res-ref-name>    <res-type>javax.sql.DataSource</res-type>    <res-auth>Container</res-auth>  </resource-ref></web-app>

# 连接工具类

DBCPConnect.java

package com.dbcp;import java.sql.Connection;import javax.naming.Context;import javax.naming.InitialContext;import javax.sql.DataSource;import org.apache.commons.dbcp.BasicDataSource;public class DBCPConnect {private static DataSource dataSource;private static Connection con;    public static Connection connect(){   try {Context context = new InitialContext();DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/db");Connection conn = ds.getConnection();System.out.println("数据库连接成功！");return conn;} catch (Exception ex) {// TODO Auto-generated catch blockSystem.out.println("数据库连接失败！");return null;} }}

# 前端测试页面

index.jsp

<%@page import="java.sql.PreparedStatement"%><%@page import="com.dbcp.DBCPConnect"%><%@page import="java.sql.SQLException"%><%@page import="java.sql.ResultSet"%><%@page import="java.sql.Statement"%><%@page import="java.sql.Connection"%><%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><!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>C3P0数据源测试</title></head><body>   <%     Connection conn =null;     PreparedStatement stmt = null;     try{     conn = DBCPConnect.connect();     String sql = "select * from message"; stmt = conn.prepareStatement(sql); ResultSet rs=stmt.executeQuery();         //遍历结果集 while(rs.next()){for(int i=1;i<=rs.getMetaData().getColumnCount();i++){out.println(rs.getString(i)+"\t");}out.println("<br/>");}     }catch(SQLException e){     e.printStackTrace();     }     finally{     if(stmt != null){     try{     stmt.close();     }catch(SQLException e){     e.printStackTrace();     }     }     if(conn != null){     try{     conn.close();     }catch(SQLException e){     e.printStackTrace();     }     }     }   %></body></html>

# 运行截图

0 0