Mybatis - Dynamic SQL

티스토리 뷰

DB/MyBatis

Mybatis - Dynamic SQL

xoo | 수진 2023. 8. 7. 16:12

🎯오늘 하루는

Mybatis에서 select, DML 문을 작성하는 것에 이어

Dynamic SQL 문법을 배웠다.

JDBC보단 훨씬 간결하지 싶으면서도 아직까지는 머릿속에 정리가 되지 않아 어지러워 꼼꼼히 정리하고 눈에 손에 익히는 과정을 거치고자 한다!

📖 정리

1️⃣ Multi Select

select * from dept where deptno IN ( 값, 값2, ... )

⇒ 값의 갯수가 안정해짐 ★

⇒ 값을 전달

DeptMain.java

import java.io.InputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

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

import com.dto.DeptDTO;

public class DeptMain {

	public static void main(String[] args) throws Exception {
		// Configuration.xml 파일 읽기
		String resource = "com/config/Configuration.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory =
		  new SqlSessionFactoryBuilder().build(inputStream);
		
		// SqlSession 얻기
		SqlSession session = sqlSessionFactory.openSession();

		// < Dynamic SQL >     값의 갯수가 안정해짐 ★
		// 1. Multi select
		// select * from dept where deptno IN ( 값, 값2, ... ) 
		// => 내가 만들고싶은 코드
		// => 값의 갯수가 안정해지고 달라질 수 있는게 포인트 ★
		
		// 값들을 뭉쳐서 Mapper 쪽으로 던져야하기 때문에 List 사용
		List<Integer> deptnoList = Arrays.asList(10,20,30);
		
		// DTO에 담는다.
		// resultType : DeptDTO
		// parameterType : deptnoList
		List<DeptDTO> list = session.selectList("DeptDynamicMapper.selectByDeptnoMulti", deptnoList);
		
		// foreach문으로 원하는 값을 출력
		for (DeptDTO dto : list) {
			System.out.println(dto);
		}
		
		session.close();
	}

}

Configuration.xml

<?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>

<!-- 추가해주기  -->
<typeAliases>
	<typeAlias alias="DeptDTO" type="com.dto.DeptDTO"/>
</typeAliases>

  <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="com/config/DeptDynamicMapper.xml"/>    <!-- .이 아니라 / 이다. -->
  </mappers>
</configuration>

DeptDynamicMapper.xml

<?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="DeptDynamicMapper">

  <!-- Main클래스에서 resultType과 parameterType 알수있음 -->
  <select id="selectByDeptnoMulti" resultType="DeptDTO" parameterType="arrayList">
  	select deptno, dname, loc
  	from dept
  	where deptno IN 
  	<foreach item="item" collection="list"
        open="(" separator="," close=")" >
          #{item}
    </foreach>
  </select>
  
  
</mapper>

https://mybatis.org/mybatis-3/dynamic-sql.html

mybatis – MyBatis 3 | Dynamic SQL

Dynamic SQL One of the most powerful features of MyBatis has always been its Dynamic SQL capabilities. If you have any experience with JDBC or any similar framework, you understand how painful it is to conditionally concatenate strings of SQL together, mak

mybatis.org

2️⃣ Multi Select (2)

select * from dept where deptno IN ( 값, 값2, ... )

⇒ DTO 전달하기

DeptMain.java

import java.io.InputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

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

import com.dto.DeptDTO;

public class DeptMain {

	public static void main(String[] args) throws Exception {
		// Configuration.xml 파일 읽기
		String resource = "com/config/Configuration.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory =
		  new SqlSessionFactoryBuilder().build(inputStream);
		
		// SqlSession 얻기
		SqlSession session = sqlSessionFactory.openSession();

		
		// 1. Multi select(2)
		// select * from dept where deptno IN ( 값, 값2, ... )
		// => DTO를 전달
		
		List<DeptDTO> deptnoList2 = Arrays.asList(new DeptDTO(10,"",""),
												 new DeptDTO(20,"",""),
											     new DeptDTO(40,"",""));
		List<DeptDTO> list2 = session.selectList("DeptDynamicMapper.selectByDeptnoMulti2", deptnoList2);
		for (DeptDTO dto : list2) {
			System.out.println(dto);
		}
		session.close();
	}

}

Configuration.xml

<?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>

<!-- 추가해주기  -->
<typeAliases>
	<typeAlias alias="DeptDTO" type="com.dto.DeptDTO"/>
</typeAliases>

  <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="com/config/DeptDynamicMapper.xml"/>    <!-- .이 아니라 / 이다. -->
  </mappers>
</configuration>

DeptDynamicMapper.xml

<?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="DeptDynamicMapper">

  
  <select id="selectByDeptnoMulti2" resultType="DeptDTO" parameterType="arrayList">
  	select deptno, dname, loc
  	from dept
  	where deptno IN 
  	<foreach item="item" collection="list"
        open="(" separator="," close=")" >
          #{item.deptno}      <!-- DTO에서 사용하고자 하는  -->
    </foreach>
  </select>
  
</mapper>

✅ 강사님 방식

<select id="selectByDeptnoMulti2" resultType="DeptDTO" parameterType="arrayList">
  	select deptno, dname, loc
  	from dept
  	where deptno IN 
  	<foreach item="item" collection="list"
        open="(" separator="," close=")" >
          #{item.deptno}      <!-- DTO에서 사용하고자 하는  -->
    </foreach>
  </select>

✅ 가이드 방식

<select id="selectByDeptnoMulti2" resultType="DeptDTO" parameterType="arrayList">
  	select deptno, dname, loc
  	from dept
  	<where>
  	<foreach item="item" collection="list"
        open="deptno IN (" separator="," close=")" >
          #{item.deptno}      <!-- DTO에서 사용하고자 하는  -->
    </foreach>
    </where> 
  </select>

💡 가이드 방식

3️⃣ Multi Delete

DeptMain

import java.io.InputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

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

import com.dto.DeptDTO;

public class DeptMain {

	public static void main(String[] args) throws Exception {
		// Configuration.xml 파일 읽기
		String resource = "com/config/Configuration.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory =
		  new SqlSessionFactoryBuilder().build(inputStream);
		
		// SqlSession 얻기
		SqlSession session = sqlSessionFactory.openSession();

//2. Multi Delete
	// 값들을 뭉쳐서 Mapper 쪽으로 던져야하기 때문에 List 사용
	List<Integer> deptnoList3 = Arrays.asList(50, 60, 80);
	
	// session에 있는 delete 호출
	int n = session.delete("DeptDynamicMapper.deleteMulti", deptnoList3);
	System.out.println(n+" 개가 삭제됨");
	session.commit();    // 추가 ★
	
	session.close();
}

}

DeptDynamicMapper.xml

<?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="DeptDynamicMapper">

<delete id="deleteMulti" parameterType="arrayList">
  	delete from dept
  	<where>
	  	<foreach item="item" collection="list"
	        open="deptno IN (" separator="," close=")" >
	          #{item}     
	    </foreach>
  	</where>
  </delete>
  
</mapper>

4️⃣ Multi Update

import java.io.InputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

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

import com.dto.DeptDTO;

public class DeptMain {

	public static void main(String[] args) throws Exception {
		// Configuration.xml 파일 읽기
		String resource = "com/config/Configuration.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory =
		  new SqlSessionFactoryBuilder().build(inputStream);
		
		// SqlSession 얻기
		SqlSession session = sqlSessionFactory.openSession();


// 3. Multi Update
  // update dept set dname='개발' where deptno IN (값, 값2, ...)
  List<Integer> deptnoList4 = Arrays.asList(11, 12);
  int n2 = session.update("DeptDynamicMapper.updateMulti", deptnoList4);
  System.out.println(n2+" 개가 수정됨");
  session.commit();     // ★
	
	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="DeptDynamicMapper">


<!-- 3. Multi Update -->
  <update id="updateMulti" parameterType="arraylist">
  	update dept
  	set dname='개발'
  	<where>
  		<foreach item="item" collection="list"
	        open="deptno IN (" separator="," close=")" >
	          #{item}     
	    </foreach>
  	</where>
  </update>
  
</mapper>

5️⃣ Multi Insert

import java.io.InputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;

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

import com.dto.DeptDTO;

public class DeptMain {

	public static void main(String[] args) throws Exception {
		// Configuration.xml 파일 읽기
		String resource = "com/config/Configuration.xml";
		InputStream inputStream = Resources.getResourceAsStream(resource);
		SqlSessionFactory sqlSessionFactory =
		  new SqlSessionFactoryBuilder().build(inputStream);
		
		// SqlSession 얻기
		SqlSession session = sqlSessionFactory.openSession();

//4. Multi Insert
  /*
   * insert all
   * 	into dept ~
   *  	into dept ~
   *  subquery;
   */
// update dept set dname='개발' where deptno IN (값, 값2, ...)
  
  List<DeptDTO> deptnoList5 = Arrays.asList(new DeptDTO(90,"개발","서울"),
		  new DeptDTO(91,"개발","서울"),
		  new DeptDTO(92,"개발","서울"));
  
  int n3 = session.insert("DeptDynamicMapper.insertMulti", deptnoList5);
  System.out.println(n3 + "개가 저장됨");
  session.commit();
	
	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="DeptDynamicMapper">


<insert id="insertMulti" parameterType="arraylist">
  	insert all
  	<foreach item="item" collection="list">
	       into dept (deptno, dname, loc ) values (#{item.deptno}, #{item.dname}, #{item.loc})
	</foreach>
	select * from dual
  </insert>
  
</mapper>

저작자표시 비영리 변경금지

'DB > MyBatis' 카테고리의 다른 글

Mybatis - 분리(1) (0)	2023.08.07
Mybatis - 조건문 (0)	2023.08.07
Mybatis - 환경설정 및 SELECT 예제 (0)	2023.08.04
트랜잭션(Transaction) 처리 (0)	2023.08.03
DAO(Data Access Object) Pattern (0)	2023.08.02

공지사항

최근에 올라온 글

최근에 달린 댓글

Total

Today

Yesterday

링크

TAG more

« 2024/05 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

글 보관함

< Xoo | Log >

티스토리 뷰