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

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

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

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Guy Rouillier-2
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

------ Original Message ------
From: "rwfischer" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 11/15/2017 10:24:59 PM
Subject: How to create a typeHandler for a user defined SQL type?

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

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

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

On Wednesday, November 15, 2017 at 9:09:19 PM UTC-8, Guy Rouillier wrote:
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

------ Original Message ------
From: "rwfischer" <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="l2q658frBgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">rwfi...@...>
To: "mybatis-user" <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="l2q658frBgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybati...@...>
Sent: 11/15/2017 10:24:59 PM
Subject: How to create a typeHandler for a user defined SQL type?

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

--
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 <a href="javascript:" target="_blank" gdf-obfuscated-mailto="l2q658frBgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Iwao AVE!

2017-11-17 3:04 GMT+09:00 rwfischer <[hidden email]>:
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

On Wednesday, November 15, 2017 at 9:09:19 PM UTC-8, Guy Rouillier wrote:
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

------ Original Message ------
From: "rwfischer" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 11/15/2017 10:24:59 PM
Subject: How to create a typeHandler for a user defined SQL type?

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

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

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

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

On Thursday, November 16, 2017 at 10:42:11 AM UTC-8, Iwao AVE! wrote:
Hi Roger,

If your DB is Oracle, this might help.
<a href="https://github.com/harawata/mybatis-issues/blob/master/ml-20170926T060927/src/test/java/test/ListTypeHandler.java" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Fblob%2Fmaster%2Fml-20170926T060927%2Fsrc%2Ftest%2Fjava%2Ftest%2FListTypeHandler.java\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFwSu_hHsn2LgvD66bT1he-2GQO7w&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fharawata%2Fmybatis-issues%2Fblob%2Fmaster%2Fml-20170926T060927%2Fsrc%2Ftest%2Fjava%2Ftest%2FListTypeHandler.java\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFwSu_hHsn2LgvD66bT1he-2GQO7w&#39;;return true;">https://github.com/harawata/mybatis-issues/blob/master/ml-20170926T060927/src/test/java/test/ListTypeHandler.java

Regards,
Iwao

2017-11-17 3:04 GMT+09:00 rwfischer <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="Ev7amSMYBwAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">rwfi...@...>:
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

On Wednesday, November 15, 2017 at 9:09:19 PM UTC-8, Guy Rouillier wrote:
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

------ Original Message ------
From: "rwfischer" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 11/15/2017 10:24:59 PM
Subject: How to create a typeHandler for a user defined SQL type?

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

--
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 <a href="https://groups.google.com/d/optout" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.

--
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 <a href="javascript:" target="_blank" gdf-obfuscated-mailto="Ev7amSMYBwAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

rwfischer
In reply to this post by 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

On Thursday, November 16, 2017 at 10:04:59 AM UTC-8, rwfischer wrote:
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

On Wednesday, November 15, 2017 at 9:09:19 PM UTC-8, Guy Rouillier wrote:
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

------ Original Message ------
From: "rwfischer" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 11/15/2017 10:24:59 PM
Subject: How to create a typeHandler for a user defined SQL type?

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

--
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 <a href="https://groups.google.com/d/optout" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

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


On Friday, November 17, 2017 at 4:40:33 PM UTC-8, rwfischer wrote:
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

On Thursday, November 16, 2017 at 10:04:59 AM UTC-8, rwfischer wrote:
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

On Wednesday, November 15, 2017 at 9:09:19 PM UTC-8, Guy Rouillier wrote:
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

------ Original Message ------
From: "rwfischer" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 11/15/2017 10:24:59 PM
Subject: How to create a typeHandler for a user defined SQL type?

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

--
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 <a href="https://groups.google.com/d/optout" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

Iwao AVE!
That's a shame.
There is an open feature request for Struct support.

I haven't tried, but...

This thread explains how it needs to be done.

There is another JDBC driver that supports user defined types.

Hope this helps,
Iwao

2017-11-18 10:48 GMT+09:00 rwfischer <[hidden email]>:
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


On Friday, November 17, 2017 at 4:40:33 PM UTC-8, rwfischer wrote:
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

On Thursday, November 16, 2017 at 10:04:59 AM UTC-8, rwfischer wrote:
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

On Wednesday, November 15, 2017 at 9:09:19 PM UTC-8, Guy Rouillier wrote:
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

------ Original Message ------
From: "rwfischer" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 11/15/2017 10:24:59 PM
Subject: How to create a typeHandler for a user defined SQL type?

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

--
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 [hidden email]om.
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

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

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


On Saturday, November 18, 2017 at 4:44:11 AM UTC-8, Iwao AVE! wrote:
That's a shame.
There is an open feature request for Struct support.
<a onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fpgjdbc%2Fpgjdbc%2Fissues%2F381\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHGM_brGJZt5EwOc7teXdkYnu1YyA&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fgithub.com%2Fpgjdbc%2Fpgjdbc%2Fissues%2F381\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHGM_brGJZt5EwOc7teXdkYnu1YyA&#39;;return true;" href="https://github.com/pgjdbc/pgjdbc/issues/381" target="_blank" rel="nofollow">https://github.com/pgjdbc/pgjdbc/issues/381

I haven't tried, but...

This thread explains how it needs to be done.
<a onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fwww.postgresql-archive.org%2Fquot-create-type-quot-custom-types-not-supported-by-JDBC-td2173342.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFXFmCxH7PXLhCMx5fZpTl0QrdnGQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fwww.postgresql-archive.org%2Fquot-create-type-quot-custom-types-not-supported-by-JDBC-td2173342.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFXFmCxH7PXLhCMx5fZpTl0QrdnGQ&#39;;return true;" href="http://www.postgresql-archive.org/quot-create-type-quot-custom-types-not-supported-by-JDBC-td2173342.html" target="_blank" rel="nofollow">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.
<a onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fimpossibl.github.io%2Fpgjdbc-ng%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEvpHVVYpb3w34c9VZHAv7sQOmNLQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fimpossibl.github.io%2Fpgjdbc-ng%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNEvpHVVYpb3w34c9VZHAv7sQOmNLQ&#39;;return true;" href="http://impossibl.github.io/pgjdbc-ng/" target="_blank" rel="nofollow">http://impossibl.github.io/pgjdbc-ng/

Hope this helps,
Iwao

2017-11-18 10:48 GMT+09:00 rwfischer <<a onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;" href="javascript:" target="_blank" rel="nofollow" gdf-obfuscated-mailto="JXH0msOhBwAJ">rwfi...@...>:
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


On Friday, November 17, 2017 at 4:40:33 PM UTC-8, rwfischer wrote:
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

On Thursday, November 16, 2017 at 10:04:59 AM UTC-8, rwfischer wrote:
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

On Wednesday, November 15, 2017 at 9:09:19 PM UTC-8, Guy Rouillier wrote:
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

------ Original Message ------
From: "rwfischer" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 11/15/2017 10:24:59 PM
Subject: How to create a typeHandler for a user defined SQL type?

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

--
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 <a onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" href="https://groups.google.com/d/optout" target="_blank" rel="nofollow">https://groups.google.com/d/optout.

--
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 <a onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;" href="javascript:" target="_blank" rel="nofollow" gdf-obfuscated-mailto="JXH0msOhBwAJ">mybatis-user...@googlegroups.com.
For more options, visit <a onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" href="https://groups.google.com/d/optout" target="_blank" rel="nofollow">https://groups.google.com/d/optout.

--
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 [hidden email].
For more options, visit https://groups.google.com/d/optout.