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" />	
  <result column="BMZDM" property="bmzdm" jdbcType="VARCHAR" />	

<sql id="findByPageCondition">
		<isNotEmpty property="objCondition">
		    	<isNotEmpty property="objCondition.bmzdm">
					 bmzdm LIKE '%$objCondition.bmzdm$%'
		    	</isNotEmpty>
		    	<isNotEmpty property="objCondition.rymc">
				    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">
			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 Rydm implements Serializable{
    /**
     * This field was generated by Abator for iBATIS.
     * This field corresponds to the database column RYDM.RYDM
     *
     * @abatorgenerated Sat Aug 06 16:55:26 CST 2011
     */
    private String rydm;


    private String rymc;

    private String bmzdm;

 

 

服务层调用

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 class UsersAction extends ActionSupport implements ServletRequestAware,
SessionAware{
    public String message;
    public String conditions;
	protected Map session;//会话对象
	protected HttpServletRequest request;//请求对象
/*
	 * 查询用户信息
	 */
	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;
	}
}

  返回配置 我是使用了ext  所以转换json 显示到客户端

<package name="admin" extends="json-default" namespace="/admin">
	
	<action name="listUser" class="userAction" method="listUser">
			<result type="json">
				<param name="root">page</param>
				<param name="excludeProperties">limit,start,objCondition</param>
			</result>
		</action>
</package>

 

 

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}});
	
}