JDBC连接池(c3p0和Druid)&JDBCTemplate

一.c3p0

C3PO︰数据库连接池技术
步骤∶

1. 导入jar包（两个) c3p0-0.9.5.5.jar mchange-commons-java-0.2.19.jar,
   *不要忘记导入数据库驱动jar包
2. 定义配逆i文件∶
   名称:c3po.properties或者c3po-config.xml*路径∶直接将文件放在src目录下即可。
3. 创建核心对象数据库连接池对象comboPooledDataSource
4. 获取连接∶getconnection

package cn.itcast.datasource.c3p0;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

public class C3P0Demo2 {


    public static void main(String[] args) throws SQLException {
//        DataSource ds=new ComboPooledDataSource();
//        for(int i=0;i<10;i++){
//            Connection conn= null;
//            try {
//                conn = ds.getConnection();
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
//            System.out.println(conn);
//            if(i==5){
//                conn.close();
//            }
//        }
        testNamedConfig();
    }
    public static void testNamedConfig() throws SQLException{
        DataSource ds=new ComboPooledDataSource("intergalactoApp");
        for (int i = 0; i < 10; i++) {
            Connection conn= ds.getConnection();
            System.out.println(i+":"+conn);
        }
    }
}

c3p0-config.xml

<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test? characterEncoding=utf8 &amp; useSSL=false &amp; serverTimezone=UTC &amp; rewriteBatchedStatements=true</property>
        <property name="user">root</property>
        <property name="password">24365769</property>

        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">10</property>
        <property name="checkoutTimeout">3000</property>
    </default-config>

    <!-- This app is massive! -->
    <named-config name="intergalactoApp">
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test? characterEncoding=utf8 &amp; useSSL=false &amp; serverTimezone=UTC &amp; rewriteBatchedStatements=true</property>
        <property name="user">root</property>
        <property name="password">24365769</property>

        <property name="initialPoolSize">5</property>
        <property name="maxPoolSize">8</property>
        <property name="checkoutTimeout">3000</property>
    </named-config>
</c3p0-config>

二.Druid

Druid:数据库连接池实现技术，由阿里巴巴提供的

• 步骤︰

1. 导入jar包druid-1.0.9.jar
2. 定义配i文件:
   *是properties形式的
   *可以叫任意名称，可以放在任意目录下
3. 加载配置文件。Properties
4. 获取数据库连接池对象:通过工厂来来获取DruidDataSourceFactory
5. 获取连接:getconnection

package cn.itcast.datasource.druid;

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

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

public class DruidDemo {
    public static void main(String[] args) throws Exception {

        Properties pro=new Properties();
        InputStream is = DruidDemo.class.getClassLoader().getResourceAsStream("druid.properties");
        pro.load(is);
        DataSource ds = DruidDataSourceFactory.createDataSource(pro);
        Connection conn = ds.getConnection();
        System.out.println(conn);
    }
}

druid.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test? characterEncoding=utf8 & useSSL=false & serverTimezone=UTC & rewriteBatchedStatements=true
username=root
password=24365769
initialSize=5
maxActive=10
maxWait=3000

• 定义工具类

1. 定义一个类JDBcUtils
2. 提供静态代码块加载配置文件，初始化连接池对象
3. 提供方法
   1．获取连接方法:通过数据库连接池获取连接
   2．释放资源
   3．获取连接池的方法

package cn.itcast.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
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 {
   //1.定义成员变量Datasource
   private static DataSource ds;
   static {
       //1.加载配置文件
       Properties pro=new Properties();
       try {
           pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
           ds= DruidDataSourceFactory.createDataSource(pro);
       } catch (IOException e) {
           e.printStackTrace();
       } catch (Exception e) {
           e.printStackTrace();
       }

   }
   public static Connection getConnection() throws SQLException {
       return ds.getConnection();
   }

   public static void close(Statement stmt,Connection conn){
       if(stmt!=null){
           try {
               stmt.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
       if(conn!=null){
           try {
               conn.close();//归还连接，不是关闭连接
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
   }
   public static void close(ResultSet rs,Statement stmt, Connection conn){
       if(rs!=null){
           try {
               rs.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
       if(stmt!=null){
           try {
               stmt.close();
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
       if(conn!=null){
           try {
               conn.close();//归还连接，不是关闭连接
           } catch (SQLException e) {
               e.printStackTrace();
           }
       }
   }
   public static DataSource getDataSource(){
       return ds;
   }
}

package cn.itcast.datasource.druid;

import cn.itcast.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DruidDemo2 {
    public static void main(String[] args) {
        Connection conn=null;
        PreparedStatement pstmt=null;
        try {
            //1.获取链接
            conn= JDBCUtils.getConnection();
            //2.定义sql
            String sql="insert into t_user values(?,?,?)";
            //3.获取pstmt对象
            pstmt=conn.prepareStatement(sql);
            //4.赋值
            pstmt.setString(1,"2018");
            pstmt.setString(2,"李四");
            pstmt.setString(3,"123456");
            //5.执行sql
            int count=pstmt.executeUpdate();
            System.out.println(count);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(pstmt,conn);
        }

    }
}

三、JDBCTemplate

• Spring框架对DBC的简单封装。提供了一个DBCTemplate对象简化JDBC的开发
• 步骤∶
  1. 导入jar包
     spring-tx-5.2.9.RELEASE.jar,
     spring-jdbc-5.2.9.RELEASE.jar,
     spring-core-5.2.9.RELEASE.jar,
     spring-beans-5.2.9.RELEASE.jar
     commons-logging-1.2.jar
  2. 创建dbcTemplate对象。依赖于数据源Datasource
     jdbcTemplate template = new JdbcTemplate(ds);
  3. 调用dbcTemplate的方法来完成CRUD的操作
     update():执行DML语句。增、删、改语句
     queryForMap():查询结果将结果集封装为map集合
     *注意:这个方法查询的结果集长度只能是1
     queryForList(:查询结果将结果集封装为list集合
     *注意:将每一条记录封装为一-个Map集合, 再将Map集合装载到List集合中
     query():查询结果，将结果封装为JavaBean对象
     * query的参数: RowMapper
     *一般我们使用BeanPropertyRowMapper实现类。 可以完成数据到JavaBean的 自动封装
     * new BeanPropertyRowMapper<类型>(类型.class)
     queryForobject :查询结果，将结果封装为对象
     * 一般用于聚合函数的查询

package cn.itcast.jdbctemplate;

import cn.itcast.utils.JDBCUtils;
import org.springframework.jdbc.core.JdbcTemplate;

public class JdbcTemplateDemo1 {
    public static void main(String[] args) {
        //1.导入jar包
        //2.创建JDBCTemplate对象
        JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());
        //3.调用方法
        String sql="update t_user set username='礼物' where userid=?";
        int count = template.update(sql, "2018");
        System.out.println(count);

    }
}

4. 练习:
   需求:
   1.修改数据
   2.添加一条记录
   3.删除刚才添加的记录
   4.查询userid为2018的记录，将其封装为Map集合
   5.查询所有记录，将其封装为List
   6.查询所有记录，将其封装为User对象的List集合
   7.查询总记录数

package cn.itcast.jdbctemplate;

import cn.itcast.domain.User;
import cn.itcast.utils.JDBCUtils;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
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 JdbcTemplateDemo2 {
    private JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());
    @Test
    public void test1(){

        String sql="update t_user set username='李六' where userid=2018";
        int count = template.update(sql);
        System.out.println(count);
    }
    @Test
    public void test2(){
        String sql="insert into t_user values(?,?,?)";
        int count = template.update(sql, "2019", "哈哈", "123456");
        System.out.println(count);
    }
    @Test
    public void test3(){
        String sql="delete from t_user where userid=?";
        int count = template.update(sql, 2019);
        System.out.println(count);
    }
    @Test
    public void test4(){
        String sql="select * from t_user where userid=?";
        Map<String, Object> map = template.queryForMap(sql, "2018");
        System.out.println(map);
    }
    @Test
    public void test5(){
        String sql="select * from t_user";
        List<Map<String, Object>> maps = template.queryForList(sql);
        System.out.println(maps);
    }
    @Test
    public void test6(){
        String sql="select * from t_user";
        List<User> list = template.query(sql, new RowMapper<User>() {

            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                User user=new User();
                String userid=resultSet.getString("userid");
                String username=resultSet.getString("username");
                String password=resultSet.getString("password");
                user.setUserid(userid);
                user.setUsername(username);
                user.setPassword(password);
                return user;
            }
        });
        for (User user:list
             ) {
            System.out.println(user);
        }
    }
    @Test
    public void test7(){
        String sql="select * from t_user";
        List<User> list = template.query(sql, new BeanPropertyRowMapper<User>(User.class));
        for (User user:list
             ) {
            System.out.println(user);
        }
    }
    @Test
    public void test8(){
        String sql="select count(userid) from t_user";
        Long total = template.queryForObject(sql, Long.class);
        System.out.println(total);
    }
}