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

我会带着你远行 2022-06-16 09:38 139阅读 0赞

一. C3P0数据库连接池

\# 目录结构

![Center][]

\#连接池配置文件

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 block
    		e.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数据库连接池

\# 目录结构

![Center 1][]

\# 配置文件

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 block
    		System.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连接就不一样了)

\# 目录结构

![Center 2][]

\# 配置文件

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 block
    			System.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>

\# 运行截图

![Center 3][]

[Center]: /images/20220616/9b81497b73bb46e7ac00fc3092393ed1.png
[Center 1]: /images/20220616/001a0dea20e04505ba4a0f0892a46760.png
[Center 2]: /images/20220616/78fd307409d4452488dafd0e3515893f.png
[Center 3]: /images/20220616/3ccccf54cd9544a1a951be7db8a1c50d.png