본문 바로가기
[study]이론정리/JDBC

MyBatis(3) 동적sql 처리

by yoon9i 2024. 4. 5.

5. MyBatis 동적sql 처리 ( emp 테이블로 실습 )

 

1) 다중 insert 처리

mysql sql 문:

INSERT INTO emp ( empno, ename, sal )
VALUES ( 10,  'a', 100 ), ( 20,  'a', 100 ), ( ), ....; -- 동적으로 처리

mybatis sql 문:
        <insert id="multiInsert" parameterType="arraylist">
         INSERT INTO emp ( empno, ename, sal )
        VALUES
            <foreach item="dto"  collection=" list ", separator=" , ">
                  (  #{ dto.empno }, #{ dto.ename }, #{ dto.sal }     )
            </foreach> <!-- list 갯수만큼 반복해서 ( ) 를 만듬 -->
        </insert> 

2) 다중 delete 처리
mysql sql문:

DELETE FROM emp
WHERE sal IN ( 800, 1000, ... );

mybatis sql문1: sal 무조건 sal 전달됨.
         
       <delete id="remove" parameterType="arrayList">
        DELETE FROM emp
        WHERE sal IN
                <foreach item="x" collection="list" open="(" separator="," close=")">
                         #{x}
                </foreach>
         </delete>


mybatis sql문2: sal 전달안될수도 있는 경우
         
       <delete id="remove" parameterType="arrayList">
        DELETE FROM emp
           <where>
               <foreach item="x" collection="list" open="(" separator="," close=")">
                        #{x}
               </foreach>
             </where>
         </delete>


3) 선택적 update 문

mysql sql 문: 어떤 컬럼을 수정할지 잘 모르는 경우

ex) sal 또는 ename 또는 2개모두

UPDATE emp
--set sal 값
--set ename = 값
SET sal = 값, ename = 값
WHERE empno = 값;
 

mybatis sql 문:

UPDATE emp
<set>
       <if test=" sal != 0 "> sal = #{sal} </if>
       <if test=" ename != null "> ename = #{ename} </if>
</set>
WHERE empno = 값


4) 다중 update 문

mysql sql 문:

UPDATE emp
SET sal = sal + 100
WHERE empno IN ( 800,1000,... )

mybatis sql 문:

<update id = "update: parameter="arraylist">
    UPDATE emp
    SET sal = sal + 100
    <where>
         <foreach item="x" collection="list" open="empno IN ("    separator=","   close=")">
                        #{x}
          </foreach>
      </where>
</update>


5) 조건이 여러개

mysql sql 문: 

job 이 'MANAGER' 인 경우 sal > 1500 조회
job 이 'SALESMAN' 인 경우 sal > 2500 조회
모두 아니면 sal > 3000 조회

mybatis sql 문:

SELECT empno, ename, sal
FROM emp
<where>
     <choose>
                        <when test= " job == 'MANAGER' "> sal > 1500 </when>
                        <when test= " job == 'SALESMAN' "> sal > 2500 </when>
                        <otherwise> sal > 3000 </otherwise>
     </choose>
</where>


6) 단일값 조회
ex)
SELECT COUNT(*) AS cnt
FROM emp

count(*)
---------
17

JDBC 이용:
rs.next( );
rs.getInt(1); // rs.getInt("cnt")

MyBatis 이용:

int cnt = session.selectOne(" cnt  ");

<select id="cnt" resultType="int"></select>


7) 페이징 처리
 가. 내림차순 정렬 

           <!-- 페이징 처리 -->
      <select id="paging" resultType="EmpDTO">
select empno, ename, sal
from emp
order by empno desc
      </select>

나. selectList("", 파라미터, new RoundBounds(skip, 갯수) )
ex)
          int curPage=2;
int perPage=3; // 페이지당 보여줄 레코드 갯수
int skip = (curPage-1)*perPage;

                List<EmpDTO> list7 = session.selectList("com.config.EmpMapper.paging", null, 
                           new RowBounds(skip, perPage));




8) CDATA 섹션 정리
=> XML 문법
=> CDATA ( Character DATA ) 표현식

<![CDATA[
       SQL 문
]]>


* > : &gt
  < : &lt
  공백 : &nbsp;
   " : &quot;

 

# 주석문 ( com.config.jdbc.properties )


jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/testdb
jdbc.userid=root
jdbc.passwd=1234
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;

import com.config.MySqlSessionFactory;
import com.dto.EmpDTO;

public class MyBatisEmpMain {

	public static void main(String[] args) {
		SqlSession session = MySqlSessionFactory.getSession();
		
		// 1. multi insert
		EmpDTO dto1 = new EmpDTO(1, "aaa1", "SALESMAN", 7369, null, 700, 0, 30);
		EmpDTO dto2 = new EmpDTO(2, "aaa2", "SALESMAN", 7369, null, 700, 0, 30);
		EmpDTO dto3 = new EmpDTO(3, "aaa3", "SALESMAN", 7369, null, 700, 0, 30);
		
		List<EmpDTO> list = Arrays.asList(dto1, dto2, dto3);
		
		
		//
		int n = session.insert("com.config.EmpMapper.multiInsert", list);
		session.commit();
		System.out.println(n + " 개가 저장됨.");
		//
		
		
		//-----------------------------------------------------------
		
		// 2. multi delete
		List<Integer> list2 = Arrays.asList( 1, 2, 9000, 8000);
		int n2 = session.delete("com.config.EmpMapper.remove", list2);
		session.commit();
		System.out.println(n2 + " 개가 삭제됨.");
		
		//-----------------------------------------------------------
		
		 // 3. 선택적인 update
		EmpDTO dto4 = new EmpDTO();
		dto4.setSal(8500);
		dto4.setEname("홍길동");
		dto4.setEmpno(3);
		
		int n3 = session.update("com.config.EmpMapper.update", dto4);
		session.commit();
		System.out.println(n3 + " 개가 업데이트 되었습니다.");
		
		//-----------------------------------------------------------
		
		// 4. multi update
		List<Integer> list4 = Arrays.asList(3, 7369, 7499);
		int n4 = session.update("com.config.EmpMapper.updateSal", list4);
		session.commit();
		System.out.println(n4 + " 개가 수정됨.");
		
		//-----------------------------------------------------------

		// 5. 다중 조건
		HashMap<String, String> map = new HashMap<>();
		map.put("xxx", "MANAGER"); // SELECT empno, sal FROM emp WHERE sal > 1500 ORDER BY sal DESC;
		map.put("xxx", "SALESMAN"); // SELECT empno, sal FROM emp WHERE sal > 2500 ORDER BY sal DESC;
		
		List<EmpDTO> list5 = session.selectList("com.config.EmpMapper.multiChoose", map);
		for (EmpDTO k : list5) {
			System.out.println(k.getEmpno()+"\t"+k.getJob()+"\t"+k.getSal());
		}
		
		//-----------------------------------------------------------
		
		// 6. 단일 컬럼
		int cnt = session.selectOne("com.config.EmpMapper.totalCount");
		System.out.println("총 레코드 갯수: " + cnt);
		
//		//-----------------------------------------------------------
		
		// 7. 페이징 처리
		int curPage = 1;
		int perPage = 3; // 페이지당 보여줄 레코드 갯수
		int skip = (curPage - 1) * perPage;
		
		List<EmpDTO> list6 = session.selectList("com.config.EmpMapper.paging", null, new RowBounds(skip, perPage));
		for (EmpDTO xxx : list6) {
			System.out.println(xxx.getEmpno()+"\t"+xxx.getEname());
		}
				

		//-----------------------------------------------------------
		session.close();
	}

}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "https://mybatis.org/dtd/mybatis-3-mapper.dtd">


<mapper namespace="com.config.EmpMapper">

	<!-- 삽입 -->
	<!-- 
	INSERT INTO emp ( empno, ename, sal )
	VALUES ( 10,  'a', 100 ), ( 20,  'a', 100 ), ( ), ....;	
	-->
	<insert id="multiInsert" parameterType="arraylist">
		INSERT INTO emp ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
		VALUES
		<foreach item="dto" collection="list" separator=",">
			( #{dto.empno}, #{dto.ename}, #{dto.job} , #{dto.mgr} ,now(), #{dto.sal}, #{dto.comm}, #{dto.deptno} )
		</foreach>
	</insert>

	<!-- ############################################################# -->


	<!-- 삭제 -->
	<delete id="remove" parameterType="arraylist">
		DELETE FROM emp
		WHERE empno IN
		<foreach item="x" collection="list" open="(" separator="," close=")">
			#{x}
		</foreach>
	</delete>

	<!-- ############################################################# -->

	<!-- 
	UPDATE emp 
	-set sal 값
	-set ename = 값
	SET sal = 값, ename = 값
	WHERE empno = 값;
	
	-->

	<!-- 선택적인 update -->
	<update id="update" parameterType="EmpDTO">
		UPDATE emp
		<set>
			<if test="sal != 0"> sal = #{sal}, </if>
			<if test="ename != null"> ename = #{ename} </if>
		</set>
		WHERE empno = #{empno}
	</update>

	<!-- ############################################################# -->

	<!-- 
	UPDATE emp
	SET sal = sal + 100
	WHERE empno IN ( 800,1000,... )
	-->

	<!-- multi update -->
	<update id="updateSal" parameterType="arraylist">
		UPDATE emp
		SET sal = sal + 10
		<where>
			<foreach item="x" collection="list" open="empno IN (" separator="," close=")">
				#{x}
			</foreach>
		</where>
	</update>

	<!-- ############################################################# -->

	<!-- 
	job 이 'MANAGER' 인 경우 sal > 1500 조회
	job 이 'SALESMAN' 인 경우 sal > 2500 조회
	모두 아니면 sal > 3000 조회	
	-->

	<!-- 다중 조건 -->
	<select id="multiChoose" parameterType="hashmap" resultType="EmpDTO">
		SELECT empno, job, sal
		FROM emp
		<where>
			<choose>
				<when test=" job == 'MANAGER' "> sal > 1500 </when>
				<when test=" job == 'SALESMAN' "> sal > 2500 </when>
				<otherwise> sal > 3000 </otherwise>
			</choose>
		</where>
		ORDER BY sal ASC
	</select>

	<!-- ############################################################# -->

	<!-- 단일 컬럼 -->
	<select id="totalCount" resultType="int">
		SELECT COUNT(*)
		FROM emp
	</select>

	<!-- ############################################################# -->

	<!-- 페이징 처리-->
	<select id="paging" resultType="EmpDTO">
		SELECT empno, ename, sal
		FROM emp
		ORDER BY empno DESC
	</select>

	<!-- ############################################################# -->

	<!-- CDATA 섹션 정리 -->

	<select id="multiChoose2" parameterType="hashmap" resultType="EmpDTO">
		SELECT empno, job, sal
		FROM emp
		<where>
			<choose>
				<when test=" job == 'MANAGER' "> sal &lt; 1500 </when>
				<when test=" job == 'SALESMAN' "> sal &lt; 2500 </when>
				<otherwise> sal &lt; 3000 </otherwise>
			</choose>
		</where>
		ORDER BY sal ASC
	</select>

	<select id="multiChoose3" parameterType="hashmap" resultType="EmpDTO">
		SELECT empno, job, sal
		FROM emp
		<where>
			<choose>
				<when test=" job == 'MANAGER' ">
				 	<![CDATA[sal < 1500]]> 
				 </when>
				<when test=" job == 'SALESMAN' "> 
					<![CDATA[sal < 2500]]> 
				</when>
				<otherwise>
				 	<![CDATA[sal < 3000]]> 
				 </otherwise>
			</choose>
		</where>
		ORDER BY sal ASC
	</select>

</mapper>
package com.dto;

public class EmpDTO {
	
	int empno;
	String ename;
	String job;
	int mgr;
	String hiredate; // 날자는 연산이 필요없으면 그냥 String 으로 처리
	int sal;
	int comm;
	int deptno;
	
	public EmpDTO() {
		// TODO Auto-generated constructor stub
	}

	public EmpDTO(int empno, String ename, String job, int mgr, String hiredate, int sal, int comm, int deptno) {
		this.empno = empno;
		this.ename = ename;
		this.job = job;
		this.mgr = mgr;
		this.hiredate = hiredate;
		this.sal = sal;
		this.comm = comm;
		this.deptno = deptno;
	}

	public int getEmpno() {
		return empno;
	}

	public void setEmpno(int empno) {
		this.empno = empno;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public int getMgr() {
		return mgr;
	}

	public void setMgr(int mgr) {
		this.mgr = mgr;
	}

	public String getHiredate() {
		return hiredate;
	}

	public void setHiredate(String hiredate) {
		this.hiredate = hiredate;
	}

	public int getSal() {
		return sal;
	}

	public void setSal(int sal) {
		this.sal = sal;
	}

	public int getComm() {
		return comm;
	}

	public void setComm(int comm) {
		this.comm = comm;
	}

	public int getDeptno() {
		return deptno;
	}

	public void setDeptno(int deptno) {
		this.deptno = deptno;
	}

	@Override
	public String toString() {
		return "EmpDTO [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate="
				+ hiredate + ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
	}
	
	
	
	
}
package com.config;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MySqlSessionFactory {

	static SqlSessionFactory sqlSessionFactory;
	static {
		String resource = "com/config/Configuration.xml";
		InputStream inputStream = null;
		try {
			inputStream = Resources.getResourceAsStream(resource);
		} catch (IOException e) {
			e.printStackTrace();
		}
		sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
	}// end static 블럭

	// SqlSessionFactory 로부터 SqlSession 얻는 메서드
	public static SqlSession getSession() {
		// MyBatis는 명시적으로 commit 지정해야 된다.
		SqlSession session = sqlSessionFactory.openSession(); // openSession(false) 동일
		return session;
	}

}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

	<!-- jdbc.properties 등록 -->
	<properties resource="com/config/jdbc.properties"></properties>
	
	<!-- DTO 별칭 -->
	<typeAliases>
		<typeAlias type="com.dto.DeptDTO" alias="DeptDTO"/>
		<typeAlias type="com.dto.EmpDTO" alias="EmpDTO"/>
	</typeAliases>

	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<!-- jdbc.properties 등록된 값을 참조: ${key} -->
				<property name="driver" value="${jdbc.driver}" />
				<property name="url" value="${jdbc.url}" />
				<property name="username" value="${jdbc.userid}" />
				<property name="password" value="${jdbc.passwd}" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<!-- DeptMapper.xml 등록 -->
		<mapper resource="com/config/DeptMapper.xml" />
		<mapper resource="com/config/DeptMapper2.xml" />
		
		<mapper resource="com/config/EmpMapper.xml"/>
	</mappers>
</configuration>

'[study]이론정리 > JDBC' 카테고리의 다른 글

Swing(1) 기본설명 및 프로젝트 생성  (0) 2024.04.09
MyBatis(4) 아키텍쳐  (0) 2024.04.05
MyBatis(2) MyBatis 사용  (0) 2024.04.04
MyBatis(1) 설치 및 설정  (0) 2024.04.04
JDBC(2) 트랜잭션  (0) 2024.04.04

관련글

티스토리툴바