JDBC Spring 4 NamedParameterJdbcTemplate with enum

Question

I have been using JdbcTemplate very happyly but then I got convinced to use NamedParameterJdbcTemplate. However not enjoying this so far sometimes or all the times.

Lets say I have a Person class as below :

public class Person implements Serializable {

    private int age;
    private String name;
    private long socialId;
    private Gender gender;
    private String email;

    enum Gender {
        MALE, FEMALE
    }

}

And I am trying to insert the list of person as below:

SqlParameterSource[] params 
= SqlParameterSourceUtils.createBatch(personList.toArray());

            getNamedJdbcTemplate().batchUpdate(insertSql, params);

However it fails with message below

PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO

at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:230)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:402)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:235)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:166)
    at org.springframework.jdbc.core.BatchUpdateUtils.setStatementParameters(BatchUpdateUtils.java:65)
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.access$000(NamedParameterBatchUpdateUtils.java:32)
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils$1.setValues(NamedParameterBatchUpdateUtils.java:48)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:999)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:989)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:644)
    ... 41 more

I debugged and can see that in StatementCreatorUtils class it will always fail if it is of Enum type in method

private static void setValue(PreparedStatement ps, int paramIndex

section of code....

else if (sqlType == SqlTypeValue.TYPE_UNKNOWN) {
            if (isStringValue(inValue.getClass())) {
                ps.setString(paramIndex, inValue.toString());
            }

QUESTION

Can someone please let me know any workaround for this? Or I need to do old way of setting data using placeholders( ?).

Many thanks

Kendallkendell

Answer 1

Got this working as below

sqlParameterSource.registerSqlType("gender", Types.VARCHAR);

This is fine but WHAT if we need to call a method on enum. Let's say getValue for enums like Male(10), FEMALE( 20)??

I am not able to use these enums very well with NamedParameterJdbcTemplate

Anyone looking to improve this in future?

Thanks

Kendallkendell

thanks, resolved my issue. Also, did you happen to find any other solution? – Dianndianna

Answer 2

The NamedParameterJdbcTemplate accepts SQL with placeholders using nested properties/methods. I don't see that in the documentation, but you can use something like:

namedParameterJdbcTemplate.batchUpdate(
       "INSERT INTO mytable " +
       "(myVarcharColumn, myIntColumn) " +
       "VALUES (:myEnum1.name, :myEnum2.ordinal)", 
       batchArgs);

Banda