AI专业者开发社区 1 号成员
  •  0 回帖  •  1 浏览  •  5 个月前
   

2022-08-20 第八组 卢睿 学习心得

目录

数据库连接池

connection 是一种稀有资源，一个连接建立就创造了一个资源

jdbc 使用数据库连接池的必要性

在使用基于 web 程序的数据库连接

  1. 在主程序中建立连接
  2. 执行 SQL
  3. 断开连接

所有的 jdbc 连接通过 DriverManager.getConnection
用完的连接不要被垃圾回收，能够重复使用

池化思想

每次去初始化一个连接池，连接池中会有很多个连接等待被使用
使用完连接之后，不需要关闭连接，只需要把连接还回到连接池
还回到连接池的操作不需要我们手动控制

设置一些属性，最大等待时间

比较常见的数据库连接池

  1. C3P0：2 代数据库连接池，太老了
  2. DBCP：2 代数据库连接池，太老了
  3. Druid（德鲁伊）数据库连接池，最好用的连接池
    整合了 C3P0 和 DBCP 各自的优点
    加入了日志监控，可以监控 SQL 语句的执行情况
  4. Hikari（光），目前最快的连接池，spring boot 默认的连接池

必须有对应的属性文件
.properties
约定 > 配置 > 编码

德鲁伊数据库连接池

import org.junit.Test;

import java.io.IOException;
import java.util.Properties;


public class Ch01 {


<span class="hljs-meta">@Test</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">test01</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> IOException, SQLException {
    <span class="hljs-type">Properties</span> <span class="hljs-variable">properties</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">Properties</span>();
    properties.load(Ch01.class.getClassLoader().getResourceAsStream(<span class="hljs-string">"druid.properties"</span>));

    <span class="hljs-type">DruidDataSource</span> <span class="hljs-variable">druidDataSource</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">DruidDataSource</span>();
    druidDataSource.configFromPropety(properties);

    System.out.println(druidDataSource.getConnection());
    System.out.println(druidDataSource.getCreateCount());
}



}

配置文件

druid.url=jdbc:mysql://127.0.0.1:3306/jsoft?useUnicode=true&characterEncoding=utf8
druid.username=root
druid.password=123456
druid.driverName=com.mysql.jdbc.Driver

druid.initialSize=10
druid.maxActive=20
druid.maxWait=20

hikari 数据库连接池


import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.junit.Test;

import java.io.IOException;
import java.sql.SQLException;
import java.util.Properties;


public class Ch02 {


<span class="hljs-meta">@Test</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">test01</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> IOException, SQLException {
    <span class="hljs-type">Properties</span> <span class="hljs-variable">properties</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">Properties</span>();
    properties.load(Ch01.class.getClassLoader().getResourceAsStream(<span class="hljs-string">"hikari.properties"</span>));

    <span class="hljs-type">HikariConfig</span> <span class="hljs-variable">hikariConfig</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">HikariConfig</span>(properties);
    <span class="hljs-type">HikariDataSource</span> <span class="hljs-variable">hikariDataSource</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">HikariDataSource</span>(hikariConfig);

    System.out.println(hikariDataSource.getConnection());
}



}

配置文件

jdbcUrl=jdbc:mysql://127.0.0.1:3306/jsoft?useUnicode=true&characterEncoding=utf8
username=root
password=123456
driverClassName=com.mysql.jdbc.Driver

获取连接

import com.jsoft.util.BaseDao;
import org.junit.Test;

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


public class Ch03 {


<span class="hljs-meta">@Test</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">test01</span><span class="hljs-params">()</span> {
    <span class="hljs-keyword">try</span> {
        <span class="hljs-type">Connection</span> <span class="hljs-variable">connection</span> <span class="hljs-operator">=</span> BaseDao.DATA_SOURCE.getConnection();

    } <span class="hljs-keyword">catch</span> (SQLException e) {
        <span class="hljs-keyword">throw</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">RuntimeException</span>(e);
    }
}



}

DBUtils

需要引入一个 jar 包


import com.jsoft.morning.demo2.Teacher;
import com.jsoft.util.BaseDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

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


public class Ch01 {


<span class="hljs-meta">@Test</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">test03</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> SQLException {

    <span class="hljs-type">QueryRunner</span> <span class="hljs-variable">runner</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">QueryRunner</span>(BaseDao.DATA_SOURCE);
    <span class="hljs-type">int</span> <span class="hljs-variable">i</span> <span class="hljs-operator">=</span> runner.update(<span class="hljs-string">"update teacher set name = ? where id = ?"</span>, <span class="hljs-string">"mmm"</span>, <span class="hljs-number">6</span>);
    System.out.println(i);

}

<span class="hljs-comment">/**
 * 查询一个记录
 */</span>
<span class="hljs-meta">@Test</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">test02</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> SQLException {
    <span class="hljs-type">QueryRunner</span> <span class="hljs-variable">runner</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">QueryRunner</span>(BaseDao.DATA_SOURCE);
    <span class="hljs-type">Teacher</span> <span class="hljs-variable">teacher</span> <span class="hljs-operator">=</span> runner.query(<span class="hljs-string">"select * from teacher where id = ?"</span>, <span class="hljs-keyword">new</span> <span class="hljs-title class_">BeanHandler</span>&lt;&gt;(Teacher.class), <span class="hljs-number">1</span>);
    System.out.println(teacher);
}

<span class="hljs-comment">/**
 * 查询多个记录
 * <span class="hljs-doctag">@throws</span> SQLException
 */</span>
<span class="hljs-meta">@Test</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">test01</span><span class="hljs-params">()</span> <span class="hljs-keyword">throws</span> SQLException {
    <span class="hljs-comment">// 要使用DBUtils使用的是一个类</span>
    <span class="hljs-comment">// 传入的是一个数据源DataSource，不是一个Connection</span>
    <span class="hljs-type">QueryRunner</span> <span class="hljs-variable">runner</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">QueryRunner</span>(BaseDao.DATA_SOURCE);
    <span class="hljs-comment">// 查询多个记录</span>
    List&lt;Teacher&gt; teachers = runner.query(<span class="hljs-string">"select * from teacher"</span>, <span class="hljs-keyword">new</span> <span class="hljs-title class_">BeanListHandler</span>&lt;&gt;(Teacher.class));
    System.out.println(teachers);
}



}