库表设计 JDBC

库表设计（复习）

步骤（面试中常考题）：

1. 整理需求
2. E-R 图做辅助

 

1. 根据需求填写字段（字段类型、约束关系）
2. 数据库设计的三大范式（减少数据存储的冗余度的）
   1. 表中的每个字段都是原子的（一个字段不能即保存姓名又保存地址）
   2. 有主键，每个字段应该是和完整的主键有关系（点赞关系表中，不应该保存文章标题）
   3. 有主键，每个字段应该是和主键呈现直接关系，而不是间接关系

 

1. 生成建表语句（这个步骤一般不考）

JDBC 实战练习（博客系统）

准备工作

用户、文章

 

用户注册、用户登录

发表文章

获取文章列表页、文章详情页

 

建库、建表（明文存储的密码，安全性很差）

CREATE DATABASE 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
   1. 每次 SQL 都使用一个新的 Connection（弊端：效率较差）
   2. 共用 Connection（限制：不是线程安全的）
   3. 连接池（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)

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

 

 

总结（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 的使用套路
   1. 注册 Driver
   2. 获取 Connection
   3. 获取 Statement
   4. 执行 SQL
   5. 关闭资源
3. JDBC 的变形
   1. Try-with-resource:  代码简单点
   2. Statement 换成了 PrepareStatement
      1. 前置知识：SQL 注入
      2. 预编译，速度快；防止 SQL 注入；代码简单点
   3. DriverManager 换成了 DataSource
      1. 可以出现连接池
      2. 代码简单点
   4. INSERT 之后获取自增的 id
4. 复杂查询
   1. 聚合查询
      1. GROUP BY
      2. HAVING
   2. 联表查询（关联查询）
      1. 笛卡尔积/结果的筛选