Spring JdbcTemplate.queryForMap()

By Arvind Rai, July 28, 2022
On the page we will learn using JdbcTemplate.queryForMap method. It executes given SQL, binding the query with the given arguments. The query is expected to be a single row query. This method returns result as Map.
The JdbcTemplate.queryForMap expects arguments as following.
1.
Map<String,Object> queryForMap(String sql) throws DataAccessException 
Executes a static SQL and returns Map.
This method uses JDBC Statement, not a PreparedStatement.
The query is expected to be a single row query. The result will be mapped to a Map.
The key of the Map is column name and column value is the Map value for that key.
2.
Map<String,Object> queryForMap(String sql, @Nullable Object... args) throws DataAccessException 
Queries the given SQL as prepared statement and binding the SQL with given list of arguments. The query is expected to be a single row query. The result will be mapped to a Map.
3.
Map<String,Object> queryForMap(String sql, Object[] args, int[] argTypes) throws DataAccessException 
Queries the given SQL as prepared statement and binding the SQL with given list of arguments. The query is expected to be a single row query. The result will be mapped to a Map.

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.

Returns:
It returns the result Map (one entry per column, with column name as key, and column value as map value for that key).

Throws:
If the query does not return exactly one row, it throws IncorrectResultSizeDataAccessException.

Example using Spring Boot

PersonDAO.java
package com.concretepage;
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;

  // queryForMap(String sql)
  public long getPersonCount() {
	String sql = "select count(*) as cnt from person";
	Map<String, Object> map = jdbcTemplate.queryForMap(sql);
	return (Long)map.get("cnt");
  }

  // queryForMap(String sql, Object... args)
  public long getPersonCountByNameAndAge(String name, int age) {
	String sql = "select count(*) as cnt from person where name = ? and age = ?";
	Map<String, Object> map = jdbcTemplate.queryForMap(sql, name, age);
	return (Long)map.get("cnt");
  }

  // queryForMap(String sql, Object[] args, int[] argTypes)
  public Person getPersonsById(int id) {
	String sql = "select * from person where id = ?";
	Object[] args = { id };
	int[] argTypes = { java.sql.Types.INTEGER };
	Map<String, Object> map = jdbcTemplate.queryForMap(sql, args, argTypes);
	Person p = new Person((String)map.get("name"), (Integer)map.get("age"));
	p.setId((Integer)map.get("id"));
	return p;
  }
} 
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;
	}
} 
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("Total Count: " + personDAO.getPersonCount());
	System.out.println("Count: " + personDAO.getPersonCountByNameAndAge("Mohan", 25));
	Person p = personDAO.getPersonsById(1);
	System.out.println(p.getId() + " - " + p.getName() + " - " + p.getAge());
  }
} 
Find the MySQL table (person) screenshot used in our demo.
Spring JdbcTemplate.queryForMap()
Find the output.
Total Count: 3
Count: 2
1 - Rakesh - 30 

Reference

Class JdbcTemplate

Download Source Code

POSTED BY
ARVIND RAI
ARVIND RAI







©2024 concretepage.com | Privacy Policy | Contact Us