2021-05-06

数据库连接池

一、C3P0数据库连接池

1、导包

或者

• c3p0-0.9.2.1.jar
• mysql-connector-java-5.1.20.jar
• mchange-commons-java-0.2.3.4.jar

或者在idea， 建好项目中pom.xml中导入

2.数据库的配置文件

建一个文件夹resources，在文件夹上右击选择Mark Directory as中的Resources root

在resources文件夹新建c3p0-config.xml的配置文件

(1)默认数据库连接池配置（MySQL）

<?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/java49?useUnicode=true&amp;characterEncoding=UTF-8</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <!--初始化获取三个连接，取值在maxPoolSize与minPoolSize-->
        <property name="initialPoolSize">5</property>
        <!--最大空闲时间,指定的时间内未使用则连接被丢弃。若为0则永不丢弃。Default: 0 -->
        <property name="maxIdleTime">60</property>
        <!--连接池中保留的最大连接数。 -->
        <property name="maxPoolSize">10</property>
        <!--连接池中保留的最大连接数。 -->
        <property name="minPoolSize">2</property>
        <!--最大链接数-->
        <property name="maxStatements">10</property>
    </default-config>

</c3p0-config>

（2） 自定义数据库连接池配置,通过named-config标签的name属性来使用，ComboPooledDataSource必须有参数（MySQL）

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
    <!--mysql连接,自定义数据库连接池配置,通过named-config标签的name属性来使用 -->
    <named-config name="mysql">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///java49</property>
        <property name="user">root</property>
        <property name="password">root</property>

        <!-- 连接池参数 -->
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">8</property>
        <property name="checkoutTimeout">1000</property>
    </named-config>
<c3p0-config>

(3)Orcle数据库连接池配置

<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
    <!--mysql连接,自定义数据库连接池配置,通过named-config标签的name属性来使用 -->
    <named-config name="mysql">
        <property name="driverClass">oracle.jdbc.driver.OracleDriver</property>
        <property name="jdbcUrl">jdbc:oracle:thin:@localhost:1521:orcl</property>
        <property name="user">zhi</property>
        <property name="password">zhi</property>

        <!-- 连接池参数 -->
        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">8</property>
        <property name="checkoutTimeout">1000</property>
    </named-config>
<c3p0-config>

3、数据连接池工具类

public class MysqlUtils {
    //默认读取配置文件c3p0-config.xml，后面无参读取默认的，有参读取自定义数据库连接池
    public static ComboPooledDataSource dataSource = new ComboPooledDataSource("mysql");
    //从数据库连接池中取一个连接；
    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void main(String[] args) {
        System.out.println(getConnection());
    }
}

4、写简单增删改查代码；

public class CRUD {
    public static QueryRunner runner = new QueryRunner(JdbcUtils.dataSource);
    /**
     * 查询记录条数
     * @return
     */
    public static int getAccount(){
        int cnt = 0;
        try {
            long count = (long)runner.query("select count(*) from user",new ScalarHandler());
            cnt = (int)count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return cnt;
    }
    /**
     * 查询所有
     * @return
     */
    public static List<User> getAll(){
        try {
            return runner.query("select username,birthday,sex,address from user",new BeanListHandler<>(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    /**
     * 查询一条
     * @return
     */
    public static User getOneUser(int id){
        try {
            return runner.query("select id,username,birthday,sex,address from user where id=?",new BeanHandler<>(User.class),id);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 增加用户
     * @param user
     */
    public static void addUser(User user){
        try {
            runner.update("insert into user(username,birthday,sex,address) values(?,?,?,?)",
                    user.getUsername(),user.getBirthday(),user.getSex(),user.getAddress());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除一个用户
     * @param id
     */
    public static void delUser(int id){
        try {
            runner.update("delete from user where id = ?",id);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    /**
     * 修改用户
     * @param user
     */
    public static void getupdateUser(User user){
        try {
            runner.update("update user set username=?,birthday=?,sex=?,address=? where id=?",
                    user.getUsername(),user.getBirthday(),user.getSex(),user.getAddress(),user.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6、测试类

@Test
    public void testAlluser(){
        CRUD crud = new CRUD();
        System.out.println(getAccount());

        List<User> users = crud.getAll();
        for (User user: users) {
            System.out.println(user);
        }
    }

    @Test
    public void testOneUser(){
        CRUD crud = new CRUD();
        System.out.println(crud.getOneUser(74));
    }

    @Test
    public void testaddUser(){
        CRUD crud = new CRUD();
        User user = new User();
        user.setUsername("张三");
        user.setBirthday("2021-05-06");
        user.setSex("女");
        user.setAddress("湖南");
        crud.addUser(user);
    }

    @Test
    public void testdelUser(){
        CRUD crud = new CRUD();
        crud.delUser(73);
    }

    @Test
    public void testupUser(){
        CRUD crud = new CRUD();
        User user = new User();
        user.setId(74);
        user.setUsername("王五");
        user.setBirthday("2021-05-06");
        user.setSex("女");
        user.setAddress("杭州");
        crud.getupdateUser(user);
    }

5、一次性插入100条数据

public class Demo1 {
    QueryRunner qr = new QueryRunner(MysqlUtils.dataSource); // mysql
    public int add(){
        int a = 0;
        for (int i = 0; i < 100; i++) {
            try {
                a +=qr.update("insert into t_users(t_uid,t_uname, t_upwd, t_uage,t_usex) values(?,?,?,?,?)", i,"aaa","123",18,"nan");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return a;
    }
}

二、Druid数据库连接池

1、导入druid-1.0.9.jar包。

2、建一个文件夹resources，在文件夹上右击选择Mark Directory as中的Resources root

在resources文件夹新建druid.properties的配置文件

driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mysql_test_data?useUnicode=true&characterEncoding=utf-8
username=root
password=root
#初始化申请的连接数量
initialSize=5
#最大连接数
maxActive=10
#超时连接等待时间
maxWait=3000

3、数据连接池工具类

/**
 * druid 连接池工具类
 */
public class JdbcUtils {
    private static DataSource dataSource;
    static {
        try {
            //加载配置文件
            Properties pro = new Properties();
            InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("resources/druid.properties");
            pro.load(is);
            try {
                dataSource = DruidDataSourceFactory.createDataSource(pro);
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //获取连接对象
    public static Connection getConnection() throws SQLException {
        Connection connection = dataSource.getConnection();
        return connection;
    }

    //关闭资源
    public static void close(ResultSet set, Statement statement, Connection connection) {
        if (set != null) {
            try {
                set.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();//归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //获取连接池对象
    public static DataSource getDataSource() {
        return dataSource;
    }
}

4、通过工厂类获取数据库连接对象

public class DruidDemo {
    public static void main(String[] args) {
        DataSource dataSource = null;
        List<Account> accounts  = new ArrayList<>();
        try {
            //加载配置文件
            Properties pro = new Properties();
            InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("resources/druid.properties");
            pro.load(is);
            try {
                //通过工厂类来获取数据库连接池对象
                dataSource = DruidDataSourceFactory.createDataSource(pro);
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

5、测试方法

public static void main(String[] args) {
        QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource());
        List<Account> accounts  = new ArrayList<>();
        try {
            accounts = qr.query("select id, name,money from account", new BeanListHandler<>(Account.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }

        for (Account account: accounts) {
            System.out.println(account);
        }
    }