二十五、JDBC和数据库连接池

一、JDBC概述

二、JDBC快速入门

2.1 添加驱动

• 新建 libs 文件夹 --> 复制驱动 --> 右键 .jar 文件 Add as Library …
  

2.2 执行sql

package com.gyhedu.jdbc.myjdbc;

import com.mysql.jdbc.Driver;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author Gao YongHao
 * @version 1.0
 */
public class JDBC01 {
    public static void main(String[] args) throws SQLException {
        // 前置工作：在项目下创建一个文件夹比如 libs
        // 将 mysql.jar 拷贝到该目录下，点击 add to project .. 加入到项目中

        // 1. 注册驱动
        Driver driver = new Driver();

        // 2. 得到连接
        // 老师解读
        // (1) jdbc:mysql:// 规定好表示协议，通过jdbc的方式连接mysql
        // (2) localhost  主机, 可以是ip地址
        // (3) 13306  表示mysql监听的端口
        // (4) dbtest1  连接到 mysql dbms 的哪个数据库
        // (5) mysql的连接本质就是前面学过的socket连接
        String url = "jdbc:mysql://localhost:13306/dbtest1";
        // 将用户和密码封装到Properties中
        Properties properties = new Properties();
        properties.setProperty("user","root"); // 用户
        properties.setProperty("password","gyhgzf"); // 用户

        // 网络连接
        Connection connect = driver.connect(url, properties);

        // 3、执行sql
        String sql = "INSERT INTO actor(name, sex, borndate, phone) VALUES('刘德华', '男', '1970-11-11', '110')";

        // statement 用于执行静态SQL语句并返回其生成的结果的对象
        Statement statement = connect.createStatement();
        int rows = statement.executeUpdate(sql);

        System.out.println(rows > 0?"成功":"失败");

        // 关闭连接
        statement.close();
        connect.close();
    }

}

2.3 获取数据库连接5种方式

2.4 ResultSet[结果集]

package com.gyhedu.jdbc.myjdbc;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @author Gao YongHao
 * @version 1.0
 */
public class JDBC03 {
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
        // 0. 获取连接数据库的配置信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");

        // 1. 动态加载类（注册驱动）
        Class.forName(driver);

        // 2. 获取连接
        Connection connection = DriverManager.getConnection(url, user, password);

        // 3. 得到 statement 对象
        Statement statement = connection.createStatement();

        // 4. 组织sql语句
        String sName = "刘德华";
        String sql = "SELECT id, `name`, sex, borndate, phone FROM actor WHERE `name` = '" + sName + "';";

        // 4. 得到结果集对象
        ResultSet resultSet = statement.executeQuery(sql);

        // 5. 使用while循环遍历数据
        while (resultSet.next()) {
            int id = resultSet.getInt(1); // 获取该该行第一列数据
            String name = resultSet.getString(2); // 获取该该行第二列数据
            String sex = resultSet.getString(3); // 获取该该行第三列数据
            String bornDate = resultSet.getString(4); // 获取该该行第四列数据
            String phone = resultSet.getString(5); // 获取该该行第五列数据
            System.out.println(id + '\t' + name + '\t' + sex + '\t' + bornDate + '\t' + phone);
        }

        resultSet.close();
        statement.close();
        connection.close();

    }
}

2.5 statement（SQL注入）

2.6 PreparedStatement

package com.gyhedu.jdbc.myjdbc;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @author Gao YongHao
 * @version 1.0
 */
public class PreparedStatement01 {
    public static void main(String[] args) throws ClassNotFoundException, IOException, SQLException {
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String driver = properties.getProperty("driver");
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        Class.forName(driver);

        Connection connection = DriverManager.getConnection(url, user, password);
        // ? 相当于占位符
        String sql = "SELECT COUNT(*) FROM actor WHERE id > ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 2);
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            System.out.println(resultSet.getInt(1));
        }
        // 关闭流
        preparedStatement.close();
        connection.close();

    }
}

三、JDBC API

四、JDBCUtils

package com.gyhedu.jdbc.jdbcutils;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * @author Gao YongHao
 * @version 1.0
 */
public class JDBCUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\mysql.properties"));
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            driver = properties.getProperty("driver");
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.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();
            }
        }
    }
}

package com.gyhedu.jdbc.jdbcutils;

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

/**
 * @author Gao YongHao
 * @version 1.0
 */
public class JDBCUtilsTest {
    public static void main(String[] args) throws SQLException {
        // 获取连接
        Connection connection = JDBCUtils.getConnection();
        String sql = "SELECT id, `name`, sex, borndate, phone FROM actor WHERE id > ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 1);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt(1);
            String name = resultSet.getString(2);
            String sex = resultSet.getString(3);
            String bornDate = resultSet.getString(4);
            String phone = resultSet.getString(5);
            System.out.println(id + "\t" + name + "\t" + sex + "\t" + bornDate + "\t" + phone);
        }

        // 关闭连接
        JDBCUtils.close(resultSet, preparedStatement, connection);

    }
}

五、事务

5.1 基本介绍

5.2 应用实例

package com.gyhedu.jdbc.event;

import com.gyhedu.jdbc.jdbcutils.JDBCUtils;
import jdk.nashorn.internal.scripts.JD;

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

/**
 * @author Gao YongHao
 * @version 1.0
 */
public class Event {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        PreparedStatement preparedStatement1 = null;

        try {
            connection = JDBCUtils.getConnection();
            // 设置不自动提交(开启事务)
            connection.setAutoCommit(false);
            // 马云向马化腾转100元
            String sql1 = "UPDATE account SET balance = balance - ? WHERE name = ?";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, 100);
            preparedStatement.setString(2, "马云");

            String sql2 = "UPDATE account SET balance = balance + ? WHERE name = ?";
            preparedStatement1 = connection.prepareStatement(sql2);
            preparedStatement1.setInt(1, 100);
            preparedStatement1.setString(2, "马化腾");

            preparedStatement.executeUpdate();
            int n = 1 / 0;
            preparedStatement1.executeUpdate();
            
            // 手动提交
            connection.commit();


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (connection != null) {
                try {
                    // 异常回滚
                    connection.rollback();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            JDBCUtils.close(null, preparedStatement, connection);
            JDBCUtils.close(null, preparedStatement1, null);
        }

    }
}

六、批处理

七、连接池

7.1 基本介绍

7.2 数据库连接池种类

7.3 C3P0应用实例

package com.gyhedu.jdbc.dbpool;

import com.gyhedu.jdbc.jdbcutils.JDBCUtils;
import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @author Gao YongHao
 * @version 1.0
 */
public class C3P0Test {
    public static void main(String[] args) throws SQLException {
        c3p0Test_02();
    }

    // 方式一：使用
    private static void testC3P0_01() throws Exception {
        // 1、创建一个数据源对象
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        // 2、通过配置文件mysql.properties获取相关连接的信息
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\mysql.properties"));
        String url = properties.getProperty("url");
        String user = properties.getProperty("user");
        String password = properties.getProperty("password");
        String driver = properties.getProperty("driver");

        // 给数据源 comboPooledDataSource 设置相关参数
        // 注意：连接管理是由 comboPooledDataSource 管理的
        comboPooledDataSource.setDriverClass(driver);
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);

        // 设置初始化连接数
        comboPooledDataSource.setInitialPoolSize(10);

        // 设置最大连接数
        comboPooledDataSource.setMaxPoolSize(50);

        // 这个方法就是对 DataSource 接口实现
        Connection connection = comboPooledDataSource.getConnection();

        connection.close();
    }

    // 方式二：使用配置文件模板来操作

    // 1. 将c3p0提供的 c3p0-config.xml 拷贝到 src 目录下
    // 2. 该文件指定了相关的参数
    private static void c3p0Test_02() throws SQLException {
        ComboPooledDataSource myc3p0 = new ComboPooledDataSource("Myc3p0");
        Connection connection = myc3p0.getConnection();
        System.out.println("连接成功");
        connection.close();
    }
}

<!-- c3p0-config.xml -->
<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
    <!-- 数据源名称代表连接池 -->
    <named-config name="Myc3p0">

        <!-- 指定连接数据源的基本属性 -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:13306/dbtest1</property>
        <property name="user">root</property>
        <property name="password">gyhgzf</property>

        <!-- 若数据库中连接数不足时, 一次向数据库服务器申请多少个连接 -->
        <!-- 每次增长的连接数，即在小于最大连接数的情况下，每次新增的连接数 -->
        <property name="acquireIncrement">5</property>
        <!-- 初始化数据库连接池时连接的数量 -->
        <property name="initialPoolSize">5</property>
        <!-- 数据库连接池中的最小的数据库连接数 -->
        <property name="minPoolSize">5</property>
        <!-- 数据库连接池中的最大的数据库连接数 -->
        <property name="maxPoolSize">10</property>

        <!-- C3P0 数据库连接池可以维护的 Statement 的个数 -->
        <property name="maxStatements">20</property>
        <!-- 每个连接同时可以使用的 Statement 对象的个数 -->
        <property name="maxStatementsPerConnection">5</property>
    
    </named-config>
        
</c3p0-config>

7.4 Druid应用实例

package com.gyhedu.jdbc.dbpool;

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

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

/**
 * @author Gao YongHao
 * @version 1.0
 */
public class DruidTest {
    public static void main(String[] args) throws Exception {
        // 1. 加入 Druid 的jar包
        // 2. 加入配置文件 druid.properties，将该文件拷贝至项目的src目录
        // 3. 创建Properties对象
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\druid.properties"));

        // 4. 创建一个指定参数的数据库连接池,Druid连接池
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        System.out.println("连接成功");
        connection.close();

    }
}

#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:13306/dbtest1?rewriteBatchStatements=true
#url=jdbc:mysql://localhost:13306/dbtest1
username=root
password=gyhgzf
#initial connection Size
initialSize=10
#min idle connection size
minIdle=5
#max active connection size
maxActive=50
#max wait time（5000 mil seconds）
maxWait=5000

package com.gyhedu.jdbc.jdbcutils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

/**
 * @author Gao YongHao
 * @version 1.0
 */
public class JDBCUtilsByDruid {
    private static DataSource dataSource;
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    
    // 关闭连接池。在数据库连接池技术中，close 不是真的断掉连接
    // 而是把使用的Connection对象放回连接池
    public static void close(ResultSet resultSet, Statement statement, Connection connection){
        if(resultSet != null){
            try {
                resultSet.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();
            }
        }
        
    }
}

八、Apache — DBUtils

8.1 自定义的解决方法

package com.gyhedu.jdbc.jdbcutils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @author Gao YongHao
 * @version 1.0
 */
public class JDBCUtilsByDruidTest {
    public static void main(String[] args) throws SQLException, InstantiationException, IllegalAccessException {
        Connection connection = JDBCUtilsByDruid.getConnection();
        String sql = "SELECT id, name, sex, borndate, phone FROM actor WHERE id >= ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 1);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<Actor> result = JDBCUtilsByDruid.getResult(resultSet, Actor.class);
        result.forEach(System.out::println);
        JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
    }
}

package com.gyhedu.jdbc.jdbcutils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

/**
 * @author Gao YongHao
 * @version 1.0
 */
public class JDBCUtilsByDruid {
    private static DataSource dataSource;
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

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

    public static <T> List<T> getResult(ResultSet resultSet, Class<T> beanClass) throws SQLException, IllegalAccessException, InstantiationException {
        List<T> list = new ArrayList<>();
        Field[] declaredFields = beanClass.getDeclaredFields();
        while(resultSet.next()){
            T entity = null;
            entity = beanClass.newInstance();
            for(Field f:declaredFields){
                f.setAccessible(true);
                f.set(entity,resultSet.getObject(f.getName()));
            }
            list.add(entity);
        }
        return list;
    }

    // 关闭连接池。在数据库连接池技术中，close 不是真的断掉连接
    // 而是把使用的Connection对象放回连接池
    public static void close(ResultSet resultSet, Statement statement, Connection connection){
        if(resultSet != null){
            try {
                resultSet.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();
            }
        }

    }
}

class Actor{
    private Integer id;
    private String name;
    private String sex;
    private Date borndate;
    private String phone;

    public Actor() { // 一定要给一个无参构造器
    }

    public Actor(Integer id, String name, String sex, Date borndate, String phone) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.borndate = borndate;
        this.phone = phone;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBorndate() {
        return borndate;
    }

    public void setBorndate(Date borndate) {
        this.borndate = borndate;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "Actor{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", borndate=" + borndate +
                ", phone='" + phone + '\'' +
                '}';
    }
}

8.2 Apache — DBUtils

package com.gyhedu.jdbc.jdbcutils;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author Gao YongHao
 * @version 1.0
 */
public class DBUtilsUse {
    public static void main(String[] args) throws SQLException {
        // 1. 得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        // 2. 使用 DBUtils 类和接口，先引入 DBUtils 相关的jar，加入到本 Project
        // 3. 创建 QueryRunner
        QueryRunner queryRunner = new QueryRunner();
        // 4. 就可以执行相关的方法，返回ArrayList结果集
        String sql = "SELECT * FROM actor WHERE id >= ?";
        // 解读
        // (1) query 方法就是执行sql语句，得到 ResultSet --- 封装到 ---> ArrayList 集合中
        // (2) 返回集合
        // (3) connection：连接
        // (4) sql：执行的sql语句
        // (5) new BeanListHandler<>(Actor.class)：在将resultset -> Actor对象 -> 封装到 ArrayList
        // (6) 1 就是给 sql 语句中 ? 赋值，可以多个值，因为是可变参数
        // (7) 底层的带的 resultset，会在query中关闭，关闭PreparedStatement
        List<Actor> list =
                queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1);
        System.out.println("输出集合的信息");
        for (Actor actor : list) {
            System.out.println(actor);
        }
        JDBCUtilsByDruid.close(null, null, connection);
    }
}

九、DAO增删改查 - BasicDao

• DAO：数据访问对象
  

// BasicDAO
package com.gyhedu.dao;

import com.gyhedu.jdbc.jdbcutils.JDBCUtils;
import com.gyhedu.utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * @author Gao YongHao
 * @version 1.0
 * 开发BasicDAO，是其他DAO的父类
 */
public class BasicDAO<T> {
    private QueryRunner queryRunner = new QueryRunner();

    // 开发通用的dml方法，针对任意的表
    public int update(String sql, Object... parameters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            int update = queryRunner.update(connection, sql, parameters);
            return update;
        } catch (SQLException e) {
            throw new RuntimeException(); // 将编译异常 -> 运行异常，抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }

    /**
     * @param sql        sql语句
     * @param clazz      传入一个类的Class对象 比如 Actor.class
     * @param parameters 传入 ? 的具体的值，可以多个
     * @param <T>
     * @return
     */
    // 返回多个对象（即查询的结果是多行），针对任意表
    public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            List<T> query =
                    queryRunner.query(connection, sql, new BeanListHandler<>(clazz), parameters);
            return query;
        } catch (SQLException e) {
            throw new RuntimeException(); // 将编译异常 -> 运行异常，抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }

    }

    /**
     * 返回第一行数据
     * @param sql
     * @param clazz
     * @param parameters
     * @param <T>
     * @return
     */
    public T querySingle(String sql, Class<T> clazz, Object... parameters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            T query =
                    queryRunner.query(connection, sql, new BeanHandler<>(clazz), parameters);
            return query;
        } catch (SQLException e) {
            throw new RuntimeException(); // 将编译异常 -> 运行异常，抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }

    }

    /**
     * 返回单值的方法
     * @param sql
     * @param parameters
     * @return
     */
    public Object queryScalar(String sql, Object... parameters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            Object query =
                    queryRunner.query(connection, sql, new ScalarHandler<>(), parameters);
            return query;
        } catch (SQLException e) {
            throw new RuntimeException(); // 将编译异常 -> 运行异常，抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }

    }

}

// ActorDAO
package com.gyhedu.dao;

import com.gyhedu.jdbc.jdbcutils.Actor;

/**
 * @author Gao YongHao
 * @version 1.0
 */
public class ActorDAO extends BasicDAO<Actor>{
    // 1. 就有 BasicDAO 方法
    // 2. 根据业务需求，可以编写特有的方法
    
}