Geek asks and answers

Calling Oracle Stored Procedures with MyBatis

I am going to move our database to Oracle from SQL Server 2008, but cannot get MyBatis to work.

In 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 explain to me how to call the Oracle procedure (with the same name and columns as the SQL Server procedure above) from UserMapper.xml, and populate my Oracle user cursor class?

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'
+5
source share
4 answers

The result map is as follows:

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

In your select expression, 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>

, , DAO. , , - mapper, DAO, DAO - , mapper. mapper:

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

mapper DAO :

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"));
}
+8

Oracle 11 MyBatis/iBATIS 3 - . , . , :

create or replace 
PROCEDURE SP_GET_ALL_STORED_PROC (l_cursor out SYS_REFCURSOR) IS
BEGIN
open l_cursor for select account_id, totalLegs, born, weight, mammal, animal from copybittest;
END SP_GET_ALL_STORED_PROC;

Map map = new HashMap();
session.selectList("ibatis_3_test.selectProductAllOracleStoredProc", map); 
List productList = (List) map.get("key");

<resultMap id="productResultMap" type="test.Product">
</resultMap>

<select id="selectProductAllOracleStoredProc" parameterType="java.util.Map" statementType="CALLABLE">
    {call SP_GET_ALL_STORED_PROC(#{key, jdbcType=CURSOR, mode=OUT, javaType=java.sql.ResultSet,resultMap=productResultMap})}
</select>
+1

clav, Snoozy, resultSet

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

"", :

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

, .

+1

.

: org.apache.ibatis.reflection.ReflectionException: setter 'columnNames' 'class java.lang.Class'

mapper.java getSearchResult (searchCriteriaVO vo)

mapper.xml

  #{userId, mode=IN, jdbcType=VARCHAR},
    #{resultList, mode=OUT, jdbcType=CURSOR, javaType=ResultSet,  resultMap=inquiryResult},

queryResult

<resultMap type="java.util.LinkedHashMap" id="inquiryResult">
<result property="name" jdbcType="VARCHAR" javaType="String" column="name"/>

, , . Value null mapper. , VO null, mybatis null. mybatis VO, null, .

,

0

More articles:


All Articles