Home  >  Spring

Spring JdbcTemplate.queryForList()

By Arvind Rai, July 31, 2022
On this page we will learn using Spring JdbcTemplate.queryForList() method. The queryForList() method executes the given SQL and returns result as List of objects.
The queryForList() method accepts arguments in following ways.
1.
List<Map<String,Object>> queryForList(String sql) throws DataAccessException 
Executes the given static query and returns a list. It uses a JDBC Statement, not a PreparedStatement. The result list contains maps. One map instance represents one row where map key is column name and map value is column value.
2.
<T> List<T> queryForList(String sql, Class<T> elementType) throws DataAccessException 
Executes the given static query and returns a list. It uses a JDBC Statement, not a PreparedStatement. The result list contains the element of given element Type.
3.
<T> List<T> queryForList(String sql, Class<T> elementType, @Nullable Object... args) throws DataAccessException 
Queries given SQL to create a prepared statement from SQL and a list of arguments to bind to the query, expecting a result list. The result list contains the element of given element Type.
4.
List<Map<String,Object>> queryForList(String sql, @Nullable Object... args) throws DataAccessException 
Query given SQL to create a prepared statement from SQL and a list of arguments to bind to the query, expecting a result list. The result list contains maps. One map instance represents one row where map key is column name and map value is column value.
5.
List<Map<String,Object>> queryForList(String sql, Object[] args, int[] argTypes) throws DataAccessException 
Queries given SQL to create a prepared statement from SQL and a list of arguments to bind to the query, expecting a result list. The result list contains maps. One map instance represents one row where map key is column name and map value is column value.
6.
<T> List<T> queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType) throws DataAccessException 
Queries given SQL to create a prepared statement from SQL and a list of arguments to bind to the query, expecting a result list. The result list contains the element of given element Type.

Parameters:
The sql is the SQL query to execute.
The args is the arguments to bind to the query.
The argTypes is the SQL types of the arguments i.e. java.sql.Types.
The elementType is the required type of element in the result list.

Returns:
It returns a List of objects.

Throws:
If the query fails, it throws DataAccessException.

Example using Spring Boot

PersonDAO.java
package com.concretepage;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class PersonDAO {
  @Autowired
  private JdbcTemplate jdbcTemplate;

  // queryForList(String sql)
  public List<Person> getAllPersons() {
	String sql = "select * from person";
	List<Map<String,Object>> list = jdbcTemplate.queryForList(sql);
	List<Person> plist = new ArrayList<>(); 
	list.forEach(m -> {
		Person p = new Person((String)m.get("name"), (Integer)m.get("age"));
		p.setId((Integer)m.get("id"));	
		plist.add(p);
	}); 
	return plist;
  }

  // queryForList(String sql, Class<T> elementType)
  public List<String> getAllNames() {
	String sql = "select name from person";
	List<String> list = jdbcTemplate.queryForList(sql, String.class);
	List<String> plist = new ArrayList<>(); 
	list.forEach(name -> plist.add(name)); 
	return plist;
  }
  
  // queryForList(String sql, Class<T> elementType, Object... args)
  public  List<Integer> getIdsByNameNAge1(String name, int age) {
	String sql = "select id from person where name = ? and age = ?";
	List<Integer> list = jdbcTemplate.queryForList(sql, Integer.class, name, age);
	List<Integer> plist = new ArrayList<>(); 
	list.forEach(id -> plist.add(id)); 
	return plist;
  }
 
  // queryForList(String sql, Object... args)
  public  List<Person> getPersonsByNameNAge1(String name, int age) {
	String sql = "select * from person where name = ? and age = ?";
	List<Map<String,Object>> list = jdbcTemplate.queryForList(sql, name, age);
	List<Person> plist = new ArrayList<>(); 
	list.forEach(m -> {
		Person p = new Person((String)m.get("name"), (Integer)m.get("age"));
		p.setId((Integer)m.get("id"));	
		plist.add(p);
	}); 
	return plist;
  }  
  
  // queryForList(String sql, Object[] args, int[] argTypes)
  public  List<Person> getPersonsByNameNAge2(String name, int age) {
	String sql = "select * from person where name = ? and age = ?";
	Object[] args = { name, age };
	int[] argTypes = { java.sql.Types.CHAR, java.sql.Types.INTEGER };
	List<Map<String,Object>> list = jdbcTemplate.queryForList(sql, args, argTypes);
	List<Person> plist = new ArrayList<>(); 
	list.forEach(m -> {
		Person p = new Person((String)m.get("name"), (Integer)m.get("age"));
		p.setId((Integer)m.get("id"));	
		plist.add(p);
	}); 
	return plist;
  }
  
  // queryForList(String sql, Object[] args, int[] argTypes, Class<T> elementType)
  public  List<Integer> getIdsByNameNAge2(String name, int age) {
	String sql = "select id from person where name = ? and age = ?";
	Object[] args = { name, age };
	int[] argTypes = { java.sql.Types.CHAR, java.sql.Types.INTEGER };	
	List<Integer> list = jdbcTemplate.queryForList(sql, args, argTypes, Integer.class);
	List<Integer> plist = new ArrayList<>(); 
	list.forEach(id -> plist.add(id)); 
	return plist;
  }  
} 
Person.java
package com.concretepage;
public class Person {
    private int id;
	private String name;
	private int age;
	public Person(String name, int age) {
	  this.name = name;
	  this.age = age;
	}
	public int getId() {
	  return id;
	}
	public void setId(int id) {
	  this.id = id;
	}
	public String getName() {
	  return name;
	}
	public int getAge() {
	  return age;
	}
	@Override
	public String toString() {
	  return id + " - " + name + " - " + age;
	}
} 
application.properties
spring.datasource.url=jdbc:mysql://localhost:3306/concretepage
spring.datasource.username=root
spring.datasource.password=cp
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver 
pom.xml
<dependencies>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter</artifactId>
	</dependency>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-jdbc</artifactId>
	</dependency>
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>6.0.5</version>
	</dependency>
</dependencies> 
MyApplication.java
package com.concretepage;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;

@SpringBootApplication
public class MyApplication {
  public static void main(String[] args) {
    ApplicationContext ctx = SpringApplication.run(MyApplication.class, args);
    PersonDAO personDAO = ctx.getBean(PersonDAO.class);
	
    System.out.println(personDAO.getAllPersons()); // [1 - Rakesh - 30, 2 - Mohan - 25, 3 - Mohan - 25]
    System.out.println(personDAO.getAllNames()); // [Rakesh, Mohan, Mohan]
    System.out.println(personDAO.getIdsByNameNAge1("Mohan", 25)); // [2, 3]
    System.out.println(personDAO.getPersonsByNameNAge1("Mohan", 25)); // [2 - Mohan - 25, 3 - Mohan - 25]
    System.out.println(personDAO.getPersonsByNameNAge2("Mohan", 25)); // [2 - Mohan - 25, 3 - Mohan - 25]	
    System.out.println(personDAO.getIdsByNameNAge2("Mohan", 25)); // [2, 3]
  }
} 
Find the MySQL table (person) screenshot used in our demo.
Spring JdbcTemplate.queryForList()

Reference

Class JdbcTemplate

Download Source Code

POSTED BY
ARVIND RAI
ARVIND RAI











©2022 concretepage.com | Privacy Policy | Contact Us