How to create a typeHandler for a user defined SQL type?

[rwfischer]

Hello,

how do I create a type handler to map between a user defined SQL type and its corresponding Java type?

Let's say I have:

-- user defined SQL type
create type my_sql_type as (
  an_int integer,
  a_text text,
  a_number real
);

// java type
class MySqlType {
  private Integer integerField;
  private String stringField;
  private Fload numberField;
  // getters and setters here
}

Now let's assume this is an out parameter from a stored procedure. In the type handler I need:

  @Override
  public MySqlType getNullableResult( CallableStatement cs, int columnIndex) throws SQLException {
    MySqlType javaParam = new MySqlType;
    ??? = cs.getObject( columnIndex);
    ???
    javaParam.setIntegerFild( ???);
    javaParam.setStringField( ???);
    javaParam.setNumberField( ???);
    return javaParam;
  }

For completeness, here is the mapper XML:

  <select id="myStoredProcedure" parameterType="hashmap" statementType="CALLABLE">
    { CALL my_stored_procedure( #{in1, jdbcType=INTEGER, #{in2, jdbcType=VARCHAR},
        #{out, jdbcType=STRUCT, typeHandler=my.package.handler.MySqlTypeTypeHandler, mode=OUT} ) }
  </select>

and the stored procedure:

create function my_stored_procedure( in_1 integer, in_2 text, out result my_sql_type ) AS $$
begin
  result.an_int := ...

Please help me fill in the blanks in the type handler.

Thanks....

[Guy Rouillier]

How would you address this situation with straight JDBC, without using MyBatis?  That may provide the answer to how you address with MyBatis.  Don't you need some type of indicator that lets you know the actual return type for a particular invocation?

--

Guy Rouillier

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[rwfischer]

I was hoping that MyBatis provides an easier way than straight JDBC for this.

I have not tried it yet, but I would think the following approach would work:

Struct dbParam = cs.getObject( columnIndex);
Object[] dbParamFields = dbParam.getAttributes(); // order is an_int, a_text, a_number
javaParam.setIntegerFild( (Integer) dbParamFields[0]);
javaParam.setStringField( (String) dbParamFields[1]);
javaParam.setNumberField( (Float) dbParamFields[2]);

But I don't see how to do the opposite, set an IN parameter. The only implementation of Struct, SerialStruct, does not have a setAttributes() method.

Roger

[Iwao AVE!]

Hi Roger,

If your DB is Oracle, this might help.

https://github.com/harawata/mybatis-issues/blob/master/ml-20170926T060927/src/test/java/test/ListTypeHandler.java

Regards,

Iwao

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

[rwfischer]

Thanks, Iwao. That is an interesting example.

I am using Postgres. I will have to look at the Postgres driver if it has similar extensions.

Roger

[rwfischer]

Ok, I had some time to read the JDBC spec. When setting a user defined type, the Struct has to be obtained from the connection, similar to how (eg) an array is obtained.

// create an array of the fields in the user defined type, in the order the fields are defined in the DB
Object[] dbParamFields = { javaParam.getIntegerField(),  javaParam.getStringField(), javaParam.getNumberField() };

// then create a struct from the array, using the connection's struct builder
Struct dbParam = ps.getConnection().createStruct( "my_sql_type", dbParamFields);

// finally set the the parameter in the prepared statement
ps.setObject( columnIndex, dbParam);

I have not yet tried this, but will so soon.

Roger

[rwfischer]

Nice try, but no cigar. :-(

The PosgreSQL Java driver does not seem to support the JDBC STRUCT type. Got this:

 java.sql.SQLFeatureNotSupportedException: Method org.postgresql.jdbc.PgConnection.createStruct(String, Object[]) is not yet implemented.

Roger

[Iwao AVE!]

That's a shame.

There is an open feature request for Struct support.

https://github.com/pgjdbc/pgjdbc/issues/381

I haven't tried, but...

This thread explains how it needs to be done.

http://www.postgresql-archive.org/quot-create-type-quot-custom-types-not-supported-by-JDBC-td2173342.html

There is another JDBC driver that supports user defined types.

http://impossibl.github.io/pgjdbc-ng/

Hope this helps,

Iwao

To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user+unsubscribe@googlegroups.com.

[rwfischer]

Really no luck with this. I figured out that I have to use PGobject. But using an UDT out parameter fails with an exception that cannot be correct. I suspect that there is a bug with either MyBatis or the PG driver. I am running out of time and can't pursue this further.

For types not supported by JDBC, PGobject serializes the types in field order, depth first. The example I used above would look like this: "(4,blah,5.55)".

The type-handler methods for the parameters should be like this (results are similar).

  @Override
  public void setNonNullParameter( PreparedStatement ps, int columnIndex, Object parameter, JdbcType jdbcType) throws SQLException {
    MyJavaType param = (MyJavaType) parameter;
    PGobject pgObj = new PGobject();
    pgObj.setType( "my_sql_type");
    pgObj.setValue( "(" + param.getAnInt() + "," + param.getAString() + "," + param.getANumber() + ")");
    ps.setObject( columnIndex, pgObj);
  }




  @Override
  public Object getNullableResult( CallableStatement cs, int columnIndex) throws SQLException {
    PGobject pgObj = (PGobject) cs.getObject( columnIndex);
    if (! "my_sql_type".equals( pgObj.getType())) throw new SQLException( "invalid object type; expected my_sql_type; got: " + pgObj.getType());
    String[] values = pgObj.getValue().substring( 1, pgObj.getValue().length() - 1).split( ",");
    param.setAnInt( Integer.valueOf( values[0]);
    param.setAString( values[1]);
    param.setANumber( Float.valueOf( values[2]);
    return (Object) param;
  }




Now the exception I get when using the user defined type (UDT) as an out parameter is:

org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: org.postgresql.util.PSQLException: A CallableStatement was executed with an invalid number of parameters
### The error may exist in mapper/_test_/FunctionsAndProceduresMapper.xml
### The error may involve com.brocade.dcm.domain.mapper._test_.FunctionsAndProceduresMapper.procWithUdtOut-Inline
### The error occurred while setting parameters
### SQL: { CALL dcmtest.proc_with_udt_out(          ?       )     }
### Cause: org.postgresql.util.PSQLException: A CallableStatement was executed with an invalid number of parameters
 at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:150)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
 at java.lang.reflect.Method.invoke(Unknown Source)
 at org.apache.ibatis.session.SqlSessionManager$SqlSessionInterceptor.invoke(SqlSessionManager.java:357)
 at com.sun.proxy.$Proxy2.selectOne(Unknown Source)
 at org.apache.ibatis.session.SqlSessionManager.selectOne(SqlSessionManager.java:166)
 at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82)
 at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
 at com.sun.proxy.$Proxy6.procWithUdtOut(Unknown Source)
 at com.brocade.dcm.domain.AdHocDomainTest.testCallingFunctionsAndProcedures(AdHocDomainTest.java:383)
 at com.brocade.dcm.domain.AdHocDomainTest.main(AdHocDomainTest.java:95)
Caused by: org.postgresql.util.PSQLException: A CallableStatement was executed with an invalid number of parameters
 at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:103)
 at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:158)
 at org.apache.ibatis.executor.statement.CallableStatementHandler.query(CallableStatementHandler.java:67)
 at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
 at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
 at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324)
 at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
 at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
 at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83)
 at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
 ... 14 more

But I am absolutely sure that everything I did was right.

So, use a UDT return value instead. That is simpler (does not require a type handler).

Roger