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

内容简介：jSqlBox主要特点是架构优、尺寸小、功能全，基本上所有与数据库操作相关的功能，jSqlBox都已提供。它的主要特点有： 1.内核基于DbUtils并与之兼容，最差情况下可以降级当成DbUtils来使用，上手快。 2.提倡在java里...

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一个静态 工具 类

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

• 超参数搜索不够高效？这几大策略了解一下
• TypeScript 中的命名参数、可选参数、默认参数
• PXC状态参数与变量参数
• 更加灵活的参数校验，Spring-boot自定义参数校验注解
• AI新人必看 | 参数和超参数还分不清楚吗？
• 如何一条Mediainfo --Inform语句同时获取视频参数和音频参数多个Parameters

本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。