MyBatis

MyBatis設定

implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'
implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.4'

spring:
	datasource:
    	    driver-class-name: org.postgresql.Driver
            jdbc-url: jdbc:postgresql://localhost/mybatis
            username: test
            password: test@@
        mybatis:
        	# 패키지 명을 생략할 수 있도록 alias 설정
        	type-aliases-package: com.mybatis.dto
            
            # mapper.xml 위치 지정
            mapper-locations: mybatis-mapper/**/*.xml
            
            # model 프로퍼티 camel case 설정
            configuration.map-underscore-to-camel-case=true
            
        # mapper 로그레벨 설정
        logging:level:com:atoz_develop:mybatissample:repository=DEBUG
            
            

Mapper XMLファイル

SELECT

<select id="selectPerson" parameterType="int" resultType="hashmap">
	SELECT * FROM PERSON WHERE ID = #{id}
</select>

// JDBC 코드와 유사함
String selectPerson = "SELECT * FROM PERSON WHERE ID=?";
PreparedStatement ps = conn.prepareStatement(selectPerson);
ps.setInt(1,id);

<select
	id="selectPerson"
    parameterType="int"
    resultType="hashmap"
    resultMap="personResultMap"
    flushCache="false"
    useCache="true"
    timeout="10"
    fetchSize="256"
    statementType="PREPARED"
    resultSetType="FORWARD_ONLY">

INSERT, UPDATE AND DELETE

<insert
	id="insertAuthor"
    parameterType="domain.blog.Author"
    flushCache="true"
    statementType="PREPARED"
    keyProperty=""
    keyColumn=""
    useGeneratedKeys=""
    timeout="20">
    
<update
	id="updateAuthor"
    parameterType="domain.blog.Author"
    flushCache="true"
    statementTypes="PREPARED"
    timeout="20">
    
<delete
	id="deleteAuthor"
    parameterType="domain.blog.Author"
    flushCache="true"
    statementType="PREPARED"
    timeout="20">

<insert id="insertAuthor">
	insert into Author (id, username, password, email, bio)
    values (#{id}, #{username}, #{password}
</insert>

<update id="updateAuthor">
	update Author set
    	username = #{username},
        password = #{password},
        email = #{email},
        bio = #{bio}
        where id = #{id}
</update>

<delete id="deleteAuthor">
	delete from Author where id = #{id}
</delete>

<insert id="insertAuthor" useGeneratedKeys="true" keyProperty="id">
	insert into Author (username, password, email, bio)
    values (#{username},#{password},#{email},#{bio})
</insert>

<insert id="insertAuthor" useGeneratedKeys="true" keyProperty="id">
	insert into Author (username, password, email, bio) values
    <foreach item="item" collection="list" separator=",">
    	(#{item.username}, #{item.password}
    </foreach>
</insert>

<select id="selectAuthor" resultType="com.test.Author">
  select * from ttest where name in
    <foreach collection='list' index='index' item='abc' open='(' close=')' separator=','>
        #{abc}
    </foreach>
</select>

<select id="selectAuthor" resultType="com.test.Author">
	select * from ttest where name in
	<foreach collection='array' index='index' item='abc' open='(' close=')' separator=','
    	#{abc}
    </foreach>
</select>

<insert id="insertAuthor">
	<selectKey keyProperty="id" resultType="int" order="BEFORE">
    	select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1
    </selectKey>
    insert into Author
    	(id, username, password, email, bio, favourite_section)
    values
    	(#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection, jdbcType=VARCHAR})
</insert>

<selectKey
	keyProperty="id"
    resultType="int"
    order="BEFORE"
    statementType="PREPARED">

@Select("select * from user where id = #{id}")
User findById(@Param("id") long id);

@Select("select * from user where name = #{name}")
User findByName(@Param("name") String name);

@Select("select * from user where email = #{email}")
User findByEmail(@Param("email") String email);

@Select("select * from user where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);

User userOfId1 = userMapper.findByColumn("id", 1L);
User userOfNameKid = userMapper.findByColumn("name", "kid");
User userOfEmail = userMapper.findByColumn("email", "[email protected]");

Result Maps

<!-- XML 설정파일에서 -->
<typeAlias type="com.test.model.User" alias="User"/>

<resultMap id="detailedBlogResultMap" type="Blog">
	<constructor>
    	<idArg column="blog_id" javaType="int" />
    </constructor>
    <result property="title" column="blog_title" />
    <association property="author" javaType="Author">
    	<id property="id" column="author_id" />
        <result proeprty="username" column="author_username"/>
        <result property="password" column="author_password"/>
    </association>
    <collection property="posts" ofType="Post">
    	<id property="id" column="post_id" />
        <result property="author" javaType="Author"/>
        <discriminator javaType="int" column="draft">
        	<case value="1" resultType="DraftPost"/>
        </discriminator>
    </collection>
</resultMap>

	- id: 추후 쿼리를 정의하는 태그에서 resultType 대신 사용될 resultMap argument의 value가 될 것이다.

	- 생성자이다. 위 예제에서는 id만 이용하고 있는데 이 경우에는 Blog(int blog_id) 꼴의 생성자를 호출할 것이다.

	- 결과값의 column을 자바 클래스의 어떤 멤버 변수에 할당할 것인지 정의한다.

	- has one일 때 사용하는 태그

resultMapは、タグに定義された結果を別のresultMapに分離して参照するために使用することができる.

	- has many일 때 사용하는 태그

<discriminator javaType="int" column="vehicle_type">
	<case value="1" resultMap="carResult" />
    <case value="2" resultMap="vanResult" />
</discriminator>

<resultMap id="blogResult" type="Blog">
	<association property="author" column="author_id" javaType="Author" select="selectAuthor" />
</resultMap>

<select id="selectBlog" resultMap="blogResult">
	select * from blog where id = #{id}
</select>

<select id="selectAuthor" resultType="Author">
	select * from author where id = #{id}
</select>