DBCP连接池

蔚落 2022-03-29 14:50 316阅读 0赞

**目录**

需要导入的jar文件

新的项目v1.1

dbcp.properties

DBCPUtil.java

DBCPDemo.java

老项目的目录结构v1.0

jdbc.properties

dbcp.properties

JDBCUtil.java

DBCPDemo.java

--------------------

# 需要导入的jar文件 #

commons-dbcp2-2.5.0.jar

commons-logging-1.2.jar

commons-pool2-2.6.0.jar

前三个下载地址：[http://commons.apache.org/][http_commons.apache.org]

my-sql-connector-java-8.0.12.jar 下载教程：[https://blog.csdn.net/qq\_32965187/article/details/83141405][https_blog.csdn.net_qq_32965187_article_details_83141405]

# 新的项目v1.1 #

发现jdbc.properties和JDBCUtil没有乱用，特别是jdbc.properties。

删除了jdbc.properties和JDBCUtil，新建了DBCPUtil.java

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyOTY1MTg3_size_16_color_FFFFFF_t_70][]

## dbcp.properties ##

没有变，参考老项目

## DBCPUtil.java ##

package com.zhujunwei.util ;
    
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    import javax.sql.DataSource;
    import org.apache.commons.dbcp2.BasicDataSourceFactory;
    
    public class DBCPUtil {
    	
    	static DataSource dataSource = null ;
    	static {
    		try {
    			dataSource = BasicDataSourceFactory.createDataSource(getProperties());
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    
    	/**
    	 * 得到DataSource连接资源
    	 * @return Connection
    	 * @throws SQLException 
    	 */
    	public  static Connection getConnection() throws SQLException {
    		
    		return dataSource.getConnection();
    		
    	}
    	/**
    	 * 得到配置文件
    	 * @return properties
    	 */
    	private static Properties getProperties() {
    		Properties properties = null ;
    		try {
    			properties = new Properties() ;
    			InputStream is = new FileInputStream("src//dbcp.properties");
    			properties.load(is);
    		} catch (IOException e) {
    			e.printStackTrace();
    		}
    		return properties;
    	}
    	
    	
    	/**
    	 * 释放资源
    	 * @param conn
    	 * @param st
    	 * @param rs
    	 */
    	public static void release(Connection conn , Statement st , ResultSet rs){
    		closeRs(rs);
    		closeSt(st);
    		closeConn(conn);
    	}
    	public static void release(Connection conn , Statement st){
    		closeSt(st);
    		closeConn(conn);
    	}
    
    	
    	private static void closeRs(ResultSet rs){
    		try {
    			if(rs != null){
    				rs.close();
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally{
    			rs = null;
    		}
    	}
    	
    	private static void closeSt(Statement st){
    		try {
    			if(st != null){
    				st.close();
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally{
    			st = null;
    		}
    	}
    	
    	private static void closeConn(Connection conn){
    		try {
    			if(conn != null){
    				conn.close();
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally{
    			conn = null;
    		}
    	}
    }

## DBCPDemo.java ##

package com.zhujunwei;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import org.junit.Test;
    import com.zhujunwei.util.DBCPUtil;
    
    public class DBCPDemo {
    	
    	@Test
    	public void testDBCP() {
    		Connection conn = null ;
    		PreparedStatement ps = null ;
    		try {
    			
    			//加载连接资源
    			conn = DBCPUtil.getConnection();
    			String sql = "insert into account values(null,?,?)";
    			ps = conn.prepareStatement(sql);
    			ps.setString(1, "zz");
    			ps.setInt(2, 100);
    			ps.executeUpdate();
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {
    			DBCPUtil.release(conn, ps);
    		}
    	}
    }

# 老项目的目录结构v1.0 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyOTY1MTg3_size_16_color_FFFFFF_t_70 1][]

## jdbc.properties ##

driverClass=com.mysql.cj.jdbc.Driver
    url=jdbc:mysql://localhost/bank?useSSL=false&serverTimezone=UTC
    name=root
    password=123456

## dbcp.properties ##

#连接设置
    driverClassName=com.mysql.cj.jdbc.Driver
    url=jdbc:mysql://localhost:3306/bank?useSSL=false&serverTimezone=UTC
    username=root
    password=123456
    
    #
    initialSize=10
    
    #最大连接数量
    maxActive=50
    
    #
    maxIdle=20
    
    #
    minIdle=5
    
    #
    maxWait=60000
    
    #JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] 
    #注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
    connectionProperties=useUnicode=true;characterEncoding=UTF8
    
    #指定由连接池所创建的连接的自动提交（auto-commit）状态。
    defaultAutoCommit=true
    
    #driver default 指定由连接池所创建的连接的只读（read-only）状态。
    #如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
    defaultReadOnly=
    
    #driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
    #可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
    defaultTransactionIsolation=READ_UNCOMMITTED

## JDBCUtil.java ##

package com.zhujunwei.util ;
    
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    
    public class JDBCUtil {
    	
    	static String driverClass = null;
    	static String url = null;
    	static String name = null;
    	static String password= null;
    	
    	static{
    		try {
    			//1. 创建一个属性配置对象
    			Properties properties = new Properties();
    //			InputStream is = new FileInputStream("jdbc.properties");
    			
    			
    			//使用类加载器，去读取src底下的资源文件。 后面在servlet
    			InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
    			//导入输入流。
    			properties.load(is);
    			
    			//读取属性
    			driverClass = properties.getProperty("driverClass");
    			url = properties.getProperty("url");
    			name = properties.getProperty("name");
    			password = properties.getProperty("password");
    			
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    	
    	/**
    	 * 获取连接对象
    	 * @return
    	 */
    	public static Connection getConn(){
    		Connection conn = null;
    		try {
    			Class.forName(driverClass);
    			//静态代码块 ---> 类加载了，就执行。 java.sql.DriverManager.registerDriver(new Driver());
    			//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
    			//DriverManager.getConnection("jdbc:mysql://localhost/test?user=monty&password=greatsqldb");
    			//2. 建立连接 参数一： 协议 + 访问的数据库 ， 参数二： 用户名 ， 参数三： 密码。
    			conn = DriverManager.getConnection(url, name, password);
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    		return conn;
    	}
    	
    	/**
    	 * 释放资源
    	 * @param conn
    	 * @param st
    	 * @param rs
    	 */
    	public static void release(Connection conn , Statement st , ResultSet rs){
    		closeRs(rs);
    		closeSt(st);
    		closeConn(conn);
    	}
    	public static void release(Connection conn , Statement st){
    		closeSt(st);
    		closeConn(conn);
    	}
    
    	
    	private static void closeRs(ResultSet rs){
    		try {
    			if(rs != null){
    				rs.close();
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally{
    			rs = null;
    		}
    	}
    	
    	private static void closeSt(Statement st){
    		try {
    			if(st != null){
    				st.close();
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally{
    			st = null;
    		}
    	}
    	
    	private static void closeConn(Connection conn){
    		try {
    			if(conn != null){
    				conn.close();
    			}
    		} catch (SQLException e) {
    			e.printStackTrace();
    		}finally{
    			conn = null;
    		}
    	}
    }

## DBCPDemo.java ##

package com.zhujunwei;
    
    import java.io.FileInputStream;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.util.Properties;
    
    import javax.sql.DataSource;
    
    import org.apache.commons.dbcp2.BasicDataSourceFactory;
    import org.junit.Test;
    
    import com.zhujunwei.util.JDBCUtil;
    
    public class DBCPDemo {
    	
    	@Test
    	public void testDBCP() {
    		Connection conn = null ;
    		PreparedStatement ps = null ;
    		try {
    			//加载配置文件
    			Properties properties = new Properties() ;
    			InputStream is = new FileInputStream("src//dbcp.properties");
    			properties.load(is);
    			
    			DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
    			
    			//得到连接对象
    			conn = dataSource.getConnection();
    			String sql = "insert into account values(null,?,?)";
    			ps = conn.prepareStatement(sql);
    			ps.setString(1, "z");
    			ps.setInt(2, 100);
    			ps.executeUpdate();
    		} catch (Exception e) {
    			e.printStackTrace();
    		} finally {
    			JDBCUtil.release(conn, ps);
    		}
    	}
    }

其他博客链接：

[DBCP数据库连接池的简单使用][DBCP]

[http_commons.apache.org]: http://commons.apache.org/
[https_blog.csdn.net_qq_32965187_article_details_83141405]: https://blog.csdn.net/qq_32965187/article/details/83141405
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyOTY1MTg3_size_16_color_FFFFFF_t_70]: /images/20220329/8d4710a441c54c9c861542b7013bf3ff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyOTY1MTg3_size_16_color_FFFFFF_t_70 1]: /images/20220329/e73692db74b0419db474376396ed5fc0.png
[DBCP]: https://www.cnblogs.com/sunseine/p/5947448.html