Mybatis查询oracle分页

dabinglibrary 2014-04-16

展开全文

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

ibtais配置分页模糊查询

Java代码
<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 <= $limit$)row_ WHERE rownum_ >$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>

分页模型通用类

Java代码
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;
实体类
Java代码
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;

服务层调用
Java代码
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设置分页属性
Java代码
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 显示到客户端
Java代码
<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 参数查询
Js代码
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}});

}