开创性的陈氏数据库动态查询设计

zhongxuchen

浏览: 32473 次
性别:
来自: 上海

最近访客更多访客>>

cqh520llr

kejiangwei

fan0128

leimingchao

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

SAGACITY(睿智)框架讲坛

设计模式数据结构 iBATIS SQL Hibernate

(新版增加了直接结果映射VO的功能无需再写rowcallbackhandler了，集成ehcache、memcached提高处理效率)

犹豫了很久，还是决定将自己的东西（sagacity睿智开发框架）的一部分逐步奉献给大家，首先申明一下版权问题，请注明“陈氏查询”！

进入正题：

首先介绍一下sagacity(睿智)框架：其包括快速原型的页面框架(这里面也有一个非常独到人性化的界面的设计)，sagacity-core核心代码库提供针对企业应用开发所需要的工具类和组件，sagacity-toolkit 工具库，包括快速原型项目生成，excel导入工具（用于项目试运行阶段数据初始化）；sagacity-struts：基于struts2+spring+hibernate+sitemesh+spring security技术以实际企业开发项目为背景非常实用演示项目。sagacity项目从实际项目出发，贯穿整个项目周期提供优雅的实现以及从开发人员的角度着眼简化开发难度！这里不一一而谈。

第一讲：陈氏查询

先抛一个问题：大家平时做数据库查询时查询条件很多也不定时怎么写hql以及sql语句？

比如：

     queryStr.append("select * ");
     queryStr.append("from OA_CAR_REGIST t ");
     queryStr.append("where 1=1 ");

if(CarRegiestVO.isActive!=null)
queryStr.append(" and t.IS_ACTIVE is ? ");

if(CarRegiestVO.beginDate!=null && CarRegiestVO.endDate!=null)
queryStr.append(" and t.REGIST_DATE>=? and t.REGIST_DATE<= ? ");

     if(CarRegiestVO.carMode!=null)
          queryStr.append("      and t.CAR_MODE like "+carMode+"%");
     sql结构非常混乱，从数据库客户端写好的sql语句还得用stringBuffer给拼接起来，以后要是改写一下语句还得debug程序得到最后的语句，太累了！

我们的做法：

我们的项目基于hibernate的，如下基于hibernate NamedQuery扩展。

     <hibernate-mapping>
<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>

<sql-query name="hr_loadOrganTree">
<![CDATA[
  select b.ORGAN_NO,b.ORGAN_NAME,b.PRE_ORGAN_NO,0 AS TYPE
  from HR_ORGAN_INFO b
  where 1=1
  #[and b.ORGAN_NO=?]
  #[and b.IS_ACTIVE=?]
  union
  select b.ORGAN_NO,b.ORGAN_NAME,b.PRE_ORGAN_NO,0 AS TYPE
  from HR_ORGAN_INFO a,HR_ORGAN_INFO b
  where 1=1
  #[and a.ORGAN_NO=b.PRE_ORGAN_NO and (a.ORGAN_NO=? or b.PRE_ORGAN_NO=a.ORGAN_NO)]
  #[and b.IS_ACTIVE=?]
]]>
</sql-query>
对于不定的查询条件以#[和]标记对，支持嵌套

我们的调用过程：

/**
* 分页方式查询机构信息
* @param organInfoVO
* @param pageModel
* @return
* @throws Exception
*/
public PaginationModel searchOrganInfo(OrganInfoVO organInfoVO,
   PaginationModel pageModel) throws Exception {
  return this.findPageByhql("hr_searchOrganInfo", null, new Object[] {
    organInfoVO.getBeginDate(),
    organInfoVO.getEndDate(),
    organInfoVO.getOrganNo(),
    organInfoVO.getOrganName(),
    organInfoVO.getIsActive() },
    pageModel);
}

在我们BaseDAOSupport中提供findPageByHql以及其它大量针对数据库查询的方法，在方法调用时只需要将参数完整化的以对象数组（或List），对于如状态选择：-1表示全部等特性，以及blank值的处理，我们提供了convertSqlParams(objects),convertSqlParams(obj,new Object[-1,""]),判断对象为空或-1则转为

null，用法：

return this.findPageByhql("hr_searchOrganInfo", null, new Object[] {
    organInfoVO.getBeginDate(),
    organInfoVO.getEndDate(),
    organInfoVO.getOrganNo(),
    organInfoVO.getOrganName(),
    convertSqlParams(organInfoVO.getIsActive() ,-1)},
    pageModel);
}

当然也可以批量转(转""为null)

return this.findPageByhql("hr_searchOrganInfo", null, convertSqlParams(new Object[] {
    organInfoVO.getBeginDate(),
    organInfoVO.getEndDate(),
    organInfoVO.getOrganNo(),
    organInfoVO.getOrganName(),
    organInfoVO.getIsActive() }),
    pageModel);
}

实现原理很简单（个人觉得这只是一个idea不属于技术范畴，说穿了谁都能实现只是实现的代码简繁而已），判断#[]中的问号所对应的参数是否为null（当然考虑like 和is，like将值直接替换进去，is 判断数据类型是否为null以及boolea类型，不是则将#[]内容切除），为null将#[]内容切除，是则将#[和]标记用""替换掉，这样整体

代码实现起来优雅简单，sql语句的可读性和可维护性大大增强！

当然你会找出一个极其变态难处理的sql整合（目前我们项目中还没有遇到），我们这个东西怎么做呢？其实

findPageByhql("hr_searchOrganInfo",param..param),第一个参数既可是是NamedQuery也可以是sql(hql)语句,自己组合咯，特殊问题特殊对待。

sagacity的宗旨在于不强迫你改变你的习惯，考虑特殊情况，为开发提供方便！

sagacity代码这几天整理一下，重新划分一下，将标签部分独立出来，减少耦合性将尽快对大家提供后台开发所需要的代码！

期待第二讲吧！

谢谢支持！

另外对于参数位置不容易记问题，其实我们这边是提供了

/**
* @todo 分页查询
* @param hql
* @param paramNames
*            ：参数名称
* @param params
*            :参数值
* @param paginationModel
*            :分页模型,含页号
* @return
*/
protected PaginationModel findPageByhql(final String hqlOrNamedQuery,
   final String[] paramNamed, final Object[] paramsValue,
   final PaginationModel paginationModel) {

}

paramNamed 参数方式的。

哈哈，说一下ibatis，ibatis确实能够实现类似的功能，但个人觉得没有这个灵活和优雅，下面发一张图，ibatis多条件动态查询无法让sql看起来如此优雅整洁：

再对比一下ibatis的做法：

<statement id="someName" resultMap="account-result" >
select * from ACCOUNT
<dynamic prepend="where">
<isGreaterThan prepend="and" property="id" compareValue="0">
ACC_ID = #id#
</isGreaterThan>
<isNotNull prepend=”and" property="lastName">
ACC_LAST_NAME = #lastName#
</isNotNull>
</dynamic>
order by ACC_LAST_NAME
</statement>

效果一目了然了，代码多，还破坏了sql的整体性

查看图片附件

分享到：

动态数据源 | Struts2源码阅读疑问

2009-02-17 11:19
浏览 3606
评论(34)
论坛回复 / 浏览 (33 / 11769)
分类:非技术
查看更多

34 楼 zhongxuchen 2010-02-03

anky_end 写道

zhongxuchen 写道

另外要知道什么叫简单就是美！从效果上你可以一眼看出我的这个sql的简单，调用也简单，难道一定是ibatis就是好吗?其实我强调的是想法，而非技术，我们现在项目中就这么用的，应用过程就一个字：爽！

^_^，不好意思，个人看法还是ibatis看起来更简单。

当然论直管可能到是你的写法更直观些。

ibaits实际上把全部判断逻辑集成在配置文件里面。
而你似乎是用代码分担部分判断逻辑功能。

另外ibatis传入参数也极为灵活。

如果论思想，两者本质差不多吧，就是通过配置文件动态拼接sql，在ibatis之前俺们也有搞过个类似的，做的还不如你。

我的看法嘛，不是贬低你，而是认为，如果要使用这类动态sql功能，不如直接上ibaits来的方便简单。

哈哈，无所谓贬不贬低了，其实判断并不是在每个代码中去判断，后台不会提供baseDaoSuppert吗，继承一下调用就一句话了，ibatis的写法不得在sql中增加
<if>等标记吗? sql一眼根本就看不明白，尤其大sql，那也叫简单，如果要修改呢，我们这个就是直接copy出去放到客户端工具上剔除一下#[]就可以，而ibatis可以吗？你想想写sql和改sql的过程就知道了

33 楼 anky_end 2010-02-03

zhongxuchen 写道

32 楼 zhongxuchen 2010-02-03

31 楼 zhongxuchen 2010-02-03

anky_end 写道

大致看了下，楼主自己也认为类似ibatis

我认为ibatis的优点在于：
1、命名替换，也就是说你不必关心参数的顺序，只要匹配好参数的key就行
2、更加动态化，比如select * from $tabname$,甚至可以整个sql语句。
3、规范化，作为一个已经被广泛认可的框架，文档齐全，上手容易。

我没看出楼主写的配置文件比ibaits的sql文件优雅到哪儿去，这个算是个人喜好问题。

另外ibaits基本也考虑到sql语句执行的方方面面，例如插入语句后返回主键，存储过程的调用，批量语句的优化等等，另外在和spring结合上也做的够好。

楼主的轮子不比ibaits高明，没有明显的优势啊。

如果仅仅就一条sql配置看起来比ibaits更优雅，是无法服众的

<sql id="dy_searchRiskWarn">
<value><![CDATA[
SELECT T.RISK_NO, T.SUBJECT,T.BIZ_DATE,T.BANK_NO,T.BANKDIST,T.ACTIVE_FLAG,
(SELECT D.DICT_VALUE FROM SYS_DICT_DETAIL D WHERE D.TYPE_CODE=:impDegreeConst AND D.DICT_KEY=T.IMP_DEGREE) IMP_DEGREE_VALUE,
(SELECT COUNT(M.POSTIL_NO) FROM SYS_MASTER_POSTIL M WHERE M.BIZ_TYPE=:functionType AND M.REFRENCE_NO=T.RISK_NO) POSTIL_COUNT,
(SELECT COUNT(T1.ATTITUDE_NO) FROM dbo.ISP_INSPECT_ATTITUDE T1 WHERE T1.TRANS_TYPE=:transTypeConst AND T1.TRANS_OBJ=T.RISK_NO) ATTITUDE_COUNT,
(SELECT P.BANK_ALIAS FROM PL_BANK_INFO P WHERE P.BANK_NO =T.BANK_NO) AS BANK_NAME,
ISP.ATTITUDE_NO,ISP.SUBJECT AS INSPECT_ATTITUDE_SUBJECT
FROM dbo.DY_RISK_WARN T LEFT JOIN ISP_INSPECT_ATTITUDE ISP ON T.ATTITUDE_NO = ISP.ATTITUDE_NO
WHERE 1=1
#[AND T.BANKDIST IN (:bankdistString)]
#[AND T.BANK_NO IN (:bankNos)]
#[AND T.IMP_DEGREE=:impDegree]
#[AND T.BIZ_DATE >=:beginDate]
#[AND T.BIZ_DATE <=:endDate]
#[AND T.SUBJECT LIKE :subject]
#[AND T.ACTIVE_FLAG=:activeFlag]
#[AND :bizType IN (SELECT B.BIZ_TYPE FROM DY_RISKWARN_BIZ_TYPE B WHERE B.RISK_NO=T.RISK_NO)]
ORDER BY T.ACTIVE_FLAG DESC,T.BIZ_DATE DESC,T.IMP_DEGREE DESC,T.RISK_NO DESC
]]></value>
</sql>
谁说不能用命名替换了，里面写注释都可以，和spring的集成那都是小问题，我这个只提供
SqlToyResult queryParam = sqlToyContext.getSql(sqlOrNamedSql,
paramsNamed, paramsValue);
logger.debug("findByJdbcQuery=" + queryParam.getSql());
final Object[] params = queryParam.getParamsValue();
通过参数结合sql处理成最终结果，最终的查询你随便用什么，因为这个贴子是我最早的用法，现在的已经完全改变了
return this.findPageByJdbc("dy_searchBankFlare",
new String[] {
"bankdistString", "bankdist", "functionType", "bankString",
"beginDate", "endDate", "subject", "activeFlag", "bizType",
"impDegreeConst", "impDegree" }, new Object[] { inStr,
SqlUtil.filterBlank(bankFlareVO.getBankdist()),
SystemConstants.FunctionType.BANK_FLARE,
SqlUtil.filterBlank(bankString),
SqlUtil.filterBlank(bankFlareVO.getBeginDate()),
SqlUtil.filterBlank(bankFlareVO.getEndDate()),
SqlUtil.filterBlank(bankFlareVO.getSubject()),
SqlUtil.filterEqual(bankFlareVO.getActiveFlag(), -1),
SqlUtil.filterEqual(bankFlareVO.getBizType(), "-1"),
SystemConstants.DataDict.IMP_LEVEL,
SqlUtil.filterEqual(bankFlareVO.getImpDegree(), "-1"), },
model, BankFlareVO.class);

30 楼 anky_end 2010-02-03

29 楼 zhongxuchen 2010-02-03

yuanhuiwu 写道

楼主，ibatis 3 相对 ibatis 2，写法就更简单了，不防看一下。另外，何必“重复造轮子”，把你的
<hibernate-mapping>
<sql-query name="hr_searchOrganInfo">
这个文件，直接使用ibatis的xml代替。你只需要集成ibatis的精华到你的项目就可以使用：

使用ibatis 3 把 sql 写在 xml中，在你的dao中传入 “参数”，然后叫ibatis:"喂，大哥，我给你参数了，你返回一条动态的sql给我，记得把我这条sql语句相对应的参数也返回给我"。Ibatis二话没说满足你的要求。

如你写的ibatis 2

<statement id="someName" resultMap="account-result" >
select * from ACCOUNT
<dynamic prepend="where">
<isGreaterThan prepend="and" property="id" compareValue="0">
ACC_ID = #id#
</isGreaterThan>
<isNotNull prepend=”and" property="lastName">
ACC_LAST_NAME = #lastName#
</isNotNull>
</dynamic>
order by ACC_LAST_NAME
</statement>

传入的参数Accout类的id="1"，
得到的sql如sql = "select * from ACCOUNT where ACC_ID = ?"
返回的参数只有Object[]{"1"}

这样，ibatis的精华你已经到手(动态sql,动态参数)，于是你自然而然的，地球人都知道，在dao(hibernate,jpa,...)中，类似使用PrepareStatement，将 ? 代换吧

你好像是没有看懂我说的是什么，条件固定了那用ibatis和hibernate都很容易实现，再看看我说的动态参数什么意思！不是将？替换值，而是
select * from ACCOUNT where ACC_ID = ? 当页面上没有传来id时，sql为
select * from ACCOUNT 就没有where ACC_ID = 这部分了，页面很多条件查询时有些条件未必选，不选就不用对这个条件进行查询了，如
select * from table a=？ and b=？ and c=？
当页面上只传a过来，sql就应该是 select * from table a=? 你说
select * from table a=？ and b=？ and c=？还能用吗？
哎！现在人都是浮躁，就不能看清楚吗？

28 楼 yuanhuiwu 2010-01-15

27 楼 tobackfurture 2009-09-01

不错!曾经也有这样的困惑,为了把不确定的因素考虑进来,在查询的时候动用了类似
if(...){.....}
else if (...){
...
}else if
.......
操作起来很麻烦,而且查询的层次不清,不过鉴于项目时间问题没考虑其它的只管实现功能了说,见天借鉴楼主的这种方法,感觉挺好的,谢谢分享!

26 楼 wqq686 2009-04-11

说实话，没觉得这个“陈氏查询”有什么开创性....不就对ibatis换了个表达方式吗？何来开放式之有？

25 楼 zhongxuchen 2009-03-21

wjx 写道

lz的这个思路很不错，准备借鉴。

ls各位好像都有点没明白lz的动态参数的意思啊？

这个模式的方便之处就是将sql或hql分离出了业务方法，放到外面，这样看起来也更加优雅，看lz的使用方式也确实方便很多。

至于“陈氏”这个命名，lz显然指的是这种思路，至于这种思路是基于hibernate还是直接的sql并不重要

哈哈，算有一个明白的，但太多人还是回答的那么的幽默。

24 楼 wjx 2009-03-20

23 楼 hite 2009-03-19

没听过hirbernate叫tom式数据库操作框架！：：S
或者jack式mvc。。。。。。。。。。。。。。。。。。。。

22 楼 zhongxuchen 2009-03-19

fireflyc 写道

List<QueryCondition> params = new ArrayList<QueryCondition>();
params.add(new QueryCondition(QueryCondition.LIKE, new QueryParam("title", "%"+title+"%")));
params.add(new QueryCondition(QueryCondition.LIKE, new QueryParam("author", "%"+author+"%")));
params.add(new QueryCondition("addTime",  QueryCondition.BETWEEN, new QueryParam("startTime", startTime), new QueryParam("endTime",
					endTime)));
ResultObject<Page<Topic>> result = topicService.findTopicByPage(RequestUtils.getPageNo(request), params);

我觉得这样的查询代码比起外部文件和复杂的用法来说更方便一些吧。。。。。。这个返回的数据类型相当于数据源，可以直接在页面上绑定到分页表格中。。（恩，是asp.net的风格)
呵呵。。莫怪，写写而已。。。

你这个是简单对象的查询用hibernate 中的条件查询不就可以了，还更简单呢，我说的是sql语句，好几个表嵌套关联的(什么字表拉，join拉、union拉)，你这个可以这样写？而且你这个是参数固定的，
如：
params.add(new QueryCondition(QueryCondition.LIKE, new QueryParam("title", "%"+title+"%")));
这叫什么？我指的是如果根本就没有这个条件查询（查询条件不定呀，你再看下面你的能叫更简单？），你还不得
if(title!=null || !title.equals(""))
params.add(new QueryCondition(QueryCondition.LIKE, new QueryParam("title", "%"+title+"%")));

21 楼 fireflyc 2009-03-19

List<QueryCondition> params = new ArrayList<QueryCondition>();
params.add(new QueryCondition(QueryCondition.LIKE, new QueryParam("title", "%"+title+"%")));
params.add(new QueryCondition(QueryCondition.LIKE, new QueryParam("author", "%"+author+"%")));
params.add(new QueryCondition("addTime",  QueryCondition.BETWEEN, new QueryParam("startTime", startTime), new QueryParam("endTime",
					endTime)));
ResultObject<Page<Topic>> result = topicService.findTopicByPage(RequestUtils.getPageNo(request), params);

20 楼 zhongxuchen 2009-03-18

lovefly_zero 写道

运行效率如何呢？

运用的非常好，简单的字符串处理不影响效率，最大的效率问题在于数据库操作呀，就关系到sql问题

19 楼 lovefly_zero 2009-03-18

运行效率如何呢？

18 楼 zhongxuchen 2009-02-25

那是你没有看明白这个东西的最大用场，动态参数（什么叫动态参数，没有体验过呀，复杂查询用hibernate查询是效率很低的而且有些sql的特性hibernate不支持），存储过程1、移植性不好，2、动态参数存储过程就能解决了！

17 楼海底行者 2009-02-25

哥们，查询可以使用你的‘陈氏’，也可以使用Hibernate的查询。只是简单的个人爱好而已。
既然SQL语句要带参数，那为什么不使用存储过程（procedure）呢？
据官方人士说存储过程是已编译的，运行效率要快。
尽管Hibernate不建议自己操作connection，但是使用存储过程应该是个很好的办法。
我个人觉得，Hibernate应该将命名的sql语句生成为存储过程，这样即解决了sql不熟练的问题、
面向对象的问题又可以使本来就慢的程序快些。
---拙见

16 楼 kimmking 2009-02-21

zhongxuchen 写道

kimmking 写道

zhongxuchen 写道

kimmking 写道

有一个简单问题复复杂化的例子：

hibernate的一般用法：

EO eo = new EO();
set get......

query....

这个问题怎么会用这种方式解决呢？直接(EO)this.get(EO.class,id),没有仔细看文章才会有这样的结论，
敢拿出来露当然有一些独到的地方，简单东西其实大家做法都差不多，属于没有必要谈的事情！

lz的文字真费解，你这个替换，其实就是多了一个null判断，难道不属于简单东西

哈哈，看来兄台是没有实际体会呀，一堆一堆
if(param)
{
append(" and t.a=?")
params.add(param);
}
要是好几个条件呢sql看起来优雅嘛?要是改条件增加条件又会怎么样？难道就是一个null判断这么简单？我要说的是这是一个思想一种创新，而不是技术！我相信面对这个问题很多人仍然在
if()if()却不会有去改进的想法！这个东西带来的代码的简洁性和可维护性很直接，代码量也大为减少，难道这个年头
靠代码行数算钱?

1、自动生成，你说的对，这类类型人工来做就是扯
2、 orm工具解决

15 楼 zhongxuchen 2009-02-20

kimmking 写道

zhongxuchen 写道

kimmking 写道

有一个简单问题复复杂化的例子：

hibernate的一般用法：

EO eo = new EO();
set get......

query....

lz的文字真费解，你这个替换，其实就是多了一个null判断，难道不属于简单东西

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论