Spring JdbcTemplate's queryForList() with many args is not readable; SQLQuery don't give me list with column names

Question

Note: This may be a simple question but I can't find a short way to make it clear. So, sorry for this long question.

In a project I am responsible, I use Spring 2.5, Hibernate 3.3.2 as middleware and Oracle database. As database is related to many other projects, some queries as really very complicated and I can't get a solution with Hibernate's solutions (HQL, Criteria, etc...). So I feel more comfortable with JdbcTemplate's queryForX() methods, as an example;

String sql = "select * from myTable";
jdbc.queryForList(sql);

Sure there are mostly "where" conditions and params indeed:

jdbc.querForList(sql, new Object[]{obj1,obj2,obj3 /* and many more arguments... */})

In this case, I must write question marks "?" for my parameters, so my SQL query string turns out some messy and hard to read; something like this:

select t1.col1, t2.col2, t1.col, --...some cols , 
    sum(nvl(some_col1,?)-nvl(other_col2,?)) over (partition by col1,col2,col3,col4) sum_of_cols 
from weird_table t1, another_table t2
where t1.col20=? and sum_of_cols>? and t1.col3=t2.col3 --and many ?'s...
and not exists (
    select ? from boring_table t3 where -- many ?'s
)
--group by  and order by order by etc

So now, which question mark is for which parameter? It is obvious but hard to read. But there are some other solutions for binded params like:

select * from a_table t where t.col1= :col1 and t.col2= :col2 -- and many more ":param"s

For this type query, we can write if it were Hibernate:

Query q = hibernateTemplate.createQuery();
q.setString("col1","a value");
q.setInteger("col2", 3);

I think it is more readable and easy to understand which value is what. I know I can do this with SQLQuery;

SQLQuery sq = hibernateTemplate.createSQLQuery();
/* same as above setInteger() etc. */

But this sq.list() gives me a list without a column name. so I have a basic array which is difficult to use:

[[1,2,"a"],[1,2,"b"], ...]

But with queryForList() I get better one:

[{COL1=1,COL2=2,COL3="a"},{COL1=1,COL2=2,COL3="b"},...]

So if I use queryForList(), I must write a very messy params Object; or I use SQLQuery and then I have to get my list without a map as column names.

Is there a simple solution with mapped list using more readable param setting (like query.setX()) ?

Solution

Well you can use NamedParameterJdbcTemplate to do just that

Heres a sample

    String query = "INSERT INTO FORUMS (FORUM_ID, FORUM_NAME, FORUM_DESC)
 VALUES (:forumId,:forumName,:forumDesc)";
    Map namedParameters = new HashMap();
    namedParameters.put("forumId", Integer.valueOf(forum.getForumId()));
    namedParameters.put("forumName", forum.getForumName());
    namedParameters.put("forumDesc", forum.getForumDesc());
    namedParameterJdbcTemplate.update(query, namedParameters);

You check the complete example with the source code in the below link

Spring NamedParameterJdbcTemplate Tutorial

Comments

[Yasin Okumuş]

Thank you very much, I'll give it a try!

[ninnemannk]

NOTE: It's cleaner to use Spring's MapSqlParameterSource for setting your parameters. It gives support for datatypes by allowing namedParameters.put("forumName", forum.getForumName(), String.class);

[Yasin Okumuş]

Thank you @ninn, I checked it too and I want to make a little correction for your code. I am editing actual answer with your addition.