As the title says, in PostgreSQL, when I defined a function with CURSOR set in the OUT parameter and executed it from MyBatis, an error occurred. I tried to find out the cause and how to avoid it.
The table definition and function definition look like this.
CREATE TABLE test (
id serial,
hoge character(10),
fuga character(10)
);
INSERT INTO test (hoge, fuga) VALUES('hogehoge', 'fugafuga');
INSERT INTO test (hoge, fuga) VALUES('hogege', 'fugaga');
CREATE FUNCTION testfunc (rc_out OUT refcursor) RETURNS refcursor AS $$
BEGIN
OPEN rc_out FOR SELECT * FROM test;
END;
$$ LANGUAGE plpgsql;
Execute using CallableStatement. Close processing is omitted.
public class App {
public static void main(String[] args) throws Exception {
HikariDataSource ds = new HikariDataSource();
ds.setDriverClassName("org.postgresql.Driver");
ds.setJdbcUrl("jdbc:postgresql://localhost:5432/postgres");
ds.setUsername("postgres");
ds.setPassword("postgres");
Connection conn = ds.getConnection();
conn.setAutoCommit(false);
CallableStatement cstmt = conn.prepareCall("{call testfunc(?)}");
cstmt.registerOutParameter(1, Types.REF_CURSOR);
cstmt.execute();
ResultSet rs = (ResultSet)cstmt.getObject(1);
while(rs.next()){
System.out.println(rs.getInt(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
}
}
}
The execution result looks like the following. I can do it properly.
1
hogehoge
fugafuga
2
hogege
fugaga
Create a Test class for mapping the execution result and its wrapper.
@Data
public class Test {
private int id;
private String hoge;
private String fuga;
}
@Data
public class TestWrapper {
private List<Test> test;
}
Next, create Mapper. Even if you omit @Result, automatic mapping works well.
public interface TestMapper {
@Select(value = "{call testfunc(#{test, mode=OUT, jdbcType=CURSOR, resultMap=testMap})}")
@Options(statementType = StatementType.CALLABLE)
@ResultType(Test.class)
@Results(id = "testMap", value = {
@Result(id = true, column = "id", property = "id"),
@Result(column = "hoge", property = "hoge"),
@Result(column = "fuga", property = "fuga")
})
void out(TestWrapper wrapper);
}
Try running it as follows.
public class App {
public static void main(String[] args) throws Exception {
HikariDataSource ds = new HikariDataSource();
ds.setDriverClassName("org.postgresql.Driver");
ds.setJdbcUrl("jdbc:postgresql://localhost:5432/postgres");
ds.setUsername("postgres");
ds.setPassword("postgres");
Environment env = new Environment("postgres", new JdbcTransactionFactory(), ds);
Configuration config = new Configuration(env);
config.addMapper(TestMapper.class);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
try(SqlSession session = sqlSessionFactory.openSession()){
TestWrapper wrapper = new TestWrapper();
session.getMapper(TestMapper.class).out(wrapper);
System.out.println(wrapper);
}
}
}
When I run it, I get this error:
Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.postgresql.util.PSQLException:The CallableStatement function is executed and output parameter 1 is java.sql.Types=It was a 2012 type. But java.sql.Types=-Type 10 has been registered.
### The error may exist in com/example/TestMapper.java (best guess)
### The error may involve com.example.TestMapper.out-Inline
### The error occurred while setting parameters
### SQL: {call testfunc(?)}
### Cause: org.postgresql.util.PSQLException:The CallableStatement function is executed and output parameter 1 is java.sql.Types=It was a 2012 type. But java.sql.Types=-Type 10 has been registered.
The SQL side returns with the type of 2012 (java.sql.Types.REF_CURSOR), but it seems that the type of the receiving side is -10 and the types do not match.
Therefore, the type specified by registerOutParameter in CallableStatement seems to be strange.
MyBatis uses the registerOutputParameters method of CallableStatementHandler to set the TYPE_CODE of JdbcType obtained from ParameterMapping as the type.
CallableStatementHandler.java
So, when I looked at JdbcType.CURSOR, I found that TYPE_CODE was -10.
JdbcType.java
So, the cause is here. Since the comment says Oracle, is Oracle -10 instead of 2012? ?? Does that mean it's for Oracle and doesn't support PostgreSQL?
If you can set 2012 with registerOutParameter, it seems to work, so try setting it with Interceptor.
http://www.mybatis.org/mybatis-3/ja/configuration.html#plugins
@Intercepts({ @Signature(type = StatementHandler.class, method = "parameterize", args = { Statement.class }) })
public class CursorInterceptor implements Interceptor {
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object ret = invocation.proceed();
if(!(invocation.getArgs()[0] instanceof CallableStatement)){
return ret;
}
CallableStatement cstmt = (CallableStatement) invocation.getArgs()[0];
List<ParameterMapping> parameterMappings = ((StatementHandler) invocation.getTarget()).getBoundSql()
.getParameterMappings();
int parameterIndex = 1;
for (ParameterMapping parameterMapping : parameterMappings) {
if ((parameterMapping.getMode() == ParameterMode.INOUT || parameterMapping.getMode() == ParameterMode.OUT)
&& parameterMapping.getJdbcType() == JdbcType.CURSOR) {
cstmt.registerOutParameter(parameterIndex, Types.REF_CURSOR);
}
parameterIndex++;
}
return ret;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
JdbcType.CURSOR is registered as Types.REF_CURSOR for the specified OUT and INOUT parameters.
By calling ʻinvocation.proceed (), the processing of CallableStatementHandler` is executed, so if you do not execute it in advance, the value you set will be overwritten.
To register an Interceptor, use the ʻaddInterceptor method of the Configuration` class.
public class App {
public static void main(String[] args) throws Exception {
HikariDataSource ds = new HikariDataSource();
ds.setDriverClassName("org.postgresql.Driver");
ds.setJdbcUrl("jdbc:postgresql://localhost:5432/postgres");
ds.setUsername("postgres");
ds.setPassword("postgres");
Environment env = new Environment("postgres", new JdbcTransactionFactory(), ds);
Configuration config = new Configuration(env);
config.addMapper(TestMapper.class);
config.addInterceptor(new CursorInterceptor()); //★ Here ★
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(config);
try(SqlSession session = sqlSessionFactory.openSession()){
TestWrapper wrapper = new TestWrapper();
session.getMapper(TestMapper.class).out(wrapper);
System.out.println(wrapper);
}
}
}
The execution result looks like the following.
TestWrapper(test=[Test(id=1, hoge=hogehoge, fuga=fugafuga), Test(id=2, hoge=hogege, fuga=fugaga)])
I couldn't tell if this was a bug or a spec. However, I feel that I have become a little more familiar with the contents of MyBatis through various investigations.
Recommended Posts