Several examples on Spring JdbcTemplate query, queryForObject, queryForList, queryForMap, queryForRowSet operations. This is a complete guide to understand running query operations on JdbcTemplate.
All these operations used to run sql queries, get the results and extract them as Java Objects. Depends on the situation, we need to pick right operation to get the results from database. Let’s have a look into several examples on all of the operations to understand how to use them first and finally will discuss when do we use the each operation.
1. Sample Data for Testing JdbcTemplate operations
Following is the sample data and User class used for all of the examples
User :
@Data // lomok
public class User {
private Long id;
private String userName;
private String password;
private Date createdTime;
private Date updatedTime;
private Date dateofBirth;
private UserType userType; // Enum Type
}
public enum UserType {
EMPLOYEE, STUDENT;
}
INSERT INTO `USER` VALUES
(1,'PeterM','ABC123abc*','2020-03-17 07:13:30',NULL,'STUDENT','1986-03-17'),
(2,'Mike','password','2020-03-18 14:59:35',NULL,'EMPLOYEE','1989-03-18'),
(3,'KingPeter','password','2020-03-19 12:19:15',NULL,'EMPLOYEE','2012-03-18'),
(4,'PeterH','ABC123abc*','2020-03-17 07:13:30',NULL,'STUDENT','1960-03-17'),
(5,'Kelvin','password','2020-03-18 14:59:35',NULL,'EMPLOYEE','1988-03-18'),
(6,'PeterLouise','password','2020-03-19 12:19:15',NULL,'EMPLOYEE','1992-03-18'),
(7,'JustinB','ABC123abc*','2020-03-17 07:13:30',NULL,'STUDENT','2000-03-17'),
(8,'AshjaA','password','2020-03-18 14:59:35',NULL,'EMPLOYEE','2018-03-18'),
(9,'JenniferH','password','2020-03-19 12:19:15',NULL,'EMPLOYEE','2001-03-18'),
(10,'DonaldT','ABC123abc*','2020-03-17 07:13:30',NULL,'STUDENT','1972-03-17'),
(11,'HilloryK','password','2020-03-18 14:59:35',NULL,'EMPLOYEE','1992-03-18'),
(12,'MartinKing','password','2020-03-19 12:19:15',NULL,'EMPLOYEE','1987-03-18');
2. query()
Most of the cases JdbcTemplate query() is used to run the sql query and get multiple rows results from database. To run query() we need to follow 3 steps.
- Provide a Sql query.
- Provide parameters values and types if the query has arguments.
- Extract the results.
Providing Sql Query : Provide Sql query using String object OR PreparedStatementCreator.
Providing Parameters : Provide arguments using Object[] OR using PreparedStatementSetter if query has parameters.
Extract Results : Results can be extracted using callback handler interfaces. Spring JDBC provide 3 callback handler interfaces RowMapper , ResultSetExtractor , RowCallbackHandler for extracting results.
Let’s have a look into some examples to understand when to choose String or PreparedStatementCreator to provide sql query, and when to choose Object[] or PreparedStatementSetter to provide arguments and when to choose RowMapper OR ResultSetExtractor OR RowCallbackHandler to get results.
2.1. RowMapper
UserMapper :
To create a custom RowMapper class, have to implements RowMapper callback interface and have to provide implementation for mapRow().
Creating custom RowMapper :
public class UserMapper implements RowMapper<User>{
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getLong("ID"));
user.setUserName(rs.getString("USERNAME"));
user.setPassword(rs.getString("PASSWORD"));
user.setCreatedTime(rs.getDate("CREATEDTIME"));
user.setUpdatedTime(rs.getDate("UPDATEDTIME"));
user.setUserType(UserType.valueOf(rs.getString("USERTYPE")));
user.setDateofBirth(rs.getDate("DATEOFBIRTH"));
return user;
}
}
2.1.1. A basic query example using RowMapper with no arguments.
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
@DisplayName("find-all-users-test")
void findAllUsersTest() {
String sql = "SELECT * FROM USER";
List<User> users = jdbcTemplate.query(sql, new UserMapper());
assertTrue(users.size() == 12);
users.forEach(System.err::println); // just to print in red color
}
2.1.2. A basic query example using RowMapper with parameters and providing types for arguments.
Providing types is optional, if you wants to convert provided parameter value java to SQL type, we need to provide SQL types.
@DisplayName("find-users-by-type-test")
@ParameterizedTest
@EnumSource(value = UserType.class)
void findUsersByTypeTest(UserType userType) {
String sql = "SELECT * FROM USER WHERE USERTYPE = ?";
List<User> users = jdbcTemplate.query(sql,
new Object[] { userType },
new int[] { Types.VARCHAR },
new UserMapper());
if (userType.equals(UserType.STUDENT))
assertTrue(users.size() == 4);
else
assertTrue(users.size() == 8);
System.err.println(users);
}
2.2. BeanPropertyRowMapper
If User class property names and USER database table column names are exactly same, we can use BeanPropertyRowMapper to get results instead of creating a mapper class for model class, automatically populates each column value of a single row to corresponding bean property.
List<User> users = jdbcTemplate.query(sql,
new Object[] { userType },
new int[] { Types.VARCHAR },
new BeanPropertyRowMapper(User.class));
2.3. ResultSetExtractor
For example, lets assume our requirement is getting Users by USERNAME and return as a Map of users, and map should contain USERTYPE as key and value is List<User>. In this case we can get results using ResultSetExtractor. We have to implement extractData(), that provides the complete results as ResultSet instance, we need to process them using next().
Creating custom ResultSetExtractor :
public class UserResultSetExtracter implements ResultSetExtractor<Map<UserType, List<User>>> {
@Override
public Map<UserType, List<User>> extractData(ResultSet rs) throws SQLException, DataAccessException {
return extractUsers(rs);
}
private Map<UserType, List<User>> extractUsers(ResultSet rs) throws SQLException {
Map<UserType, List<User>> users = new HashMap<UserType, List<User>>();
List<User> studentlist = new ArrayList<User>();
List<User> employeeList = new ArrayList<User>();
while (rs.next()) {
if(rs.getString("USERTYPE").equals(UserType.STUDENT.toString()))
studentlist.add(extractUser(rs));
else
employeeList.add(extractUser(rs));
}
users.put(UserType.STUDENT, studentlist);
users.put(UserType.EMPLOYEE, employeeList);
return users;
}
private User extractUser(ResultSet rs) throws SQLException {
User user = new User();
user.setId(rs.getLong("ID"));
user.setUserName(rs.getString("USERNAME"));
user.setPassword(rs.getString("PASSWORD"));
user.setCreatedTime(rs.getDate("CREATEDTIME"));
user.setUpdatedTime(rs.getDate("UPDATEDTIME"));
user.setUserType(UserType.valueOf(rs.getString("USERTYPE")));
user.setDateofBirth(rs.getDate("DATEOFBIRTH"));
return user;
}
}
A test for query() using ResultSetExtractor :
@DisplayName("find-users-by-name-test")
@ParameterizedTest
@ValueSource(strings = {"%Peter%"})
void findUsersByNameTest(String name) {
String sql = "SELECT * FROM USER where USERNAME LIKE ?";
Map<UserType, List<User>> users = jdbcTemplate.query(sql, new Object[] {name}, new UserResultSetExtracter());
assertTrue(users.get(UserType.STUDENT).size() == 2);
assertTrue(users.get(UserType.EMPLOYEE).size() == 2);
}
2.4. RowCallbackHandler
Sometimes our requirement is not mapping results to java objects, instead we just need to process results, we can choose RowCallbackHandler in those cases. For example we need to find users whose username has less than 6 characters and write users to JSON files. Let’s have a look into following example code snippets.
Creating custom RowCallbackHandler :
public class UserRowCallBackHandler implements RowCallbackHandler {
// ....
@Override
public void processRow(ResultSet rs) throws SQLException {
User user = extractUser(rs);
try {
objectMapper.configure(SerializationFeature.INDENT_OUTPUT, true);
objectMapper.writeValue(getUsersJosonFile(user.getId()), user);
users.add(user);
} catch (JsonProcessingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
// ....
}
A test on query using RowCallbackHandler :
@Test
@DisplayName("find-users-by-weak-username-test")
void findUsersHasWeakUserNameTest() {
String sql = "SELECT * FROM USER where LENGTH(USERNAME) < 6";
UserRowCallBackHandler callbackHandler = new UserRowCallBackHandler();
jdbcTemplate.query(sql, callbackHandler);
assertTrue(callbackHandler.getUsers().size() == 1);
}
2.5. PreparedStatementCreator
PreparedStatementCreator is one of the central callback interfaces used by the JdbcTemplate class. This interface creates a PreparedStatement given a connection, provided by the JdbcTemplate class.
Creating a custom PreparedStatementCreator :
public class UserPreparedStatementCreator implements PreparedStatementCreator, SqlProvider {
private String param;
private String sql;
public UserPreparedStatementCreator(String sql, String param) {
super();
this.sql = sql;
this.param = param;
}
@Override
public String getSql() {
return sql;
}
@Override
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
PreparedStatement ps = con.prepareStatement(getSql(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
ps.setString(1, param);
return ps;
}
}
A test for query() using PreparedStatementCreator :
@DisplayName("findUsersByNameTest")
@ParameterizedTest
@ValueSource(strings = {"%Peter%"})
void findByNameUsersTest(String name) {
String sql = "SELECT * FROM USER where USERNAME LIKE ?";
List<User> users = jdbcTemplate.query(new UserPreparedStatementCreator(sql, name), new UserMapper());
assertTrue(users.size() == 4);
}
2.6. PreparedStatementSetter
PreparedStatementSetter is a callback interface for JdbcTemplate class, it is used to set parameter values provided by JdbcTemplate.
Creating custom PreparedStatementSetter :
public class UserPreparedStatementSetter implements PreparedStatementSetter {
private String name;
private UserType type;
public UserPreparedStatementSetter(String name, UserType type) {
super();
this.name = name;
this.type = type;
}
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setString(1, name);
ps.setString(2, type.toString());
}
}
A test for query() using PreparedStatementSetter :
@ParameterizedTest
@MethodSource("userNameAndTypeProvider")
void findByNameAndTyepTest(String name, UserType type) {
String sql = "SELECT * FROM USER where USERNAME LIKE ? AND USERTYPE = ?";
List<User> users = jdbcTemplate.query(sql, new UserPreparedStatementSetter(name, type), new UserMapper());
assertTrue(users.size() == 2);
}
static Stream<Arguments> userNameAndTypeProvider(){
return Stream.of(
arguments("Peter%", UserType.STUDENT),
arguments("%King%", UserType.EMPLOYEE)
);
}
We have seen several examples using JdbcTemplate.query() in this tutorial, now let’s take away when to use the right callback handler.
2.7. RowMapper vs ResultSetExtractor vs RowCallbackHandler
In JdbcTemplate query(), if you use RowMapper OR ResultSetExtractor OR RowCallbackHandler to extract the results, internally JdbcTemplate convert them to ResultSetExtractor implementation. But the key difference is return type and behaviour of each callback handler method.
| RowMapper | ResultSetExtractor | RowCallbackHandler |
|---|---|---|
1. T mapRow(ResultSet rs, int rowNum) | T extractData(ResultSet rs) | void processRow(ResultSet rs) |
2. Process one row of results at a time. So, if you get 4 rows of results mapRow() will be called 4 times for each query() execution. | Process all the results at a time. So, if you get any number of rows as results extractData() will be called only once for each query() execution. | Process one row of results at a time. So, if you get 4 rows of results processRow() will be called 4 times for each query() execution. |
3. To provide implementation, you need provide the Type for RowMapper and have to convert the results to provided type (refer example 1). | To provide implementation, you need provide the Type for ResultSetExtractor and have to convert the results to provided type (refer example 3). | To provide implementation, you no need provide the Type for RowCallbackHandler (refer example 4). |
| 4. RowMapper is best to choose when you need to map each row results to a Java domain model (refer example 1). | ResultSetExtractor is best to choose when you need to process all the results before converting them as domain models or Java objects (refer example 3). | RowCallbackHandler is best to choose when you need to process the each row results and saving it’s state. For example your requirement is to find specific users based on some condition and save them to Json file OR convert results as html/xml and save it in disk storage OR even you can map results to domain model and convert to List (refer example 4). |
| 5. Keeps the results as Stateless. | Keeps the results as Stateless. | Keeps the results as Stateful. |
Performance : Avoid to work with BeanPropertyRowMapper for large data sets. Whether you use RowMapper OR ResultSetExtractor OR RowCallbackHandler there won’t be much performance difference, because internally all of them converted to ResultSetExtractor implementation. But when you are working on large data set, you may face out of memory issues, to avoid them need to create efficient queries and apply paging or slicing on the database results.
2.8. PreparedStatementCreator vs PreparedStatementSetter
2.8.1. When do use PreparedStatementCreator with JdbcTemplate?
When you call the query() by providing sql query as String , JdbcTemplate takes the responsible for creating PreparedStatement object (if you provided query parameters) with default optimum settings. So that, you don’t have control over the PreparedStatement settings. In some cases, you may need to control the PreparedStatement with specific configuration values to get results as per your requirement (refer Example 5). PreparedStatementCreator provides responsibility of creating PreparedStatement instance to it’s implementation class, so that it has full control over it.
2.8.2. When do use PreparedStatementSetter with JdbcTemplate?
PreparedStatementSetter is used to set the parameter values to the PreparedStatement provided by JdbcTemplate, and also it maps the parameter java type to respective SQL type. When you have large number of parameters in query and type conversion between Java types and sql types is required, PreparedStatementSetter implementation provides full control over it.
3. queryForObject()
3.1. queryForObject Example for a Single value
When you run queryForObject() with no parameters, JdbcTemplate creates Statement, executes query and returns single row or single column value as result.
@Test
@DisplayName("users-count-test")
void usersCountTest() {
String sql = "SELECT count(*) FROM USER";
int total = jdbcTemplate.queryForObject(sql, Integer.class);
assertTrue(total == 12);
}
3.2. queryForObject with parameters
Here an example with parameters and RowMapper. When you run queryForObject() with parameters JdbcTemplates creates PreparedStatement and executes query and return single row or single value as results.
public User findById(Long id) {
String sql = "SELECT * FROM USER where ID = ?";
return jdbcTemplate.queryForObject(sql, new Object[] { id }, new UserMapper());
}
3.3. queryForObject with null
In queryForObject() if the results not found, JdbcTemplate does not returns null, instead it throws EmptyResultDataAccessException. But it’s good to return null, otherwise it may breaks the application flow. Following is the example to handle EmptyResultDataAccessException and return null.
public User findById(Long id) {
String sql = "SELECT * FROM USER where ID = ?";
try {
return jdbcTemplate.queryForObject(sql, new Object[] { id }, new UserMapper());
} catch (EmptyResultDataAccessException e) {
// Log error
return null;
}
}
3.4. queryForObject with Optional
Java 8 Optional class provides a best way to deal with null values. It’s better implementation that returning an empty Optional instead of returning null when no results found.
public Optional<User> findById(Long id) {
String sql = "SELECT * FROM USER WHERE ID = ?";
try {
return Optional.of(jdbcTemplate.queryForObject(sql, new Object[] { id }, new UserMapper()));
//return Optional.of(jdbcTemplate.queryForObject(sql, new Object[] { id }, new BeanPropertyRowMapper<User>()));
} catch (EmptyResultDataAccessException e) {
return Optional.empty();
}
}
4. queryForList()
4.1. queryForList for single column results
JdbcTemplate queryForList() returns multiple rows results as List for single column or multiple columns.
@Test
@DisplayName("find-by-id-test")
void findWeakUsersTest() {
String sql = "SELECT USERNAME FROM USER where LENGTH(USERNAME) < 7";
List<String> users = jdbcTemplate.queryForList(sql, String.class);
assertTrue(users.size() == 5);
}
4.2. queryForList for selected columns
A queryForList example to get selected columns (ID, USERNAME, HAS_NUMERIC_PASSWORD) as results. HAS_NUMERIC_PASSWORD is not a database column, if the password has number, taking its value as ‘true’ else ‘false’ for some validation.
@Test
@DisplayName("find-users-test")
void findUsersTest() {
String sql = "SELECT ID, USERNAME, IF(PASSWORD REGEXP '[0-9]', 'true', 'false') as HAS_NUMERIC_PASSWORD FROM USER";
List<Map<String, Object>> users = jdbcTemplate.queryForList(sql);
users.forEach( rowMap -> {
Long id = (Long) rowMap.get("ID");
String userName = (String) rowMap.get("USERNAME");
boolean hasNumericPass = Boolean.valueOf(rowMap.get("HAS_NUMERIC_PASSWORD").toString());
System.out.println(id +"-"+userName+"-"+hasNumericPass);
});
}
Output :
1-PeterM-true 2-Mike-false 3-KingPeter-false 4-PeterH-true 5-Kelvin-false 6-PeterLouise-false 7-JustinB-true 8-AshjaA-false 9-JenniferH-false 10-DonaldT-true 11-HilloryK-false 12-MartinKing-false
4.3. queryForList with parameters
A queryForList example to get selected columns results and query with parameters. Provide parameters list Object[] and corresponding sql types in int[]. Types is optional, it’s required when you need to convert provided parameter java type to sql type.
@ParameterizedTest
@EnumSource(names = {"EMPLOYEE"})
void findUsersAgeTest(UserType userType) {
// TIMESTAMPDIFF(YEAR, DATEOFBIRTH, CURDATE()) - gets dates differnce in years
String sql = "SELECT USERNAME, TIMESTAMPDIFF(YEAR, DATEOFBIRTH, CURDATE()) as AGE FROM USER WHERE USERTYPE=?";
List<Map<String, Object>> users = jdbcTemplate.queryForList(sql, new Object[]{userType}, new int[]{Types.VARCHAR});
users.forEach( rowMap -> {
String userName = (String) rowMap.get("USERNAME");
int age = ((Long) rowMap.get("AGE")).intValue();
System.out.println(userName+"-"+age);
});
}
Output :
Mike-31 KingPeter-8 Kelvin-32 PeterLouise-28 AshjaA-2 JenniferH-19 HilloryK-28 MartinKing-33
5. queryForMap()
JdbcTemplate queryForMap() returns single row results as Map at a time either for single column or multiple columns.
5.1. queryForMap with no parameters Example
@Test
void findFirstUserAgeTest() {
// TIMESTAMPDIFF(YEAR, DATEOFBIRTH, CURDATE()) - gets dates difference in years just for test
String sql = "SELECT USERNAME, TIMESTAMPDIFF(YEAR, DATEOFBIRTH, CURDATE()) as AGE FROM USER LIMIT 1";
Map<String, Object> rowMap = jdbcTemplate.queryForMap(sql);
String userName = (String) rowMap.get("USERNAME");
int age = ((Long) rowMap.get("AGE")).intValue();e
System.out.println(userName+"-"+age); //PeterM-34
}
5.2. queryForMap with parameters Example
@ParameterizedTest
@ValueSource(strings = {"PeterM"})
void findUserByNameTest(String name) {
// run on mysql
String sql = "SELECT ID, USERNAME, IF(PASSWORD REGEXP '[0-9]', 'true', 'false') as HAS_NUMERIC_PASSWORD FROM USER WHERE USERNAME=?";
Map<String, Object> rowMap = jdbcTemplate.queryForMap(sql, new Object[]{name});
Long id = (Long) rowMap.get("ID");
String userName = (String) rowMap.get("USERNAME");
boolean hasNumericPass = Boolean.valueOf(rowMap.get("HAS_NUMERIC_PASSWORD").toString());
System.out.println(id +"-"+userName+"-"+hasNumericPass); //1-PeterM-true
}
6. queryForRowSet()
queryForRowSet() returns SqlRowSet, which is similar to ResultSet, we can access multiple rows results of single column or multiple columns using rowset.next() .
6.1. queryForRowSet example with no parameter
@Test
@DisplayName("find-weak-user-names-test")
void findWeakUsersTest() throws InvalidResultSetAccessException, SQLException {
String sql = "SELECT USERNAME FROM USER where LENGTH(USERNAME) < 7";
ResultSetWrappingSqlRowSet rowset = (ResultSetWrappingSqlRowSet) jdbcTemplate.queryForRowSet(sql);
CachedRowSet crs = (CachedRowSet) rowset.getResultSet();
List<String> users = new ArrayList<String>();
while(crs.next()){
users.add(rowset.getString("USERNAME"));
}
System.out.println(users);
}
6.2. queryForRowSet example with parameters
@ParameterizedTest
@EnumSource(names = {"EMPLOYEE"})
void findUsersAgeTest(UserType userType) {
String sql = "SELECT USERNAME, TIMESTAMPDIFF(YEAR, DATEOFBIRTH, CURDATE()) as AGE FROM USER WHERE USERTYPE=?";
SqlRowSet rowset = jdbcTemplate.queryForRowSet(sql, new Object[]{userType}, new int[]{Types.VARCHAR});
List<Map<String, Integer>> users = new ArrayList<Map<String, Integer>>();
while(rowset.next()){
users.add(Map.of(rowset.getString("USERNAME"), rowset.getInt("Age")));
}
System.out.println(users);
}
7. query vs queryForObject vs queryForList vs queryForMap vs queryForRowSet
When do use query() : As it supports different callback handlers to extract the results, when we need to get multiple rows data as results and convert them to Java Domain Models ( for example List<User>), query() is right fit in those situations.
When do use queryForObject() : This operation also supports for RowMapper callback handler. When you wants to get single row results and convert it to Domain Model Or you wants to get single value as results, queryForObject() is right fit in those situations.
When do use queryFoList() : Like query() operation queryFoList() also returns multiple rows data, but there is no callback handlers support. Sometimes we need selected column results of multiple rows (projections) required for instant use, we no need to convert them as Domain Model. queryFoList() is right fit in those situations.
When do use queryForMap() : Like queryForObject() operation queryForMap() also returns single value or multiple column values of one database table row. Sometimes we need selected column results of single row (projections) required for instant use, we no need to convert them as Domain Model. queryFoMap() is right fit in those situations.
When do use queryForRowSet() : All the above operations works on top of ResultSet to get the results. queryForRowSet() loads all the results to CachedRowSet wraps it as SqlRowSet implementation and returns, which is mirror interface for RowSet representing a disconnected variant of ResultSet data. If you would like to work in disconnected fashion or to work with RowSet for specific reasons, can be used queryForRowSet() operation (refer example 1 under queryForObject() to understand how to get RowSet).
8. Conclusion
A single JdbcTemplate query operation may not fit in all situations. In this guide we have covered several examples on Spring JdbcTemplate query, queryForObject, queryForList, queryForMap, queryForRowSet operations to understand how to use them depends on requirement.
In Short :
- query() – is used to get multiple rows results as list.
- queryForObject() – is used to get single value or single row results.
- queryForList() – is used to get selected columns data of multiple rows as List.
- queryForMap() – is used to get selected columns data of single row as Map.
- queryForRowSet() – is used when you wants to work with RowSets instead of ResultSet.
Checkout source code at GitHub.
Other Spring JDBC Examples :
- Spring JdbcTemplate – Batch Update
- Spring JdbcTemplate – Pagination
- Spring JdbcTemplate – Stored Procedures
- Spring JdbcTemplate – IN Clause
- Spring JdbcTemplate – Auto-generated Keys
- Spring Boot – Loading Initial Data
9. References
- Spring Document
- JUnit 5 Parameterized Test
- Spring Boot Data JDBC example
- Spring Boot – Loading Initial Data
- Lombok Data
