Unable to bind a null value for UUID column with PostgreSQL [SPR-16669]

[spring-projects-issues]

Rémi Aubel opened SPR-16669 and commented

PostgreSQL database defines the type UUID (this type does not exist in java.sql.Types).

I want to use this kind of PreparedStatement:

SELECT ... WHERE :VAL IS NOT NULL AND :VAL = <my_uuid_column>

And I want to bind a null value for VAL.
In this specific case, PostgreSQL JDBC driver requires the data type to be specified. And since the UUID data type does not exist in java.sql.Types, we must specify the type name as well, by calling PreparedStatement.setNull(\_, java.sql.Types.OTHER, "uuid").
With plain old java (Connection and PreparedStatement), this works fine.

Using NamedParameterJdbcTemplate (and providing the "data type" and "data type name" in a MapSqlParameterSource), I'm not able to make it work.
Under the hood, StatementCreatorUtils.setNull(\_, \_, type, typeName) delegates to PreparedStatement.setNull(\_, type) (with no type name) when type is Types.OTHER.

Shouldn't it call PreparedStatement.setNull(_, type, typeName) when typeName is provided (non null) instead?

---

Affects: 4.3.14, 5.0.4

Issue Links:

• Can't insert into nvarchar2 using SimpleJdbcInsert whereas it works with SimpleJdbcTemplate [SPR-8571] #13215 Can't insert into nvarchar2 using SimpleJdbcInsert whereas it works with SimpleJdbcTemplate

Referenced from: commits 9a722b4, 5629fa2

Backported to: 4.3.15

[spring-projects-issues]

Juergen Hoeller commented

This seems to be a regression introduced in Spring Framework 4.1 (#13215). I'll revise this for the upcoming 5.0.5 and 4.3.14 releases.

[spring-projects-issues]

Juergen Hoeller commented

I've resolved this through not entering our fallback block in case of Types.OTHER with a non-null typeName, going to the usual PreparedStatement.setNull variant with a type name again.

[spring-projects-issues]

Rémi Aubel commented

Thank you very much!