spring、struts、mybatis、Postgresql集成使用存储过程进行分页

    到目前为止Mybatis的分页还是基于内存分页，这样的分页实现在实际项目中基本上没有任何作用，网络上关于Mybatis的物理分页也有好几种方法，本人因为以前经常使用存储过程进行分页，故在此提出来跟各位进行讨论。首先说一下本人实验项目中所使用的产品：Postgresql、DBCP、Mybatis、Spring、Struts2、JUnit、Sitemesh，关于这些产品的整合过程，以后有机会再说,在这里需要提出一点，那就是需要更改一下DBCP配置文件，增加下面的属性：

<property name="defaultAutoCommit" value="false"></property>

Postgresql存储过程的编写，网上资料比较多，本文就不再赘述。关于分页存储过程，Postgresql跟Oralce中的相差也不是很大，基本上也就是pl/sql到pl/pgsql，本文中使用的表和存储过程如下：

CREATE TABLE t0_sys_model
(
  id character(36) NOT NULL,
  code character varying(30),
  name character varying(50),
  parentid character(36),
  "isLeaf" boolean,
  url character varying(255),
  level integer,
  "isSys" boolean,
  des text,
  state smallint,
  creator character(36),
  "createTime" date,
  "lastOperator" character(36),
  "lastUpdateTime" date,
  CONSTRAINT pk_t0_sys_model PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE t0_sys_model
  OWNER TO postgres;

    主键使用的是character(36)，用于存储GUID，在Postgresql中还有什么更好的方式来用作主键，还请大家多提建议（本人不喜欢使用自增类型，各种弊端）。

CREATE OR REPLACE FUNCTION p0_sys_model_search(IN pageindex integer, IN recordcountperpage integer, OUT allrecordcount integer, OUT curresult refcursor)
  RETURNS record AS
$BODY$
DECLARE
    vSQL varchar;
BEGIN
    vSQL:='select count(*) from t0_sys_model';
    execute vSQL into allRecordCount;

    vSQL:='select * from  t0_sys_model limit ' ||recordCountPerPage || ' offset ' ||recordCountPerPage*(pageIndex-1);

    OPEN curResult FOR EXECUTE vSQL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION p0_sys_model_search(integer, integer)
  OWNER TO postgres;

    存储过程有两个输入参数，分别为当前页和每页显示多少条记录；两个输出参数：总记录数和记录集。

    接下来就是准备Mybatis映射文件（SqlMap的xml文件）了：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="mycompany.dao.base.ModelDao">    
    <resultMap id="modelResultMap" type="mycompany.domain.base.Model">
       <id property="id" column="ID"/>
       <result property="code" column="code"/>
       <result property="name" column="name"/>
       <result property="parentId" column="parentId"/>
       <result property="isLeaf" column="isLeaf"/>
       <result property="url" column="url"/>
       <result property="level" column="level"/>
       <result property="isSys" column="isSys"/>
       <result property="des" column="des"/>
       <result property="state" column="state"/>
       <result property="creator" column="creator"/>
       <result property="createTime" column="createTime"/>
       <result property="lastOperator" column="lastOperator"/>
       <result property="lastUpdateTime" column="lastUpdateTime"/>
     </resultMap>
     
     <cache />
    
     <select id="getPage" statementType="CALLABLE" parameterType="HashMap">
       <![CDATA[
           {call p0_sys_model_search(
              #{pageIndex,mode=IN,jdbcType=INTEGER},
              #{recordCountPerPage,mode=IN,jdbcType=INTEGER},
              #{allRecordCount,mode=OUT,jdbcType=INTEGER},
              #{curResult,mode=OUT,jdbcType=OTHER, javaType=ResultSet, resultMap=modelResultMap}
           )}
       ]]>
     </select> 
</mapper>

    在配置文件中需要注意的是游标对应的jdbcType为OTHER，而不是CURSOR。

    按照MyBatis的规范，需要编写对应的实体和访问接口：

package mycompany.domain.base;

/**
 * @function ***
 * @author ***
 * @date ***
 * @memo 
 */
public class Model{
    private static final long serialVersionUID = 1L;
    
    protected String id;//***
        protected String creator;//***
        protected Date createTime;//***
        protected String lastOperator;//***
        protected Date lastUpdateTime;//***
    private String code;//***
    private String name;//***
    private String parentId;//***
    private boolean isLeaf;//***
    private String url;//***
    private int level;//***
    private boolean isSys;//***
    private String des;//***
    private int state;//***
    
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getCreator() {
        return creator;
    }
    public void setCreator(String creator) {
        this.creator = creator;
    }
    public Date getCreateTime() {
        return createTime;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
    public String getLastOperator() {
        return lastOperator;
    }
    public void setLastOperator(String lastOperator) {
        this.lastOperator = lastOperator;
    }
    public Date getLastUpdateTime() {
        return lastUpdateTime;
    }
    public void setLastUpdateTime(Date lastUpdateTime) {
        this.lastUpdateTime = lastUpdateTime;
    }
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getParentId() {
        return parentId;
    }
    public void setParentId(String parentId) {
        this.parentId = parentId;
    }
    public boolean isLeaf() {
        return isLeaf;
    }
    public void setLeaf(boolean isLeaf) {
        this.isLeaf = isLeaf;
    }
    public String getUrl() {
        return url;
    }
    public void setUrl(String url) {
        this.url = url;
    }
    public int getLevel() {
        return level;
    }
    public void setLevel(int level) {
        this.level = level;
    }
    public boolean isSys() {
        return isSys;
    }
    public void setSys(boolean isSys) {
        this.isSys = isSys;
    }
    public String getDes() {
        return des;
    }
    public void setDes(String des) {
        this.des = des;
    }
    public int getState() {
        return state;
    }
    public void setState(int state) {
        this.state = state;
    }
    
}

package mycompany.dao.base;

import java.util.List;
import java.util.Map;

import mycompany.domain.base.Model;

/**
 * @function **
 * @author **
 * @date **
 * @memo 
 */
public interface ModelDao {
    void getPage(Map<String, Object> params);
}

    最后是在客户端调用：

package mycompany.test.base;

import java.util.HashMap;
import java.util.List;

import org.junit.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import mycompany.domain.base.Model;
import mycompany.dao.base.ModeldDao;


/**
 * @function ***
 * @author ***
 * @date ***
 * @memo 
 */
public class ModelDaoTest {
    @Test
    public void testGetPage() { 
        ApplicationContext context = new ClassPathXmlApplicationContext("configFiles/spring/applicationContext*.xml");
        ModelDao modelDao = (ModelDao)context.getBean("modelDao");
        HashMap<String, Object> paras = new HashMap<String, Object>();  
        paras.put("pageIndex", 1); 
        paras.put("recordCountPerPage", 10); 
        modelService.getPage(paras); 
        List<Model> models=(List<Model>)paras.get("curResult");  
        //int allRecordCount = (Integer)(paras.get("allRecordCount"));  
        System.out.println(models.get(0).getName());
    }

}

好了，最终的结果是完美的绿线。本文中虽然没有将各种配置文件（其实也就是Spring配置文件）全部写出，但是核心操作也无外乎上述内容。