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

One thought on “Spring JDBC Prepared Statement & Char Datatype

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s