数据库动态查询最佳实现

上次发表过这样的介绍，可能是我介绍的不够准确，太多人都没有搞明白（挺郁闷，当然也请大家将文章能够看的仔细点，主要是提供针对hibernate查询不足的弥补（当然这种做法并不局限于hibernate，事实上是针对所有涉及数据库查询的一个非常好的解决办法，这里且拿hibernate说事），简单的查询可以不考虑如：User user=(User)this.getHibernateTemplate().get(User.class,id); this.getHibernateTemplate().find("from User where enable=?","") ，这些大家其实都差不多，还有就是hibernate的条件查询Criteria很多人都会就不特别说了）。

还是开门见山，本文所要介绍的是针对数据库查询(当然要是ldap什么的基于查询指令的都适用)，其目的在于将sql跟代码进行分离，同时能够保持sql或hql语句的

结构完整性（写sql的步骤一般是：在数据库客户端整理好sql并排版好放到代码中用stringbuffer拼起来，要是修改就痛苦了，还得调试代码得到整个语句），请好好看上面红色字的内容，平时是不是这样做的？如果是你就接着看下面的内容，对你帮助很大！

我们有很多时候数据库查询是由多个条件组合而成，但究竟是哪个条件是不确定的

展示一下效果：

 提示一下，本功能支持hql查询如（对于hql优点主要体现在查询条件不固定时，hql写法非常优雅）：

 <sql-query name="hr_searchOrganInfo">
  <![CDATA[
  from HrOrganInfo
  where 1=1
  #[and createDate>=? and createDate<=?]
  #[and ORGAN_NO like ?]
  #[and ORGAN_NAME like ?]
  #[and IS_ACTIVE=?]
 ]]>
 </sql-query>

 

本功能最大的优点在于动态查询条件时hql或sql的写法非常的优雅，结构完整！
 调用方法：

   sql语句可以是

   select * from t where t.name=:name and t.createDate>:createDate 不一定是？号，以解除使用者需要记住参数顺序

的烦恼。

1、分页调用

    通过反调映射到对象

    PaginationModel findPageByJdbc(final String sqlOrNamedSql,
   final String[] paramsNamed, final Object[] paramsObj,
   final PaginationModel paginationModel,
   final RowCallbackHandler rowCallbackHandler);

   

   直接返回数据库结果结合，不映射对象

   PaginationModel findPageByJdbc(final String sqlOrNamedSql,
   final String[] paramsNamed, final Object[] paramsObj,
   final PaginationModel paginationModel);

 

 

   直接转VO式调用

    PaginationModel findPageByJdbc(final String sqlOrNamedSql,
   final String[] paramsNamed, final Object[] paramsObj,
   final PaginationModel paginationModel, final Class voClass);

2、非分页反调方式

   直接数据库数据集合返回

   List findByJdbcQuery(final String sqlOrNamedSql,
   final String[] paramsNamed, final Object[] paramsValue);

 通过反调映射到对象

   List findByJdbcQuery(final String sqlOrNamedSql,
   final String[] paramsNamed, final Object[] paramsValue,
   final RowCallbackHandler rowCallHandler)

  直接转VO对象调用

   List findByJdbcQuery(final String sqlOrNamedSql,
   final String[] paramsNamed, final Object[] paramsValue,
   final Class voClass)

 hql的调用差不多：findByhql 或findPageByhql,参数当然就没有voClass和RowCallbackHandler了，因为hql返回的就是对象集合！

 

 

 工作机理很简单，就是通过判断参数是否为null（结合sql特性如is null）对sql语句进行重新整理，后台提供了convertSqlParams功能将特定值转为null

this.findPageByJdbc("hr_getOrganInfoByNo", null,new Object[] { organNO,this.convertSqlParams(status,"-1")}, pageModel,OrganInfoVO.class);

提供java反射器直接映射成VO，也提供了RowCallbackHandler通过反调方式生成VO，当然voClass和RowCallbackHandler都为null，就直接返回数组集合！

 

 

 

评论

46 楼 zhongxuchen 2009-04-09  

QuakeWang 写道

你还是需要继续解释的，光写一个嵌套语句在那边，我不太明白想要实现的结果是什么。
首先你的子查询少了一个右括号，我猜测正确的写法应该是？

select * from table_name t where 1 = 1
#[and t.status=:status
  and t.orderId=(select orderId
                 from t2 #[where t2.name like :name])]

其次，你需要解释一下，这种嵌套在不同参数下面会产生什么样的语句，比如：
1. 什么参数都不传，是产生A还是B?
A. select * from table_name t where 1 = 1
B. select * from table_name t where 1 = 1 and t.orderId=(select orderId from t2)

2. 如果只传递了name参数，是产生前面中的A还是这样：
select * from table_name t where 1 = 1 and t.orderId=(select orderId from t2 where t2.name like :name)

3. 只传递status参数又会产生什么样的语句？

其实我想弄明白的是这个嵌套对于外层和内层的变量是否有优先级，比如外层的变量没有传递，是否就不管内层所有的语句？

不论你选择的是那种策略，还是可以用等价的and/or开头，换行结尾来替换：

select * from table_name t where 1 = 1
and t.status = :status
and t.orderId = (select orderId from t2 where 1 = 1 
and t2.name like :name
)

select * from table_name t where 1 = 1
and t.status = :status
and t.orderId = (select orderId from t2 where 1 = 1 and t2.name like :name)

你说的没错，是少了一个括号，随便写个例子大意了！
如果status为null，就会是
select * from table_name t where 1 = 1
如果status不为null，name为nul则
select * from table_name t where 1 = 1
and t.status=:status
  and t.orderId=(select orderId
                 from t2)
这些可以根据你实际的情况自己决定怎么做，用换行我想肯定是做不到的

45 楼 QuakeWang 2009-04-09  

你还是需要继续解释的，光写一个嵌套语句在那边，我不太明白想要实现的结果是什么。
首先你的子查询少了一个右括号，我猜测正确的写法应该是？

select * from table_name t where 1 = 1
#[and t.status=:status
  and t.orderId=(select orderId
                 from t2 #[where t2.name like :name])]

其次，你需要解释一下，这种嵌套在不同参数下面会产生什么样的语句，比如：
1. 什么参数都不传，是产生A还是B?
A. select * from table_name t where 1 = 1
B. select * from table_name t where 1 = 1 and t.orderId=(select orderId from t2)

2. 如果只传递了name参数，是产生前面中的A还是这样：
select * from table_name t where 1 = 1 and t.orderId=(select orderId from t2 where t2.name like :name)

3. 只传递status参数又会产生什么样的语句？

其实我想弄明白的是这个嵌套对于外层和内层的变量是否有优先级，比如外层的变量没有传递，是否就不管内层所有的语句？

不论你选择的是那种策略，还是可以用等价的and/or开头，换行结尾来替换：

select * from table_name t where 1 = 1
and t.status = :status
and t.orderId = (select orderId from t2 where 1 = 1 
and t2.name like :name
)

select * from table_name t where 1 = 1
and t.status = :status
and t.orderId = (select orderId from t2 where 1 = 1 and t2.name like :name)

44 楼 zhongxuchen 2009-04-08  

QuakeWang 写道

zhongxuchen 写道

第一：and和换行这就严重约束了sql语句的写法，必须严格按照你的约定来换行，否则就完蛋。其实你根本就没有考虑一些特殊问题比方 #[t.date>=:begindate and t.date<=:enddate],这个只要有一个为null整体都得拿掉，还有很多类似的问题，你的做法限制过于苛刻，这样用反而成了累赘。

你没有明白我说的意思，具体来说：
你这个例子在开头没有and 或者 or，是不正确的查询，完整的应该类似：

select * from table_name t where 1 = 1
#[and t.date >= :begindate and t.date <= :enddate]
#[or t.status = :status]

按照我建议的更简化约定来写，就需要写成

select * from table_name t where 1 = 1
and t.date >= :begindate and t.date <= :enddate
or t.status = :status

这个lib在读到以and和or开头的行，就可以进行是否要动态拼接的判断，和你用#[]来判断是一样的，你说的整体拿掉，原先代码是拿掉#[]之间的，这里是拿掉整行，也是等价的。
而好处是更简化：先在sql客户端进行复杂的组合sql调试，然后复制过来按这个约定进行排版，无需在每行开头加#[，在结尾加]。
缺点有一些，比如有固定参数的，我们得先写在一行，或者加个空格：

select * from table_name t where 1 = 1 and t.date > '20000101'
and t.date >= :begindate and t.date <= :enddate
 and t.status <> 'delete'

但是正如你所说，能够让95%的代码写起来更轻松，另外5%加一些限制也无所谓，这只是看如何取舍的问题。

我的#[]支持嵌套，sql或hql可以写成这样
select * from table_name t where 1 = 1
#[and t.status=:status
  and t.orderId=(select orderId
                 from t2 #[where t2.name like :name]]
and t.date>=:date

#[]只需要在需要判断为null的地方加，并不一定所有的地方都要加！
用换行是否可以呢？我决定不想再解释我的这个东西了！

43 楼 zhongxuchen 2009-04-08  

elam 写道

42087743 写道

越来越不了解javaeye的大牛了,是不是确实自己太牛了......
这种帖子被评为新手帖,确实是太过分了,你们应该明白逛javaeye的大部分人都没有你们那么牛,所以请你们手下留情点吧.....
人家辛辛苦苦地写了那么多,你们评为新手帖,确实是太打击人的积极性了,这样下去,javaeye的技术论坛不会再有人发帖子了,大家都去泡海阔天空吧

不是JE的大牛都太牛了,是这个本不算创新,在JE发技术帖最好先确定要发的这个东西是不是新课题.
而说成是什么陈氏什么什么法,有些言过其实了.

怎么才算创新呢？之前有类似简洁的做法吗？首先要说明的是我绝对没有借鉴JE上的任何人的做法，我的想法来自ibatis（去阿里面试时以前同事介绍ibatis时突然让我产生了灵感）以及对大家编程过程中的如何简化的思考！
我想有时候微小的进度都是巨大的进步，你不信在我基础上你看还能有多少改进，而相对之前的JE上人的做法我这个进度又是多大，就好比cpu一样core2比core1按你说也不是什么创新，因为还是cpu呀，哈哈！
我不太想回复了！你们要是觉得在没有看我这个之前有更简单的做法你就露一手让大家看看，我可以自信的说，没有！不要总吹嘘了，好就是好，不好就是不好，给句公道话，不服就把所谓你们先进的做法拿出来让大家评，请用事实说话！
所以我说是陈氏查询一点都不过份！现实是我们团队用了这个代码简化了很多，维护起来也方便了，我们原先的做法其实并不比大家的差，但改进的感觉是巨爽的，如果对每个团队都有这样的提升，命名为陈氏查询又有何过之有，有什么不妥呢，这是对原创的尊重！

42 楼 xiquwgugou 2009-04-08  

大家都是报复性质的投新手帖啊！

41 楼 elam 2009-04-08  

42087743 写道

越来越不了解javaeye的大牛了,是不是确实自己太牛了......
这种帖子被评为新手帖,确实是太过分了,你们应该明白逛javaeye的大部分人都没有你们那么牛,所以请你们手下留情点吧.....
人家辛辛苦苦地写了那么多,你们评为新手帖,确实是太打击人的积极性了,这样下去,javaeye的技术论坛不会再有人发帖子了,大家都去泡海阔天空吧

不是JE的大牛都太牛了,是这个本不算创新,在JE发技术帖最好先确定要发的这个东西是不是新课题.
而说成是什么陈氏什么什么法,有些言过其实了.

40 楼 75468850 2009-04-08  

42087743 写道

越来越不了解javaeye的大牛了,是不是确实自己太牛了......
这种帖子被评为新手帖,确实是太过分了,你们应该明白逛javaeye的大部分人都没有你们那么牛,所以请你们手下留情点吧.....
人家辛辛苦苦地写了那么多,你们评为新手帖,确实是太打击人的积极性了,这样下去,javaeye的技术论坛不会再有人发帖子了,大家都去泡海阔天空吧

确实委屈了楼主，楼主也是为大家分享，提出自己的见解，还是可以借鉴学习的！新手贴台委屈了！

39 楼 42087743 2009-04-08  

越来越不了解javaeye的大牛了,是不是确实自己太牛了......
这种帖子被评为新手帖,确实是太过分了,你们应该明白逛javaeye的大部分人都没有你们那么牛,所以请你们手下留情点吧.....
人家辛辛苦苦地写了那么多,你们评为新手帖,确实是太打击人的积极性了,这样下去,javaeye的技术论坛不会再有人发帖子了,大家都去泡海阔天空吧

38 楼 justshare 2009-04-07  

QuakeWang 写道

大家在这篇2年前的帖子中讨论过各种类似做法：
http://www.iteye.com/topic/58834
你提出的想法是在约定大于配置上更进一步，少去了一些tag，如果更进一步，连#[]都可以省略，可以and开头，换行结尾作为匹配：

from HrOrganInfo where 1=1
and createDate>= ? and createDate<= ?
and ORGAN_NO like ?
and ORGAN_NAME like ?
and IS_ACTIVE = ?

啊，这个头像哪来的？
跟我外SHEN好像啊

37 楼 魔力猫咪 2009-03-29  

参考楼主的思想，动态查询语句处理框架“仓库猫”正式诞生。我在googlecode上开了项目，英文名“catsorage”。目前0.1测试版的源代码已经上传。因为我使用了google-json库，为了避免麻烦，决定使用和google-json相同的Apache 2.0许可证，反正这个和LGPL也差不多，好像和商业更亲和。
但是因为使用说明还没有编写好，所以暂时不提供直接下载。
楼主的大名和博客地址我已经放到首页了。

36 楼 魔力猫咪 2009-03-29  

就差最后的测试了。如果测试没问题的话，我写完帮助就可以拿出来让大家试用了，决定采用LGPL许可证。

35 楼 魔力猫咪 2009-03-29  

zhongxuchen 写道

魔力猫咪 写道

报告大家一个好消息。根据楼主的思想，我编写的小工具快完成了。如果最后完成，希望大家指证。

我希望在你的小工具上声明一下借鉴陈式查询，其实我本希望将我的东西拿出来给大家共享的（因为公司部分原因我只给大家展示了基于hibernate的实现，我在我的sagacity中已经独立出来成为一个小插件形式提供给spring的集成），因为我们的东西一直在用，已经相对完善了，受此打击还是算了（竟然被评为新手贴！），哈哈！

好的我会在工具的Redme中写出是借鉴了你的思想。并给出到你的博客的链接http://zhongxuchen.iteye.com/。

34 楼 zhongxuchen 2009-03-29  

魔力猫咪 写道

报告大家一个好消息。根据楼主的思想，我编写的小工具快完成了。如果最后完成，希望大家指证。

我希望在你的小工具上声明一下借鉴陈式查询，其实我本希望将我的东西拿出来给大家共享的（因为公司部分原因我只给大家展示了基于hibernate的实现，我在我的sagacity中已经独立出来成为一个小插件形式提供给spring的集成），因为我们的东西一直在用，已经相对完善了，受此打击还是算了（竟然被评为新手贴！），哈哈！

33 楼 魔力猫咪 2009-03-29  

报告大家一个好消息。根据楼主的思想，我编写的小工具快完成了。如果最后完成，希望大家指证。

32 楼 zhongxuchen 2009-03-28  

算了不扯淡了，给大家介绍一些其它的吧，这篇就到此为止了：
动态数据源：
http://www.iteye.com/topic/334788
数据库SEQUENCE
http://www.iteye.com/topic/335565

31 楼 zhongxuchen 2009-03-28  

zhenjia 写道

zhongxuchen 写道

首先解释一下，这个工具并不是只针对sql，是支持hql的，
而且hql或sql可以这么写
from OrganInfo as t
where 1=1
      #[and t.name like ?]
       and t.enable=?
就是说#[]相当于if，只需在需要判断的地方增加#[];

1.做点改善吧#[]应该去，不然把SQL COPY进去的时候 还要去写#[] 够麻烦的
2.只是简单的null判断？如果不支持复杂的判断，那应该努力。
3.对于这样的动态SQL 我的实现。

@Transactional(type = TransactionType.READ_ONLY)
	@FinderByCriteria(entity = WfReq.class,
 restriction = {@Restriction(column = "lockYn", value = "Y", condition = Condition.EQ) },
 orderBy = { @OrderBy(column = "sendDate", isAsc = false) })
	public Page<WfReq> getPageListBySend(@FirstResult int start,
			@MaxResults int limit,
			@SearchModeList List<SearchMode> searchModeList) {
		return null;
	}

无SQL 基于QBC annotation声明查询方式 动态 静态 排序 等等，
searchModeList 传入你所谓的#[]#[]#[]#[] 自动判断。并且判断支持非null以外的其他逻辑判断
我只管仍有可能用到的查询参数，其他不管。

在我们的BaseDAOSupport中提供了Criteria查询，hql查询，同时也提供了jdbc查询，简单的用Criteria查，
表多了关系复杂了就用sql，
谢谢提醒，已经提供了任何参数的判断，在传参数时new Object[]{this.convertSqlParams(obj,"-1")}单个转，也可以是this.convertSqlParams(objs),默认是转"",也可以是this.convertSqlParams(objs,new Object[]{-1,""}),多个转，给项目组用的第一天就提出来了，本来让他们自己转null的，后来还是提供了一些方法，提供了convertSqlParams(obj,new Object[]{对照值1,对照值2})，提供对多个对象转null和多个值对比如-1什么的。还提供lessThen，between，moreThen对比函数。
#[]何尝不想去，个别地方要增加这个东西是烦一点，但提供一个约定方便后来维护者知道那里到那里是要通过null判断去掉的。同时sql写起来不需要过多排版约束，其实这是一个折中的处理！

30 楼 zhongxuchen 2009-03-28  

downpour 写道

真的不知道我是不是在同一个火星人说话。

这年头，使用hbm.xml的人已经少之又少，为什么？因为不乐意在维护一个entity的同时，再去维护一个繁重的xml。所以出现了annotation，出现了约定大于配置。但是你目前的思路还是要维护配置文件，你可以不在你自定义的配置文件中写你的SQL或者HQL，但是你目前的思路基本上还是处于需要有某处地方来维护你的SQL或者HQL语句，这就带来了很大的维护成本，在我看来，这的确就是一种落伍的思想方式。

你的这个工具，请允许我称之为工具，放在2年前，可能会被评为良好贴或者给别人提供一个很好的思路，但是现在再拿出来，还到处宣称自己有多么多么优秀，是不合适的。我想指出的，只是一个事实，你的东西没有你想象中那么优秀，你的语言过激了。如果你想讨论，可以看看之前很多人的实现方式，然后改进你的方案，别人拿不拿别人的方案出来分享，是别人的自由，你总是质疑别人拿不出比你更好的方案来，你为什么不想一想，别人为什么要拿出他的方案来和你讨论呢？

工具和框架是给人用的，自己用的舒服就成，你可以分享给大家，但是不能强迫大家都说你的东西好。Javaeye的论坛一向没有所谓的高手，不过这里至少还有一些敢于提出批评意见的人，能够给出相对客观的评价。

BTW 如果你的东西真的有你吹嘘的那么好，那么具有独创性。就不会有那么多朋友给你投新手帖了。

讨论就此结束，不再做任何回复。

哈哈，第一我没有说我这个不是工具呀，我上升到框架推荐给别人了吗？现在感觉一定通过annotation方式就是先进，你就感觉别人不知道，我们是因为客户现场环境一般要求基于jdk1.4而已，所以在小项目中用jdk1.5但在实际项目中我们目前是要求尽可能以1.4为要求，还有用annotation和xml其实并不重要只是习惯而已，项目的目的在于选用合适的架构和技术追求的是稳定和功能，在这个过程中能够然开发者开发更简单便捷很重要，同时还要考虑项目的风险，技术的应用要跟项目及项目团队人员状况相匹配，当然也要考虑公司技术的延续性统一性。
   我就是搞不懂，用一个annotation就标榜自己先进(这个东西知道的人很多，就好比我开始发表这个文章时，有一个说有一个做法:EO eo=(EO)this.get().非要写成String query="from EO where id=?" 一样，别人是不知道this.get()吗？是因为别人不是在讨论这个问题)，但我不想谈所谓先进，我所要体现的是sql(或hql)维护以及使用的简洁，再说你根本就没有看清楚我的文章，我的做法是sql写在配置文件或以string 方式传递都是可以的，你说的是覆盖的，我给开发者提供了放在文件和代码中的两种可能，因为findByJdbcQuery(String namedQueryOrSql ,我会首先判断namedQueryOrSql 是否直接是一个查询语句，如果不是则作为一个Map key去查对应的sql。我说过我的东西不是给大家带来使用的麻烦，而是作为一个选择,是完全非侵入式的，不强迫你改变以往的习惯！
   哈哈，算了，搞成了推荐一个东西给大家遭到如此打击!
 

29 楼 zhenjia 2009-03-28  

zhongxuchen 写道

首先解释一下，这个工具并不是只针对sql，是支持hql的，
而且hql或sql可以这么写
from OrganInfo as t
where 1=1
      #[and t.name like ?]
       and t.enable=?
就是说#[]相当于if，只需在需要判断的地方增加#[];

1.做点改善吧#[]应该去，不然把SQL COPY进去的时候 还要去写#[] 够麻烦的
2.只是简单的null判断？如果不支持复杂的判断，那应该努力。
3.对于这样的动态SQL 我的实现。

@Transactional(type = TransactionType.READ_ONLY)
	@FinderByCriteria(entity = WfReq.class,
 restriction = {@Restriction(column = "lockYn", value = "Y", condition = Condition.EQ) },
 orderBy = { @OrderBy(column = "sendDate", isAsc = false) })
	public Page<WfReq> getPageListBySend(@FirstResult int start,
			@MaxResults int limit,
			@SearchModeList List<SearchMode> searchModeList) {
		return null;
	}

无SQL 基于QBC annotation声明查询方式 动态 静态 排序 等等，
searchModeList 传入你所谓的#[]#[]#[]#[] 自动判断。并且判断支持非null以外的其他逻辑判断
我只管仍有可能用到的查询参数，其他不管。

28 楼 魔力猫咪 2009-03-28  

居然成了新手贴确实太冤枉。我觉得这种想法还是不错的。就算是思想落后了，你举出实际代码反驳就是了。
我认为如果输入参数固定的查询语句，用JPA的预定义语句就可以了，这个想法没有用处。但是参数动态的问题可没有直接的解决办法。一个语句如果参数不定的话，就会出现大量的拼语句或者每种情况一个方法。用楼主提出的方法我觉得可以解决大量人工拼的方法，让软件自动帮我们拼。
用配置文件维护的问题不要上来就认为落后。JPA的预定义语句无论你是在注释还是在配置文件里维护总归都要维护。有人认为预定义语句应该和类本身分开，这样更好维护。原来的hbm.xml是你既要维护代码又要维护配置，所以麻烦，但是现在只需要维护一处，怎么会增加成本呢？

27 楼 downpour 2009-03-28  

真的不知道我是不是在同一个火星人说话。

这年头，使用hbm.xml的人已经少之又少，为什么？因为不乐意在维护一个entity的同时，再去维护一个繁重的xml。所以出现了annotation，出现了约定大于配置。但是你目前的思路还是要维护配置文件，你可以不在你自定义的配置文件中写你的SQL或者HQL，但是你目前的思路基本上还是处于需要有某处地方来维护你的SQL或者HQL语句，这就带来了很大的维护成本，在我看来，这的确就是一种落伍的思想方式。

你的这个工具，请允许我称之为工具，放在2年前，可能会被评为良好贴或者给别人提供一个很好的思路，但是现在再拿出来，还到处宣称自己有多么多么优秀，是不合适的。我想指出的，只是一个事实，你的东西没有你想象中那么优秀，你的语言过激了。如果你想讨论，可以看看之前很多人的实现方式，然后改进你的方案，别人拿不拿别人的方案出来分享，是别人的自由，你总是质疑别人拿不出比你更好的方案来，你为什么不想一想，别人为什么要拿出他的方案来和你讨论呢？

工具和框架是给人用的，自己用的舒服就成，你可以分享给大家，但是不能强迫大家都说你的东西好。Javaeye的论坛一向没有所谓的高手，不过这里至少还有一些敢于提出批评意见的人，能够给出相对客观的评价。

BTW 如果你的东西真的有你吹嘘的那么好，那么具有独创性。就不会有那么多朋友给你投新手帖了。

讨论就此结束，不再做任何回复。