1. 환경설정
가. 프로젝트 환경설정
1) 종속성 추가
// build.gradle
implementation 'org.springframework.boot:spring-boot-starter-jdbc'
runtimeOnly 'mysql:mysql-connector-java'
Java
복사
2) DB 설정값 추가
// apllication.properties
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/todo_db?serverTimezone=UTC&characterEncoding=UTF-8
spring.datasource.username=todo
spring.datasource.password=team5
spring.datasource.initialization-mode=always
Java
복사
나. SQL 설정
1) shema.sql
DROP TABLE IF EXISTS `employees`;
CREATE TABLE if not exists `employees`(
`id` bigint(20) auto_increment NOT NULL primary key,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`yearly_income` bigint(20) NOT NULL
);
SQL
복사
2) data.sql
INSERT INTO employees (first_name, last_name, yearly_income) VALUES ('John', 'Doe', 80000);
INSERT INTO employees (first_name, last_name, yearly_income) VALUES ('Mary', 'Jackson', 75000);
INSERT INTO employees (first_name, last_name, yearly_income) VALUES ('Peter', 'Grey', 60000);
SQL
복사
다. Model & DAO setting
1) Model, Employee
•
toMap은 Employee 객체를 Map 형태로 변환
public class Employee {
private long id;
private String firstName;
private String lastName;
private long yearlyIncome;
public Employee() {
}
public Employee(String firstName, String lastName, long yearlyIncome) {
this.firstName = firstName;
this.lastName = lastName;
this.yearlyIncome = yearlyIncome;
}
public Map<String, Object> toMap() {
Map<String, Object> values = new HashMap<>();
values.put("first_name", firstName);
values.put("last_name", lastName);
values.put("yearly_income", yearlyIncome);
return values;
}
// Getter & Setter
}
Java
복사
2) DAO, EmployeeRepository
•
DAO 객체에는 Repository 어노테이션을 붙여서 Bean에 등록
@Repository
public class EmployeeRepository {
private final JdbcTemplate jdbcTemplate;
@Autowired
public EmployeeRepository(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
}
Java
복사
2. CRUD 기능 구현
가. 생성
1) save
•
JdbcTemplate::update: SQL문의 '?'와 첫번째 이후의 매개변수를 맵핑
public void save(Employee employee) {
String sqlQuery = "insert into employees(first_name, last_name, yearly_income) " +
"values (?, ?, ?)";
jdbcTemplate.update(sqlQuery,
employee.getFirstName(),
employee.getLastName(),
employee.getYearlyIncome());
}
Java
복사
2) save with returned ID
•
객체를 테이블에 저장하고 생성된 ID 값을 반환함
•
SimpleJdbcInsert는 JdbcTemplate을 감싸는 객체로, Insert 명령에 따른 ID 반환을 쉽게 할 수 있도록 도와줌
→ 테이블명과 id 컬럼을 전달해야 함
•
SimpleJdbcInsert::executeAndReturnKey: 매개변수로 Insert할 객체를 Map으로 변환된 값을 전달해야 함
public long simpleSave(Employee employee) {
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate)
.withTableName("employees")
.usingGeneratedKeyColumns("id");
return simpleJdbcInsert.executeAndReturnKey(employee.toMap()).longValue();
}
Java
복사
3) NamedParameterJdbcTemplate
•
SQL의 '?' 대신 사용할 수 있는 방법
•
관련 내용 학습할 것
나. 조회
1) query or queryForObject 활용
•
전체조회: SQL query에 변수를 넣을 필요가 없으므로 JdbcTemplate::query 사용
•
부분조회: SQL query에 변수를 삽입해야 하므로 queryForObject 사용
public List<User> findAll() {
String sqlQuery = "select id, first_name, last_name, yearly_income from employees";
return jdbcTemplate.query(sqlQuery, memberRowMapper());
}
public User findById(long id) {
String sqlQuery = "select id, first_name, last_name, yearly_income from employees where id = ?";
return jdbcTemplate.queryForObject(sqlQuery, memberRowMapper(), id);
}
SQL
복사
다. 수정
1) update 활용
•
DB에 대한 갱신이므로 Jdbc::update 사용
public void update(User user) {
String sqlQuery = "update employees set " +
"first_name = ?, last_name = ?, yearly_income = ? " +
"where id = ?";
jdbcTemplate.update(sqlQuery
, user.getFirstName()
, user.getLastName()
, user.getYearlyIncome()
, user.getId());
}
SQL
복사
라. 삭제
1) update 활용
•
DB에 대한 갱신이므로 Jdbc::update 사용
public boolean delete(long id) {
String sqlQuery = "delete from employees where id = ?";
return jdbcTemplate.update(sqlQuery, id) > 0;
}
SQL
복사
Reference
•
JDBC template know-how, https://github.com/benelog/spring-jdbc-tips
•
JDBC 기본 사용법, https://www.baeldung.com/spring-jdbc-jdbctemplate