数据库连接池C3P0&DBUtils结合使用，纯属个人学习笔记！

雨点打透心脏的1/2处 2023-02-13 05:41 29阅读 0赞

**一、数据库连接池的产生原因**

> 1、如果每次访问都走一次数据库的连接对象创建工作，比较消耗性能。

> 2、为了更好的满足日常中的高并发访问，提前在内存开辟一个空间，用来储存预先创建的一定数量的数据库连接，后续的访问直接从池子里面拿取连接，使用完归还即可

**二、开源数据库连接池C3P0**

常见的开源数据库连接池有DBCP和C3P0,这里记录的是C3P0简单使用。

> 使用前先导入相对应的Jar包  
> 创建c3p0-config.xml文件，在里面定义默认的连接配置信息，名字不能修改！

**连接代码示例**

package com.gzgs.C3P0;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    
    import org.junit.Test;
    
    import com.mchange.v2.c3p0.ComboPooledDataSource;
    
    
    public class TestDemo { 
    	@Test
    	public void test() { 
    		Connection conn=null;
    		PreparedStatement ps = null;
    		ComboPooledDataSource comboPooledDataSource=null;
    		try { 
    			comboPooledDataSource=new ComboPooledDataSource();
    			conn=comboPooledDataSource.getConnection();
    			String sql="insert into stu values(?,?)";
    			ps = conn.prepareStatement(sql);
    			ps.setString(1, "张三");
    			ps.setString(2, "1234");
    			ps.executeUpdate();
    		} catch (Exception e) { 
    			// TODO: handle exception
    		}finally { 
    			comboPooledDataSource.close();
    		}
    	}
    }

**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/student</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        
        
        <property name="initialPoolSize">1</property>
        <property name="maxIdleTime">30</property>
        <property name="maxPoolSize">100</property>
        <property name="minPoolSize">1</property>
        <property name="maxStatements">200</property>
      </default-config>
      
       
      <named-config name="oracle"> 
        <property name="acquireIncrement">50</property>
        <property name="initialPoolSize">100</property>
        <property name="minPoolSize">50</property>
        <property name="maxPoolSize">1000</property>
    
        
        <property name="maxStatements">0</property> 
        <property name="maxStatementsPerConnection">5</property>
    
        
        <user-overrides user="master-of-the-universe"> 
          <property name="acquireIncrement">1</property>
          <property name="initialPoolSize">1</property>
          <property name="minPoolSize">1</property>
          <property name="maxPoolSize">5</property>
          <property name="maxStatementsPerConnection">50</property>
        </user-overrides>
      </named-config>
    
     
    </c3p0-config>

**三、DBUtils**

DBUtils是一个方便我们对数据库进行CRUD的jar包，它只能帮我们简化了CRUD 的代码， 但是连接的创建以及获取工作。 不在他的考虑范围，所以我们可以将它和数据库连接池结合使用。

**示例代码**

\---------------test代码----------------------

package com.gzgs.dbutils;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.SQLException;
    import java.util.List;
    
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.BeanHandler;
    import org.apache.commons.dbutils.handlers.BeanListHandler;
    import org.junit.Test;
    
    import com.gzgs.domain.Student;
    import com.mchange.v2.c3p0.ComboPooledDataSource;
    
    public class TestDBUtils { 
    	//测试增删改
    	@Test
    	public void test() { 
    		Connection conn=null;
    		PreparedStatement ps = null;
    		ComboPooledDataSource comboPooledDataSource=null;
    	
    		try { 
    			comboPooledDataSource=new ComboPooledDataSource();
    			QueryRunner queryRunner=new QueryRunner(comboPooledDataSource);
    			//插入
    			queryRunner.update("insert into stu values(?,?)", "李五四","789");
    			//删除
    			queryRunner.update("delete from stu where username=?","李五四");
    			
    			//修改
    			queryRunner.update("update stu set password=? where username=?","133","张三");
    		
    			
    			//查询---查询单个对象
    			Student stu = queryRunner.query("select * from stu where username=?", new BeanHandler<Student>(Student.class),"张三");
    			//查询---查询多个对象
    			System.out.println(stu);
    			
    			//查询多个学生
    			List<Student> list = queryRunner.query("select * from stu", new BeanListHandler<Student>(Student.class));
    			for (Student student : list) { 
    				System.out.println(student);
    			}
    		} catch (SQLException e) { 
    			// TODO 自动生成的 catch 块
    			e.printStackTrace();
    		}finally { 
    			comboPooledDataSource.close();
    		}
    	}
    }

\--------------------C3P0配置文件--------------------

\------------------demain---------------------------

package com.gzgs.domain;
    
    public class Student { 
    	private String username;
    	private String password;
    	public String getUsername() { 
    		return username;
    	}
    	public void setUsername(String username) { 
    		this.username = username;
    	}
    	public String getPassword() { 
    		return password;
    	}
    	public void setPassword(String password) { 
    		this.password = password;
    	}
    	@Override
    	public String toString() { 
    		return "Student [username=" + username + ", password=" + password + "]";
    	}
    	
    }