使用MyBatis调用Oracle存储过程

问题描述：

我正在将数据库从SQL Server 2008迁移到Oracle，但是无法使MyBatis正常工作.

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

给出以下示例:

UserMapper.xml (示例)

<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过程

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

...在SQL Server 2008中有效.有人可以向我解释如何从UserMapper.xml调用Oracle过程(与上面的SQL Server过程具有相同的名称和列)，并使用以下方法填充我的User类:一个Oracle游标?

...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?

这是我尝试过的:

<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>

我得到这个错误:

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'

答

结果图如下:

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

在您的select语句中，将参数类型更改为java.util.Map.

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>

您的映射器界面看起来像这样，看起来您当前将其称为DAO.我过去做的方法是制作一个映射器接口，该接口被注入到DAO中，而DAO就是在映射器上调用方法的地方.这是一个示例映射器界面:

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);
}

然后将该映射器类注入DAO类中，并进行如下调用:

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

使用MyBatis调用Oracle存储过程

相关推荐