数据库连接池
JDBC概述
数据的持久化
持久化:把数据保存到可掉电式存储设备中以供之后使用。大多数情况下,数据持久化意味着将内存中的数据保存到硬盘上加以“回收”,而持久化的实现过程大多通过各种关系数据库来完成。
JDBC体系结构
JDBC接口包括两个层次
- 面向应用的API:Java API,抽象接口,供应用程序开发人员使用
- 面向数据库的API:Java Driver API,供开发商开发数据库驱动程序
JDBC程序编写步骤
获取数据库连接
方式一:Driver接口实现类
在程序中不需要直接去访问实现了 Driver 接口的类,而是由驱动程序管理器类(java.sql.DriverManager)去调用这些Driver实现。
- Oracle的驱动:oracle.jdbc.driver.OracleDriver
- mySql的驱动: com.mysql.cj.jdbc.Driver
参数
- jdbc:mysql: 协议
- localhost ip地址
- 3306 默认mysql端口号
- test 库名
public void test1() throws SQLException {
// 获取Driver实现类对象
Driver driver = new com.mysql.cj.jdbc.Driver();
// url:http://localhost:8080/gmall/keyboard.jpg
// jdbc:mysql:协议
// localhost:ip地址
// 3306:默认mysql的端口号
// test:test数据库
String url = "jdbc:mysql://localhost:3306/test";
// 将用户名和密码封装在Properties中
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "123456");
Connection conn = driver.connect(url, info);
System.out.println(conn);
}
方式二:使用DriverManager替换Driver
public void test3() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
//1.获取Driver类实现对象
Class clazz=Class.forName("com.mysql.cj.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
//2.提供三个基本信息
String url = "jdbc:mysql://localhost:3306/test";
String user="root";
String password="123456";
//3.注册驱动
DriverManager.registerDriver(driver);
//4.获取连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
方式三:通过读取配置文件的方式,获取连接
配置文件信息
url=jdbc:mysql://localhost:3306/test
user=root
password=123456
driverClass=com.mysql.cj.jdbc.Driver
优点
- 实现了数据与代码的分离。实现了解耦
- 如果需要修改配置文件信息,可以避免程序重新打包
public void test5() throws IOException, ClassNotFoundException, SQLException {
//1.读取配置文件中的4个基本信息
InputStream is = TestConnection.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
String user = properties.getProperty("user");
String url = properties.getProperty("url");
String password = properties.getProperty("password");
String driverClass = properties.getProperty("driverClass");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
使用PreparedStatement实现CRUD操作
操作和访问数据库
数据库连接被用于向数据库服务器发送命令和 SQL 语句,并接受数据库服务器返回的结果。其实一个数据库连接就是一个Socket连接。
在 java.sql 包中有 3 个接口分别定义了对数据库的调用的不同方式:
- Statement:用于执行静态 SQL 语句并返回它所生成结果的对象。
- PrepatedStatement:SQL 语句被预编译并存储在此对象中,可以使用此对象多次高效地执行该语句。
- CallableStatement:用于执行 SQL 存储过程。
使用Statement操作数据表的弊端
- 需要拼写sql语句
- 存在SQL注入的问题
-SQL 注入是利用某些系统没有对用户输入的数据进行充分的检查,而在用户输入数据中注入非法的 SQL 语句段或命令
PreparedStatement的使用
通用的增删改操作
public void update(String sql,Object ...args) {
//1.获取数据库连接
Connection connection = null;
PreparedStatement ps = null;
try {
connection = JDBCUtils.getConnection();
//2.预编译sql语句
ps = connection.prepareStatement(sql);
//3.填充占位符
for(int i=0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//4.执行操作
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
//5.关闭资源
JDBCUtils.closeResource(connection,ps);
}
}
通用的查询customers表的操作
- 预编译sql语句:prepareStatement()
- 填充占位符:setObject()
- 执行,并返回结果集:executeQuery()
- 获取结果集的元数据(所有的字段名):getMetaData()
- 获取结果集的列数(获取有多少列):getColumnCount()
- 知道列数后,开始处理结果集
获取对应列的值:getObject
获取对应列的列名,与对象中的属性相对应:getColumnLabel
通过反射,将值赋给对象对应的属性
通过columnLabel,通过反射得到对应的属性 - 资源的关闭
public Customer queryForCustomer(String sql,Object ...args) throws Exception {
//1.获取数据库连接
Connection connection = JDBCUtils.getConnection();
//2.预编译sql语句
PreparedStatement ps = connection.prepareStatement(sql);
//3.填充占位符
for(int i=0;i<args.length;i++){
ps.setObject(i+1,args[0]);
}
//4.执行,并返回结果集
ResultSet rs = ps.executeQuery();
//5.获取结果集的元数据(所有的字段名)
ResultSetMetaData rsmd = ps.getMetaData();
//6.获取结果集的列数(获取有多少列)
int columnCount = rsmd.getColumnCount();
//7.知道列数后,开始处理结果集
if(rs.next()){
Customer customer = new Customer();
for(int i=0;i<columnCount;i++){
//处理第一个对象的结果
//获取对应列的值
Object columnValue = rs.getObject(i + 1);
//获取对应列的列名,与对象中的属性相对应
String columnLabel = rsmd.getColumnLabel(i + 1);
//通过反射,将值赋给对象对应的属性
//通过columnLabel,通过反射得到对应的属性
Field field = Customer.class.getDeclaredField(columnLabel);
//field为对应的属性:如name,email等cust对象的属性
field.setAccessible(true);
field.set(customer,columnValue);
}
return customer;
}
//8.资源的关闭
JDBCUtils.closeResource(connection,ps,rs);
return null;
}
数据库事务
数据库事务介绍
- 事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。
- 事务处理(事务操作):保证所有事务都作为一个工作单元来执行,即使出现了故障,都不能改变这种执行方式。当在一个事务中执行多个操作时,要么所有的事务都被提交(commit),那么这些修改就永久地保存下来;要么数据库管理系统将放弃所作的所有修改,整个事务回滚(rollback)到最初状态。
- 为确保数据库中数据的一致性,数据的操纵应当是离散的成组的逻辑单元:当它全部完成时,数据的一致性可以保持,而当这个单元中的一部分操作失败,整个事务应全部视为错误,所有从起始点以后的操作应全部回退到开始状态。
考虑事务的增删改操作
public void test1(){
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
//1.取消数据自动提交
conn.setAutoCommit(false);
String sql1 = "update user_table set balance = balance - 100 where user = ?";
update(sql1, "AA");
//模拟网络异常
System.out.println(10 / 0);
String sql2 = "update user_table set balance = balance + 100 where user = ?";
update(sql2, "BB");
System.out.println("转账成功");
//2.数据的提交
conn.commit();
} catch (Exception e) {
e.printStackTrace();
//3.回滚数据
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
//修改其为自动提交数据
//主要针对于针对数据库连接池的使用
try {
conn.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
JDBCUtils.closeResource(conn,null);
}
}
Java设置数据库隔离级别
getTransactionIsolation():获取当前连接的隔离级别
- transaaction_read_uncommitted 1
- transaction_read_committed 2
- transaction_repeatable_read 4
- transaction_serializable 8
setTransactionIsolation(Connection.隔离级别):设置数据库的隔离级别
数据库连接池
JDBC数据库连接池的必要性
在使用开发基于数据库的web程序时,传统的模式基本是按以下步骤
- 在主程序(如servlet、beans)中建立数据库连接
- 进行sql操作
- 断开数据库连接
这种模式开发,存在的问题:
- 普通的JDBC数据库连接使用 DriverManager 来获取,每次向数据库建立连接的时候都要将 Connection 加载到内存中,再验证用户名和密码(得花费0.05s~1s的时间)
- 对于每一次数据库连接,使用完后都得断开。否则,如果程序出现异常而未能关闭,将会导致数据库系统中的内存泄漏,最终将导致重启数据库。
- 这种开发不能控制被创建的连接对象数,系统资源会被毫无顾及的分配出去,如连接过多,也可能导致内存泄漏,服务器崩溃。
数据库连接池技术
-
数据库连接池的基本思想
-就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接,当需要建立数据库连接时,只需从“缓冲池”中取出一个,使用完毕之后再放回去。 -
数据库连接池负责分配、管理和释放数据库连接,它允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个
-
数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中,这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用,连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数,当应用程序向连接池请求的连接数超过最大连接数量时,这些请求将被加入到等待队列中。
多种开源的数据库连接池
JDBC 的数据库连接池使用 javax.sql.DataSource 来表示,DataSource 只是一个接口,该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现,也有一些开源组织提供实现
-
DBCP:是Apache提供的数据库连接池。tomcat
服务器自带dbcp数据库连接池。速度相对c3p0较快,但因自身存在BUG,Hibernate3已不再提供支持。 -
C3P0:是一个开源组织提供的一个数据库连接池,速度相对较慢,稳定性还可以。hibernate官方推荐使用
-
Proxool:是sourceforge下的一个开源项目数据库连接池,有监控连接池状态的功能,稳定性较c3p0差一点
-
BoneCP:是一个开源组织提供的数据库连接池,速度快
-
Druid:是阿里提供的数据库连接池,据说是集DBCP 、C3P0 、Proxool
优点于一身的数据库连接池,但是速度不确定是否有BoneCP快
DataSource 通常被称为数据源,它包含连接池和连接池管理两个部分,习惯上也经常把 DataSource 称为连接池
DataSource用来取代DriverManager来获取Connection,获取速度快,同时可以大幅度提高数据库访问速度。
特别注意:
- 数据源和数据库连接不同,数据源无需创建多个,它是产生数据库连接的工厂,因此整个应用只需要一个数据源即可。
- 当数据库访问结束后,程序还是像以前一样关闭数据库连接:conn.close();
但conn.close()并没有关闭数据库的物理连接,它仅仅把数据库连接释放,归还给了数据
C3P0数据库连接池: ComboPooledDataSource
xml配置文件
<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
<named-config name="MyC3P0">
<!-- 获取连接的4个基本信息 -->
<property name="user">root</property>
<property name="password">123456</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="driverClass">com.mysql.cj.jdbc.Driver</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>
public void test3() throws SQLException {
ComboPooledDataSource cpds = new ComboPooledDataSource("MyC3P0");//配置文件中的named-config
Connection conn = cpds.getConnection();
System.out.println(conn);
}
DBCP连接池:BasicDataSource
DBCP 是 Apache 软件基金组织下的开源连接池实现,该连接池依赖该组织下的另一个开源系统:Common-pool。如需使用该连接池实现,应在系统中增加如下两个 jar 文件:
- Commons-dbcp.jar:连接池的实现
- Commons-pool.jar:连接池实现的依赖库
Tomcat 的连接池正是采用该连接池来实现的。该数据库连接池既可以与应用服务器整合使用,也可由应用程序独立使用。
数据源和数据库连接不同,数据源无需创建多个,它是产生数据库连接的工厂,因此整个应用只需要一个数据源即可。
配置属性说明
- initialSize :连接池启动时创建的初始化连接数量(默认值为0)
- maxActive :连接池中可同时连接的最大的连接数(默认值为8,调整为20,高峰单机器在20并发左右,自己根据应用场景定)
- maxIdle:连接池中最大的空闲的连接数,超过的空闲连接将被释放,如果设置为负数表示不限制(默认为8个,maxIdle不能设置太小,因为假如在高负载的情况下,连接的打开时间比关闭的时间快,会引起连接池中idle的个数 上升超过maxIdle,而造成频繁的连接销毁和创建,类似于jvm参数中的Xmx设置)
- minIdle:连接池中最小的空闲的连接数,低于这个数量会被创建新的连接(默认为0,调整为5,该参数越接近maxIdle,性能越好,因为连接的创建和销毁,都是需要消耗资源的;但是不能太大,因为在机器很空闲的时候,也会创建低于minidle个数的连接,类似于jvm参数中的Xmn设置)
- maxWait :最大等待时间,当没有可用连接时,连接池等待连接释放的最大时间,超过该时间限制会抛出异常,如果设置-1表示无限等待(默认为无限,调整为60000ms,避免因线程池不够用,而导致请求被无限制挂起)
- poolPreparedStatements:开启池的prepared(默认是false,未调整,经过测试,开启后的性能没有关闭的好。)
- maxOpenPreparedStatements:开启池的prepared 后的同时最大连接数(默认无限制,同上,未配置)
- minEvictableIdleTimeMillis :连接池中连接,在时间段内一直空闲, 被逐出连接池的时间
- removeAbandonedTimeout :超过时间限制,回收没有用(废弃)的连接(默认为 300秒,调整为180)
- removeAbandoned :超过removeAbandonedTimeout时间后,是否进 行没用连接(废弃)的回收(默认为false,调整为true)
Druid(德鲁伊)数据库连接池:DuridDataSource
Druid 相对于其他数据库连接池的优点:
- 强大的监控特性,通过Druid提供的监控功能,可以清楚知道连接池和SQL的工作情况。
监控SQL的执行时间、ResultSet持有时间、返回行数、更新行数、错误次数、错误堆栈信息;
SQL执行的耗时区间分布。什么是耗时区间分布呢?比如说,某个SQL执行了1000次,其中0-1毫秒区间50次,1-10毫秒800次,10-100毫秒100次,100-1000毫秒30次,1-10秒15次,10秒以上5次。通过耗时区间分布,能够非常清楚知道SQL的执行耗时情况
监控连接池的物理连接创建和销毁次数、逻辑连接的申请和关闭次数、非空等待次数、PSCache命中率等。 - 其次,方便扩展。Druid提供了Filter-Chain模式的扩展API,可以自己编写Filter拦截JDBC中的任何方法,可以在上面做任何事情,比如说性能监控、SQL审计、用户名密码加密、日志等等。
- Druid集合了开源和商业数据库连接池的优秀特性,并结合阿里巴巴大规模苛刻生产环境的使用经验进行优化
相关配置
<!-- 配置Druid数据源 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<!-- 基本属性 url、user、password -->
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="5" />
<property name="minIdle" value="5" />
<property name="maxActive" value="100" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="60000" />
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="validationQuery" value="SELECT 'x'" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<!-- 打开PSCache,并且指定每个连接上PSCache的大小 -->
<property name="poolPreparedStatements" value="false" />
<property name="maxPoolPreparedStatementPerConnectionSize" value="20" />
<!-- 配置监控统计拦截的filters -->
<property name="filters" value="stat,wall" />
</bean>
要想使用druid的数据监控功能需要在web.xml里面配置servlet
<!-- 配置 Druid 监控信息显示页面 -->
<servlet>
<servlet-name>DruidStatView</servlet-name>
<servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class>
<init-param>
<!-- 允许清空统计数据 -->
<param-name>resetEnable</param-name>
<param-value>true</param-value>
</init-param>
<init-param>
<!-- 用户名 -->
<param-name>loginUsername</param-name>
<param-value>druid</param-value>
</init-param>
<init-param>
<!-- 密码 -->
<param-name>loginPassword</param-name>
<param-value>druid</param-value>
</init-param>
</servlet>
<servlet-mapping>
<servlet-name>DruidStatView</servlet-name>
<url-pattern>/druid/*</url-pattern>
</servlet-mapping>
对于资源的拦截配置:(可以在web.xml,也可以在Spring里面配置)
<!-- 用于采集web-jdbc关联监控的数据。 -->
<filter>
<filter-name>DruidWebStatFilter</filter-name>
<filter-class>com.alibaba.druid.support.http.WebStatFilter</filter-class>
<init-param>
<param-name>exclusions</param-name>
<param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*</param-value>
</init-param>
<init-param>
<param-name>sessionStatMaxCount</param-name>
<param-value>1000</param-value>
</init-param>
<init-param>
<param-name>sessionStatEnable</param-name>
<param-value>false</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>DruidWebStatFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
druid是可以配置输出日志的。这样,可以在控制台里面,看到我们程序里面编写的sql语句,还可以看到可执行的sql。
只需要在spring的配置文件里面配置:
<bean id="log-filter" class="com.alibaba.druid.filter.logging.Log4jFilter">
<!--日志打印可执行的sql -->
<property name="statementExecutableSqlLogEnable" value="true" />
<property name="dataSourceLogEnabled" value="true" />
<property name="connectionLogEnabled" value="true" />
<property name="statementLogEnabled" value="true" />
<property name="resultSetLogEnabled" value="true" />
</bean>
<!-- 配置Druid数据源 -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
<!-- 基本属性 url、user、password -->
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<!-- 配置初始化大小、最小、最大 -->
<property name="initialSize" value="5" />
<property name="minIdle" value="5" />
<property name="maxActive" value="100" />
<!-- 配置获取连接等待超时的时间 -->
<property name="maxWait" value="60000" />
<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
<property name="timeBetweenEvictionRunsMillis" value="60000" />
<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
<property name="minEvictableIdleTimeMillis" value="300000" />
<property name="validationQuery" value="SELECT 'x'" />
<property name="testWhileIdle" value="true" />
<property name="testOnBorrow" value="false" />
<property name="testOnReturn" value="false" />
<!-- 打开PSCache,并且指定每个连接上PSCache的大小 -->
<property name="poolPreparedStatements" value="false" />
<property name="maxPoolPreparedStatementPerConnectionSize" value="20" />
<!-- 配置监控统计拦截的filters -->
<property name="filters" value="stat,wall" />
<property name="proxyFilters">
<list>
<ref bean="log-filter"/>
</list>
</property>
</bean>
HikariCP
优点
- 优化字节码(JVM内联),减少生成的机器码
- 精简代码, 使用Javassist生成委托。
- 无锁容器。FastStatementList、ConcurrentBag
- 等等针对BoneCP的优化。
常用配置
1、maximum-pool-size:
池中最大连接数(包括空闲和正在使用的连接);
1)默认值是10,
2)<=0会认定为10,>0 and <1 会设置为mininum-idel;
策略:一般预估应用的最大连接数,后期根据监测得到一个最大值的一个平均值。
2、minimum-idle:
池中最小空闲连接数量;
1)默认值10;
2)小于池中最大连接数;
3)小于0或大于maximum-pool-size,都会重置为maximum-pool-size;
策略:一般根据系统大部分情况下的数据库连接情况取一个平均值。Hikari会尽可能、尽快地将空闲连接数维持在这个数量上。 如果为了获得最佳性能和对峰值需求的响应能力,我们也不妨让他和最大连接数保持一致,使得HikariCP成为一个固定大小的数据库连接池。
3、pool-name:连接池的名字。一般会出现在日志和JMX控制台中。默认值:auto-genenrated。建议取一个合适的名字,便于监控。
4、auto-commit:是否自动提交池中返回的连接。默认值为true。
5、idle-timeout:
连接空闲时间,
1)仅在minimum-idle小于maximum-poop-size的时候才会起作用;
2)默认值10分钟;
3)max-lifetime>0 and >=max-lifetime,会被重置为0;
4)不等于0且小于10秒,会被重置为10秒;
5)空闲连接数大于最大连接数且空闲时间超过该值,才会被释放。
策略:根据应用实际情况做调整,对于一些间歇性流量达到峰值的应用,一般需要考虑设置的比间歇时间更大,防止创建数据库连接拖慢了应用速度。
6、max-lifetime:
连接池中连接的最大生命周期; 感觉针对活跃连接,
1)不等于0且小于30秒,会被重置为30分钟;
2)默认值30分钟;
策略:一般将这个值设置的比数据库的“闲置超时时间”小几秒,以便这些连接断开后,HikariCP能迅速的创建新一轮的连接。
7、connection-timeout:
连接超时时间。默认值为30s,可以接收的最小超时时间为250ms,<250会被设置为30s。但是连接池请求也可以自定义超时时间(com.zaxxer.hikari.pool.HikariPool#getConnection(long))。
8、type:数据连接池的类型,指定hikaricp,com.zaxxer.hikari.HikariDataSource
9、连接测试查询:spring.datasource.hikari.connection-test-query=SELECT 1