Lesson 14

数据库Databases

概念与操作

指明操作的 DB

1 
use zhaozhe;

创建表

1 2 3 4 5 
CREATE TABLE coupons (    name VARCHAR(100),    code VARCHAR(20),    percentage TINYINT);

插入数据

1 2 
INSERT INTO coupons(name, code, percentage)VALUES ("Special Event", "1024", 24);

显示所有数据

1 2 
SELECT *FROM coupons;

查询某些数据

1 2 3 
SELECT *FROM couponsWHERE code = "1024";

多表联合

需求

有很多包裹，每个包裹有很多历史

定义

1 2 3 4 5 6 7 8 9 10 11 12 
CREATE TABLE parcels (    id INT,    code VARCHAR(30),    destination VARCHAR(30)); CREATE TABLE tracking_history (    id INT,    parcel_id INT,    date DATETIME,    message VARCHAR(30));

数据

1 2 3 4 5 6 7 8 9 10 11 12 
INSERT INTO parcels(id, code, destination)    VALUES (1, "P0001", "北京");INSERT INTO parcels(id, code, destination)    VALUES (2, "P0002", "上海");INSERT INTO parcels(id, code, destination)    VALUES (3, "P0003", "广州");INSERT INTO tracking_history(id, parcel_id, date, message)    VALUES (1, 1, now(), "已打包");INSERT INTO tracking_history(id, parcel_id, date, message)    VALUES (2, 1, now(), "已抵达集运中心");INSERT INTO tracking_history(id, parcel_id, date, message)    VALUES (3, 2, now(), "已打包");

查询

Java 代码 操作数据库

Client

需要一个类似 SRRPClient 的东西 去连接 远程数据库服务器

每个数据库都会对不同的语言开发 Client

JDBC

针对这么多不同版本的 Client， Java决定进行专项整顿

定义了一套 针对数据库操作的接口

各大数据库厂商 都根据这个接口去写 实现类

收益

降低 使用者在切换数据库时的 代码修改成本

降低 数据库在 java中的api 的 学习成本

设置 Maven

1 2 3 4 5 6 7 8 9 10 11 
    <dependencies>        <dependency>            <groupId>mysql</groupId>            <artifactId>mysql-connector-java</artifactId>            <version>8.0.15</version>        </dependency>    </dependencies>    <properties>        <maven.compiler.source>1.8</maven.compiler.source>        <maven.compiler.target>1.8</maven.compiler.target>    </properties>

连接与获取数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 
try {    Class.forName("com.mysql.cj.jdbc.Driver");    String url = "jdbc:mysql://zzax-shared.cyd1mmnnvvzs.ap-northeast-2.rds.amazonaws.com:3306/zhaozhe";    String username = "username";    String password = "password";    Connection connection = DriverManager.getConnection(url, username, password);    Statement statement = connection.createStatement();     String query = "SELECT * FROM coupons";    ResultSet resultSet = statement.executeQuery(query);     resultSet.next();     String name = resultSet.getString("name");    System.out.println(name);} catch (Exception e) {    e.printStackTrace();}

使用环境变量

1 2 3 4*5*6 7 8 9 10 11 12 13 14 15 16 17 18 
try {    Class.forName("com.mysql.cj.jdbc.Driver");    String url = "jdbc:mysql://zzax-shared.cyd1mmnnvvzs.ap-northeast-2.rds.amazonaws.com:3306/zhaozhe";    String username = System.getenv("DB_USERNAME");    String password = System.getenv("DB_PASSWORD");    Connection connection = DriverManager.getConnection(url, username, password);    Statement statement = connection.createStatement();     String query = "SELECT * FROM coupons";    ResultSet resultSet = statement.executeQuery(query);     resultSet.next();     String name = resultSet.getString("name");    System.out.println(name);} catch (Exception e) {    e.printStackTrace();}

ORM

Object-relational Mapping

对象关系映射

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 
public class CouponORM {    public static Coupon toObject(ResultSet resultSet) {        Coupon coupon = new Coupon();        try {            coupon.setName(resultSet.getString("name"));        } catch (SQLException e) {            e.printStackTrace();        }         try {            coupon.setCode(resultSet.getString("code"));        } catch (SQLException e) {            e.printStackTrace();        }         try {            coupon.setPercentage(resultSet.getInt("percentage"));        } catch (SQLException e) {            e.printStackTrace();        }         return coupon;    }}

总体流程分析

User <> Client <> Server <> Database Server 

    -   User 
    |
    |
    |
    v
    -   EventHanlder::handle()
    |  _  textField.text : String
    | /
    |/
    .   用户脑子里现在多出了 第四条 

    1.  用户 > 内存数据 
        立刻 + 局部

        textField.text : String -> Message 
        messages.add(message: Message)

    2.  内存数据 > 数据库
        立刻 + 局部

    -   EventHanlder::handle()  
    \   
        -   message: Message 
        |
        |   GSON         |  
        v
        -   messageJson: String 
        |
        |   Request::data : String  
        |
        v 
        -   request: Request 
    /
    v 
    SRRPClient 
    |
    |   Request > String > byte[]
    |
    |   ============= Socket ==============
    |
    |   byte[] > String > Request 
    |
    v 
    SRRPServer 
    |
    |   Request 
    |
    v
    XxxxHandler 
    \
        -   request: Request 
        |
        |   request.data 
        |       
        v 
        -   messageJson: String 
        |
        |   GSON 
        |
        v  
        -   message: Message
        |
        |
        |
        v 
        -   query: String (SQL) , INSERT ......
        |
        |
        |
    /
    v 
    JDBC Client 
    |
    |   ? ========== SOCKET =============
    |
    v 
    Database Server 

    3.  内存 -> 界面
        立刻 + 整体

    -   EventHanlder::handle()  
        message: Message 

        messages: List<Message>

    -   updateToView()
        message: List 

        labels