jdbc 与 mysql 连接 - 数据库连接池

c3p0 数据库连接池

在 lib 文件下导入 c3p0-0.9.1.2.jar 包

com.atguigu4.connection 包

package com.atguigu4.connection;

import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.SQLException;

import org.junit.Test;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.mchange.v2.c3p0.DataSources;

public class C3P0Test {
   

	// 方式一：
	@Test
	public void testGetConnection() throws Exception {
   
		// 获取 c3p0 数据库连接池
		ComboPooledDataSource cpds = new ComboPooledDataSource();
		cpds.setDriverClass( "com.mysql.cj.jdbc.Driver" );           
		cpds.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );
		cpds.setUser("root");                                  
		cpds.setPassword("123456");  
		
		// 通过设置相关的参数，对数据库连接池进行管理;
		// 设置初始时数据库连接池中的连接数
		cpds.setInitialPoolSize(10);
		
		Connection conn = cpds.getConnection();
		System.out.println(conn);
		
		// 销毁 c3p0 数据库连接池
//		DataSources.destroy(cpds);
	}
	
	// 方式二：使用配置文件
	@Test
	public void testGetConnection1() throws SQLException {
   
		ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
		Connection conn = cpds.getConnection();
		System.out.println(conn);
	}
}

com.atguigu4.util 包

package com.atguigu4.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;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class JDBCUtils {
   
	
	/**
	 * 获取数据库的连接
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection() throws Exception {
   
		//1.读取配置文件中的 4 个基本信息
		InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
		
		Properties pros = new Properties();
		pros.load(is);
		
		String user = pros.getProperty("user");
		String password = pros.getProperty("password");
		String url = pros.getProperty("url");
		String driverClass = pros.getProperty("driverClass");
		
		//2.加载驱动
		Class.forName(driverClass);
		
		//3.获取连接
		Connection conn = DriverManager.getConnection(url,user,password);
		return conn;
	}

	/**
	 * 使用 C3P0 的数据库连接池技术
	 * 
	 */
	// 数据库连接池一个就足够，不必造很多，浪费资源
	private static ComboPooledDataSource cpds = new ComboPooledDataSource("helloc3p0");
	public static Connection getConnection1() throws Exception {
   
		Connection conn = cpds.getConnection();
		
		return conn;
	}
	
	/**
	 * 关闭连接和 Statement 的操作
	 * @param conn
	 * @param ps
	 */
	public static void closeResource(Connection conn,Statement ps) {
   
		try {
   
			if(ps != null)
				ps.close();
		} catch (SQLException e) {
   
			e.printStackTrace();
		}
		try {
   
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
   
			e.printStackTrace();
		}
	}
	
	// 关闭资源操作
	public static void closeResource(Connection conn,Statement ps,ResultSet rs) {
   
		try {
   
			if(ps != null)
				ps.close();
		} catch (SQLException e) {
   
			e.printStackTrace();
		}
		try {
   
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
   
			e.printStackTrace();
		}
		try {
   
			if(rs != null) 
				rs.close();
		} catch (SQLException e) {
   
			e.printStackTrace();
		}
	}
}

src 下的 c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>

  <named-config name="helloc3p0"> 
    <!-- 提供获取连接的 4 个基本信息 -->
    <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql:///test</property>
    <property name="user">root</property>
    <property name="password">123456</property>

    <!-- 进行数据库连接池管理的基本信息 -->
    <!-- 当数据库连接池中的连接数不够时，c3p0 一次性向数据库服务器申请的连接数 -->
    <property name="acquireIncrement">50</property>
    <!-- c3p0 数据库连接池中初始化时的连接数 -->
    <property name="initialPoolSize">100</property>
    <!-- c3p0 数据库连接池维护的最少连接数 -->
    <property name="minPoolSize">50</property>
    <!-- c3p0 数据库连接池维护的最多的连接数 -->
    <property name="maxPoolSize">1000</property>
    <!-- c3p0 数据库连接池最多维护 Statement 的个数 -->
    <property name="maxStatements">0</property> 
    <!-- 每个连接中可以最多使用的 Statement 的个数 -->
    <property name="maxStatementsPerConnection">5</property>

  </named-config>
</c3p0-config>

com.atguigu3.dao.junit 包

package com.atguigu3.dao.junit;

import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.Date;
import java.util.List;

import org.junit.Test;

//import com.atguigu1.util.JDBCUtils;
// 使用 c3p0 的数据库连接池技术
import com.atguigu4.util.JDBCUtils;
import com.atguigu2.bean.Customer;
import com.atguigu3.dao.CustomerDAOImpl;

public class CustomerDAOImplTest {
   
	
	private CustomerDAOImpl dao = new CustomerDAOImpl();

	@Test
	public void testInsert() {
   
		Connection conn = null;
		try {
   
			conn = JDBCUtils.getConnection();
			Customer cust = new Customer(1,"于小飞","xiaofei@126.com",new Date(23453453232L));
			dao.insert(conn, cust);
			System.out.println("添加成功");
		} catch (Exception e) {
   
			e.printStackTrace();
		} finally {
   
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testDeleteById() {
   
		Connection conn = null;
		try {
   
			conn = JDBCUtils.getConnection();
			
			dao.deleteById(conn, 13);
			
			System.out.println("删除成功");
		} catch (Exception e) {
   
			e.printStackTrace();
		} finally {
   
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testUpdateConnectionCustomer() {
   
		Connection conn = null;
		try {
   
			conn = JDBCUtils.getConnection();
			
			Customer cust = new Customer(18,"贝多芬","beiduofen@126.com",new Date(3425214134L));
			dao.update(conn, cust);
			
			System.out.println("修改成功");
			// 修改成功
		} catch (Exception e) {
   
			e.printStackTrace();
		} finally {
   
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetCustomerById() {
   
		Connection conn = null;
		try {
   
			// 使用 c3p0 的数据库连接池技术
			conn = JDBCUtils.getConnection1();
			
			Customer cust = dao.getCustomerById(conn, 19);
			System.out.println(cust);
			
			System.out.println("添加成功");
		} catch (Exception e) {
   
			e.printStackTrace();
		} finally {
   
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetAll() {
   
		Connection conn = null;
		try {
   
			conn = JDBCUtils.getConnection();
			
			List<Customer> list = dao.getAll(conn);
			list.forEach(System.out::println);
			
			System.out.println("");
			
//			Customer [id=1, name=汪峰, email=wf@126.com, birth=2010-02-02]
//			Customer [id=2, name=王菲, email=wangf@163.com, birth=1988-12-26]
//			Customer [id=4, name=汤唯, email=tangw@sina.com, birth=1986-