Ibatis配置文件ibatis.xml:
<resultMap id="result-map" class="com.xxx.xxx.Table">
<result property="rsId" column="id" />
<!--在Java对象跟Oracle列之间转换 -->
</resultMap>
<parameterMap id="parameter-map" class="java.util.HashMap" >
<parameter property="param1" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="param2" jdbcType="Integer" javaType="java.lang.Integer" mode="IN"/>
<parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" />
</parameterMap>
<procedure id="testCur" resultMap="result-map" parameterMap="parameter-map">
{call testProcedures(?, ?, ?)}
</procedure>
再看java里面调用:
Map paramMap = new HashMap();
String str = request.getParameter(.....);
int n = request.getParameter(.....);
paramMap.put("param1",str);
paramMap.put("param2",n);
List detailList = null;
try{
detailList = getSqlMapClientTemplate().queryForList("testCur", paramMap);
//可以用getSqlMapClientTemplate().queryForObject("testCur", paramMap); 返回值是Object。
}catch(Exception ex){
ex.printStackTrace(); return detailList;
detailList就是需要的结果集
================================================================
在实际的工作和学习中,我们可以通过Oracle数据库提供的REF CURSOR功能实现在程序间传递结果集的功能,另外,利用REF CURSOR可以同时实现BULK SQL,以此提高SQL的性能。
首先,我们需要使用scott用户的emp表实现以下测试:
SQL> desc emp
Name Null? Type
-------------------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
最后使用ref cursor获得结果集输出:
SQL> set serveroutput on
SQL> DECLARE
2 TYPE mytable IS TABLE OF emp%ROWTYPE;
3 l_data mytable;
4 l_refc sys_refcursor;
5 BEGIN
6 OPEN l_refc FOR
7 SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp;
8
9 FETCH l_refc BULK COLLECT INTO l_data;
10
11 CLOSE l_refc;
12
13 FOR i IN 1 .. l_data.COUNT
14 LOOP
15 DBMS_OUTPUT.put_line ( l_data (i).ename
16 || ' was hired since '
17 || l_data (i).hiredate
18 );
19 END LOOP;
20 END;
21 /
SMITH was hired since 17-DEC-80
ALLEN was hired since 20-FEB-81
WARD was hired since 22-FEB-81
JONES was hired since 02-APR-81
MARTIN was hired since 28-SEP-81
BLAKE was hired since 01-MAY-81
CLARK was hired since 09-JUN-81
SCOTT was hired since 19-APR-87
KING was hired since 17-NOV-81
TURNER was hired since 08-SEP-81
ADAMS was hired since 23-MAY-87
JAMES was hired since 03-DEC-81
FORD was hired since 03-DEC-81
MILLER was hired since 23-JAN-82
PL/SQL procedure successfully completed.
=====================================================================
iBatis 调用 存储过程返回结果集
数据表: t_userview plaincopy to clipboardprint?</pre><pre class="html" name="code">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)
)
</pre><pre class="html" name="code">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()
view plaincopy to clipboardprint?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;
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文件:
view plaincopy to clipboardprint?<?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" <span style="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlStringArrayTypeHandler</span>"/>
<result property="privilege" column="privilege" <span style="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlIntegerArrayTypeHandler</span>"/>
</resultMap>
<parameterMap id="<span style="color:#ff0000;">loginUserParameters</span>" class="java.util.HashMap">
<parameter property="result" <span style="color:#ff0000;">jdbcType="OTHER" javaType="java.sql.ResultSet</span>" 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="<span style="color:#ff0000;">userResultMap</span>" parameterMap="<span style="color:#ff0000;">loginUserParameters</span>" >
{? = call f_login_user(?,?)}
</procedure>
</sqlMap>
<?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" <span style="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlStringArrayTypeHandler</span>"/>
<result property="privilege" column="privilege" <span style="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlIntegerArrayTypeHandler</span>"/>
</resultMap>
<parameterMap id="<span style="color:#ff0000;">loginUserParameters</span>" class="java.util.HashMap">
<parameter property="result" <span style="color:#ff0000;">jdbcType="OTHER" javaType="java.sql.ResultSet</span>" 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="<span style="color:#ff0000;">userResultMap</span>" parameterMap="<span style="color:#ff0000;">loginUserParameters</span>" >
{? = 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得到这个结果集:
view plaincopy to clipboardprint?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);
}
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 来返回多个结果集。
<resultMap id="result-map" class="com.xxx.xxx.Table">
<result property="rsId" column="id" />
<!--在Java对象跟Oracle列之间转换 -->
</resultMap>
<parameterMap id="parameter-map" class="java.util.HashMap" >
<parameter property="param1" jdbcType="VARCHAR" javaType="java.lang.String" mode="IN"/>
<parameter property="param2" jdbcType="Integer" javaType="java.lang.Integer" mode="IN"/>
<parameter property="result" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" />
</parameterMap>
<procedure id="testCur" resultMap="result-map" parameterMap="parameter-map">
{call testProcedures(?, ?, ?)}
</procedure>
再看java里面调用:
Map paramMap = new HashMap();
String str = request.getParameter(.....);
int n = request.getParameter(.....);
paramMap.put("param1",str);
paramMap.put("param2",n);
List detailList = null;
try{
detailList = getSqlMapClientTemplate().queryForList("testCur", paramMap);
//可以用getSqlMapClientTemplate().queryForObject("testCur", paramMap); 返回值是Object。
}catch(Exception ex){
ex.printStackTrace(); return detailList;
detailList就是需要的结果集
================================================================
在实际的工作和学习中,我们可以通过Oracle数据库提供的REF CURSOR功能实现在程序间传递结果集的功能,另外,利用REF CURSOR可以同时实现BULK SQL,以此提高SQL的性能。
首先,我们需要使用scott用户的emp表实现以下测试:
SQL> desc emp
Name Null? Type
-------------------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
最后使用ref cursor获得结果集输出:
SQL> set serveroutput on
SQL> DECLARE
2 TYPE mytable IS TABLE OF emp%ROWTYPE;
3 l_data mytable;
4 l_refc sys_refcursor;
5 BEGIN
6 OPEN l_refc FOR
7 SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp;
8
9 FETCH l_refc BULK COLLECT INTO l_data;
10
11 CLOSE l_refc;
12
13 FOR i IN 1 .. l_data.COUNT
14 LOOP
15 DBMS_OUTPUT.put_line ( l_data (i).ename
16 || ' was hired since '
17 || l_data (i).hiredate
18 );
19 END LOOP;
20 END;
21 /
SMITH was hired since 17-DEC-80
ALLEN was hired since 20-FEB-81
WARD was hired since 22-FEB-81
JONES was hired since 02-APR-81
MARTIN was hired since 28-SEP-81
BLAKE was hired since 01-MAY-81
CLARK was hired since 09-JUN-81
SCOTT was hired since 19-APR-87
KING was hired since 17-NOV-81
TURNER was hired since 08-SEP-81
ADAMS was hired since 23-MAY-87
JAMES was hired since 03-DEC-81
FORD was hired since 03-DEC-81
MILLER was hired since 23-JAN-82
PL/SQL procedure successfully completed.
=====================================================================
iBatis 调用 存储过程返回结果集
数据表: t_userview plaincopy to clipboardprint?</pre><pre class="html" name="code">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)
)
</pre><pre class="html" name="code">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()
view plaincopy to clipboardprint?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;
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文件:
view plaincopy to clipboardprint?<?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" <span style="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlStringArrayTypeHandler</span>"/>
<result property="privilege" column="privilege" <span style="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlIntegerArrayTypeHandler</span>"/>
</resultMap>
<parameterMap id="<span style="color:#ff0000;">loginUserParameters</span>" class="java.util.HashMap">
<parameter property="result" <span style="color:#ff0000;">jdbcType="OTHER" javaType="java.sql.ResultSet</span>" 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="<span style="color:#ff0000;">userResultMap</span>" parameterMap="<span style="color:#ff0000;">loginUserParameters</span>" >
{? = call f_login_user(?,?)}
</procedure>
</sqlMap>
<?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" <span style="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlStringArrayTypeHandler</span>"/>
<result property="privilege" column="privilege" <span style="color:#ff0000;">typeHandler="org.kylin.emap.util.ibatis.PgsqlIntegerArrayTypeHandler</span>"/>
</resultMap>
<parameterMap id="<span style="color:#ff0000;">loginUserParameters</span>" class="java.util.HashMap">
<parameter property="result" <span style="color:#ff0000;">jdbcType="OTHER" javaType="java.sql.ResultSet</span>" 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="<span style="color:#ff0000;">userResultMap</span>" parameterMap="<span style="color:#ff0000;">loginUserParameters</span>" >
{? = 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得到这个结果集:
view plaincopy to clipboardprint?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);
}
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 来返回多个结果集。