CODEWITHSUNDEEP
javasqloracle-databasespring-jdbc
Kirill Reznikov
Kirill Reznikov

Reputation: 2357

Spring JdbcTemplate with parameter returns empty List

I can't understand what wrong with it, i spent many hours trying to get my String object from method queryForList(..) and all for nothing. So here this stupid sql query:

String GET_EMAIL_BY_LDAP = "select ld.LDAP_EMAIL1 from IPS.ldap ld where ld.ldap_login = ?"

I have tried this also without '?' parameter and it is works perfectly.

Here is the way, how I use the method:

List<String> email = jdbcTemplate.queryForList(GET_EMAIL_BY_LDAP, String.class, userId.toUpperCase());

And this version of method also returns empty List:

List<String> email = jdbcTemplate.queryForList(GET_EMAIL_BY_LDAP, new Object[]{userId.toUpperCase()}, String.class);

P.S. getEmailById("DN270391RKA") - this is my user id parameter that passed. It hasn't spaces.

Type of ld.LDAP_EMAIL1 field in table is : NVARCHAR2(381 CHAR)

Upvotes: 0

Views: 3934

Answers (2)

Kirill Reznikov
Kirill Reznikov

Reputation: 2357

I have understood what it was!

ld.ldap_login values in my ( legacy :-D ) table have a tailing spaces. After minor editing of my sql statement it works. I just added TRIM() on this column.

"select ld.LDAP_EMAIL1 from IPS.ldap ld where TRIM(ld.ldap_login) = ?"

So, it seems if it is usual sql statement then when I execute query then oracle database does TRIM() automatically and in case of PreparedStatement doesn't.

Upvotes: 1

gantners
gantners

Reputation: 471

List<String> email = jdbcTemplate.queryForList(GET_EMAIL_BY_LDAP, String.class, userId.toUpperCase());

This line is fully correct so it seems to be either a bad userID or your sql query is not correct.

Best way is to paste your sql query with an existing user id in your query browser and check if you get more than an empty result.

then use the jdbc template again and pass hardcoded previously working user id and check if you get the same results.

or: delete the userid clause and select all not specific to the user id.

Also make sure the ld.LDAP_EMAIL is String representation e.g varchar/text

Upvotes: 0

Related Questions