ibatis查询oracle分页

开发采用spring+struts+ibatis+extjs，数据库用oracle

ibtais配置分页 模糊查询

<sqlMap namespace="RYDM" >
<typeAlias alias="page" type="com.portal.util.Page" />
<typeAlias alias="abatorgenerated_RydmResult" type="com.portal.model.Rydm" />
  <resultMap id="abatorgenerated_RydmResult" class="com.portal.model.Rydm" >
 <result column="RYDM" property="rydm" jdbcType="VARCHAR" />
    <result column="RYMC" property="rymc" jdbcType="VARCHAR" />	


<sql id="findByPageCondition">
		<isNotEmpty property="objCondition">
		    	<isNotEmpty property="objCondition.bmzdm">
				<![CDATA[
					 bmzdm LIKE '%$objCondition.bmzdm$%'
		    	]]>
		    	</isNotEmpty>
		    	<isNotEmpty property="objCondition.rymc">
				<![CDATA[
				    AND rymc LIKE '%$objCondition.rymc$%'    
		    	]]>
		    	</isNotEmpty>
		</isNotEmpty>
	</sql>
	<select id="findByPage" parameterClass="page" 
                             resultClass="abatorgenerated_RydmResult">
            SELECT * FROM (SELECT row_.*, rownum rownum_ FROM
                    (select ry.* from RYDM ry  
                    where 1=1 
         <dynamic prepend="AND">
			<include refid="findByPageCondition" />
		</dynamic>       
            )row_ WHERE rownum &lt;= $limit$)row_ WHERE rownum_ &gt;$start$ 
			ORDER BY rydm
	</select>
	<select id="findByCount" parameterClass="page" resultClass="int">
		<![CDATA[
			SELECT COUNT(*) FROM RYDM ry   where 1=1
		]]>
		<dynamic prepend="AND">
			<include refid="findByPageCondition" />
		</dynamic>
	</select>

分页模型通用类

public class Page implements java.io.Serializable{

	public void setPageProperty(Page page) {
		if(page.getStart()>0){
		// 设置取数据的区间
		int endPage=page.getStart()+page.getLimit(); 
		int stratPage=page.getStart();
		this.setStart(stratPage);
		this.setLimit(endPage);
		}else{
			this.setLimit(page.getLimit());
			this.setStart(0);
		}
	}

	/** 总记录数 */
	private int totalProperty;

	/** 分页结果 */
	private List root;

	/** 开始页码 */
	private int start;

	/** 每页多少 */
	private int limit;

	/** 成功与否 */
	private boolean success;

	/** 查询条件 */
	private Object objCondition;

服务层调用

public class RydmServiceImpl extends SqlMapClientDaoSupport implements
		RydmService {
	
public Page findByPageRydm(Page page) throws BusinessException {
		page.setTotalProperty((Integer) getSqlMapClientTemplate()
				.queryForObject("RYDM.findByCount", page));
		page.setPageProperty(page);
		page.setRoot(getSqlMapClientTemplate().queryForList(
				"RYDM.findByPage", page));
		return page;
	}

action设置分页属性

/*
	 * 查询用户信息
	 */
	public String listUser() {
              Rydm user=new   Rydm();                
               user.setBmzdm(request.getParameter("bmzdm").toString());
		user.setRymc(request.getParameter("rymc").toString());
		int start =0;
		int Limit=10;
		Page page = new Page();
		
              try {
		start = Integer.valueOf(getRequest().getParameter("start"));
		Limit = Integer.valueOf(getRequest().getParameter("limit"));
		}catch (NumberFormatException e) {
		}
		page.setStart(start);
		page.setLimit(Limit);
		//使用对象作为查询参数 传入ibtais
		page.setObjCondition(user);
		page = userService.findByPage(page);
		} catch (BusinessException e) {
			log.warn("UsersAction.class查询用户信息出现异常"+e.getMessage(), e);
		}
		return SUCCESS;
	}

extjs 参数查询

var rydm_store = new Ext.data.Store({
	proxy : new Ext.data.HttpProxy({
		url : "/extDemo/admin/userList.action",
		method:"post"
	}),
	reader : new Ext.data.JsonReader({
		totalProperty : 'totalProperty',
		root : 'root',
		fields : [  {
			name : 'bmzdm',
			type : 'string'
		} , {
			name : 'bmmc',
			type : 'string'
		}]
	})
});

var btn_search_rydm = new Ext.Button({
	text : '查询',
	iconCls : 'icon-search',
	handler : queryRydm
});


var queryRydm = function() {
	rydm_store.baseParams.bmzdm= bmzdm_search_rydn.getValue();
	rydm_store.baseParams.rymc =text_search_rydm.getValue();
	rydm_store.load({params : {start : 0,limit : 15}});
	
}

呵呵。到这里下载吧。这里是最厚道的开源平台，赤裸裸的开源。这里不但有ibatis+oracle的分页算法实现还有iBatis+mysql的，iBatis+H2的等等，应有尽有~~~~~~

http://www.g4studio.org/

偶也来说几句，省得有人说我做广告：楼主的封装很复杂没仔细看，但看到下面的SQL语句有点感受

    <select id="findByPage" parameterClass="page"   
                             resultClass="abatorgenerated_RydmResult">  
            SELECT * FROM (SELECT row_.*, rownum rownum_ FROM  
                    (select ry.* from RYDM ry    
                    where 1=1   
         <dynamic prepend="AND">  
            <include refid="findByPageCondition" />  
        </dynamic>         
            )row_ WHERE rownum &lt;= $limit$)row_ WHERE rownum_ &gt;$start$   
            ORDER BY rydm  
    </select> 

 建议楼主把上面的分页算法屏蔽掉，对于平台的二次开发程序员来说是不需要关注如何分页的，而只需要传入分页参数就可以了。封装后应该这样写分页语句：

	<select id="getCodeViewList" parameterClass="map" resultClass="dto">
		SELECT codeid, field, fieldname, code, codedesc, sortno
		  FROM codeview order by codeid , sortno
	</select>

 数据库分页不分页由你的不同的dao调用来实现：比如dao.query4Page();这个自动拦截SQL语句并拼上分页算法。query4List()则不分页。

    <select id="findByPage" parameterClass="page"   
                             resultClass="abatorgenerated_RydmResult">  
            SELECT * FROM (SELECT row_.*, rownum rownum_ FROM  
                    (select ry.* from RYDM ry    
                    where 1=1   
         <dynamic prepend="AND">  
            <include refid="findByPageCondition" />  
        </dynamic>         
            )row_ WHERE rownum &lt;= $limit$)row_ WHERE rownum_ &gt;$start$   
            ORDER BY rydm  
    </select> 

	<select id="getCodeViewList" parameterClass="map" resultClass="dto">
		SELECT codeid, field, fieldname, code, codedesc, sortno
		  FROM codeview order by codeid , sortno
	</select>

难道你直接用？ 一看就不靠谱的分页。 还需要各位高手出来优化，让我们学习学习。

    <select id="findByPage" parameterClass="page"   
                             resultClass="abatorgenerated_RydmResult">  
            SELECT * FROM (SELECT row_.*, rownum rownum_ FROM  
                    (select ry.* from RYDM ry    
                    where 1=1   
         <dynamic prepend="AND">  
            <include refid="findByPageCondition" />  
        </dynamic>         
            )row_ WHERE rownum &lt;= $limit$)row_ WHERE rownum_ &gt;$start$   
            ORDER BY rydm  
    </select> 

	<select id="getCodeViewList" parameterClass="map" resultClass="dto">
		SELECT codeid, field, fieldname, code, codedesc, sortno
		  FROM codeview order by codeid , sortno
	</select>

我没用啊 我用我自己的哈。 http://www.g4studio.org/ 欢迎大家下载看了以后仍鸡蛋啊！