Mybatis - Dynamic SQL

🎯오늘 하루는

 

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

 

 

 

 

---

 

 

 

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>