ibatis调用oracle存储过程返回结果集的问题

[tangxufeng]

xml代码
<!DOCTYPE mapper PUBLIC "-//ibatis.apache.org//DTD iBatis Mapper 3.0 //EN" "http://ibatis.apache.org/dtd/ibatis-3-mapper.dtd">
<mapper namespace="org.yhb.ibatis.dao.UserDAO">
<!-- 表结构
create table IBATIS_USER
(
ID NUMBER not null,
NAME VARCHAR2(20) not null,
BRITHDAY DATE not null
)
-->

<!-- 存储过程
create or replace procedure getAllUser(userList out sys_refcursor)
as
begin
open userList for select * from ibatis_user;
end;
-->

<!-- resultMap -->
<resultMap type="org.yhb.ibatis.model.User" id="userMap">
<id column="id" property="id" />
<result column="name" property="name" />
<result column="birthday" property="birthday" />
</resultMap>

<!-- 调用存储过程 -->
<select id="getAllUser" statementType="CALLABLE" resultMap="userMap">
{call getAllUser(#{userList,mode=OUT,javaType=java.sql.ResultSet,jdbcType=CURSOR,resultMap=userMap})}
</select>
</mapper>

java调用代码
public void testProcedure() throws Exception {
Reader reader = null;
reader = Resources.getResourceAsReader("SqlMapper.xml");
SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
reader.close();
SqlSession session = ssf.openSession();
Map map = new HashMap();
session.selectOne("org.yhb.ibatis.dao.UserDAO.getAllUser", map);
System.out.println(map);
//返回的集合被放入了map中
List<User> userList = (List<User>) map.get("userList");
System.out.println(userList);
session.close();
}

错误代码：
org.apache.ibatis.exceptions.IbatisException:
### Error querying database. Cause: java.lang.IllegalArgumentException: Result Maps collection does not contain value for userMap
### The error may exist in user.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: {call getAllUser(?)}

[稻米的微笑]

最近正在学Oracle

[tangxufeng]

没有回答吗，自己顶一下

[tangxufeng]

没有人回答吗，再顶一下

[forgetsam]

1 你先确定这个是mybatis还是ibatis，ibatis不是这么写。 2 你返回结果集，就老老实实用结果集，你想转化成List，就别提ResultSet这几个字 正常的写法应该是parameterMap里面嵌套一个resultMap

<resultMap type="org.yhb.ibatis.model.User" id="rrr">  
        <id column="id" property="id" />  
        <result column="name" property="name" />  
        <result column="birthday" property="birthday" />  
    </resultMap>
<parameterMap id="mapppp" type="java.util.HashMap">
    <parameter property="key" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="rrr"/> 
</parameterMap>

<select id="getAllUser" statementType="CALLABLE" parameterMap="ppp">  
    {call getAllUser(?)}  
</select>

或者mybatis里可以直接用#说明parameter，这时候根本不写parameter，#{}里面已经写过一遍了。

<resultMap type="org.yhb.ibatis.model.User" id="rrr">  
        <id column="id" property="id" />  
        <result column="name" property="name" />  
        <result column="birthday" property="birthday" />  
    </resultMap>


<select id="getAllUser" statementType="CALLABLE" >  
    {call getAllUser(#{userList,mode=OUT,javaType=java.sql.ResultSet,jdbcType=CURSOR,resultMap=userMap})} 
</select>

[forgetsam]

上面发的id全粘贴错了，自己对下吧。

[tangxufeng]

谢谢forgetsam,看了你的代码，我有一些明白了，但是调试还是出错。 iabtis 配置文件<mapper namespace="org.yhb.ibatis.dao.UserDAO"> <resultMap type="org.yhb.ibatis.model.User" id="rrr"> <id column="id" property="id"/> <result column="name" property="name"/> <result column="birthday" property="birthday" /> </resultMap> <parameterMap id="ppp" type="java.util.HashMap"> <parameter property="userList" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="rrr"/> </parameterMap> <select id="getAllUser" statementType="CALLABLE" parameterMap="ppp"> {call getAllUser(?)} </select> </mapper> java代码 Reader reader = null; reader = Resources.getResourceAsReader("SqlMapper.xml"); SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader); reader.close(); SqlSession session = ssf.openSession(); Map map = new HashMap(); session.selectOne("org.yhb.ibatis.dao.UserDAO.getAllUser", map); System.out.println(map); //返回的集合被放入了map中 List<User> userList = (List<User>) map.get("userList"); System.out.println(userList); session.close(); 错误信息 org.apache.ibatis.exceptions.IbatisException: ### Error querying database. Cause: org.apache.ibatis.builder.BuilderException: Improper inline parameter map format. Should be: #{propName,attr1=val1,attr2=val2} ### The error may exist in user.xml ### The error may involve org.yhb.ibatis.dao.UserDAO.getAllUser ### The error occurred while executing a query ### Cause: org.apache.ibatis.builder.BuilderException: Improper inline parameter map format. Should be: #{propName,attr1=val1,attr2=val2} at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:8) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:60) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:52) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:37) at org.yhb.ibatis.model.Test1.testProcedure(Test1.java:36) at org.yhb.ibatis.model.Test1.main(Test1.java:23) Caused by: org.apache.ibatis.builder.BuilderException: Improper inline parameter map format. Should be: #{propName,attr1=val1,attr2=val2} at org.apache.ibatis.builder.SqlSourceBuilder$ParameterMappingTokenHandler.buildParameterMapping(SqlSourceBuilder.java:84) at org.apache.ibatis.builder.SqlSourceBuilder$ParameterMappingTokenHandler.handleToken(SqlSourceBuilder.java:42) at org.apache.ibatis.parsing.GenericTokenParser.parse(GenericTokenParser.java:29) at org.apache.ibatis.builder.SqlSourceBuilder.parse(SqlSourceBuilder.java:23) at org.apache.ibatis.builder.xml.dynamic.DynamicSqlSource.getBoundSql(DynamicSqlSource.java:25) at org.apache.ibatis.mapping.MappedStatement.getBoundSql(MappedStatement.java:198) at org.apache.ibatis.executor.BaseExecutor.createCacheKey(BaseExecutor.java:110) at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:80) at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:67) at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:58) ... 4 more

[tangxufeng]

你的是mybatis，而我的是ibatis，版本不一樣。

[forgetsam]

我基本上直接用你的代码测试，但是我的没错。

<?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="sex"> 
    <!-- 用别名指向Java里属性  数据库字段正常写 -->
    <select id="querySex" resultType="java.util.HashMap" parameterType="java.lang.String"> 
        select * from sex
    </select> 
    
    <resultMap id="sexxx" type="com..xml.Sex">
    	<id column="ct_id" property="ct_id" /> 
    	<result column="code" property="code" />   
        <result column="codename" property="codename" />  
        <result column="fathercode" property="fathercode" />
    </resultMap>
	
	<parameterMap id="sexxxmap" type="java.util.HashMap">
		<parameter property="sexList" mode="OUT" jdbcType="CURSOR" javaType="java.sql.ResultSet" resultMap="sexxx"/> 
	</parameterMap>
    
    
    <select id="querySexPro" parameterMap="sexxxmap" statementType="CALLABLE"> 
        {call bql.pro_query_sex(?)}
    </select> 

Reader reader = Resources.getResourceAsReader("mybatis-config.xml"); 
        SqlSessionFactory sqlMapFactory = new SqlSessionFactoryBuilder().build(reader); 
        
        SqlSession session = sqlMapFactory.openSession();
        //1 常规方法 找sqlKey
        Map map = new HashMap();
        session.selectOne("sex.querySexPro", map);  
        System.out.println(map);
        //返回的集合被放入了map中
        
        List<Sex> sexList = (List<Sex>) map.get("sexList");  
        System.out.println(sexList);  
        session.close(); 

[forgetsam]

用上面的调用方式，就不需要在你的DAO接口里写方法了。

[forgetsam]

可能是你的版本问题 ibatis应该只有2，3就是mybatis了 <?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">