iBatis 调用 PostgreSQL 存储过程返回结果集

dickrong

　　最近在学习PostgreSQL，PostgreSQL有些很好的特性，比如可以用一维或多维数组做字段类型，可以省去一张关联表，如果在GIS领域应用的话，PostGIS为它增加了很强的空间计算能力，另外PostgreSQL对SQL的支持也比较标准和全面。
回到题目，接下来要在应用中使用PostgreSQL，回到我们的框架：Spring + Struts + iBatis，iBatis如何调用 PostgreSQL的存储过程呢？
PostgreSQL存储过程的语法类似Oracle，我们先看一个例子：

数据表： t_user


CREATE TABLE t_user
(
  id serial NOT NULL,
  login_name character varying(20) NOT NULL,
  login_passwd character varying(20),
  name character varying(20) NOT NULL,
  sex smallint NOT NULL DEFAULT 1,
  phone character varying(10)[],
  privilege integer[],
  CONSTRAINT t_user_pkey PRIMARY KEY (id)
)
此处我们看到 t_user表中有 serial字段（这是一个SEQUENCE，用法与Oracle略同，有一点点差异），建表的时候，PostgreSQL会自动建一个SEQUENCE与id字段关联，并且默认值为 nextval(t_user_id_seq)，其次我们看到phone字段的类型是varchar(10)这样的一个数组，privilege的类型是int的数组。

我们创建一个登录的存储过程：f_login_user()

CREATE OR REPLACE FUNCTION f_login_user(p_login_name in character varying, p_login_passwd in character varying)
RETURNS refcursor
AS
$BODY$
DECLARE
    p_user_cur refcursor;
BEGIN
    OPEN p_user_cur FOR SELECT * FROM t_user
        WHERE login_name = p_login_name AND login_passwd = p_login_passwd;
    RETURN p_user_cur;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;
存储过程有2个参数，登录名，登录密码，返回一个游标类型。我们准备用iBatis来调用这个存储过程，调用的方式为：{? = call f_login_user(?,?)}

接下来我们准备iBatis的SqlMap的xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//iBATIS.com//DTD SQL Map 2.0//EN" "http://www.ibatis.com/dtd/sql-map-2.dtd">

<sqlMap namespace="UserDaoSqlMap">

    <typeAlias alias="user" type="org.kylin.emap.bean.UserBean"/>

    <resultMap id="userResultMap" class="user">
        <result property="id" column="id"/>
        <result property="loginName" column="login_name"/>
        <result property="loginPasswd" column="login_passwd"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <result property="phone" column="phone" typeHandler="org.kylin.emap.util.ibatis.PgsqlStringArrayTypeHandler"/>
        <result property="privilege" column="privilege" typeHandler="org.kylin.emap.util.ibatis.PgsqlIntegerArrayTypeHandler"/>
    </resultMap>
        
    <parameterMap id="loginUserParameters" class="java.util.HashMap">
        <parameter property="result" jdbcType="OTHER" javaType="java.sql.ResultSet" mode="OUT"/>
        <parameter property="loginName" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
        <parameter property="loginPasswd" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
    </parameterMap>
   
    <select id="checkUser" resultMap="userResultMap" parameterClass="user">
        SELECT id, login_name, login_passwd, name, sex, phone, privilege
        FROM t_user
        WHERE login_name = #loginName# AND login_passwd = #loginPasswd#
    </select>
   
    <procedure id="loginUser" resultMap="userResultMap" parameterMap="loginUserParameters" >
         {? = call f_login_user(?,?)}
    </procedure>
   
</sqlMap>

注意看红色部分的字：在userResultMap中有typeHandler的描述，如phone字段，用org.kylin.emap.util.ibatis.PgsqlStringArrayTypeHandler这个类来处理数组类型，这个类是实现了iBatis的com.ibatis.sqlmap.client.extensions.TypeHandlerCallback，iBatis是很强啊，很方便扩展。

先看procedure的定义，用到了loginUserParameters做参数，返回userResultMap。
loginUserParameters描述了3个参数,按照调用方式: ? = call f_login_user(?, ?)的顺序，第一个参数是返回结果集的，这里的jdbcType填写OTHER，javaType填写java.sql.ResultSet，如果是ORACLE的存储过程通过游标返回结果集的话，jdbcType应该填写为ORACLECURSOR，看来iBatis专门为ORACLE做了开发，不过在PostgreSQL中不能用ORACLECURSOR，得用OTHER。

好了，现在我们看看DAO中如何通过SqlMapClient得到这个结果集：

    public UserBean loginUser(String loginName, String loginPasswd) throws DaoException {

        HashMap<String, String> parameters = new HashMap<String, String>();
        parameters.put("loginName", loginName);
        parameters.put("loginPasswd", loginPasswd);
        return (UserBean)getSqlMapClientTemplate().queryForObject("UserDaoSqlMap.loginUser", parameters);
    }

当前这个例子，存储过程通过游标返回了一行数据，我们可以用queryForObject得到结果集，结果集也自动影射为UserBean了，很方便使用。

另外需要注意的问题：
1. PostgreSQL的存储过程支持 returns SETOF record 来返回多行记录，这种方法在存储过程中内部使用了游标，效率比较高，另外应用也不用关心游标的关闭和释放的问题。
2. 如果存储过程返回多个结果集，需要用 returns SETOF refcursor 来返回多个结果集。