本节为系列教程的第三部分: 真正的在线记账本: 客户端 + 服务器 + 数据库.

经过前两节的努力, 我们已经实现了一个客户端 + 服务器模式的在线记账本, 但记账数据并未保存到数据库, 当服务端重启数据将丢失, 本节我们将实现将数据保存到数据库, 实现一个真正意义上的 “在线记账本”.

12. 创建数据库

本教程中我们使用 MySQL, 当然, 你也可以使用其它的 DBMS, 如: Microsoft SQL Server 等.

这只需要使用相应的 JDBC 驱动, 并变换一下连接参数即可 (见下文).

MySQL 的安装与配置就不赘述了, 问度娘~

创建一个名为 account_book 的数据库 (Schema):

1
create databae account_book;

在 account_book数据库中创建基本表 bills, 用于存储账单数据:

1
2
3
4
5
6
7
CREATE TABLE `bills` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` datetime NOT NULL,
  `amount` int(11) NOT NULL,
  `memo` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8;

注意观察 bills 表的结构, 它们与此前我们客户端/服务器端的数据模型是一致的.

13. 对接数据库

13.1 准备数据库驱动

我们将使用 JDBC 连接 MySQL 数据库, 所以先得 下载 MySQL 的驱动程序.

然后, 将此驱动程序文件 (.jar) 放到上节创建的 WEB-INF/lib文件夹下 ( 和上节 web-lighter 的那些 jar 文件放在一起 )

最后, 在 IDEA 中驱动程序文件上点击右键, 右键菜单中选择 Add As Library…, 将其添加到构建路径.

完成后的 WEB-INFO 文件夹结构大概像这样:

Configurations

13.2 创建数据库工具类

与数据库交互时有很多参数和步骤是相同的, 为了统一管理, 我们先来创建一个数据库工具类.

Code-13.2: DBUtil.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package example.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    // 数据库驱动程序名
    final private static String driver = "com.mysql.jdbc.Driver";
    // 数据连接地址
    // 注意其中的数据库名"account_book"
    final private static String url = "jdbc:mysql://localhost/account_book?autoReconnect=true";
    // 数据库登录名
    final private static String user = "root";
    // 数据库登录密码
    final private static String password = "1234";

    // 返回数据库连接对象
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        return DriverManager.getConnection(url, user, password);
    }
}

DBUtil类将数据库连接参数信息统一管理, 此外还提供了一个公有 (public) 静态(static) 方法getConnection() 返回数据库连接对象.

这里的 DBUtil 只对取得数据库连接对象(Connection)这一功能进行的封装. 在未来实践的过程中你可能会意识到可以把更多的功能 (如: 查询/更新) 封装到 DBUtil 里.

注意: 第 12, 14, 16 行中的数据库名, 登录名, 密码改成你自己的!

13.3 创建数据库访问类

现在让我们把跟数据库交互的相关操作都封装起来, 创建如下AccountBookDAO类:

对照代码中的注释慢慢看…

本人有点懒, 若前面注释过的内容, 后面再出现就没再注释了, 所以请按顺序看.

Code-13.3: AccountBookDAO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
package example.dao;

import example.vo.Bill;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AccountBookDAO {
    /**
     * 查询数据库中现有的账单
     */
    public List<Bill> queryBills() throws SQLException, ClassNotFoundException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Bill> bills = new ArrayList<>();
        try {
            // 获得数据库连接对象, 参见 Code-13.2 第 19 ~ 22 行
            conn = DBUtil.getConnection();
            // 创建语句(Statement)对象
            stmt = conn.createStatement();
            // 执行查询, 查询到的结果放在结果集(ResultSet)中
            // 这里把账单按时间降序排序
            rs = stmt.executeQuery("select * from bills order by time desc;");
            
            // 将查询到的每一条账单数据打包成一个 Bill 对象, 并放入 List<Bill>
            // 参见第 111 ~ 118 行
            while (rs.next()) {
                bills.add(packBill(rs));
            }
        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        }
        // 返回查询结果
        return bills;
    }

    /**
     * 保存账单数据到数据库
     */
    public Bill saveBill(Bill bill) throws SQLException, ClassNotFoundException {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            // 获得数据库连接对象, 参见 Code-13.2 第 19 ~ 22 行
            conn = DBUtil.getConnection();
            String sql = "insert into bills(time, amount, memo) values (?, ?, ?)";
          
            // 这里使用了 PreparedStatement, 支持参数占位符"?", 与第 24 行对比一下
            // Statement.RETURN_GENERATED_KEYS 参数要求 insert 执行成功后返回此记录的主键字段值
            // 对照第12节, 建表的 SQL 语句中ID字段是"自增长"型(AUTO_INCREMENT)
            pstm = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
          
            // 取得服务器当前系统时间
            // 若要保存的信息同时含日期和时间, 必须使用 Timestamp
            Timestamp now = new Timestamp(System.currentTimeMillis());
          
            // 设置 PreparedStatement 的参数, 即 SQL 语句中的那些"?"
            // 注意在 JDBC 中, 第 1 个参数序号是 1, 而不是 0
            pstm.setTimestamp(1, now);
            pstm.setLong(2, bill.getAmount());
            pstm.setString(3, bill.getMemo());
          
            // 执行数据库更新, 将账单数据保存到数据库
            pstm.executeUpdate();
          
            // 获得数据库端生成的主键值
            rs = pstm.getGeneratedKeys();
            if (rs.next()) {
                // 将主键值和账单时间写入账单数据对象
                // 以保持数据模型与数据库中实际存储的数据一致
                bill.setId(rs.getInt(1));
                bill.setTime(now);
            } else {
                throw new SQLException("新增数据失败");
            }
          
        } finally {
            if (rs != null) rs.close();
            if (pstm != null) pstm.close();
            if (conn != null) conn.close();
        }
        return bill;
    }

    /**
     * 删除账单
     */
    public void removeBill(Bill bill) throws SQLException, ClassNotFoundException {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConnection();
            String sql = "delete from bills where id = ?";
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, bill.getId());
            pstm.execute();
        } finally {
            if (pstm != null) pstm.close();
            if (conn != null) conn.close();
        }
    }

    /**
     * 将 ResultSet 中的账单数据打包成 Bill 对象
     */
    private Bill packBill(ResultSet rs) throws SQLException {
        Bill bill = new Bill();
        bill.setId(rs.getInt("id"));
        bill.setTime(rs.getTimestamp("time"));
        bill.setAmount(rs.getLong("amount"));
        bill.setMemo(rs.getString("memo"));
        return bill;
    }
}

上述AccountBookDAO实现了对账单数据进行”查询/插入/删除”的封装. 修改功能并未实现, 留给你来完成.

​

此外, 请注意: AccountBookDAO 与外界的接口部分转递的信息是最简单朴素的 Bill 或 List<Bill>. 也就是说, 我们要尽可能地让 AccountBookDAO 之外的世界不知道数据库和 JDBC 的存在, 也无需关心数据是怎么来的, 怎么保存的, 这同样是分层解耦的思想.

14. 最后一步, 大功告成!

最后我们来修改 BillAction.java 中的代码, 让它来与上面定义的 AccountBookDAO 对接, 而不是对接AccountBookService.

Code-14.1: BillAction.java (修改后)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
package example.action;

import com.bailey.web.lighter.action.ActionResult;
import com.bailey.web.lighter.action.ActionSupport;
import com.bailey.web.lighter.annotation.Inject;
import com.bailey.web.lighter.annotation.Param;
import com.bailey.web.lighter.annotation.Request;
import example.dao.AccountBookDAO;
import example.service.AccountBookService;
import example.vo.Bill;

public class BillAction extends ActionSupport {
  
    // 响应前端 /wl/getBills 请求, 将账本中的所有账单记录返回前端.
    // 注意形参表中 @Inject 注解后不是 AccountBookService 了, 而变成 AccountBookDAO
    @Request(url = "/getBills")
    public ActionResult getBills(@Inject AccountBookDAO dao) {
        try {
            // 使用 AccountBookDAO 从数据库中获得所有账单数据
            // ActionResult.success() 方法将账单数据封装为 JSON 字符串
            return ActionResult.success(dao.queryBills());
        } catch (Exception e) {
            // 若数据库交互异常, 向前端返回"出错"信息
            return ActionResult.failure();
        }
    }

    // 响应前端 /wl/saveOrUpdateBill 请求, 将账本中的所有账单记录返回前端.
    // 注意形参表中 @Inject 注解后不是 AccountBookService 了, 而变成 AccountBookDAO
    @Request(url = "/saveOrUpdateBill")
    public ActionResult saveOrUpdateBill(
            @Inject AccountBookDAO dao,
            @Param(name = "action") String action,
            @Param(name = "bill") Bill bill) {

        try {
            switch (action) {
                case "append":
                    // 插入账单到数据库
                    bill = dao.saveBill(bill);
                    break;
                case "remove":
                    // 删除数据库中的账单记录
                    dao.removeBill(bill);
                    break;
            }
            // 将保存后的账单数据打包成 JSON 字符串返回前端
            // 注意这里必须把保存后的账单信息带回前端
            // 因为对于新增账单而言, 账单的 ID 和时间是在服务器端生成的
            return ActionResult.success(bill);
        } catch (Exception e) {
            // 若出错, 向前端返回"出错"信息
            return ActionResult.failure();
        }
    }
}

再开一个浏览器窗口, 打开第 (二)节教程, 仔细对比上面的代码(Code-14.1)和原先的代码(Code-10.1)的变化.

其实, 关键的变化是我们把服务器端数据模型的交互操作从 AccountBookService 切换成了 AccountBookDAO.

这又是分层设计的优势的另一体现

​

重启一下服务器端 Tomcat, 浏览器里一顿操作之后是不是发现数据都已经保存到数据库里了.

此时, 即使重启服务器, 你的账本数据都还好好的在那里等着你. 这就是”真正的在线记账本”!

这回大家开心了吧~

​

按照惯例我们还是来小结一下:

  • 本节内容其实不多, 只是在第(二)节基础上把数据的存储位置切换到了数据库里. 问题的关键在于:

    分层、低耦合的设计将使得程序变得条理清晰, 而又灵活. 在需要撤换某一层时, 低耦合的设计将使得这项工作简单且平顺地完成.

  • 反思 AccountBookDAO.java (Code-13.3) 的代码, 你也许会发现它并不那么优雅, 甚至有点点臃肿, 乱!

    比如:

    1. 那些 SQL 语句能否不用手工来写, 如果有工具能帮我们自动生成所需的 SQL 该有多好!

    2. 那个packBill()方法的作用在于把数据库查询结果 ResultSet 中的数据库封装成简单的值对象(bill, VO) , packBill() 里大量的代码都在做搬运工. 能否有工具能帮我们完成这个封装工作, 并维护 bill 的状态?

      严格来说, 与数据库中记录对应的 Java 对象应称作持久层对象(PO, Persistant Object). 在实践中, PO 除数据外可能还有状态等其它信息.

      本例为了简单起见, 不想把事情搞大, 所以将就用一下此前定义的那个 VO (Value Object)

    类似的问题还很多, 程序写得多了, 你就会有强烈的愿望, 想要有个东西来帮你完成与数据库交互这一层的封装,这就是所谓的持久层封装.

    呵呵, 那还等什么?

    继续学习ORM (Object Relational Mapping), 持久层框架 (如: Hibernate, myBatis, Cayenne…) , 它们就是你想要的.

    当然, 本人推荐 Cayenne, 本博客里好几篇介绍 Cayenne 的文章.

​

~ 全剧终 ~

~ 点个关注呗 ~

~ 掌声、鲜花、小礼物走一走呗 ~

​

如果你发现此教程中有错误, 比如: 按照教程来做就是不对, 或者有些地方没有解释清楚. 麻烦你告诉我, 我改 !

即使你天生丽质, 自己已经把教程中的错误摆平, 也一定记得告诉我, 我改 !

免得折腾后来的同学 !