CODEWITHSUNDEEP
springoracle-databasespring-jdbc
David Balažic
David Balažic

Reputation: 1473

Storing enum property via BeanPropertySqlParameterSource fails with SQLException: Invalid conversion requested

Java, Spring JDBC v3.0.5

I defined a DTO which has among others a property of type of custom enum. When sending it as parameters to a stored procedure call via a BeanPropertySqlParameterSource object, all other properties (that are "normal" types, like: Long, String and Boolean) work fine, except the enum type. It throws:

java.sql.SQLException: Invalid conversion requested

Looking into the StatementCreatorUtils.java method setValue() I see that it does not handle Types.CHAR which is the sqlType of the enum object (I know this from debugging - that is actually the declared type of the parameter in the SQL procedure).

Reading the same DTO (by another procedure) via a BeanPropertyRowMapper works fine.

Is this some bug or omission in Spring code?

The DTO looks like this:

public class MyDTO extends Serializable {
    private Long num;
    private Boolean bool;
    private String str;

    public static enum MyEnum { A , B }
    private MyEnum en;
    // getters and setters omitted 
}

// the call:
new SimpleJdbcCall(m_dataSource).withProcedureName("procedureName").withSchemaName("schema").executeObject(BigDecimal.class, new BeanPropertySqlParameterSource(aMyDTO)).longValue();

I worked the problem around by making MyEnum implement the java.lang.CharSequence interface, so the isStringValue() method in the StatementCreatorUtils class "thinks" it is a string and calls:

ps.setString(paramIndex, inValue.toString());

It is the same line as a few lines above:

    else if (sqlType == Types.VARCHAR || sqlType == Types.LONGVARCHAR ||
            (sqlType == Types.CLOB && isStringValue(inValue.getClass()))) {
        ps.setString(paramIndex, inValue.toString());
    }

which is skipped since it does not allow the CHAR sqlType.

The database used is Oracle and the actual exception is thrown from the Oracle driver, so it has a similar problem (can't convert from enum to String). The procedure parameter is defined as "IN CHAR".

The exception thrown is:

Caused by: java.sql.SQLException: Invalid conversion requested
at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:13780)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:13682)
at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:14515)
at oracle.jdbc.driver.OracleCallableStatement.setObject(OracleCallableStatement.java:10918)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:735)
at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:356)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:127)
at org.springframework.jdbc.core.CallableStatementCreatorFactory$CallableStatementCreatorImpl.createCallableStatement(CallableStatementCreatorFactory.java:212)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:947)

Edit:

I found another workaround in the Spring forum:

paramSource.registerSqlType("en", Types.VARCHAR);

Upvotes: 3

Views: 3893

Answers (1)

Pavel
Pavel

Reputation: 2776

BeanPropertySqlParameterSource parameterSource = new BeanPropertySqlParameterSource(pojo) {
    @Override
    public Object getValue(String paramName) throws IllegalArgumentException {
        Object value = super.getValue(paramName);
        if (value instanceof Enum) {
            return value.toString();
        }

        return value;
    }
};

Upvotes: 3

Related Questions