How to write Query with JdbcTemplate!
(Cases of retrieving one or multiple records with a SELECT statement)
TOC
Premise
We will explain assuming that the User class has the following table (users) and stores it.
users table
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE IF NOT EXISTS users ( id VARCHAR(50) PRIMARY KEY ,password VARCHAR(100) ,name VARCHAR(50) ,birthday DATE ,age INT ,marrige BOOLEAN ,role VARCHAR(50) ); |
User class
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
import java.util.Date; import lombok.Data; // Annotation with setter/getter @Data public class User { private String id; private String password; private String name; private Date birthday; private int age; private boolean marrige; private String role; // Default Constructor public User() { } public User(String id, String password, String name, Date birthday, int age, boolean marrige, String role) { this.id = id; this.password = password; this.name = name; this.birthday = birthday; this.age = age; this.marrige = marrige; this.role = role; } } |
Get Data
I will explain the case where only one record and multiple records are acquired.
How to get with Map
Get only 1
Create SQL statementCreate SQL statementGet the query result in the form of Map<String, Object>
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; public class UserDao { @Autowired JdbcTemplate jdbcTemplate; public User selectOne(String id) throws DataAccessException { // Execute the SQL with the queryForMap method and receive the result as a List of Map.SQL statement String sql = "" + "SELECT" + " *" + " FROM" + " users" + " WHERE" + " id = ?"; // Execute the SQL with the queryForMap method and receive the result as a List of Map. // Give the value to be applied to the [?] part of the SQL statement together. Map<String, Object> oneUser = jdbcTemplate.queryForMap(sql, id); // Store in User object. User user = new User( (String) oneUser.get("id") ,(String) oneUser.get("password") ,(String) oneUser.get("name") ,(Date) oneUser.get("birthday") ,((Integer) oneUser.get("age")).intValue() ,(Boolean) oneUser.get("marrige") ,(String) oneUser.get("role") ); return user; } } |
Get multiple
Get the query result with List<Map<String, Object>>.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; public class UserDao { @Autowired JdbcTemplate jdbcTemplate; public List<User> selectMany() throws DataAccessException { // Create SQL statement String sql = "" String sql = "" + "SELECT" + " *" + " FROM" + " users"; // Execute the SQL with the queryForList method and receive it as a List of the result Map. List<Map<String, Object>> users = jdbcTemplate.queryForList(sql); // Create List for storing User object. List<User> userList = new ArrayList<User>(); // Iterate the list of received Map with for statement and store the value of each user in User object. for(Map<String, Object> eachUser: users) { User user = new User( (String) eachUser.get("id") ,(String) eachUser.get("password") ,(String) eachUser.get("name") ,(Date) eachUser.get("birthday") ,((Integer) eachUser.get("age")).intValue() ,(Boolean) eachUser.get("marrige") ,(String) eachUser.get("role") ); // Add a User object to the List. userList.add(user); } return userList; } } |
How to get with RowMapper (requires preparation)
You need to prepare your own mapper that implements the RowMapper interface.
Preparation
Create a class that implements RowMapper interface.
(Only 1 case, common to multiple cases)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; import com.example.demo.login.domain.model.User; // Prepare a class that implements RowMapper interface. public class UserRowMapper implements RowMapper<User> { @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { // Implementation that stores the query result (ResultSet rs) in the User object User user = new User( rs.getString("id") ,rs.getString("password") ,rs.getString("name") ,rs.getDate("birthday") ,rs.getInt("age") ,rs.getBoolean("marrige") ,rs.getString("role") ); return user; } } |
Get only 1
Receive mapping result that implements RowMapper interface.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; public class UserDaoRowMapper { @Autowired JdbcTemplate jdbcTemplate; public User selectOne(String id) throws DataAccessException { // create SQL statement String sql = "" + "SELECT" + " *" + " FROM" + " users" + " WHERE" + " id = ?"; // Execute queryForObject method // Map the SQL result with UserRowMapper and receive the result. UserRowMapper rowMapper = new UserRowMapper(); User user = jdbcTemplate.queryForObject(sql, rowMapper, id); return user; } } |
Get multiple
Receive the result of implementing RowMapper interface in List.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; public class UserDaoRowMapper { @Autowired JdbcTemplate jdbcTemplate; public List<User> selectMany() throws DataAccessException { // create SQL statement String sql = "" + "SELECT" + " *" + " FROM" + " users"; // Execute queryForObject method // Receive List of User objects, which is the mapping result of UserRowMapper. UserRowMapper rowMapper = new UserRowMapper(); List<User> userList = jdbcTemplate.query(sql, rowMapper); return userList; } } |
How to get with BeanPropertyRowMapper (no preparation required!)
No need to create a class that implements the RowMapper interface!
If the column name of the table and the field name of the mapping class are the same,
It will set the value automatically.
Applicable example
- snake case
Table column name: user_id
Class field name: userId - Same case
Table column name: id
Class field name: id
* In the class to store (in this case, User class),
It is assumed that there is a Default Constructor and a setter.
If you’re using Lombok, either the @Setter annotation or
It would be nice to have the @Data annotation.
Get only 1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.stereotype.Repository; public class UserDaoBeanPropertyRowMapper { @Autowired JdbcTemplate jdbcTemplate; public User selectOne(String id) throws DataAccessException { // create SQL statement String sql = "" + "SELECT" + " *" + " FROM" + " users" + " WHERE" + " id = ?"; // Execute the queryForObject method // SQL execution result can be obtained by automatically mapping to User class. RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class); User user = jdbcTemplate.queryForObject(sql, rowMapper, id); return user; } } |
Get multiple
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.BeanPropertyRowMapper; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.stereotype.Repository; public class UserDaoBeanPropertyRowMapper { @Autowired JdbcTemplate jdbcTemplate; public List<User> selectMany() throws DataAccessException { // create SQL statement String sql = "" + "SELECT" + " *" + " FROM" + " users"; // Execute the query method // SQL execution result is automatically mapped to User class and can be obtained in List. RowMapper<User> rowMapper = new BeanPropertyRowMapper<User>(User.class); List<User> userList = jdbcTemplate.query(sql, rowMapper); return userList; } } |
Which one is better?
First : BeanPropertyRowMapper
Easiest way to map.
If setters are allowed, use them.
If you can’t use the setter (need to convert to ValueObject),
It can be implemented with RowMapper.
Second : RowMapper
Creating a class to map is a little troublesome.
However, if you create it, you can get one and multiple things like Map in two places
There is no need to write the setter process to the User class each time.
You can use it when you want to define your own mapping class.
Third : Map
Because it is necessary to write to store the query acquisition result in the User class,
Not recommended in terms of time and maintenance.
Comments