jSqlBox5.0.1 版,参数内嵌式 SQL 了解一下,把 SQL 掰直了写

来源: 投稿
作者: yong9981
2020-10-23

jSqlBox主要特点是架构优、尺寸小、功能全,基本上所有与数据库操作相关的功能,jSqlBox都已提供。它的主要特点有:  

1.内核基于DbUtils并与之兼容,最差情况下可以降级当成DbUtils来使用,上手快。  
2.提倡在java里拼写SQL,独创参数内嵌式SQL写法(下面会详细介绍)。  
3.只有单个1M大小的jar包,不依赖任何第三方库,不依赖Spring(但也支持在Spring环境下使用)。  
4.支持分库分表、声明式事务、分布式事务、缓存翻译、长文本、ActiveRecord。
5.支持80多种数据库方言,分页、函数变换、DDL生成、实体源码生成、实体或数据库结构导出Excel。
6.主要的实体类注解兼容JPA标准。  

拥抱SQL,在Java中直接写SQL是jSqlBox的主要特点,如果对SQL精通,也可以只利用SQL来完成项目的开发。说SQL开发效率不如ORM,只是因为没有把SQL写到极致。上次有人说“直接在JAVA里写SQL有什么出奇,我一直这么玩”。我回答:“如果不出意外,jSqlBox玩的比你更调皮”。这是因为jSqlBox采用的"参数内嵌式SQL写法",它的功能比你能想到的还要多得多。  

传统的SQL有什么问题? 最大的问题就是它是“弯”的,下面这个SQL大家看出问题没?
DB.exe("insert into users (id, name, age, address) values(?, ?, ?, ?)", param(1, "张三", 10, "北京"));
name、问号、和它的实参"张三",这三个关联的要素出现在三个间隔遥远的位置,这是违反常理的,只有写成下面这样,才会把三个关联要素在纵向对齐在一起: 
insert into users (id,   name,   age,   address)
                 values(?,    ?,      ?,     ?)
                param(1,   "张三",  10,    "北京"));
也就是说,现在的SQL要做到可维护性好,就必须“弯”着写。当然实际项目因为动态参数的问题,没人把SQL弯着写,所以问题就来了,参数一多,列名、问号、参数这三者之就配对困难,程序员要经常1、2、3、4去数数,影响开发和维护效率。  
jSqlBox为了解决这个问题,采取的方案是参数内嵌式SQL写法,把SQL给掰直了:
DB.exe("insert into users (id ", param(1), ",name ", par("张三"), ", age", par(10), ",address)", par("北京") , valuesQuestions());
这种写法无论SQL写多长,都不影响可维护性,如果要获得更好的可维护性,可以把它竖过来写,新增字段只要添加一行即可,注意竖过来写也是直的,不是弯的:

DB.exe("insert into users (id ", param(1), //
                ",name ", par("张三"), //
                ", age", par(10), //
                ",address)", par("北京") ,
                valuesQuestions());

具体jSqlBox使用说明请见它的用户手册,本文是对jSqlBox参数内嵌式SQL写法的详细介绍:

参数内嵌式SQL是jSqlBox的首创,在SQL里直接写参数,SQL执行时自动转化为preparedStatement,这种方式的优点是被赋值的字段和实际参数可以写在同一行上,字段很多时利于维护,也方便根据不确定的条件动态拼接SQL。SQL参数必须放在par或que方法里,如果是SqlResultHandler、拉截器、Connection、DbContext, SqlItem等已知类型的对象,则不必用方法括住。字符串类型如果不放在par或que方法里的话则视为SQL文本的一部分。
很多场合业务逻辑不复杂,但是字段很多,SQL写得很长,当要添加、修改一个字段时,光是找到这个字段和它对应的是哪一个参数就很麻烦(用模板是一种方案,但模板占位符要多打几个字,模板本身的快速定位查找也是个问题,因为通常IDE不支持定位到XML或文本文件的某一行。) 利用SQL内嵌参数这种写法,可以方便地增加、删除字段,因为每一个字段和它对应的实参都写在了同一行上。
参数内嵌式SQL是jSqlBox5.0版起的默认书写格式,所有以qry/ins/exe/upd/entity打头的方法都采用参数内嵌式SQL写法。

参数内嵌式SQL示例:

  DbContext db= new DbContext(dataSource);
  db.exe("insert into users (", //
            " name ,", par("Sam"), //一个参数写一行
            notNull("age,", user.getAge()), //notNull方法的第二个参数为null时,这一项将不会添加到SQL中
            " address ", par("Canada"), //
            ") ", valuesQuestions()); //自动根据参数个数补上 values(?,?...?)片段
  db.exe("update users set name=?,address=?", par("Tom", "China"));//参数也可以连写
  Assert.assertEquals(1L, ctx.iQueryForObject("select count(*) from users where name=? and address=?", par("Tom", "China")));
  db.exe("delete from users where name=", que("Tom"), " or address=", que("China"));//问号也可以省

上例中SQL只有几行,还看不出它的优点,但是如果有二十行、二十个参数,就能体会这种写法的好处了。

上例的nutNull、par、que等方法是用“import static com.github.drinkjava2.jsqlbox.DB.*; ”这种静态引入方式使用。que(或ques)与par(或param)方法的区别是que会在原地留下一个问号字符串去拼SQL,而par仅会返回一个“”空字符串,到底用que还是par要视具体情况而定。

在DB中定义了大量的静态方法供使用,当系统中设定好一个DbContext默认全局实例后,可以直接引用DB工具类中的SQL方法,例如:

DbContext.setGlobalDbContext(new DbContext(someDataSource));
exe("delete from users where userId=",que(1)); //直接使用静态方法

上述静态引入DB类的SQL方法的局限是只限于单数据源场合。

DB不是jSqlBox的核心类 ,如果对它的方法命名不满意,用户可以根据它的源码写出自已喜欢的静态方法库,以方便静态引入。

当需要根据复杂的条件来动态拼接SQL时,参数内嵌式SQL写起来也很简单:

  	ctx.exe("insert into users (", //
  		 " name", par(name), //
  		   when(age!=null," ,age ", par(age)), //when是条件判断,相当于IF
  			" ,address ", par(address), //这里只能用par,因为valuesQuestions方法会补足问号
  			") ", valuesQuestions());
  	ctx.exe("update users set ", //
  			" name=", que(name), //这里也可以写成 " name=? ", par(name)
  			when(age!=null, ", age=", que(age)), //
  			when(address!=null, ", address=",  que(address)), //
  			" where name is not null"
  	);
  	Assert.assertEquals(1L, ctx.qryLongValue(//
  			"select count(*) from users where 1=1 ", //
  			when(name!=null," and name=", que(name)),//
  			when("Tom".equals(name)," and name=", que(name)),//
  			when("China".equals(address)," and address=", que(address)),//
  			" order by name"
  	));

这有点类似模板语言,但比模强的地方是无需学习模板语法,Java本身就是最好的模板,而且Java方法可以随时自定义添加,具体怎么添加大家可以看一下DB.par()、DB.when()等方法的源码就明白了,仅有1行代码。

参数内嵌式SQL要求所有的方法最后一个参数是一个不定长对象数组,传入的内容分为以下各种情况:

  • 用来拼接SQL和参数的元素, 如:
    字符串类型: 会被解释为SQL文本
    数组类型: 会被递归解析,直到数组不再有嵌套数组为止
    param或par(参数1,参数2...): 会被解释为SQL参数并在原地返回一个空字符串,它定义在DB类中,通常静态引入使用,下同 ques或que(参数)会被解释为SQL参数, 并在原地返回一个问号字符串 notNull(str,obj) obj非空时,会被解释为SQL参数,并在SQL中添加str作为SQL文本
    noNull(str,obj...) 没有一个obj为空时,将所有obj参数相连成一个SQL参数,并在SQL中添加str作为SQL文本
    valuesQuestions() 自动根据参数个数,生成一个values(?,?...?)SQL文本片段
    CustomizedSqlItem: 自定义的特殊SQL条目,用户可以自已定义如何来翻译成SQL或参数。 when(boolean, obj...) 根据条件返回对象数组,如条件不满足则返回一个空字符串, when支持嵌套。
  • 特殊类型, 如:
    pagin(pageNumber,pageSize) 会被解释为一个分页拦截器, 详见分页一节
    other(obj...)方法,将一些额外参数(通常是字段别名或显示宽度等)保存在线程局部变量,并返回一个空字符串,用DB.getOthers()可以获取保存的参数
    shardTB(shardvalues) 根据传入值生成分表后的表名字符串,详见分库分表一节
    shardDB(shardvalues) 根据传入值解释为分库后的DbContext,详见分库分表一节
    shard(shardvalues) 根据传入值会解释为分库后的DbContext和表名,详见分库分表一节
    Xxxx.class: 如果一个参数是User.class这种类型,表示SQL方法将根据User类来翻译成SQL,常用于Text类多行文本解析和实体类查询。
    TableModel实例:传入一个TableModel可以进行覆盖实体到数据表的缺省配置,详见动态配置一节。
    SqlResultHandler实例: 某些方法需要传入一个SqlResultHander参数,详见DbUtils
    SqlHander拉截器实例: 传入SqlHandler拦截器,详见拦截器一章。
    Connection实例:传入Connection实例, 运行期由这个Connection去执行SQL。 DbContext实例:传入DbContext实例, 运行期由这个实例去执行SQL。
    SqlTemplateEngine实例:当接收到一个SqlTemplateEngine接口的实例后(如DB.TEMPLATE),SQL转为模板方式运行
    IGNORE_NULL 这是一个开关参数,当实体插入和修改时(即entityInsert/entityUpdate方法),忽略掉实体的所有null值字段
    IGNORE_EMPTY 这是一个开关参数,当实体插入和修改时,忽略掉实体的所有null值字段和空字符串字段。

对于jSqlBox的SQL方法变长参数的理解,可以将它看成是Windows操作系统下的消息,每一个SQL条目,只是一个消息而已,jSqlBox将会汇总所有消息并把它们翻译成实际的SQL或参数并执行,在jSqlBox中所有内容都可以作为参数传递,如拦截器、模板引擎、甚至DbContext实例本身,也可以作为参数传递(这种情况下,传入的DbContext实例将夺取SQL执行权,常用于多数据源场合)。另一方面,jSqlBox的大多数SQL方法、CURD方法(包括ActiveRecord的方法),都允许额外附加不限数量的SQL条目,以实现最大的灵活性,这就是为什么jSqlBox中的大多数方法最后一个参数都是一个可变对象数组参数的原因。

最后再上几个复杂点的例子,显示参数内嵌式SQL的灵活强大:

//写出支持重构的SQL:
ctx.iExecute("insert into ", USER, " ( ", //USER、NAME是在User类中定义的常量,静态引入
	NAME, ",", par("Sam"), //
	ADDRESS, " ", par("Canada"), //
	") ", valuesQuesions());

//传入一个自带模板对象DB.TEMPLATE,就可以使用SQL模板了。如果传入Beetl模板就会支持复杂的模板语法了
UserAR sam = new UserAR("Sam", "Canada");
UserAR tom = new UserAR("Tom", "China");
paramMap.put("user", sam);
ctx2.exe(DB.TEMPLATE,  "insert into users (name, address) values(#{user.name},:user.address)", paramMap);
ctx2.exe(DB.TEMPLATE,"update users set name=#{user.name}, address=:user.address", bind("user", tom));
Assert.assertEquals(1L,
          ctx2.qryLongValue(TEMPLATE,"select count(*) from users where name=#{name} and address=:addr",
          bind("name", "Tom", "addr", "China")));

//other方法可以存放任意额外信息,用DB.others()方法可获取,为什么显示列宽和颜色要写到SQL里? 这是给前后端是同一个人时设计的,参见GoSqlGo项目
Map<String, Object> result = DB.qryMap("select ", //
		" id", other("id", 10), //jSqlBox是GoSqlGo唯一指定DAO工具
		when(u.age==5, ", name as name1 ", other("姓名1", "年纪=5", "注:用红字显示")), //
		when(true, ", name as name2 ", other("姓名2", "显示列宽=10")), //
		" from TitleDemoEntity", //
		" where id<>", que("a"), //
		when(name != null, " and name like ", que("%" + name + "%")), //
		new PrintSqlHandler() //
);		 

//万物皆可传
new User(100, "Tom", "China").update(ctx2," and age>?", param(5), IGNORE_EMPTY, new PrintSqlHander());

上例最后一行做了以下事情:
手工切换到ctx2这个DbContext实例上(即操作另一个数据源)
ActiveRecord实体User主键为100的记录,如果age字段大于5则更新它的内容 勿略User实体的所有null或空值属性
打印SQL到控制台
如果User类ID上有@ShardDB或@ShardTB注解,会根据ID=100的值进行分库分表操作

DbContext和DB类中定义的参数内嵌式SQL方法一览:

qry(Object...) 执行一个查询,返回类型由传入的SqlResultHander或SqlHander来决定
qryObject(Object...) 执行一个查询,返回一个Object值
qryLongValue(Object...) 执行一个查询,返回一个long值
qryIntValue(Object...) 执行一个查询,返回一个int值
qryString(Object...) 执行一个查询,返回一个字符串值
qryMapList(Object...) 执行一个查询,返回一个List<map<String,Object>>类型
qryMap(Object...) 执行一个查询,将第一行记录返回一个map<String,Object>类型
qryList(Object...) 执行一个查询,将第一列记录返回一个List<Object>类型
qryEntityList(Object...) 执行一个查询,第一个参数通常是实体类类型,返回一个实体列表
upd(Object...) 执行一个SQL, 等效于DbUtils的update方法,但不用捕获导常
ins(Object...) 执行一个insert SQL,等效于DbUtils的insert方法,但不用捕获导常
exe(Object...) 执行一个SQL, 等效于DbUtils的execute方法,但不用捕获导常
entityXxxx(Object...) 实体相关的一系列CURD方法,详见entity方法一节  

从5.0版起,jSqlBox删除了p、i、n、t、e开头的方法,只使用参数内嵌式风格为唯一书写SQL方式,e开头的方法改为entityXxxx形式,这样改进后方法更少,可读性和可维护性也更好。

本次5.0.1.jre8版更新内容:

  • 去除pinte系列方法, i系统方法改为qry/ins/exe/upd系列方法, e系列方法改为entity打头,t系列方法改为传入模板,p和n这两种写法因为很少用,所以直接取消,详见用户手册。5.0版不再兼容4.0版,精简和重命名是为了更好的发展,或者说胳膊扭不过大腿,不止一次被人抱怨这个p/i/n/t/e的命名了。
  • 去除DB.sql()方法,默认字符串都是SQl片段
  • 大写的PARAM、QUES、VALUESQUESTION方法去掉,大写的PARAM很少用到,而且容易与小写的param方法混淆。
  • PrintSqlhandler调试拦载器改进为可以输出参数代入后的完整SQL,方便粘贴到SQL工具里运行。例如本文开头的示例会输出为:
        insert into users (id, name, age, address) values(1, '张三', 10, '北京')
  • 添加DB.other、DB.when、DB.par、DB.que四个方法,DB.par等同于旧版的DB.param方法, DB.que等同于旧版的DB.ques方法。
  • 添加@UUID26注解
  • SQL参数和Java类型转换做成可配置,见jSqlBox配置一节
  • StrUtils工具类中的array静态方法,当条目为空时返为(null)
  • 新增DB.qryList方法,返回查询内容的第一行内容
  • 新增DB.qryMap方法,返回查询内容的第一列内容
  • 不再使用JDBPRO类,只保留DB一个静态工具类
展开阅读全文
11 收藏
分享
加载中
精彩评论
这说的经典了,谁有权听说的,不管对错
2020-10-23 17:11
1
举报
最新评论 (10)
像病毒
2020-10-28 00:06
0
回复
举报
jooq不香吗
2020-10-24 11:27
0
回复
举报
jooq太偏重于可重构的SQL,牺牲了可读性和可移植性,属于重新发明SQL。遇到复杂的子查询或几个括号嵌套条件的复杂SQL会死的很难看。jSqlBox不重新发明SQL,只是把参数写的位置移了一下而已。
在写jSqlBox之前我曾比较过现有的所有主要DAO工具,可以看一下供参考:https://gitee.com/zhoufox/jSqlBox
2020-10-24 11:57
0
回复
举报
反而更乱,还是没解决
2020-10-24 09:37
0
回复
举报
这个。。。。
2020-10-23 22:28
0
回复
举报
感觉更别扭了
2020-10-23 21:59
0
回复
举报
现在orm框架的一个通病是代码与orm框架多少都有耦合,不纯粹
2020-10-23 16:41
0
回复
举报
更多评论
11 评论
11 收藏
分享
返回顶部
顶部