前言:

推荐免费JavaWeb入门到实战视频:【狂神说Java】JavaWeb入门到实战_哔哩哔哩_bilibili

JDBC复习

JDBC: Java连接数据库

image-20220412131817985

需要jar包的支持:

  • java.sql
  • javax.sql
  • mysql-conneter-java(连接驱动,必须导入)

回顾环境搭建

创建数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建jdbc数据库
create database jdbc;
-- 用户表
create table users(
`id` int primary key,
`name` varchar(40),
`pwd` varchar(40),
`email` varchar(40),
`birthday` date
);
insert into users(id,`name`,pwd,email,birthday) values(1,'小王','123','123@gmail.com','1999-01-01');
insert into users(id,`name`,pwd,email,birthday) values(2,'张三','123','123@gmail.com','1999-01-01');
insert into users(id,`name`,pwd,email,birthday) values(3,'李四','123','123@gmail.com','1999-01-01');
insert into users(id,`name`,pwd,email,birthday) values(4,'王五','123','123@gmail.com','1999-01-01');
insert into users(id,`name`,pwd,email,birthday) values(5,'小明','123','123@gmail.com','1999-01-01');

导入数据库依赖

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>com.jokerdig</groupId>
<artifactId>javaweb-jdbc</artifactId>
<version>1.0-SNAPSHOT</version>

<dependencies>
<!-- MySQL连接驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
</dependencies>
</project>

基本的JDBC

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package com.jokerdig.test;

import java.sql.*;

/**
* @author Joker大雄
* @data 2022/4/12 - 13:36
**/
public class JDBCTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 配置
String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&serverTimezone=UTC";
String username="root";
String password="123456";
// 加载启动
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn=DriverManager.getConnection(url,username,password);

Statement st = conn.createStatement();
// 编写sql
String sql="select * from users";
ResultSet re = st.executeQuery(sql);
while(re.next()){
System.out.println("id:"+re.getObject("id"));
System.out.println("name:"+re.getObject("name"));
System.out.println("password:"+re.getObject("pwd"));
System.out.println("email:"+re.getObject("email"));
System.out.println("birthday:"+re.getObject("birthday"));
}
// 释放资源
re.close();
st.close();
conn.close();
}
}

JDBC固定步骤

  • 加载驱动
  • 连接数据库,代表数据库
  • 向数据库发送SQL的对象Statement:CRUD
  • 编写SQL(根据业务和不同的SQL)
  • 执行SQL
  • 关闭连接

使用PreparedStatement更安全

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
package com.jokerdig.test;

import java.sql.*;

/**
* @author Joker大雄
* @data 2022/4/12 - 17:08
**/
public class JDBCTest2 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 配置
String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&serverTimezone=UTC";
String username="root";
String password="123456";
// 加载启动
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn= DriverManager.getConnection(url,username,password);
// 编写sql
String sql="select * from users where id=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1,1);
ResultSet re = ps.executeQuery();
while(re.next()){
System.out.println("id:"+re.getObject("id"));
System.out.println("name:"+re.getObject("name"));
System.out.println("password:"+re.getObject("pwd"));
System.out.println("email:"+re.getObject("email"));
System.out.println("birthday:"+re.getObject("birthday"));
}
// 释放资源
re.close();
ps.close();
conn.close();
}
}

JDBC事务

要么都成功,要么都失败(ACID原则)

1
2
3
4
5
6
7
开始事务

事务提交 commit()

事务回滚 rollback()

关闭事务

Junit简单了解

IDEA新版运行Junit找不到包,需在设置Bulid Tools>Maven>Runner中勾选第一项Delegate IDE build/run actions to Maven(打开会降低运行速度)

了解:Junit单元测试

1
2
3
4
5
6
7
public class JDBCTest3 {
// @Test只有在方法上有效
@Test
public void test(){
System.out.println("Hello");
}
}

事务解决转账问题

创建事务测试表

1
2
3
4
5
6
7
8
9
10
-- 新建事务测试表
create table account(
id int primary key auto_increment,
`name` varchar(40),
money float
);
-- 插入测试数据
insert into account(`name`,money)values('A',1000);
insert into account(`name`,money)values('B',1000);
insert into account(`name`,money)values('C',1000);

通过转账测试事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
package com.jokerdig.test;

import java.sql.*;

/**
* @author Joker大雄
* @data 2022/4/12 - 17:18
**/
public class JDBCTest3 {
public static void main(String[] args) throws SQLException {
// 配置
String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false&serverTimezone=UTC";
String username = "root";
String password = "123456";
Connection conn = null;
PreparedStatement ps = null;
try {
// 加载启动
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(url, username, password);
// 通知数据库开启事务 关闭自动提交
conn.setAutoCommit(false);
// 转账
String sql = "update account set money = money-100 where `name` ='A'";
ps = conn.prepareStatement(sql);
ps.executeUpdate();
// 制造错误
// int i = 1/0;
// 转账
String sql1 ="update account set money = money+100 where `name` ='B'";
ps = conn.prepareStatement(sql1);
ps.executeUpdate();
// 提交事务
conn.commit();
System.out.println("转账成功");
// 开启自动提交
conn.setAutoCommit(true);
} catch (Exception e) {
try {
// 异常就回滚事务
conn.rollback();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
e.printStackTrace();
} finally {
// 释放资源
ps.close();
conn.close();
}
}
}