文章目录
MySQL高级+JDBC实战
一、数据库的完整性
数据完整性（数据的准确性）
作用：保证用户输入的数据保存到数据库中是正确的。
确保数据的完整性=在建表时给表中添加约束
1.1 实体完整性
实体：即表中的一行代表一个实体
实体完整性的作用：表示每一行数据不重复。
所有的约束在创建表的时候就应该指定。
1.1.1 主键约束（primary key）
每个表中要有一个主键，即每张表都需要创建一列主键。
特点：数据唯一（不重复），且不能为null。
主键列一般会选择 int 类型。
主键=唯一约束+非空约束（从功能上来说）
1.1.2 自增约束（auto_increment)
应用在int列上。
表示数值的自动增长。
经常和主键列一起使用。
1.1.3 唯一约束（unique）
不允许重复，但是允许为空。
1.2 域完整性
作用：限制单元格的数据正确，不对照此列的其他单元格比较。
域代表当前单元格
域完整性约束：数据类型 非空约束(not null) 默认值约束(default)
1.2.1 数据类型
1.2.2 非空约束（not null）
该列必须要有值。
1.2.3 默认值约束（default）
当用户不指定该列的值的时候，会按照默认值给定。
1.3 引用完整性
1.3.1 外键约束
基于多表的基础之上实现的。
为了保证表与表之间的准确的引用关系。
外键约束应该添加到外键表中。
实际开发中为了保证数据库中的效率，有些公司会要求不创建外键约束，但是会保存外键关系。
二、JDBC
2.1 JDBC简介
java database connectivity 是一种用于执行SQL语句的java API，可以为多种关系型数据库提供统一访问，由一组用java语言编写的类和接口组成。
2.2 JDBC架构
2.3 JDBC核心组件
DriverManager：此类管理数据库驱动程序列表。使用通信子协议将来自java应用程序的连接请求与适当的数据库驱动程序匹配。
Driver：此接口处理与数据库服务器的通信，我们很少直接与Driver对象进行交互，而是使用DriverManager对象来管理这种类型的对象。
Connection：该界面具有用于联系数据库的所有方法。连接对象表示通信上下文，即与数据库的所有通信仅通过连接对象。
Statement：使用此接口创建的对象将SQL语句提交到数据库。除执行存储过程之外，一些派生接口还接受参数。
ResultSet：在使用Statement对象执行SQL查询之后，这些对象保存从数据库检索的数据。它作为一个迭代器，允许我们移动其数据。
SQLException：此类处理数据库应用程序中发生的任何错误。
2.4 整体使用步骤
注意：jar包的版本和数据库的版本应当匹配
构建JDBC应用程序涉及以下6个步骤：
- 导入包：需要包含数据库编程所需要的JDBC类的包。大多数情况下，使用 import java.sql就足够了。
- 注册JDBC驱动程序：初始化驱动程序，以便可以打开与数据库的通信通道。
- 打开连接：需要使用DriverManager.getConnection()方法创建一个Connection对象，该对象表示与数据库的物理连接。
- 执行查询：需要使用类型为Statement/PrepareStatement的对象来构建和提交SQL语句到数据库。
- 从结果集中提取数据：需要使用相应的ResultSet.getXXX()方法从结果集中检索数据。
- 释放资源：需要明确地关闭所有数据库资源，而不是依赖于jvm的垃圾收集。
实例:
public static void select() {
// JDBC操作数据库-查询全部
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try{
// 1.加载驱动 8.0 com.mysql.cj.jdbc.Driver 5.1 com.mysql.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.获得连接
// url = "jdbc:mysql://IP:端口号/数据库名?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String username = "root";
String password = "123456";
connection = DriverManager.getConnection(url, username, password);
// 3.得到状态通道-建立java和数据库之间的一个SQL通道
statement = connection.createStatement();
String sql = "select * from mystudent";
// 执行查询：executeQuery()
// resultSet 结果集-查询的结果(虚拟表)
// 4.执行SQL，得到返回结果
resultSet = statement.executeQuery(sql);
// 5.解析结果集
while(resultSet.next()) {
int stuno = resultSet.getInt("stuno"); // resultSet.getXXX("列名")
String stuname = resultSet.getString("stuname");
Date birthday = resultSet.getDate("birthday");
System.out.println("stuno:" + stuno + ",stuname:" + stuname + ",birthday:" + birthday);
}
} catch (Exception e) {
System.out.println("有异常");
} finally {
// 6.关闭连接 Connection、Statement、ResultSet
try {
// 安全
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
}
// PrepareStatement预编译SQL防止SQL注入（可用于用户登录时验证）
public static void select2(String uname, String upass) {
// JDBC操作数据库-查询全部
Connection connection = null;
PrepareStatement pps = null;
ResultSet resultSet = null;
try{
// 1.加载驱动 8.0 com.mysql.cj.jdbc.Driver 5.1 com.mysql.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.获得连接
// url = "jdbc:mysql://IP:端口号/数据库名?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String username = "root";
String password = "123456";
connection = DriverManager.getConnection(url, username, password);
// 3.得到状态通道-建立java和数据库之间的一个SQL通道--提前编译sql
// ? 表示占位
String sql = "select * from student where stuname=? and password=?";
pps = connection.prepareStatement(sql);
// 给占位符赋值
pps.setString(1, uname);
pps.setString(2, upass);
// 执行查询：executeQuery()
// resultSet 结果集-查询的结果(虚拟表)
// 4.执行SQL，得到返回结果
resultSet = pps.executeQuery();
// 5.解析结果集
if (resultSet.next()) {
System.out.println("登录成功");
} else {
System.out.println("登陆失败");
}
} catch (Exception e) {
System.out.println("有异常");
} finally {
// 6.关闭连接 Connection、Statement、ResultSet
try {
// 安全
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
}
public static void insert() {
Connection connection = null;
Statement statement = null;
//ResultSet resultSet = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
connection = DriverManager.getConnection(url, "root", "123456");
statement = connection.createStatement();
String sql = "insert into student(stuid,stuname,stusex) values(null,'刘楠楠','女')";
// 增删改用 executeUpdate()，返回的结果为受影响的行数
int count = statement.executeUpdate(sql);
System.out.println(count > 0 ? "新增成功" : "新增失败");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
// 安全
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
}
public static void update() {
Connection connection = null;
Statement statement = null;
//ResultSet resultSet = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
connection = DriverManager.getConnection(url, "root", "123456");
statement = connection.createStatement();
String sql = "update student set stuage=18 where stuname='刘楠楠'";
// 增删改用 executeUpdate()，返回的结果为受影响的行数
int count = statement.executeUpdate(sql);
System.out.println(count > 0 ? "更新成功" : "更新失败");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
// 安全
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
}
public static void delete() {
Connection connection = null;
Statement statement = null;
//ResultSet resultSet = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
connection = DriverManager.getConnection(url, "root", "123456");
statement = connection.createStatement();
String sql = "delete from student where stuid=10";
// 增删改用 executeUpdate()，返回的结果为受影响的行数
int count = statement.executeUpdate(sql);
System.out.println(count > 0 ? "删除成功" : "删除失败");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
try {
// 安全
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
}
public static void main(String[] args) {
select();
insert();
update();
delete();
}
2.5 保存数据库中返回的结果数据(使用实体类实现数据封装)
public static List<Student> select2(String uname, String upass) {
List<Student> list = new ArrayList<>();
// JDBC操作数据库-查询全部
Connection connection = null;
PrepareStatement pps = null;
ResultSet resultSet = null;
try{
// 1.加载驱动 8.0 com.mysql.cj.jdbc.Driver 5.1 com.mysql.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.获得连接
// url = "jdbc:mysql://IP:端口号/数据库名?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String username = "root";
String password = "123456";
connection = DriverManager.getConnection(url, username, password);
// 3.得到状态通道-建立java和数据库之间的一个SQL通道--提前编译sql
// ? 表示占位
String sql = "select * from student";
pps = connection.prepareStatement(sql);
// 执行查询：executeQuery()
// resultSet 结果集-查询的结果(虚拟表)
// 4.执行SQL，得到返回结果
resultSet = pps.executeQuery();
// 5.解析结果集
while (resultSet.next()) {
// 数据表-->类 列名-->属性名 ==>根据表结构，创建出同功能的类（实体类entity）
// 数据库中一行数据-->java中一个对象
// 此处类根据表自建
Student student = new Student();
/* int stuid = resultSet.getInt("stuid");
String stuname = resultSet.getString("stuname");
String stusex = resultSet.getString("stusex");
int stuage = resultSet.getInt("stuage");
int managerid = resultSet.getInt("managerid");
String password1 = resultSet.getString("password");
student.setStuId(stuid);
student.setStuName(stuname);
student.setStuSex(stusex);
student.setStuAge(stuage);
student.setManagerId(managerid);
student.setPassword(password1); */
student.setStuId(resultSet.getInt("stuid"));
student.setStuName(resultSet.getString("stuname"));
student.setStuSex(resultSet.getString("stusex"));
student.setStuAge(resultSet.getInt("stuage"));
student.setManagerId(resultSet.getInt("managerid"));
student.setPassword(resultSet.getString("password"));
// 保存结果
list.add(student);
}
} catch (Exception e) {
System.out.println("有异常");
} finally {
// 6.关闭连接 Connection、Statement、ResultSet
try {
// 安全
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
return list;
}
public static void main(String[] args) {
// 保存数据库中返回的结果数据
List<Student> students = select2();
for (Student student : students) {
System.out.println(student);
}
}
三、MySQL多表操作
3.1 数据表
-- ---------------------------- -- Table structure for `grade` -- ---------------------------- DROP TABLE IF EXISTS `grade`; CREATE TABLE `grade` ( `gid` int NOT NULL AUTO_INCREMENT, `gname` varchar(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`gid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Records of grade -- ---------------------------- INSERT INTO `grade` VALUES ('1', '一年级'); INSERT INTO `grade` VALUES ('2', '二年级'); INSERT INTO `grade` VALUES ('3', '三年级'); INSERT INTO `grade` VALUES ('4', '四年级'); -- ---------------------------- -- Table structure for `husband` -- ---------------------------- DROP TABLE IF EXISTS `husband`; CREATE TABLE `husband` ( `husid` int NOT NULL AUTO_INCREMENT, `husname` varchar(5) COLLATE utf8_bin DEFAULT NULL, `wifeid` int DEFAULT NULL, PRIMARY KEY (`husid`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Records of husband -- ---------------------------- INSERT INTO `husband` VALUES ('1', '邓超', '1'); INSERT INTO `husband` VALUES ('2', '刘备', '2'); -- ---------------------------- -- Table structure for `scores` -- ---------------------------- DROP TABLE IF EXISTS `scores`; CREATE TABLE `scores` ( `scoreid` int NOT NULL AUTO_INCREMENT, `score` double DEFAULT NULL, `stuid` int DEFAULT NULL, `subjectid` int DEFAULT NULL, PRIMARY KEY (`scoreid`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Records of scores -- ---------------------------- INSERT INTO `scores` VALUES ('1', '56', '1', '1'); INSERT INTO `scores` VALUES ('2', '78', '2', '1'); INSERT INTO `scores` VALUES ('3', '67', '3', '1'); INSERT INTO `scores` VALUES ('4', '77', '4', '1'); INSERT INTO `scores` VALUES ('5', '88', '5', '1'); INSERT INTO `scores` VALUES ('6', '99', '6', '1'); INSERT INTO `scores` VALUES ('7', '90', '6', '4'); -- ---------------------------- -- Table structure for `student` -- ---------------------------- DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `stuid` int NOT NULL AUTO_INCREMENT, `stuname` varchar(10) COLLATE utf8_bin DEFAULT NULL, `stusex` char(1) COLLATE utf8_bin DEFAULT NULL, `stuage` int DEFAULT NULL, `gradeid` int DEFAULT NULL, `managerid` int DEFAULT NULL COMMENT '班长的id', `password` varchar(255) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`stuid`), KEY `fk_student_gradeid` (`gradeid`), CONSTRAINT `fk_student_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gid`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Records of student -- ---------------------------- INSERT INTO `student` VALUES ('1', '张三', '女', '18', '1', '3', '8888'); INSERT INTO `student` VALUES ('2', '李四', '女', '20', '1', '3', '8888'); INSERT INTO `student` VALUES ('3', '王五', '女', '22', '1', '3', '8888'); INSERT INTO `student` VALUES ('4', '张杰', '男', '25', '2', '5', '8888'); INSERT INTO `student` VALUES ('5', '泰达', '男', '32', '2', '5', '8888'); INSERT INTO `student` VALUES ('6', '杨潇', '男', '18', '2', '5', '8888'); INSERT INTO `student` VALUES ('7', '刘yi良', '男', '18', null, null, null); -- ---------------------------- -- Table structure for `sub` -- ---------------------------- DROP TABLE IF EXISTS `sub`; CREATE TABLE `sub` ( `subid` int NOT NULL AUTO_INCREMENT, `subjectname` varchar(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`subid`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Records of sub -- ---------------------------- INSERT INTO `sub` VALUES ('1', '语文'); INSERT INTO `sub` VALUES ('2', '数学'); INSERT INTO `sub` VALUES ('3', '英语'); INSERT INTO `sub` VALUES ('4', '化学'); -- ---------------------------- -- Table structure for `wife` -- ---------------------------- DROP TABLE IF EXISTS `wife`; CREATE TABLE `wife` ( `wifeid` int NOT NULL AUTO_INCREMENT, `wifename` varchar(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`wifeid`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin; -- ---------------------------- -- Records of wife -- ---------------------------- INSERT INTO `wife` VALUES ('1', '孙俪'); INSERT INTO `wife` VALUES ('2', '孙尚香');
3.2 相关需求演示
# 查询id为1的学生信息(同时展示出年级信息)
select * from student where stuid=1;
select * from grade where gid=1;
# 多表联查--连接查询
# 1.内连接 2.外连接 3.自然连接
# 1.1 等值连接 非等值连接 自连接
# 2.1 左外连 右外连 全外连（mysql不支持）
# 1.内连接：将N张表中有关联关系的数据查询出来
# 第一种语法：
select 列名 from 表1,表2,表3
where 表1.列名=表2.列名（主外键关系） and 表1/表2.列名=表3.列名（主外键关系）
and 其他条件
select stuname,subjectname,score from sub su,student s,scores sc
where su.subid=sc.subjectid and s.stuid=sc.stuid;
# 第二种语法：
select 列名 from 表1 inner join 表2 on 表1.列名=表2.列名（主外键关系）
inner join 表3 on 表1/表2.列名=表3.列名（主外键关系）
where 其他条件
select stuname,subjectname,score
from sub su
inner join scores sc on su.subid=sc.subjectid
inner join student s on s.stuid=sc.stuid;
# 2.外连查询（左外连 右外连）
# 将主表中的数据全部展示，次表中的数据匹配展示，能匹配到的展示数据，匹配不到的填null
# 左外连
select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
# 内敛查询中，表的顺序可以互换，外连查询中主表和次表的顺序不能互换
# 查询出所有的年级信息（没有学员也要展示出来）
select * from grade g left join student s on g.gid=s.gradeid;
# 查询所有的科目以及考试信息（学生）
select subjectname,score from sub left join scores s
on sub.subid=s.subjectid;
# 注意：1.外连查询用的场景相对较少
# 2.表顺序不要随意调换
# 3.常用于子查询
# 补充：
# 1.笛卡尔积
select * from student s,grade g;
# 2.自连接
select s1.stuname studentname, s2.stuname managername
from student s1,student s2
where s1.managerid=s2.stuid;
# 3.合并查询
# 将两个select的结果合并到同一个界面中展示
# union （去重）
select gid,gname from grade union
select subid,subjectname from sub;
# 合并展示结果集时要求列数，列类型，顺序要相同
# union all 展示出两张表中的数据（不去重）
select gid,gname from grade union all
select subid,subjectname from sub;
# 子查询
# 查询嵌套查询，将查询语句作为新的查询语句的条件或者数据表
# 1.使用查询结果作为另外的查询的表
select stuname,gname from
(select * from student s,grade g where s.gradeid=g,gid) sg;
# 2.查询作为新的语句的条件
# 查询出所有考语文的学员中，比李四的语文分数还要高的学员信息
# 步骤1：查询出李四的语文成绩
select score from student,sub,scores
where student.stuid=scores.stuid and sub.subid=scores.subjectid
and stuname='李四' and subjectname='语文';
# 步骤2：查询出比李四语文成绩更高的学生信息
select score from student,sub,scores
where student.stuid=scores.stuid and sub.subid=scores.subjectid
and score>(李四的成绩) and subjectname='语文';
# 步骤3：整合
select score from student,sub,scores
where student.stuid=scores.stuid and sub.subid=scores.subjectid
and score>(select score from student,sub,scores
where student.stuid=scores.stuid and sub.subid=scores.subjectid
and stuname='李四' and subjectname='语文')
and subjectname='语文';
四、事务
对于数据库来说，用户每一次修改表中的数据的时候，都会存在事务。直白的说，事务是一组要么同时执行成功，要么同时执行失败的SQL语句。是数据库操作的一个执行单元。
4.1 事务概述
数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。
场景：
# 此时的两条语句同属于一个业务逻辑--转账操作，一个人的账户余额增加，另一个余额减少 update account set money=money-1000 where username='A'; # 在执行完第一条语句后出现了异常 update account set money=money+1000 where username='B'; # 此时第一条执行成功，数据永久保存，第二条语句执行前发生了异常，不执行。
事务开始于
- 连接到数据库上，并执行一条DML语句insert、update或delete
- 前一个事务结束后，又输入了另一条DML语句
事务结束于
- 执行commit或rollback语句。
- 执行一条DDL语句，例如create table语句，在这种情况下，会自动执行commit语句。
- 执行一条DDL语句，例如grant语句，在这种情况下，会自动执行commit。
- 断开与数据库的连接
- 执行了一条DML语句，该语句却失败了，在这种情况中，会为这个无效的DML语句执行rollback语句。
4.2 事务的四大特点
（ACID）
- actomicity(原子性)
表示一个事务内的所有操作是一个整体，要么全部成功，要么全部失败。
- consistency(一致性)
表示一个事务内有一个操作失败时，所有的更改过的数据都必须回滚到修改前状态。
- isolation(隔离性)
事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。
- durability(持久性)
持久性事务完成之后，它对于系统的影响是永久性的。提交之后，数据永久保存。
4.3 SQL中实现事务管理
set autocommit=0; # 取消自动提交
start transaction; # 开启事务
update account set money=money-1000 where username='A';
update account set money=money+1000 where username='B';
commit; # 提交事务
4.4 java操作事务
如果JDBC连接处于自动提交模式，默认情况下，则每个SQL语句在完成后都会提交到数据库。要启用手动事务支持，而不是JDBC驱动程序默认使用的自动提交模式，请使用Connection对象的setAutoCommit()方法。如果将false传递给setAutoCommit()，则关闭自动提交。
4.4.1 事务的提交和回滚
完成更改后，我们要提交更改，然后在连接对象上调用**commit（）**方法：
conn.commit( );
否则，要使用连接名为conn的数据库回滚更新：
conn.rollback( );
public static void update() {
Connection connection = null;
PrepareStatement pps = null;
ResultSet resultSet = null;
try{
// 1.加载驱动 8.0 com.mysql.cj.jdbc.Driver 5.1 com.mysql.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.获得连接
// url = "jdbc:mysql://IP:端口号/数据库名?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String username = "root";
String password = "123456";
connection = DriverManager.getConnection(url, username, password);
// 3.得到状态通道-建立java和数据库之间的一个SQL通道--提前编译sql
// 设置手动提交
connection.setAutoCommit(false);
String sql1 = "update account set money=money-1000 where username='A'";
pps = connection.prepareStatement(sql1);
pps.executeUpdate();
String sql2 = "update account set money=money+1000 where username='B'";
pps = connection.prepareStatement(sql2);
// 4.执行SQL，得到返回结果
int i = pps.executeUpdate();
// 手动提交哦
connection.commit();
// 5.解析结果集
System.out.println(i > 0 ? "成功" : "失败");
} catch (Exception e) {
System.out.println("有异常");
// 出现异常，执行回滚
try {
connection.rollback();
} catch (SQLException e) {
System.out.println("SQLException");
}
} finally {
// 6.关闭连接 Connection、Statement、ResultSet
try {
// 安全
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
}
五、批处理
批量处理允许将相关的SQL语句分组到批处理中，并通过对数据库的一次调用提交它们。当我们一次向数据库发送多个SQL语句时，可以减少连接数据库的开销，从而提高性能。
5.1 Statement批处理
-
使用*createStatement()*方法创建Statement对象。
-
使用*setAutoCommit()*将auto-commit设置为false 。
-
使用*addBatch()*方法在创建的语句对象上添加您喜欢的SQL语句到批处理中。
-
在创建的语句对象上使用*executeBatch()*方法执行所有SQL语句。
-
最后，使用*commit()*方法提交所有更改。
Statement statement = conn.createStatement();
connection.setAutoCommit(false);
//sql1
String sql1 = "update account set money=money-1000 where username='A'";
statement.addBatch(sql1);
//sql2
String sql2 = "update account set money=money+1000 where username='B'";
statement.addBatch(sql2);
int[] res = stmt.executeBatch();
connection.commit();
5.2 PrepareStatement批处理
-
使用占位符创建SQL语句。
-
使用*prepareStatement()*方法创建PrepareStatement对象。
-
使用*setAutoCommit()*将auto-commit设置为false 。
-
使用*addBatch()*方法在创建的语句对象上添加您喜欢的SQL语句到批处理中。
-
在创建的语句对象上使用*executeBatch()*方法执行所有SQL语句。
-
最后，使用*commit()*方法提交所有更改。
// 设置手动提交
connection.setAutoCommit(false);
String sql = "update account set money=money+? where username=?";
pps = connection.prepareStatement(sql);
// 给sql中的占位符赋值
// 第一次赋值
pps.setInt(1, -1000);
pps.setString(2, "A");
pps.addBatch(); // 将sql添加到批处理操作中
// 第二次赋值
pps.setInt(1, 1000);
pps.setString(2, "B");
pps.addBatch(); // 将sql添加到批处理操作中
// 执行sql
int[] res = pps.executeBatch();
for (int a : res) {
System.out.println(a);
}
connection.commit();
六、反射处理结果集
public static void test() {
Connection connection = null;
PrepareStatement pps = null;
ResultSet resultSet = null;
List<Student> list = new ArrayList<>();
try{
// 1.加载驱动 8.0 com.mysql.cj.jdbc.Driver 5.1 com.mysql.jdbc.Driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2.获得连接
// url = "jdbc:mysql://IP:端口号/数据库名?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String url = "jdbc:mysql://localhost:3306/mydemo1?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
String username = "root";
String password = "123456";
connection = DriverManager.getConnection(url, username, password);
String sql = "select * from student";
pps = connection.prepareStatement(sql);
res = pps.executeQuery();
// 取出结果集
// 返回和结果集相关的信息
ResultSetMetaData metaData = resultSet.getMetaData();
// 得到列的个数
int columnCount = metaData.getColumnCount();
// 取出列名
String[] columnNames = new String[columnCount];
for (int i = 0; i < columnCount; i++) {
columnNames[i] = metaData.getColumnName(i + 1);
}
// 比对列名和属性名
// 得到类中的所有方法
Class<Student> studentClass = Student.class;
Method[] declaredMethods = studentClass.getDeclaredMethods();
// 取出每一行数据，赋值给对应的属性
while (resultSet.next()) {
// 创建一个student
Student student = studentClass.newInstance();
for (String columnName :columnNames) {
String methodname = "set" + columnName;
for (Method declareMethod : declaredMethods) {
if (declaredMethod.getName().equalsIgnoreCase(methodname)) {
declaredMethod.invoke(student,resultSet.getObject(columnName));
break;
}
}
}
// 将封装后的对象保存到集合中
list.add(student);
}
} catch (Exception e) {
System.out.println("有异常");
} finally {
// 6.关闭连接 Connection、Statement、ResultSet
try {
// 安全
if (connection != null) {
connection.close();
}
if (statement != null) {
statement.close();
}
if (resultSet != null) {
resultSet.close();
}
} catch (SQLException e) {
System.out.println("SQLException");
}
}
return list;
}
七、封装工具类
7.1 工具类
public class DBUtils {
//1.定义需要的工具类对象
protected Connection connection = null;
protected PreparedStatement pps = null;
protected ResultSet rs = null;
protected int k = 0;//受影响的行数
private String url = "jdbc:mysql://localhost:3306/mydatabase?characterEncoding=utf8&useSSL=false&serverTimeZone=UTC";
private String username = "root";
private String password = "123456";
//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, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
//4.创建通道
protected PreparedStatement getPps(String sql){
try {
getConnection();
pps = connection.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pps;
}
//5.给占位符赋值 list中保存的是给占位符所赋的值
private void setParams(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 e) {
e.printStackTrace();
}
}
//6.增删改调取的方法
protected int update(String sql, List params) {
try {
getPps(sql);
setParams(params);
k = pps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return k;
}
//7.查询的时候调取一个方法
protected ResultSet query(String sql, List list) {
try {
getPps(sql);
setParams(list);
rs = pps.executeQuery();
return rs;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//8.关闭资源
protected void closeall() {
try {
if (rs != null) {
rs.close();
}
if (pps != null) {
pps.close();
}
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
八、属性文件
db.properties
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/airinfo?characterEncoding=utf8&useSSL=false&serverTimezone=UTC
username=root
password=123456
工具类读取配置文件：
InputStream inputStream = 当前类名.class.getClassLoader().getResourceAsStream("db.properties");
Properties properties = new Properties();
properties.load(inputStream);
dirverName = properties.getProperty("driver");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
九、连接池
连接池基本的思想是在系统初始化的时候，将数据库连接作为对象存储在内存中，当用户需要访问数据库时，并非建立一个新的连接，而是从连接池中取出一个已建立的空闲连接对象。使用完毕后，用户也并非将连接关闭，而是将连接放回连接池中，以供下一个请求访问使用。而连接的建立、断开都由连接池自身来管理。同时，还可以通过设置连接池的参数来控制连接池中的初始连接数、连接的上下限数以及每个连接的最大使用次数、最大空闲时间等等，也可以通过其自身的管理机制来监视数据库连接的数量、使用情况等。
9.1 德鲁伊连接池
阿里出品，淘宝和支付宝专用数据库连接池，但它不仅仅是一个数据库连接池，它还包含一个ProxyDriver（代理驱动），一系列内置的JDBC组件库，一个SQL Parser(sql解析器)。支持所有JDBC兼容的数据库，包括Oracle、MySql、Derby、Postgresql、SQL Server、H2等等。
Druid针对Oracle和MySql做了特别优化，比如Oracle的PS Cache内存占用优化，MySql的ping检测优化。
Druid提供了MySql、Oracle、Postgresql、SQL-92的SQL的完整支持，这是一个手写的高性能SQL Parser，支持Visitor模式，使得分析SQL的抽象语法树很方便。
通过Druid提供的SQL Parser可以在JDBC层拦截SQL做相应处理，比如说分库分表、审计等。Druid防御SQL注入攻击的WallFilter就是通过Druid的SQL Parser分析语义实现的。
9.2 德鲁伊连接池的特点
Druid 是目前比较流行的高性能的，分布式列存储的OLAP框架(具体来说是MOLAP)。它有如下几个特点：
1.亚秒级查询
druid提供了快速的聚合能力以及亚秒级的OLAP查询能力，多租户的设计，是面向用户分析应用的理想方式。
2.实时数据注入
druid支持流数据的注入，并提供了数据的事件驱动，保证在实时和离线环境下事件的实效性和统一性。
3.可扩展的PB级存储
druid集群可以很方便的扩容到PB的数据量，每秒百万级别的数据注入。即便在加大数据规模的情况下，也能保证时其效性。
4.多环境部署
druid既可以运行在商业的硬件上，也可以运行在云上。它可以从多种数据系统中注入数据，包括hadoop，spark，kafka，storm和samza等。
5.丰富的社区
druid拥有丰富的社区，供大家学习。
9.3 德鲁伊连接池使用的jar包
jar包：
druid-1.1.21.jar
9.4 德鲁伊连接池配置文件
druid.properties：
url=jdbc:mysql://localhost:3306/test
username=root
password=123
#驱动类名。根据url自动识别，这一项可配可不配，如果不配置druid会根据url自动识别相应的driverClassName
driverClassName=com.mysql.jdbc.Driver
#初始化时建立物理连接的个数。初始化发生在显示调用init方法，或者第一次 getConnection时
initialSize=5
#最大连接池数量
maxActive=10
#最小连接池数量
minIdle=5
#获取连接时最大等待时间，单位毫秒。
maxWait=3000
9.5 德鲁伊连接池java工具类
DruidUtil.java:
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class DruidUtil {
private static DataSource data = null;
static {
InputStream is = DruidUtil.class.getClassLoader().getResourceAsStream("druid.properties");
Properties ppt = new Properties();
try {
ppt.load(is);
data = DruidDataSourceFactory.createDataSource(ppt);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 用于从DBCP连接池中 获取一个连接
*
* @return DBCP连接池中的一个连接对象.
*/
public static Connection getConnection() {
try {
return data.getConnection();
} catch (SQLException e) {
e.printStackTrace();
return null;
}
}
public static void close(Connection con, SetResultBean bean) {
if (bean != null) {
close(con, bean.getPs(), bean.getRs());
} else {
close(con, null, null);
}
}
public static void close(Connection con, IntResultBean bean) {
if (bean != null) {
close(con, bean.getPs(), null);
} else {
close(con, null, null);
}
}
/**
* 用于释放连接 , 执行环境 , 结果集 等资源
*
* @param conn 要释放的连接资源
* @param state 要释放的执行环境资源
* @param result 要释放的结果集资源
*/
public static void close(Connection conn, Statement state, ResultSet result) {
if (result != null) {
try {
result.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (state != null) {
try {
state.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 编写查询公共方法
*/
public static SetResultBean query(Connection con, String sql, Object[] params) throws SQLException {
if (con != null) {
/**此处不要自己创建preparedStatement*/
PreparedStatement ps = con.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
ResultSet rs = ps.executeQuery();
return new SetResultBean(rs, ps);
} else {
return null;
}
}
/**
* 编写增删改公共方法
*/
public static IntResultBean update(Connection con, String sql, Object[] params) throws SQLException {
if (con == null) {
return null;
}
PreparedStatement ps = con.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
return new IntResultBean(ps.executeUpdate(), ps);
}
public static class SetResultBean {
private final ResultSet rs;
private final PreparedStatement ps;
public ResultSet getRs() {
return rs;
}
public PreparedStatement getPs() {
return ps;
}
public SetResultBean(ResultSet rs, PreparedStatement ps) {
this.rs = rs;
this.ps = ps;
}
}
public static class IntResultBean {
private final int result;
private final PreparedStatement ps;
public int getResult() {
return result;
}
public PreparedStatement getPs() {
return ps;
}
public IntResultBean(int result, PreparedStatement ps) {
this.result = result;
this.ps = ps;
}
}
}