1.批处理应用

1.基本介绍

2.批处理演示

1.创建测试表

-- 创建的测试表
CREATE TABLE admin2(
 id INT PRIMARY key auto_increment,
 username VARCHAR(32) NOT NULL,
 PASSWORD VARCHAR(32) NOT NULL
)
-- 查看表数据
SELECT * FROM admin2 
-- 查看行数
SELECT count(*) FROM admin2

2.修改url

?rewriteBatchedStatements=true //添加这行代码

3.编写java代码

package jdbc_;

import org.junit.jupiter.api.Test;
import utils.JDBCUtils;

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

/**
 * @author 孙显圣
 * @version 1.0
 */
public class Batch_ {
    @Test
    public void testDML() {
        //建立连接
        Connection connection = JDBCUtils.getConnection();

        //编写sql语句进行插入
        String sql = "insert into admin2 values (null, ?, ?)";

        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            //循环进行预处理，并添加到处理包中
            for (int i = 0; i < 5000; i++) {
                preparedStatement.setString(1, "Tom");
                preparedStatement.setString(2, "666666");
                //1.添加到处理包中，先不执行
                preparedStatement.addBatch();
                if ((i + 1) % 1000 == 0) {
                    //2.每执行1000次则执行一次
                    preparedStatement.executeBatch();
                    //3.执行之后清空处理包中的sql语句
                    preparedStatement.clearBatch();
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }
}

3.批处理源码分析

2.数据库连接池

1.传统连接弊端分析

2.数据库连接池基本介绍

1.概念介绍

2.数据库连接池示意图

3.数据库连接池种类

3.C3P0连接池

1.环境配置

1.导入jar包

2.将整个lib添加到项目中

3.配置代码提示

2.C3P0方式一（java程序）

    @Test
    public void testC3P01() throws Exception  {
        //1.创建一个数据源对象，可以理解为这个数据源对象就是那个连接池
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();

        //2.读取配置文件，获取url，user，password，driver
        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");

        //3.给数据源设置相关参数
        comboPooledDataSource.setJdbcUrl(url);
        comboPooledDataSource.setUser(user);
        comboPooledDataSource.setPassword(password);
        comboPooledDataSource.setDriverClass(driver);

        //4.设置初始化参数
        comboPooledDataSource.setInitialPoolSize(10); //初始化连接数
        comboPooledDataSource.setMaxPoolSize(50); //最大连接数

        //5.获取连接
        Connection connection = comboPooledDataSource.getConnection();
        System.out.println("连接OK");

        //6.关闭连接
        connection.close();
    }

3.C3P0方式二（配置文件）

1.环境配置

1.将c3p0-config.xml配置文件复制到src目录下

<c3p0-config>
 <!-- 数据源名称，可以随意-->
 <named-config name="hello">
     <!-- 驱动类 -->
     <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
     <!-- url-->
     <property name="jdbcUrl">jdbc:mysql://localhost:3306/hsp_db02</property>
     <!-- 用户名 -->
     <property name="user">root</property>
     <!-- 密码 -->
     <property name="password">root</property>
     <!-- 每次增长的连接数-->
     <property name="acquireIncrement">5</property>
     <!-- 初始的连接数 -->
     <property name="initialPoolSize">10</property>
     <!-- 最小连接数 -->
     <property name="minPoolSize">5</property>
     <!-- 最大连接数 -->
     <property name="maxPoolSize">50</property>

     <!-- 可连接的最多的命令对象数 -->
     <property name="maxStatements">5</property>

     <!-- 每个连接对象可连接的最多的命令对象数 -->
     <property name="maxStatementsPerConnection">2</property>
 </named-config>
</c3p0-config>

2.修改配置文件的参数

2.编写java代码

    @Test
    public void testC3P02() throws Exception {
        //1.创建与配置文件名称相同的数据源
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource("hello"); //注意：这里的hello是配置文件中的名字
        //2.获取连接
        Connection connection = comboPooledDataSource.getConnection();
        System.out.println("连接OK");
        //3.关闭连接
        connection.close();
    }

4.德鲁伊连接池

1.环境配置

1.导入jar包

2.将配置文件复制到src目录下，名字任意

#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/girls?rewriteBatchedStatements=true
#url=jdbc:mysql://localhost:3306/girls
username=root
password=root
#initial connection Size
initialSize=10
#min idle connecton size
minIdle=5
#max active connection size
maxActive=50
#max wait time (5000 mil seconds)
maxWait=5000

3.修改配置文件的参数

2.编写java代码

package datasource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

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

/**
 * @author 孙显圣
 * @version 1.0
 */
public class Druid_ {
    public static void main(String[] args) throws Exception {
        //1.读取配置文件
        Properties properties = new Properties();
        properties.load(new FileInputStream("src\\druid.properties"));
        //2.创建数据源对象（就是连接池），将配置文件传进去
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        //3.获取连接
        Connection connection = dataSource.getConnection();
        System.out.println("连接OK");
        //4.关闭连接
        connection.close();
    }
}

5.德鲁伊工具类

1.编写代码

package utils;

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 孙显圣
 * @version 1.0
 */
public class JDBCUtilsByDruid {
    //静态数据源引用（jdbc的接口）
    private static DataSource dataSource;

    //静态代码块，在类加载时为数据源引用赋值
    static {
        //1.读取配置文件
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        //2.使用配置文件，创建德鲁伊数据源对象
        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    //编写getConnection方法
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //把Connection对象放回连接池
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


}

2.测试使用

package datasource;

import org.junit.jupiter.api.Test;
import utils.JDBCUtilsByDruid;

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

/**
 * @author 孙显圣
 * @version 1.0
 */
public class DruidUtils_Use {
    @Test
    public void testSelect() {
        //建立连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //编写sql语句进行查询
        String sql = "select name, phone from actor where id = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //进行预处理
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 8);
            //执行查询
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String string = resultSet.getString("name");
                String string1 = resultSet.getString("phone");
                System.out.println(string + " " + string1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源
            JDBCUtilsByDruid.close(resultSet, preparedStatement, connection);
        }

    }

}

6.Apache——DBUtils

1.引出

2.基本介绍

3.Apache——DBUtils查询

1.添加依赖

2.编写java代码

1.Actor.java(封装每一行的bean)

package datasource;

import java.sql.Timestamp;


/**
 * @author 孙显圣
 * @version 1.0
 * 这是一个bean，用来封装actor表的每一行数据
 */
public class Actor {
        private Integer id; //注意要使用包装类
        private String name;
        private String sex;
        private Timestamp borndate; //mysql8只能用这个类型来接受datetime
        private String phone;

        public Actor() { //一定要给一个无参构造器[反射需要]
        }

        public Actor(Integer id, String name, String sex, Timestamp 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 Timestamp getBorndate() {
            return borndate;
        }

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

        public String getPhone() {
            return phone;
        }

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

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

2.查询多条记录

new BeanListHandler<>(Actor.class)

package datasource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.jupiter.api.Test;
import utils.JDBCUtilsByDruid;

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

/**
 * @author 孙显圣
 * @version 1.0
 */
public class DBUtils_USE {
    @Test
    public void testQueryMan() throws SQLException {
        //1.得到连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.创建QueryRunner
        QueryRunner queryRunner = new QueryRunner();

        //3.编写sql
        String sql = "select borndate from actor where id >= ?";

        //4.调用方法，返回ArrayList结果集，其中每一个元素都是表的一行，封装到了bean中
        List<Actor> query = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1); //1就是给里面的问号赋值

        //5.获取每一行的bean
        for (Actor actor : query) {
            System.out.println(actor);
        }
        //6.关闭连接，他会自动关闭resultset和preparedStatement
        JDBCUtilsByDruid.close(null, null, connection);
    }

}

3.查询单条记录

new BeanHandler<>(Actor.class)

    @Test
    public void testQuerySingle() {
        //1.获取连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.创建queryRunner
        QueryRunner queryRunner = new QueryRunner();
        //3.编写sql
        String sql = "select * from actor where id = ?";
        //4.调用查询方法
        Actor query = null;
        try {
            query = queryRunner.query(connection, sql, new BeanHandler<>(Actor.class), 8);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        System.out.println(query);
        //5.关闭资源
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

4.查询单行单列记录

new ScalarHandler()

    @Test
    public  void testScalar() {
        //1.获取连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.创建queryRunner
        QueryRunner queryRunner = new QueryRunner();
        //3.编写sql
        String sql = "select name from actor where id = ?"; //单行单列
        //4.查询
        try {
            Object query = queryRunner.query(connection, sql, new ScalarHandler(), 8);
            System.out.println(query);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        //5.关闭资源
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

4.DML操作

    @Test
    public void testDML() throws SQLException {
        //1.获取连接
        Connection connection = JDBCUtilsByDruid.getConnection();
        //2.创建queryRunner
        QueryRunner queryRunner = new QueryRunner();
        //3.编写sql
        //增加
        String sql1 = "insert into actor values(?, ?, ?, ?, ?)";
        //删除
        String sql2 = "delete from actor where id = ?";
        //修改
        String sql3 = "update actor set phone = ?";
        //4.执行sql
        int update = queryRunner.update(connection, sql1, null, "张三丰", "男", "2005-11-02", "51552");
        int update1 = queryRunner.update(connection, sql2, 11);
        int update2 = queryRunner.update(connection, sql3, 123456);
        //5.关闭资源
        try {
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

7.BasicDao

1.引出

2.BasicDao分析

3.代码实现

1.文件目录

2.BasicDao

package BasicDao_;

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 utils.JDBCUtilsByDruid;

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

/**
 * @author 孙显圣
 * @version 1.0
 * 开发BasicDAO , 是其他DAO的父类
 */
public class BasicDao<T> { //泛型指定具体类型

    private QueryRunner qr =  new QueryRunner();

    //开发通用的dml方法, 针对任意的表
    public int update(String sql, Object... parameters) {

        Connection connection = null;

        try {
            connection = JDBCUtilsByDruid.getConnection();
            int update = qr.update(connection, sql, parameters);
            return  update;
        } catch (SQLException e) {
           throw  new RuntimeException(e); //将编译异常->运行异常 ,抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }

    }

    //返回多个对象(即查询的结果是多行), 针对任意表

    /**
     *
     * @param sql sql 语句，可以有 ?
     * @param clazz 传入一个类的Class对象 比如 Actor.class
     * @param parameters 传入 ? 的具体的值，可以是多个
     * @return 根据Actor.class 返回对应的 ArrayList 集合
     */
    public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) {

        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);

        } catch (SQLException e) {
            throw  new RuntimeException(e); //将编译异常->运行异常 ,抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }

    }

    //查询单行结果 的通用方法
    public T querySingle(String sql, Class<T> clazz, Object... parameters) {

        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return  qr.query(connection, sql, new BeanHandler<T>(clazz), parameters);

        } catch (SQLException e) {
            throw  new RuntimeException(e); //将编译异常->运行异常 ,抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }

    //查询单行单列的方法,即返回单值的方法

    public Object queryScalar(String sql, Object... parameters) {

        Connection connection = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            return  qr.query(connection, sql, new ScalarHandler(), parameters);

        } catch (SQLException e) {
            throw  new RuntimeException(e); //将编译异常->运行异常 ,抛出
        } finally {
            JDBCUtilsByDruid.close(null, null, connection);
        }
    }

}

3.ActorDao

package BasicDao_.dao;

import BasicDao_.domain.Actor;

/**
 * @author 孙显圣
 * @version 1.0
 */
public class ActorDao extends BasicDao<Actor>{
    //拥有BasicDao所有的方法
    //根据业务需求可以写上特有的方法
}

4.Actor

package BasicDao_.domain;

import java.sql.Timestamp;


/**
 * @author 孙显圣
 * @version 1.0
 * 这是一个bean，用来封装actor表的每一行数据
 */
public class Actor {
        private Integer id;
        private String name;
        private String sex;
        private Timestamp borndate; //mysql8只能用这个类型来接受datetime
        private String phone;

        public Actor() { //一定要给一个无参构造器[反射需要]
        }

        public Actor(Integer id, String name, String sex, Timestamp 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 Timestamp getBorndate() {
            return borndate;
        }

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

        public String getPhone() {
            return phone;
        }

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

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

5.TestDao

package BasicDao_.test;

import BasicDao_.dao.ActorDao;
import BasicDao_.domain.Actor;

import java.util.List;

/**
 * @author 孙显圣
 * @version 1.0
 */
public class TestDao {
    public static void main(String[] args) {
        //测试一下ActorDao对actor表的操作
        ActorDao actorDao = new ActorDao();
        //1.查询
        List<Actor> actors = actorDao.queryMulti("select * from actor where id >?", Actor.class, 2);
        for (Actor actor : actors) {
            System.out.println(actor);
        }
        //2.查询单行记录
        Actor actor = actorDao.querySingle("select * from actor where id = ?", Actor.class, 4);
        System.out.println(actor);
        //3.查询单行单列记录
        Object o = actorDao.queryScalar("select name from actor where id = ?", 9);
        System.out.println(o);
        //4.增加一条记录
        int update = actorDao.update("insert into actor values(?,?,?,?,?)", null, "王五", "女", "2002-1-9", "5455555");
        System.out.println(update > 0 ? "成功" : "失败");
        //5.删除一条记录
        int update1 = actorDao.update("delete from actor where id > ?", 10);
        System.out.println(update1 > 0 ? "成功" : "失败");
        //6.修改一条记录
        int update2 = actorDao.update("update actor set name = ?", "女");
        System.out.println(update2 > 0 ? "成功" : "失败");

    }
}

6.JDBCUtilsByDruid

package BasicDao_.utils;

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 孙显圣
 * @version 1.0
 */
public class JDBCUtilsByDruid {
    //静态数据源引用（jdbc的接口）
    private static DataSource dataSource;

    //静态代码块，在类加载时为数据源引用赋值
    static {
        //1.读取配置文件
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        //2.使用配置文件，创建德鲁伊数据源对象
        try {
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    //编写getConnection方法
    public static Connection getConnection() {
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //把Connection对象放回连接池
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


}

7.druid.properties

#key=value
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/hsp_db02?rewriteBatchedStatements=true
#url=jdbc:mysql://localhost:3306/girls
username=root
password=root
#initial connection Size
initialSize=10
#min idle connecton size
minIdle=5
#max active connection size
maxActive=50
#max wait time (5000 mil seconds)
maxWait=5000