CODEWITHSUNDEEP
javasqlspringspring-jdbc
Prayas Bhatnagar
Prayas Bhatnagar

Reputation: 47

Batch Select operation using NamedParameterJdbcTemplate

I need help with a batch operation for select queries in NamedParameterJdbcTemplate. I have a List of employees having id and name, against which I need the unique list of employee's project codes and ultimately it will add to the final list of all projects from all employees.

My source code:

//input = List<Employee> employee

String sql = "Select project_name from employee where id = :id and name = :name";

RowMapper<String> rowMapper = (rs, rowNum) ->
{
String s = rs.getString("project_name");
return s;
};


MapSqlParameterSource params;
List<String> finalProjectList = new ArrayList<String>();
List<String> employeeProjectList = null;
for(Employee e : employee)
{
params = new MapSqlParameterSource();
employeeProjectList = new ArrayList<String>();

params.addvalue("id",e.getId());
params.addvalue("name",e.getName());

employeeProjectList = namedParameterJdbcTemplate.query(sql, params, rowMapper);
finalProjectList.addAll(employeeProjectList);
}

Note: Please answer for select queries without IN clause and not for update or insert operations. I am aware that for updates and inserts there is a batchUpdate method provided by NamedParameterJdbcTemplate.

Upvotes: 0

Views: 1395

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81862

Unfortunately you don't really tell us what your problem is. But there are a couple of things that seem problematic.

  1. finalProjectList.addAll(finalProjectList) is just a fancy NOOP, since finalProjectList is empty and will stay that way. Should this be finalProjectList.addAll(employeeProjectList)?

  2. You are running the same statement multiple times in a tight loop. It's probably more efficient to turn that in a single statement or at least way less then you have currently. This can be done by combining multiple copies of the select statement using UNION or if you know they are distinct UNION ALL. Of course you'll have to change the parameter names, so they are unique.

  3. Alternatively you could use an IN-clause. I know you explicitly asked for solutions without IN, but you have that above and you didn't give a reason, so I'm going to ignore that constraint for this section. The challenge is that you have two arguments to filter on. That in itself seems strange, since one of the parameters is called id which suggests it should be unique. The following solution assumes that there is a character that you can guarantee that it doesn't appear in id. I use _ and an example. Then the following statement should do the trick:

    SELECT project_name 
FROM employee 
WHERE id IN :ids 
AND CONCAT(id, name) IN :idnames

    The query parameter ids and idnames would then need to be set to a collection of id values a the concatenation id + "_" + name. The first IN will limit the number of rows to be processed by the second IN and hopefully use an index, while the second can't use an index but does the correct filtering.

  4. Since you are only selecting a single column you may use queryForList or queryForObject and drop the RowMapper.

Upvotes: 1

Related Questions