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 但是它必然会有两个不可避免的缺点

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

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

  1. 用户可以硬编码 SQL 语句,比如:
    Sql sql = Sqls.create("DELETE FROM t_abc WHERE name='Peter'");
    
  2. 支持占位符的书写方式,比如:
    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
  3. 用户可以将所有的 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");
    
  4. 你可以为你的 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了。

总结一下:

  1. 回调对象实现接口 org.nutz.dao.sql.SqlCallback,事实上,就像上例所示,这种场景非常适合使用匿名类。
  2. 你的回调函数的返回值会存放在 Sql 对象中
  3. 调用 sql.getResult() 可以直接返回这个对象
  4. sql.getList() 以及 sql.getObject() 方法会泛型安全的替你转型
    • 如果你的对象类型符合要求,则直接返回,否则会通过 Nutz.Castors 替你转换。
    • 对于 getList(),泛型参数用来描述集合内部元素的类型
  5. 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 语句存放在一个或者多个文件中,语句的间隔可以通过注释 "。 是的这是一种非常简单的纯文本文件,文件里只包含三种信息:

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

请注意: 你的 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自由文档许可证下修改和再使用。