自定义 SQL

优质

小牛编辑

132浏览

2023-12-01

Nutz.Dao 提供了大多数简单的操作，在80%以上的情况下，你并不需要编写 SQL，因为 Nutz.Dao 会自动替你
生成可以使用的 SQL。但是，在某些特殊的情况下，尤其是考虑到效率等问题，直接写作 SQL 仍然是程序员们
的一个杀手锏，有了这个杀手锏，程序员们永远可以针对任何数据库做他们想要的任何操作。

在之前的时代，很多程序员将 SQL 代码同 Java 代码混杂在一起，即所谓的硬编码。硬编码通常是不
好的，所以很多程序员都采用了各种办法将 SQL 提炼出来存放在一个独立的文件中。其中比较著名的一个框架
就是 iBatis。这个小巧的 SQL 映射框架（Nutz.Dao 比它还小）在这个领域里干的不错。缺省的它将 SQL 存
放在 XML 文件中，现在最新的 iBatis3 也提供了JAVA注解的写法。但是我并不认为 XML 文件或者是 JAVA
注解是存放我的 SQL 语句好地方，我认为 SQL 存放的地方，应该是可以用 Eclipse 的 SQL 编辑器打开并且
能够被正确语法高亮的一种文本文件。

著名的 Hibernate 提供 HQL，虽然语法近似于 SQL 但是它必然会有两个不可避免的缺点

对于数据库方言支持的不好
必然会增加使用者的学习曲线

因此，Nutz.Dao 的自定义 SQL 部分的解决方案是：

用户可以硬编码 SQL 语句，比如：

Sql sql = Sqls.create("DELETE FROM t_abc WHERE name='Peter'");

支持占位符的书写方式，比如：

Sql sql = Sqls.create("DELETE FROM $table WHERE name=@name");
sql.vars().set("table","t_abc");
sql.params().set("name","Peter");
// 连写
sql.setVar("table","t_abc").setVar(...);
sql.setParam("name","Peter").setParam(...);

$table 将会被替换成 t_abc
@name 将会被替换成 ?，用来创建 PreparedStatement

用户可以将所有的 SQL 语句存放在一个或者多个文件中，语句的间隔可以通过注释，比如：

/* delete.data */
DELETE FROM $table WHERE name LIKE @name
/* update.data */
UPDATE $table SET name=@name WHERE id=@id

在你的 Java 代码中：

Sql sql = dao.sqls().create("delete.data");

你可以为你的 SQL 任意定制回调，后面会有详细讲解

下面我们就由 org.nutz.dao.sql.Sql 接口入手，详细讲解一下 Nutz.Dao 的自定义 SQL 解决方案

Sql 对象 -- org.nutz.dao.sql.Sql

我几乎是不加思索的将 SQL 的实现封装在一个接口后面。现在想想这到也没什么坏处。接口的默认实现是
org.nutz.dao.impl.sql.NutSql。你可以直接 new 这个对象，当然，我也提供了构造 Sql 对象的
静态方法:

如何创建 Sql 对象

通过 org.nutz.dao.Sqls 类提供的静态方法 create，你可以很方便的构建你的 Sql 对象

Sql sql = Sqls.create("INSERT INTO t_abc (name,age) VALUES('Peter',18)");

Sqls 提供的

fetchEntity
fetchInt
fetchString
fetchRecord
queryEntity
queryRecord
其他等你发现

方法来帮助你构建 Sql 对象。它们之间的区别在稍后会详细说明。

通常的情况，你需要构建某些动态的 SQL，所以我也允许你为你的 SQL 设置占位符，占位符分两种：

变量(var)占位符 - 形式为 $名称
- 以字符 $ 开头，名称为英文字母，数字，下划线，减号和句点。
- 正则表达式为：
```
 [$][a-zA-Z0-9_-]
```
- 在执行 SQL 前，该占位符会被用户设置的值替换
- 类似 C 语言中的宏
参数(param)占位符 - 形式为 @名称
- 以字符 @ 开头，名称为英文字母，数字，下划线，减号和句点。
- 正则表达式为：
```
[@][a-zA-Z0-9_-]+
```
- 在执行 SQL 前，该占位符会被字符 "?" 替换，用来创建 PreparedStatement
- Nutz.Dao 会自动计算 PreparedStatement的索引值
从1.r.67的2018-12-23号起的快照版,支持带定界符的${abc} 和 @{abc} 形式

所有的占位符可以同样的名称出现的多个地方。并且变量占位符和参数占位符的名称不互相干扰，比如:

Sql sql = Sqls.create("INSERT INTO $table ($name,$age,$weight) VALUES(@name,@age,@weight)");
// 为变量占位符设值
sql.vars().set("table","t_person");    
sql.vars().set("name","f_name").set("age","f_age").set("weight","f_weight");
// 为参数占位符设值
sql.params().set("name","Peter").set("age",18).set("weight",60);

通过上例，我们可以看出，变量占位符和参数占位符的确可以重名且不相互干扰的。

Sql 的逃逸字符

有些时候，有的朋友给出的 SQL 包括特殊字符 '@' 或者 '$'，比如:

Sql sql = Sqls.create("INSERT INTO t_usr (name,email) VALUES('XiaoMing','xiaoming@163.com');"

这个时候，因为有关键字 '@'，所以 SQL 不能被正确解析，因为你的本意是给一个 'xiaoming@163.com'
这个字符串。但是 Nutz.Dao 却认为这个是个语句参数。

这时候你可以使用逃逸字符:

Sql sql = Sqls.create("INSERT INTO t_usr (name,email) VALUES('XiaoMing','xiaoming@@163.com');"

即

输入 "@@" 表示一个 '@'
输入 "$$" 表示一个 '$'

如何执行 Sql 对象

当你顺利的创建了一个 Sql 对象，执行它就相当简单了，比如：

public void demoSql(Dao dao){
    Sql sql = Sqls.create("SELECT name FROM t_abc WHERE name LIKE @name");
    sql.params().set("name", "A%");
    dao.execute(sql);
}

这就完了吗？我怎么取得查询的结果呢。是的，同 UPDATE, DELETE, INSERT 不同， SELECT 是需要返回
结果的，但是 Nutz.Dao 也不太清楚怎样为你自定义的 SELECT 语句返回结果，于是，就需要你设置回调。

Tips:

如果运行的 sql 语句是类似 "show columns from tableName from dbName" 这样的语句，
请在调用 dao.execute 方法前调用 sql.forceExecQuery 方法来强制让 nutz 用 select 方式运行该 sql 语句。

回调的用处

接上例，你需要这么改造一下你的函数：

List<String> demoSql(Dao dao) {
    Sql sql = Sqls.create("SELECT name FROM t_abc WHERE name LIKE @name");
    sql.params().set("name", "A%");
    sql.setCallback(new SqlCallback() {
        public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
            List<String> list = new LinkedList<String>();
            while (rs.next())
                list.add(rs.getString("name"));
            return list;
        }
    });
    dao.execute(sql);
    return sql.getList(String.class);
    // Nutz内置了大量回调, 请查看Sqls.callback的属性
}

看到熟悉的 ResultSet 了吧。当然，如果你执行的不是 SELECT 语句，你依然可以设置回调，但是 ResultSet
参数就是 null了。

总结一下：

回调对象实现接口 org.nutz.dao.sql.SqlCallback，事实上，就像上例所示，这种场景非常适合使用匿名类。
你的回调函数的返回值会存放在 Sql 对象中
调用 sql.getResult() 可以直接返回这个对象
sql.getList() 以及 sql.getObject() 方法会泛型安全的替你转型
- 如果你的对象类型符合要求，则直接返回，否则会通过 Nutz.Castors 替你转换。
- 对于 getList()，泛型参数用来描述集合内部元素的类型
sql.getInt() 会安全的替你将结果转成 int，如果它可以被转成 int 的话，以下是我能想到的列表：
- 字符串
- 各种数字类型
- 字符
- 布尔类型

获取一个列表的回调

为了更加详细的说明一下回调的用处，我们再下面这个例子：

Sql sql = Sqls.create("SELECT m.* FROM master m JOIN detail d ON m.d_id=d.d_id WHERE d.name='aa'");
sql.setCallback(Sqls.callback.entities());
sql.setEntity(dao.getEntity(Master.class));
dao.execute(sql);
List<Master> list = sql.getList(Master.class);

只要你保证你的 Master 类声明了 @Table 并且每个字段上的 @Column 可以同你的 ResultSet 配置起来
那么，上面的代码可以很方便的帮你获取一个 List<Master>.

批量执行

在 Nutz 1.b.38 之后的版本，自定义 SQL 可以支持批量操作

Sql sql = Sqls.create("UPDATE t_pet SET name=@name WHERE id=@id");
sql.params().set("name","XiaoBai").set("id",4);
sql.addBatch();
sql.params().set("name","XiaoHei").set("id",5);
sql.addBatch();
dao.execute(sql);

常用回调

Sqls.callback.XXX 提供了80%以上场景所需要的回调类型,在编写自定义回调之前,建议您先看看有没有现成的

名称	结果类型	备注
bool	Boolean
bools	`boolean[]`	1.r.62及之前的版本是LinkedArray
doubleValue	Double
entities	`List<Pojo>`	取决于Entity对应的Pojo类
entity	Pojo	取决于Entity对应的Pojo类
floatValue	Float
integer	Integer
ints	`int[]`
longValue	Long
longs	`long[]`
map	NutMap	与Record类型,但区分大小写
maps	`List<NutMap>`
record	Record	字段名均为小写
records	`List<Record>`
str	String
strList	`List<String>`
strs	`String[]`
timestamp	TimeStamp

Nutz.Dao SQL 文件的格式

我们了解了如何构建 Sql 对象，但是一个应用通常由很多 SQL 语句构成，如何管理这些语句呢？前面我说过，我希望：
" 用户可以将所有的 SQL 语句存放在一个或者多个文件中，语句的间隔可以通过注释 "。
是的这是一种非常简单的纯文本文件，文件里只包含三种信息：

SQL 语句
SQL 语句的名称 (或者说是键值)。你的程序可以通过语句的名称获取到某一条或几条 SQL 语句
注释 (通常包括在 /* 与 */ 之间)

请注意： 你的 SQL 文件必须为 "UTF-8" 编码。

下面是一个例子

/*
这里是这个 SQL 文件的注释，你随便怎么写
*/
/* sql1 */
DROP TABLE t_abc
/* 你可以随便写任何的注释文字，只有距离 SQL 语句最近的那一行注释，才会被认为是键值 */
/* getpet*/
SELECT * FROM t_pet WHERE id=@id
/* listpets*/
SELECT * FROM t_pet $condition

加载 SQL 文件

如何使用上述的 SQL 文件呢，可以将数个 SQL 文件加载到 Dao 对象中。在之后，只要得到 Dao 的对象，可以使用 dao.sqls() 方法获得
org.nutz.dao.SqlManager 接口，从这个接口中你就可以获得你预先定义好的 Sql 对象了。

对于 Dao 接口的默认实现， org.nutz.dao.impl.NutDao，提供两个方法，一个是通过构造函数，另一个是 setter 函数。

在构造时加载(示意代码,新建NutDao属于重量级操作,应使用单例或Ioc模式)

Dao dao = new NutDao(datasource,new FileSqlManager("demo/sqls/all.sqls"));
System.out.println(dao.sqls().count());

上述代码将打印出 all.sqls 文件中 SQL 语句的数量。
路径 "demo/sqls/all.sqls" 是一个存在在 CLASSPATH 的文件。

FileSqlManager 的构造函数接受数目可变的字符串对象，每个对象就是 SQL 文件的路径。
如不是存在在 CLASSPATH 中的文件，则需要写上绝对路径。
如果你给出的 path 是一个目录，那么该目录下所有后缀为.sqls 的文件都会被加载

在构造之后的任何时加载(示意代码,新建NutDao属于重量级操作,应使用单例或Ioc模式)

Dao dao = new NutDao(datasource);
((NutDao)dao).setSqlManager(new FileSqlManager("demo/sqls/all.sqls"));
System.out.println(dao.sqls().count());

条件变量占位符

我认为 Nutz.Dao 比较吸引人的一个函数就是 Dao.query，它允许你用多种方法传入一个条件
关于复杂的条件，请参看复杂的SQL条件

在 Sql 对象中，我在接口里也设计了一个方法 :

Sql setCondition(Condition condition);

是的，你的 Sql 对象也可以使用 Condition，但是这个 Condition 要如何同你自定义的 SQL 拼装在一起呢，
这里，我提供了一个特殊的变量占位符 -- 条件变量占位符 $condition

特殊的占位符 -- $condition

唯一需要说明的是，在你写作的 SQL 中，需要声明一个特殊的占位符，比如下面的代码输出所有 id 大
于 35 的 Pet 对象的名称

Sql sql = Sqls.create("SELECT name FROM t_pet $condition");
sql.setCondition(Cnd.where("id", ">", 35)).setCallback(new SqlCallback() {
    public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
        List<String> list = new LinkedList<String>();
        while (rs.next())
            list.add(rs.getString("name"));
        return list;
    }
});
dao.execute(sql);
for (String name : sql.getList(String.class))
    System.out.println(name);

请主要看看这两行代码：

Sql sql = Sqls.create("SELECT name FROM t_pet $condition");
sql.setCondition(Cnd.where("id", ">", 35));

第一行的占位符 $condition 已经被 Nutz.Dao 保留。声明了该占位符的 SQL 都可以使用 setCondition 函数。
否则，你设置的条件将无效。

另外一个例子 - 将所有的 id 大于 35 的 Pet 对象的 masterId 设置为 45

void demoCondition2(Dao dao){
    Sql sql = Sqls.create("UPDATE t_pet SET masterid=@masterId $condition");
    sql.params().set("masterId", 45);
    sql.setCondition(Cnd.wrap("id>35"));
    dao.execute(sql);
}

使用 `Entity<?>`

Nutz.Dao 会将你的 POJO 预先处理，处理的结果就是 Entity<?>。你可以通过 Dao 接口的 getEntity() 方法
获取。你通过实体注解配置的信息，尤其是 @Column 中配置的数据库字段的名字
（当数据库字段名同 Java 字段名不同时）尤其有用。Condition 接口的 toSql(Entity<?>) 方法是你唯一
要实现的方法，如果你将一个 Condition 赋个了 Sql 对象，在生成真正 SQL 语句的时候，这个 Entity<?>
又要从那里来呢？答案是，(*你要预先设置}。

如果你不设置 Entity<?>，那么你的 Condition 的 toSql(Entity<?>) 参数就是 null。你可以通过 Dao
接口随时获取任何一个 POJO 的Entity<?>，但是如何设置给你的 Condition 呢，答案是，通过 Sql 对象。

void demoEntityCondition(Dao dao) {
    Sql sql = Sqls.create("UPDATE t_pet SET masterid=@masterId $condition");
    Entity<Pet> entity = dao.getEntity(Pet.class);
    sql.setEntity(entity).setCondition(new Condition() {
        public String toSql(Entity<?> entity) {
            return String.format("%s LIKE 'Y%'", entity.getField("name").getColumnName());
        }
    });
    dao.execute(sql);
}

很多时候，大量的 SQL 语句就是为了能够查出一些 POJO 对象，因此，我给你内置了两个回调，这两个回调都需要你
为你的 Sql 设置一个正确的 Entity<?>

获取实体的回调

Pet demoEntityQuery(Dao dao) {
    Sql sql = Sqls.create("SELECT * FROM t_pet $condition");
    sql.setCallback(Sqls.callback.entity());
    Entity<Pet> entity = dao.getEntity(Pet.class);
    sql.setEntity(entity).setCondition(Cnd.wrap("id=15"));
    dao.execute(sql);
    return sql.getObject(Pet.class);
}

为了方便起见，你可以直接使用 Sqls.fetch 来创建你的 Sql 对象，这个函数会自动为你的 Sql 设置获取实体的回调

Pet demoEntityQuery(Dao dao) {
    Sql sql = Sqls.fetchEntity("SELECT * FROM t_pet $condition");
    Entity<Pet> entity = dao.getEntity(Pet.class);
    sql.setEntity(entity).setCondition(Cnd.wrap("id=15"));
    dao.execute(sql);
    return sql.getObject(Pet.class);
}

查询实体的回调

List<Pet> demoEntityQuery(Dao dao) {
    Sql sql = Sqls.create("SELECT * FROM t_pet $condition");
    sql.setCallback(Sqls.callback.entities());
    Entity<Pet> entity = dao.getEntity(Pet.class);
    sql.setEntity(entity).setCondition(Cnd.wrap("id=15"));
    dao.execute(sql);
    return sql.getList(Pet.class);
}

那么，我提供了一个 Sqls.queryEntity 函数也就不奇怪了吧。 :)

多条件占位符(1.b.53开始提供)

任意变量的值类型为Condition时,均自动识别为条件占位符

    Sql sql = Sqls.create("select * from user where id in (select id from vips $vip_cnd ) and name in (select name from girls $girl_cnd )");
    sql.setVar("vip_cnd", Cnd.where("level", ">", 5));
    sql.setVar("girl_cnd", Cnd.where("age", "<", 30));
    sql.setCallback(Sqls.callback.records());
    dao.execute(sql);

注意事项: 如果调用过setEntity,那么对应的Cnd会进行属性名-字段名映射.

分页

对于不太复杂的sql, 分页还是需要的.

Sql sql = Sqls.queryEntity("SELECT * FROM t_pet");
sql.setPager(dao.createPager(2,20));
sql.setEntity(dao.getEntity(Pet.class));
dao.execute(sql);
return sql.getList(Pet.class);

提醒一下, 用于分页的sql对象,不可重复使用!!

若自动分页失败(例如生成的sql错误),那只能自行分页了.

SQL内的数据库特殊注释

注释不可用单独一行.例如下面的例子,让SELECT与注释同一行,即可避免被当成sql的key进行处理.

/* user.create */

/*+ALL+_ROWS*/ SELECT  
EMP_NO,EMP_NAM,DAT_IN 
FROM BSEMPMS 
WHERE EMP_NO=’SCOTT’

安全性

基本原则,不允许把不可信数据(例如从网页表单获取的参数值)直接拼入SQL

错误示例


@At("/query")
public List<User> query(String city) {
    Sql sql = Sqls.queryEntity("select * from t_user where city='" + city + "'"); // 随便就被注入了
    sql.setEntity(dao.getEntity(User.class));
    dao.execute(sql);
    return sql.getList(User.class);
}

正确用法


@At("/query")
public List<User> query(String city) {
    Sql sql = Sqls.queryEntity("select * from t_user where city=@city");//参数化
    sql.setEntity(dao.getEntity(User.class));
    sql.params().set("city", city);
    dao.execute(sql);
    return sql.getList(User.class);
}

若需要拼入,那么请使用Sqls.escapeXXXXX系列方法,针对性地移除特殊字符,保障安全!!