MySQL数据库—进阶
1. 外键
MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。
外键是用来实现参照完整性的，不同的外键约束方式可以使两张表紧密的结合起来，特别是修改或删除的级联操作将使日常维护工作更加轻松，外键主要保证数据的完整和一致性，外键默认有如下几种级别，默认是RESTRICT
- CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。
- SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。
- NO ACTION: InnoDB拒绝删除或者更新父表。
- RESTRICT: 拒绝删除或者更新父表。指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。
外键使用场景
外键约束使用最多的两种情况：
1）父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败；
2）父表更新时子表也更新，父表删除时子表匹配的项也删除。
前一种情况，在外键定义中，我们使用ON UPDATE CASCADE ON DELETE RESTRICT；
后一种情况，可以使用ON UPDATE CASCADE ON DELETE CASCADE。
-- 创建主表 t_classdrop table if exists t_class;create table if not exists t_class(cid int(8) not null auto_increment,cname char(10),office char(3),constraint class_id_pk primary key(cid));insert into t_class(cname,office) value('理科班','501');insert into t_class(cname,office) value('文科班','502');insert into t_class(cname,office) value('酱油班','504');-- 更新主表数据update t_class set id=5 where id=3;-- 创建从表 t_stu 【包含了外键】drop table if exists t_stu;create table if not exists t_stu(id int(8) not null auto_increment,name varchar(20),gender enum('M','F'),birthday date,class_id int(8),constraint stu_id_pk primary key(id),constraint stu_class_id_fk foreign key(class_id) references t_class(cid) on update cascade on delete set null);-- 插入数据insert into t_stu(name,gender,birthday,class_id) value('tom','M','1990-09-08',null);insert into t_stu(name,gender,birthday,class_id) value('jack','M','1990-09-08',1);insert into t_stu(name,gender,birthday,class_id)values('lucy','M','1990-09-08',2),('kevin','M','1990-09-08',3),('jerry','F','1990-09-08',2),('lucy','M','1990-09-08',1),('tony','M','1990-09-08',3),('admin','F','1990-09-08',1);-- 从表是不能查询主表中不存在的数据-- 优点 ： 保证从表和从表数据的强一致性-- 缺点 ： 后期的维护性较复杂，添加数据要有顺序-- 更新从表数据update t_stu set class_id=null where id=9;-- 互联网95%的企业都是禁止使用外键的-- 外键自带索引的，所以在一定程度上既保证了数据的安全性又可以提高查询效率-- 互联网为什么？ 互联网的业务非常负载，表之间的关系错综复杂-- 在互联网中复杂的业务会导致使用外键后维护起来就是一场灾难--- 通过SQL逻辑（连接查询）来逻辑绑定表与表之间的关系的-- inner join on left join on
2. 函数
MySQL提供了众多功能强大、方便易用的函数，使用这些函数，可以极大地提高用户对于数据库的管理效率，从而更加灵活地满足不同用户的需求。本文将MySQL的函数分类并汇总，以便以后用到的时候可以随时查看。
我们在前面的课程中学习过了MySQL内置的函数，下面我们看一下如何自定义一个函数
-- 函数定义语法
DROP function IF EXISTS 函数名;
delimiter //
create function 函数名称(参数列表) returns 返回类型
begin
sql语句逻辑;
end
//
delimiter ;
说明：delimiter用于设置分割符，默认为分号
在“sql语句”部分编写的语句需要以分号结尾，此时回车会直接执行，所以要创建存储过程前需要指定其它符号作为分割符，此处使用//，也可以使用其它字符，也可以直接用下面的方式实现
DROP FUNCTION IF EXISTS 函数名;
CREATE FUNCTION 函数名称(参数列表) returns 返回类型
begin
逻辑
end
- 声明临时变量，只能在 begin … end 语法体中
declare 变量名 类型 default 默认值;
例：declare x varchar(100);
- 设置变量值
set 变量名=值;
例：set x='abc';
使用变量：将变量写到表达式中，即可调用变量的值
- 判断结构
if 条件1 then
语句1;
elseif 条件2 then
语句2;
else
语句;
end if;
- 循环结构
while 条件 do
语句;
end while;
退出循环：leave，相当于break
退出本次循环：iterate，相当于continue
准备工作
创建函数/存储过程时
出错信息：
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
原因：
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句
其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
解决方法：
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
这样添加了参数以后，如果mysqld重启，那个参数又会消失，因此记得在my.ini配置文件中添加：
log_bin_trust_function_creators=1
案例分析 ：
-- 1. 模拟一个TRIM的功能函数
-- 控制台写法
DROP FUNCTION IF EXISTS my_trim;
delimiter //
create function my_trim(str varchar(100)) returns varchar(100)
begin
declare x varchar(100);
set x = trim(str);
return x;
end
//
delimiter ;
-- 可视化工具用法
CREATE FUNCTION `my_trim`(str varchar(100)) RETURNS varchar(100)
begin
declare x varchar(100);
set x = trim(str);
return x;
end
-- 2. 定义函数odd，输出1-100间的偶数
-- 控制台写法
delimiter //
create function odd() returns varchar(300)
BEGIN
declare i INT(3) default 1;
declare x VARCHAR(300) default '';
while i <= 100 do
if i % 2 = 0 then
set x = CONCAT(x,' ',i);
end if;
set i = i+1;
end while;
return x;
END
//
delimiter ;
-- 可视化工具用法
CREATE FUNCTION `odd`() RETURNS varchar(300)
BEGIN
declare i INT(3) default 1;
declare x VARCHAR(300) default '';
while i <= 100 do
if i % 2 = 0 then
set x = CONCAT(x,' ',i);
end if;
set i = i+1;
end while;
return x;
END
3. 视图
传统对于复杂的查询，在多个地方被使用，如果需求发生了改变，需要更改sql语句，则需要在多个地方进行修改，维护起来非常麻烦
视图的概念
视图(View)是一个从单张或多张基础数据表或其他视图中构建出来的虚拟表。同基础表一样,视图中也包含了一系列带有名称的列和行数据。但是数据库中只是存放视图的定义,也就是动态检索数据的查询语句,而并不存放视图中的数据,这些数据依旧存放于构建视图的基础表中,只有当用户使用视图时才去数据库请求相对应的数据,即视图中的数据是在引用视图时动态生成的。因此视图中的数据依赖于构建视图的基础表,如果基本表中的数据发生了变化,视图中相应的数据也会跟着改变。
定义视图
视图本质就是对基表操作的封装 给用户提供了一种 “虚表”(虚拟的表但是却可以提供普通数据表的行为)机制.视图就是一条SELECT语句执行后返回的结果集
视图名建议以v_开头
create view 视图名称 as select 语句;
-- 推荐使用创建视图的语法
create view 视图名称 as select 语句 with check option;
视图作用：
方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；更加安全，数据库授权命令不能限定到特定行和特定列，但是通过合理创建视图，可以把权限限定到行列级别；
应用场景
- 安全原因， 视图可以隐藏一些数据。 如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和金额数；
- 可使复杂的查询易于理解和使用。一般情况下视图主要功能就是提供查询。在很多情况下视图和基表是一对多的关系，如果想通过视图同时修改多张基表的数据就会报错。
视图的优点
- 提高了重用性，就像一个函数
- 对数据库重构，却不影响程序的运行
- 提高了安全性能。可以对不同的用户**，设定不同的视图
- 让数据更加清晰，想要什么样的数据，就创建什么样的视图
-- 视图的定义语法
CREATE VIRW 视图名称 AS SELECT 语句;
-- 曾经的查询方式 ： 直接使用select查询原表数据
SELECT * FROM t_user;
-- 假设这个表是一个社保或医疗系统的数据表（导致所有IDE敏感数据全部暴露）
-- 解决方案 ： 视图 【虚拟表】
DROP VIEW IF EXISTS v_user;
CREATE VIEW v_user
AS
SELECT id,uname,gender FROM t_user;
-- 使用视图完成数据的查询
SELECT * FROM v_user;
-- 视图能否操作添加，删除和修改？
insert into v_user(uname,gender) value('张三','男');
update v_user set uname='李四' where id=5;
delete from v_user where id=5;
-- 视图的问题分析
CREATE VIEW v_emp2 AS
SELECT * FROM t_emp WHERE dept_id=2;
insert into v_emp2(tno,name,dept_id) value('WNSH1000','纣王',2);
insert into v_emp2(tno,name,dept_id) value('WNSH1001','妲己',3);
-- 修改视图的结构（让视图自己可以检查数据是否安全）
CREATE OR REPLACE VIEW v_emp2
AS
SELECT * FROM t_emp WHERE dept_id=2
WITH CHECK OPTION; -- 强制检查数据的安全性和合理性
> 1369 - CHECK OPTION failed 'tc38.v_emp2'
> 时间: 0.001s
-- 视图可以简化对数据库多表的查询操作
CREATE VIEW v_emp_dept AS
SELECT e.*,d.dname,d.location,d.area
FROM t_emp e INNER JOIN t_dept d
ON e.dept_id = d.id
WITH CHECK OPTION;
-- 若频繁的查询两表操作，就可以使用视图来查询
SELECT * FROM v_emp_dept;
4. 索引
4.1 索引的概念
百度搜索关键字？户籍管理找人？
一般的应用系统对比数据库的读写比例在10:1左右，而且插入操作和更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重，当数据库中数据量很大时，查找数据会变得很慢。
为什么要使用索引？
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
索引（Index）是帮助MySQL高效获取数据的数据结构。
MySQL中的索引使用的数据结构是B+tree
4.2 索引的原理
4.3 索引的分类
4.4 索引使用
-- 1. 查询表的索引
show index from 表名;
-- 2. 创建索引
CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));
ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
ALTER TABLE table_name ADD INDEX index_name (column_list) ;
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
-- 3. 删除索引
DROP INDEX [indexName] ON mytable;
alter table table_name drop index index_name ;
索引的缺点
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要更新索引数据，建立索引会占用磁盘空间,综上应该根据实际情况创建合适的索引。
4.5 执行计划
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
语法 ：Explain + SQL语句
-- 参数 id ： select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。
select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况
id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在
-- 参数 select type
-- 查询的类型，主要是用于区别：普通查询、联合查询、子查询等的复杂查询
SIMPLE 简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为
SUBQUERY 在SELECT或WHERE列表中包含了子查询
DERIVED 在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询, 把结果放在临时表里。
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT 从UNION表获取结果的SELECT
-- 参数table ： 显示这一行的数据是关于哪张表的
-- 参数type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
需要记忆的： system>const>eq_ref>ref>range>index>ALL
只要是上百万条数据的表，出现ALL就是全表扫描，需要进行优化。一般来说，得保证查询至少达到range级别，最好能达到ref。
（1）system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现。
（2）const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MYSQL就能将该查询转换为一个常量。此查询，表中只查询到了一个数据，MYSQL就能将该查询转换为一个常量，所有type类型为const。
（3）eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
（4）ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
（5）range：只检索给定范围的行，使用一个索引赖选择行，key列显示使用了哪个索引，一般就是在where语句中出现between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描好，因为它只需要开始于索引的某一点，结束语于另一点。，不用扫描全部索引。
（6）index：（Full Index Scan），Index和All区别为index类型只遍历索引树，这通常比ALL块，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从所以中读取的，而all是从磁盘中读的）
（7）all：（Full Table Scan）将遍历全表以找到匹配的行。
-- 参数 possible_keys字段：（哪些索引可以使用）
主要的作用：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际应用
-- 参数 key字段：（哪些索引被实际使用）
主要的作用：实际使用的索引，如果为NULL，可能没有建立索引，也可能索引失效，未使用。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
-- 参数 key_len字段：
主要的作用：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
-- ref字段：
主要的作用：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。
-- rows字段：（每张表有多少行被优化器查询）
主要的作用：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
-- Extra字段：
主要的作用：包含不适合在其他列中显示但十分重要的额外信息。
（1）Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MYSQL中无法利用索引完成的排序操作称为“文件排序”。
（2）Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表，常见于排序order by 和分组查询 group by。
（3）Using index：
表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效果不错！如果同时出现了using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
（4）Using where：
表明使用了where过滤。
（5）Using join buffer：
使用了连接缓存。
（6）impossible where：
where子句的值总是false,不能用来获取任何元组。
（7）select tables optimized away
在没有GROUP BY 子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
（8）distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
4.6 索引优化
- 当使用索引列进行查询时尽量不要使用表达式，把计算放到业务层而不是数据库层
- 尽量使用主键查询，而不是其他索引，因为主键查询不会触发【回表】
- 回表 ： 其他索引先查询出这个索引对应的主键，然后再通过主键查询数据
- 使用前缀索引
- 使用索引扫描来排序
- union all, in , or都可以使用索引，但是推荐使用in
- or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
- 范围可以使用到索引
- 范围条件 < <= > >= between and
- 范围列可以使用到索引，但是范围后面的列无法用到索引，索引最多用于一个范围列 >
- 强制类型转换会全表扫描：如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描【字符串不加单引号索引失效】
- where phone = 18817384287 不会触发索引
- where phone = ‘18817384287’ 会触发索引
- 更新较频繁，数据区分度不高的字段上不宜建立索引
- 更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
- 类似于性别这样的字段，因为区分不大，建立索引是没有意义的，不能有效过滤数据
- 一般区分度在80%以上的时候就可以建立索引，区分度可以使用count(distinct(列名))/count(*)来计算
- 创建索引的列不允许为null，否则可能会得到不符合预期的结果
- 当需要进行表连接的时候，最好不要超过三张表，因为要使用join的字段，数据类型必须一致
- 能使用limit的时候尽量使用limit
- like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效
- 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0
- 对索引字段进行计算操作、字段上使用函数。对索引列运算（如，+、-、*、/），索引失效
- 索引字段上使用is null， is not null，可能导致索引失效
- 组合索引，不是使用第一列索引，索引失效 index(idcard,name,age)
where idcard='' -- 可以用到索引
where idcard='' and name='' and age=1; -- 可以用到索引
where name='' and idcard='' and and age=1; -- 可以用到索引
where age=1 and name='' and idcard=''; -- 可以用到索引
where name='' -- 不会使用索引
where age=1 -- 不会使用索引
- 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
当表的索引被查询，会使用最好的索引，除非优化器使用全表扫描更有效。优化器优化成全表扫描取决与使用最好索引查出来的数据是否超过表的30%的数据。
不要给'性别'等增加索引。如果某个数据列里包含了均是"0/1"或“Y/N”等值，即包含着许多重复的值，就算为它建立了索引，索引效果不会太好，还可能导致全表扫描。
MySQL出于效率与成本考虑，估算全表扫描与使用索引，哪个执行快。这跟它的优化器有关，下图就是MySQL查询的逻辑架构图：
-- 聚集索引（主键和数据是绑定在一起的）
-- INNODB 数据库的存储引擎（用来指定数据库的数据是按照什么方式来存储数据）
-- INNODB 的主键索引策略就是一个聚集索引策略
select * from t_emp where id=1;
-- 直接通过主键id查询id对应的数据
select * from t_emp where name='张三';
-- 通过张三先查询到张三的主键id
-- 然后再通过主键id查询id对应的数据
-- 索引常用的语法
show index from test_index;
-- 索引的作用 : 为了提高查询效率
select * from test_index where title='MSG1899053';
> OK
> 时间: 1.445s
-- 执行计划 Explain
EXPLAIN
select * from test_index where title='MSG1899053';
EXPLAIN
select * from t_emp where tno='WNSH009';
EXPLAIN
select * from t_emp where id BETWEEN 5 and 10;
EXPLAIN
select * from t_emp;
-- 如何给表中的列来创建索引
create index idx_title on test_index(title);
-- 创建索引是一件非常耗时的操作 B+Tree
-- create table 表名(
-- ,
-- primary key(id),
-- unique(tno),
-- index(idcard),
-- foreign key(dept_id) references t_dept(id)
-- );
-- 如何删除一个字段的索引
alter table test_index drop index idx_title;
-- 添加索引的目的 ： 为了极大的提高查询效率
-- 查看每列的区分度来决定是否应该创建索引
select count(distinct(id))/count(*)*100 as id,
count(distinct(tno))/count(*)*100 as tno,
count(distinct(name))/count(*)*100 as name,
count(distinct(gender))/count(*)*100 as gender,
count(distinct(title))/count(*)*100 as title
from t_emp;
EXPLAIN
select * from t_emp where name='张君宝';
create index idx_emp_name on t_emp(name);
alter table t_emp drop index emp_name_idx;
show index from t_emp;
-- 演示%模糊查询索引失效的过程
EXPLAIN
select * from t_emp where `name` LIKE '%丹';
SQL 优化技巧 ：
https://www.jb51.net/article/136701.htm
https://www.cnblogs.com/lideqiang0909/p/11162665.html
5. 事务
5.1 事务的概念
事务(Transaction)是用来维护数据库完整性的,它能够保证一系列的MySQL操作要么全部执行,要么全不执行。
例子1 : 举一个例子来进行说明,例如转账操作: A账户要转账给B账户,那么A账户上减少的钱数和B账户上增加的钱数必须一致,也就是说A账户的转出操作和B账户的转入操作要么全部执行,要么全不执行;如果其中一个操作出现异常而没有执行的话,就会导致账户A和账户B的转入转出金额不一致的情况,为而事实上这种情况是不允许发生的,所以为了防止这种情况的发生,需要使用事务处理。
例子2 : 在淘宝购物下订单的时候,商家库存要减少,订单增加记录,付款我的账号少100元操作要么全部执行,要么全不执行。
事务的概念
事务(Transaction)指的是一个操作序列,该操作序列中的多个操作要么都做,要么都不做,是一个不可分割的工作单位中的逻辑工作单位,由DBMS (数据库管理系统)中的事务管理子系统负责事务的处理
目前常用的存储引擎有InnoDB (MySQL5.5以后默认的存储引擎)和MyISAM (不支持事务处理的) ,其中InnoDB支持事务处理机制,而MyISAM不支持。
事务的特性
事务处理可以确保除非事务性序列内的所有操作都成功完成,否则不会永久更新面向数据的资源,通过将一组相关操作组合为一个要么全部成功要么全部失败的序列,可以简化错误恢复并使应用程序更加可靠，但并不是所有的操作序列都可以称为事务,这是因为一个操作序列要成为事务,必须满足事务的原子性(Atomicity) 、一致性(Consistency) 、隔离性(Isolation)和持久性(Durability) .这四个特性简称为ACID特性。
5.2 事务的特性
原子性（Atomicity）
事务中包含的逻辑步骤不可分割
整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性（Consistency）
事务执行前后数据完整性一致（转账）
以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。
隔离性（Isolation）
事务在执行期间不应受其他事务影响
如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
持久性（Durability）
事务执行成功应该持久保存到数据库
在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。
5.3 事务的并发性
- 脏读（Dirty read）
一个事务读到了另一个事务没有提交的update数据
| 时间点 | 事务A | 事务B |
|---|---|---|
| 1 | 开启事务 | |
| 2 | 开启事务 | |
| 3 | 查询余额1000 | |
| 4 | 余额增加100 | |
| 5 | 查询余额1100 (脏数据) | |
| 6 | 事务回滚 |
- 不可重复读 （Unrepeatable read）
一个事务读取到了另一个事务已经提交的update读数据，最终导致这个事务多次查询的结果不一致
| 时间点 | 事务A | 事务B |
|---|---|---|
| 1 | 开启事务 | |
| 2 | 开启事务 | |
| 3 | 查询余额1000 | 查询余额1000 |
| 4 | 余额增加100 | |
| 5 | 查询余额1000 | |
| 6 | 提交事务 | |
| 查询余额1100 |
- 幻读（Phantom read）
一个事务读取到了另一个事务已经提交的insert或delete数据，导致多次查询结果不一致
| 时间点 | 事务A | 事务B |
|---|---|---|
| 1 | 开启事务 | |
| 2 | 开启事务 | |
| 3 | 查询所有记录 10 | 查询所有记录 10 |
| 4 | 插入/删除一条记录 | |
| 5 | 提交记录 | |
| 6 | 查询所有记录9/11条 |
不可重复读和幻读的区别
不可重复读的重点是修改， 幻读的重点在于新增或删除
-- 1. 不可重复读
分析 ： 事务1中A先生读取自己的工资为1000的操作还未完成，事务2中的B先生就修改了A的工资为2000，导致A再次读取自己的工资时工资变成了2000，这就是不可重复读问题
-- 2. 幻读
分析1 ： 假设事务1的HR查询工资大于5000的工资单数据时，现在有4人, 事务2的HR初始查询到数据也是4条，但是事务2的HR有插入了一个新的工资大于5000的记录。这样当事务1的HR再去查询记录时就变成了5条数据，这就导致了幻读
解决不可重复读的问题只需要锁住满足条件的行，解决幻读需要锁表
锁是计算机在执行多线程或线程时用于并发访问同一共享资源时的同步机制，MySQL中的锁是在服务器层或者存储引擎层实现的，保证了数据访问的一致性与有效性。
MySQL锁可以按模式分类为：乐观锁与悲观锁。按粒度分可以分为全局锁、表级锁、页级锁、行级锁。按属性可以分为：共享锁、排它锁。按状态分为：意向共享锁、意向排它锁。按算法分为：间隙锁、临键锁、记录锁。
行级锁是粒度最低的锁，发生锁冲突的概率也最低、并发度最高。但是加锁慢、开销大，容易发生死锁现象。
MySQL中只有InnoDB支持行级锁，行级锁分为共享锁和排他锁。
在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking。
5.4 事务的隔离级别
事务的隔离级别用于决定如何控制并发用户读写数据的操作。数据库是允许多用户并发访问的，如果多个用户使用开启事务并对同一数据进行读写操作的话，有可能会出现脏读，不可重复读，幻读的问题，所以MySQL提供了四种隔离级别来解决上述的问题。
事务隔离级别从低到高一依次为 ：READ-UNCOMMITTED 、 READ-COMMITTED 、REPEATABLE- READ 、 SERIALIZABLE，隔离级别越低，越能支持高并发的数据库操作，隔离级别越高，则安全性越高，但是并发性就会降低，因此需要给数据库设置合适的隔离级别。
MySQL默认的隔离级别是 ： REPEATABLE-READ 【防护了脏读和不可重复读】
Oracle默认的隔离级别是 ： READ-COMMITTED 【防护了脏读】
数据库隔离级别的最低要求设置 ： 需要防止脏读的出现
使用数据库默认的隔离级别就可以了
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ-UNCOMMITTED 读未提交 1 | √ | √ | √ |
| READ-COMMITTED 读已提交 2 | × | √ | √ |
| REPEATABLE-READ 可重复读 4 | × | × | √ |
| SERIALIZABLE 串行化 8 | × | × | × |
事务操作的基本语法
-- MySQL5.0.x 版本
select @@tx_isolation; -- 查询事务的默认隔离级别
-- MySQL8.0.x 版本
select @@transaction_isolation; -- 查询事务的默认隔离级别
-- 修改MySQL数据库默认的隔离级别
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;
-- 需要掌握
-- 开启事务
start transaction;
-- 提交事务
commit;
-- 回滚事务
rollback;
-- 事务开启后需要通过提交或回滚来结束事务
-- MySQL8.0.x 版本
select @@transaction_isolation; -- 查询事务的默认隔离级别
-- MySQL的默认隔离级别为 ： REPEATABLE-READ 可重复读
-- 演示脏读问题 ： 降低隔离级别到 read uncommitted （读未提交）
set session transaction isolation level read uncommitted;
-- 开启事务
START TRANSACTION;
-- 查询张三的余额
select * from t_account where name='张三';
update t_account set balance=balance+100 where name='张三';
-- 回滚数据
rollback;
-- 提交
commit;
-- 前面学习的DML语句（insert dalete update） MySQL数据库会动提交
-- 原则 ：
假如你想单独使用CRUD语句完成操作，那就直接使用SQL即可
假如你要将所有的操作放在同一个事务中，就需要手动开启事务来保证
整个操作过程在同一个事务内
-- 模拟张三向李四转账100过程（重点）：
-- 1. 扣钱
update t_account set balance=balance-100 where name='张三'; -- 自动提交
-- 2. 加钱
update t_account set balance=balance2+100 where name='李四';-- 自动提交
-- 如何加事务？【手动开启事务，保证多个操作在同一个事务内】
start transaction; -- 手动开启事务
update t_account set balance=balance-100 where name='张三';
update t_account set balance=balance+100 where name='李四';
commit; -- 所有DML操作全部成功时才会提交
rollback; -- 所有DML操作只要有一个失败就必须回滚
6. JDBC
6.1 JDBC 概念
6.2 JDBC 流程
- 加载数据库驱动（校验数据库驱动 ： 检查jar是否正常依赖工程）
- 获取数据库连接对象 Connection
- 获取发送和执行SQL语句的预编译对象 PrepareStatement
- 执行SQL语句
- 添加，删除，修改【返回的是受影响的行数 int】 executeUpdate()
- 查询 【返回的是查询的结果集 ResultSet】 executeQuery()
- 释放资源 close()
/**
* 标准的JDBC 添加、删除、修改通用流程
*/
public class CommonJdbc {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1. 校验数据库驱动
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/tc38";
String user = "root";
String password = "root";
//2. 获取数据库连接对象
conn = DriverManager.getConnection(url, user, password);
//3. 获取预编译对象（编译SQL和发送SQL）
String sql = "";
ps = conn.prepareStatement(sql);
//4. 执行SQL
int rows = ps.executeUpdate();
System.out.println(rows>0?"操作成功":"操作失败");
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
// 5. 释放资源
if(ps != null)
ps.close();
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
6.3 JDBC实现添删改
6.3.1 添加
/**
* 标准的JDBC 添加通用
*/
public class AddJdbc {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1. 校验数据库驱动
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/tc38";
String user = "root";
String password = "root";
//2. 获取数据库连接对象
conn = DriverManager.getConnection(url, user, password);
//3. 获取预编译对象（编译SQL和发送SQL）
String sql = "insert into t_emp(tno,name,gender,birthday,salary) value(?,?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, "WNSH1001");
ps.setString(2, "谢逊");
ps.setString(3, "男");
ps.setString(4, "1998-06-08");
ps.setBigDecimal(5, new BigDecimal("8000"));
//4. 执行SQL
int rows = ps.executeUpdate();
System.out.println(rows>0?"添加成功":"添加失败");
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
// 5. 释放资源
if(ps != null)
ps.close();
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
6.3.2 删除
/**
* JDBC的标准流程 ： 删除
*/
public class DeleteJdbc {
public static void main(String[] args) {
//1. 校验数据库驱动【通过代码来检查一下数据库的jar有没有导入】
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//2. 获取数据库的连接对象【驱动管理器 DriverManager】
String url = "jdbc:mysql://localhost:3306/tc38?useSSL=false&characterEncoding=UTF8&serverTimeZone=UTC"; // 用来定位你需要连接哪台电脑的哪个数据库服务
String user = "root"; // 数据库登录的账户
String password = "root"; //数据库登录的密码
try {
Connection conn = DriverManager.getConnection(url, user, password);
//3. 获取预编译对象（通过连接对象来获取）
String sql = "DELETE FROM t_emp WHERE id=? "; // ? 占位符
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, 26);
//4. 执行SQL(ps将SQL语句发送到DBMS来执行)
int rows = ps.executeUpdate();
System.out.println(rows>0?"删除成功":"删除失败");
// 5. 释放资源
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
6.3.3 修改
/**
* 标准的JDBC 添加通用
*/
public class AddJdbc {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
try {
//1. 校验数据库驱动
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://127.0.0.1:3306/tc38";
String user = "root";
String password = "root";
//2. 获取数据库连接对象
conn = DriverManager.getConnection(url, user, password);
//3. 获取预编译对象（编译SQL和发送SQL）
String sql = "insert into t_emp(tno,name,gender,birthday,salary) value(?,?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setString(1, "WNSH1001");
ps.setString(2, "谢逊");
ps.setString(3, "男");
ps.setString(4, "1998-06-08");
ps.setBigDecimal(5, new BigDecimal("8000"));
//4. 执行SQL
int rows = ps.executeUpdate();
System.out.println(rows>0?"添加成功":"添加失败");
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
// 5. 释放资源
if(ps != null)
ps.close();
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
6.4 连接工厂的封装
属性文件 ： db.properties
mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/tc38?useSSL=false&characterEncoding=UTF8&serverTimeZone=UTC
mysql.user=root
mysql.password=root
/**
* 数据库工具类
* 1. 校验数据库驱动
* 2. 获取数据库连接对象
*/
public class DBUtil {
private static String driver;
private static String url;
private static String user;
private static String password ;
// 静态代码快 ：类加载时只会执行一次（加载驱动）
static {
try {
// 加载属性文件获取数据库连接参数
Properties props = new Properties();
props.load(new FileReader("db.properties"));
driver = props.getProperty("mysql.driver"); // 获取数据库驱动路径
url = props.getProperty("mysql.url"); // 获取连接数据库的url
user = props.getProperty("mysql.user"); // 获取数据库的用户名
password =props.getProperty("mysql.password"); // 获取数据库的密码
// 校验数据库的驱动
Class.forName(driver);
} catch (ClassNotFoundException e) {
System.out.println("数据库驱动校验失败！");
} catch (IOException e) {
System.out.println("数据属性文件初始化失败");
}
}
/**
* 获取数据库的连接对象
* @return
*/
public static Connection getConnnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
System.out.println("获取数据库连接失败！");
}
return null;
}
/**
* 释放数据库资源（添加删除修改专用）
* @param conn
* @param ps
*/
public static void release(Connection conn, PreparedStatement ps) {
release(conn,ps,null);
}
/**
* 释放数据库资源（查询专用）
* @param conn
* @param ps
* @param rs
*/
public static void release(Connection conn, PreparedStatement ps, ResultSet rs) {
try {
if(rs != null)
rs.close();
if(ps != null)
ps.close();
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
6.5 JDBC 查询
- 查询单个对象
/**
* 通过主键id来查询一个特定的对象 : 单个对象
流程 ： 一条记录 转换成 Java中的一个对象
t_emp 转换成 Employee 对象
*/
public class QueryTest1 {
public static void main(String[] args) {
//1. 通过连接工厂来获取数据库的连接对象
Connection conn = DBUtil.getConnnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
//2. 获取预编译对象
String sql = "SELECT id,tno,name,gender,birthday,title,salary,manager_id,dept_id "
+ "FROM t_emp WHERE id=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, 1);
//3. 发送并执行SQL并获取到一个结果集
rs = ps.executeQuery();
while(rs.next()) { // 判断结果集是否存在下一条记录
int id = rs.getInt("id");
String tno = rs.getString("tno");
String name = rs.getString("name");
String gender = rs.getString("gender");
Date date = rs.getDate("birthday"); // 数据库Date与util的Date转换【时间戳】
long time = date.getTime();
java.util.Date birthday = new java.util.Date(time);
String title = rs.getString("title");
BigDecimal salary = rs.getBigDecimal("salary");
int managerId = rs.getInt("manager_id");
int deptId = rs.getInt("dept_id");
// 将数据库中的一条记录封装成Java一个实体对象
Employee employee = new Employee(id, tno, name, gender, birthday, title, salary, managerId, deptId);
System.out.println(employee);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
// 释放资源
DBUtil.release(conn, ps, rs);
}
}
}
- 查询对象的集合
package com.woniuxy.query;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.woniuxy.entity.Employee;
import com.woniuxy.util.DBUtil;
/**
* 通过主键id来查询一个特定的对象
查询的结果是一个对象的集合
场景：
数据库查询（多条记录） 存储 对象的集合
*/
public class QueryTest2 {
public static void main(String[] args) {
//1. 通过连接工厂来获取数据库的连接对象
Connection conn = DBUtil.getConnnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
//2. 获取预编译对象
String sql = "SELECT id,tno,name,gender,birthday,title,salary,manager_id,dept_id "
+ "FROM t_emp";
ps = conn.prepareStatement(sql);
//3. 发送并执行SQL并获取到一个结果集
rs = ps.executeQuery();
// 创建一个集合来存储所有的员工
ArrayList<Employee> list = new ArrayList<>();
while(rs.next()) { // 判断结果集是否存在下一条记录
int id = rs.getInt("id");
String tno = rs.getString("tno");
String name = rs.getString("name");
String gender = rs.getString("gender");
Date date = rs.getDate("birthday"); // 数据库Date与util的Date转换【时间戳】
long time = date.getTime();
java.util.Date birthday = new java.util.Date(time);
String title = rs.getString("title");
BigDecimal salary = rs.getBigDecimal("salary");
int managerId = rs.getInt("manager_id");
int deptId = rs.getInt("dept_id");
// 将数据库中的一条记录封装成Java一个实体对象
Employee employee = new Employee(id, tno, name, gender, birthday, title, salary, managerId, deptId);
list.add(employee); // 将数据库中的一条条记录封装成一个个对象然后存储在集合中
}
// 打印出查询到的所有的对象
for (Employee emp : list) {
System.out.println(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
// 释放资源
DBUtil.release(conn, ps, rs);
}
}
}
- 多表查询 【使用HashMap + ArrayList处理】
/**
* JDBC 处理多表查询
*/
public class QueryTest4 {
public static void main(String[] args) {
//1. 通过连接工厂来获取数据库的连接对象
Connection conn = DBUtil.getConnnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
//2. 获取预编译对象
String sql = "SELECT e.id,tno,name,gender,birthday,title,salary,manager_id,dept_id,d.dname,d.location,d.area "
+ "FROM t_emp e INNER JOIN t_dept d ON e.dept_id = d.id";
ps = conn.prepareStatement(sql);
//3. 发送并执行SQL并获取到一个结果集
rs = ps.executeQuery();
// 创建一个集合来存储所有的员工
ArrayList<HashMap<String,Object>> list = new ArrayList<>();
while(rs.next()) { // 判断结果集是否存在下一条记录
// 将数据库中的一条记录封装成Java一个实体对象
HashMap<String, Object> map = new HashMap<>();
map.put("id", rs.getInt("id"));
map.put("tno", rs.getString("tno"));
map.put("name", rs.getString("name"));
map.put("gender", rs.getString("gender"));
map.put("birthday", rs.getString("birthday"));
map.put("title", rs.getString("title"));
map.put("salary", rs.getBigDecimal("salary"));
map.put("managerId", rs.getInt("manager_id"));
map.put("deptId", rs.getInt("dept_id"));
map.put("dname", rs.getString("dname"));
map.put("location", rs.getString("location"));
map.put("area", rs.getString("area"));
list.add(map); // 将数据库中的一条条记录封装成一个个对象然后存储在集合中
}
// 打印出查询到的所有的对象
for (HashMap<String, Object> map : list) {
System.out.println(map);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
// 释放资源
DBUtil.release(conn, ps, rs);
}
}
}
- 查询统计函数
package com.woniuxy.query;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.woniuxy.util.DBUtil;
/**
查询统计函数
*/
public class QueryTest5 {
public static void main(String[] args) {
//1. 通过连接工厂来获取数据库的连接对象
Connection conn = DBUtil.getConnnection();
PreparedStatement ps = null;
ResultSet rs = null;
try {
//2. 获取预编译对象
String sql = "SELECT COUNT(*) AS count, MAX(salary) FROM t_emp";
ps = conn.prepareStatement(sql);
//3. 发送并执行SQL并获取到一个结果集
rs = ps.executeQuery();
if(rs.next()) { // 判断结果集是否存在下一条记录
long count = rs.getLong("count");
float maxSalary = rs.getFloat("MAX(salary)");
System.out.println("员工表共有"+count+"条记录");
System.out.println("员工的最高薪资为："+maxSalary);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
// 释放资源
DBUtil.release(conn, ps, rs);
}
}
}
6.6 SQL注入
SQL注入的浅层次 理解 ： WHERE 条件后拼接了一个恒成立的条件，导致后面的所有过滤条件失效
select * from t_user
where uname='admin1' and password='1234' OR 1=1 ;
-- sql 注入
-- JDBC中的Statement会产生SQL注入
-- JDBC中的PrepareStatement防止SQL注入（？）
SELECT id,uname,password FROM t_user WHERE uname='admin' and password='123'
防止SQL注入，我们需要注意以下几个要点：
1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双”-“进行转换等。
2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。
5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
6.sql注入的检测方法一般采取辅助软件或网站平台来检测，软件一般采用sql注入检测工具jsky，网站平台就有亿思网站安全平台检测工具。MDCSOFT SCAN等。采用MDCSOFT-IPS可以有效的防御SQL注入，XSS攻击等。
/**
* 演示SQL注入
*/
public class SQLAttack {
public static void main(String[] args) throws SQLException {
String name = "'admin'";
String password="'124'" +" OR 1=1";
Connection conn = DBUtil.getConnnection();
// 1. 获取一个Statement
Statement st = conn.createStatement();
// 全部采用拼接的方式完成
String sql = "SELECT id,uname,password FROM t_user "
+ "WHERE uname="+name+" and password="+password;
ResultSet rs = st.executeQuery(sql);
while(rs.next()) {
String uname = rs.getString("uname");
String pwd = rs.getString("password");
System.out.println("账号:"+uname+",密码："+pwd);
}
rs.close();
st.close();
conn.close();
}
}
/**
* SQL注入的解决 : 使用PreparedStatement实现
*/
public class SQLAttackResolve {
public static void main(String[] args) throws SQLException {
String name = "admin";
String password="123 OR 1=1";
Connection conn = DBUtil.getConnnection();
// 1. 获取一个Statement
String sql = "SELECT id,uname,password FROM t_user WHERE uname=? and password=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, name);
ps.setString(2, password);
ResultSet rs = ps.executeQuery();
while(rs.next()) {
String uname = rs.getString("uname");
String pwd = rs.getString("password");
System.out.println("账号:"+uname+",密码："+pwd);
}
rs.close();
ps.close();
conn.close();
}
}
7. 分包思想
7.1 DAO模式分析
7.2 DAO接口的设计
持久层数据接口是一种【Java操作数据库CRUD】的通用规范
/**
* 数据持久层接口（通用的规范）
*/
public interface EmployeeDAO {
/**
* 添加员工对象（向数据库表中插入数据）
* @param emp
* @return 返回受影响的行数
*/
public abstract int add(Employee emp);
/**
* 根据主键来删除数据库表中的数据
* @param id
* @return
*/
public abstract int deleteByPrimaryKey(int id) throws SQLException ;
/**
* 更新数据库中的记录（根据主键来更新）
* @param emp
* @return
*/
public abstract int update(Employee emp);
/**
* 根据id来查询数据库记录并封装成一个实体对象
* @param id
* @return null 表示没有查询到任何数据
*/
public abstract Employee findById(int id);
/**
* 查询数据库中的所有记录
* @return null 表示没有查询到任何数据
*/
public abstract List<Employee> findAll();
}
7.3 DAO实现类封装
package com.woniuxy.dao.impl;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.woniuxy.dao.EmployeeDAO;
import com.woniuxy.entity.Employee;
import com.woniuxy.util.DBUtil;
/**
* EmployeeDAO的数据持久层实现类
* 通过JDBC与数据库交互
*/
public class EmployeeDAOImpl implements EmployeeDAO{
@Override
public int add(Employee emp) throws SQLException {
Connection conn = DBUtil.getConnnection();
String sql = "INSERT INTO t_emp(tno,name,gender,birthday,title,salary,manager_id,dept_id) VALUE(?,?,?,?,?,?,?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, emp.getTno());
ps.setString(2, emp.getName());
ps.setString(3, emp.getGender());
long time = emp.getBirthday().getTime();
ps.setDate(4, new Date(time));
ps.setString(5, emp.getTitle());
ps.setBigDecimal(6, emp.getSalary());
ps.setObject(7, emp.getManagerId());
ps.setObject(8, emp.getDeptId());
int rows = ps.executeUpdate();
DBUtil.release(conn, ps);
return rows;
}
@Override
public int deleteByPrimaryKey(int id) throws SQLException {
Connection conn = DBUtil.getConnnection();
String sql = "DELETE FROM t_emp WHERE id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, id);
int rows = ps.executeUpdate();
DBUtil.release(conn, ps);
return rows;
}
@Override
public int update(Employee emp) throws SQLException {
// TODO Auto-generated method stub
return 0;
}
@Override
public Employee findById(int id) throws SQLException {
Connection conn = DBUtil.getConnnection();
String sql = "SELECT id,tno,name,gender,birthday,title,salary,manager_id,dept_id "
+ "FROM t_emp WHERE id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, id);
//3. 发送并执行SQL并获取到一个结果集
ResultSet rs = ps.executeQuery();
Employee emp = null;
while(rs.next()) { // 判断结果集是否存在下一条记录
int eid = rs.getInt("id");
String tno = rs.getString("tno");
String name = rs.getString("name");
String gender = rs.getString("gender");
Date date = rs.getDate("birthday"); // 数据库Date与util的Date转换【时间戳】
long time = date.getTime();
java.util.Date birthday = new java.util.Date(time);
String title = rs.getString("title");
BigDecimal salary = rs.getBigDecimal("salary");
int managerId = rs.getInt("manager_id");
int deptId = rs.getInt("dept_id");
// 将数据库中的一条记录封装成Java一个实体对象
emp = new Employee(eid, tno, name, gender, birthday, title, salary, managerId, deptId);
}
DBUtil.release(conn, ps,rs);
return emp;
}
@Override
public List<Employee> findAll() throws SQLException {
Connection conn = DBUtil.getConnnection();
String sql = "SELECT id,tno,name,gender,birthday,title,salary,manager_id,dept_id "
+ "FROM t_emp";
PreparedStatement ps = conn.prepareStatement(sql);
//3. 发送并执行SQL并获取到一个结果集
ResultSet rs = ps.executeQuery();
ArrayList<Employee> list = new ArrayList<>();
while(rs.next()) { // 判断结果集是否存在下一条记录
int eid = rs.getInt("id");
String tno = rs.getString("tno");
String name = rs.getString("name");
String gender = rs.getString("gender");
Date date = rs.getDate("birthday"); // 数据库Date与util的Date转换【时间戳】
long time = date.getTime();
java.util.Date birthday = new java.util.Date(time);
String title = rs.getString("title");
BigDecimal salary = rs.getBigDecimal("salary");
int managerId = rs.getInt("manager_id");
int deptId = rs.getInt("dept_id");
// 将数据库中的一条记录封装成Java一个实体对象
list.add(new Employee(eid, tno, name, gender, birthday, title, salary, managerId, deptId));
}
DBUtil.release(conn, ps, rs);
return list.isEmpty() ? null : list;
}
}
8. JDBC 事务
/**
* Account类的持久层接口
*/
public interface AccountDAO {
/**
* 1. 给name的账户增加money金额
* @param name
* @param money
* @return
*/
int add(Connection conn, String name, BigDecimal money) throws SQLException ;
/**
* 2. 给name的账户减少money金额
* @param name
* @param money
* @return
*/
int sub(Connection conn, String name, BigDecimal money) throws SQLException ;
}
/**
* AccountDAO的实现类 ： JDBC操作数据库的代码
*/
public class AccountDAOImpl implements AccountDAO {
@Override
public int add(Connection conn, String name, BigDecimal money) throws SQLException {
String sql = "UPDATE t_account SET balance=balance+? WHERE name=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, money);
ps.setObject(2, name);
int rows = ps.executeUpdate();
return rows;
}
@Override
public int sub(Connection conn, String name, BigDecimal money) throws SQLException {
String sql = "UPDATE t_account SET balance=balance-? WHERE name=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setObject(1, money);
ps.setObject(2, name);
int rows = ps.executeUpdate();
return rows;
}
}
/**
* Account 业务逻辑层接口
*/
public interface AccountService {
/**
* 转账的业务API
* @param from
* @param to
* @param money
* @return
*/
public abstract boolean transferMoney(String from, String to, BigDecimal money);
}
/**
* AccountService 实现类
* 功能 ： 封装了真实的业务逻辑（都是调用DAO层的API完成业务的）
事务 ： 完成一件事情的所有逻辑单元要么同时成功，要么同时失败
ACID
如何保证多个不同的操作在同一个事务中 ： 保证多个操作使用的是同一个数据库连接对象
*/
public class AccountServiceImpl implements AccountService {
@Override
public boolean transferMoney(String from, String to, BigDecimal money) {
if(from == null || to ==null || money == null || "".equals(from) || "".equals(to) || money.doubleValue()<=0) {
return false;
}
// 创建一个 AccountDaoImpl（持久层接口的实现类）对象
AccountDAOImpl adi = new AccountDAOImpl();
// 【事务1】 ： 需要在业务逻辑层创建一个连接对象
Connection conn = DBUtil.getConnnection();
try {
// 【事务2】：手动开启事务【将DML语句的自动提交改为手动提交】
conn.setAutoCommit(false);
//1. 转账人from需要扣钱 sub
int row1 = adi.sub(conn, from, money);
//2. 收款人to需要加钱 add
if(row1 > 0) {
int row2 = adi.add(conn, to, money);
if(row2 > 0) {
conn.commit(); //【事务3】 当所有操作都完成时，才能提交（提交前数据都在缓存中）
return true;
}else {
throw new SQLException();
}
}else {
throw new SQLException();
}
} catch (SQLException e) {
try {
conn.rollback(); // 【事务4】只要有一个事务的步骤出错就回滚
} catch (SQLException e1) {
e1.printStackTrace();
}
}
return false;
}
}
事务学习模板
public class XxxServiceImpl implements XxxService{
事务方法(参数){
Connection conn = DBUtil.getConnnection(); // 获取数据库连接
try{
conn.setAutoCommit(false); // 开启事务
dao.方法1(conn,...);
dao.方法2(conn,...);
dao.方法3(conn,...);
dao.方法4(conn,...);
以上操作都成功时 conn.commit(); // 提交事务
}catch(Exception e){
conn.rollback(); // 回滚事务
}finally{
conn.close();
}
}
}
若有收获，就点个赞吧
0 人点赞
