19. 마이바티스 프레임워크

<?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>
    <!-- VO 클래스 별칭 설정 -->
    <typeAliases>
        <typeAlias type="com.spring.vo.MemberVO"
            alias="memberVO" />
    </typeAliases>

    <!-- DB 연결 설정 -->
    <environments default="development">
        <environment id="development">
            <transactionManager
                type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver"
                    value="oracle.jdbc.driver
                           .OracleDriver" />
                <property name="url"
                    value="jdbc:oracle:thin:
                           @localhost:1521:XE" />
                <property name="username"
                    value="scott" />
                <property name="password"
                    value="tiger" />
            </dataSource>
        </environment>
    </environments>

    <!-- 매퍼 파일 등록 -->
    <mappers>
        <mapper resource="member.xml" />
    </mappers>
</configuration>

<?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="mapper.member">

    <!-- 전체 회원 조회 -->
    <select id="selectAllMembers"
        resultType="memberVO">
        <![CDATA[
            SELECT * FROM t_member
            ORDER BY joinDate DESC
        ]]>
    </select>

    <!-- 회원 추가 -->
    <insert id="insertMember"
        parameterType="memberVO">
        INSERT INTO t_member
            (id, pwd, name, email)
        VALUES
            (#{id}, #{pwd},
             #{name}, #{email})
    </insert>

    <!-- 회원 삭제 -->
    <delete id="deleteMember"
        parameterType="String">
        DELETE FROM t_member
        WHERE id = #{id}
    </delete>

</mapper>

public class MemberDAO {

    private static SqlSessionFactory factory;

    // SqlSessionFactory 초기화
    static {
        String resource = "config.xml";
        try {
            Reader reader = Resources
                .getResourceAsReader(resource);
            factory = new SqlSessionFactoryBuilder()
                .build(reader);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 전체 회원 조회
    public List<MemberVO> selectAllMembers() {
        SqlSession session =
            factory.openSession();
        List<MemberVO> list =
            session.selectList(
                "mapper.member"
                + ".selectAllMembers");
        session.close();
        return list;
    }

    // 회원 추가
    public int insertMember(MemberVO vo) {
        SqlSession session =
            factory.openSession();
        int result = session.insert(
            "mapper.member.insertMember", vo);
        session.commit();
        session.close();
        return result;
    }
}

<select id="searchMember"
    parameterType="memberVO"
    resultType="memberVO">
    SELECT * FROM t_member
    <where>
        <if test="name != null
                  and name != ''">
            name = #{name}
        </if>
        <if test="email != null
                  and email != ''">
            AND email = #{email}
        </if>
    </where>
</select>

<select id="searchByCondition"
    parameterType="map"
    resultType="memberVO">
    SELECT * FROM t_member
    <where>
        <choose>
            <when test="searchType == 'name'">
                name LIKE '%'
                    || #{keyword} || '%'
            </when>
            <when test="searchType == 'email'">
                email LIKE '%'
                    || #{keyword} || '%'
            </when>
            <otherwise>
                id = #{keyword}
            </otherwise>
        </choose>
    </where>
</select>

<select id="selectByIds"
    parameterType="map"
    resultType="memberVO">
    SELECT * FROM t_member
    WHERE id IN
    <foreach item="item"
        collection="idList"
        open="(" close=")"
        separator=",">
        #{item}
    </foreach>
</select>

SELECT * FROM t_member
WHERE id IN ('kim', 'lee', 'park')

<insert id="insertMembers"
    parameterType="map">
    <foreach item="item"
        collection="list"
        open="INSERT ALL"
        separator=" "
        close="SELECT * FROM DUAL">
        INTO t_member
            (id, pwd, name, email)
        VALUES
            (#{item.id}, #{item.pwd},
             #{item.name}, #{item.email})
    </foreach>
</insert>

<!-- SQL 조각 정의 -->
<sql id="memberColumns">
    id, pwd, name, email, joinDate
</sql>

<!-- 재사용 -->
<select id="selectAllMembers"
    resultType="memberVO">
    SELECT
        <include refid="memberColumns" />
    FROM t_member
</select>

<select id="selectMember"
    parameterType="String"
    resultType="memberVO">
    SELECT
        <include refid="memberColumns" />
    FROM t_member
    WHERE id = #{id}
</select>

<include
    refid="mapper.common.memberColumns" />

<!-- SqlSessionFactory 빈 등록 -->
<bean id="sqlSessionFactory"
  class="org.mybatis.spring
         .SqlSessionFactoryBean">
    <property name="dataSource"
        ref="dataSource" />
    <property name="configLocation"
        value="classpath:config.xml" />
</bean>

<!-- SqlSessionTemplate 빈 등록 -->
<bean id="sqlSession"
  class="org.mybatis.spring
         .SqlSessionTemplate">
    <constructor-arg
        ref="sqlSessionFactory" />
</bean>

@Repository("memberDAO")
public class MemberDAOImpl
        implements MemberDAO {

    @Autowired
    private SqlSession sqlSession;

    @Override
    public List<MemberVO> selectAllMembers() {
        return sqlSession.selectList(
            "mapper.member.selectAllMembers");
    }

    @Override
    public int insertMember(MemberVO vo) {
        return sqlSession.insert(
            "mapper.member.insertMember", vo);
    }
}