Spring STEP 5 - MyBatis 응용 — Developer Story House

728x90

★ MyBatis

영속성 계층(데이터베이스 조작 기술)
JDBC 역할과 동일
응용 프로그램 <-> JDBC <-> 데이터베이스
응용 프로그램 <-> MyBatis <-> 데이터베이스
JDBC 기반 > 관리, 생산성 향상

★ MyBatis를 스프링에 적용하는 방법

1. XML 매퍼를 사용하는 방법
2. 인터페이스 매퍼를 사용하는 방법

1. XML 매퍼를 사용하는 방법

pom.xml > dependency 추가

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <!-- <version>3.4.6</version> warning 때문에 --> 
    <version>3.5.2</version>
</dependency>

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>1.3.2</version>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>${org.springframework-version}</version>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>${org.springframework-version}</version>
</dependency>

ojdbc6.jar > 가져오기

Test
src/test/java -> com.test.persistence 패키지 생성 -> JDBCTest.java 생성

@Log4j
public class JDBCTest {

	@Test
	public void testConnection() {
		
		try {
			
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "hr", "java1234");
			
			assertNotNull(conn);
			
			log.info(conn.isClosed());
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
}

★ Connection 객체들 따로 관리

- Connection 객체들을 미리 여러개 생성(10개)
- 모두 DB 연결 (open)
- 사용자가 요청 시 1개씩 꺼내서 제공
- 사용자가 사용 종료 > 제공했던 Connection 객체 회수

★ Connection Pool, 커넥션 풀

- Commons DBCP
- Tomcat DBCP
- HikariCP > 스프링 부트 2.0부터 기본 적용

★ Commons DBCP 설정 방법

pom.xml > commons-dbcp 의존 추가

<dependency>
  <groupId>commons-dbcp</groupId>
  <artifactId>commons-dbcp</artifactId>
  <version>1.4</version>
</dependency>

root-context.xml > bean 추가

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>
  <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"></property>
  <property name="username" value="hr"></property>
  <property name="password" value="java1234"></property>
</bean>

com.test.persistence > "DBCPTest.java" 파일 생성

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("file:src/main/webapp/WEB-INF/spring/root-context.xml")
@Log4j
public class DBCPTest {

	@Autowired
	private DataSource dataSource;
	
	@Test
	public void testConnection() {
		
		assertNotNull(dataSource);
		
		try {
			
            		//getConnection() 해서 사용
			Connection conn = dataSource.getConnection();
			
			log.info(conn.isClosed());
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
}

★ HirakiCP

pom.xml > 의존 추가

<dependency>
      <groupId>com.zaxxer</groupId>
      <artifactId>HikariCP</artifactId>
      <version>2.7.4</version>
  </dependency>

root-context.xml > 설정 추가

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
  <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>
  <property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521:xe"></property> 
  <property name="username" value="hr"></property>
  <property name="password" value="java1234"></property>
</bean>

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
  <constructor-arg ref="hikariConfig"></constructor-arg>
</bean>

★ MyBatis 설정

pom.xml > 의존 추가(4개)
root-context.xml > 설정 추가

<!-- MyBatis -->
<bean id="sessionfactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource"></property>
    <property name="mapperLocations" value="classpath*:mapper/*.xml"></property>
</bean>

src/main/resources > "mapper" 폴더 > "test.xml" 생성

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- namespace="파일명" -->
<mapper namespace="test">
	
</mapper>

src/test/main > com.test.persistence > "MapperTest.java" 생성

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("file:src/main/webapp/WEB-INF/spring/root-context.xml")
@Log4j
public class MapperTest {

	@Autowired
	private SqlSessionFactory sqlSessionFactory;
	
	@Test
	public void sessionTest() {
		
		assertNotNull(sqlSessionFactory);
		
	}
	//Caused by: org.xml.sax.SAXParseException; lineNumber: 1; columnNumber: 39; Premature end of file.
	// xml -> 무엇을 잘못했다는 것 -> test.xml
	// Well-formed XML : 에러가 없는 xml -> 반드시 루트 태그가 존재해야함.
}

■ 쿼리 날려보기

test.xml

<mapper namespace="test">
    <!--
        모든 SQL 작성 > XML
     -->
    <!-- 
    <select id=""></select>
    <insert id=""></insert>
    <update id=""></update>
    <delete id=""></delete>
     -->

    <select id="time" resultType="String">
        select sysdate from dual
    </select>
</mapper>

MapperTest.java

@Test
public void sessionTest() {

    assertNotNull(sqlSessionFactory);

    //SqlSessionTemplate > SQL 실행 > Statement 역할
    SqlSession session = sqlSessionFactory.openSession();

    //NameSpace.id
    String time = session.selectOne("test.time");
    log.info(time);

}

★ 각종 SQL 실행하기

파일 구성

- "com.test.controller" > "MyBatisController.java"

- "com.test.persistence" > "MyBatisDAO.java"(I)
                     	 > "MyBatisDAOImpl.java"(C)
                         
- "com.test.domain" > "MyBatisDTO.java"

- src/main/resources > mapper > "mybatis.xml"

- views > "list.jsp"
    	> "add.jsp"
        > "addok.jsp"
        
- MyBatisTest > "script.sql"

기초 DB 작업 -> script.sql

create table tblMyBatis (
    seq number primary key,
    name varchar2(30) not null,
    age number(3) not null,
    address varchar2(300) not null,
    gender char(1) not null
);

create sequence seqMyBatis;

insert into tblMyBatis(seq, name, age, address, gender) values (seqMyBatis.nextVal, '홍길동', 20, '서울시 강남구', 'm');

commit;

DTO 작업 -> MyBatisDTO.java

package com.test.domain;

import lombok.Data;

@Data
public class MyBatisDTO {

	private String seq;
	private String name;
	private String age;
	private String address;
	private String gender;
	
}

servlet-context.xml -> 추가

<context:component-scan base-package="com.test.controller" />
<context:component-scan base-package="com.test.persistence" />

■ MyBatis(SqlSessionTemplate) > XML 기반 작업(XML Mapper)

1. executeUpdate()

- template.insert()
- template.update()
- template.delete()

2. executeQuery()

- template.selectOne() > 결과셋 레코드 1개 
                       > if (rs.next()) {}

- template.selectList() > 결과셋 레코드 N개
                        > while (rs.next()) {}

insert 작성

------------------------------- MyBatisController -> 
@GetMapping("/m1.do")
public String m1() {

    this.dao.m1();

    return "list";

}
-------------------------------- MyBatisDAO ->
public interface MyBatisDAO {

	void m1();
	
}
-------------------------------- MyBatisDAOImpl ->
@Override
public void m1() {

    //MyBatis(SqlSessionTemplate) > XML 기반 작업(XML Mapper)

    //this.template.insert("매퍼 네임스페이스.쿼리 ID");
    this.template.insert("mybatis.m1");

}
-------------------------------- mybatis.xml ->
<mapper namespace="mybatis">

	<insert id="m1">
	
		insert into tblMyBatis(seq, name, age, address, gender) 
			values (seqMyBatis.nextVal, '아무개', 26, '서울시 노원구', 'm')
	
	</insert>
	
</mapper>

■ log4jdbc-log4j2 설정하기

log4j > JDBC 감시
pom.xml ->

<dependency>
    <groupId>org.bgee.log4jdbc-log4j2</groupId>
    <artifactId>log4jdbc-log4j2-jdbc4</artifactId>
    <version>1.16</version>
</dependency>

src/main/resources > log4jdbc.log4j2.properties 파일 생성 -> 아래 데이터 삽입 및 저장

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

root-context.xml -> driverClassName, jdbcUrl 변경

<property name="driverClassName" value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property>
<property name="jdbcUrl" value="jdbc:log4jdbc:oracle:thin:@localhost:1521:xe"></property>

src/test/resource > log4j.xml : warn까지만 출력하고, 자잘한 메세지는 없애 달라는 코드

<logger name="jdbc.audit">
   <level value="warn" />
</logger>

<logger name="jdbc.resultset">
   <level value="warn" />
</logger>

<logger name="jdbc.connection">
   <level value="warn" />
</logger>

<logger name="jdbc.sqltiming">
   <level value="off" />
</logger>

src/main/resource > log4j.xml

<logger name="jdbc.audit">
   <level value="warn" />
</logger>

<logger name="jdbc.resultset">
   <level value="warn" />
</logger>

<logger name="jdbc.connection">
   <level value="warn" />
</logger>

<logger name="jdbc.sqltiming">
   <level value="off" />
</logger>

■ jsp -> 폼 전송 -> insert

-------------------------- MyBatisController.java ->
@PostMapping("/addok.do")
public String addok(MyBatisDTO dto) {

    this.dao.add(dto);

    return "addok";

}
-------------------------- MyBatisDAO.java ->
void add(MyBatisDTO dto);
-------------------------- MyBatisDAOImpl.java ->
@Override
public void add(MyBatisDTO dto) {

    this.template.insert("mybatis.add", dto);

}
-------------------------- MyBatis.xml ->
<insert id="add" parameterType="com.test.domain.MyBatisDTO">

    insert into tblMyBatis(seq, name, age, address, gender) 
        values (seqMyBatis.nextVal, #{name}, #{age}, #{address}, #{gender})

</insert>

■ delete -> seq -> 삭제

------------------------------- MyBatisController.java
@GetMapping("/m2.do")
public String m2(Model model, String seq) {

    //삭제
    //- delete from tblMyBatis where seq = 5

    int result = this.dao.m2(seq);

    model.addAttribute("result", result);

    return "list";
}
------------------------------- MyBatisDAO.java
int m2(String seq);
------------------------------- MyBatisDAOImpl.java
@Override
public int m2(String seq) {



    return this.template.delete("mybatis.m2", seq);
}
------------------------------- mybatis.xml
<delete id="m2" parameterType="String">
    delete from tblMyBatis where seq = #{seq}
</delete>

■ 수정하기 -> update -> 결과

-------------------------- MyBatisController.java
@GetMapping("/m3.do")
public String m3() {

    //수정하기
    Map<String,String> map = new HashMap<String, String>();

    map.put("seq", "3");
    map.put("name", "리민지");
    map.put("address", "경상북도 구미시");

    int result = dao.m3(map);

    return "list";

}
-------------------------- MyBatisDAO.java
int m3(Map<String, String> map);

-------------------------- MyBatisDAOImpl.java
@Override
public int m3(Map<String, String> map) {

    return this.template.update("mybatis.m3", map);
}
------------------------- mybatis.xml
<update id="m3" parameterType="java.util.HashMap">
    update tblMyBatis set
        name = #{name},
        address = #{address}
        where seq = #{seq}
</update>

■ 단일 값 반환 (1행 1열)

-------------------------- MyBatisController.java
@GetMapping("/m4.do")
public String m4(Model model) {

    //단일값 반환
    //- select count(*) from tblMyBatis

    int count = dao.m4();

    model.addAttribute("count", count);

    return "list";

}
-------------------------- MyBatisDAO.java
int m4();
-------------------------- MyBatisDAOImpl.java
@Override
public int m4() {

    //SqlSessionTemplate
    //- selectOne()
    //- selectList()

    return this.template.selectOne("mybatis.m4");
}
------------------------- mybatis.xml
<select id="m4" resultType="Integer">
    select count(*) from tblMyBatis
</select>

■ 다중 값 반환(1행 N열)

-------------------------- MyBatisController.java
@GetMapping("/m5.do")
public String m5(Model model, String seq) {

    //단중값 반환(1행 N열)
    //- select count(*) from tblMyBatis

    MyBatisDTO dto = dao.m5(seq);

    model.addAttribute("dto", dto);

    return "list";

}
-------------------------- MyBatisDAO.java
MyBatisDTO m5(String seq);
-------------------------- MyBatisDAOImpl.java
@Override
public MyBatisDTO m5(String seq) {

    return this.template.selectOne("mybatis.m5", seq);
}
------------------------- mybatis.xml
<select id="m5" parameterType="String" resultType="com.test.domain.MyBatisDTO">
    select * from tblMyBatis where seq = #{seq}
</select>

■ 다중 값 반환(N행 1열)

-------------------------- MyBatisController.java
@GetMapping("/m6.do")
public String m6(Model model) {

    //단중값 반환(N행 1열)
    //- select count(*) from tblMyBatis

    List<String> names = dao.m6();

    model.addAttribute("names", names);

    return "list";

}
-------------------------- MyBatisDAO.java
List<String> m6();
-------------------------- MyBatisDAOImpl.java
@Override
public List<String> m6() {

    //String sql = "";
    //stat = conn.createStatement()
    //rs = stat.executeQuery();
    //List<String> list...
    //while (rs.next()) {
    //list.add(rs.getString("name"))
    //return list;

    return this.template.selectList("mybatis.m6");
}
------------------------- mybatis.xml
<select id="m6" resultType="String">
    select name from tblMyBatis
</select>

■ 다중 값 반환(N행 N열)

-------------------------- MyBatisController.java
@GetMapping("/m7.do")
public String m7(Model model) {

    //단중값 반환(N행 N열)
    //- select count(*) from tblMyBatis

    List<MyBatisDTO> list = dao.m7();

    model.addAttribute("list", list);

    return "list";

}
-------------------------- MyBatisDAO.java
List<MyBatisDTO> m7();
-------------------------- MyBatisDAOImpl.java
@Override
public List<MyBatisDTO> m7() {

    return this.template.selectList("mybatis.m7");

}
------------------------- mybatis.xml
<select id="m7" resultType="com.test.domain.MyBatisDTO">
    select * from tblMyBatis
</select>

■ 테이블 컬럼 갯수(테이블) -> count(*)

#{key} : 데이터 바인딩 > 문자열 전송 > '문자열'
${key} : 식별자 바인딩 > 문자열 전송 > 문자열

-------------------------- MyBatisController.java
@GetMapping("/m8.do")
public String m8(Model model, String table) {

    //m8.do?table=tblMyBatis
    //- select count(*) from tblMyBatis

    //m8.do?table=employees
    //- select count(*) from employees

    int count = dao.m8(table);

    model.addAttribute("count", count);

    return "list";

}
-------------------------- MyBatisDAO.java
int m8(String table);
-------------------------- MyBatisDAOImpl.java
@Override
public int m8(String table) {

    return this.template.selectOne("mybatis.m8", table);
}
------------------------- mybatis.xml
<select id="m8" parameterType="String" resultType="Integer">
    select count(*) from ${table}
</select>

■ 조건절이 붙은 Select

-------------------------- MyBatisController.java
@GetMapping("/m9.do")
public String m9(Model model, int age) {

    //m9.do?age=25
    //- select * from tblMyBatis where age > 25

    List<MyBatisDTO> list = dao.m9(age);

    model.addAttribute("list", list);

    return "list";

}
-------------------------- MyBatisDAO.java
List<MyBatisDTO> m9(int age);
-------------------------- MyBatisDAOImpl.java
@Override
public List<MyBatisDTO> m9(int age) {

    return this.template.selectList("mybatis.m9", age);
}
------------------------- mybatis.xml
<select id="m9" parameterType="Integer" resultType="com.test.domain.MyBatisDTO">
    select * from tblMyBatis where age > #{age}
</select>
/* < : 사용하려면 Escape를 시켜주어야 한다.
<![CDATA[ '이 영역은 Escape 영역입니다.' ]]> 

<select id="m9" parameterType="Integer" resultType="com.test.domain.MyBatisDTO">
    select * from tblMyBatis 
        <![CDATA[ 
        where age < #{age}
        ]]>
</select>

*/

■ 특정 word가 포함된 Select

-------------------------- MyBatisController.java
@GetMapping("/m10.do")
public String m10(Model model, String word) {

    //m10.do?word=검색어
    //- select * from tblMyBatis where address like '%검색어%'

    List<MyBatisDTO> list = dao.m10(word);

    model.addAttribute("list", list);

    return "list";

}
-------------------------- MyBatisDAO.java
List<MyBatisDTO> m10(String word);
-------------------------- MyBatisDAOImpl.java
@Override
public List<MyBatisDTO> m10(String word) {

    return this.template.selectList("mybatis.m10", word);
}
------------------------- mybatis.xml
<select id="m10" parameterType="String" resultType="com.test.domain.MyBatisDTO">
    select * from tblMyBatis 
        where address like '%${word}%'
</select>

■ DTO Insert 정적 Ver

insert + dto -> 방금 insert한 PK 가져오는 방법

-------------------------- MyBatisController.java
@GetMapping("/m11.do")
public String m11(Model model) {

    //insert + dto
    //방금 insert한 PK 가져오는 방법

    MyBatisDTO dto = new MyBatisDTO();

    dto.setName("장보고");
    dto.setAge("23");
    dto.setAddress("바닷가");
    dto.setGender("m");

    int result = dao.m11(dto);

    model.addAttribute("result", result);

    return "list";

}
-------------------------- MyBatisDAO.java
int m11(MyBatisDTO dto);
-------------------------- MyBatisDAOImpl.java
@Override
public int m11(MyBatisDTO dto) {

    int result = this.template.insert("mybatis.m11", dto);

    System.out.println("방금 추가된 PK : " + dto.getSeq());

    return result;
}
------------------------- mybatis.xml
<insert id="m11" parameterType="com.test.domain.MyBatisDTO" >

    <selectKey keyProperty="seq" order="BEFORE" resultType="String">
        select seqMyBatis.nextVal from dual
    </selectKey>

    insert into tblMyBatis(seq, name, age, address, gender) 
        values (#{seq}, #{name}, #{age}, #{address}, #{gender})

</insert>

■ MyBatis 동적 쿼리

동적 태그
JSTL 유사

-------------------------- MyBatisController.java
@GetMapping("/m12.do")
public String m12(Model model, String type) {

  //1. if
  //2. choose(when, otherwise)
  //3. trim(where, set)
  //4. foreach

  //m12.do?type=1 > select first_name from employees
  //m12.do?type=1 > select last_name from employees

  List<String> elist = dao.m12(type);

  model.addAttribute("elist", elist);

  return "list";
}
-------------------------- MyBatisDAO.java
List<String> m12(String type);
-------------------------- MyBatisDAOImpl.java
@Override
public List<String> m12(String type) {

  return template.selectList("mybatis.m12", type);
}
------------------------- mybatis.xml
<select id="m12" resultType="String">

  <!--  
  <if test="type == 1">
  select first_name from employees   
  </if>

  <if test="type == 2">
  select last_name from employees   
  </if>
  -->

  select
    <if test="type == 1">first_name</if>
    <if test="type == 1">last_name</if>
  from employees

</select>

■ 동적으로 조건에 따른 처리하기

-------------------------- MyBatisController.java
@GetMapping("/m13.do")
public String m13(Model model, String column, String word) {

    //1. 이름
    //2. 주소
    //3. 나이

    Map<String,String> map = new HashMap<String,String>();

    map.put("column", column);
    map.put("word", word);

    List<MyBatisDTO> list = dao.m13(map);

    model.addAttribute("list", list);

    return "list";

}
-------------------------- MyBatisDAO.java
List<MyBatisDTO> m13(Map<String, String> map);
-------------------------- MyBatisDAOImpl.java
@Override
public List<MyBatisDTO> m13(Map<String, String> map) {

    return template.selectList("mybatis.m13", map);
}
------------------------- mybatis.xml
<select id="m13" resultType="com.test.domain.MyBatisDTO">

    select * from tblMybatis	
		/* 방법 1
        <if test="column == 'name'">
        where name = #{word}
        </if>

        <if test="column == 'age'">
        <![CDATA[
        where age <= #{word}
        ]]>
        </if>

        <if test="column == 'address'">
        where address like '%' || #{word} || '%'
        </if>
		*/
        //방법 2
        <choose>
            <when test="column == 'name'">
                where name = #{word}
            </when>
            <when test="column == 'age'">
                <![CDATA[
                where age <= #{word}
                ]]>
            </when>
            <otherwise>
                where address like '%' || #{word} || '%'
            </otherwise>
        </choose>
</select>

■ 동적으로 주소 검색 - 2

-------------------------- MyBatisController.java
@GetMapping("/m14.do")
public String m14(Model model, String address) {

    List<MyBatisDTO> list = dao.m14(address);

    model.addAttribute("list", list);

    return "list";

}
-------------------------- MyBatisDAO.java
List<MyBatisDTO> m14(String address);
-------------------------- MyBatisDAOImpl.java
@Override
public List<MyBatisDTO> m14(String address) {
    // TODO Auto-generated method stub
    return template.selectList("mybatis.m14", address);
}
------------------------- mybatis.xml
<select id="m14" resultType="com.test.domain.MyBatisDTO">

    <!-- 

        1. m14.do
            - select * from tblMyBatis
        2. m14.do?address=서울
            - select * from tblMyBatis where 조건

     -->

     select * from tblMyBatis
        <where>
            <if test="address != null">
                address like '%' || #{address} || '%'
            </if>
            <!-- if문의 보조 역할 - <trim> -->
            <trim prefix="and">
                rownum = 1
            </trim>
        </where>

</select>

■ forEach를 활용

-------------------------- MyBatisController.java
@GetMapping("/m15.do")
public String m15(Model model) {

    List<String> word = new ArrayList<String>();

    word.add("홍길동");
    word.add("장보고");

    List<MyBatisDTO> list = dao.m15(word);

    model.addAttribute("list", list);

    return "list";

}
-------------------------- MyBatisDAO.java
List<MyBatisDTO> m15(List<String> word);
-------------------------- MyBatisDAOImpl.java
@Override
public List<MyBatisDTO> m15(List<String> word) {

    return template.selectList("mybatis.m15", word);
}
------------------------- mybatis.xml
<select id="m15" resultType="com.test.domain.MyBatisDTO">

    select * from tblMyBatis
        where name in

        <foreach collection="word" item="name" open="(" close=")" separator=",">
            #{name}
        </foreach>

</select>

728x90

저작자표시 비영리 변경금지 (새창열림)

'Spring' 카테고리의 다른 글

Spring STEP 7 - Tiles (2)	2023.06.16
Spring STEP 6 - MyBatis 활용 게시판 만들기 (0)	2023.06.16
Spring STEP 4 - Spring MVC (0)	2023.06.14
Spring STEP 3 - DI & AOP (0)	2023.06.14
Spring STEP 2 - 스프링 구성요소 (2)	2023.06.13

내 블로그 - 관리자 홈 전환	`Q` `Q`
새 글 쓰기	`W` `W`

글 수정 (권한 있는 경우)	`E` `E`
댓글 영역으로 이동	`C` `C`

이 페이지의 URL 복사	`S` `S`
맨 위로 이동	`T` `T`
티스토리 홈 이동	`H` `H`
단축키 안내	`Shift` + `/` `⇧` + `/`

Spring STEP 5 - MyBatis 응용

'Spring' 카테고리의 다른 글

'Spring' 카테고리의 다른 글

티스토리툴바

단축키

내 블로그

블로그 게시글

모든 영역