MySQL数据库（数据库连接池）

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_ {@Testpublic 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程序）

    @Testpublic void testC3P01() throws Exception  {//1.创建一个数据源对象，可以理解为这个数据源对象就是那个连接池ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();//2.读取配置文件，获取url，user，password，driverProperties 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代码

    @Testpublic 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 {@Testpublic 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只能用这个类型来接受datetimeprivate 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;}@Overridepublic 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 {@Testpublic void testQueryMan() throws SQLException {//1.得到连接Connection connection = JDBCUtilsByDruid.getConnection();//2.创建QueryRunnerQueryRunner queryRunner = new QueryRunner();//3.编写sqlString sql = "select borndate from actor where id >= ?";//4.调用方法，返回ArrayList结果集，其中每一个元素都是表的一行，封装到了bean中List<Actor> query = queryRunner.query(connection, sql, new BeanListHandler<>(Actor.class), 1); //1就是给里面的问号赋值//5.获取每一行的beanfor (Actor actor : query) {System.out.println(actor);}//6.关闭连接，他会自动关闭resultset和preparedStatementJDBCUtilsByDruid.close(null, null, connection);}}

3.查询单条记录

new BeanHandler<>(Actor.class)

    @Testpublic void testQuerySingle() {//1.获取连接Connection connection = JDBCUtilsByDruid.getConnection();//2.创建queryRunnerQueryRunner queryRunner = new QueryRunner();//3.编写sqlString 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()

    @Testpublic  void testScalar() {//1.获取连接Connection connection = JDBCUtilsByDruid.getConnection();//2.创建queryRunnerQueryRunner queryRunner = new QueryRunner();//3.编写sqlString 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操作

    @Testpublic void testDML() throws SQLException {//1.获取连接Connection connection = JDBCUtilsByDruid.getConnection();//2.创建queryRunnerQueryRunner 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.执行sqlint 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只能用这个类型来接受datetimeprivate 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;}@Overridepublic 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