<!--[if !supportLists]-->n <!--[endif]-->Spring Boot, MariaDB를 이용해서 EMP 테이블을 만들고 JdbcTemplate을 이용하여 CRUD 기능을 구현해 보자.
STS에서
File -> New -> Project -> Spring Starter Project
Name : jdbc2
Package : jdbc
다음화면에서 SQL : JDBC, MySQL 선택
[src/main/resources/schema.sql(파일 속성에서 text encoding을 UTF-8로)]
drop database if exists jdbc2;
create database jdbc2;
use emp;
create table emp
(
empno int(4) not null auto_increment,
ename varchar(50),
primary key (empno)
) ENGINE=InnoDB;
[src/main/resources/data.sql(파일 속성에서 text encoding을 UTF-8로)]
insert into emp(ename) values ('1길동');
insert into emp(ename) values ('2길동');
insert into emp(ename) values ('3길동');
MariaDB에서 직접 SQL을 작성하여 테이블 및 데이터를 생성할 수 있지만 스프링 부트에서는 클래스패스 경로에 schema.sql, data.sql이 존재하면 자동실행 하므로 스키마 생성부분과 데이터 생성부분을 파일로 만들어두면 된다.
[src/main/resources/application.properties]
spring.datasource.platform=mysql
spring.datasource.url=jdbc:mysql://localhost/jdbc2?createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=1111
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.sql-script-encoding=UTF-8
spring.datasource.validation-query=select 1
#커넥션풀에서 커넥션을 가져올 경우 커넥션이 유효한지 검사
spring.datasource.test-on-borrow=true
#spring.datasource.auto-commit=true
도메인 클래스(Emp.java) – 테이블구조와 동일하다.
package jdbc.domain;
public class Emp {
private Long empno;
private String ename;
public Emp() { }
public Emp(String ename) { this.ename = ename; }
public Emp(Long empno, String ename) {
this.empno = empno;
this.ename = ename;
}
public Long getEmpno() { return empno; }
public void setEmpno(Long empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String toString() { return "[empno=" + empno +",ename=" + ename + "]"; }
}
RowMapper 구현체(EmpRowMapper.java)
package jdbc.repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import jdbc.domain.Emp;
@Repository
public class EmpRowMapper implements RowMapper {
@Override
public Emp mapRow(ResultSet rs, int rowNum) throws SQLException {
Long empno = rs.getLong("empno");
String ename = rs.getString("ename");
return new Emp(empno, ename);
}
}
Repository 인터페이스(EmpRepository.java) – 영속성 서비스용 인터페이스
package jdbc.repository;
import java.util.List;
import jdbc.domain.Emp;
public interface EmpRepository {
List<Emp> findAll();
Emp findOne(Long empnno);
Emp save(Emp emp);
void delete(Long empno);
}
Repository 구현체(EmpRepositoryImpl.java) – 영속성 서비스용 구상클래스
package jdbc.repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import jdbc.domain.Emp;
@Repository
@Transactional(readonly=true)
public class EmpRepositoryImpl implements EmpRepository {
private SimpleJdbcInsert jdbcInsert;
private JdbcTemplate jdbcTemplate;
@Autowired
RowMapper<Emp> empRowMapper;
@Autowired
public void setDataSource(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
@Override
public List<Emp> findAll() {
List<Emp> emps = jdbcTemplate.query("select empno, ename from emp",empRowMapper);
return emps;
}
@Override
public Emp findOne(Long empno) {
return (Emp)jdbcTemplate.queryForObject("select empno, ename from emp where empno = ?", empRowMapper, empno);
}
@Override
@Transactional(readonly=false)
public Emp save(Emp emp) {
SqlParameterSource param = new BeanPropertySqlParameterSource(emp);
if (emp.getEmpno() == null) {
Number key = jdbcInsert.executeAndReturnKey(param);
emp.setEmpno(key.longValue());
}
else {
this.jdbcTemplate.update(
"insert into emp (empno, ename) values (?, ?)",
emp.getEmpno(), emp.getEname()
);
}
return emp;
}
@Override
@Transactional(readonly=false)
public void delete(Long empno) {
this.jdbcTemplate.update(
"delete from emp where empno = ?",
empno
);
}
//생성자가 실행된 후에 실행된다.
@PostConstruct
public void init() {
//INSERT SQL Auto Create
jdbcInsert = new SimpleJdbcInsert(jdbcTemplate).withTableName("emp").usingGeneratedKeyColumns("empno");
}
}
스프링 부트 메인(Jdbc2Application.java)
package jdbc;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import jdbc.domain.Emp;
import jdbc.repository.EmpRepository;
@SpringBootApplication
public class Jdbc2Application implements CommandLineRunner {
public static void main(String[] args) {
SpringApplication.run(Jdbc2Application.class, args);
}
@Autowired
EmpRepository empRepository;
public void run(String...args) {
//전체 사원 SELECT
List<Emp> emps = empRepository.findAll();
for(Emp e : emps) { System.out.println(e); }
System.out.println("---------------------");
//2번 사원 SELECT
Emp e = empRepository.findOne(2L);
System.out.println(e);
System.out.println("---------------------");
//3번 사원 DELETE
empRepository.delete(3L);
emps = empRepository.findAll();
for(Emp e1 : emps) { System.out.println(e1); }
System.out.println("---------------------");
//4번 사원 INSERT
e = empRepository.save(new Emp(4L, "4길동"));
emps = empRepository.findAll();
for(Emp e1 : emps) { System.out.println(e1); }
System.out.println("---------------------");
//'5길동' 사원 INSERT
Emp e5 = new Emp(“5길동”);
e = empRepository.save(e5);
emps = empRepository.findAll();
for(Emp e1 : emps) { System.out.println(e1); }
}
}
댓글 없음:
댓글 쓰기