스프링 부트 - MyBatis 2 / xml 매핑

.

 

두 번째 방법 

장문의 sql문을 작성할 때는 xml로도 사용 가능하다. 

DROP TABLE IF EXISTS Products;

CREATE TABLE Products
(
    prod_id     INT     		PRIMARY KEY AUTO_INCREMENT,
    prod_name   VARCHAR(255)    NOT NULL,
    prod_price  INT             NOT NULL
);

INSERT INTO Products (prod_name, prod_price) values ('베베숲 물티슈', 2700);
INSERT INTO Products (prod_name, prod_price) values ('여름 토퍼', 35180);
INSERT INTO Products (prod_name, prod_price) values ('페이크 삭스', 860);
INSERT INTO Products (prod_name, prod_price) values ('우산', 2900);

 

 

 

 

 

 

 

 

 

package com.myapp.mybatis.model;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Getter
@Setter
@AllArgsConstructor //전체 생성자
@NoArgsConstructor //기본 생성자
public class Product {
	
	private Long prodId;
	private String prodName;
	private int prodPrice;
}

 

 

db에서는 _바뀜 

prod_id

 

 

 

# MyBatis
# mapper.xml 위치 지정
mybatis.mapper-locations= mapper/*.xml

# model 프로퍼티 camel case 설정
mybatis.configuration.map-underscore-to-camel-case=true

# 패키지 result tpye 을 생략할 수 있도록 alias 설정
mybatis.type-aliases-package=com.myapp.mybatis.model

# mapper 로그레벨 설정(trace -> 자세한 로그)
logging.level.com.myapp.mybatis.mapper=TRACE

 

result type 은 mapper.xml에서 작성할 때 아래와 같은 result type을 말한다. 

 

 

 

 

mapper 폴더 생성

 

에러나는 이유는 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">

 

 

 

 

인터페이스로 메소드를 만들어주고 해당하는 sql문은 xml에 작성한다. 

package com.myapp.mybatis.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import com.myapp.mybatis.model.Product;

@Mapper
public interface ProductMapper {

	Product selectProductById(Long id);
       List<Product> selectAllProducts();
       void insertProduct(Product product);
       void updateProduct(Product product);
       void deleteProductById(Long id);
}

 

 

 

 

ProductMapper.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="com.myapp.mybatis.mapper.ProductMapper">

	<select id="selectProductById" resultType="Product">
        SELECT prod_id
              ,prod_name
              ,prod_price
        FROM products
        WHERE prod_id = #{prodId}
	</select>
	
	    <select id="selectAllProducts" resultType="Product">
        SELECT prod_id
              ,prod_name
              ,prod_price
        FROM products
    </select>

    <insert id="insertProduct" parameterType="Product">
      INSERT INTO products (prod_name, prod_price)
      VALUES (#{prodName}, #{prodPrice})
    </insert>

	<update id="updateProduct" parameterType="Product">
		UPDATE products
		SET prod_name =#{prodName},
			prod_price=#{prodPrice}
		WHERE prod_id=#{prodId}
	</update>
	
	<delete id="deleteProductById" parameterType="Long">
		DELETE FROM products
		WHERE  prod_id=#{prodId}
	</delete>
</mapper>

 

 

 

ProductController

package com.myapp.mybatis.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import com.myapp.mybatis.mapper.ProductMapper;
import com.myapp.mybatis.model.Product;

@RestController // 화면없이 데이터만 전달
@RequestMapping("/products")
public class ProductController {

	@Autowired
	private ProductMapper productMapper;

	@GetMapping("/{id}")
	public Product getUser(@PathVariable("id") Long id) {
		Product product = productMapper.selectProductById(id);
		return product;
	}

	@GetMapping
	public List<Product> getProductList() {
		List<Product> productList = productMapper.selectAllProducts();
		return productList;
	}

	@PostMapping
	public void createProduct(
			@RequestParam("name") String prodName,
			@RequestParam("price") int prodPrice) {
		Product product = new Product();
		product.setProdName(prodName);
		product.setProdPrice(prodPrice);
		productMapper.insertProduct(product);
	}


	@PutMapping("/{id}")
	public void editProduct(
			@PathVariable("id") Long prodId, 
			@RequestParam("name") String prodName,
			@RequestParam("price") int prodPrice) {
		Product product = new Product(prodId,prodName,prodPrice);
		productMapper.updateProduct(product);
	}
	
	@DeleteMapping("/{id}")
	public void deleteProduct(@PathVariable("id") Long id) {
		productMapper.deleteProductById(id);
	}
}

 

 

테스트하기

 

제품 검색하기 

 

 

 

 

 

모든 제품 검색하기 

 

 

 

 

 

 

제품 입력하기 

 

http://localhost:8080/products?name=노트&price=700000

 

DB확인

 

 

 

 

 

제품 수정하기 

 

http://localhost:8080/products/5?name=노트북&price=800000

 

 

 

 

 

 

제품 삭제하기