Oracle BigDecimal NULL handling and JDBCTypes

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

Oracle BigDecimal NULL handling and JDBCTypes

Nick Jones
Hi,

First off. MyBatis is great.

I'd like to clarify if there is any way I can work around type hinting of NULL values in MyBatis statements. Below is a contrived example that demonstrates the issue we are running into. Note this is for an automated conversion so we don't control much of the SQL we are trying to run.

Given the setup below and given that the DTO has a BigDecimal parameter, is there any way MyBatis can automagically infer the NUMERIC jdbcType in the case of NULLs? If not is there a better hook than having to define this in every statement, I.e. a type mapping table?

Thanks for your help.

SQL

CREATE TABLE NULL_UNION_EXAMPLE (
anumber NUMBER(12,4)
)
/

INSERT INTO NULL_UNION_EXAMPLE(anumber) VALUES (12345.67)
/

COMMIT
/

DTO

import java.math.BigDecimal;

public class TestNullDTO {
private BigDecimal aNumber;

public BigDecimal getaNumber() {
return aNumber;
}

public void setaNumber(BigDecimal aNumber) {
this.aNumber = aNumber;
}
}

Failing Case

### SQL: SELECT anumber FROM NULL_UNION_EXAMPLE         UNION         SELECT ? FROM NULL_UNION_EXAMPLE
### Cause: java.sql.SQLSyntaxErrorException: ORA-01790: expression must have same datatype as corresponding expression

<select id="nullDemoMybatisList" parameterType="TestNullDTO" resultType="TestNullDTO">
SELECT anumber FROM NULL_UNION_EXAMPLE
UNION
SELECT #{aNumber} FROM NULL_UNION_EXAMPLE
</select>

Working Case

<select id="nullDemoMybatisList" parameterType="TestNullDTO" resultType="TestNullDTO">
SELECT anumber FROM NULL_UNION_EXAMPLE
UNION
SELECT #{aNumber,jdbcType=NUMERIC} FROM NULL_UNION_EXAMPLE
</select>

Code To Execute

TestNullDTO testNullUnion = new TestNullDTO();
testNullUnion.setaNumber(null);
return sqlSession.selectList("nullDemoMybatisList", testNullUnion);

Drivers/Versions

Oracle 11
<ojdbc8.version>12.2.0.1</ojdbc8.version>
<oracle.ucp.version>12.2.0.1</oracle.ucp.version>
<mybatis.spring.boot.version>1.3.1</mybatis.spring.boot.version>

--
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: Oracle BigDecimal NULL handling and JDBCTypes

Iwao AVE!

Hi Nick,

Although casting the parameter (i.e. CAST(#{aNumber} AS NUMERIC)) seems to be a better solution, a custom type handler might work.

@MappedTypes(BigDecimal.class)
public class CustomBigDecimalTypeHandler  implements TypeHandler<BigDecimal>{
  @Override
  public void setParameter(PreparedStatement ps, int i, BigDecimal parameter, JdbcType jdbcType) throws SQLException {
    if (parameter == null) {
      try {
        ps.setNull(i, Types.NUMERIC);
      } catch (SQLException e) { throw new TypeException("...", e); }
    } else {
      try {
        ps.setBigDecimal(i, parameter);
      } catch (Exception e) { throw new TypeException("...", e); }
    }
  }

...
<typeHandlers>
  <typeHandler handler="test.CustomBigDecimalTypeHandler" />
</typeHandlers>

Regards,
Iwao


On Mon, Dec 10, 2018 at 10:16 AM Nick Jones <[hidden email]> wrote:
Hi,

First off. MyBatis is great.

I'd like to clarify if there is any way I can work around type hinting of NULL values in MyBatis statements. Below is a contrived example that demonstrates the issue we are running into. Note this is for an automated conversion so we don't control much of the SQL we are trying to run.

Given the setup below and given that the DTO has a BigDecimal parameter, is there any way MyBatis can automagically infer the NUMERIC jdbcType in the case of NULLs? If not is there a better hook than having to define this in every statement, I.e. a type mapping table?

Thanks for your help.

SQL

CREATE TABLE NULL_UNION_EXAMPLE (
anumber NUMBER(12,4)
)
/

INSERT INTO NULL_UNION_EXAMPLE(anumber) VALUES (12345.67)
/

COMMIT
/

DTO

import java.math.BigDecimal;

public class TestNullDTO {
private BigDecimal aNumber;

public BigDecimal getaNumber() {
return aNumber;
}

public void setaNumber(BigDecimal aNumber) {
this.aNumber = aNumber;
}
}

Failing Case

### SQL: SELECT anumber FROM NULL_UNION_EXAMPLE         UNION         SELECT ? FROM NULL_UNION_EXAMPLE
### Cause: java.sql.SQLSyntaxErrorException: ORA-01790: expression must have same datatype as corresponding expression

<select id="nullDemoMybatisList" parameterType="TestNullDTO" resultType="TestNullDTO">
SELECT anumber FROM NULL_UNION_EXAMPLE
UNION
SELECT #{aNumber} FROM NULL_UNION_EXAMPLE
</select>

Working Case

<select id="nullDemoMybatisList" parameterType="TestNullDTO" resultType="TestNullDTO">
SELECT anumber FROM NULL_UNION_EXAMPLE
UNION
SELECT #{aNumber,jdbcType=NUMERIC} FROM NULL_UNION_EXAMPLE
</select>

Code To Execute

TestNullDTO testNullUnion = new TestNullDTO();
testNullUnion.setaNumber(null);
return sqlSession.selectList("nullDemoMybatisList", testNullUnion);

Drivers/Versions

Oracle 11
<ojdbc8.version>12.2.0.1</ojdbc8.version>
<oracle.ucp.version>12.2.0.1</oracle.ucp.version>
<mybatis.spring.boot.version>1.3.1</mybatis.spring.boot.version>

--
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: Oracle BigDecimal NULL handling and JDBCTypes

Nick Jones
Thanks Iwao.

I'm getting some mileage out of this suggestion.

On Wednesday, 12 December 2018 23:56:09 UTC+13, Iwao AVE! wrote:

Hi Nick,

Although casting the parameter (i.e. CAST(#{aNumber} AS NUMERIC)) seems to be a better solution, a custom type handler might work.

@MappedTypes(BigDecimal.class)
public class CustomBigDecimalTypeHandler  implements TypeHandler<BigDecimal>{
  @Override
  public void setParameter(PreparedStatement ps, int i, BigDecimal parameter, JdbcType jdbcType) throws SQLException {
    if (parameter == null) {
      try {
        ps.setNull(i, Types.NUMERIC);
      } catch (SQLException e) { throw new TypeException("...", e); }
    } else {
      try {
        ps.setBigDecimal(i, parameter);
      } catch (Exception e) { throw new TypeException("...", e); }
    }
  }

...
<typeHandlers>
  <typeHandler handler="test.CustomBigDecimalTypeHandler" />
</typeHandlers>

Regards,
Iwao


On Mon, Dec 10, 2018 at 10:16 AM Nick Jones <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="w_EV1IOXBQAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">whatsyourpro...@gmail.com> wrote:
Hi,

First off. MyBatis is great.

I'd like to clarify if there is any way I can work around type hinting of NULL values in MyBatis statements. Below is a contrived example that demonstrates the issue we are running into. Note this is for an automated conversion so we don't control much of the SQL we are trying to run.

Given the setup below and given that the DTO has a BigDecimal parameter, is there any way MyBatis can automagically infer the NUMERIC jdbcType in the case of NULLs? If not is there a better hook than having to define this in every statement, I.e. a type mapping table?

Thanks for your help.

SQL

CREATE TABLE NULL_UNION_EXAMPLE (
anumber NUMBER(12,4)
)
/

INSERT INTO NULL_UNION_EXAMPLE(anumber) VALUES (12345.67)
/

COMMIT
/

DTO

import java.math.BigDecimal;

public class TestNullDTO {
private BigDecimal aNumber;

public BigDecimal getaNumber() {
return aNumber;
}

public void setaNumber(BigDecimal aNumber) {
this.aNumber = aNumber;
}
}

Failing Case

### SQL: SELECT anumber FROM NULL_UNION_EXAMPLE         UNION         SELECT ? FROM NULL_UNION_EXAMPLE
### Cause: java.sql.SQLSyntaxErrorException: ORA-01790: expression must have same datatype as corresponding expression

<select id="nullDemoMybatisList" parameterType="TestNullDTO" resultType="TestNullDTO">
SELECT anumber FROM NULL_UNION_EXAMPLE
UNION
SELECT #{aNumber} FROM NULL_UNION_EXAMPLE
</select>

Working Case

<select id="nullDemoMybatisList" parameterType="TestNullDTO" resultType="TestNullDTO">
SELECT anumber FROM NULL_UNION_EXAMPLE
UNION
SELECT #{aNumber,jdbcType=NUMERIC} FROM NULL_UNION_EXAMPLE
</select>

Code To Execute

TestNullDTO testNullUnion = new TestNullDTO();
testNullUnion.setaNumber(null);
return sqlSession.selectList("nullDemoMybatisList", testNullUnion);

Drivers/Versions

Oracle 11
<ojdbc8.version>12.2.0.1</ojdbc8.version>
<oracle.ucp.version>12.2.0.1</oracle.ucp.version>
<mybatis.spring.boot.version>1.3.1</mybatis.spring.boot.version>

--
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="w_EV1IOXBQAJ" 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.