目录
一、Mysql初级
1、数据库简介
1.1 简介
- 指一种操作和管理数据库的大型软件,用于建立、使用和维护数据库,对数据库进行统一管理和控制,以保证数据库的安全性和完整性。
- 用户通过数据库管理系统访问数据库中的数据。
- 数据库软件应该为数据库管理系统,数据库是通过数据库管理系统创建和操作的。
1.2 常见的数据库管理系统
1.3 三大范式
- 第一范式:无重复的列。当关系模式R的所有属性都不能在分解为更基本的数据单位时,称R是满足第一范式的,简记为1NF。满足第一范式是关系模式规范化的最低要求,否则,将有很多基本操作在这样的关系模式中实现不了。
- 第二范式:属性完全依赖于主键 [ 消除部分子函数依赖 ]。如果关系模式R满足第一范式,并且R得所有非主属性都完全依赖于R的每一个候选关键属性,称R满足第二范式,简记为2NF。第二范式(2NF)是
在第一范式( 1NF )的基础上建立起来的,即满足第二范式( 2NF )必须先满足第一范式( 1NF )。 第 二范式( 2NF )要求数据库表中的每个实例或行必须可以被唯一地区分 。为实现区分通常需要为表加上一个列,以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。
- 第三范式:属性不依赖于其它非主属性 [ 消除传递依赖 ]。设R是一个满足第一范式条件的关系模式,X是R的任意属性集,如果X非传递依赖于R的任意一个候选关键字,称R满足第三范式,简记为3NF. 满足
第三范式( 3NF )必须先满足第二范式( 2NF )。 第三范式( 3NF )要求一个数据库表中不包含已在其 它表中已包含的非主关键字信息。注:关系实质上是一张二维表,其中每一行是一个元组,每一列是一个属性第二范式( 2NF )和第三范式( 3NF )的概念很容易混淆,区分它们的关键点在于,2NF :非主键列是 否完全依赖于主键,还是依赖于主键的一部分;3NF :非主键列是直接依赖于主键,还是直接依赖于非 主键列。
1.4 Mysql的卸载和安装
[mysqld]
# 设置 3306 端口port=3306# 设置 mysql 的安装目录basedir=D:\Program Files\mysql # 设置 mysql 数据库的数据的存放目录datadir=D:\Program Files\mysql\data# 允许最大连接数max_connections=200# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统max_connect_errors=10# 服务端使用的字符集默认为 UTF8character-set-server=utf8# 创建新表时将使用的默认存储引擎default-storage-engine=INNODB# 默认使用 “mysql_native_password” 插件认证default_authentication_plugin=mysql_native_password[mysql]# 设置 mysql 客户端默认字符集default-character-set=utf8[client]# 设置 mysql 客户端连接服务端时默认使用的端口port=3306default-character-set=utf8
mysqld --initialize --console
alter user 'root'@'localhost' identified with mysql_native_password BY ' 新密码 ';
修改密码,注意命令尾的分号一定要有,这是mysql的语法
退出数据库:
2、SQL语言
2.1 概述
- 各数据库厂商都支持ISO的SQL标准,普通话
- 各数据库厂商在标准的基础上做了自己的扩展,方言
2.2 SQL语句分类
- DDL(Data Defifinition Language):数据定义语言,用来定义数据库对象:库、表、列等。
- DML(Data Manipulation Language):数据操作语言,用来定义数据库记录(数据)增删改。
- DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别。
- DQL(Data Query Language):数据查询语言,用来查询记录(数据)查询。
2.3 DDL操作数据库
//create database 数据库名
CREATE DATABASE mydb1;
//create database 数据库名 character set 编码方式
CREATE DATABASE mydb2 character SET GBK;
//create database 数据库名 character set 编码方式 collate 排序规则
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;
show databases;
//show create database 数据库名;
Show CREATE DATABASE mydb2;
alter database 数据库名 character set 编码方式
ALTER DATABASE mydb2 character SET utf8;
DROP DATABASE mydb3;
Select database();
USE mydb2;
2.4 DDL操作表
CREATE TABLE 表名(
列名1 数据类型 [约束],
列名2 数据类型 [约束],
列名n 数据类型 [约束]
);
CREATE TABLE Employees(
id INT ,
age INT ,
first VARCHAR(255),
last VARCHAR(255)
);
DROP TABLE table_name;
SHOW TABLES;
DESC employee;
ALTER TABLE employee ADD image blob;
ALTER TABLE employee MODIFY job varchar(60);
ALTER TABLE employee change job job varchar(60);
ALTER TABLE user CHANGE name username varchar(100);
ALTER TABLE employee DROP image;
alter table user rename users;
SHOW CREATE TABLE user;
ALTER TABLE user CHARACTER SET gbk;
练习:表名 card( 会员卡表 )列名 数据类型cardid intcardnum varchar(20)regDate date需求:(1) 创建该表(2) 将 card 表名修改为 CardInfo(3) 添加 delDate( 注销时间 ) 列到表中(4) 将 cardnum 改为 varchar(30)(5) 删除 regDate 列(6) 删除 cardInfo 表
2.5 DML操作
insert into student(stuname,stuage,stusex,birthday) values('张三1',18,'a','2000- 1-1');
- 注意:
- 非数值的列值两侧需要加单引号
- 添加数据的时候可以将列名省略->当给所有列添加数据的时候
- 此时列值的顺序按照数据表中列的顺序执行
- 同时添加多行
insert into student(stuname,stuage,stusex,birthday)
values('张三3',18,'a','2000-1-1'),
('张三4',18,'a','2000-1-1'),
('张三5',18,'a','2000-1-1'),
('张三6',18,'a','2000-1-1'),
('张三7',18,'a','2000-1-1'),
('张三8',18,'a','2000-1-1');
- 列名与列值的类型、个数、顺序要一一对应。
- 参数值不要超出列定义的长度。
- 如果插入空值,请使用null
- 插入的日期和字符一样,都使用引号括起来。
create table emp(
id int primary key,
name varchar(100) not null,
gender varchar(10) not null,
birthday date,
salary float(10,2),
entry_date date,
resume text
);
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(1,'zhangsan','female','1990-5-10',10000,'2015-5-5-','goodgirl');
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(2,'lisi','male','1995-5-10',10000,'2015-5-5','good boy');
INSERT INTO emp(id,name,gender,birthday,salary,entry_date,resume)
VALUES(3,'你好','male','1995-5-10',10000,'2015-5-5','good boy');
- 算术运算符: +,-,*,/(除法),求余(%)
- 赋值运算符:=
- 逻辑运算符:
- 关系运算符:
- DELETE 删除表中的数据,表结构还在;删除后的数据可以找回
- TRUNCATE 删除是把表直接DROP掉,然后再创建一个同样的新表。
- 删除的数据不能找回。执行速度比DELETE快。
为空的条件:列名 is null or 列名 =''注 : 两个单引号表示空字符串日期类型值的区别 :
- date:yyyy-MM-dd (年月日)
- time:hh:mm:ss (时分秒)
- datetime:yyyy-MM-dd hh:mm:ss (年月日时分秒)
select now();
2.6 DCL
create user test123@localhost IDENTIFIED by 'test123'
create user test456@10.4.10.18 IDENTIFIED by 'test456'
create user test7@'%' IDENTIFIED by 'test7'
grant select,insert,update,delete,create on chaoshi.* to 'test456'@'127.0.0.1';
grant all on *.* to 'test456'@'127.0.0.1'
show grants for 'root'@'%';
REVOKE SELECT ON *.* FROM 'root'@'%' ;
drop user test123@localhost;
3、DQL数据查询
SELECT 要查询的列名称FROM 表名称WHERE 限定条件 /* 行条件 */GROUP BY grouping_columns /* 对结果分组 */HAVING condition /* 分组后的行条件 */ORDER BY sorting_columns /* 对结果分组 */LIMIT offset_start, row_count /* 结果限定 */
3.1 简单查询
查询所有列
SELECT * FROM stu;
SELECT sid, sname, age FROM stu;
3.2 条件查询
= 、 != 、 <> 、 < 、 <= 、 > 、 >=; BETWEEN…AND ; IN(set) ; IS NULL ; AND ; OR ; NOT ;
3.3 模糊查询
3.4 字段控制查询
3.5 排序
3.6 聚合函数
- COUNT(列名):统计指定列不为NULL的记录行数;
- MAX(列名):计算指定列的最大值,如果指定列是字符串类型,那么使用字符串排序运算;
- MIN(列名):计算指定列的最小值,如果指定列是字符串类型,那么使用字符串排序运算;
- SUM(列名):计算指定列的数值和,如果指定列类型不是数值类型,那么计算结果为0;
- AVG(列名):计算指定列的平均值,如果指定列类型不是数值类型,那么计算结果为0;
3.7 分组查询
3.7.2 HAVING子句
3.8 LIMIT
3.8.1 分页查询
二、数据库高级
1.1 数据库的完整性
- 实体完整性(行完整性)
- 域完整性(列完整性)
- 引用完整性(关联表完整性、参照完整性)
主键约束: primary key唯一约束: unique [key]非空约束: not null默认约束: default自动增长: auto_increment外键约束 : foreign key
create table student(
studentno int primary key auto_increment,
loginPwd varchar(20) not null default '123456',
studentname varchar(50) not null,
sex char(2) not null,
gradeid int not null,
phone varchar(255) not null,
address varchar(255) default '学生宿舍',
borndate datetime,
email varchar(50) );
1.2 实体完整性
约束类型:
- 主键约束(primary key) *
- 唯一约束unique)*
- 自动增长列auto_ increment)*
- sqlserver数据库 (identity-标识列)
- oracle数据库(sequence-序列)
1.2 域完整性
- 域完整性的作用:限制此单元格的数据正确,不对照此列的其它单元格比较
- 域代表当前单元格
- 数据类型
- 非空约束(not null)
- 默认值约束(default)
- check约束(mysql不支持)
- check(sex='男' or sex='女')
①数值类型:
- 表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
- 每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
- TIMESTAMP类型有专有的自动更新特性
CREATE TABLE student(
Id int primary key,
Name varchar(50) not null,
Sex varchar(10) );
INSERT INTO student values(1,’tom’,null);
1.4 引用完整性(参照完整性)
CREATE TABLE student(
id int primary key,
name varchar(50) not null,
sex varchar(10) default '男' );
create table score(
id int primary key,
score int,
sid int ,
constraint fk_score_sid foreign key(sid) references student(id) );
ALTER TABLE score ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES student(id);
2、多表查询
2.1 多表的关系
2.2 多表查询
注意:被合并的两个结果:列数、列类型必须相同。
使用主外键关系做为条件来去除无用信息
select 列名 from 表 1inner join 表 2 on 表 1. 列名 = 表 2. 列名inner join 表 3 on 表 1 或表 2. 列名 = 表 3. 列名 where
select 列名 from 表 1, 表 2, 表 3where 表 1. 列名 = 表 2. 列名 and 表 1/ 表 2. 列名 = 表 3. 列名
内连接的特点:查询结果必须满足条件。
- 包括左外连接和右外连接,
- 外连接的特点:查询出的结果存在不满足条件的可能。
- 连接不限于两张表,连接查询也可以是三张、四张,甚至N张表的连接查询。
- 通常连接查询不可能需要整个笛卡尔积,而只是需要其中一部分,那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。
- 两张表的连接查询一定有一个主外键关系,三张表的连接查询就一定有两个主外键关系,所以在大家不是很熟悉连接查询时,首先要学会去除无用笛卡尔积,那么就是用主外键关系作为条件来处理。如果两张表的查询,那么至少有一个主外键条件,三张表连接至少有两个主外键条件。
- 一个select语句中包含另一个完整的select语句。
- 子查询就是嵌套查询,即SELECT中包含SELECT,如果一条语句中存在两个,或两个以上SELECT,那么就是子查询语句了。
- where后,作为条为被查询的一条件的一部分;
- from后,作表;
- any
- all
- 单行单列(用于条件)
- 单行多列(用于条件)
- 多行单列(用于条件)
- 多行多列(用于表)
3、扩展
3.1 多行新增
insert into 表名 ( 列名 ) values ( 列值 ),( 列值 ),( 列值);
3.2 多表更新
update 表 1, 表 2 set 列名 = 列值 where 表 1. 列名 = 表 2. 列名 and 其他限定条件
update 表 1inner join 表 2 on 表 1. 列名 = 表 2. 列名set 列名 = 列值where 限定条件
set title='助工',salary=1000
3.3 多表删除
delete 被删除数据的表 from 删除操作中使用的表where 限定条件
// 删除人事部的信息delete d,e,s from department d,employee e,salary swhere d.depid=e.depid and s.empid=e.empid and depname='人事部 '
4.4 日期运算函数
- now() 获得当前系统时间
- year(日期值) 获得日期值中的年份
- date_add(日期,interval 计算值 计算的字段);
date_add(now(),interval -40 year);//40 年前的日期
3、数据库优化
- 对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
- 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null 。最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库. 备注、描述、评论之类的可以设置为 NULL,其他的,最好不要使用NULL。
- 应尽量避免在 where 子句中使用 != 或 <> 操作符,否则引擎将放弃使用索引而进行全表扫描。
- 应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or Name = 'admin
select id from t where num = 10union allselect id from t where Name = 'admin'
5. in 和 not in 也要慎用,否则会导致全表扫描,如:
三、数据库进阶
1、事务
1.1 事务的语法
- start transaction; begin;
- commit; 使得当前的修改确认
- rollback; 使得当前的修改被放弃
1.2 事务的ACID特性
- 全部执行成功
- 全部执行失败
1.3 事务的并发问题
1.4 事务的隔离级别
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.93 sec)
事务A其实除了了查询两次以外,其它什什么事情都没做,结果钱就从1000变成0了了,这就是不不可重复读的问题。
1.5 不同隔离级别的锁的情况
1.6 隐式提交
四、JDBC
1、简介
- 连接数据库。
- 创建SQL或MySQL语句。
- 在数据库中执行SQL或MySQL查询。
- 查看和修改生成的记录
2、JDBC体系结构
- JDBC API:这提供了应用程序到JDBC管理器连接。
- JDBC驱动程序API:这支持JDBC管理器到驱动程序连接。
3、JDBC核心组件
- DriverManager: 此类管理数据库驱动程序列表。使用通信子协议将来自java应用程序的连接请求与适当的数据库驱动程序匹配。
- Driver:此接口处理与数据库服务器的通信,我们很少会直接与Driver对象进行交互。而是使用 DriverManager对象来管理这种类型的对象。
- Connection:该界面具有用于联系数据库的所有方法。连接对象表示通信上下文,即,与数据库的所有通信仅通过连接对象。
- Statement:使用从此接口创建的对象将SQL语句提交到数据库。除了执行存储过程之外,一些派生接口还接受参数。
- ResultSet:在使用Statement对象执行SQL查询后,这些对象保存从数据库检索的数据。它作为一个迭代器,允许我们移动其数据。
- SQLException:此类处理数据库应用程序中发生的任何错误
4、CRUD语法介绍
SQL> CREATE DATABASE DATABASE_NAME;
SQL> DROP DATABASE DATABASE_NAME;
SQL> CREATE TABLE Employees (id INT NOT NULL,age INT NOT NULL,first VARCHAR(255),last VARCHAR(255),PRIMARY KEY ( id ));
④DROP TABLE语句用于删除现有表。
SQL> DROP TABLE table_name;
SQL> INSERT INTO table_name VALUES (column1, column2, ...);
SQL> SELECT column_name, column_name, ... FROM table_name WHERE conditions;
SQL> UPDATE table_nameSET column_name = value, column_name = value, ...WHERE conditions;
SQL> DELETE FROM table_name WHERE conditions;
WHERE子句可以使用比较运算符,例如=,!=,<,>,<=和> =,以及BETWEEN和LIKE运算符。
5、使用步骤
- 导入包:需要包含数据库编程所需的JDBC类的包。大多数情况下,使用import java.sql.*就足够了。
- 注册JDBC驱动程序:要求您初始化驱动程序,以便您可以打开与数据库的通信通道。
- 打开连接:需要使用DriverManager.getConnection()方法创建一个Connection对象,该对象表示与数据库的物理连接。
- 执行查询:需要使用类型为Statement的对象来构建和提交SQL语句到数据库。
- 从结果集中提取数据:需要使用相应的ResultSet.getXXX()方法从结果集中检索数据。
- 释放资源:需要明确地关闭所有数据库资源,而不依赖于JVM的垃圾收集。
6、JDBC连接步骤
- 导入JDBC包:将Java语言的*import*语句添加到Java代码中导入所需的类。
- 注册JDBC驱动程序:此步骤将使JVM将所需的驱动程序实现加载到内存中,以便它可以满足您的JDBC请求。
- 数据库URL配置:这是为了创建一个格式正确的地址,指向要连接到的数据库。
- 创建连接对象:最后,调用DriverManager对象的getConnection()方法来建立实际的数据库连接。
①注册JDBC驱动程序
Class.forName(); 注册驱动程序最常见的方法是使用Java的Class.forName()方法,将驱动程序的类文件动态加载到内存中,并将其自动注册
try {
Class.forName("com.mysql.cj.jdbc.Driver");
}catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}
try {
Driver myDriver = new com.mysql.cj.jdbc.Driver();
DriverManager.registerDriver( myDriver );
}catch(ClassNotFoundException ex) {
System.out.println("Error: unable to load driver class!");
System.exit(1);
}
- getConnection(String url)
- getConnection(String url,Properties prop)
- getConnection(String url,String user,String password)
③创建数据库连接对象
String URL = "jdbc:mysql://localhost:3306/数据库名?serverTimezone=UTC";
String USER = "username";
String PASS = "password";
Connection conn = DriverManager.getConnection(URL, USER, PASS);
jdbc:mysql://localhost:3306/数据库名?
useSSL=false&useUnicode=true&characterEncoding=UTF-8
版本2:
jdbc:mysql://localhost:3306/数据库名?serverTimezone=UTC
DriverManager.getConnection(String url, Properties info);
import java.util.*; String URL = "jdbc:mysql://localhost:3306/yhp2?serverTimezone=UTC";
Properties info = new Properties( );
info.put( "user", "username" );
info.put( "password", "password" );
Connection conn = DriverManager.getConnection(URL, info);
com.close();
6.1 JDBC执行SQL语句
接口 | 推荐使用 |
声明 |
用于对数据库进行通用访问。在运行时使用静态
SQL
语句时很有用。
Statement
接口不能接受参数。
|
PreparedStatement的 | 当您计划多次使用SQL语句时使用。PreparedStatement接口在运行时接受输入参数。 |
6.2 Statement
Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}catch (SQLException e) {
. . .
}finally {
. . .
}
- boolean execute(String SQL):如果可以检索到ResultSet对象,则返回一个布尔值true; 否则返回false。使用此方法执行SQL DDL语句或需要使用真正的动态SQL时。
- int executeUpdate(String SQL):返回受SQL语句执行影响的行数。使用此方法执行预期会影响多个行的SQL语句,例如INSERT,UPDATE或DELETE语句。
- ResultSet executeQuery(String SQL):返回一个ResultSet对象。当您希望获得结果集时,请使用此方法,就像使用SELECT语句一样。
Statement stmt = null;
try {
stmt = conn.createStatement( );
. . .
}catch (SQLException e) {
. . .
}finally {
stmt.close();
}
6.3 SQL注入
String username ="admin";
String password=" 'abc' or 1=1 ";
String sql="select * from users where username= '"+username+"' and password= "+password;
7、PreparedStatement(预状态通道)
PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.prepareStatement(SQL);
. . .
}catch (SQLException e) {
. . .
}finally {
. . .
}
PreparedStatement pstmt = null;
try {
String SQL = "Update Employees SET age = ? WHERE id = ?";
pstmt = conn.preparedStatement(SQL);
. . .
}catch (SQLException e) {
. . .
}finally {
pstmt.close();
}
实例:
public class JDBCTest {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement pps = null;
ResultSet resultset = null;
int result = 0;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获得连接
String username = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC";
connection = DriverManager.getConnection(url,username,password);
//3.定义sql创建预状态通道(进行sql的发送)
String sql = "select * from employee where name=? and title=?";
pps = connection.prepareStatement(sql);
pps.setString(1,"李四");
pps.setString(2,"工程师");
resultset = pps.executeQuery();
//4.取出结果集信息
while(resultset.next()){
//取出数据
String name = resultset.getString("name");
String sex = resultset.getString("sex");
System.out.println("姓名:"+name+",性别:"+sex);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if(resultset != null) {
//关闭数据库连接
resultset.close();
}
if(pps != null){
pps.close();
}
if(connection != null){
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
8、 ResultSet
try {
Statement stmt = conn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
}catch(Exception ex) {
....
}finally {
....
}
实例:
import java.sql.*;
/**
* @Author: xuliushen
* @Description:
* @Date Created in 2021-08-16 10:02
* @Modified by :
*/
public class JDBCTest {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultset = null;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获得连接
String username = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC";
connection = DriverManager.getConnection(url,username,password);
//3.定义sql创建状态通道(进行sql的发送)
statement = connection.createStatement();
resultset = statement.executeQuery("select * from employee");
//4.取出结果集信息
while(resultset.next()){
//取出数据
String name = resultset.getString("name");
System.out.println(name);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
try {
if(resultset != null) {
//关闭数据库连接
resultset.close();
}
if(statement != null){
statement.close();
}
if(connection != null){
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
9、Java操作两表关系
10、数据库事务
10.1 事务概述
- 连接到数据库上,并执行一条DML语句insert、update或delete
- 前一个事务结束后,又输入了另一条DML语句
- 执行commit或rollback语句。
- 执行一条DDL语句,例如create table语句,在这种情况下,会自动执行commit语句。
- 执行一条DCL语句,例如grant语句,在这种情况下,会自动执行commit。
- 断开与数据库的连接。
- 执行了一条DML语句,该语句却失败了,在这种情况中,会为这个无效的DML语句执行rollback语句。
10.2 事务的四大特点 (ACID)
10.3 JDBC中事务应用
10.4 事务的提交和回滚
conn.commit( );
conn.rollback( );
try{
//Assume a valid connection object conn
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
String SQL = "INSERT INTO Employees values (106, 20, 'Rita', 'Tez')";
stmt.executeUpdate(SQL);
//Submit a malformed SQL statement that breaks
String SQL = "INSERTED IN Employees VALUES (107, 22, 'Sita', 'Singh')";
stmt.executeUpdate(SQL);
// If there is no error.
conn.commit();
}catch(SQLException se){
// If there is any error.
conn.rollback();
}
10.5 Savepoints
- setSavepoint(String savepointName):定义新的保存点。它还返回一个Savepoint对象。
- releaseSavepoint(Savepoint savepointName):删除保存点。请注意,它需要一个Savepoint对象作为参数。此对象通常是由setSavepoint()方法生成的保存点。
try{
//Assume a valid connection object conn
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
String SQL = "INSERT INTO Employees VALUES (106, 20, 'Rita', 'Tez')";
stmt.executeUpdate(SQL);
String SQL = "INSERTED IN Employees VALUES (107, 22, 'Sita', 'Tez')";
stmt.executeUpdate(SQL);
conn.commit();
}catch(SQLException se){
conn.rollback(savepoint1);
}
try {
connection.setAutoCommit(false);
add(connection);
// int i = 1/0;
sub(connection);
System.out.println("===============");
connection.commit();
} catch (Exception e) {
// TODO Auto-generated catch block
System.out.println("---------------");
try {
connection.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
11 、JDBC批处理
11.1 Statement批处理
- - 使用createStatement()方法创建Statement对象。
- - 使用setAutoCommit()将auto-commit设置为false 。
- - 使用addBatch()方法在创建的语句对象上添加您喜欢的SQL语句到批处理中。
- - 在创建的语句对象上使用executeBatch()方法执行所有SQL语句。
- - 最后,使用commit()方法提交所有更改。
Statement stmt = conn.createStatement();
conn.setAutoCommit(false);
//sql1
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(200,'Zia',
'Ali', 30)";
stmt.addBatch(SQL);
//sql2
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(201,'Raj',
'Kumar', 35)";
stmt.addBatch(SQL);
//sql3
String SQL = "UPDATE Employees SET age = 35 WHERE id = 100";
stmt.addBatch(SQL);
int[] count = stmt.executeBatch();
conn.commit();
11.2 PreparedStatement批处理
1. 使用占位符创建 SQL 语句。2. 使用 prepareStatement () 方法创建 PrepareStatement 对象。3. 使用 setAutoCommit () 将 auto-commit 设置为 false 。4. 使用 addBatch () 方法在创建的语句对象上添加您喜欢的 SQL 语句到批处理中。5. 在创建的语句对象上使用 executeBatch () 方法执行所有 SQL 语句。6. 最后,使用 commit () 方法提交所有更改。
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(SQL);
conn.setAutoCommit(false);
// Set the variables
pstmt.setInt( 1, 400 );
pstmt.setString( 2, "Pappu" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 33 );
// Add it to the batch
pstmt.addBatch();
// Set the variables
pstmt.setInt( 1, 401 );
pstmt.setString( 2, "Pawan" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 31 );
// Add it to the batch
pstmt.addBatch();
//add more batches
//Create an int[] to hold returned values
int[] count = stmt.executeBatch();
//Explicitly commit statements to apply changes
conn.commit();
12、反射处理结果集
之前我们从数据库获取对象的的写法:
- 获取固定的表的数据,从结果集中依次拿出每一列的数据对对象进行赋值
- 当列数很多的时候,这种方法并不方便
使用反射处理结果集:
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @Author: xuliushen
* @Description:
* @Date Created in 2021-08-20 8:30
* @Modified by :
*/
public class StudentDaoImpl2 implements StudentDao{
@Override
public List<Student> getAllStudent(Class c ) {
Connection connection = null;
PreparedStatement pps = null;
ResultSet result = null;
try {
//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.获得连接
String username = "root";
String password = "123456";
String url = "jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC";
connection = DriverManager.getConnection(url,username,password);
//3.定义sql,创建预状态通道
String sql = "select * from student";
pps = connection.prepareStatement(sql);
//4.执行sql
result = pps.executeQuery();
List students = new ArrayList();
//1.得到数据库查询结果信息
ResultSetMetaData metaData = result.getMetaData();//存储结果集信息
int columnCount = metaData.getColumnCount();//得到列数
String[] columnNames = new String[columnCount];
for (int i=0;i<columnCount;i++){
columnNames[i] = metaData.getColumnName(i+1);//列数从1开始
System.out.println("columnName="+columnNames[i]);
}
//得到类中所有方法
Method []declaredMethods = c.getDeclaredMethods();
while(result.next()){
Object student = c.newInstance();
for (String columnName:columnNames) {
String methodName = "set"+columnName;
for (Method declaredmethod:declaredMethods) {
if(declaredmethod.getName().equalsIgnoreCase(methodName)){
declaredmethod.invoke(student,result.getObject(columnName));
break;
}
}
}
students.add(student);
}
return students;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} finally {
try {
if(result != null) {
result.close();
}
if(pps != null){
pps.close();
}
if(connection != null){
connection.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
return null;
}
}
13、工具类的定义
把操作数据库过程中重复使用到的代码封装起来(其中方法的权限修饰符都是protected,因此Dao类可以通过继承该工具类而直接调用其中的方法)
import java.sql.*;
import java.util.List;
/**
* @Author: xuliushen
* @Description:
* @Date Created in 2021-08-20 9:24
* @Modified by :
*/
public class DBUtils {
//1.定义变量
private Connection connection = null;
private PreparedStatement pps = null;
private ResultSet resultSet = null;
private int count;//存储受影响的行数
private String username = "root";
private String userPass = "123456";
private String url = "jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC";
//2.加载驱动
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//3.获得链接
protected Connection getConnection(){
try {
connection = DriverManager.getConnection(url,username,userPass);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
//4.获得预状态通道
protected PreparedStatement getPps(String sql){
try {
pps = getConnection().prepareStatement(sql);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return pps;
}
//5.绑定参数 list保存的十给占位符所赋的值
protected void param(List list){
try {
if(list!=null && list.size()>0){
for (int i = 0; i < list.size(); i++) {
pps.setObject(i+1,list.get(i));
}
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
//6.执行操作(增删改、查询)
protected int update(String sql,List list){
try {
getPps(sql);
param(list);
count = pps.executeUpdate();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return count;
}
//7.查询
protected ResultSet query(String sql,List list){
try {
getPps(sql);
param(list);
resultSet = pps.executeQuery();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return resultSet;
}
//8.关闭资源
protected void closeAll(){
try {
if(connection != null){
connection.close();
}
if(pps != null){
pps.close();
}
if(resultSet != null){
resultSet.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
StudentDao:
import JDBC.util.DBUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @Author: xuliushen
* @Description:
* @Date Created in 2021-08-20 8:30
* @Modified by :
*/
public class StudentDaoImpl2 extends DBUtils implements StudentDao{
@Override
public Student getByStuid(int id) {
Student student = new Student();
try {
String sql = "select * from student where stuid=?";
List list = new ArrayList();
list.add(id);
ResultSet rs = query(sql,list);
while(rs.next()){
student.setStuid(rs.getInt("stuid"));
student.setStuname(rs.getString("stuname"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
closeAll();
}
return student;
}
}
属性文件:properties
方式一:
方式二:
14、连接池
14.1 自定义连接池
- 在系统初始化的时候,将数据库连接作为对象存储在内存中。
- 当用户需要访问数据库时,并非建立一个新的连接,而是从连接池中取出一个已建立的空闲连接对象。
- 使用完毕后,用户也并非将连接关闭,而是将连接放回连接池中,以供下一个请求访问使用。
- 连接的建立、断开都由连接池自身来管理。
- 可以通过设置连接池的参数来控制连接池中的初始连接数、连接的上下限数以及每个连接的最大使用次数、最大空闲时间等等。
- 可以通过其自身的管理机制来监视数据库连接的数量、使用情况等。
- 获取连接方法
- 回收连接方法
public class Pool{
static LinkedList<Connection> list = new LinkedList<Connection>();
static{
for (int i = 0; i < 10; i++) {
Connection connection = JDBCUtils.newInstance().getConnection();
list.add(connection); } }/*** 从连接池子中获取连接的方式 * @return */
public static Connection getConnection(){
if (list.isEmpty()) {
//JDBCUtils类是自定义类,封装了连接数据库的信息代码
Connection connection = JDBCUtils.newInstance().getConnection();
list.addLast(connection);
}
Connection conn = list.removeFirst();
return conn; }
/*** 返回到连接池子中 */
public static void addBack(Connection conn){
if (list.size() >= 10) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else{
list.addLast(conn); //10 }
}
/*** 获取连接池子中连接数量的方法 */
public static int getSize(){
return list.size();
}
}
public class MyConnection implements Connection{
//将被装饰者导入
private Connection conn;
private LinkedList<Connection> list;
public MyConnection(Connection conn, LinkedList<Connection> list) {
super();
this.conn = conn;
this.list = list;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return conn.unwrap(iface);
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return conn.isWrapperFor(iface);
}
@Override
public Statement createStatement() throws SQLException {
return conn.createStatement();
}
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
return conn.prepareStatement(sql);
}
@Override
public CallableStatement prepareCall(String sql) throws SQLException {
return null;
}
@Override
public String nativeSQL(String sql) throws SQLException {
return null;
}
@Override
public void setAutoCommit(boolean autoCommit) throws SQLException {
}
@Override
public boolean getAutoCommit() throws SQLException {
return false;
}
@Override
public void commit() throws SQLException {
conn.commit();
}
@Override
public void rollback() throws SQLException {
conn.rollback();
}
@Override
public void close() throws SQLException {
list.addLast(conn);
}
...
}
/**
* 创建一个规范的连接池子
*/
public class DataSourcePool implements DataSource{
static LinkedList<Connection> list = new LinkedList<Connection>();
static{
for (int i = 0; i < 10; i++) {
Connection connection = JDBCUtils.newInstance().getConnection();
list.add(connection);
}
}
public static int getSize(){
return list.size();
}
@Override
public Connection getConnection() throws SQLException {
Connection conn = list.removeFirst();
MyConnection conn1 = new MyConnection(conn, list);
return conn1;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
return null;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
}
@Override
public int getLoginTimeout() throws SQLException {
return 0;
}
@Override
public Logger getParentLogger() throws SQLFeatureNotSupportedException {
return null;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
return false;
}
@Override
public Connection getConnection(String username, String password) throws
SQLException {
return null;
}
}
14.2 DBCP连接池
- mysql-jdbc.jar
- commons-dbcp.jar
- commons-pool.jar
- 所谓的硬编码方式就是在代码中添加配置
@Test
public void testHard() throws SQLException{
// 硬编码 使用DBCP连接池子
BasicDataSource source = new BasicDataSource();
//设置连接的信息
source.setDriverClassName("com.mysql.jdbc.Driver");
source.setUrl("jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC");
source.setUsername("root");
source.setPassword("123456");
Connection connection = source.getConnection();
String sql = "select * from student";
Statement createStatement = connection.createStatement();
ResultSet executeQuery = createStatement.executeQuery(sql);
while (executeQuery.next()) {
System.out.println(executeQuery.getString(2));
}
connection.close(); //回收
}
- 所谓的软编码,就是在项目中添加配置文件,这样就不需要每次代码中添加配合!
#连接设置driverclass=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTCusername=rootuserPass=123456#<!-- 初始化连接 -->initialSize=20# 最大连接数量maxActive=50#<!-- 最大空闲连接 -->maxIdle=20#<!-- 最小空闲连接 -->minIdle=5#<!-- 超时等待时间以毫秒为单位 6000 毫秒 /1000 等于 60 秒 -->maxWait=6000
②代码中读取配置文件
static {
//DBCP
ResourceBundle bundle = ResourceBundle.getBundle("db");
driverName = bundle.getString("driverclass");
username = bundle.getString("username");
userPass = bundle.getString("userPass");
url = bundle.getString("url");
basicDataSource = new BasicDataSource();
basicDataSource.setUsername(username);
basicDataSource.setPassword(userPass);
basicDataSource.setUrl(url);
basicDataSource.setDriverClassName(driverName);
basicDataSource.setInitialSize(20);
}
//3.获得链接
protected Connection getConnection(){
try {
//DBCP
connection = basicDataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
14.3 C3P0连接池
- dbcp没有自动回收空闲连接的功能
- c3p0有自动回收空闲连接功能
- dbcp需要手动设置配置文件
- c3p0不需要手动设置
src/c3p0-config.xml (文件名固定)ComboPooledDataSource cpds = new ComboPooledDataSource();加载 文件中 <default-config> 中的配置ComboPooledDataSource cpds = new ComboPooledDataSource("aaa");加载 <named-config name="aaa"> 中的配置
<?xml version="1.0" encoding="utf-8"?>
<c3p0-config>
<!-- 默认配置,如果没有指定则使用这个配置 -->
<default-config>
<!-- 基本配置 -->
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/kaikeba?serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<!--扩展配置-->
<!-- 连接超过30秒报错-->
<property name="checkoutTimeout">30000</property>
<!--30秒检查空闲连接 -->
<property name="idleConnectionTestPeriod">30</property>
<property name="initialPoolSize">10</property>
<!-- 30秒不适用丢弃-->
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
</c3p0-config>
代码:
static {
//C3P0
comboPooledDataSource = new ComboPooledDataSource();
}
//3.获得链接
protected Connection getConnection(){
try {
//C3P0
connection = comboPooledDataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}
14.3 Druid连接池
static {
//德鲁伊
ResourceBundle bundle = ResourceBundle.getBundle("db");
driverName = bundle.getString("driverclass");
username = bundle.getString("username");
userPass = bundle.getString("userPass");
url = bundle.getString("url");
druidDataSource = new DruidDataSource();
druidDataSource.setUsername(username);
druidDataSource.setPassword(userPass);
druidDataSource.setUrl(url);
druidDataSource.setDriverClassName(driverName);
}
//3.获得链接
protected Connection getConnection(){
try {
//德鲁伊
connection = druidDataSource.getConnection();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return connection;
}