Calling procedure with updates and a ResultSet in MyBatis

Question

I want to map a SQL Server stored procedure with MyBatis, using annotations.

@Select(value = "{call sp_cen_obliczcene(" +
    "#{wytworId, mode=IN, jdbcType=NUMERIC}, " +
    "#{rodzajCenyId, mode=IN, jdbcType=NUMERIC}, " +
    "#{walutaId, mode=IN, jdbcType=NUMERIC}, " +
    "#{jmId, mode=IN, jdbcType=NUMERIC}, " +
    "#{ilosc, mode=IN, jdbcType=DECIMAL}, " +
    "#{data, mode=IN, jdbcType=DATE})}")
@Result(property = "kwota", column = "kwota", javaType = BigDecimal.class, jdbcType = JdbcType.DECIMAL)
@Options(statementType = StatementType.CALLABLE)
public DtoCena dajCene(CriteriaCena parametry);

The procedure selects one row - I am interested in one column. Now, I've mapped a procedure before, only I had multiple rows and selected more then one column from them. Everything worked perfectly fine. When I mapped new procedure, in a similar way I got an error:

### The error occurred while setting parameters
### SQL: {call sp_cen_obliczcene(?, ?, ?, ?, ?, ?)}
### Cause: java.lang.NullPointerException

I started the SQL Profiler and saw that the procedure is called properly with the given parameters. I've noticed that the procedure I'm mapping is executing other procedures. They're performing some updates. When I changed my annotation to @Update I got an other error: that Integer cannot be cast to DtoCena type. I changed the return value of the method to Integer and I got no errors but as you can guess it did not return what I was looking for.

The question is, can I map a stored procedure which updates tables AND returns a ResultSet? I can do this using JDBC, but is this possible with MyBatis? Am I doing something wrong when using the @Select annotation?

It tells that one of theses variables is null. You can check it in debug mode at least. Moreover, would you please publish more error information? — Horsing, Apr 26 '16 at 06:18

score 1 · Answer 1 · answered Nov 14 '16 at 16:24

Looks like the @Update returns the affected row count ... Anyway, I don't think the issue is related to calling stored procedure, this is merely a mapping issue that would occur with simple select.

You must use @Result annotation inside @Results annotation, otherwise it is ignored. Here is a simplified, yet functional, code:

@Select("select 'hello' as h, 1 as n from dual")
@Results({
    @Result(column="n")
})
Integer test();

Just add a property attribute and change return type to retrieve result into an object.

Calling procedure with updates and a ResultSet in MyBatis

1 Answers1