'JDBC Spring 4 NamedParameterJdbcTemplate with enum

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

spring spring-jdbc

Solution 1:^[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

Solution 2:^[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);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	user3808335
Solution 2	Italo Borssatto

'JDBC Spring 4 NamedParameterJdbcTemplate with enum

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]