Top ↑

无匹配结果，请重新输入

自定义 SQL

Mar 13, 2019 6:56:09 PM

Nutz.Dao 自定义 SQL 的概述

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系列方法,针对性地移除特殊字符,保障安全!!

本页面的文字允许在知识共享署名-相同方式共享 3.0协议和GNU自由文档许可证下修改和再使用。