[ Mybatis ] Mybatis SQL

즐겁게 하하하 2022. 2. 2. 09:12

728x90

동적 SQL 엘리먼트	설명
<if test="조건">SQL</if>	조건이 참일 경우 SQL문을 반환한다.
<choose> <when test="조건1">SQL</when> <when test="조건2">SQL</when> <otherwise>SQL</otherwise> <choose>	검사할 조건이 여러개일 경우 사용 자바의 if-else문과 유사하다. 일치하는 조건이 없으면 <otherwise>의 SQL이 반환된다.
<where> <if test="조건1">SQL</if> <if test="조건2">SQL</if> </where>	SQL문의 WHERE절을 만들때 사용한다. <where> 안의 조건식에서 일치하는 조건이 있으면 WHERE절을 만들어 반환하고 없으면 만들지 않는다.
<trim prefix="접두어" prefixOverrides="문자열\|문자열"> <if test="조건1">SQL</if> <if test="조건2">SQL</if> </trim>	조건에 따라 SQL이 반환되면 SQL문의 앞부분에서 prefixOverrides에 지정된 문자열과 일치하는 문자열을 제거하고 prefix로 지정한 접두어를 붙인다.
<set> <if test="조건1">SQL</if> <if test="조건2">SQL</if> </set>	UPDATE문의 SET절을 만들때 사용한다. <set> 안의 조건식에서 일치하는 조건이 있으면 SET절을 만들어 반환하고 없으면 만들지 않는다. SET절의 항목이 여러 개일 경우 자동으로 콤마(,)를 붙인다.
<foreach item="항목" index="인덱스" collection="목록" open="시작문자열" close="종료문자열" separator="구분자"> </foreach>	목록형 데이터로 SQL문을 만들때 사용한다. 특히 IN(값, 값, ...) 조건을 만들때 편리하다. item : 한 개의 항목을 가리키는 변수 이름 지정 index : 인덱스 값을 꺼낼때 사용할 변수 이름 지정 collection : java.util.List 구현체나 배열 객체 지정 open : 최종 반환값의 접두어 지정 close : 최종 반환값의 접미어 지정 separator : 매 회차 종료시 들어갈 구분자 문자열 지정
<bind name="변수명" value="값"/>	변수를 생성할때 사용

동적 SQL에서 가장 흔하게 사용하는 표현식

<select id="findBlog" resultType="Blog">
  SELECT * FROM BLOG
  WHERE 1 = 1
  
  <if test="state != null">
    AND state = #{state}
  </if>
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="searchStartDate != '' and searchStartDate != null">
    AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d') <![CDATA[>=]]> #{searchStartDate}
  </if>
  <if test="searchEndDate != '' and searchEndDate != null">
    AND DATE_FORMAT(CREATE_DATE, '%Y-%m-%d') <![CDATA[<=]]> #{searchEndDate}
  </if>
  
</select>

choose, when, otherwise :

WHERE 조건절에 java에서의 switch문과 유사한 문법을 적용해보고 싶은 경우에 사용합니다.

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
  
</select>

<select id="selectList" parameterType="map" resultMap="projectResultMap">
    select PNO, PNAME, STA_DATE, END_DATE, STATE
    from PROJECTS
    order by
    <choose>
        <when test="orderCond == 'TITLE_ASC'">PNAME asc</when>
        <when test="orderCond == 'TITLE_DESC'">PNAME desc</when>
        <when test="orderCond == 'STARTDATE_ASC'">STA_DATE asc</when>
        <when test="orderCond == 'STARTDATE_DESC'">STA_DATE desc</when>
        <when test="orderCond == 'ENDDATE_ASC'">END_DATE asc</when>
        <when test="orderCond == 'ENDDATE_DESC'">END_DATE desc</when>
        <when test="orderCond == 'STATE_ASC'">STATE asc</when>
        <when test="orderCond == 'STATE_DESC'">STATE desc</when>
        <when test="orderCond == 'PNO_ASC'">PNO asc</when>
        <otherwise>PNO desc</otherwise>
    </choose>
</select>

trim, where, set :

prefix	trim문에서 작성하는 쿼리의 맨 앞에 해당 문자를 붙여준다.
prefixOverrides	trim문에서 작성하는 쿼리의 맨 앞에 오는 문자들을 삭제한다.
suffixOverrides	trim문에서 작성하는 쿼리의 맨 끝에 오는 문자들을 삭제한다.
suffix	trim문에서 작성하는 쿼리의 맨 끝에 해당 문자를 붙여준다.

prefix로 where를 기본으로 붙여주고,
prefixOverrides는 "AND | OR"가 나오면 해당 문자열을 지워주는 역할을 합니다.

<select id="findBlog" resultType="Blog">
  SELECT * FROM BLOG
  
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
      <if test="state != null">
           state = #{state}
      </if>
      <if test="title != null">
          AND title like #{title}
      </if>
      <if test="author != null and author.name != null">
          AND author_name like #{author.name}
      </if>
    </trim>
    
</select>

suffixOverrides는 ","가 맨 끝에 나오면 해당 문자열을 지워주는 역할을 합니다.

prefixOverrides는 ","가 맨 앞에 나오면 해당 문자열을 지워주는 역할을 합니다.

<update id="updateBlog">
  update Author
  
    <trim prefix="set" suffixOverrides=",">
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </trim>
    
  where id=#{id}
  
</update>

맨 앞에 "AND ("를 붙이고

내부문의 맨 앞에 "AND" 또는 "OR"가 있으면 제거하고,
맨 끝에 ")"를 붙여줍니다.

<select id="findBlog" resultType="Blog">
  SELECT * FROM BLOG
  WHERE
  
  <if test="state != null">
    state = #{state}
  </if>
  
  <trim prefix="AND (" prefixOverrides="AND | OR" suffix=")">
    <if test="title != null">
      OR title like #{title}
    </if>
    <if test="author != null and author.name != null">
      OR author_name like #{author.name}
    </if>
    <if test="category != null and category != null">
      OR category like #{category}
    </if>
  </trim>
  
</select>

<update id="update" parameterType="map">
    update PROJECTS
    <set>
        <if test="title != null">PNAME=#{title},</if>
        <if test="content != null">CONTENT=#{content},</if>
        <if test="startDate != null">STA_DATE=#{startDate},</if>
        <if test="endDate != null">END_DATE=#{endDate},</if>
        <if test="state != null">STATE=#{state},</if>
        <if test="tags != null">TAGS=#{tags},</if>
    </set>
    where PNO = #{no}
</update>

foreach

IN조건에 해당 하는 구문을 작성할때 반복되는 값을 사용하는 경우 사용합니다.

"list"형태의 값을 구분자 ","로 구분하고, "("과 ")"로 감싸주도록 합니다.

open="(" separator="," close=")"

<select id="selectIn" resultType="BlogDTO">
  SELECT *
  FROM Blog P
  WHERE ID in
  
  <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
        #{item}
  </foreach>
  
</select>

SELECT *
  FROM POST P
  WHERE ID in (val1, val2, val3, val4)

-- union 사용 예
<select id="searchContractCount" parameterType="java.util.Map" resultType="hashMap">
    /* common.searchContractCount */  

    <foreach collection="prdList" item="prd" index="index" separator="UNION ALL" >
        select  
        '[' || #{searchMonth} || ']' as month,
        <foreach collection="searchMonthList" item="item" index="index" separator=",">  
            coalesce( fn_get_contract_sum('contract_total', #{searchYear} , #{item}, #{prd} , ${fnTreePageParam}) , 0 ) as "${item}"
        </foreach> 
    </foreach> 
</select>

- Array 사용

String[] array = {"a", "b", "c"};
demoVO.setDemoArray(array);

<select id="selectDemoList" resultType="com.example.demo.vo.DemoVO">
    select id from table_name where id in
    <foreach collection="demoArray" item="item" index="index" open="(" close=")" separator=",">
        #{item}
    </foreach>
</select>

- List 사용

List<String> list = new ArrayList<>();
list.add("a");
list.add("b");
list.add("c");
demoVO.setDemoList(list);

<select id="selectDemoList" resultType="com.example.demo.vo.DemoVO">
    select id from table_name where id in
    <foreach collection="demoList" item="item" index="index" open="(" close=")" separator=",">
        #{item}
    </foreach>
</select>

CDATA 를 사용

[ ] 안에 있는 문장은 파싱되지 않고 그대로 문자열로 출력된다.

SELECT문에는 조건을 걸어 쿼리하기 위해 <, >, = 등의 기호를 많이 사용하는데,

이것이 파싱 중에 태그로 인식되거나 하는 등의 문제가 생길 수 있다.

<select id="findAll" resultMap="MemberResultMap">
    <![CDATA[
    select *  from employees  where  1=1
    ]]>
    <choose>
        <when test='user_type != null and user_type =="1"'>
            <![CDATA[
                salary > 100
            ]]>
        </when>
 
        <otherwise>
            <![CDATA[
                salary < 100
            ]]>
        </otherwise>
    </choose>
</select>

refid(reference id)

반복되는 쿼리를 미리 작성해 놓고 재활용 할 수 있게 해준다.

<mapper>
  
  <sql id="a">
      SELECT * 
   FROM TABLE
  </sql>
  
    <sql id="b">
      SELECT * 
   FROM TABLE
   WHERE ${param1}
  </sql>
  
  <select id="hahahaA" resultType="hashmap">
      <include refid="a" />
      WHERE filed = #{value}
  </select>
  
  <select id="hahahaB" resultType="hashmap">
      <include refid="b">
      	<property name="param1" value="value">
      </include>
      WHERE filed = #{value}
  </select>
  
</mapper>

SQL include

<sql id = "selectFormBoard">
  select * from board
</sql>

<select id="select parameterType="int" resultType="BoardDto">
   <include refid="selectFromBoard"/>
   where bno = #{bno}
</select>

SQL LIKE 구문

# MySql

SELECT * FROM TABLE_NAME

WHERE COLUMN_NAME LIKE CONCAT(‘%’, #{searchKeyword}, ‘%’)

# ORACLE

SELECT * FROM TABLE_NAME

WHERE COLUMN_NAME LIKE '%'||#searchKeyword#||'%'

# Ms-Sql

SELECT * FROM TABLE_NAME

WHERE COLUMN_NAME LIKE '%' + #searchKeyword# + '%'

USER DAO

SqlSession session = getSqlSessionFactory().openSession();
Map<String,Object> map2 = new LinkedHashMap<>();
try{
    HashMap<String,Object> map = new HashMap();
    map.put("startNum",startNum);
    map.put("endNum",amount);
    map.put("userDTO",userDTO);

    List<UserDTO> listUser = session.selectList("listUser" , map);
    int total_count = session.selectOne("queryRowCount");
    map2.put("listUser",listUser);
    map2.put("total_count",total_count);
}finally {
    session.close();
}
return map2;

Mybatis -mapper.xml

<select id="queryRowCount" resultType="int" >
    SELECT FOUND_ROWS() AS total_cnt
</select>


<select id="listUser" parameterType="hashmap" resultType="userConfig">
    select
        SQL_CALC_FOUND_ROWS
        *
    from
    (
        select
            @rownum:=@rownum+1 as num
             , mc.*
        from user mc
           ,(SELECT @rownum:=0) r
        where  <![CDATA[ mc.user_no > 0 ]]>

        <if test="userDTO.user_id != null" >
            and mc.user_id LIKE CONCAT('%', #{userDTO.user_id}, '%')
        </if>
        <if test="userDTO.user_nm != null" >
            and mc.user_nm LIKE CONCAT('%', #{userDTO.user_nm}, '%')
        </if>

        order by mc.user_no desc
    )ee
    order by num desc
    LIMIT  #{startNum} , #{endNum}
</select>

ON DUPLICATE KEY UPDATE를 이용(UPDATE + INSERT)

<insert id = "updateMenuAuth" parameterType="menuAuthConfig">
        iNSERT INTO menu_auth(
            emp_id, mm_parent, mm_code, mu_view, mu_new, mu_mod, mu_del
        ) VALUES ( 
            #{emp_id} ,
            #{mm_parent} ,
            #{mm_code} ,
            #{mu_view} ,
            #{mu_new} ,
            #{mu_mod} ,
            #{mu_del}
        ) ON DUPLICATE KEY UPDATE
             mu_view = VALUES (mu_view) ,
             mu_new = VALUES (mu_new) ,
             mu_mod = VALUES (mu_mod) ,
             mu_del = VALUES (mu_del) ; 
    </insert>

SELECT KEY 사용법

useGeneratedKeys : (insert, update에만 적용) 자동생성 키를 받을때 true로 설정한다. (default: false)

useGeneratedKeys="true"로 설정해서, 자동 생성된 키 값들을 가져올 거라는 것을 명시

keyProperty : 리턴 받을 변수명 설정. 여러개를 사용한다면 ,(콤마)를 구분자로 나열한다.
resultType : 러턴 값의 자료형
order : 순서 [ AFTER | BEFORE ]
- AFTER : insert 구문 실행 후 selectKey 구문 실행
- BEFORE : selectKey 구문 실행 후 리턴 값을 keyProperty에 셋팅한 뒤 insert 구문 실행

<insert id="insertStudents" useGeneratedKeys="true" keyProperty="id">
  insert into Students (name ,email)
  values (#{name },#{email})
</insert>

https://hahagogo.tistory.com/132?category=955499

[ Mybatis ] mybatis에서 selectKey 사용법

Board라는 게시판에서 입력을 수행시 boardID값을 기존에 최대값에 +1한 다음에 그 값을 입력하고 싶다면 아래의 같이 처리하면 된다. SELECT MAX(boardID)+1 FROM board INSERT INTO board(boardID, title, con..

hahagogo.tistory.com

728x90