Spring JdbcTemplate.update() is not updating the rows

Question

I am using Spring JdbcDaoSupport in my DAO and trying to update the records using the following query.

String callersUpdateQuery = "update W67U999S a set pcrdattim= ? where exists (select b.CRDATTIM, b.RECORDCD, b.CRNODE, b.UNITCD, b.WRKTYPE from W03U999S b where a.PCRDATTIM = ? and a.CCRDATTIM = b.CRDATTIM and a.CRECORDCD = b.RECORDCD and a.CCRNODE = b.CRNODE and a.PRECORDCD = 'F' and a.PCRNODE = '01' and b.WRKTYPE = 'CALLER' and b.UNITCD=? and a.crecordcd='T')";

The following is the code that should update the table:

 int updatedRowsCount =getJdbcTemplate().update(callersUpdateQuery, new Object[]{newFolderCrdattim, crdattim, businessAreaName});

But getJdbcTemplate().update() is not updating the required rows and returning the updated rows count as zero. Weirdly, the same sql query when I execute at the database end, the records are getting updated. Can anyone guess what's wrong with the code or query?

The log messages are also showing correct values, but somehow the query is not updating the database:

21:04:01,288 DEBUG [org.springframework.jdbc.core.JdbcTemplate.execute] Executing prepared SQL statement [update W67U999S a set pcrdattim= ? where exists (select b.CRDATTIM, b.RECORDCD, b.CRNODE, b.UNITCD, b.WRKTYPE from W03U999S b where a.PCRDATTIM = ? and a.CCRDATTIM = b.CRDATTIM and a.CRECORDCD = b.RECORDCD and a.CCRNODE = b.CRNODE and a.PRECORDCD = 'F' and a.PCRNODE = '01' and b.WRKTYPE = 'CALLER' and b.UNITCD=? and a.crecordcd='T')]
21:04:01,288 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection] Fetching JDBC Connection from DataSource
21:04:01,288 DEBUG [org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver] Creating new JDBC DriverManager Connection to [jdbc:oracle:thin:@10.193.244.225:1521:AWD]
21:04:03,865 TRACE [org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal] Setting SQL statement parameter value: column index 1, parameter value [2017-08-09-10.33.10.168480], value class [java.lang.String], SQL type unknown
21:04:03,865 TRACE [org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal] Setting SQL statement parameter value: column index 2, parameter value [2017-07-20-04.22.20.893340], value class [java.lang.String], SQL type unknown
21:04:03,865 TRACE [org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal] Setting SQL statement parameter value: column index 3, parameter value [CS2XAA], value class [java.lang.String], SQL type unknown
21:04:04,115 DEBUG [org.springframework.jdbc.core.JdbcTemplate.doInPreparedStatement] SQL update affected 0 rows
21:04:04,131 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection] Returning JDBC Connection to DataSource

Answer 1

Oracle CHAR type is the culprit here. The columns that I want to update are of type CHAR. That's causing the issue. This link helped me in figuring out the solution: Oracle JDBC and Oracle CHAR data type