数据库连接池

（1）概念

• 系统初始化之后，容器被创建，容器中会申请一些连接对象，当用户用来访问数据库时，从容器中获取连接对象，用户访问完之后，会将连接对象返还给容器
• 好处
  • 节约资源
  • 访问高效

• 常见数据库连接池

  • C3P0
  • Druid：阿里巴巴实现的

    （2）C3P0

    环境配置

• 下载地址：https://www.mchange.com/projects/c3p0/

• 导入两个jarmchange-commons-java-0.2.19.jarc3p0-0.9.5.5.jar

• class目录下新建一个c3p0.properties or c3p0-config.xml，这里定义数据库url密码

  <c3p0-config>
   <!-- 使用默认的配置读取连接池对象 -->
   <default-config>
      <!--  连接参数 -->
      <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
      <property name="user">root</property>
      <property name="password">root</property>
      <!-- 连接池参数 -->
      <!--初始化申请的连接数量-->
      <property name="initialPoolSize">5</property>
      <!--最大的连接数量-->
      <property name="maxPoolSize">10</property>
      <!--超时时间-->
      <property name="checkoutTimeout">3000</property>
   </default-config>
   <named-config name="otherc3p0">
      <!--  连接参数 -->
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql://localhost:3306/test</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>

• 建立连接 ``` import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource; import java.sql.*;

public class Main {

public static void main(String[] args) {

    // 创建数据库连接池 什么都不传，使用默认配置，传字符串使用对应的配置
    DataSource ds = new ComboPooledDataSource();

    try {
        Connection connection = ds.getConnection();
        System.out.println(connection);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

}

<a name="yOg1w"></a>
#### maxPoolSize

- 超过连接上限就会崩溃
- connection.close();只是返还给连接池，并没有释放数据库连接

    // 创建数据库连接池
    DataSource ds = new ComboPooledDataSource();

    try {

        // 超过连接池数量上限
        // An attempt by a client to checkout a Connection has timed out.
        for (int i = 0; i < 11; i++) {
            Connection connection = ds.getConnection();
            // connection.close();
            System.out.println(i+","+connection);
        }


    } catch (SQLException e) {
        e.printStackTrace();
    }

<a name="2LhWT"></a>
### （3）Druid数据库连接池

- 官网：[https://github.com/alibaba/druid/](https://github.com/alibaba/druid/)
- 阿里巴巴提供的[druid-1.1.23.jar](https://www.yuque.com/attachments/yuque/0/2020/jar/503653/1600047905334-11bf2122-d3a3-4975-9308-f39d46796ab6.jar?_lake_card=%7B%22status%22%3A%22done%22%2C%22source%22%3A%22transfer%22%2C%22src%22%3A%22https%3A%2F%2Fwww.yuque.com%2Fattachments%2Fyuque%2F0%2F2020%2Fjar%2F503653%2F1600047905334-11bf2122-d3a3-4975-9308-f39d46796ab6.jar%22%2C%22name%22%3A%22druid-1.1.23.jar%22%2C%22ext%22%3A%22jar%22%2C%22size%22%3A2866573%2C%22id%22%3A%227tv0x%22%2C%22card%22%3A%22file%22%7D)
- 导入jar，配置文件druid.properties（src目录下）

driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/test username=root password=root initialSize=5 maxActive=10 maxWait=3000

- 基本使用

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties;

public class Main {

public static void main(String[] args) {

    try {
        // 加载配置文件
        Properties properties = new Properties();
        InputStream asStream = Main.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(asStream);

        // 获取连接池对象
        DataSource ds = DruidDataSourceFactory.createDataSource(properties);

        // 获取对象
        Connection connection = ds.getConnection();
        System.out.println(connection);
    } catch (IOException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

}

- druid工具类封装

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties;

public class JDBCUtils {

private static DataSource ds;

static {
    // 加载配置文件
    try {
        Properties properties = new Properties();
        InputStream asStream = Main.class.getClassLoader().getResourceAsStream("druid.properties");
        properties.load(asStream);
        ds = DruidDataSourceFactory.createDataSource(properties);
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }

}

public static Connection getConnection() throws SQLException {

    return ds.getConnection();
}

/**
 * 释放资源DQL
 *
 * @param statement
 * @param connection
 */
public static void close(ResultSet resultSet, Statement statement, Connection connection) {

    if (resultSet != null) {
        try {
            resultSet.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    close(statement, connection);
}

/**
 * 释放资源DML
 *
 * @param statement
 * @param connection
 */
public static void close(Statement statement, Connection connection) {
    if (connection != null) {
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    if (statement != null) {
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

/**
 * 获取连接池
 * @return
 */
public static DataSource getDataSource(){
    return ds;
}

}

- 工具类使用

import java.sql.*;

public class Main {

public static void main(String[] args) {

    Connection connection = null;
    PreparedStatement statement = null;

    try {
        connection = JDBCUtils.getConnection();
        System.out.println(connection);
        String sql = "insert card values(null,?,?);";
        statement = connection.prepareStatement(sql);
        statement.setInt(1,3000);
        statement.setString(2,"123445");
        int i = statement.executeUpdate();
        System.out.println(i);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        JDBCUtils.close(statement,connection);
    }
}

}

<a name="lk2Ub"></a>
### （4）Spring框架对JDBC的封装JDBCTemplate

- [https://repo.spring.io/release/org/springframework/spring/](https://repo.spring.io/release/org/springframework/spring/)
- 从中找出这四个jar
- [spring-tx-5.0.0.RELEASE.jar](https://www.yuque.com/attachments/yuque/0/2020/jar/503653/1600675257060-02cf0bc0-de21-4913-8811-1bb250c2aa6d.jar?_lake_card=%7B%22status%22%3A%22done%22%2C%22source%22%3A%22transfer%22%2C%22src%22%3A%22https%3A%2F%2Fwww.yuque.com%2Fattachments%2Fyuque%2F0%2F2020%2Fjar%2F503653%2F1600675257060-02cf0bc0-de21-4913-8811-1bb250c2aa6d.jar%22%2C%22name%22%3A%22spring-tx-5.0.0.RELEASE.jar%22%2C%22ext%22%3A%22jar%22%2C%22size%22%3A248889%2C%22id%22%3A%22Y1fFR%22%2C%22card%22%3A%22file%22%7D)[spring-jdbc-5.0.0.RELEASE.jar](https://www.yuque.com/attachments/yuque/0/2020/jar/503653/1600675270533-3930ada6-6982-4f88-a907-36ef849af6c4.jar?_lake_card=%7B%22status%22%3A%22done%22%2C%22source%22%3A%22transfer%22%2C%22src%22%3A%22https%3A%2F%2Fwww.yuque.com%2Fattachments%2Fyuque%2F0%2F2020%2Fjar%2F503653%2F1600675270533-3930ada6-6982-4f88-a907-36ef849af6c4.jar%22%2C%22name%22%3A%22spring-jdbc-5.0.0.RELEASE.jar%22%2C%22ext%22%3A%22jar%22%2C%22size%22%3A390640%2C%22id%22%3A%2258uxj%22%2C%22card%22%3A%22file%22%7D)[spring-core-5.0.0.RELEASE.jar](https://www.yuque.com/attachments/yuque/0/2020/jar/503653/1600675273119-64d38dd1-8a23-4c16-b2ab-f4edbe3cdb53.jar?_lake_card=%7B%22status%22%3A%22done%22%2C%22source%22%3A%22transfer%22%2C%22src%22%3A%22https%3A%2F%2Fwww.yuque.com%2Fattachments%2Fyuque%2F0%2F2020%2Fjar%2F503653%2F1600675273119-64d38dd1-8a23-4c16-b2ab-f4edbe3cdb53.jar%22%2C%22name%22%3A%22spring-core-5.0.0.RELEASE.jar%22%2C%22ext%22%3A%22jar%22%2C%22size%22%3A1207463%2C%22id%22%3A%22WWA1Z%22%2C%22card%22%3A%22file%22%7D)[spring-beans-5.0.0.RELEASE.jar](https://www.yuque.com/attachments/yuque/0/2020/jar/503653/1600675275045-66aa708f-44a0-4976-a5bd-b30decf9ded1.jar?_lake_card=%7B%22status%22%3A%22done%22%2C%22source%22%3A%22transfer%22%2C%22src%22%3A%22https%3A%2F%2Fwww.yuque.com%2Fattachments%2Fyuque%2F0%2F2020%2Fjar%2F503653%2F1600675275045-66aa708f-44a0-4976-a5bd-b30decf9ded1.jar%22%2C%22name%22%3A%22spring-beans-5.0.0.RELEASE.jar%22%2C%22ext%22%3A%22jar%22%2C%22size%22%3A654710%2C%22id%22%3A%22GbK2c%22%2C%22card%22%3A%22file%22%7D)
- [http://commons.apache.org/proper/commons-logging/download_logging.cgi](http://commons.apache.org/proper/commons-logging/download_logging.cgi)
- 还需要下载一个日志工具
- [[commons-logging-1.2.jar](https://www.yuque.com/attachments/yuque/0/2020/jar/503653/1600676706947-3b9978b6-3375-4f85-a189-39848a4a41da.jar?_lake_card=%7B%22status%22%3A%22done%22%2C%22source%22%3A%22transfer%22%2C%22src%22%3A%22https%3A%2F%2Fwww.yuque.com%2Fattachments%2Fyuque%2F0%2F2020%2Fjar%2F503653%2F1600676706947-3b9978b6-3375-4f85-a189-39848a4a41da.jar%22%2C%22name%22%3A%22commons-logging-1.2.jar%22%2C%22ext%22%3A%22jar%22%2C%22size%22%3A61829%2C%22id%22%3A%22lXncA%22%2C%22card%22%3A%22file%22%7D)](http://commons.apache.org/proper/commons-logging/download_logging.cgi)
<a name="s3D5q"></a>
#### update

    // 添加一条记录
    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    String sql = "insert card values(null,?,?);";
    int abc = jdbcTemplate.update(sql, 6000, "abc");
    System.out.println(abc);

    // 更新数据
    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    String sql = "update card set money = 10000 where id = ?;";
    int abc = jdbcTemplate.update(sql,3);
    System.out.println(abc);

    // 删除
    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    String sql = "delete from card where id = ?;";
    int abc = jdbcTemplate.update(sql,3);
    System.out.println(abc);

<a name="svt3H"></a>
#### queryForMap

    // queryForMap 结果集长度只能一条，否则报错Incorrect result size: expected 1, actual 5
    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    String sql = "select * from card where id = ?;";
    Map<String, Object> map = jdbcTemplate.queryForMap(sql, 4);
    // {id=4, money=2000, name=zhangfei}
    System.out.println(map);

<a name="PcxlV"></a>
#### queryForObject

- 将查询结果封装成一个对象

    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    String sql = "select count(id) from card;";
    Integer total = jdbcTemplate.queryForObject(sql, Integer.class);
    System.out.println(total);

<a name="3wU64"></a>
#### queryForList

    // 结果是一个列表，每个item是map
    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    String sql = "select * from card;";
    List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
    for (int i = 0; i < maps.size(); i++) {
        System.out.println(maps.get(i));
    }

<a name="fsrD9"></a>
#### query(sql, RowMapper)

- 自定义RowMapper

    // 结果封装对对象，自定义
    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    String sql = "select * from card;";
    List<Card> maps = jdbcTemplate.query(sql,new RowMapper<Card>(){

        @Override
        public Card mapRow(ResultSet resultSet, int i) throws SQLException {
            Card card = new Card();
            int id = resultSet.getInt("id");
            int money = resultSet.getInt("money");
            String name = resultSet.getString("name");

            card.setId(id);
            card.setMoney(money);
            card.setName(name);
            return card;
        }
    });
    for (int i = 0; i < maps.size(); i++) {
        System.out.println(maps.get(i).toString());
    }

- BeanPropertyRowMapper

    JdbcTemplate jdbcTemplate = new JdbcTemplate(JDBCUtils.getDataSource());
    String sql = "select * from card;";
    List<Card> maps = jdbcTemplate.query(sql,new BeanPropertyRowMapper<Card>(Card.class));
    for (int i = 0; i < maps.size(); i++) {
        System.out.println(maps.get(i).toString());
    }

```