我们写的所有程序的代码来自：官方提供（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("批量删除成功");
            }
        }
    }
}

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家自动化/article/detail/659687