[MySQL] 실습 3~5 가이드라인

실습 3: 도서 대출 및 반납 트랜잭션

실습 3은 Spring의 @Transactional을 사용하여 여러 SQL 작업을 하나의 트랜잭션으로 묶는 것입니다. Mapper에서는 각 SQL 쿼리를 정의합니다.

BookMapper.java (MyBatis Annotation)

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.annotations.Insert;

@Mapper
public interface BookMapper {

    // 1. 책 재고 감소 (대출용)
    @Update("UPDATE books SET stock = stock - 1 WHERE id = #{bookId} AND stock > 0")
    int decreaseStock(@Param("bookId") int bookId);

    // 2. 대출 기록 추가
    @Insert("INSERT INTO rentals (book_id, member) VALUES (#{bookId}, #{member})")
    int createRental(@Param("bookId") int bookId, @Param("member") String member);

    // 3. 책 재고 증가 (반납용)
    @Update("UPDATE books SET stock = stock + 1 WHERE id = #{bookId}")
    int increaseStock(@Param("bookId") int bookId);

    // 4. 대출 기록 업데이트 (반납용)
    @Update("UPDATE rentals SET return_date = NOW() WHERE book_id = #{bookId} AND member = #{member} AND return_date IS NULL")
    int updateRental(@Param("bookId") int bookId, @Param("member") String member);
}

BookService.java

@Transactional 어노테이션을 서비스 메소드에 추가하여, 메소드 내의 모든 데이터베이스 작업이 성공적으로 완료되어야만 최종 커밋됩니다. 하나라도 실패하면 모든 작업이 롤백됩니다.

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class BookService {

    private final BookMapper bookMapper;

    public BookService(BookMapper bookMapper) {
        this.bookMapper = bookMapper;
    }

    @Transactional
    public void rentBook(int bookId, String member) {
        // 1. 재고 감소
        int updatedRows = bookMapper.decreaseStock(bookId);
        if (updatedRows == 0) {
            throw new RuntimeException("재고가 부족합니다.");
        }

        // 2. 대출 기록 추가
        int insertedRows = bookMapper.createRental(bookId, member);
        if (insertedRows == 0) {
            throw new RuntimeException("대출 기록 추가에 실패했습니다.");
        }
    }

    @Transactional
    public void returnBook(int bookId, String member) {
        // 1. 재고 증가
        int updatedRows = bookMapper.increaseStock(bookId);
        if (updatedRows == 0) {
            throw new RuntimeException("해당하는 책이 없습니다.");
        }

        // 2. 대출 기록 업데이트
        int updatedRentalRows = bookMapper.updateRental(bookId, member);
        if (updatedRentalRows == 0) {
            throw new RuntimeException("반납 처리 중 오류가 발생했습니다. (대출 기록이 없거나 이미 반납됨)");
        }
    }
}

---

실습 4: 도서 등록 및 검색 XML 만들기

실습 4는 MyBatis XML 매퍼를 사용하여 동적 SQL을 작성하는 것입니다.

BookMapper.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.example.yourpackage.BookMapper">

  <!-- 조건2: 책 단일 등록 (등록된 도서번호 반환) -->
  <insert id="registerBook" parameterType="map" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO books (title, stock)
    VALUES (#{title}, #{stock})
  </insert>

  <!-- 조건3: 대출 기록 검색 (동적 쿼리) -->
  <select id="searchRentals" parameterType="map" resultType="map">
    SELECT r.id, r.book_id, b.title, r.member, r.rent_date, r.return_date
    FROM rentals r
    JOIN books b ON r.book_id = b.id
    <where>
      <if test="member != null and member != ''">
        r.member LIKE CONCAT('%', #{member}, '%')
      </if>
      <if test="title != null and title != ''">
        AND b.title LIKE CONCAT('%', #{title}, '%')
      </if>
    </where>
  </select>

  <!-- 조건4: 책 일괄 등록 (동적 쿼리) -->
  <insert id="registerBooks" parameterType="list" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO books (title, stock)
    VALUES
    <foreach collection="list" item="book" separator=",">
      (#{book.title}, #{book.stock})
    </foreach>
  </insert>

</mapper>

설명:

• useGeneratedKeys와 keyProperty: INSERT 후 자동 생성된 id 값을 파라미터 객체의 id 프로퍼티에 설정해줍니다.
• <where>와 <if>: 동적 쿼리를 생성합니다. <where> 태그는 내부 조건이 하나라도 있으면 WHERE 키워드를 추가하고, AND나 OR로 시작하는 경우 자동으로 제거해줍니다.
• <foreach>: 리스트 형태의 파라미터를 반복하여 여러 개의 값을 한 번에 INSERT 할 수 있습니다.
  • collection="list": 파라미터가 List 타입일 경우 list로 지정합니다.
  • item="book": 반복 중인 각 항목을 가리키는 변수 이름입니다.
  • separator=",": 각 항목 사이에 쉼표(,)를 구분자로 추가합니다.

---

실습 5: 도서 테이블 수정 및 서브쿼리 조회 XML 만들기

실습 5는 ALTER TABLE을 사용하여 테이블 구조를 변경하고, 서브쿼리를 사용하여 데이터를 조회하는 방법을 다룹니다.

BookMapper.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.example.yourpackage.BookMapper">

  <!-- 조건2-1: books 테이블에 price 필드 추가 -->
  <update id="addPriceColumn">
    ALTER TABLE books ADD COLUMN price INT NOT NULL DEFAULT 0
  </update>

  <!-- 조건2-2: books 테이블의 title 필드 타입 수정 -->
  <update id="changeTitleColumnType">
    ALTER TABLE books MODIFY COLUMN title LONGTEXT NOT NULL
  </update>

  <!-- 조건3-1: 평균 재고보다 많은 재고를 가진 도서 조회 -->
  <select id="findBooksWithStockAboveAverage" resultType="com.example.yourpackage.Book">
    SELECT *
    FROM books
    WHERE stock > (SELECT AVG(stock) FROM books)
  </select>

  <!-- 조건3-2: 가장 많이 대출한 도서 조회 -->
  <select id="findMostRentedBook" resultType="com.example.yourpackage.Book">
    SELECT b.*
    FROM books b
    JOIN (
        SELECT book_id, COUNT(*) as rental_count
        FROM rentals
        GROUP BY book_id
        ORDER BY rental_count DESC
        LIMIT 1
    ) as top_rented ON b.id = top_rented.book_id
  </select>

</mapper>

설명:

• ALTER TABLE: ADD COLUMN으로 새 열을 추가하거나 MODIFY COLUMN으로 기존 열의 타입을 변경할 수 있습니다.
• 서브쿼리 (Subquery):
  • WHERE stock > (SELECT AVG(stock) FROM books): WHERE 절에서 서브쿼리를 사용하여 평균 재고를 계산하고, 그보다 재고가 많은 책을 찾습니다.
  • JOIN ( ... ): FROM 절에서 서브쿼리(인라인 뷰)를 사용하여 가장 많이 대출된 book_id를 찾고, 그 결과를 books 테이블과 조인하여 해당 도서의 전체 정보를 가져옵니다.