현재 근무지에서 사용중인 환경이 Spring4에 MVC이다.
안타깝게도 JPA등의 ORM을 사용하지 않아 Repository에서 사용할 예제 패턴을 정리해본다.
- Query for Single Row
In Spring, we can use jdbcTemplate.queryForObject() to query a single row record from database, and convert the row into an object via row mapper.
1.1 Custom RowMapper
CustomerRowMapper.java
import org.springframework.jdbc.core.RowMapper;
import java.sql.ResultSet;
import java.sql.SQLException;
public class CustomerRowMapper implements RowMapper<Customer> {
@Override
public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
Customer customer = new Customer();
customer.setID(rs.getLong("ID"));
customer.setName(rs.getString("NAME"));
customer.setAge(rs.getInt("AGE"));
customer.setCreatedDate(rs.getTimestamp("created_date").toLocalDateTime());
return customer;
}
}import org.springframework.jdbc.core.JdbcTemplate;
@Autowired
private JdbcTemplate jdbcTemplate;
public Customer findByCustomerId(Long id) {
String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";
return jdbcTemplate.queryForObject(sql, new Object[]{id}, new CustomerRowMapper());
}1.2 Spring BeanPropertyRowMapper, this class saves you a lot of time for the mapping.
import org.springframework.jdbc.core.BeanPropertyRowMapper;
public Customer findByCustomerId2(Long id) {
String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";
return (Customer) jdbcTemplate.queryForObject(
sql,
new Object[]{id},
new BeanPropertyRowMapper(Customer.class));
}1.3 In Java 8, we can map it directly:
public Customer findByCustomerId3(Long id) {
String sql = "SELECT * FROM CUSTOMER WHERE ID = ?";
return jdbcTemplate.queryForObject(sql, new Object[]{id}, (rs, rowNum) ->
new Customer(
rs.getLong("id"),
rs.getString("name"),
rs.getInt("age"),
rs.getTimestamp("created_date").toLocalDateTime()
));
}- Query for Multiple Rows
For multiple rows, we use jdbcTemplate.query()
2.1 Custom RowMapper
public List<Customer> findAll() {
String sql = "SELECT * FROM CUSTOMER";
List<Customer> customers = jdbcTemplate.query(
sql,
new CustomerRowMapper());
return customers;
}2.2 BeanPropertyRowMapper
public List<Customer> findAll() {
String sql = "SELECT * FROM CUSTOMER";
List<Customer> customers = jdbcTemplate.query(
sql,
new BeanPropertyRowMapper(Customer.class));
return customers;
}2.3 Java 8
public List<Customer> findAll() {
String sql = "SELECT * FROM CUSTOMER";
return jdbcTemplate.query(
sql,
(rs, rowNum) ->
new Customer(
rs.getLong("id"),
rs.getString("name"),
rs.getInt("age"),
rs.getTimestamp("created_date").toLocalDateTime()
)
);
}2.4 jdbcTemplate.queryForList, it works, but not recommend, the mapping in Map may not same as the object, need casting.
public List<Customer> findAll() {
String sql = "SELECT * FROM CUSTOMER";
List<Customer> customers = new ArrayList<>();
List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
for (Map row : rows) {
Customer obj = new Customer();
obj.setID(((Integer) row.get("ID")).longValue());
obj.setName((String) row.get("NAME"));
// Spring returns BigDecimal, need convert
obj.setAge(((BigDecimal) row.get("AGE")).intValue());
obj.setCreatedDate(((Timestamp) row.get("CREATED_DATE")).toLocalDateTime());
customers.add(obj);
}
return customers;
}- Query for a Single Value
It’s same like query a single row from database, uses jdbcTemplate.queryForObject()
3.1 Single column name
public String findCustomerNameById(Long id) {
String sql = "SELECT NAME FROM CUSTOMER WHERE ID = ?";
return jdbcTemplate.queryForObject(
sql, new Object[]{id}, String.class);
}3.2 Count
public int count() {
String sql = "SELECT COUNT(*) FROM CUSTOMER";
// queryForInt() is Deprecated
// https://www.mkyong.com/spring/jdbctemplate-queryforint-is-deprecated/
//int total = jdbcTemplate.queryForInt(sql);
return jdbcTemplate.queryForObject(sql, Integer.class);
}- Test
Run a Spring Boot CommandLineRunner application, create tables and test the APIs.
pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- in-memory database -->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
</dependency>StartApplication.java
package com.mkyong;
import com.mkyong.customer.Customer;
import com.mkyong.customer.CustomerRepository;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;
@SpringBootApplication
public class StartApplication implements CommandLineRunner {
private static final Logger log = LoggerFactory.getLogger(StartApplication.class);
@Autowired
JdbcTemplate jdbcTemplate;
@Autowired
CustomerRepository customerRepository;
public static void main(String[] args) {
SpringApplication.run(StartApplication.class, args);
}
@Override
public void run(String... args) {
log.info("StartApplication...");
startCustomerApp();
}
// Tested with H2 database
void startCustomerApp() {
jdbcTemplate.execute("DROP TABLE customer IF EXISTS");
jdbcTemplate.execute("CREATE TABLE customer(" +
"id SERIAL, name VARCHAR(255), age NUMERIC(2), created_date timestamp)");
List<Customer> list = Arrays.asList(
new Customer("Customer A", 19),
new Customer("Customer B", 20),
new Customer("Customer C", 21),
new Customer("Customer D", 22)
);
list.forEach(x -> {
log.info("Saving...{}", x.getName());
customerRepository.save(x);
});
log.info("[FIND_BY_ID]");
log.info("{}", customerRepository.findByCustomerId(1L));
log.info("{}", customerRepository.findByCustomerId2(2L));
log.info("{}", customerRepository.findByCustomerId3(3L));
log.info("[FIND_ALL]");
log.info("{}", customerRepository.findAll());
log.info("{}", customerRepository.findAll2());
log.info("{}", customerRepository.findAll3());
log.info("{}", customerRepository.findAll4());
log.info("[FIND_NAME_BY_ID]");
log.info("{}", customerRepository.findCustomerNameById(4L));
log.info("[COUNT]");
log.info("{}", customerRepository.count());
}
}Output
INFO com.mkyong.StartApplication - Saving...Customer A
INFO com.mkyong.StartApplication - Saving...Customer B
INFO com.mkyong.StartApplication - Saving...Customer C
INFO com.mkyong.StartApplication - Saving...Customer D
INFO com.mkyong.StartApplication - [FIND_BY_ID]
INFO com.mkyong.StartApplication - Customer{ID=1, name='Customer A', age=19, createdDate=2019-08-01T15:48:45.950848}
INFO com.mkyong.StartApplication - Customer{ID=2, name='Customer B', age=20, createdDate=2019-08-01T15:48:45.961819}
INFO com.mkyong.StartApplication - Customer{ID=3, name='Customer C', age=21, createdDate=2019-08-01T15:48:45.961819}
INFO com.mkyong.StartApplication - [FIND_ALL]
INFO com.mkyong.StartApplication - [
Customer{ID=1, name='Customer A', age=19, createdDate=2019-08-01T15:48:45.950848},
Customer{ID=2, name='Customer B', age=20, createdDate=2019-08-01T15:48:45.961819},
Customer{ID=3, name='Customer C', age=21, createdDate=2019-08-01T15:48:45.961819},
Customer{ID=4, name='Customer D', age=22, createdDate=2019-08-01T15:48:45.961819}
]
//...omitted, duplicate code
INFO com.mkyong.StartApplication - [FIND_NAME_BY_ID]
INFO com.mkyong.StartApplication - Customer D
INFO com.mkyong.StartApplication - [COUNT]
INFO com.mkyong.StartApplication - 4출처
MKyong
back-end developer