JDBC 数据事务
1. 事务处理
介绍事务:
事务:一组逻辑操作单元,使数据从一种状态变换到另一种状态。(AA给BB转账100,更CCDD没关系)
一致性:事务处理的时,数据的一致性可以保持,而当这个单元中的一部分操作失败,整个事务应全部视为错误,所有从起始点以后的操作应全部回退到开始状态。
事务回滚:回滚到上一次提交之后的状态。
以AA给BB 转账100为例:
上代码:
更新了update代码:
/**
* 考虑事务 --增删改
* 事务处理的原则:要么流畅的全部执行;要么在中途报错,所有单位回到原来的状态
* 数据一旦提交就不能回滚
*
* 哪些操作会导致数据的自动提交??
* >DDL操作都会自动提交
* >set autocommit = false 对DDL操作失效
* >DML默认情况下,一旦执行,自动提交
* >可以通过set autocommit = false 取消他的自动提交
* >默认在关闭连接时,会自动提交数据
*/
public int update(Connection connection, String sql, Object... args) throws SQLException {
//预编译sql语句,填充占位符
PreparedStatement ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
//执行
int n = ps.executeUpdate();
//3.关闭,(除了连接,其他资源都关闭)
JDBCUtils.closeResource(null, ps);
return n;
}
测试:
@Test //事物测试
public void test_updateAffairs() throws SQLException {
Connection connection = null;
try {
//开始事务
connection = JDBCUtils.getConnection();
//关闭自动提交
connection.setAutoCommit(false);
//进行数据库操作 AA向BB转账100
String sql1 = "update user_table set balance = balance - 100 where user = ?";
int aa = update(connection, sql1, "AA");
// //模拟网络一场
// System.out.println(10/0);
String sql2 = "update user_table set balance = balance + 100 where user = ?";
int bb = update(connection, sql2, "BB");
//若没有异常,提交事务
connection.commit();
} catch (Exception e) {
e.printStackTrace();
try {
//有异常,回滚
connection.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
try {
//回复自动提交
connection.setAutoCommit(true);
} catch (SQLException e) {
e.printStackTrace();
}
//关闭connection资源
JDBCUtils.closeResource(connection,null);//ps已经在update运行结束的时候被关闭了
}
}
效果:
模拟网络错误的时候,表格不变。
2. 数据库的并发问题和隔离级别
并发问题:同时运行多个事务的时候出现的问题
- 脏读: 对于两个事务 T1, T2,;T1 读取了已经被 T2 更新但还没有被提交的字段。之后, 若 T2 回滚,
T1读取的内容就是临时且无效的。 - 不可重复读: 对于两个事务T1, T2, T1 读取了一个字段, 然后 T2 更新了该字段。之后, T1再次读取同一个字段, 值就不同了。
- 幻读: 对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后 T2 在该表中插入了一些新的行。之后, 如 果 T1再次读取同一个表, 就会多出几行。
数据库提供的四个隔离级别
Oracle 支持的 2 种事务隔离级别:READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READCOMMITED 。
Mysql 支持 4 种事务隔离级别。Mysql 默认的事务隔离级别为:REPEATABLE READ。
相关的隔离操作:
1.查看当前的隔离级别:SELECT @@tx_isolation;
2.设置当前mysql的隔离级别:set transaction isolation level read committed;
3.设置数据库系统的全局隔离级别:
set global transaction isolation level read committed;
补充操作:
1.创建mysql用户:create user tom identified by 'abc123';
(tom,abc123是密码)
2.授权:
#授予通过网络方式登录的tom用户,对所有库所有表的全部权限,密码设为abc123
grant all privileges on *.* to tom@'%' identified by 'abc123';
#给tom用户使用本地命令行方式,授予atguigudb这个库下的所有表的插删改查的权限.
grant select,insert,delete,update on atguigudb.* to tom@localhost identified by 'abc123';`
JDBC DAO相关
ok这里重点理解。
1. DAO介绍
简介:全部看完了,再来看看简介感觉很舒服(我又要说了,就像是三九北风熬了一碗鲫鱼汤那般)
- DAO(BaseDAO):Data Access Object访问数据信息的类和接口,包括了对数据的CRUD(Create、Retrival、Update、 Delete),而不包含任何业务相关的信息
- 作用:为了实现功能的模块化,更有利于代码的维护和升级。
层次结构分析:
2. 针对customer表,演示DAO
上代码:
1.BaseDao类:(顶层通用的类,包含了update增删改方法、查询单条多条数据方法、特殊查询方法)
package com.JDBC_Advanced.after;
import com.JDBC_Advanced.JDBCUtils;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* 访问数据信息的类和接口,模块化升级
*/
public abstract class BaseDao {
//增删改,考虑事务
public int update(Connection connection, String sql, Object... args) {
PreparedStatement ps = null;
try {
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
int i = ps.executeUpdate();
return i;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.closeResource(null, ps);
} catch (SQLException e) {
e.printStackTrace();
}
}
return 0;
}
//查一条数据
public <T> T queryForOne(Connection connection, Class<T> clazz, String sql, Object... args) {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
if (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
Object value = rs.getObject(rsmd.getColumnLabel(i + 1));
//反射
Field declaredField = clazz.getDeclaredField(rsmd.getColumnLabel(i + 1));
declaredField.setAccessible(true);
declaredField.set(t, value);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.closeResource(null, ps, rs);
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
//查询多条数据
public <T> List<T> queryForMore(Connection connection, Class<T> clazz, String sql, Object... args) {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
ArrayList<T> list = new ArrayList<>();
while (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
String columnLabel = rsmd.getColumnLabel(i + 1);
Object value = rs.getObject(columnLabel);
//反射
Field declaredField = clazz.getDeclaredField(columnLabel);
declaredField.setAccessible(true);
declaredField.set(t, value);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.closeResource(null, ps, rs);
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
//查询特殊值?
public <E> E getValue(Connection connection, String sql, Object... args) {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
if (rs.next()) {
return (E) rs.getObject(1);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.closeResource(null, ps, rs);
} catch (SQLException e) {
e.printStackTrace();
}
}
return null;
}
}
2.Customer类:(针对的是表customers)
package com.JDBC_Advanced.after;
/**
* 针对customer表而创建的类
*/
import java.sql.Date;
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
public Customer() {
}
public Customer(int id, String name, String email, Date birth) {
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
}
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", birth=" + birth +
'}';
}
}
3.CustomerDao接口:(规范针对Customer表的操作)
package com.JDBC_Advanced.after;
/**
* 此接口用于规范 针对customer表的常用操作
*/
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;
public interface CustomerDao {
//插入一条记录
void insert(Connection connection, Customer customer) throws SQLException;
//删除一条记录--byID
void deleteById(Connection connection, int id);
//修改指定数据 --针对内存中的cust对象
void update(Connection connection, Customer customer);
//查询一条记录 ---根据id
Customer getCustomerById(Connection connection, int id);
//查询所有记录
List<Customer> getAllCustomers(Connection connection);
//返回数据表中的数据的条目数
Long getCount(Connection connection);
//返回最大生日
Date getMaxBirth(Connection connection);
}
4.CustomerDaoImpl:(接口实现类,对于接口里的方法的具体实现)
package com.JDBC_Advanced.after;
/**
* 对于接口里的方法的具体实现
*/
import java.sql.Connection;
import java.sql.Date;
import java.util.List;
public class CustomerDaoImpl extends BaseDao implements CustomerDao {//这里的泛型指明了是操作的Customer类,所以后续可以选择更改删除参数中的Customers.class
@Override
public void insert(Connection connection, Customer customer) {
String sql = "insert into customers(name,email,birth) values(?,?,?)";
update(connection, sql, customer.getName(), customer.getEmail(), customer.getBirth());
}
@Override
public void deleteById(Connection connection, int id) {
String sql = "delete from customers where id = ?";
update(connection, sql, id);
}
@Override
public void update(Connection connection, Customer customer) {
String sql = "update customers set name = ?, email = ?, birth = ? where id = ?";
update(connection, sql, customer.getName(), customer.getEmail(), customer.getBirth(), customer.getId());
}
@Override
public Customer getCustomerById(Connection connection, int id) {
String sql = "select id,name,email,birth from customers where id = ?";
Customer customer = queryForOne(connection, Customer.class, sql, id);
return customer;
}
@Override
public List<Customer> getAllCustomers(Connection connection) {
String sql = "select id,name,email,birth from customers";
List<Customer> list = queryForMore(connection, Customer.class, sql);
return list;
}
@Override
public Long getCount(Connection connection) {
String sql = "select count(*) from customers";
return getValue(connection, sql);
}
@Override
public Date getMaxBirth(Connection connection) {
String sql = "select max(birth) from customers";
return getValue(connection, sql);
}
}
5.CustomerDaoImplTest类:(测试类,用的是@Test)
中间有一个快捷键生成的测试类的,如下图
package com.JDBC_Advanced.after;
import com.JDBC_Advanced.JDBCUtils;
import org.junit.Test;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.List;
public class CustomerDaoImplTest {
private CustomerDaoImpl dao = new CustomerDaoImpl();
@Test
public void insert() {
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
Customer customer = new Customer(1, "文二牛", "wenerniu@11.com", new Date(2012 - 1 - 1));
dao.insert(connection, customer);
System.out.println("添加成功!");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.closeResource(connection, null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Test
public void deleteById() {
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
dao.deleteById(connection, 8);//删除8号
System.out.println("删除成功!");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.closeResource(connection, null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Test
public void update() {
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
Customer customer = new Customer(12, "文三牛", "wen三niu@11.com", new Date(2012 - 11 - 1));
dao.update(connection, customer);
System.out.println("修改成功!");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.closeResource(connection, null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Test
public void getCustomerById() {
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
Customer customer_query = dao.getCustomerById(connection, 16);
System.out.println("查询结果为----");
System.out.println(customer_query);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.closeResource(connection, null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Test
public void getAllCustomers() {
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
List<Customer> allCustomersList = dao.getAllCustomers(connection);
System.out.println("查询全部-----");
allCustomersList.forEach(System.out::println);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.closeResource(connection, null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Test
public void getCount() {
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
Long count = dao.getCount(connection);
System.out.println("计数结果------" + count);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.closeResource(connection, null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
@Test
public void getMaxBirth() {
Connection connection = null;
try {
connection = JDBCUtils.getConnection();
Date maxBirth = dao.getMaxBirth(connection);
System.out.println("年龄最小为(birth值最大)------" + maxBirth);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
JDBCUtils.closeResource(connection, null);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
效果:
添加成功!
删除成功
修改成功
查询16号朱茵
查询全部
总计12条数据
birth最大值
3. 优化Dao
不太好理解
优化思路:从原来的代码中有所重复(如下图),(既然是针对Customer了,没必要在此处写获取类)
优化方法:
上代码**(代码注释中详细解析)**
把如下代码加入到BaseDao类开头:
/**
*出现对象之前可以给子类赋值的位置有哪些?---???
* 获取当前对象父类的泛型
*/
{
//作用:当前BaseDAO的子类继承了父类中的泛型、、、靠杯啊真离谱
Type genericSuperclass = this.getClass().getGenericSuperclass();
ParameterizedType parameterizedType = (ParameterizedType) genericSuperclass;
Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();//获取了父类的泛型参数
clazz = (Class<T>) actualTypeArguments[0];//泛型的第一个参数
}
2.更改掉用到.class() 和相关的clazz
package com.JDBC_Advanced;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.Date;
import java.util.List;
public class CustomerDaoImpl extends BaseDao<Customer> implements CustomerDao {//这里的泛型指明了是操作的Customer类,所以后续可以选择更改删除参数中的Customers.class
/* 为什么不写在这里呢??
-------如果写在这里的话,每次针对不同的表格(比如说order表customers表book表),都需要在这里写,
所以把他提炼出来,放到最顶级的父类里(也就是BaseDao中):这就充分的体现了优化的效果
----原理:继承。每次子类实例化一个对象的时候,都先从父类里面怎么怎么样....他们说这是多态!
------因为你每次针对不同的表格 都要写不同的类(就是Customer类),然后要写这个类对应的接口(也就是CustomerDao接口,里面有具体的想要操作的方法都在里面添加)
然后,每次都要写这个接口的实现类(也就是这里的CustomerDaoImpl,),每次到这里都要写一遍以下“获取父类泛型”的代码,
所以,把他提炼出来,放到最顶层的BaseDao类中(这个类是包含了所有的通用的东西,比如update方法啊,查询啊,还有这里的父类泛型也是提取出的)
{
Type genericSuperclass = this.getClass().getGenericSuperclass();
ParameterizedType parameterizedType = (ParameterizedType) genericSuperclass;
Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();//获取了父类的泛型参数
clazz = actualTypeArguments[0]
}
*/
@Override
public void insert(Connection connection, Customer customer) {
String sql = "insert into customers(name,email,birth) values(?,?,?)";
update(connection, sql, customer.getName(), customer.getEmail(), customer.getBirth());
}
@Override
public void deleteById(Connection connection, int id) {
String sql = "delete from customers where id = ?";
update(connection, sql, id);
}
@Override
public void update(Connection connection, Customer customer) {
String sql = "update customers set name = ?, email = ?, birth = ? where id = ?";
update(connection, sql, customer.getName(), customer.getEmail(), customer.getBirth(), customer.getId());
}
@Override
public Customer getCustomerById(Connection connection, int id) {
String sql = "select id,name,email,birth from customers where id = ?";
Customer customer = queryForOne(connection, sql, id);
return customer;
}
@Override
public List<Customer> getAllCustomers(Connection connection) {
String sql = "select id,name,email,birth from customers";
List<Customer> list = queryForMore(connection, sql);
return list;
}
@Override
public Long getCount(Connection connection) {
String sql = "select count(*) from customers";
return getValue(connection, sql);
}
@Override
public Date getMaxBirth(Connection connection) {
String sql = "select max(birth) from customers";
return getValue(connection, sql);
}
}
数据库连接池相关技术
真正开发中用到的连接数据库的技术。
1. 引入
上述学习的传统连接方式的问题:
- 连接数据库没有得到很好的利用,频繁的连接占用资源,导致系统崩溃
- 对于每次数据库连接都要断开。否则数据泄露
- 不能控制被创建的对象数量,连接过多直接崩
数据库连接池技术:(地铁班车,定时发车,一次顶多运输那么多数据,统一断开连接,等车…)
2. Druid数据库连接池
主流技术,
上代码:
@Test
public void Druidconn() throws Exception {
InputStream is = DruidTest.class.getClassLoader().getResourceAsStream("Druid.properties");
Properties properties = new Properties();
properties.load(is);
DataSource source = DruidDataSourceFactory.createDataSource(properties);
Connection connection = source.getConnection();
System.out.println(connection);
}
其中的配置文件:(相关的参数查阅网络吧)
url=jdbc:mysql://localhost:3306/jdbc?rewriteBatchedStatements=true
username=root
password=123456
driverClass=com.mysql.cj.jdbc.Driver
initialSize=10
maxActive=20
maxWait=1000
filters=wall
效果:
Apache-DBUtils实现CRUD操作(第三方)
在maven导入dbutils
依赖:
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.6</version>
</dependency>
效果:
测试DBUtils
1. 添加数据:
上代码:
@Test
public void testInsert() throws Exception {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtils.getConnection_Druid();
String sql = "insert into customers(name,email,birth) values(?,?,?)";
int count = queryRunner.update(connection, sql, "文五牛", "555@com", "2015-5-5");
System.out.println("添加了" + count + "条记录。");
}
效果:
2. 查询一条数据
上代码:
//查询一条
@Test
public void testQueryInstance() throws Exception {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtils.getConnection_Druid();
String sql = "select id, name, email, birth from customers where id = ?";
//
BeanHandler<Customer> handler = new BeanHandler<>(Customer.class);//这里就是rs
Customer customer = queryRunner.query(connection, sql, handler, 1);
System.out.println(customer);
JDBCUtils.closeResource(connection, null);
}
效果:
3. 查询多条数据
上代码:
//查询多条记录
@Test
public void testQueryInstances() throws Exception {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtils.getConnection_Druid();
String sql = "select id, name, email, birth from customers";
//
BeanListHandler<Customer> listHandler = new BeanListHandler<>(Customer.class);
//BeanListHandler:是ResultSetHandler接口类的实现类,用于封装表中的多条记录
List<Customer> list = queryRunner.query(connection, sql, listHandler);
list.forEach(System.out::println);
}
效果:
4. 特殊值查询
上代码:
//特殊值查询
/**
* * 如何查询类似于最大的,最小的,平均的,总和,个数相关的数据,
* * 使用ScalarHandle
*/
@Test
public void testQueryforSpecial() throws Exception {
QueryRunner queryRunner = new QueryRunner();
Connection connection = JDBCUtils.getConnection_Druid();
String sql = "select count(*) from customers";
ScalarHandler<Object> handler = new ScalarHandler<>();
Object query = queryRunner.query(connection, sql, handler);
System.out.println("数据总数:" + query);
JDBCUtils.closeResource(connection, null);
}
效果:
5. 自定义实现类
上代码:
//自定义实现类
@Test
public void testByOwn() throws SQLException, IOException, ClassNotFoundException {
Connection connection = JDBCUtils.getConnection();
QueryRunner queryRunner = new QueryRunner();
String sql = "select * from customers";
ResultSetHandler<List<Customer>> handler = new ResultSetHandler<List<Customer>>() {
//匿名内部类
@Override
public List<Customer> handle(ResultSet resultSet) throws SQLException {
ArrayList<Customer> list = new ArrayList<>();
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String email = resultSet.getString("email");
Date birth = resultSet.getDate("birth");
Customer customer = new Customer(id, name, email, birth);
list.add(customer);
}
return list;
}
};
List<Customer> queryList = queryRunner.query(connection, sql, handler);
queryList.forEach(System.out::println);
}
效果:
6. 关闭资源
//DBUtils关闭资源
public void closeResource_DBUtils(Connection connection, Statement ps, ResultSet rs) {
DbUtils.closeQuietly(connection);
DbUtils.closeQuietly(ps);
DbUtils.closeQuietly(rs);
}