Spring Boot/JDBC JdbcTemplate/CRUD 예제 오라클자바커뮤니티 (ojc.asia, ojcedu.com)
Spring Boot, Gradle 과오픈소스인 MariaDB 를이용해서 EMP 테이블을만들고 JdbcTemplate, SimpleJdbcTemplate 을이용하여 CRUD 기능을구현해보자. 마리아 DB 설치는다음 URL 에서확인하자. http://ojc.asia/bbs/board.php?bo_table=lecspring&wr_id=524 STS 에서 File -> New -> Project -> Spring Starter Project 다음화면에서 Data -> JDBC 체크 build.gradle 에 MySQL 설정추가 runtime ('mysql:mysql-connector-java') 2
src/main/resources/schema.sql drop database if exists emp; create database emp; 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 insert into emp(ename) values ('1 길동 '); insert into emp(ename) values ('2 길동 '); insert into emp(ename) values ('3 길동 '); MariaDB 에서직접 SQL 을작성하여테이블및데이터를생성할수있지만스프링부트에서는클래스패스경로에 schema.sql, data.sql 이존재하면자동실행하므로스키마생성부분과데이터생성부분을파일로만들어두면된다. 3
src/main/resources/application.properties spring.datasource.platform=mysql spring.datasource.url=jdbc:mysql://localhost/emp?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(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 + "]"; 5
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); 6
Repository 구현체 (EmpRepositoryImpl.java) 영속성서비스용구상클래스 (1) 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; 7
Repository 구현체 (EmpRepositoryImpl.java) 영속성서비스용구상클래스 (2) @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); 8
Repository 구현체 (EmpRepositoryImpl.java) 영속성서비스용구상클래스 (3) @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 ); return emp; emp.getempno(), emp.getename() 9
Repository 구현체 (EmpRepositoryImpl.java) 영속성서비스용구상클래스 (4) @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"); 10
스프링부트메인 (SpringbootjdbcApplication.java) (1) 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 SpringbootjdbcApplication implements CommandLineRunner { public static void main(string[] args) { SpringApplication.run(SpringbootjdbcApplication.class, args); @Autowired EmpRepository emprepository; 11
스프링부트메인 (SpringbootjdbcApplication.java) (2) 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(); e5.setename("5 길동 "); e = emprepository.save(e5); emps = emprepository.findall(); for(emp e1 : emps) { System.out.println(e1); [empno=1,ename=1 길동 ] [empno=2,ename=2 길동 ] [empno=3,ename=3 길동 ] --------------------- [empno=2,ename=2 길동 ] --------------------- [empno=1,ename=1 길동 ] [empno=2,ename=2 길동 ] --------------------- [empno=1,ename=1 길동 ] [empno=2,ename=2 길동 ] [empno=4,ename=4 길동 ] --------------------- [empno=1,ename=1 길동 ] [empno=2,ename=2 길동 ] [empno=4,ename=4 길동 ] [empno=5,ename=5 길동 ] 12