JDBC实战

库表设计

步骤（面试常考题）

1、整理需求

需求分析：
发文章
发表评论
文章列表页
文章详情页

2、E-R图做辅助

E-R图(实体关系图)

3、根据需求填写字段（字段类型、约束关系）

4、数据库设计的三大范式

a)表中的每个字段都是原子的（一个字段不能即保存姓名有保存地址）；
b)有主键，每个字段应该是和完整地主键有关系（点赞关系中，不应该保存文章标题）
c)有主键，每个字段应该是和主键呈现直接关系而不是间接关系

5、生成建表语句（面试考的比较少）

实战练习（博客系统）

准备工作

用户 ，文章

用户可以注册，用户可以登录；
发表文章；
获取文章列表页，文章详情页；

建库，建表(明文存储的密码，安全性很差)

CREATE BATABASE java20_0211 CHARSET utf8mb4;
	
USE java20_0211;

CREATE TABLE users(
	id INT PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(255) UNIQUE NOT NULL,
	password VARCHAR(255) NOT NULL
);

CREATE TABLE articles(
	id INT PRIMARY KEY AUTO_INCREMENT,
	author_id INT NOT NULL,
	title VARCHAR(255),
	content TEXT
);

项目环境

JDBC的套路

1. 注册 Driver（程序中只需要进行一次）
2. 获取 Connection
   a) 每次 SQL 都使用一个新的 Connection（弊端：效率较差）
   b) 共用 Connection（限制：不是线程安全的）
   c) 连接池（Connection pool）
3. 获取 Statement
4. 执行 SQL
5. 销毁

功能的SQL

用户注册
INSERT INTO users (username, password) VALUES (‘chenpeixin’, ‘123’);
INSERT INTO users (username, password) VALUES (‘?, ‘?’);
用户登录
SELECT id, username FROM users WHERE username = ‘chenpeixin’ AND password = ‘123’;
发表文章
insert into articles(author_id, title, content) values(1,‘title’, ‘qwewrtryeurasddfg ‘);
文章详情页（根据文章id查询文章）
SELECT id, author_id, title, content FROM articles WHERE id = ?

注意

用户登录后，应该保存登录的用户信息，用于以后使用（发表文章）

JDBC API 变形

Statement 变形成 PrepareStatement

1. SQL 注入：用户输入特殊字符导致SQL不是按照作者期望的方式运行

用户名: ‘ OR 1 = 1 OR ‘
密码随便

防止 SQL 注入

用法

String sql = "SELECT id, username FROM users WHERE username = ? AND password = ?";
try (PreparedStatement statement = con.prepareStatement(sql)) {
    // 类似 ResultSet
    // 1. 各种各样的类型
    // 2. 下标从 1 开始
    statement.setString(1, username);
    statement.setString(2, password);

    // MySQL Driver 时打印 SQL 的小技巧
    com.mysql.jdbc.PreparedStatement mysqlStatement =(com.mysql.jdbc.PreparedStatement) statement;
    System.out.println(mysqlStatement.asSql());

    try (ResultSet resultSet = statement.executeQuery()) {
        if (!resultSet.next()) {
            System.out.println("登录失败");
        } else {
            int id = resultSet.getInt("id");
            String usernameInTable = resultSet.getString("username");
            System.out.println("登录成功: " + id + ", " + usernameInTable);
        }
    }
}

获取连接的方式

较早版本的 JDBC 的规定
DriverManager.getConnection(); 弊端: 每次获取连接，都是一次网络请求，用完之后马上销毁，导致效率很低

新版本的 JDBC 的规定
DataSource

1. 使用方式简单
2. 可以带有连接池功能（建议但不强制）

连接池：池化技术，提升效率

获取插入的自增id

try (Connestion con = dataSource.getconnestion()){
	try(PreparedStatement statement = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)){
		statement.setString(1,"xiaowang");
		statement.setString(2,"123");

		statement.executeUpdate();
		//获取插入数的id
		try(ResultSet rs = statement.getGeneratedKeys()){
			rs.next();
			int id = rs.getInt(1);
			System.out.println(id + "xiaowang 123")
		}
	}
}

总结（DataSource + PrepareStatement）

String sql = "SELECT id, username, password FROM users LIMIT ?";

MysqlDataSource mysqlDataSource = new MysqlDataSource();
mysqlDataSource.setServerName("127.0.0.1");
mysqlDataSource.setPort(3306);
mysqlDataSource.setUser("root");
mysqlDataSource.setPassword("");
mysqlDataSource.setDatabaseName("java20_0211");
mysqlDataSource.setUseSSL(false);
mysqlDataSource.setCharacterEncoding("utf8");

DataSource dataSource = mysqlDataSource;

try (Connection con = dataSource.getConnection()) {
    try (PreparedStatement statement = con.prepareStatement(sql)) {
        statement.setInt(1, 3);

        try (ResultSet rs = statement.executeQuery()) {

            while (rs.next()) {
                System.out.println(rs.getInt("id"));
                System.out.println(rs.getString("username"));
                System.out.println(rs.getString("password"));
                System.out.println("=====================================");
            }
        }
    }
}

联表查询

分别查询

SELECT id, author_id, title, content FROM articles WHERE id = 1;
SELECT id, username FROM users WHERE id = {author_id};

联表查询

SELECT
    articles.id, author_id, title, content,
    users.id, username
FROM
    articles, users
WHERE author_id = users.id;

SELECT
    a.id aid, title, content,
    u.id uid, username
FROM
    articles AS a, users u
WHERE author_id = u.id;

聚合查询

SELECT COUNT(*) FROM articles;
SELECT COUNT(*), author_id FROM articles GROUP BY author_id;

注意点：
SELECT 后边只能跟 a) 聚合函数 b) 分组凭证 （MySQL 特例）

SELECT author_id, COUNT(*) count FROM articles GROUP BY author_id ORDER BY count;


SELECT author_id, COUNT(*) count FROM articles GROUP BY author_id HAVING count > 3 ORDER BY count;

额外知识

Try-with-resource

带着 Closable 的类都可以用

a 对象的创建
a 对象的使用
a 对象的关闭 a.close()

Connection/Statement/PrepareStatement/ResultSet
InputStream/OutputStream

Try (对象的创建) {
对象的使用
} // 隐含着 对象的关闭，自动执行 对象.close()

总结

1. 库表设计（重点） 需求分析 -> E-R 辅助 -> 三大范式
2. JDBC 的使用套路
   a) 注册 Driver
   b) 获取 Connection
   c) 获取 Statement
   d) 执行 SQL
   e) 关闭资源
3. JDBC 的变形
   a) Try-with-resource: 代码简单点
   b) Statement 换成了 PrepareStatement
   i. 前置知识：SQL 注入
   ii. 预编译，速度快；防止 SQL 注入；代码简单点
   c) DriverManager 换成了 DataSource
   i. 可以出现连接池
   ii. 代码简单点
   d) INSERT 之后获取自增的 id
4. 复杂查询
   a) 聚合查询
   i. GROUP BY
   ii. HAVING
   b) 联表查询（关联查询）
   i. 笛卡尔积/结果的筛选