How to use a list of string in NamedParameterJDBCTemplate to get results

Question

Experimenting with Spring-JDBC. I am using this as reference. I am trying to get a list of actors who have the same last name. Running this code gave me the desired results:

public List<String> getActorsWithSameLastName(String lastName,             NamedParameterJdbcTemplate template) {         String query = "SELECT FIRSTNAME FROM ACTORS WHERE LASTNAME=:LASTNAME";         Map<String, String> paramMap = new HashMap<String, String>();         paramMap.put("LASTNAME", lastName);         return template.queryForList(query, paramMap, String.class);     } 

I have a List<String> of last names. How can I get a List of actors with the list that I have? Do I iterate over the list of last names and call the getActorsWithSameLastName() everytime or does spring provide a way where it does the iteration and fetches the result for me? Please advice.

Answer 1

Use IN Clause..

How to use SELECT IN clause in JDBCTemplates?

List<String> lastnames= new ArrayList<>();  Map namedParameters = Collections.singletonMap("lastnamevalues", lastnames);  StringBuffer recordQueryString = new StringBuffer();  recordQueryString.append("select FIRSTNAME, LASTNAME from ACTORS where lastname in (:lastnamevalues)");  List nameInvolvements = this.namedparameterJdbcTemplate.query(recordQueryString.toString(), namedParameters, new MyMapper()); 

Answer 2

You can also use MapSqlParameterSource

String query = "SELECT FIRSTNAME FROM ACTORS WHERE LASTNAME in (:LASTNAME)"; Set<String> ids = ....;  MapSqlParameterSource parameters = new MapSqlParameterSource(); parameters.addValue("LASTNAME", ids);  this.namedparameterJdbcTemplate.query(query, parameters);