Spring
Spring STEP 5 - MyBatis 응용
IT의 큰손
2023. 6. 15. 16:45
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