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

  • 开发
  • 22

文章目录

1.批处理应用

1.基本介绍

image-20240118195806605

image-20240118203024400

2.批处理演示

image-20240118203433909

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

image-20240118204517836

?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.批处理源码分析

image-20240118205608298

image-20240118205232540

2.数据库连接池

1.传统连接弊端分析

image-20240119091642286

image-20240119091627707

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

image-20240119091910403

2.数据库连接池示意图

image-20240119092246324

3.数据库连接池种类

image-20240119092345612

3.C3P0连接池

1.环境配置
1.导入jar包

image-20240119094059495

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

image-20240119094146794

3.配置代码提示

image-20240119094217187

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.修改配置文件的参数

image-20240119100430525

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包

image-20240119101351299

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

image-20240119101549362

#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.修改配置文件的参数

image-20240119102116306

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.引出

image-20240119133851789

2.基本介绍

image-20240119134845277

3.Apache——DBUtils查询

image-20240119135310282

1.添加依赖

image-20240119135911819

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.引出

image-20240119162157480

image-20240119162211983

2.BasicDao分析

image-20240119162441113

3.代码实现
1.文件目录

image-20240119173803106

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
阅读全部

相关推荐

  2. SpringBoot连接mysql数据库相关配置（druid连接池）

    2024-04-03 17:38:06       45 阅读

  5. JDBC数据库连接池

    2024-04-03 17:38:06       47 阅读

  6. C++数据库连接池

    2024-04-03 17:38:06       41 阅读

  7. 【JDBC】数据库连接池

    2024-04-03 17:38:06       19 阅读

  8. 数据库连接池

    2024-04-03 17:38:06       4 阅读

  9. 【数据库连接池】01：连接池初始化

    2024-04-03 17:38:06       43 阅读

最近更新

  3. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-04-03 17:38:06       5 阅读

  4. Could not load dynamic library ‘cudart64_100.dll‘

    2024-04-03 17:38:06       5 阅读

  9. 在Django里面运行非项目文件

    2024-04-03 17:38:06       4 阅读

  19. Python语言-面向对象

    2024-04-03 17:38:06       7 阅读

  20. 如何分清楚常见的 Git 分支管理策略Git Flow、GitHub Flow 和 GitLab Flow

    2024-04-03 17:38:06       6 阅读

热门阅读

  1. WebKit结构简介

    2024-04-03 17:38:06       23 阅读

  2. SSD学习＿hdparm中的sanitize和security erase的区别

    2024-04-03 17:38:06       24 阅读

  3. RL — 强化学习的若干技巧

    2024-04-03 17:38:06       20 阅读

  5. 掌握ChatGPT技巧：打造引人注目的学术论文写作风格

    2024-04-03 17:38:06       25 阅读

  7. Flutter 开源组件库

    2024-04-03 17:38:06       18 阅读

  10. Pandas处理缺省数据

    2024-04-03 17:38:06       18 阅读

  24. 什么才有filter方法数组还是对象

    2024-04-03 17:38:06       25 阅读

  26. 仅使用PyTorch就可以完成联邦学习

    2024-04-03 17:38:06       21 阅读

  28. Caffeine本地缓存

    2024-04-03 17:38:06       17 阅读

  29. C#开发中获取XML节点值，XML转对象案例

    2024-04-03 17:38:06       23 阅读

  30. 安卓Glide加载失败时点击按钮重新加载图片

    2024-04-03 17:38:06       17 阅读