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]

[spring-projects-issues]

Jeff Maxwell opened SPR-16154 and commented

When using NamedParameterJdbcTemplate, NVARCHAR or NCLOB(4000 characters or less) columns are not properly populated since StatementCreatorUtils uses setString for these types instead of setNString.

Before #16555 NVARCHAR columns would populate correctly as the logic falls back to setObject since NVARCHAR and LONGNVARCHAR are not explicitly handled.

After #16555 setString is called for NVARCHAR (and LONGNVARCHAR) causing the data to be stored with the NLS_CHARACTERSET and not the NLS_NCHAR_CHARACTERSET.

This commit handles NCLOBs properly but only if they are greater than 4000 characters.

Note that NCHAR works fine as it still handled by setObject.

The updated setValue method is below.

Environment

• Java 1.8.0_121
• Spring 4.3.2 to 4.3.12
• Windows 7 Professional SR1
• Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
• Oracle Character Set Config:
  • NLS_CHARACTERSET: WE8MSWIN1252
  • NLS_NCHAR_CHARACTERSET: AL16UTF16
• Oracle Drivers:
  • ojdbc7 12.1.0.2.0
  • ojdbc7_g 12.1.0.2.0
  • ojdbc8 12.2.0.1
  • ojdbc8_g 12.2.0.1

	private static void setValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName,
			Integer scale, Object inValue) throws SQLException {

		if (inValue instanceof SqlTypeValue) {
			((SqlTypeValue) inValue).setTypeValue(ps, paramIndex, sqlType, typeName);
		}
		else if (inValue instanceof SqlValue) {
			((SqlValue) inValue).setValue(ps, paramIndex);
		}
		else if (sqlType == Types.VARCHAR || sqlType == Types.LONGVARCHAR ) {
			ps.setString(paramIndex, inValue.toString());
		}
		else if (sqlType == Types.NVARCHAR || sqlType == Types.LONGNVARCHAR) {
			ps.setNString(paramIndex, inValue.toString());
		}
		else if ((sqlType == Types.CLOB || sqlType == Types.NCLOB) && isStringValue(inValue.getClass())) {
			String strVal = inValue.toString();
			if (strVal.length() > 4000) {
				// Necessary for older Oracle drivers, in particular when running against an Oracle 10 database.
				// Should also work fine against other drivers/databases since it uses standard JDBC 4.0 API.
				try {
					if (sqlType == Types.NCLOB) {
						ps.setNClob(paramIndex, new StringReader(strVal), strVal.length());
					}
					else {
						ps.setClob(paramIndex, new StringReader(strVal), strVal.length());
					}
					return;
				}
				catch (AbstractMethodError err) {
					logger.debug("JDBC driver does not implement JDBC 4.0 'setClob(int, Reader, long)' method", err);
				}
				catch (SQLFeatureNotSupportedException ex) {
					logger.debug("JDBC driver does not support JDBC 4.0 'setClob(int, Reader, long)' method", ex);
				}
			}
			else {
				// Fallback: setString or setNString binding
				if (sqlType == Types.NCLOB) {
					ps.setNString(paramIndex, strVal);
				}
				else {
					ps.setString(paramIndex, strVal);
				}
			}
		}
		else if (sqlType == Types.DECIMAL || sqlType == Types.NUMERIC) {
			if (inValue instanceof BigDecimal) {
				ps.setBigDecimal(paramIndex, (BigDecimal) inValue);
			}
			else if (scale != null) {
				ps.setObject(paramIndex, inValue, sqlType, scale);
			}
			else {
				ps.setObject(paramIndex, inValue, sqlType);
			}
		}
		else if (sqlType == Types.BOOLEAN) {
			if (inValue instanceof Boolean) {
				ps.setBoolean(paramIndex, (Boolean) inValue);
			}
			else {
				ps.setObject(paramIndex, inValue, Types.BOOLEAN);
			}
		}
		else if (sqlType == Types.DATE) {
			if (inValue instanceof java.util.Date) {
				if (inValue instanceof java.sql.Date) {
					ps.setDate(paramIndex, (java.sql.Date) inValue);
				}
				else {
					ps.setDate(paramIndex, new java.sql.Date(((java.util.Date) inValue).getTime()));
				}
			}
			else if (inValue instanceof Calendar) {
				Calendar cal = (Calendar) inValue;
				ps.setDate(paramIndex, new java.sql.Date(cal.getTime().getTime()), cal);
			}
			else {
				ps.setObject(paramIndex, inValue, Types.DATE);
			}
		}
		else if (sqlType == Types.TIME) {
			if (inValue instanceof java.util.Date) {
				if (inValue instanceof java.sql.Time) {
					ps.setTime(paramIndex, (java.sql.Time) inValue);
				}
				else {
					ps.setTime(paramIndex, new java.sql.Time(((java.util.Date) inValue).getTime()));
				}
			}
			else if (inValue instanceof Calendar) {
				Calendar cal = (Calendar) inValue;
				ps.setTime(paramIndex, new java.sql.Time(cal.getTime().getTime()), cal);
			}
			else {
				ps.setObject(paramIndex, inValue, Types.TIME);
			}
		}
		else if (sqlType == Types.TIMESTAMP) {
			if (inValue instanceof java.util.Date) {
				if (inValue instanceof java.sql.Timestamp) {
					ps.setTimestamp(paramIndex, (java.sql.Timestamp) inValue);
				}
				else {
					ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
				}
			}
			else if (inValue instanceof Calendar) {
				Calendar cal = (Calendar) inValue;
				ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);
			}
			else {
				ps.setObject(paramIndex, inValue, Types.TIMESTAMP);
			}
		}
		else if (sqlType == SqlTypeValue.TYPE_UNKNOWN || (sqlType == Types.OTHER &&
				"Oracle".equals(ps.getConnection().getMetaData().getDatabaseProductName()))) {
			if (isStringValue(inValue.getClass())) {
				ps.setString(paramIndex, inValue.toString());
			}
			else if (isDateValue(inValue.getClass())) {
				ps.setTimestamp(paramIndex, new java.sql.Timestamp(((java.util.Date) inValue).getTime()));
			}
			else if (inValue instanceof Calendar) {
				Calendar cal = (Calendar) inValue;
				ps.setTimestamp(paramIndex, new java.sql.Timestamp(cal.getTime().getTime()), cal);
			}
			else {
				// Fall back to generic setObject call without SQL type specified.
				ps.setObject(paramIndex, inValue);
			}
		}
		else {
			// Fall back to generic setObject call with SQL type specified.
			ps.setObject(paramIndex, inValue, sqlType);
		}
	}

---

Affects: 4.3.12, 5.0.1

Issue Links:

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

Referenced from: pull request #1586, and commits d5f34ed, e4c5b77, a37fce8

Backported to: 4.3.13

[spring-projects-issues]

Jeff Maxwell commented

Should fallback logic be added?

[spring-projects-issues]

Juergen Hoeller commented

Your implementation looks fine to me. If we get explicit NVARCHAR or NCLOB type indications there, we can assume setNString to be supported as well. If some JDBC driver happens to deviate from this, we'll address any issues against specific follow-up reports.

BTW, your 4.3.x pull request seems to consist of two commits, and your master pull request seems to have cherry-picked those 4.3.x commits. This is rather unusual; we tend to fix issues in master through an external PR with a single commit which we then cherry-pick to 4.3.x ourselves. Either way, we'll certainly roll that change into both branches for inclusion in 5.0.2 as well as 4.3.13 (scheduled for mid November).

[spring-projects-issues]

Jeff Maxwell commented

I will redo the PR just to master if that is OK.

[spring-projects-issues]

Juergen Hoeller commented

Sure, that would be ideal. I'll care for the backport then.

[spring-projects-issues]

Jeff Maxwell commented

OK updated #1586

[spring-projects-issues]

Juergen Hoeller commented

Thanks for your efforts - merged into master now!