title: JBDC
date: 2022-03-07 23:25:04
tags: 学习
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.littleblack.JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class JDBCDemo {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/student";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "update student.student_information set stu_name = '母舰妹妹' where stu_no = '201250137'";
Statement stmt = conn.createStatement();
int count = stmt.executeUpdate(sql);
stmt.close();
conn.close();
}
}
|
API分析
注册驱动
对应代码：
1
| Class.forName("com.mysql.cj.jdbc.Driver");
|
该行代码可以注释掉
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| package com.mysql.cj.jdbc;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
public Driver() throws SQLException {
}
static {
try {
DriverManager.registerDriver(new Driver());
} catch (SQLException var1) {
throw new RuntimeException("Can't register driver!");
}
}
}
|
静态方法随着Driver类被初始化而调用
获取连接
对应代码
1
2
3
4
| String url = "jdbc:mysql://localhost:3306/student";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);
|
语法：
jbdc:mysql//ip地址（域名）:端口号/数据库名称？参数键值对1&单数键值对2…
如果连接的是本机的数据库服务且端口为3306，可直接简写为
1
| String url = "jdbc:mysql:///student";
|
获取执行对象
对应代码
1
| Statement stmt = conn.createStatement();
|
获取执行sql的具体对象：
普通执行对象
1
| Statement createStatement()
|
预编译SQL的执行SQL对象
1
| PreparedStatement prepareStatement(sql)
|
执行存储过程的对象
1
| CallableStatement prepareCall(sql)
|
事务管理
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
53
54
| package com.littleblack.JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo2_Connection {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/student";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);
String sql1 = "update student.student_information set stu_name = '母舰妹妹' where stu_no = '201250137'";
String sql2 = "update student.student_information set stu_name = '阿巴阿巴' where stu_no = '201250164'";
Statement stmt = conn.createStatement();
try {
conn.setAutoCommit(false);
int count1 = stmt.executeUpdate(sql1);
int count2 = stmt.executeUpdate(sql2);
conn.commit();
} catch (Exception e) {
e.printStackTrace();
System.out.print("执行事务失败！请检查事务");
conn.rollback();
}
stmt.close();
conn.close();
}
}
|
JBDC事务处理
Connection接口中定义了3个对应的方法
- 开启事务：setAutoCommit(boolean autoCommit) : true为自动提交事务； false为手动提交事务，即为开启事务
- 提交事务：commit()
- 回滚事务：rollback()
执行SQL语句
执行DML、DDL语句
return：(1)DML语句影响的行数(2)DDL语句执行后，执行成功也可能返回0
处理结果
ResultSet封装了DQL查询语句的结果
1
| ResultSet stmt.executeQuery(sql)
|
获取查询结果
ps：Int的编号是从1开始的
源码展示
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
| package com.littleblack.JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCDemo4_ResultSet {
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/student";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "select * from student.student_information";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
int stuNo = rs.getInt(1);
String stuName = rs.getString(2);
System.out.println(stuNo);
System.out.println(stuName);
}
rs.close();
stmt.close();
conn.close();
}
}
|
其中while中代码与以下代码等效
1
2
3
4
5
6
| while (rs.next()) {
int stuNo = rs.getInt("stu_no");
String stuName = rs.getString("stu_name");
System.out.println(stuNo);
System.out.println(stuName);
}
|
SQL注入
源码展示
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
| package com.littleblack.JDBC;
import model.Student;
import java.sql.*;
import java.util.ArrayList;
public class JDBCDemo6_UserLogin {
public static ArrayList<Student> students = new ArrayList<>();
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/db1";
String username = "root";
String password = "root";
Connection conn = DriverManager.getConnection(url, username, password);
String name = "zhangsan";
String pwd = "' or '1' = '1";
String sql = "select * fromwhere username = '%s' and password = '%s';".formatted(name, pwd);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getInt(1) + " " + rs.getString(2));
}
}
}
|
输出结果：
原本的代码逻辑应该是如果传入的name和pwd变量与数据库内username和password相等时，返回他的序号 + 姓名，但是通过修改pwd传递的字符串，可以使sql语句改变原来的含义，这就是SQL注入。
预编译SQL
PreparedStatement预编译SQL并执行SQL语句
1.获取SQL对象
1
2
3
4
5
|
String sql = "select * from user where username = ? and password = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
|
2.设置参数值
1
2
3
4
5
| PreparedStatement对象： setXxx(参数1，参数2): 给 ? 赋值
Xxx: 数据类型，如setInt(参数1， 参数2)
参数:
参数1: ? 的位置编号，从1开始
参数2: ? 的值
|
3.执行SQL
1
| executeUpdate(); 不需要传递sql,因为已经获取了sql对象
|
通过对sql赋值时将赋值文本转义的操作，解决了sql注入的问题（将’ 转义为/‘）
同时预编译使用占位符，可以将sql语句先发送给mysql服务器进行语法检查和编译，等到设置完值后就可以直接运行，提高性能。
同时，预编译以后就无需再检查和编译，所以复用性能大大提高。
开启方式，在url后加入参数
1
| url = "jdbc:mysql:///dbq?useServerPrepStmts=true";
|
数据库连接池
/fm.png)
