基于spring+hibernate的组合查询+分页组件

  最近由于项目需要，自己尝试做了一个组合查询+分页的组件。花了5天时间，只能说是初具雏形。核心还是hibernate的QBC，只是加上了XML实现了可配置。能够满足当前项目地应用，也解决了criteria查询记录条数的BUG，有兴趣的朋友可以下载使用。

 

  使用的例子和源码以及说明见附件。由于时间仓促，今后有空的话还会继续进行修改，提供JDBC支持，以及脱离SPRING和HIBERNATE等等。

 

   基本使用方法如下,根据项目需求建立自己的通用条件查询对象并继承CommonCondition

/**
 * 本项目的通用条件查询对象 包含cret所有页面的条件作为属性 今后可能继续增加属性
 *   @author fw
 *
 */
public class CretCondition extends CommonCondition{
	
	
	private Integer id;//xx编号
	private String dept;//部门
	private String job;//岗位
	private String checkName;//单据经办人
	
	private String costType;//报销单类型
	private Integer state;//单据状态 
	
	private Date startDate;//起始时间
	private Date endDate;//结束时间
	private Date[] dateRange;//日期范围

           ........
}

 

   然后编写一个总的条件配置文件，默认放src目录下，取名为condition-cfg.xml(时间仓促没来得及写验证....)

 

<?xml version="1.0" encoding="UTF-8"?>
<condition-cfg common-condition-class="com.lovo.cett.model.condition.CretCondition">
	
  <!-- 为需要组合条件查询的模型编写的配置文件 默认相对目录为src 可以有多个 -->
  <mapping resource="config/condition/stuffcost-condition-mapping.xml"/>
   <mapping resource="config/condition/tourcost-condition-mapping.xml"/>
  
  <!-- 配置common-condition-class中的属性和运算符号的对应关系 -->
  <expression-mapping>
    <equal>
      <property>id</property>
      <property>dept</property>
      <property>job</property>
      <property>costType</property>
      <property>state</property>
      <property>belongUserId</property>
    </equal>
    <between>
      <property>dateRange</property>
      <property>moneyRange</property>
    </between>
    <greater-than>
      <property>startDate</property>
      <property>startMoney</property>
    </greater-than>
    <less-than>
      <property>endDate</property>
      <property>endMoney</property>
    </less-than>
    <like>
      <property>checkName</property>
    </like>
    <!-- 默认按模型的id属性降序排列 如果不需要排序的话 可以写一个空的<order-by/>-->
    <order-by defaultProperty="id" defaultOrder="desc">
      <!-- 配置通用条件对象中的orderByDate属性为动态排序字段 -->
      <property>orderByDate</property>
    </order-by>
  </expression-mapping>
</condition-cfg>

 

   接下来是编写针对某个模型的组合查询映射文件，例如材料报销单

  

<?xml version="1.0" encoding="UTF-8"?>
<condition-mapping mapping-class="com.lovo.cett.model.cost.StuffCost" >

	<!-- 动态设置模型类中抓取策略 暂时只能动态设置join/select/auto
	 auto表示不改变抓取策略 即使用hbm文件中配置的抓取策略 可以配置多个-->
    <model-fetch property="stuffCostItems" fetch="select"/>
    
    <!-- 关联CretCondition通用条件对象和StuffCost对象的属性 -->	
	<property>
	  <condition-property>id</condition-property>
	  <model-property>id</model-property>
	</property>
	
	<property>
	  <condition-property>startDate</condition-property>
	  <model-property>date</model-property>
	</property>
	
	<property>
	  <condition-property>endDate</condition-property>
	  <model-property>date</model-property>
	</property>
	
	<property>
	  <condition-property>dept</condition-property>
	  <model-property>dept</model-property>
	</property>
	
	<property>
	  <condition-property>job</condition-property>
	  <model-property>job</model-property>
	</property>
	
	<property>
	  <condition-property>dateRange</condition-property>
	  <model-property>date</model-property>
	</property>
	
	<property>
	  <condition-property>state</condition-property>
	  <model-property>state</model-property>
	</property>
	
	<property>
	  <condition-property>checkName</condition-property>
	  <model-property>name</model-property>
	</property>
	
	<property>
	  <condition-property>moneyRange</condition-property>
	  <model-property>requestMoney</model-property>
	</property>
	
	<property>
	  <condition-property>startMoney</condition-property>
	  <model-property>requestMoney</model-property>
	</property>
	
	<property>
	  <condition-property>endMoney</condition-property>
	  <model-property>requestMoney</model-property>
	</property>
	
	<property>
	  <condition-property>orderByDate</condition-property>
	  <model-property>date</model-property>
	</property>
	
	
	
</condition-mapping>

 

 

    然后运行test包下的TestConditionFinderImpl

   

/**
 * 测试
 * @author fw
 *
 */
public class TestConditionFinderImpl extends BaseSpringTest {
	
	private ConditionFinder conditionFinder;
	
	
	
	private PageBean<StuffCost> pb;
	private CretCondition cc;
	static final int NOW_PAGE=1;//当前第1页
	static final int PAGE_SIZE=3;//每页显示3条
	static final int TOTAL_COUNT=7;
	
	@Override
	protected void onSetUp() throws Exception {
		/**以下条件代表：查询 经办人like fw/状态等于1/日期为昨天到今天/申请金额为100至200 
		 * 的材料报销单 结果按日期降序
		 * 每页显示3条 显示第1页
		 *  */
		pb = new PageBean<StuffCost>(NOW_PAGE,PAGE_SIZE);
		cc = new CretCondition();
		cc.setState(1);
		cc.setCheckName("fw");
		Date endDate = new Date();
		Date startDate = new Date();
		startDate.setDate(startDate.getDate()-1);
		cc.setDateRange(new Date[]{startDate,endDate});
		cc.setMoneyRange(new Double[]{Double.parseDouble("100"),Double.parseDouble("200")});
		cc.setOrderByDate(OrderBy.DESC);

                                 ........................

 

   以ORACLE为例，生成SQL如下

  

Hibernate: select count(*) as y0_ from t_cost this_ where this_.c_type='sc' and this_.c_name like ? and this_.c_state=? and this_.c_date between ? and ? and this_.c_requestMoney between ? and ?
Hibernate: select * from ( select this_.c_id as c1_2_0_, this_.c_costdept as c3_2_0_, this_.c_date as c4_2_0_, this_.c_dept as c5_2_0_, this_.c_financeSanctionMopney as c6_2_0_, this_.c_flowchoice as c7_2_0_, this_.c_job as c8_2_0_, this_.c_loanmoney as c9_2_0_, this_.c_loanperson as c10_2_0_, this_.c_name as c11_2_0_, this_.c_remark as c12_2_0_, this_.c_requestMoney as c13_2_0_, this_.c_tourProject as c14_2_0_, this_.c_state as c15_2_0_ from t_cost this_ where this_.c_type='sc' and this_.c_name like ? and this_.c_state=? and this_.c_date between ? and ? and this_.c_requestMoney between ? and ? order by this_.c_date desc, this_.c_id desc ) where rownum <= ?

 

   

评论

2 楼 fw2003 2010-12-31  

JEye17173 写道

你的改进版呢

过年的时候放出 基于JDBC的ORM 附带AOP以及默认的声明式事务 全部采用注解配置

1 楼 JEye17173 2010-11-25  

你的改进版呢