Java boolean is not handled correctly when used with Oracle JDBC driver [SPR-14116]

[spring-projects-issues]

Igor Voshkulat opened SPR-14116 and commented

Database field is defined as NUMBER(1,0).
Model object with a boolean field is mapped to parameters via SqlParameterSourceUtil.createBatch().

In version 3.2.2.RELEASE StatementCreationUtils.javaTypeToSqlTypeMap had no mapping for java boolean (more precisely it was commented out) and was working fine. This resulted in StatementCreationUtils.setValue() to execute ps.setObject(paramIndex, inValue).

After boolean mapping was uncommented ps.setObject(paramIndex, inValue, sqlType) is used.

Oracle doesn't have proper matching boolean type, however driver is smart enough to do automatic conversion from boolean to NUMBER.

Therefore following statements are valid:
ps.setObject(1, true);
ps.setBoolean(1, true);
ps.setObject(1, true, Types.NUMERIC); //very narrow-minded, works for this particular case but not generic enough

What is currently done:
ps.setObject(1, true, Types.BOOLEAN);

Which fails with invalid column type 17004.

---

Affects: 4.2.5

Issue Links:

• 'Invalid column type' exception generated when writing a Boolean data member to Oracle database [SPR-14581] #19150 'Invalid column type' exception generated when writing a Boolean data member to Oracle database ("is duplicated by")
• Regression in handling of String passed as Types.OTHER to JdbcTemplate [SPR-12890] #17488 Regression in handling of String passed as Types.OTHER to JdbcTemplate
• Oracle 12c JDBC driver throws inconsistent exception from getParameterType (affecting setNull calls) [SPR-13825] #18398 Oracle 12c JDBC driver throws inconsistent exception from getParameterType (affecting setNull calls)
• INSERT SELECT of large CLOB causes ORA-01461 on setString [SPR-12240] #16854 INSERT SELECT of large CLOB causes ORA-01461 on setString
• JdbcTemplate should support JDBC 4.1 getObject(columnIndex, type) [SPR-11600] #16223 JdbcTemplate should support JDBC 4.1 getObject(columnIndex, type)

Referenced from: commits 797f5db, 6b0f26c

[spring-projects-issues]

Juergen Hoeller commented

Thanks for raising this! We're explicitly calling PreparedStatement.setBoolean for Types.BOOLEAN now, which seems to be a sensible processing path in any case.