RowMapper in JdbcTemplate returning only 1 as the result set

[kkrikor]

Hello All ,

I'm implementing the RowMapper to get the result set but it seems that every time i run the query i get a result set of only 1 where i know there are more than one record in the db. Here's my code.

public String method(final Object arg1)
{
// Get the arg1 object and then extract second property.

Generic init = (Generic)arg1;
String name = (String)init.getPriorTwo();
logger.debug("input " + name);

//String name = arg1;

// The Sql statement which will look for programs in the ACS
// cr_items table and extract the id

String sql = "SELECT cr_items.item_id, cr_items.name from cr_items " +
" where cr_items.parent_id=982 and cr_items.name like ? ";

// Assign the parameter and then call the query
final Object[] params = new Object[] { name };
final Generic g = new Generic();
List list = jdbcTemplate.query(sql,params,new RowMapperResultReader(new GenericRowMapper()));
return "yes";
}

and the row mapper implementation

class GenericRowMapper implements RowMapper{
public Object mapRow(ResultSet rs, int index) throws SQLException {
Generic g = new Generic(String.valueOf(rs.getInt("item_id")),rs.ge tString("name"),"");
return g;
}
}


This code runs with no error but returns no more than one record as a result. Any ideas

thank you

[kkrikor]

Well Since nobody responded i'll respond

I resolved the problem by doing this

String sql = "SELECT cr_items.item_id, cr_items.name from cr_items " +
" where cr_items.parent_id=982 and cr_items.name like '%" + name+ "'% ";

and getting rid of the params in the jdbcTemplate.query call.

Another question i have though , every time i call jdbcTemplate.query a new connection to db happens and at the end of the call the connection is closed. Is there a way using jdbcTemplate i could keep the connection open and then close it deliberatly. I have more than 40 calls to this function that can happen incrementaly so if i could keep it open until it's all done then close it that will be a performance boost ?

thank you

[trisberg]

You can either wrap everything in a transaction or use SingleConnectionDataSource. For the latter you need to call destroy() when you are done in order to close the connection.