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 = 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(...);
/* 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");
下面我们就由 org.nutz.dao.sql.Sql 接口入手,详细讲解一下 Nutz.Dao 的自定义 SQL 解决方案
我几乎是不加思索的将 SQL 的实现封装在一个接口后面。现在想想这到也没什么坏处。接口的默认实现是 org.nutz.dao.impl.sql.NutSql。你可以直接 new 这个对象,当然,我也提供了构造 Sql 对象的静态方法:
通过 org.nutz.dao.Sqls 类提供的静态方法 create,你可以很方便的构建你的 Sql 对象
Sql sql = Sqls.create("INSERT INTO t_abc (name,age) VALUES('Peter',18)");
Sqls 提供的
方法来帮助你构建 Sql 对象。它们之间的区别在稍后会详细说明。
通常的情况,你需要构建某些 动态 的 SQL,所以我也允许你为你的 SQL 设置占位符,占位符分两种:
[$][a-zA-Z0-9_-]
[@][a-zA-Z0-9_-]+
所有的占位符可以同样的名称出现的多个地方。并且变量占位符和参数占位符的名称不互相干扰,比如:
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 = 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 对象,执行它就相当简单了,比如:
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 语句返回结果,于是,就需要你设置回调。
如果运行的 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了。
总结一下:
为了更加详细的说明一下回调的用处,我们再下面这个例子:
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 |
我们了解了如何构建 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 文件加载到 Dao 对象中。在之后,只要得到 Dao 的对象,可以使用 dao.sqls() 方法获得org.nutz.dao.SqlManager 接口,从这个接口中你就可以获得你预先定义好的 Sql 对象了。
对于 Dao 接口的默认实现, org.nutz.dao.impl.NutDao,提供两个方法,一个是通过构造函数,另一个是 setter 函数。
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 的文件。
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
唯一需要说明的是,在你写作的 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); }
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 函数也就不奇怪了吧。 :)
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 = 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错误),那只能自行分页了.
注释不可用单独一行.例如下面的例子,让SELECT与注释同一行,即可避免被当成sql的key进行处理.
/*+ALL+_ROWS*/ SELECT EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’}}}
基本原则,不允许把不可信数据(例如从网页表单获取的参数值)直接拼入SQL
错误示例
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);
}}}}
正确用法
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系列方法,针对性地移除特殊字符,保障安全!!