[스프링부트/MariaDB기반 CRUD 실습]스프링5 JDBC JdbcTemplate 클래스,execute, update, b…

[스프링부트/MariaDB기반 CRUD 실습]스프링5 JDBC JdbcTemplate 클래스,execute, update, batchUpdate, query 메소드와 RowMapper, 스프링프레임워크/스프링동영상/자바교육/스프링교육/자바학원/스프링학원

 

JdbcTemplate execute 메소드

 

n  주로 DDL(CREATE, ALTER, DROP) 처리용으로 사용하며 리턴값은 void 형이다.

@Autowired

JdbcTemplate jdbcTemplate;

 

@Override

public void run(String... args) throws Exception {

           jdbcTemplate.execute("DROP TABLE customers");

           jdbcTemplate.execute("CREATE TABLE customers(id number GENERATED AS IDENTITY, firstName VARCHAR(100), lastName VARCHAR2(100))");

}

 

n  SQL 문자열로 부터 PreparedStatement를 생성하고 파라미터를 바인딩 하는 작업을 많이 하므로 JdbcTemplateexecute 메소드를 제공한다.

n  DML에서 사용하는 경우 경우 백그라운드에서 JdbcTemplate PreparedStatement PreparedStatementSetter를 생성 시킨다.

 

public void insertPerson(Person person) {

String sql = “insert into person (id, firstname, lastname) values (?,?,?)”;

 

Object[] params = new Object[] {

                                               person.getId(),

                                               person.getFirstName(),

                                               person.getLastName()

                                          };

return jdbcTemplate.execute(sql, params);

}

 

JdbcTemplate update 메소드

 

n  execute에 비해 NULL setting할때 이점이 있다.(TYPE을 인자로 받아들임)

n  주로 DML 처리용(insert, update, delete)으로 파라미터는 가변인자나 객체배열 형태로 제공하면 된다.

 int update(String sql)

 int update(String sql, Object... args)

 int update(String sql, Object[] args)

 

n  SQL문의 실행이 성공하면 추가 / 변경 / 삭제 된 행 수를 리턴한다.

n  아래 SQL문 실행시 백그라운드에서 JdbcTemplate PreparedStatement PreparedStatementSetter를 생성 시킨다.

public int insertPerson(Person person) {

String sql = “insert into person (id, firstname, lastname) values (?,?,?)”;

 

Object[] params = new Object[] {

                                               person.getId(),

                                               person.getFirstName(),

                                               person.getLastName()

                                          };

int[] types = new int[] { TYPES.INTEGER, TYPES.VARCHAR, TYPES.VARCHAR };

 

return jdbcTemplate.update(sql, params, types);

}

 

JdbcTemplate batchUpdate 메소드

 

n  갱신하고자 하는 레코드가 2건 이상인 경우에 사용하며, BatchPreparedStatementSetter를 만들어 인자로 넣어준다.

setValues(PreparedStatemet ps, int i) throws SQLException;  //파라미터 바인딩

int getBatchSize();  //실행시킬 SQL문의 수를 알려준다. setValues의 호출 수 지정.

 

[예문1]

public int updatePersons(final List persons) {

     String sql = “insert into person (id, firstname, lastname) values (?,?,?)”;

BatchPreparedStatementSetter setter = null;

setter = new BatchPreparedStatementSetter() {   //Anonymous Class(익명클래스)

public int getBatchSize() {

   return persons.size();

}

 

public void  setValues(PreparedStatement ps, int index)

throws SQLException {

Person person = (Person)persons.get(index);

int parameterIndex = 1;

ps.setString(parameterIndex++, person.getId());

ps.setString(parameterIndex++, person.getFirstName());

ps.setString(parameterIndex, person.getLastName());

}

};

return this.jdbcTemplate.batchUpdate(sql, setter);  //질의문 실행

}//:

 

[예문2]

public void insertBatch(final List persons){

    String sql = “insert into person (id, firstname, lastname) values (?,?,?)”;

    List<Object[]> parameters = new ArrayList<Object[]>();

 

    for (Person person: persons) {

      parameters.add(new Object[] { person.getId(), person.getFirstName(), person.getLastName()} );

    }

this.jdbcTemplate.batchUpdate(sql, parameters);  //질의문 실행

}

 

JdbcTemplate query 메소드와 RowMapper 인터페이스

 

n  RowMapper 인터페이스

여러 건의 레코드(여러 Person 객체)를 얻을 수 있는 메소드가 필요하다면  RowMapper를 구현해서 데이터를 추출할 수 있는데 RowMapper ResultSet“SELECT된 레코드와 객체를 매핑시키는 역할을 한다.

 

//레코드(, 로우)를 매핑할 PersonRowMapper RowMapper인터페이스를 구현해서 만든다.

public class PersonRowMapper implements RowMapper {

public Object mapRow(ResultSet rs, int index) throws SQLException {

Person person = new Person();

person.setId(new Integer(rs.getInt("id")));

person.setFirstName(rs.getString(“firstname"));

person.setLastName(rs.getString(“lastname"));

return person;

}

}

 

…………

 

public List getAllPersons() { 

    String sql = “select id, firstname, lastname from person”;

    return jdbcTemplate.query( sql, new PersonRowMapper());  //queryForList

}

}

 

//아이디를 통해 person 객체를 얻어오는 메소드

public Person getPerson(Integer id) {

        String sql = “select id, firstname, lastname from person where id =? “;

  Person person = (Person)jdbcTemplate.query(sql, id, new PersonRowMapper());

  return person;

}//:

 

 

 

 

 

 

JdbcTemplate queryForObject, queryForList, 기타 query 메소드

 

n  queryForObject : 한 개의 레코드 처리용, 객체타입으로 결과를 리턴해 준다.

public Person getLastNameById(Integer id) {

String sql = "SELECT lastname FROM person WHERE id = ? ";

Object[] args = new Object[] { id };  //?에 대입되는 매개변수

 

String lastname=(String)this.jdbcTemplate.queryForObject(sql, args, String.class);

return lastname;

}

 

n  queryForList : 한 개 이상의 레코드 처리용, 여러건의 레코드를 List 객체로 리턴해 준다.

public List<Person> getAllPerson() {

String sql = "SELECT * FROM person ";

List<Person> persons = this.jdbcTemplate.queryForList(sql, new PersonRowMapper());

return persons;

}

 

n  기타 query 메소드

//queryForInt, queryForLong은 더 이상 사용되지 않는다.

String sql = "SELECT count(*) FROM person WHERE id = ?";

int count = jdbcTemplate.queryForObject(sql, new Object[] { id }, Integer.class);

                                

String sql = "SELECT longCol FROM person WHERE id = ?";

long col = jdbcTemplate.queryForObject(sql, new Object[] { id }, Long.class);}

 

// 아래 double형이나 date형은 queryForObject를 적절히 사용하면 된다.

String sql = " SELECT doubleCol FROM Table ";

Double d  = (Double)this.jdbcTemplate.queryForObject(sql, Double.class);

 

String sql = " SELECT dateCol FROM Table ";

Date d  = (Date)this.jdbcTemplate.queryForObject(sql, Date.class);

 

 

[JdbcTemplate 이용한 CRUD 예제]

 

n  Spring Boot, MariaDB를 이용해서  EMP 테이블을 만들고 JdbcTemplate을 이용하여 CRUD 기능을 구현해 보자.

 

STS에서

 

File -> New  -> Project  -> Spring Starter Project

Name : jdbc2

Package : jdbc

 

다음화면에서 SQL : JDBC API, MySQL Driver 선택

 

 

[pom.xml]

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

       xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">

       <modelVersion>4.0.0</modelVersion>

       <parent>

             <groupId>org.springframework.boot</groupId>

             <artifactId>spring-boot-starter-parent</artifactId>

             <version>2.3.4.RELEASE</version>

             <relativePath/> <!-- lookup parent from repository -->

       </parent>

       <groupId>com.example</groupId>

       <artifactId>jdbc2</artifactId>

       <version>0.0.1-SNAPSHOT</version>

       <name>jdbc2</name>

       <description>Demo project for Spring Boot, JdbcTemplate</description>

 

       <properties>

             <java.version>1.8</java.version>

       </properties>

 

       <dependencies>

             <dependency>

                    <groupId>org.springframework.boot</groupId>

                    <artifactId>spring-boot-starter-jdbc</artifactId>

             </dependency>

 

             <dependency>

                    <groupId>mysql</groupId>

                    <artifactId>mysql-connector-java</artifactId>

                    <scope>runtime</scope>

             </dependency>

             <dependency>

                    <groupId>org.springframework.boot</groupId>

                    <artifactId>spring-boot-starter-test</artifactId>

                    <scope>test</scope>

                    <exclusions>

                           <exclusion>

                                 <groupId>org.junit.vintage</groupId>

                                 <artifactId>junit-vintage-engine</artifactId>

                           </exclusion>

                    </exclusions>

             </dependency>

       </dependencies>

 

       <build>

             <plugins>

                    <plugin>

                           <groupId>org.springframework.boot</groupId>

                           <artifactId>spring-boot-maven-plugin</artifactId>

                    </plugin>

             </plugins>

       </build>

 

</project>

 

[src/main/resources/schema.sql]

drop table if exists emp;

create table emp

(

           empno int(4) not null auto_increment,

           ename varchar(50),      

           sal   int(15),

           primary key (empno)

) ENGINE=InnoDB;

 

[src/main/resources/data.sql(파일 속성에서 text encoding UTF-8)]

insert into emp(ename, sal) values ('1길동', 1000000);

insert into emp(ename, sal) values ('2길동', 2000000);

insert into emp(ename, sal) values ('3길동', 3000000);

 

[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=mariadb

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.sql-script-encoding=UTF-8

spring.datasource.initialization-mode=always

 

도메인 클래스(Emp.java) – 테이블구조와 동일하다.

package jdbc;

 

public class Emp {

       private long empno;

       private String ename;

       private int sal;

      

       public Emp(long empno, String ename, int sal) {

             super();

             this.empno = empno;

             this.ename = ename;

             this.sal = sal;

       }

      

       @Override

       public String toString() {

             return "Emp [empno=" + empno + ", ename=" + ename + ", sal=" + sal + "]";

       }

      

       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 int getSal() {

             return sal;

       }

       public void setSal(int sal) {

             this.sal = sal;

       }     

}

 

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;

 

@Component

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");

int sal = rs.getInt("sal");

                     

                      return new Emp(empno, ename, sal);

           }

}

 

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);

           int[] batchInsert(List<Object[]> params);

}

 

Repository 구현체(EmpRepositoryImpl.java) – 영속성 서비스용 구상클래스

package jdbc.repository;

 

import java.util.List;

 

import javax.annotation.PostConstruct;

 

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 {

       @Autowired

       private JdbcTemplate jdbcTemplate;

      

       @Autowired

       RowMapper<Emp> empRowMapper;

      

       @Override

       public List<Emp> findAll() {

             List<Emp> emps = jdbcTemplate.query("select empno, ename, sal from emp",empRowMapper);

             return emps;

       }

      

       @Override

       public Emp findOne(Long empno) {

             return (Emp)jdbcTemplate.queryForObject("select empno, ename, sal from emp where empno = ?", empRowMapper, empno);

       }

 

       @Override

       @Transactional(readOnly=false)

       public Emp save(Emp emp) {

             this.jdbcTemplate.update("insert into emp (empno, ename, sal) values (?, ?, ?)"

                                            ,emp.getEmpno(), emp.getEname(), emp.getSal());

             return emp;        

       }

 

       @Override

       @Transactional(readOnly=false)

       public void delete(Long empno) {

             this.jdbcTemplate.update("delete from emp where empno = ?",    empno);

       }

 

       @Override

       @Transactional(readOnly=false)

       public int[] batchInsert(List<Object[]> params) {

             return this.jdbcTemplate.batchUpdate("insert into emp (empno, ename, sal) values (?, ?, ?)",params);

       }

}

 

스프링 부트 메인(Jdbc2Application.java)

package jdbc;

 

import java.util.ArrayList;

import java.util.List;

import java.util.stream.IntStream;

 

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;

 

           @Override

           public void run(String... args) throws Exception {

                      // 전체 사원 SELECT

                      List<Emp> emps = empRepository.findAll();

                      for (Emp e : emps) {

                                 System.out.println(e);

                      }

                      System.out.println("전체사원 SELECT ---------------------");

 

                      // 2번 사원 SELECT

                      Emp e = empRepository.findOne(2L);

                      System.out.println(e);

                      System.out.println("2번 사원 SELECT ---------------------");

 

                      // 3번 사원 DELETE 후 전체출력

                      empRepository.delete(3L);

                      emps = empRepository.findAll();

                      for (Emp e1 : emps) {

                                 System.out.println(e1);

                      }

                      System.out.println("3번 사원 DELETE 후 전체출력---------------------");

 

                      // 4번 사원 INSERT

                      e = empRepository.save(new Emp(4L, "4길동", 4000000));

                      emps = empRepository.findAll();

                      for (Emp e1 : emps) {

                                 System.out.println(e1);

                      }

                      System.out.println("4번 사원 INSERT ---------------------");

                     

                      //5,6번 사원 배치인서트

                      List<Object[]> params = new ArrayList<Object[]>();

                      params.add(new Object[] {5L, "5길동", 5000000});

                      params.add(new Object[] {6L, "6길동", 6000000});

                      int[] ret = empRepository.batchInsert(params);

                      System.out.println(IntStream.of(ret).sum() + "건 입력OK!");

                     

                      emps = empRepository.findAll();

                      for (Emp e1 : emps) {

                                 System.out.println(e1);

                      }

                      System.out.println("5,6번 사원 배치인서트 후---------------------");

           }

}         

 

[실행결과]

Emp [empno=1, ename=1길동, sal=1000000]

Emp [empno=2, ename=2길동, sal=2000000]

Emp [empno=3, ename=3길동, sal=3000000]

전체사원 SELECT ---------------------

Emp [empno=2, ename=2길동, sal=2000000]

2번 사원 SELECT ---------------------

Emp [empno=1, ename=1길동, sal=1000000]

Emp [empno=2, ename=2길동, sal=2000000]

3번 사원 DELETE 후 전체출력---------------------

Emp [empno=1, ename=1길동, sal=1000000]

Emp [empno=2, ename=2길동, sal=2000000]

Emp [empno=4, ename=4길동, sal=4000000]

4번 사원 INSERT ---------------------

2건 입력OK!

Emp [empno=1, ename=1길동, sal=1000000]

Emp [empno=2, ename=2길동, sal=2000000]

Emp [empno=4, ename=4길동, sal=4000000]

Emp [empno=5, ename=5길동, sal=5000000]

Emp [empno=6, ename=6길동, sal=6000000]

5,6번 사원 배치인서트 후---------------------

 

 

 

 

 

#스프링jdbc, #JdbcTemplate, #Springjdbc, #자바스프링, #Spring동영상, #Spring강의, #스프링프레임워크, #스프링교육, #스프링학원, #스프링강좌, #스프링강의, #자바학원, #자바, #스프링동영상, #자바동영상, #스프링프레임워크교육, #스프링프레임워크강의, #스프링프레임워크학원  

, , , , , , , , , , , , , , , , ,

Comments