0725 학습 요약

✅ MyBatis 설정

mybatis:
  mapper-locations: classpath:mappers/**/*.xml
  configuration:
    map-underscore-to-camel-case: true
  type-aliases-package: com.spring.database

✅ Pet Entity 클래스

@Getter
@Setter
@ToString
@EqualsAndHashCode
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Pet {
    private Long id;
    private String petName;
    private int petAge;
    private boolean injection;
}

✅ PetMapper 인터페이스 (CRUD 명세)

@Mapper
public interface PetMapper {
    boolean save(Pet pet);              // CREATE
    Pet findById(Long id);              // READ 단건
    List<Pet> findAll();                // READ 전체
    boolean update(Pet pet);            // UPDATE
    boolean deleteById(Long id);        // DELETE
    int petCount();                     // COUNT
}

✅ PetMapper 테스트 코드

• saveTest(), updateTest(), deleteTest(), findAllTest(), findOneTest(), countTest() 각각의 CRUD 동작을 검증

✅ MyBatis XML 매핑 설정

<mapper namespace="com.spring.database.chap03.PetMapper">
  <insert id="save">
    INSERT INTO tbl_pet (pet_name, pet_age, injection)
    VALUES (#{petName}, #{petAge}, #{injection})
  </insert>

  <update id="update">
    UPDATE tbl_pet
    SET pet_name = #{petName},
        pet_age = #{petAge},
        injection = #{injection}
    WHERE id = #{id}
  </update>

  <delete id="deleteById">
    DELETE FROM tbl_pet
    WHERE id = #{id}
  </delete>

  <select id="findById" resultType="Pet">
    SELECT * FROM tbl_pet
    WHERE id = #{id}
  </select>

  <select id="findAll" resultType="Pet">
    SELECT * FROM tbl_pet ORDER BY pet_name
  </select>

  <select id="petCount" resultType="int">
    SELECT COUNT(*) FROM tbl_pet
  </select>
</mapper>

---

✅ SQL JOIN 복습

✔ 게시물과 댓글 조인

SELECT P.POST_ID, P.CONTENT, P.VIEW_COUNT, TO_CHAR(P.CREATION_DATE, 'YYYY-MM-DD') AS CREATED_AT, C.COMMENT_TEXT
FROM POSTS P
INNER JOIN COMMENTS C ON P.POST_ID = C.POST_ID;

✔ 사용자, 게시물, 댓글 작성자 정보 포함 조인

SELECT
  P.USER_ID,
  U.USERNAME,
  P.POST_ID,
  P.CONTENT,
  P.VIEW_COUNT,
  TO_CHAR(P.CREATION_DATE, 'YYYY-MM-DD') AS CREATED_AT,
  C.USER_ID,
  U2.USERNAME AS COMMENTER,
  C.COMMENT_TEXT
FROM POSTS P
INNER JOIN COMMENTS C ON P.POST_ID = C.POST_ID
INNER JOIN USERS U ON P.USER_ID = U.USER_ID
INNER JOIN USERS U2 ON C.USER_ID = U2.USER_ID;

✔ OUTER JOIN 예시

SELECT *
FROM USERS U
LEFT OUTER JOIN USER_PROFILES UP ON U.USER_ID = UP.USER_ID
ORDER BY U.USER_ID;

✔ Oracle 전용 외부조인 (+)

SELECT *
FROM USERS U, USER_PROFILES UP
WHERE U.USER_ID = UP.USER_ID (+)
ORDER BY U.USER_ID;

---

✅ 요약

• Pet 엔티티와 매퍼를 통해 MyBatis 기반 CRUD 기능 구현
• 매핑 XML 설정으로 SQL과 자바 객체를 연결
• JOIN/OUTER JOIN을 활용한 테이블 연결 및 조회 쿼리 연습