Spring JdbcTemplate.queryForMap()
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
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
Map
.
3.
Map<String,Object> queryForMap(String sql, Object[] args, int[] argTypes) throws DataAccessException
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.javapackage 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; } }
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; } }
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
<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>
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()); } }
Total Count: 3 Count: 2 1 - Rakesh - 30