【开发】JDBC学习笔记
【开发】JDBC学习笔记
基本概述
JDBC全称是Java Database Connectivity，是使用Java语言操作关系型数据库的一套API。
也就是说，JDBC承担了一个中间角色的责任，
【Java代码】 ===> 【JDBC】 ===> 【不同关系型数据库的驱动】
最简单的使用JDBC流程如下：
- 创建工程，从官网下载jar包并导入
- 注册驱动
- 获得连接
- 定义SQL语句
- 获取执行SQL对象
- 执行SQL
- 处理返回结果
- 释放资源
为了体验这个流程，我先让GPT给了一个代码（其中的role是另外创建的用户）：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/test?useSSL=false";
String username = "role";
String password = "123456";
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, username, password);
Statement stmt = conn.createStatement();
String sql = "SELECT * FROM student";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getInt("id") + "\t"
+ rs.getString("name") + "\t"
+ rs.getInt("age"));
}
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
API-DriverManager
直译过来是一个驱动管理类，作用为：
- 注册驱动
- 获取数据库连接
这个类里面的方法基本都是静态的，所以都是类名.方法进行调用
事实上MySQL5之后，不需要自己写Class.forname(...)了，会自动加载META-INF里面的驱动信息
另外一个重要的函数是
static Connection getConnection(String url, String name, String password);
url遵从以下的格式：
-
协议：jdbc:mysql://
-
ip:port
-
数据库名称
-
参数表，用&连接
注：如果连接的是本机MySQL，端口默认3306，ip:port处可以不用写端口。
另外，useSSL = false禁用安全连接，解决警告问题。
API-Connection
这是数据库连接对象，主要作用是：
- 获取执行SQL的对象
- 管理事务
主要用到下面几个方法：
-
获取执行SQL的对象
- 普通执行SQL对象
Statement createStatement();- 预编译SQL的执行SQL对象，防止SQL注入
PreparedStatement prepareStatement(sql);- 执行存储过程的对象
CallableStatement prepareCall(sql); -
事务管理
// 开启事务
setAutoCommit(boolean autoCommit);
// 提交事务
commit();
// 回滚事务
rollback();
为了保证事务的一致性，一般采用try-catch包裹进行事务处理
try {
// 开启事务
...
// 提交事务
} catch (Exception e) {
// 回滚事务
e.printStackTrace();
}
API-Statement
Statement就是用来执行SQL语句的，主要有两种方法
int executeUpdate(sql);
// 执行DML、DDL语句
// 返回DML影响的行数、DDL执行成功可能返回0
ResultSet executeQuery(sql);
// 执行DQL语句
// 返回ResultSet结果集对象
API-Resultset
ResultSet封装了DQL查询语句的结果
ResultSet result = stmt.executeQuery(sql);
获取查询结果：
// 将光标从当前位置向前移动一行/判断该行是否为有效行
boolean next();
T getT(参数);
// int: 列的编号，从1开始
// String: 列的名称
API-PreparedStatement
PreparedStatement继承于Statement，预编译语句并执行，预防SQL注入问题
流程如下：
- 获取PreparedStatement对象
String sql = "SELECT * FROM user WHERE username = ? and password = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
- 设置参数值
pstmt.setT(参数1, 参数2);
// T: 数据类型
// 参数1: ?的位置编号，从1开始
// 参数2: ?的值
- 执行SQL语句
executeUpdate();
executeQuery();
PreparedStatement能够防止SQL注入的原理是会将字符串中的敏感字符强行转义，这样在字符串拼接的过程中就不会由于恶意攻击产生信息泄露
实战
为了熟练使用jdbc的接口，我让GPT生成了以下几个表：
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
name VARCHAR(30) NOT NULL COMMENT '姓名',
gender CHAR(1) NOT NULL COMMENT '性别：男/女',
age INT NOT NULL COMMENT '年龄',
score DECIMAL(5,2) DEFAULT 0.00 COMMENT '成绩',
city VARCHAR(50) DEFAULT NULL COMMENT '所在城市',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) COMMENT '学生表';
INSERT INTO student (name, gender, age, score, city) VALUES
('张三', '男', 20, 88.50, '广州'),
('李四', '女', 19, 92.00, '深圳'),
('王五', '男', 21, 76.50, '佛山'),
('赵六', '女', 20, 85.00, '东莞'),
('孙七', '男', 22, 90.50, '珠海'),
('周八', '女', 18, 95.00, '中山'),
('吴九', '男', 23, 67.00, '惠州'),
('郑十', '女', 21, 81.50, '江门');
DROP TABLE IF EXISTS user_account;
CREATE TABLE user_account (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
username VARCHAR(30) NOT NULL UNIQUE COMMENT '用户名',
password VARCHAR(30) NOT NULL COMMENT '密码',
balance DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '余额',
status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1正常，0禁用'
) COMMENT '用户账户表';
INSERT INTO user_account (username, password, balance, status) VALUES
('zhangsan', '123456', 1000.00, 1),
('lisi', '111111', 800.00, 1),
('wangwu', 'abc123', 1500.00, 1),
('admin', 'admin888', 9999.00, 1),
('test_user', 'test123', 300.00, 0);
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(30) NOT NULL UNIQUE,
password VARCHAR(30) NOT NULL,
nickname VARCHAR(30),
age INT,
email VARCHAR(50),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO users (username, password, nickname, age, email) VALUES
('tom', '123456', '汤姆', 20, 'tom@example.com'),
('jerry', '654321', '杰瑞', 19, 'jerry@example.com'),
('alice', 'alice123', '爱丽丝', 21, 'alice@example.com'),
('admin', '1@3N9rk$p', '管理员', 0, 'admin@admin.com');
查询所有学生
其实与例子完全一致，不过根据开发思路封装了以下Student类
import java.math.BigDecimal;
import java.util.ArrayList;
import java.sql.*;
class Student {
String name;
String gender;
int age;
BigDecimal score;
String city;
public Student(String name, String gender, int age, BigDecimal score, String city) {
this.name = name;
this.gender = gender;
this.age = age;
this.score = score;
this.city = city;
}
@Override
public String toString() {
return "=====================\n" +
"名字：" + name + "\n" +
"性别：" + gender + "\n" +
"年龄：" + age + "\n" +
"分数：" + score + "\n" +
"城市：" + city + "\n" +
"======================";
}
}
public class inquireAllStudent {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false";
String username = "role";
String password = "123456";
ArrayList<Student> students = new ArrayList<>();
try {
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String sql = "SELECT * FROM student";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String name = resultSet.getString("name");
String gender = resultSet.getString("gender");
int age = resultSet.getInt("age");
BigDecimal score = resultSet.getBigDecimal("score");
String city = resultSet.getString("city");
students.add(new Student(name, gender, age, score, city));
}
} catch (Exception e) {
e.printStackTrace();
}
for (Student stu : students) {
System.out.println(stu);
}
}
}
查询特定学生
其实就是把前一个部分的sql改成需要的DQL
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String sql = "SELECT * FROM student WHERE id BETWEEN 2 AND 4";
ResultSet resultSet = statement.executeQuery(sql);
插入学生
这时候就要用到：
int executeUpdate(sql);
代码如下：
try {
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String sql = "INSERT INTO student (name, gender, age, score, city) VALUES ('嘉豪', '男', 18, 0.00, '洛杉矶')";
int result = statement.executeUpdate(sql);
System.out.println("插入结果：" + result);
} catch (Exception e) {
e.printStackTrace();
}
修改数据
就是把对应语句放进sql
String sql = "UPDATE student SET score = 20.00 WHERE name = '嘉豪'";
删除数据
String sql = "DELETE FROM student WHERE name = '嘉豪'";
用户转账
其实做法跟上面的差不多，不过要塞入参数，开始采用PreparedStatement
import java.math.BigDecimal;
import java.util.ArrayList;
import java.sql.*;
class Account {
String username;
String password;
BigDecimal balance;
int status;
public Account(String username, String password, BigDecimal balance, int status) {
this.username = username;
this.password = password;
this.balance = balance;
this.status = status;
}
@Override
public String toString() {
return "=======================\n" +
"username: " + username + "\n" +
"password: " + password + "\n" +
"balance: " + balance + "\n" +
"status: " + status + "\n" +
"=======================\n";
}
}
public class accountTransfer {
static void selectAll() {
String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false";
String username = "role";
String password = "123456";
ArrayList<Account> list = new ArrayList<>();
try {
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
String sql = "SELECT * FROM user_account";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String innerUsername = resultSet.getString("username");
String innerPassword = resultSet.getString("password");
BigDecimal balance = resultSet.getBigDecimal("balance");
int status = resultSet.getInt("status");
list.add(new Account(innerUsername, innerPassword, balance, status));
}
} catch (Exception e) {
e.printStackTrace();
}
for (Account account : list) {
System.out.println(account);
}
}
public static void main(String[] args) {
selectAll();
String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false";
String username = "role";
String password = "123456";
Connection connection = null;
PreparedStatement pstm1 = null;
PreparedStatement pstm2 = null;
try {
connection = DriverManager.getConnection(url, username, password);
connection.setAutoCommit(false);
String sql1 = "UPDATE user_account SET balance = balance - ? WHERE username = ?";
pstm1 = connection.prepareStatement(sql1);
pstm1.setBigDecimal(1, new BigDecimal("100.00"));
pstm1.setString(2, "zhangsan");
pstm1.executeUpdate();
// int x = 1 / 0; // 模拟失败情况
String sql2 = "UPDATE user_account SET balance = balance + ? WHERE username = ?";
pstm2 = connection.prepareStatement(sql2);
pstm2.setBigDecimal(1, new BigDecimal("100"));
pstm2.setString(2, "admin");
pstm2.executeUpdate();
connection.commit();
System.out.println("转账成功！");
} catch (Exception e) {
try {
if (connection != null) {
connection.rollback();
System.out.println("转账失败！");
}
} catch (Exception ex) {
ex.printStackTrace();
}
e.printStackTrace();
} finally {
try {
if (pstm1 != null) pstm1.close();
if (pstm2 != null) pstm2.close();
if (connection != null) connection.close();
} catch (Exception e) {
e.printStackTrace();
}
}
selectAll();
}
}
用户登录
这个操作下将对比两种Statement的差异。
首先是比较危险的Statement：
import java.sql.*;
import java.util.Scanner;
public class dangerousLogin {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false";
String username = "role";
String password = "123456";
Scanner sc = new Scanner(System.in);
try {
Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
System.out.println("输入用户名：");
String innerUsername = sc.nextLine();
System.out.println("输入密码：");
String innerPassword = sc.nextLine();
String sql = "SELECT * FROM users WHERE username = '" + innerUsername + "' and password = '" + innerPassword + "'";
ResultSet result = statement.executeQuery(sql);
if (result.next()) {
System.out.println("登录成功！");
} else {
System.out.println("登录失败！");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
假设我给的用户和密码分别是：
admin
1' OR '1' = '1
这样粗暴的字符串拼接之后就是：
SELECT * FROM users WHERE username = 'admin' and password = '1' OR '1' = '1';
结果就是肯定能登陆成功
所以这时候PreparedStatement就是必选项
import java.sql.*;
import java.util.Scanner;
public class safeLogin {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc?useSSL=false";
String username = "role";
String password = "123456";
Scanner sc = new Scanner(System.in);
String sql = "SELECT * FROM users WHERE username = ? and password = ?";
System.out.println("输入用户名：");
String innerUsername = sc.nextLine();
System.out.println("输入密码：");
String innerPassword = sc.nextLine();
try (
Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement preparedStatement = connection.prepareStatement(sql)
) {
preparedStatement.setString(1, innerUsername);
preparedStatement.setString(2, innerPassword);
ResultSet resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
System.out.println("登录成功！");
} else {
System.out.println("登录失败！");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
这样用简单注入的方式就无法成功登录了
浙公网安备 33010602011771号