지금까지 여러 페이지로 이동되는 매핑과 DB 연결에 대해 알아봤는데
이번에는 사용자가 입력하는대로 DB 를 수정할 수 있게 해보자

참고 https://mybatis.org/mybatis-3/ko/sqlmap-xml.html

MyBatis로 DB 조회/수정

기본 설정

• build.gradle 라이브러리 확인

dependencies {  
    implementation 'javax.servlet:jstl'
    implementation 'org.apache.tomcat.embed:tomcat-embed-jasper'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.3.0'
	compileOnly 'org.projectlombok:lombok'
	developmentOnly 'org.springframework.boot:spring-boot-devtools'
	runtimeOnly 'com.h2database:h2'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
}

스프링부트가 JAR로 패키징 될때 내장된 톰캣은 jsp를 처리할 수 없으므로
tomcat-embed-jasper 의존성을 추가한다.

• application.yml

server:
  port: 8080
  servlet:
    encoding:
      charset: utf-8
      force: true
spring:
  datasource:
    url: jdbc:h2:mem:test;MODE=MySQL
    driver-class-name: org.h2.Driver
    username: sa
    password: 
  mvc:
    view:
      prefix: /WEB-INF/view/
      suffix: .jsp    
  sql:
    init:
      schema-locations:
      - classpath:db/table.sql
      data-locations:
      - classpath:db/data.sql
  h2:
    console:
      enabled: true
  output:
    ansi:
      enabled: always
mybatis:
  mapper-locations:
  - classpath:mapper/**.xml

yml 파일을 보고 다음 설정

• product.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="shop.mtcoding.orange.model.ProductRepository">
   <select id="findAll" resultType="shop.mtcoding.orange.model.Product">
      select * from product order by id desc
   </select>
  
   <select id="findOne" resultType="shop.mtcoding.orange.model.Product">
      select * from product where id = #{id}
   </select>
  
   <insert id="insert">
      insert into product(name, price, qty, createdAt) 
     			   values(#{name},#{price},#{qty}, now())
   </insert>
  
   <delete id="delete" >
      delete from product where id = #{id}
   </delete>
  
   <update id="update">
      update product set name=#{name}, price=#{price}, qty=#{qty} where id = #{id}
   </update>
</mapper>

• table.sql

create table product(
    id int auto_increment primary key,
    name varchar not null unique,
    price int not null,
    qty int not null,
    createdAt Timestamp
);

• data.sql

insert into product(name, price, qty, createdAt) values('바나나', 1000, 50, now());
insert into product(name, price, qty, createdAt) values('사과', 2000, 100, now());
insert into product(name, price, qty, createdAt) values('딸기', 3000, 150, now());
commit;

자바코드

다음은 product.xml 파일을 보고 자바코드 작성

• ProductRepository / ( Product는 생략 )

@Mapper  
public interface ProductRepository {

    public List<Product> findAll();
    public Product findOne(int id);
    public int insert(@Param("name") String name,
    				  @Param("price") int price, @Param("qty") int qty);
    public int delete(@Param("id") int id);
    public int update(@Param("id") int id, @Param("name") String name,
    				  @Param("price") int price, @Param("qty") int qty);
}

• ProductController

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import shop.mtcoding.orange.model.Product;
import shop.mtcoding.orange.model.ProductRepository;

@Controller
public class ProductController {

    @Autowired      
    private ProductRepository productRepository;

    @GetMapping({"/product","/"})
    public String findAll(Model model){
        List<Product> productlist = productRepository.findAll();
        model.addAttribute("productList", productlist);        
        // model -> request.setAttribute("productlist", productlist); 의 기능
        return "product/main";  
    }

    @GetMapping("/product/{id}")
    public String findOne(@PathVariable int id, Model model){
        Product product = productRepository.findOne(id);
        model.addAttribute("product", product);
        return "product/detail";
    }

    @GetMapping("product/addForm")
    public String addForm(){
        return "product/addForm";
    }

    @PostMapping("product/add")
    public String add(String name, Integer price, Integer qty){
        // SRP를 지키자 -> DB 접근은 Repository 가 책임진다 ( 위임 ) 
        // 아래의 기능도 분리시키는게 좋지만 한번에 작성하겠음
        if (name==null||price==null||qty==null)
             return "redirect:/product/addForm"; 
        int result = productRepository.insert(name,price,qty);
        if(result == 1){  // 1개만 추가되게 폼을 만들어 놨으므로
            return "redirect:/product/";
        }else{
            return "redirect:/product/addForm";
        }
    }

    @PostMapping("product/{id}/delete")
    public String delete(@PathVariable int id) {
        int result = productRepository.delete(id);
        if (result == 1){
            return "redirect:/product/";
        }else{
            return "redirect:/product/addForm";
        }
    }

    @GetMapping("product/{id}/update")
    public String update(@PathVariable int id, Model model) {
        Product product = productRepository.findOne(id);
        model.addAttribute("p", product);
        return "product/updateForm";
    }

    @PostMapping("product/{id}/update")
    public String update1(@PathVariable int id, String name, int price, int qty) {
        int result = productRepository.update(id, name, price, qty);
        if ( result == 1){
            return "redirect:/product";
        }else{
            return "redirect:/product/updateForm";
            
        }
    }
}

• jsp 파일에서 사용되는 주요 코드들은

<c:forEach items="${productList}" var="product">
	<tr>
		<td>${product.id}</td>
       	<td><a href="/product/${product.id}">${product.name}</a></td>
        <td>${product.price}</td>
        <td>${product.qty}</td>
        <td>${product.createdAt}</td>
    </tr>
</c:forEach>

 <form action="/product/${product.id}/update" method="get">
     <button type="submit">상품 수정</button>
 </form>
 <form action="/product/${product.id}/delete" method="post">
     <button type="submit">상품 삭제</button>
 </form>
 <form action="/product/${p.id}/update" method="post">
 	 <button type="submit">수정 완료</button>
 </form>

결과는