目录
2.4 从 DataSource 对象中得到 Connection 对象
2.6 从 ResultSet 中遍历每行结果,从每行中获取每列的值
1. 介绍
什么是 JDBC 呢?
JDBC 代表 Java 数据库连接(JavaDatabaseConnectivity),它是用于 Java 编程语言和数据库之间的数据库无关连接的标准 Java API。
我们写的所有程序的代码来自:官方提供(JDK)、我们写的(App)。
为了减少工作量,引入一些别人(不是官方和我们)写的代码,一般把这类代码称为第三方。以库(library lib)。
在 Java 中,一般是以一组类文件(*.class)提供。把这组类文件打出一个文件(采用 zip 压缩格式,Java 称为 Jar 包:Java ARchive)。
所以,我们要使用别人的第三方库,需要:
拿到一个 jar 包
配置工程(IDEA)让工程在编写、编译、运行阶段,都可以找到第三方库中的类
我们要使用一个 MySQL 官方提供的,进行 SQL 查询的代码库 "mysql-connector-java-5.1.47.jar"
描述:我们的应用 依赖于(depends on)"mysql-connector-java-5.1.47.jar"
为了处理好这些依赖关系,引入 Java 构建阶段的工具:maven/gradle。
这类工具,为了定位每个库,定义了一个坐标(coordinate)的概念。
这个库属于哪个组织(公司):groupId
这个库自己的专有名字:artifactId
这个库的当前版本:version
groupId + artifactId + version 唯一确定一个库。
pom.xml 关于 Maven 工具的配置文件,我们的依赖关系要在这里写清楚。
POM 的主体结构:
在新建一个 maven 项目后,我们需要添加依赖,使用以下代码即可。
<properties>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<encoding>utf-8</encoding>
</properties>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
</dependencies>
如下图所示:
2. 使用 JDBC 连接数据库
2.1 如何使用 JDBC 连接数据库
-
DataSource(数据源)
-
Connection(连接)
-
Statement(SQL 语句)/ PreparedStatement
-
ResultSet(结果集)
对比打电话的例子:
DataSource 类比 联系方式 or 通讯录
Connection 类比打通电话,需要 DataSource 作为前提
Statement:客户向服务器发送的 SQL 语句
ResultSet:服务器向客户回的结果
2.2 导入的各个类
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
2.3 DataSource 对象的创建
DataSource 只是 JDBC 提供的一个接口,要创建的是 MysqlDataSource 对象。
数据库的相关信息
1. 数据库所在的主机:127.0.0.1 / localhost
2. 数据库所在的端口:3306 / 3307 ...
3. 数据库的用户名: ...
4. 数据库的密码: ...
5. 额外的配置选项
5.1. 连接使用的字符集编码 characterEncoding=utf8
5.2. 不使用加密连接: useSSL=false
5.3. 指定时区: serverTimezone=Asia/Shanghai
第一种创建方式:
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setServerName("127.0.0.1");
dataSource.setPort(3306);
dataSource.setUser("debian-sys-maint");
dataSource.setPassword("*********");
dataSource.setDatabaseName("learn");
dataSource.setCharacterEncoding("utf8");
dataSource.setUseSSL(false);
dataSource.setServerTimezone("Asia/Shanghai");
第二种创建方式:
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setUser("debian-sys-maint");
dataSource.setPassword("*********");
dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/learn?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai");
2.4 从 DataSource 对象中得到 Connection 对象
// try-with-resource
// 利用这种写法,不用自己写 con.close(),代码结构看起来干净
try (Connection con = dataSource.getConnection()) {
// 利用 con 对象执行 SQL
} catch (SQLException exc) {
// 处理异常
}
2.5 创建 Statement 对象
// -- 列出当前所在库的所有表名称
String sql = "show tables";
try (PreparedStatement ps = con.prepareStatement(sql)) {
// execute: 执行
// query: 查询
try (ResultSet rs = ps.executeQuery()) {
// 结果就可以从 ResultSet 对象中获取
}
}
2.6 从 ResultSet 中遍历每行结果,从每行中获取每列的值
// hasNext() + next()
while (rs.next()) {
// rs 代表当前遍历的行
// 第一列的结果是表的名称,所以是字符串 String
String tableName = rs.getString(1); // 下标是 1
System.out.println(tableName);
}
2.7 代码汇总
package com.peixinchen;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Main {
public static void main(String[] args) throws SQLException {
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setUser("debian-sys-maint");
dataSource.setPassword("***************");
dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/learn?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai");
// try-with-resource
// 利用这种写法,不用自己写 con.close(),代码结构看起来干净
try (Connection con = dataSource.getConnection()) {
// 利用 con 对象执行 SQL
// -- 列出当前所在库的所有表名称
String sql = "show tables"; // 只有 1 列
try (PreparedStatement ps = con.prepareStatement(sql)) {
try (ResultSet rs = ps.executeQuery()) {
// 结果就可以从 ResultSet 对象中获取
// hasNext() + next()
while (rs.next()) {
// rs 代表当前遍历的行
// 第一列的结果是表的名称,所以是字符串 String
String tableName = rs.getString(1); // 下标是 1
System.out.println(tableName);
}
}
}
}
}
}
进程的执行结果 = 程序代码 + 进程执行时的环境
同样的代码,不同的环境是可能得到不同的结果的,所以如果遇到了运行结果不符合预期,除了检查代码的问题之外,现在应该把更多精力放到检查周边环境是否符合预期上了。
3. PrepareStatement 详解
3.1 动态 SQL 执行
// 静态 SQL
String sql = "show tables";
String sql = "select * from oj_records where oj_id = 1"
我们在之前使用的都是静态的 SQL ,但是在实际的应用中,大多都是动态的 SQL ,比如用户登陆界面都是等待用户输入数据,并不是固定的数据。因此,我们来了解一下动态 SQL。
PreparedStatement 通过占位符(placeholder)和动态绑定的方式做到。
// ? 作为占位符,先把位置占住,等待用户输入
String sql = "select * from users where username = ? and password = ?";
PreparedStatement ps = conn.preparedStatement(sql);
String username = scanner.nextLine();
String password = scanner.nextLine();
// 用实际得到的用户名和密码替换占位符 —— 动态绑定
ps.setString(1, username); // 用用户名替换第一个 ?
ps.setString(2, password); // 用密码替换第二个 ?
接下来,我们运行以下完整的代码(密码和库更改为自己的即可):
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class PreparedStatementDemo {
public static void main(String[] args) throws SQLException{
Scanner scanner = new Scanner(System.in);
System.out.println("请输入要查询的难度: ");
String difficulty = scanner.nextLine();
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setUser("debian-sys-maint");
dataSource.setPassword("*****************");
dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/learn?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai");
try (Connection con = dataSource.getConnection()) {
// 利用 con 对象执行 SQL,建立连接
String sql = "select * from oj_records where difficulty = ?";
// 动态 SQL
try (PreparedStatement ps = con.prepareStatement(sql)) {
ps.setString(1, difficulty);
System.out.println(ps);
try (ResultSet rs = ps.executeQuery()) {
// 结果就可以从 ResultSet 对象中获取
// hasNext() + next()
while (rs.next()) {
// rs 代表当前遍历的行
// 第一列的结果是表的名称,所以是字符串 String
String id = rs.getString(1); // 下标是 1
System.out.println(id);
}
}
}
}
}
}
3.2 SQL 类型
// 针对 PreparedStatement 执行动态绑定时
int x = ...;
ps.setInt(1, x);
String s = "...";
ps.setString(1, s);
String datetime = "2023-04-24 20:54:00"
ps.setString(1, datetime);
// 从 ResultSet 获取值时
int x = rs.getInt(1);
String s = rs.getString(1);
String datetime = rs.getString(1);
Java 代码类型 | MySQL 类型 | 举例 |
int setInt(..) getInt(..) | int | ps.getInt(1,x); x = rs.getInt(1); |
String | varchar(..) char(..) text longtext | |
String | datetime date time |
3.3 SQL 注入(Inject)
什么是 SQL 注入呢?
SQL注入是一种非常常见的数据库攻击手段,SQL注入漏洞也是网络世界中最普遍的漏洞之一。大家也许都听过某某学长通过攻击学校数据库修改自己成绩的事情,这些学长们一般用的就是SQL注入方法。
SQL注入其实就是恶意用户通过在表单中填写包含SQL关键字的数据来使数据库执行非常规代码的过程。,SQL数据库的操作是通过SQL语句来执行的,而无论是执行代码还是数据项都必须写在SQL语句之中,这就导致如果我们在数据项中加入了某些SQL语句关键字(比如说SELECT、DROP等等),这些关键字就很可能在数据库写入或读取数据时得到执行。
我们来从代码角度看看是如何进行 SQL 注入的。
String username = scanner.nextLine();
String sql = "select * from users where username = '%s'";
sql = String.format(sql, username);
// 执行
当用户输入用户名是类型 " ' or 1 = 1 or 1 = ' "
String sql = "select * from users where username = '' or 1 = 1 or 1 = ''";
由于 name = ' ' 为假,1 = 1 为真,1 = ' ' 为假,因此 where 后面的语句最终执行结果为真,因此,会将数据库中的所有信息显示出来,从而导致数据泄露。
4. 关于 SQL 的执行
4.1 带查询结果的执行
select ...;
-- 查询..
show ...;
-- 显示..
ResultSet rs = ps.executeQuery();
4.2 不带查询结果的执行
create database ...;
-- 创建库
create table ...;
-- 创建表
drop database ...;
-- 删除库
drop table ...;
-- 删除表
insert into ...;
-- 增加数据
update ...;
-- 更新数据
delete ...;
-- 删除数据
// number 本次执行成功多少行
int number = ps.executeUpdate();
4.3 关于 ResultSet 读取列
1)读取第 n 列
rs.getInt(n); // 读取第 n 列
2) 根据列名称读取
rs.getInt("oj_id");
5. 实践应用:博客系统
只有一张表 博客文章(blogs)
0) bid int PK AI
1) author varchar(30) NN 作者
2) published_at datetime NN 发表时间
3) content text NN 正文
create table blogs (
bid int primary key auto_increment,
author varchar(20) not null comment '作者',
title varchar(100) not null comment '标题',
published_at datetime not null comment '发表时间',
content text not null comment '正文'
);
-- 新增文章
insert into blogs (author, title, published_at, content) values (?, ?, ?, ?);
-- 删除一篇文章
-- by bid
delete from blogs where bid = ?;
-- 批量 by bid
delete from blogs where bid in (...);
-- 根据标题(只能删自己的)
delete from blogs where author = ? and title = ?;
-- 给出文章列表(bid/作者/发表时间/标题)
-- 根据发表从新到旧
select bid, author, published_at, title from blogs order by published_at desc;
-- 增加分页(每页共 3 篇),需要第 x 页
select bid, author, published_at, title from blogs order by published_at desc limit 3 offset 3 * (x - 1)
x == 1 : offset 0
x == 2 : offset 3
x == 3 : offset 6
创建一个 DBUtil 类用来存放配置信息:
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
// Util: 工具
public class DBUtil {
private static final DataSource dataSource;
static {
MysqlDataSource mysqlDataSource = new MysqlDataSource();
mysqlDataSource.setUser("debian-sys-maint");
mysqlDataSource.setPassword("***********");
mysqlDataSource.setUrl("jdbc:mysql://127.0.0.1:3306/learn?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai");
dataSource = mysqlDataSource;
}
public static Connection connection() throws SQLException {
return dataSource.getConnection();
}
}
发表文章
接下来实现发表文章的功能:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Scanner;
public class PublishArticle {
public static void main(String[] args) throws SQLException {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名:");
String author = sc.nextLine();
System.out.println("请输入文章标题:");
String title = sc.nextLine();
System.out.println("请输入正文内容:");
String content = sc.nextLine();
// 获取当前时间
ZoneId zone = ZoneId.of("Asia/Shanghai");
LocalDateTime now = LocalDateTime.now(zone);
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
String publishedAt = now.format(formatter);
String sql = "insert into blogs (author, title, published_at, content) values (?, ?, ?, ?)";
try (Connection c = DBUtil.connection()) {
try (PreparedStatement ps = c.prepareStatement(sql)) {
// 1. 进行动态绑定
ps.setString(1, author);
ps.setString(2, title);
ps.setString(3, publishedAt);
ps.setString(4, content);
// 此处是不带结果集的查询
System.out.println(ps);
ps.executeUpdate();
System.out.println("文章发表成功");
}
}
}
}
删除文章ByBid
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class DeleteByBid {
public static void main(String[] args) throws SQLException {
Scanner scanner = new Scanner(System.in);
System.out.print("请输入要删除的 bid: ");
int bid = scanner.nextInt();
String sql = "delete from blogs where bid = ?";
try (Connection c = DBUtil.connection()) {
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, bid);
System.out.println(ps);
ps.executeUpdate();
System.out.println("删除成功");
}
}
}
}
批量删除文章ByBid
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import java.util.stream.Collectors;
public class BulkDeleteByBid {
public static void main(String[] args) throws SQLException {
Scanner scanner = new Scanner(System.in);
List<Integer> bidList = new ArrayList<>();
System.out.print("请输入要删除的 bid: ");
while (scanner.hasNextInt()) {
int bid = scanner.nextInt();
bidList.add(bid);
System.out.print("请输入要删除的 bid: ");
}
System.out.println("DEBUG: 要删除的 bid 列表为: " + bidList);
// bidList -> "1, 3, 7
// List<Integer> -> List<String>
// String.join(", ", list of string)
// Stream 流式写法
List<String> bidListString = bidList.stream()
.map(i -> String.valueOf(i))
.collect(Collectors.toList());
String inClause = String.join(", ", bidListString);
String sql = String.format("delete from blogs where bid in (%s)", inClause);
try (Connection c = DBUtil.connection()) {
try (PreparedStatement ps = c.prepareStatement(sql)) {
System.out.println(ps);
ps.executeUpdate();
System.out.println("批量删除成功");
}
}
}
}