Suppose you have a where condition with like clause:

AND ( UPPER(C.ndg) LIKE UPPER('%test%') OR UPPER(C.ndg_name) LIKE UPPER('%test%') )

on Java you should write something like that:

AND ( UPPER(C.ndg) LIKE UPPER('%?%') OR UPPER(C.ndg_name) LIKE UPPER('%?%') )

but, when you fill the paramter with SimpleJdbcTemplate(), this exception will be raised:

PreparedStatementCallback; SQL []; The column index is out of range: 1, number of columns: 0.; nested exception is org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

This happens because with prepared statement you need to remove the ‘ but, now, how can I write the condition with ‘%’ char?

Simple, you need to sorround the paramter with ‘%’ like that:

sqlString += " and ( UPPER(C.ndg) like UPPER(?) or UPPER(C.ndg_name) like UPPER(?) ) ";
 
getJdbcTemplate().query(sqlString, new Object[] {"%" + searchForm.getNdg() + "%", "%" + searchForm.getNdg() + "%"}, new ViewCGRowMapper());
Use of LIKE clause in sql prepared statement
Tagged on:         

Leave a Reply