본문 바로가기
서버/Spring
[스프링(Spring)] MyBatis
Hana_h 2023. 6. 16. 00:49

// MyBatis(iBatis)
    - 영속성 계층(데이터베이스 조작 기술)
    - 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 추가

    - 이전 방식: WEB-INF\lib 폴더에 복사
    - 새로운 방식 : classpath에 external jar 추가 > Deployment Assembly > Maven Dependencies > Add

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


// Connection Pool, 커넥션풀

  1. Commons DBCP
  2. Tomcat DBCP
  3. 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 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="사용자명"></property>
    <property name="password" value="비밀번호"></property>
</bean>


    - "com.test.persistence" > DBCPTest

package com.test.persistence;

import java.sql.Connection;

import javax.sql.DataSource;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import lombok.extern.log4j.Log4j;

@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() {
		
		try {
			
			Connection conn = dataSource.getConnection();
			
			log.info(conn.isClosed());
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	
}
  • HikariCP

    - 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="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property>
    <property name="jdbcUrl" value="jdbc:log4jdbc:oracle:thin:@localhost:1521:xe"></property>
    <property name="username" value="사용자명"></property>
    <property name="password" value="비밀번호"></property>
</bean>
 
<!-- 커넥션 풀 -->
<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
	<constructor-arg ref="hikariConfig"></constructor-arg>
</bean>

// MyBatis 설정

- pom.xml > 의존 추가(4개)

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


- root-context.xml > 설정 추가

<bean id="sessionfactory" class="org.mybatis.spring.SqlSessionFactoryBean">
	<property name="dataSource" ref="dataSource"></property>
	<property name="mapperLocations" value="classpath*:mapper/*.xml"></property>
</bean>
	
<bean class="org.mybatis.spring.SqlSessionTemplate">
	<constructor-arg ref="sessionfactory"></constructor-arg>
</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">

	<!-- 
		모든 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>


~ src/test/java > com.test.persistence > MapperTest.java

package com.test.persistence;

import static org.junit.Assert.assertNotNull;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import lombok.extern.log4j.Log4j;

@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);
		
		// SAXParseException > XML에서 오류가 난 것!
		// Well-formed XML > 반드시 루트 태그가 존재해야 한다.
		
		//SqlSessionTemplate
		SqlSession session = sqlSessionFactory.openSession();
		
		//Namespace.id
		String time = session.selectOne("test.time");
		log.info(time);
		
	}
	
}

// MyBatis 각종 SQL 실행하기

* 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()) {}

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

package com.test.controller;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;

import com.test.domain.MyBatisDTO;
import com.test.persistence.MyBatisDAO;

@Controller
public class MyBatisController {
   
   //MyBatisController > (의존) > MyBatisDAO
   
   @Autowired
   private MyBatisDAO dao;
   
   
   @GetMapping("/test.do")
   public String test() {
      
      System.out.println(this.dao == null);
      
      this.dao.test();
      
      return "list";
   }
   
   //반환값(X), 매개변수(X)
   
   @GetMapping("/m1.do")
   public String m1() {
      
      this.dao.m1();
      
      return "list";
   }
   
   
   @GetMapping("/add.do")
   public String add() {
	   
	   return "add";
   }
   
   @PostMapping("/addok.do")
   public String addok(MyBatisDTO dto) {
	   
	   //this.dao.add(dto);
	   this.dao.add(dto);
	   
	   return "addok";
   }
   
   @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";
   }

   @GetMapping("/m3.do")
   public String m3() {
      
	   //3 하하하 37 서울시 동대문구 ,
	   // > 수정하기
	   
	   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";
   }
   
   @GetMapping("/m4.do")
   public String m4(Model model) {
      
	   //단일값 반환(1행 1열)
	   //- select count(*) from tblMyBatis
	   
	   int count = dao.m4();
	   
	   model.addAttribute("count", count);
	   
	   return "list";
   }
   
   
   @GetMapping("/m5.do")
   public String m5(Model model, String seq) {
	   
	   //다중값 반환(1행 n열)
	   //- select * from tblMyBatis where seq = 1
	   
	   MyBatisDTO dto = dao.m5(seq);
	   
	   model.addAttribute("dto", dto);
	   
	   return "list";
   }
   
   @GetMapping("/m6.do")
   public String m6(Model model) {
	   
	   //다중 반환(n행 1열)
	   //- select name from tblMyBatis where seq = 1
	   
	   List<String> names = dao.m6();
	   
	   model.addAttribute("names", names);
	   
	   return "list";
   }
	   
   @GetMapping("/m7.do")
   public String m7(Model model) {

	   //다중 반환(n행 n열)
	   //- select * from tblMyBatis
	   
	   List<MyBatisDTO> list = dao.m7();
	   
	   model.addAttribute("list", list);
	   
	   return "list";
   }
   
   @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";
   }
   
   @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";
   }
   
   @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";
   }
   
   @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 "result";
   }
   
}


~ "com.test.persistence" > MyBatisDAO.java(I)

package com.test.persistence;

import java.util.List;
import java.util.Map;

import com.test.domain.MyBatisDTO;

public interface MyBatisDAO {
	
	void test();
	
	void m1();

	void add(MyBatisDTO dto);

	int m2(String seq);

	int m3(Map<String, String> map);

	int m4();

	MyBatisDTO m5(String seq);

	List<String> m6();

	List<MyBatisDTO> m7();

	int m8(String table);

	List<MyBatisDTO> m9(int age);

	List<MyBatisDTO> m10(String word);

	int m11(MyBatisDTO dto);
	
}


 > MyBatisDAOImpl.java(C)

package com.test.persistence;

import java.util.List;
import java.util.Map;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.test.domain.MyBatisDTO;

//@Component
@Repository
public class MyBatisDAOImpl implements MyBatisDAO {
	
	//MyBatisDAO > (의존) > SqlSessionTemplate
	
	@Autowired
	private SqlSessionTemplate template;
	
	@Override
	public void test() {
		System.out.println(this.template == null);
	}

	@Override
	public void m1() {
		
		//MyBatis(SqlSessionTemplate) > XML 기반 작업(XMl Mapper)
        
		//this.template.insert("매퍼 네임스페이스.쿼리 ID");
		this.template.insert("mybatis.m1");
		
	}

	@Override
	public void add(MyBatisDTO dto) {
		
		this.template.insert("mybatis.add", dto);
		
	}

	@Override
	public int m2(String seq) {
				
		return this.template.delete("mybatis.m2", seq);
	}

	@Override
	public int m3(Map<String, String> map) {
		
		return this.template.update("mybatis.m3", map);
	}

	@Override
	public int m4() {
		
		//SqlSessionTemplate
		// - selectOne()
		// - selectList()
		
		return this.template.selectOne("mybatis.m4");
	
	}

	@Override
	public MyBatisDTO m5(String seq) {
				
		return this.template.selectOne("mybatis.m5", seq);
	}

	@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");
	}

	@Override
	public List<MyBatisDTO> m7() {
				
		return this.template.selectList("mybatis.m7");
	}
	
	@Override
	public int m8(String table) {
		
		return this.template.selectOne("mybatis.m8", table);
	}
	
	@Override
	public List<MyBatisDTO> m9(int age) {
		
		return this.template.selectList("mybatis.m9", age);
	}
	
	@Override
	public List<MyBatisDTO> m10(String word) {

		return this.template.selectList("mybatis.m10", word);
	}
	
	@Override
	public int m11(MyBatisDTO dto) {
		
		int result = this.template.insert("mybatis.m11", dto);
		
		System.out.println("방금 추가된 PK: " + dto.getSeq());
		
		return result;
	}
	
}


~ "com.test.domain" > 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;

}


~ src/main/resources > mapper> mybatis.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="mybatis">
	<insert id="m1">
		insert into tblMyBatis(seq, name, age, address, gender) values (seqMyBatis.nextVal, '하하하', 23, '서울시 강동구', 'm')
	</insert>
	
	<!-- parameterType은 생략 가능 -->
	<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 id="m2" parameterType="String">
		delete from tblMyBatis where seq = #{seq}
	</delete>
	
	<update id="m3" parameterType="java.util.HashMap">
		update tblMyBatis set
			name = #{name},
			address = #{address}
				where seq = #{seq}
	</update>
	
	<!-- resultType은 생략 불가!! 반드시 적어야 한다. + 클래스형으로 -->
	<select id="m4" resultType="Integer">
		select count(*) from tblMyBatis		
	</select>
	
	<select id="m5" parameterType="String" resultType="com.test.domain.MyBatisDTO">
		select * from tblMyBatis where seq = #{seq}
	</select>
	
	<select id="m6" resultType="String">
		select name from tblMybatis
	</select>
	
	<select id="m7" resultType="com.test.domain.MyBatisDTO">
		select * from tblMyBatis		
	</select>
	
	<select id="m8" parameterType="String" resultType="Integer">
		<!--		
			#{key}: 데이터 바인딩 > 문자열 전송 > '문자열' 
			${key}: 식별자 바인딩 > 문자열 전송 > 문자열
		-->	
		select count(*) from ${table}
	</select>
	
	<select id="m9" parameterType="Integer" resultType="com.test.domain.MyBatisDTO">
	
		select * from tblMyBatis
			<![CDATA[  
			where age < #{age}
			]]>
			
	</select>	
	
	<select id="m10" parameterType="String" resultType="com.test.domain.MyBatisDTO">
	
		select * from tblMyBatis 
			where address like '%${word}%'
			<!-- where address like '%' || #{word} || '%'  -->
	
	</select>
	
	<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>
	
</mapper>


~ views > list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix = "c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://me2.do/5BvBFJ57">
<style>

</style>
</head>
<body>

	<h1>결과</h1>
	
	<div class="message" title="삭제">
		${result}
	</div>
	
	<div class="message" title="count">
		${count} 
	</div>
	
	<div class="message" title="dto">
		<div>${dto.seq}</div>
		<div>${dto.name}</div>
		<div>${dto.age}</div>
		<div>${dto.address}</div>
		<div>${dto.gender}</div>
	</div>
	
	<div class="list">
		<c:forEach items="${names}" var="name">
		<div>${name}</div>
		</c:forEach>
	</div>
	
	<table>
		<tr>
			<th>번호</th>
			<th>이름</th>
			<th>나이</th>
			<th>주소</th>
			<th>성별</th>
		</tr>
		<c:forEach items="${list}" var="dto">
		<tr>
			<td>${dto.seq}</td>
			<td>${dto.name}</td>
			<td>${dto.age}</td>
			<td>${dto.address}</td>
			<td>${dto.gender}</td>
		</tr>
		</c:forEach>
	</table>


<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.4/jquery.min.js"></script>
<script>

</script>
</body>
</html>


~ views > add.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix = "c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://me2.do/5BvBFJ57">
<style>

</style>
</head>
<body>

	<h1>입력</h1>
	
	<form method="POST" action="/mybatis/addok.do">
	<table class="vertical">
		<tr>
			<th>이름</th>
			<td><input type="text" name="name" required></td>
		</tr>
		<tr>
			<th>나이</th>
			<td><input type="text" name="age" required></td>
		</tr>
		<tr>
			<th>주소</th>
			<td><input type="text" name="address" required></td>
		</tr>
		<tr>
			<th>성별</th>
			<td>
				<select name="gender">
					<option value="m">남자
					<option value="f">여자
				</select>
			</td>
		</tr>
	</table>
	<div>
		<button>등록하기</button>
	</div>	
	</form>


<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.4/jquery.min.js"></script>
<script>

</script>
</body>
</html>


~ views > addok.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix = "c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://me2.do/5BvBFJ57">
<style>

</style>
</head>
<body>

	<h1>게시판 <small>글쓰기</small></h1>
	
	<div>글쓰기 완료</div>


<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.4/jquery.min.js"></script>
<script>

</script>
</body>
</html>

// log4jdbc-log4j2 설정하기
    - log4j > JDBC 감시

~ src/main/resources > log4jdbc.log4j2.properties

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


~ src/test/resources > log4j.xml
~ src/main/resources > log4j.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

	<!-- Appenders -->
	<appender name="console" class="org.apache.log4j.ConsoleAppender">
		<param name="Target" value="System.out" />
		<layout class="org.apache.log4j.PatternLayout">
			<param name="ConversionPattern" value="%-5p: %c - %m%n" />
		</layout>
	</appender>
	
	<!-- Application Loggers -->
	<logger name="com.test.mybatis">
		<level value="info" />
	</logger>
	
	<!-- 3rdparty Loggers -->
	<logger name="org.springframework.core">
		<level value="info" />
	</logger>	
	
	<logger name="org.springframework.beans">
		<level value="info" />
	</logger>
	
	<logger name="org.springframework.context">
		<level value="info" />
	</logger>

	<logger name="org.springframework.web">
		<level value="info" />
	</logger>

	<!-- Root Logger -->
	<root>
		<priority value="info" />
		<appender-ref ref="console" />
	</root>
	
	<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>
	
</log4j:configuration>

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

    ~ pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.test</groupId>
	<artifactId>simple</artifactId>
	<name>MyBatisSimpleTest</name>
	<packaging>war</packaging>
	<version>1.0.0-BUILD-SNAPSHOT</version>
	<properties>
		<java-version>11</java-version>
		<org.springframework-version>5.0.7.RELEASE</org.springframework-version>
		<org.aspectj-version>1.6.10</org.aspectj-version>
		<org.slf4j-version>1.6.6</org.slf4j-version>
	</properties>
	<dependencies>
		<!-- Spring -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${org.springframework-version}</version>
			<exclusions>
				<!-- Exclude Commons Logging in favor of SLF4j -->
				<exclusion>
					<groupId>commons-logging</groupId>
					<artifactId>commons-logging</artifactId>
				</exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>

		<!-- AspectJ -->
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjrt</artifactId>
			<version>${org.aspectj-version}</version>
		</dependency>

		<!-- Logging -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>${org.slf4j-version}</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>jcl-over-slf4j</artifactId>
			<version>${org.slf4j-version}</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>${org.slf4j-version}</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>

		<!-- @Inject -->
		<dependency>
			<groupId>javax.inject</groupId>
			<artifactId>javax.inject</artifactId>
			<version>1</version>
		</dependency>

		<!-- Servlet / JSP -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
			<version>3.1.0</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet.jsp</groupId>
			<artifactId>javax.servlet.jsp-api</artifactId>
			<version>2.3.3</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>

		<!-- Test -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>

		<!-- Lombok -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.28</version>
			<scope>provided</scope>
		</dependency>

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

		<!-- AOP -->
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjrt</artifactId>
			<version>${org.aspectj-version}</version>
		</dependency>

		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjweaver</artifactId>
			<version>${org.aspectj-version}</version>
		</dependency>

		<!-- JSON -->
		<dependency>
			<groupId>com.fasterxml.jackson.core</groupId>
			<artifactId>jackson-databind</artifactId>
			<version>2.15.0</version>
		</dependency>

		<dependency>
			<groupId>com.fasterxml.jackson.dataformat</groupId>
			<artifactId>jackson-dataformat-xml</artifactId>
			<version>2.15.0</version>
		</dependency>

		<dependency>
			<groupId>com.google.code.gson</groupId>
			<artifactId>gson</artifactId>
			<version>2.10.1</version>
		</dependency>

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

		<!-- MyBatis -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<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>

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

	</dependencies>
	<build>
		<plugins>
			<plugin>
				<artifactId>maven-eclipse-plugin</artifactId>
				<version>2.9</version>
				<configuration>
					<additionalProjectnatures>
						<projectnature>org.springframework.ide.eclipse.core.springnature</projectnature>
					</additionalProjectnatures>
					<additionalBuildcommands>
						<buildcommand>org.springframework.ide.eclipse.core.springbuilder</buildcommand>
					</additionalBuildcommands>
					<downloadSources>true</downloadSources>
					<downloadJavadocs>true</downloadJavadocs>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.5.1</version>
				<configuration>
					<source>11</source>
					<target>11</target>
					<compilerArgument>-Xlint:all</compilerArgument>
					<showWarnings>true</showWarnings>
					<showDeprecation>true</showDeprecation>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.codehaus.mojo</groupId>
				<artifactId>exec-maven-plugin</artifactId>
				<version>1.2.1</version>
				<configuration>
					<mainClass>org.test.int1.Main</mainClass>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>


    ~ ojdbc6.jar

    ~ web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee https://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">

	<!-- The definition of the Root Spring Container shared by all Servlets and Filters -->
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>/WEB-INF/spring/root-context.xml</param-value>
	</context-param>

	<!-- Creates the Spring Container shared by all Servlets and Filters -->
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>

	<!-- Processes application requests -->
	<servlet>
		<servlet-name>appServlet</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>/WEB-INF/spring/appServlet/servlet-context.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>

	<servlet-mapping>
		<servlet-name>appServlet</servlet-name>
		<url-pattern>/</url-pattern>
	</servlet-mapping>

	<filter>
		<filter-name>encodingFilter</filter-name>
		<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
		<init-param>
			<param-name>encoding</param-name>
			<param-value>UTF-8</param-value>
		</init-param>
	</filter>
	<filter-mapping>
		<filter-name>encodingFilter</filter-name>
		<url-pattern>*</url-pattern>
	</filter-mapping>

</web-app>


    ~ XML Mapper 건너뜀

    ~ "src/main/resources" > log4jdbc.log4j2.properties

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


    ~ log4j level

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

	<!-- Appenders -->
	<appender name="console" class="org.apache.log4j.ConsoleAppender">
		<param name="Target" value="System.out" />
		<layout class="org.apache.log4j.PatternLayout">
			<param name="ConversionPattern" value="%-5p: %c - %m%n" />
		</layout>
	</appender>
	
	<!-- Application Loggers -->
	<logger name="com.test.simple">
		<level value="info" />
	</logger>
	
	<!-- 3rdparty Loggers -->
	<logger name="org.springframework.core">
		<level value="info" />
	</logger>
	
	<logger name="org.springframework.beans">
		<level value="info" />
	</logger>
	
	<logger name="org.springframework.context">
		<level value="info" />
	</logger>

	<logger name="org.springframework.web">
		<level value="info" />
	</logger>
	
	<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>

	<!-- Root Logger -->
	<root>
		<priority value="warn" />
		<appender-ref ref="console" />
	</root>
	
</log4j:configuration>


    ~ Mapper 인터페이스 만들기
        - src/main/java > "com.test.mapper" > TimeMapper.java(I)
        - SQL 실행하는 담당자

package com.test.mapper;

import org.apache.ibatis.annotations.Select;

public interface TimeMapper {

	//인터페이스 매퍼+ 어노테이션(SQL)
	@Select("select sysdate from dual")
	public String getTime();
	
	//XML 매퍼 파일 사용하기
	public String getTime2();
	
}


    ~ Mapper 인터페이스를 인식할 수 있게
        - root-context.xml 수정

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
	xsi:schemaLocation="http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
		http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd">

	<!-- Root Context: defines shared resources visible to all other web components -->
	<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
		<property name="driverClassName"
			value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property>
		<property name="jdbcUrl"
			value="jdbc:log4jdbc: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>

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

	<!-- <bean class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg ref="sessionfactory"></constructor-arg>
	</bean> -->
	
	<!--
		1. SQL 어디 있는지?
		2. SqlSessionTemplate > 누가 SQL 실행?
			- <mybatis-spring>
			- TimeMapper(I) > 인식 > SQL 실행 
	-->
	
	
	<!-- 인터페이스를 넣어둔 패키지를 지정 -->
	<mybatis-spring:scan base-package="com.test.mapper"/>
	
</beans>


    ~ src/main/resources > 인터페이스 매퍼가 들어있는 패키지와 동일한 구조의 폴더 생성 > TimeMapper.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">

<mapper namespace="com.test.mapper.TimeMapper">

	<select id="getTime2" resultType="String">
		select to_char(sysdate, 'hh24:mm:ss') from dual
	</select>

</mapper>

// Crud

    - 파일 생성
        ~ MyBatisSimpleTest > script.sql

drop table tblAddress;

drop sequence seqAddress;

create table tblAddress (
    seq number primary key,
    name varchar2(30) not null,
    age number(3) not null,
    address varchar2(300) not null
);

create sequence seqAddress;

create table tblEmail(
    seq number primary key,
    email varchar2(100) not null,
    pseq number not null references tblAddress(seq)
);

create sequence seqEmail;


        ~ "com.test.controller" > AddressController.java

package com.test.controller;

import java.util.Arrays;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;

import com.test.domain.AddressDTO;
import com.test.service.AddressService;

@Controller
public class AddressController {

	@Autowired
	private AddressService service;
	
	@GetMapping("/list.do")
	public String list(Model model) {
		
		//Address(1) : Email(N)
		List<AddressDTO> list = service.list();
		
		model.addAttribute("list", list);
		
		return "list";
	}
	
	@GetMapping("/add.do")
	public String add() {
		
		return "add";
	}
	
	@PostMapping("/addok.do")
	public String addok(Model model, AddressDTO dto, String[] emailList) {
		
		System.out.println(dto);
		System.out.println(Arrays.toString(emailList));
		
		service.add(dto, emailList);
		
		return "redirect:/list.do";
	}
	
	
	@GetMapping("/view.do")
	public String view() {
		
		return "view";
	}
	
}


        ~ "com.test.service' > AddressService.java(I)
                                        > AddressServiceImpl.java(C)

package com.test.service;

import java.util.List;

import com.test.domain.AddressDTO;

public interface AddressService {

	void add(AddressDTO dto, String[] emailList);

	List<AddressDTO> list();

}

 

package com.test.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.test.domain.AddressDTO;
import com.test.domain.EmailDTO;
import com.test.mapper.AddressMapper;

@Service
public class AddressServiceImpl implements AddressService {
	
	@Autowired
	private AddressMapper mapper;
	
	@Override
	public void add(AddressDTO dto, String[] emailList) {
		
		mapper.add(dto);
		
		for(int i=0; i<emailList.length; i++) {
			
			if(emailList[i].equals("")) {
				continue;
			}
			
			EmailDTO edto = new EmailDTO();
			edto.setPseq(dto.getSeq());
			edto.setEmail(emailList[i]);
			
			mapper.addEmail(edto);
			
		}
		
	}
	
	@Override
	public List<AddressDTO> list() {
		
		//tblAddress(1) : tblEmail(N)
		//1. 직접 구현
		//2. MyBatis
		
		/*
		List<AddressDTO> list = mapper.list();
		
		for(AddressDTO dto : list) {
			
			List<EmailDTO> elist = mapper.elist(dto.getSeq());
			
			dto.setEmail(elist);
			
		}
		*/
		
		 List<AddressDTO> list = mapper.list2();
		
		return list;
	}

}


        ~ "com.test.mapper" > AddressMapper.java(I)  (= AddressDAO 역할)

package com.test.mapper;

import java.util.List;

import com.test.domain.AddressDTO;
import com.test.domain.EmailDTO;

public interface AddressMapper {

	public void add(AddressDTO dto);

	public void addEmail(EmailDTO edto);

	public List<AddressDTO> list();

	public List<EmailDTO> elist(String pseq);

	public List<AddressDTO> list2();
	
}


        ~ "com.test.domain" > AddressDTO.java
                                         > EmailDTO.java

package com.test.domain;

import java.util.List;

import lombok.Data;

@Data
public class AddressDTO {
	
	private String seq;
	private String name;
	private String age;
	private String address;
	
	private List<EmailDTO> email;

}

 

package com.test.domain;

import lombok.Data;

@Data
public class EmailDTO {

	private String seq;
	private String email;
	private String pseq;
	
}


        ~ src/main/resources > com > test> mapper > AddressMapper.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">

<mapper namespace="com.test.mapper.AddressMapper">

	<insert id="add">
	
		<selectKey keyProperty="seq" order="BEFORE" resultType="String">
			select seqAddress.nextVal from dual
		</selectKey>
		
		insert into tblAddress (seq, name, age, address)
			values (#{seq}, #{name}, #{age}, #{address})
	
	</insert>
	
	<insert id="addEmail">
		
		insert into tblEmail (seq, email, pseq)
			values (seqEmail.nextVal, #{email}, #{pseq})
		
	</insert>
	
	
	<select id="list" resultType="com.test.domain.AddressDTO">
		
		select * from tblAddress
			order by seq asc
		
	</select>
	
	<select id="elist" resultType="com.test.domain.EmailDTO">
		
		select * from tblEmail
			where pseq = #{pseq}
				order by seq asc
		
	</select>
	
	<resultMap type="com.test.domain.AddressDTO" id="addressMap">
	<!--
		property : dto의 멤버 변수 이름
		column : 쿼리문의 컬럼명
	-->
		<id property="seq" column="seq"/>
		<result property="seq" column="seq" />
		<result property="name" column="name" />
		<result property="age" column="age" />
		<result property="address" column="address" />
		<collection property="email" resultMap="emailMap"></collection>
	</resultMap>
	
	<resultMap type="com.test.domain.EmailDTO" id="emailMap">
		<!-- <id property="seq" column="seq" /> -->
		<result property="seq" column="seq" />
		<result property="email" column="email" />
		<result property="pseq" column="pseq" />
	</resultMap>
	
	<select id="list2" resultMap="addressMap">
		select 
			a.seq,
			a.name,
			a.age,
			a.address,
			e.email
		from tblAddress a
			left outer join tblEmail e
				on a.seq = e.pseq
					order by a.seq asc
	</select>

</mapper>


        ~ views > list.jsp
                     > add.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix = "c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://me2.do/5BvBFJ57">
<style>
	input[name=emailList] {
		display: block;
		margin-bottom: 5px;
	}
	

</style>
</head>
<body>

	<h1>주소록 <small>등록하기</small></h1>
	
	<form method="POST" action="/simple/addok.do">
	<table class="vertical">
		<tr>
			<th>이름</th>
			<td><input type="text" name="name" required></td>
		</tr>
		<tr>
			<th>나이</th>
			<td><input type="number" name="age" min="0" max="150" required class="short"></td>
		</tr>	
		<tr>
			<th>주소</th>
			<td><input type="text" name="address" required class="full"></td>
		</tr>			
		<tr>
			<th>이메일</th>
			<td>
				<input type="text" name="emailList">
				<input type="text" name="emailList">
				<input type="text" name="emailList">
			</td>
		</tr>
	</table>
	
	<div>
		<input type="button" value="돌아가기" onclick="location.href='/simple/list.do';">		
		<input type="submit" value="등록하기">		
	</div>
	</form>


<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.4/jquery.min.js"></script>
<script>

</script>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix = "c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" href="https://me2.do/5BvBFJ57">
<style>

</style>
</head>
<body>

	<h1>주소록 <small>목록보기</small></h1>
	
	<table>
		<tr>
			<th>번호</th>
			<th>이름</th>
			<th>나이</th>
			<th>주소</th>
			<th>이메일</th>
		</tr>
		<c:forEach items="${list}" var="dto">
		<tr>
			<td>${dto.seq}</td>
			<td>${dto.name}</td>
			<td>${dto.age}</td>
			<td>${dto.address}</td>
			<td>
				<c:forEach items="${dto.email}" var="edto">
				<div>${edto.email}</div>
				</c:forEach>
			</td>
		</tr>
		</c:forEach>
	</table>

	<div>
		<input type="button" value="등록하기" class="add" onclick="location.href='/simple/add.do';">
	</div>

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.4/jquery.min.js"></script>
<script>

</script>
</body>
</html>

'서버 > Spring' 카테고리의 다른 글

[Spring Security] 로그인, 로그아웃, 계정 정보  (0) 2023.06.21
[스프링(Spring)] 파일 업로드 / 다운로드  (0) 2023.06.20
[스프링(Spring)] MVC 에러 처리  (0) 2023.06.16
[스프링(Spring)] MVC 데이터 수신 및 전송  (1) 2023.06.15
[스프링(Spring)] AOP  (1) 2023.06.15

'서버/Spring' Related Articles

티스토리툴바