Calling Oracle stored procedures with MyBatis

Question

I am in the process of moving our database over to Oracle from SQL Server 2008 but cannot get MyBatis to work.

Given the following example:

UserMapper.xml (example)

<resultMap type="User" id="UserResult">
    <id property="userId" column="userId"/>
    <result property="firstName" column="firstName"/>
    <result property="lastName" column="lastName"/>
</resultMap>

<select id="getUsers" statementType="CALLABLE" resultMap="UserResult">
    {CALL GetUsers()}
</select>

UserDAO.java

public interface UserDAO {
    public List<User> getUsers();
}

SQL Server procedure

CREATE PROCEDURE [dbo].[GetUsers]
AS
BEGIN
    SET NOCOUNT ON;
    SELECT userId, firstName, lastName
    FROM Users
END

...works in SQL Server 2008. Can someone please explain to me how to call the Oracle procedure (that has the same name and columns as the SQL Server procedure above) from the UserMapper.xml and populate my User class with an Oracle cursor?

This is what I tried:

<resultMap type="User" id="UserResult">
    <id property="userId" column="userId"/>
    <result property="firstName" column="firstName"/>
    <result property="lastName" column="lastName"/>
</resultMap>

<select id="getUsers" statementType="CALLABLE" resultMap="UserResult">
    {CALL GetUsers(#{resultSet,mode=OUT,jdbcType=CURSOR,resultMap=UserResult})}
</select>

and I get this error:

Caused by: org.apache.ibatis.reflection.ReflectionException: 
Could not set property 'resultSet' of 'class java.lang.Class'
with value 'oracle.jdbc.driver.OracleResultSetImpl@476d05dc' 
Cause: org.apache.ibatis.reflection.ReflectionException: 
There is no setter for property named 'resultSet' in 'class java.lang.Class'

Answer 1

Result map looks like this:

<resultMap id="UserResult" type="User">
    <id property="userId" column="userId"/>
    <result property="firstName" column="firstName"/>
    <result property="lastName" column="lastName"/>     
</resultMap>

In your select statement, change the parameter type to java.util.Map.

<select id="getUsers" statementType="CALLABLE" parameterType="java.util.Map"> 
    {call GetUsers(#{users, jdbcType=CURSOR, javaType=java.sql.ResultSet, mode=OUT, resultMap=UserResult})} 
</select>

Your mapper interface looks like this, it looks like you are currently calling this the DAO. The way I've done it in the past is to make a mapper interface that gets injected into the DAO and the DAO is what calls the methods on the mapper. Here's an example mapper interface:

public interface UserMapper {
    public Object getUsers(Map<String, Object> params);
}

That mapper class would then get injected into a DAO class and make the call like this:

public List<User> getUsers() {
    Map<String, Object> params = new HashMap<String, Object>(); 
    ResultSet rs = null;
    params.put("users", rs);
    userMapper.getUsers(params);
    return ((ArrayList<User>)params.get("users"));
}