Java连接数据库 #05# SQL与代码分离

索引

读取html中的SQL语句
缺陷总结

在Java连接数据库 #04#里大概是这样放sql语句的：

package org.sample.shop.db.queryrunner;

import org.sample.shop.db.queryrunner.statementfactory.StatementFactory;

// TODO sql一长可读性变得非常非常差
public interface Statements {

    String ITEM_SAVE_ITEM = "INSERT INTO item(user_id, name, price, status, quantity) VALUES (?, ?, ?, ?, ?)";
    String ITEM_REMOVE_BY_ID = "DELETE FROM item WHERE id=?";
    String ITEM_UPDATE_BY_ID = "UPDATE item SET name=?, price=?, status=?, quantity=? WHERE id=?";
    String ITEM_GET_BY_ID = "SELECT id, user_id AS userId, name, price, status, quantity FROM item WHERE id=?";
    // order
    String ORDER_GET_BY_UID = "SELECT id, user_id AS userId, total FROM simple_order WHERE user_id=?";
    String ORDER_SAVE_ORDER = "INSERT INTO simple_order(user_id, total) VALUES(?, ?)";
    String ORDER_SAVE_ORDER_DETAIL = "INSERT INTO order_detail(order_id, item_id, user_id, quantity, price, status) VALUES(?, ?, ?, ?, ?, ?)";
    // order detail
    String ORDER_DETAIL_GET_BY_ORDER_ID = "SELECT id, order_id AS orderId, item_id AS itemId, user_id AS userId, quantity, price, status FROM order_detail WHERE order_id=?";
}

在简单的程序里这样写还是挺方便的，然而单条sql语句一旦变长可读性就会变得非常非常差。改起来也相当不方便。

我首先想到的解决办法是：像mybatis一样把sql放到单独一个XML文件里。于是，我简单写了个草稿程序添加DOM4J依赖开始尝试。结果当我把sql字符串放到xml标签里却被提示格式不对，解析起来也报错。。。。。。

然后仔细看看，mybatis能正常解析xml依靠的是这个↓

我只是想把sql单独放到文件里，并且我希望：

方便阅读和修改sql语句
方便Java解析文件并读取sql语句

为了节约学习成本，我脑洞一开。。。。就投机取巧地用html + jsoup做这件事了。

读取html中的SQL语句

sql.html：

<sql id="ITEM_LIST_BY_UID_AND_STATUS">
    SELECT *
    FROM (
    SELECT rownum AS r, id, user_id AS userId, name, price, status, quantity
    FROM item
    WHERE user_id=? AND status=?
    )temp
    WHERE r >= ? AND r < ?
</sql>
<sql id="ITEM_SAVE_ITEM">
    INSERT INTO item(user_id, name, price, status, quantity)
    VALUES (?, ?, ?, ?, ?)
</sql>
<sql id="ITEM_REMOVE_BY_ID">
    DELETE FROM item
    WHERE id=?
</sql>
<sql id="ITEM_UPDATE_BY_ID">
    UPDATE item
    SET name=?, price=?, status=?, quantity=?
    WHERE id=?
</sql>
<sql id="ITEM_GET_BY_ID">
    SELECT id, user_id AS userId, name, price, status, quantity
    FROM item
    WHERE id=?
</sql>

StatementFactory.java：

package org.sample.shop.db.queryrunner.statementfactory;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;

import java.io.File;
import java.io.IOException;

public class StatementFactory {
    private static final String PATHNAME = StatementFactory.class.getResource("/sql.html").getPath();
    private static final String CHAR_SET = "UTF-8";

    private static Document doc;

    static {
        File input = new File(PATHNAME);
        try {
            doc = Jsoup.parse(input, CHAR_SET, "");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static String getStatement(String statementId) {
        Element sql = doc.select(statementId).first();
        return sql.text();
    }
}

Statements.java（原来的Sql.java）：

package org.sample.shop.db.queryrunner;

import org.sample.shop.db.queryrunner.statementfactory.StatementFactory;

public interface Statements {
    // item
    String ITEM_LIST_BY_UID_AND_STATUS = StatementFactory.getStatement("#ITEM_LIST_BY_UID_AND_STATUS");
    String ITEM_SAVE_ITEM = StatementFactory.getStatement("#ITEM_SAVE_ITEM");
    String ITEM_REMOVE_BY_ID = StatementFactory.getStatement("#ITEM_REMOVE_BY_ID");
    String ITEM_UPDATE_BY_ID = StatementFactory.getStatement("#ITEM_UPDATE_BY_ID");
    String ITEM_GET_BY_ID = StatementFactory.getStatement("#ITEM_GET_BY_ID");;
}

PS. interface中常量和类中静态域性质是一样的！！

缺陷总结

这样做虽然解决了一些问题，但是改进空间还是相当大的。。。

例如说实际写的时候需要来回在4个文件跳转：

以及Statements.java和StatementFactory.java职能其实是重合的。。只是暂时没去把它们写到一起。

就。。有时间再搞吧。。。。。。。。。。。。。。。。