数据库连接池

连接池

连接池就是存储我们数据库连接对象的容器,系统初始化后,会去申请一些连接对象,用户访问数据库时,回去容器中获取数据库连接对象,使用完毕后会归还给容器

DataSource

Java提供的接口, DataSource接口由框架编写者实现

实现DataSource

一般情况下不需要我们实现此接口

  1. C3P0
  2. Druid 阿里巴巴

C3P0

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 1. 导入jar包
 * 2. 添加配置文件
 *    配置文件格式为xml格式
 *    默认名称为c3p0-config.xml
 * 3. 获取数据源对象DataSource
 * 4. 通过DataSource获取数据库连接Connection
 * 5. 通过连接对象获取PreparedStatement,执行sql语句
 * 6. 遍历结果
 * 7. 关闭
 */


public class Demo {
    public static void main(String[] args) {
        DataSource ds = new ComboPooledDataSource();
        Connection conn = null;
        try {
            conn = ds.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        String sql = "SELECT * FROM person";
        ResultSet resultSet = null;
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            resultSet = ps.executeQuery();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        try {
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println("id:" + id + " name:" + name);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
}

配置文件

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--配置默认节点-->
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost/test1</property>
        <property name="user">root</property>
        <property name="password">sr20000316</property>
    </default-config>
    <!--配置自定义节点-->
    <named-config name="myApp">
        <property name="user">root</property>
        <property name="password">root</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/curd</property>
        <!--初始化连接个数-->
        <property name="initialPoolSize">10</property>
        <!--最大空闲时间 单位秒-->
        <property name="maxIdleTime">30</property>
        <!--初始化连接个数-->
        <property name="maxPoolSize">100</property>
    </named-config>
</c3p0-config>

Jar包：

c3p0-0.9.5.5.jar,mchange-commons-java-0.2.19.jar,mysql-connector-java-8.0.20.jar

Druid

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;


/**
 * 1. 导入jar包
 * 2. 添加配置文件
 * 3. 加载配置文件
 * 4. 获取DataSource
 * 5. 获取Connection
 * 6. 操作数据库
 * 7. 释放资源
 *
 * 与C3P0不同的是，在获取DataSource时，C3P0使用的是ComboPooledDataSource()
 * 而Druid使用的是DruidDataSourceFactory.createDataSource
 *
 *
 */

public class Demo {
    public static void main(String[] args) throws Exception {
        Properties p = new Properties();
        p.load(new FileReader("D:\\JAVA学习\\Druid\\src\\druid.properties"));
        DataSource ds = DruidDataSourceFactory.createDataSource(p);
        Connection conn = ds.getConnection();
        String sql = "SELECT* FROM person";
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            int id = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println("id:" + id + " name:" + name);
        }
        rs.close();
        ps.close();
    }
}

配置文件

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///test1
username=root
password=sr20000316
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

Jar包

druid-1.1.22.jar，mysql-connector-java-8.0.20.jar

Spring-JDBC框架

Spring的JDBC模块负责数据库资源管理和错误处理，大大简化了开发人员对数据库的操作，针对数据库的操作， Spring框架提供了 JdbcTemplate类，该类是 Spring框架数据抽象层的基础，其他更高层次的抽象类却是构建于 JdbcTemplate类之上。

测试类

import org.junit.Test;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class Demo {
    JdbcTemplate jst = new JdbcTemplate(utils.JDBCutils.getDataSource());

    /**
     * 跟新语句
     */
    @Test
    public void t1(){
        String sql = "UPDATE person SET id = ? WHERE name = ?";
        jst.update(sql,10010,"Li");
    }

    /**
     * 将查询的数据封装到Map中
     */
    @Test
    public void t2(){
        String sql = "SELECT * FROM person WHERE id = ?";
        Map<String, Object> map = jst.queryForMap(sql, 1004);
        System.out.println(map.get("id") + " " + map.get("name"));
    }

    /**
     * 将查询的数据封装到User对象中
     */
    @Test
    public void t3(){
        String sql = "SELECT * FROM person";
        List<User> users = jst.query(sql, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                return new User(id, name);
            }
        });
        System.out.println(users.toString());
    }

    /**
     * 将查询的数据放入list集合中
     */
    @Test
    public void t4(){
        String sql = "SELECT* FROM person";
        List<Map<String, Object>> list = jst.queryForList(sql);
        for(int i = 0 ; i < list.size() ; i++){
            System.out.println(list.get(i).get("id") + " " + list.get(i).get("name"));
        }
    }

    /**
     * queryForObject()实现聚合查询，第二个参数为long.class，返回一个long类型的参数
     */
    @Test
    public void t5(){
        String sql =  "SELECT COUNT(*) FROM person";
        long cnt = jst.queryForObject(sql,long.class);
        System.out.println(cnt);
    }
}


class User{
    private int id;
    private String name;


    public User(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

工具类

package utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.ConnectionEvent;
import javax.sql.DataSource;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class JDBCutils {
    static DataSource ds;
    static {
        Properties p = new Properties();
        try {
            p.load(new FileReader("D:\\JAVA学习\\Spring-JDBC\\src\\druid.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            ds = DruidDataSourceFactory.createDataSource(p);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }


    public static DataSource getDataSource(){
        return ds;
    }

    public static void close(Connection coo, Statement st, ResultSet rs){
        if(coo != null){
            try {
                coo.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}

配置文件

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql:///test1
username=root
password=sr20000316
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000

Jar包

commons-logging-1.2.jar，druid-1.1.22.jar，mysql-connector-java-8.0.20.jar，spring-beans-5.2.5.RELEASE.jar，spring-core-5.2.5.RELEASE.jar，spring-jdbc-5.2.5.RELEASE.jar，spring-tx-5.2.5.RELEASE.jar