An error occurred when executing a function from MyBatis with the OUT parameter set to CURSOR in PostgreSQL.

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.

environment

PostgreSQL functions

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;

Try running from Java normally

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

Try running it on MyBatis

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.

Find the cause of the error

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?

I'll try to move it somehow

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.

Try again

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

Summary

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

An error occurred when executing a function from MyBatis with the OUT parameter set to CURSOR in PostgreSQL.
A validation error occurred when saving to the intermediate table.
When trying to insert a specific gem (levenshtein) with Docker, an error occurred without ruby-dev
Resolved the error that occurred when trying to use Spark in an environment where Java 8 and Java 11 coexist.
How to set the default fetch size when jOOQ gets an OOM error when fetching a huge result set
I want to display an error message when registering in the database
When introducing JOOQ to Spring boot, a story that was dealt with because an error occurred around Liquibase
How to store an object in PostgreSQL as JSON with MyBatis (Mapper XML)
When using a list in Java, java.awt.List comes out and an error occurs
NoClassDefFoundError occurred when trying to call an Android function from Unity via jar
About the solution of the error that occurred when trying to create a Japanese file of devise in the Docker development environment
Summary of how to use the proxy set in IE when connecting with Java
[JMH] What to do if the JMH Gradle Plugin says FAILURE: Build failed with an exception. A failure occurred while executing me.champeau.gradle.IsolatedRunner [Gradle]
Check how to set the timeout when connecting with Spring + HikariCP + MySQL and executing SQL
How to deal with the error ERROR: While executing gem ... (Gem :: FilePermissionError)
Mapping to a class with a value object in How to MyBatis
[Docker] The story that an error occurred in docker-compose up
How to set up a proxy with authentication in Feign
A memo of the update command when npm install causes an error in GitHub Action etc.
[Java] How to search for a value in an array (or list) with the contains method
I tried to create an API to get data from a spreadsheet in Ruby (with service account)
What to do when an UnsupportedCharsetException occurs in a lightweight JRE
[Java] Adding an element to the Collection causes a compile error
Collecting client information when an error occurs in a web application
How to output the value when there is an array in the array
I tried to translate the error message when executing Eclipse (Java)
The story that Tomcat suffered from a timeout error in Eclipse
How to solve the unknown error when using slf4j in Java
It should be the same as the sample when logging in to Twitter, but an error occurs ~ resolution
What I don't like when using interface of a function with default arguments in Kotlin from Java
How to set an image in the drawable Left / Right of a button using an icon font (Iconics)
An error occurred in the free course of RubyOnRails Udemy, solved it, and went through to the end
[Rails] What to do if you get an error saying "Could not find a JavaScript runtime." When executing the rails s command on Catalina