Support for Types.NCLOB in SqlLobValue to simplify use with NamedParameterJdbcTemplate. [SPR-11938]

[spring-projects-issues]

Oleksandr Gavenko opened SPR-11938 and commented

The only way to avoid LobHandler in DAO code for insert/update:

MapSqlParameterSource paramSource = new MapSqlParameterSource();
paramSource.addValue("clob", "a long long text", Types.CLOB);
namedTemplate.update(INSERT_STMT, paramSource);

See:

• http://stackoverflow.com/questions/5791662/inserting-clob-with-namedparameterjdbctemplate
• http://codeblow.com/questions/placing-clob-with-namedparameterjdbctemplate/

another way (only for JdbcTemplate, not for NamedParameterJdbcTemplate):

• http://docs.spring.io/spring/docs/3.0.x/reference/jdbc.html#jdbc-lob
• http://www.java2s.com/Code/Java/Spring/InsertClobData.htm

java.sql.Types has NCLOB constans for Java 6/7/8:

• http://docs.oracle.com/javase/6/docs/api/java/sql/Types.html#NCLOB
• http://docs.oracle.com/javase/7/docs/api/java/sql/Types.html#NCLOB

If I do:

paramSource.addValue("clob", "a long long text", Types.CLOB);

all work fine, but with:

paramSource.addValue("clob", "a long long text", Types.NCLOB);

I get:

=> Fail to add incidentjava.lang.ClassCastException: java.lang.String cannot be cast to oracle.sql.CLOB
   at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:8762) ~[ojdbc6.jar:11.2.0.3.0]
   at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8286) ~[ojdbc6.jar:11.2.0.3.0]
   at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:8868) ~[ojdbc6.jar:11.2.0.3.0]
   at oracle.jdbc.driver.OraclePreparedStatementWrapper.setObject(OraclePreparedStatementWrapper.java:240) ~[ojdbc6.jar:11.2.0.3.0]
   at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:406) ~[spring-jdbc-3.2.8.RELEASE.jar:3.2.8.RELEASE]
   at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:234) ~[spring-jdbc-3.2.8.RELEASE.jar:3.2.8.RELEASE]
   at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:149) ~[spring-jdbc-3.2.8.RELEASE.jar:3.2.8.RELEASE]
   at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:298) ~[spring-jdbc-3.2.8.RELEASE.jar:3.2.8.RELEASE]
   at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:251) ~[spring-jdbc-3.2.8.RELEASE.jar:3.2.8.RELEASE]
   at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:583) ~[spring-jdbc-3.2.8.RELEASE.jar:3.2.8.RELEASE]
   at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:818) ~[spring-jdbc-3.2.8.RELEASE.jar:3.2.8.RELEASE]
   at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:840) ~[spring-jdbc-3.2.8.RELEASE.jar:3.2.8.RELEASE]
   at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:281) ~[spring-jdbc-3.2.8.RELEASE.jar:3.2.8.RELEASE]

Actual type of table column is NCLOB, and with Type.CLOB code work fine.

My suggestion just add NCLOB to src/main/java/org/springframework/jdbc/core/StatementCreatorUtils.java with same role as CLOB!

---

Affects: 3.2.9, 4.0.5

Issue Links:

• When using NamedParameterJdbcTemplate, NVARCHAR or NCLOB(4000 characters or less) columns are not properly populated since StatementCreatorUtils does setString for these types instead of setNString. [SPR-16154] #20702 When using NamedParameterJdbcTemplate, NVARCHAR or NCLOB(4000 characters or less) columns are not properly populated since StatementCreatorUtils does setString for these types instead of setNString.
• 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
• INSERT SELECT of large CLOB causes ORA-01461 on setString [SPR-12240] #16854 INSERT SELECT of large CLOB causes ORA-01461 on setString

[spring-projects-issues]

Juergen Hoeller commented

Good point! StatementCreatorUtils populates NVARCHAR, LONGNVARCHAR, NCLOB via setString as well now.

Juergen

[spring-projects-issues]

Oleksandr Gavenko commented

Hi! I apply your changes on top of 3.x series:

git diff  ec4e6e0^! >.diff
git reset --hard origin/3.2.x 
patch -p1 <.diff
gradle

and test my application with Type.NCLOB. All fine, thanks!