how to pass multiple values to query using jdbcTemplate in spring

Question

In my Spring Hibernate application i have all the sql queries in one common_queries.xml file,where some queries require 2 to 3 parameters shown as below

   <query id="mining.fuel" no-of-params="2">
select ms.id id,ms.name value,concat(ms.name,' ','  (',ms.code,')') label,ms.rate rate     from mining_fuel ms where ms.name like '?' and ms.fuel_type_id=?  LIMIT 10
 </query>

In my daoImpl i get this query

lookupList = jdbcTemplate.queryForList(q1.getQuery());

I will get the query here,but how to pass the value of '?'s here, i have those 2 values with me in daoImpl.. pl send the code of how to achieve this.I dont want to use prepared statement.

Costi Ciudatu · Accepted Answer · 2011-08-10 11:38:43Z

5

Use this overload which takes an Object vararg for passing the query parameters:

lookupList = jdbcTemplate.queryForList(q1.getQuery(), value1, value2, value3);

answered Aug 10, 2011 at 11:38

Costi Ciudatu

38.5k7 gold badges58 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Anupama Over a year ago

well, i tried that i have 2 values like (q1.getQuery(),lookupValue,filterType);but when i hardcode lookupValue and pass only one parameter filterType its working fine, but when i do it reverse its throwing java.sql.SQLException: No parameters defined during prepareCall() and when i pass both values its throwing java.sql.SQLException: Parameter index out of bounds. 2 is not between valid values of 1 and 1 .Is there any problem with the select query near 'like' i have mentioned above?

Costi Ciudatu Over a year ago

Try to remove the quotes around the first ?: like ?.

Anupama Over a year ago

actually i want to use ( like ' ?% ' ) but if i remove single quotes ( like ?% ) its showing sql grammer exception..If i use with quotes its showin parameter out of bounds exception.

Costi Ciudatu Over a year ago

I guess you need to use it without quotes and have the '%' appended to the value: ... like ? ..., and then your value should be someString + '%'

German Attanasio · Accepted Answer · 2013-06-01 06:04:01Z

4

I think that you only need to create an Object array with the params used by the query, take in mind that the order is important because value1 will be the first replacement for ? in the query.

lookupList = jdbcTemplate.queryForList(q1.getQuery(), new Object[]{value1, value2, value3});

answered Jun 1, 2013 at 6:04

German Attanasio

23.9k7 gold badges51 silver badges64 bronze badges

Comments

naresh · Accepted Answer · 2013-06-01 04:43:46Z

0

First calls the queryForList method on jdbctemplate reference and pass query and object type array in this object type array we must pass only objects means if we have id it is int type we have to convert to object type while putting inside object array.

lookupList = jdbcTemplate.queryForList(q1.getQuery, new Object[]{designation, new Integer(id), new Float(sal)}

answered Jun 1, 2013 at 4:43

naresh

1

Collectives™ on Stack Overflow

how to pass multiple values to query using jdbcTemplate in spring

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related