Spring JDBC Prepared Statement & Char Datatype

I spent couple of hours figuring out this, hence thought to have a quick post.

I was trying to pass a string value as a parameter of prepared statement. I was using Spring JDBCTemplate for the same. This code was supposed to query a legacy database. When I was using the value in SQL directly it was working. But when I was using the prepared statement it was not. E.g.

// Following was working

String id = getJdbcTemplate().queryForObject("SELECT ID FROM SOME_TABLE WHERE TABLE_FIELD = 'abc'", String.class);

// Following was not working

String value = "abc";

String id = getJdbcTemplate().queryForObject("SELECT ID FROM SOME_TABLE WHERE TABLE_FIELD = ?", String.class, value);

The reason was that the TABLE_FIELD was of type CHAR(50) and hence the issue.

Since I did not have any control on changing the datatype itself. In order to fix the issue I changed the SQL to trim the char field as shown below:

String value = "abc";

String id = getJdbcTemplate().queryForObject("SELECT ID FROM SOME_TABLE WHERE RTRIM(TABLE_FIELD) = ?", String.class, value);

So, that’s one of the reasons why we should prefer using VARCHAR2 or equivalent datatype. Lesson learnt 🙂

Happy Learning,

Kamlesh

Advertisements

JSP: Problem Using Spring Message with JSTL

Couple of days back I was trying to externalize the labels and messages in a JSP file to a property file. As I was using Spring MVC, decided to use <spring:message> tag to achieve this. Everything else worked fine except when I used <spring:message> tag as a value attribute of <c:set>. Albeit it sounds very simple, I had to spend some time finding out the way. And hence considering this a candidate for my blog.

Problem:

Unable to use <spring:message> tag with the JSTL  <c:set> tag like below:

<c:set var=”someVar” value=”<spring:message code=’some.value’/>” />

Solution:
Nest the <spring:message> tag in the JSTL tag.

<c:set var=”someVar”>
<spring:message code=”some.value”/>
</c:set>

Enjoy Learning,
Kamlesh