自定义 SQL
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");
- $table 将会被替换成 t_abc
- @name 将会被替换成 ?,用来创建 PreparedStatement
- 用户可以将所有的 SQL 语句存放在一个或者多个文件中,语句的间隔可以通过注释,比如:
/* delete.data */ DELETE FROM $table WHERE name LIKE @name /* update.data */ UPDATE FROM $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
- queryEntity
方法来帮助你构建 Sql 对象。它们之间的区别在稍后会详细说明。
通常的情况,你需要构建某些 动态 的 SQL,所以我也允许你为你的 SQL 设置占位符,占位符分两种:
- 变量(var)占位符 - 形式为 $名称
- 以字符 $ 开头,名称为英文字母,数字,下划线,减号和句点。
- 正则表达式为:
[$][a-zA-Z0-9_-]
- 在执行 SQL 前,该占位符会被用户设置的值替换
- 类似 C 语言中的 宏
- 参数(param)占位符 - 形式为 @名称
- 以字符 @ 开头,名称为英文字母,数字,下划线,减号和句点。
- 正则表达式为:
[@][a-zA-Z0-9_-]+
- 在执行 SQL 前,该占位符会被字符 "?" 替换,用来创建 PreparedStatement
- Nutz.Dao 会自动计算 PreparedStatement的索引值
所有的占位符可以同样的名称出现的多个地方。并且变量占位符和参数占位符的名称不互相干扰,比如:
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 语句返回结果,于是,就需要你设置回调。
回调的用处
接上例,你需要这么改造一下你的函数:
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);
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); }