[Spring Boot 실습 #13] MyBatis CRUD - H2 Database

[Spring Boot 실습 #13] MyBatis CRUD - H2 Database

 

1. Spring Boot Mybatis 설정 및 기본 CRUD (H2 Database)

2. 개발환경

    2.1. 개발환경

    2.2. 라이브러리

3. 구현

    3.1. 프로젝트 구조

    3.2. 구현 - 소스코드

4. 결과

    4.1. 테이블 생성

    4.2. save

    4.3. findAll, findById

    4.4. update

    4.5. delete

 

 

1. 임의의 http status code 반환

http 호출로 에러 발생시 정상 범위로 판단하여 임의의 http status code 반환

 

2. 개발환경

 

2.1. 개발환경

MacOS M1 - macOS Monterey 12.0.1

IntelliJ IDEA 2021.2 (Community Edition)

 

2.2. 라이브러리

JDK 11

spring-boot-2.6.6

 

 

3. 구현

3.1. 프로젝트 구조

 

 

3.2. 구현 - 소스코드

 

[ 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.7.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>io.home.test</groupId>
    <artifactId>spring-boot-mybatis-starter</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>spring-boot-mybatis-starter</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
 
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
 
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
 
</project>

 

 

[ mybatis-config.xml ]

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
 
<configuration>
    <typeAliases>
        <package name="io.home.test.app.product.domain"/>
    </typeAliases>
</configuration>

 

 

[ 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="io.home.test.app.product.mapper.ProductMapper">
 
    <select id="findAll" resultType="io.home.test.app.product.domain.Product">
        select * from TB_PRODUCT;
    </select>
 
    <select id="findById" resultType="io.home.test.app.product.domain.Product">
        select * from TB_PRODUCT where id = #{id};
    </select>
 
    <insert id="save" parameterType="io.home.test.app.product.domain.Product">
        INSERT INTO TB_PRODUCT (
            name,
            description,
            price,
            record_state,
            status,
            created_date,
            updated_date
        )
        VALUES (
            #{name},
            #{description},
            #{price},
            #{recordState},
            #{status},
            #{createdDate},
            #{updatedDate}
        )
    </insert>
 
    <update id="update" parameterType="io.home.test.app.product.domain.Product">
        UPDATE
            TB_PRODUCT
        SET
            <if test="name!=null">name=#{name}</if>
            <if test="description!=null">,description=#{description}</if>
            <if test="price!=null">,price=#{price}</if>
            <if test="recordState!=null">,record_state=#{recordState}</if>
            <if test="status!=null">,status=#{status}</if>
            <if test="createdDate!=null">,created_date=#{createdDate}</if>
            <if test="updatedDate!=null">,updated_date=#{updatedDate}</if>
        WHERE
            id=#{id};
    </update>
 
    <delete id="deleteById">
       delete from TB_PRODUCT where id = #{id};
    </delete>
 
</mapper>

 

 

[ init_ddl.sql ] 테이블 생성용 sql

-- tb_product
CREATE TABLE IF NOT EXISTS `TB_PRODUCT`(
    id              bigint auto_increment primary key           comment 'id',
--  product_token   varchar(36)  not null                       comment 'product token',
    name            varchar(255) not null                       comment 'product name',
    description     varchar(255) null                           comment 'product description',
    status          varchar(30)  not null default 'PREPARED'    comment 'status',
    record_state    varchar(30)  not null default 'ACTIVE'      comment 'record_state',
    price           bigint default 0                            comment 'product price',
    created_date    datetime(6) null                            comment 'created date',
    updated_date    datetime(6) null                            comment 'updated date'
);
 
-- create
-- index tb_product_idx01 on tb_product (product_token);
 
create
index tb_product_idx02 on tb_product (created_date);
 
create
index tb_product_idx03 on tb_product (updated_date);

 

 

[ application.properties ]

server.port=8080
# ===============================
# = SPRING JPA
# ===============================
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none
#spring.jpa.generate-ddl=true
#spring.jpa.hibernate.ddl-auto=update
 
# ===============================
# = SPRING H2
# ===============================
spring.h2.console.enabled=true
spring.h2.console.settings.web-allow-others=true
 
# ===============================
# = DATA SOURCE
# ===============================
# Set here configurations for the database connection
spring.datasource.platform=h2
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.url=jdbc:h2:file:./target/h2db/db/app;DB_CLOSE_DELAY=-1
spring.datasource.username=tester
spring.datasource.password=
 
# Keep the connection alive if idle for a long time (needed in production)
spring.datasource.testWhileIdle=true
spring.datasource.validationQuery=SELECT 1
 
# jndi
#spring.datasource.jndi-name=jdbc/test
 
# ===============================
# = MYBATIS
# ===============================
# MyBatis
# mapper.xml
mybatis.mapper-locations=mappers/**/*.xml
# model camel case
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.configuration.config-location=classpath:mybatis-config.xml

 

 

[ io.home.test.app.product.domain.Product.java ] Product 모델

package io.home.test.app.product.domain;
 
import io.home.test.base.entity.RecordState;
import org.hibernate.annotations.CreationTimestamp;
import org.hibernate.annotations.UpdateTimestamp;
import org.springframework.data.relational.core.mapping.Table;
 
import javax.persistence.*;
import java.math.BigDecimal;
import java.time.ZonedDateTime;
 
@Entity
//@Table(name = "tb_product")
public class Product {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String description;
    private BigDecimal price;
 
    @CreationTimestamp
    @Column(name = "created_date", updatable = false)
    private ZonedDateTime createdDate = ZonedDateTime.now();
 
    @UpdateTimestamp
    @Column(name = "updated_date", updatable = true)
    private ZonedDateTime updatedDate;
 
    @Enumerated
    @Column(name = "record_state")
    private RecordState recordState = RecordState.ACTIVE;
 
    @Enumerated
    private Status status = Status.PREPARE;
 
    public enum Status {
        PREPARE("prepare"),
        ON_SALE("on sale"),
        OUT_OF_STOCK("out of stock"),
        END_OF_SALE("end of sale");
 
        private final String desc;
 
        Status(String desc) {
            this.desc = desc;
        }
 
        public String getDesc() {
            return desc;
        }
    }
 
    public Product() {
    }
 
    public Product(Long id, String name, BigDecimal price) {
        this.id = id;
        this.name = name;
        this.price = price;
    }
 
    public Long getId() {
        return id;
    }
 
    public void setId(Long id) {
        this.id = id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public String getDescription() {
        return description;
    }
 
    public void setDescription(String description) {
        this.description = description;
    }
 
    public BigDecimal getPrice() {
        return price;
    }
 
    public void setPrice(BigDecimal price) {
        this.price = price;
    }
 
    public ZonedDateTime getCreatedDate() {
        return createdDate;
    }
 
    public void setCreatedDate(ZonedDateTime createdDate) {
        this.createdDate = createdDate;
    }
 
    public ZonedDateTime getUpdatedDate() {
        return updatedDate;
    }
 
    public void setUpdatedDate(ZonedDateTime updatedDate) {
        this.updatedDate = updatedDate;
    }
 
    public Status getStatus() {
        return status;
    }
 
    public void setStatus(Status status) {
        this.status = status;
    }
 
    public RecordState getRecordState() {
        return recordState;
    }
 
    public void setRecordState(RecordState recordState) {
        this.recordState = recordState;
    }
}

 

 

[ io.home.test.app.product.mapper.ProductMapper.java ] mybatis의 productMapper.xml과 연결

package io.home.test.app.product.mapper;
 
import io.home.test.app.product.domain.Product;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;
 
import java.util.List;
 
@Repository
public interface ProductMapper {
 
    List<Product> findAll();
 
    Product findById(Long id);
 
    void save(Product product);
 
    void update(Product product);
 
    void deleteById(Long id);
 
//    @Select("SELECT CURRENT_TIMESTAMP result FROM sysibm.sysdummy1")    // db2
    @Select("SELECT CURRENT_TIMESTAMP FROM DUAL")    // H2
    String now() throws Exception;
 
}

 

 

[ io.home.test.app.product.service.ProductService.java ]

package io.home.test.app.product.service;
 
import io.home.test.app.product.domain.Product;
import io.home.test.app.product.mapper.ProductMapper;
import org.springframework.stereotype.Service;
 
import java.util.List;
 
@Service
public class ProductService {
 
    private final ProductMapper productMapper;
 
    public ProductService(ProductMapper productMapper) {
        this.productMapper = productMapper;
    }
 
    public List<Product> findAll() {
        return productMapper.findAll();
    }
 
    public Product findById(Long id) {
        return productMapper.findById(id);
    }
 
    public void save(Product product) {
        productMapper.save(product);
    }
 
    public void update(Product product) {
        productMapper.update(product);
    }
 
    public void deleteById(Long id) {
        productMapper.deleteById(id);
    }
 
    public String now() throws Exception {
        return productMapper.now();
    }
 
}

 

 

[ io.home.test.app.product.web.ProductResource.java ]

package io.home.test.app.product.web;
 
import io.home.test.app.product.domain.Product;
import io.home.test.app.product.service.ProductService;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
 
import java.math.BigDecimal;
import java.time.ZonedDateTime;
import java.util.List;
 
@RequestMapping(value = "/api/products")
@RestController
public class ProductResource {
 
    private final ProductService productService;
 
    public ProductResource(ProductService productService) {
        this.productService = productService;
    }
 
    @GetMapping(value = "")
    public ResponseEntity<List<Product>> findAllProducts() {
        List<Product> products = productService.findAll();
        return ResponseEntity.ok(products);
    }
 
    @GetMapping(value = "/{id}")
    public ResponseEntity<Product> findById(@PathVariable(value = "id") Long id) {
        Product product = productService.findById(id);
        return ResponseEntity.ok(product);
    }
 
    @GetMapping(value = "/test/save")
    public ResponseEntity<?> testSave() {
        Product product = new Product();
        product.setName("test-product");
        product.setPrice(BigDecimal.valueOf(15000L));
        productService.save(product);
        return ResponseEntity.ok("saved");
    }
 
    @GetMapping(value = "/test/delete")
    public ResponseEntity<?> testDelete(@RequestParam(value = "id") long id) {
        productService.deleteById(id);
        return ResponseEntity.ok("deleted");
    }
 
    @GetMapping(value = "/test/update")
    public ResponseEntity<?> testUpdate(@RequestParam(value = "id") long id) {
        Product oldProduct = productService.findById(id);
 
        oldProduct.setName("new Name");
        oldProduct.setDescription("new Description");
        oldProduct.setUpdatedDate(ZonedDateTime.now());
 
        productService.update(oldProduct);
        return ResponseEntity.ok("updated");
    }
 
    @GetMapping(value = "/now")
    public ResponseEntity<?> now() throws Exception {
        String now = productService.now();
        return ResponseEntity.ok(now);
    }
}

 

 

[ io.home.test.base.entity.RecordState.java ]

package io.home.test.base.entity;
 
public enum RecordState {
    ACTIVE,
    UPDATING,
    DELETED
}

 

 

[ io.home.test.SpringBootMybatisStarterApplication.java ] @MapperScan 추가

package io.home.test;
 
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
 
@MapperScan(basePackageClasses = SpringBootMybatisStarterApplication.class)
@SpringBootApplication
public class SpringBootMybatisStarterApplication {
 
    public static void main(String[] args) {
        SpringApplication.run(SpringBootMybatisStarterApplication.class, args);
    }
 
}

 

 

4. 결과

4.1. 테이블 생성

H2 console 접근

 

테이블 생성

 

4.2. save

save

http://localhost:8080/api/products/test/save

 

 

4.3. findAll, findById

findAll

http://localhost:8080/api/products

findById

http://localhost:8080/api/products/{id}

 

4.4. update

update

http://localhost:8080/api/products/test/update?id={id}

 

findById

http://localhost:8080/api/products/{id}

 

 

4.5. delete