JAVA
18.mybatis
네스이
2022. 8. 8. 16:31
2022.08.08.월
1.mybatis
//deptMapper.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.mybatis.dept.DeptMapper">
<select id="list" resultType="Dept">
<![CDATA[
select deptno, dname, loc
from dept
order by deptno desc
]]>
</select>
<select id="selectByDeptno"
resultType="com.dto.DeptDTO" parameterType="int">
<![CDATA[
select deptno, dname, loc
from dept
where deptno < #{deptno}
order by deptno desc
]]>
</select>
<select id="selectByDeptnoAndDname"
resultType="Dept" parameterType="Dept">
<![CDATA[
select deptno, dname, loc
from dept
where deptno < #{deptno} and dname = #{dname}
order by deptno desc
]]>
</select>
<select id="selectByDeptnoAndDnameMap"
resultType="Dept" parameterType="hashmap">
<![CDATA[
select deptno, dname, loc
from dept
where deptno < #{xxx} and dname = #{yyy}
order by deptno desc
]]>
</select>
<select id="listPage" resultType="Dept">
<![CDATA[
select deptno, dname, loc
from dept
order by deptno desc
]]>
</select>
<select id="selectOneByDeptno" resultType="Dept" parameterType="int">
<![CDATA[
select deptno, dname, loc
from dept
where deptno = #{deptno}
]]>
</select>
<insert id="deptInsert" parameterType="Dept">
<![CDATA[
insert into dept(deptno, dname, loc)
values(#{deptno}, #{dname}, #{loc})
]]>
</insert>
<insert id="deptInsertMap" parameterType="hashmap">
<![CDATA[
insert into dept(deptno, dname, loc)
values(#{x}, #{y}, #{z})
]]>
</insert>
<update id="deptUpdate" parameterType="Dept">
<![CDATA[
update dept
set dname=#{dname}, loc=#{loc}
where deptno=#{deptno}
]]>
</update>
<delete id="deleteByDeptno" parameterType="int">
<![CDATA[
delete dept
where deptno=#{deptno}
]]>
</delete>
<delete id="deleteByDeptnoOrDname" parameterType="Dept">
<![CDATA[
delete dept
where deptno=#{deptno} or dname=#{dname}
]]>
</delete>
</mapper>//EmlMapper.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.mybatis.emp.EmpMapper">
<!-- 컬럼명과 DTO의 변수명이 다를때 사용 -->
<resultMap type="Emp" id="xxx">
<id property="no" column="empno"/>
<result property="ename" column="ename"/>
<result property="salary" column="sal"/>
</resultMap>
<!-- ########################### -->
<select id="list" resultType="Emp">
<![CDATA[
select empno as no, ename, sal as salary
from emp
]]>
</select>
<select id="list2" resultMap="xxx">
<![CDATA[
select empno, ename, sal
from emp
]]>
</select>
</mapper>-insert
import java.io.IOException;
import java.io.InputStream;
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 DeptInsertMain {
public static void main(String[] args)
{
//Mybatis DB 연동
//Configuration.xml 읽기
String resource = "com/config/Configuration.xml";
InputStream inputStream = null;
try
{
inputStream = Resources.getResourceAsStream(resource);
}
catch (IOException e)
{
e.printStackTrace();
}
SqlSessionFactory x = new SqlSessionFactoryBuilder().build(inputStream);
//sqlSessionFactory 에서 SqlSession 얻기
SqlSession session = x.openSession();
/*
int n = session.insert(String id);
int n = session.insert(String id, Object obj);
*/
// //1.DeptDTO 파라미터
// DeptDTO dto = new DeptDTO();
// dto.setDeptno(55);
// dto.setDname("관리");
// dto.setLoc("서울");
// int n = session.insert("deptInsert", dto);
// session.commit();
// System.out.println(n);
//2.HashMap 파라미터
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("x", 56);
map.put("y", "개발");
map.put("z", "제주");
int n = session.insert("deptInsertMap", map);
session.commit();
System.out.println(n);
//최종적으로 session을 close해야 됨
session.close();
}
}-update
import java.io.IOException;
import java.io.InputStream;
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 DeptUpdateMain {
public static void main(String[] args)
{
//Mybatis DB 연동
//Configuration.xml 읽기
String resource = "com/config/Configuration.xml";
InputStream inputStream = null;
try
{
inputStream = Resources.getResourceAsStream(resource);
}
catch (IOException e)
{
e.printStackTrace();
}
SqlSessionFactory x = new SqlSessionFactoryBuilder().build(inputStream);
//sqlSessionFactory 에서 SqlSession 얻기
SqlSession session = x.openSession();
/*
int n = session.update(String id);
int n = session.update(String id, Object obj);
*/
//1.DeptDTO 파라미터
DeptDTO dto = new DeptDTO();
dto.setDeptno(56);
dto.setDname("관리");
dto.setLoc("서울");
int n = session.update("deptUpdate", dto);
session.commit();
System.out.println(n);
//최종적으로 session을 close해야 됨
session.close();
}
}-delete
import java.io.IOException;
import java.io.InputStream;
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 DeptDeleteMain {
public static void main(String[] args)
{
//Mybatis DB 연동
//Configuration.xml 읽기
String resource = "com/config/Configuration.xml";
InputStream inputStream = null;
try
{
inputStream = Resources.getResourceAsStream(resource);
}
catch (IOException e)
{
e.printStackTrace();
}
SqlSessionFactory x = new SqlSessionFactoryBuilder().build(inputStream);
//sqlSessionFactory 에서 SqlSession 얻기
SqlSession session = x.openSession();
/*
int n = session.delete(String id);
int n = session.delete(String id, Object obj);
*/
int n = session.delete("deleteByDeptno", 56);
DeptDTO dto = new DeptDTO();
dto.setDeptno(55);
dto.setDname("개발");
n = session.delete("deleteByDeptnoOrDname", dto);
session.commit();
System.out.println(n);
//최종적으로 session을 close해야 됨
session.close();
}
}-타입 별칭사용
//configuration.xml
<!-- DTO 별칭 -->
<typeAliases>
<typeAlias type="com.dto.DeptDTO" alias="Dept"/>
</typeAliases>2.mybatis 동적 sql
import java.io.IOException;
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;
import com.dto.EmpDTO;
public class EmpDynamicMain {
public static void main(String[] args)
{
//Mybatis DB 연동
//Configuration.xml 읽기
String resource = "com/config/Configuration.xml";
InputStream inputStream = null;
try
{
inputStream = Resources.getResourceAsStream(resource);
}
catch (IOException e)
{
e.printStackTrace();
}
SqlSessionFactory x = new SqlSessionFactoryBuilder().build(inputStream);
//sqlSessionFactory 에서 SqlSession 얻기
SqlSession session = x.openSession();
// //1.update문
// EmpDTO dto = new EmpDTO();
// dto.setEmpno(7369); //고정
//
// //dto.setSal(8000);
// dto.setEname("SMITH");
//
// int n = session.update("com.mybatis.emp.EmpMapper.updateAuthorIfNecessary", dto);
// session.commit();
// //3.다중 insert문
// DeptDTO dto1 = new DeptDTO(1, "AAA", "AAA");
// DeptDTO dto2 = new DeptDTO(2, "BBB", "BBB");
// DeptDTO dto3 = new DeptDTO(3, "CCC", "CCC");
// List<DeptDTO> list = Arrays.asList(dto1, dto2, dto3);
//
// int n
// = session.insert("com.mybatis.emp.EmpMapper.deptMultiDelete", list);
// System.out.println("저장갯수 : " + n);
// session.commit();
// //4.다중 delete문
// DeptDTO dto1 = new DeptDTO();
// DeptDTO dto2 = new DeptDTO();
// dto1.setDeptno(1);
// dto2.setDeptno(2);
// List<DeptDTO> list = Arrays.asList(dto1, dto2);
// int n2 = session.delete("com.mybatis.emp.EmpMapper.deptMultiDelete", list);
// System.out.println("삭제갯수 : " + n2);
// session.commit();
// //5.다중 delete문2
// List<String> list = Arrays.asList("BBB", "CCC", "ZZZ");
// int n3 = session.delete("com.mybatis.emp.EmpMapper.deptMultiDelete2", list);
// System.out.println("삭제갯수 : " + n3);
// session.commit();
// //6.다중 update
// List<Integer> list = Arrays.asList(7369, 7499, 7521);
// int n4 = session.update("com.mybatis.emp.EmpMapper.empMultiUpdate", list);
// System.out.println("수정갯수 : " + n4);
// session.commit();
//7.다중 select
List<Integer> list = Arrays.asList(10, 20, 30);
List<DeptDTO> deptlist
= session.selectList("com.mybatis.emp.EmpMapper.deptMultiSelect", list);
System.out.println(deptlist);
//최종적으로 session을 close해야 됨
session.close();
}
}3.mybatis MVC패턴
-config(*.xml)
//properties.xml
jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:xe
jdbc.userid=SCOTT
jdbc.passwd=TIGER//configuration.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- jdbc.properties 파일 설정 -->
<properties resource="com/config/jdbc.properties"></properties>
<!-- DTO 별칭 -->
<typeAliases>
<typeAlias type="com.dto.DeptDTO" alias="Dept"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.userid}"/>
<property name="password" value="${jdbc.passwd}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/config/DeptMapper.xml"/>
</mappers>
</configuration>//DeptMapper.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.mybatis.dept.DeptMapper">
<select id="list" resultType="Dept">
<![CDATA[
select deptno, dname, loc
from dept
order by deptno desc
]]>
</select>
<select id="selectByDeptno"
resultType="com.dto.DeptDTO" parameterType="int">
<![CDATA[
select deptno, dname, loc
from dept
where deptno < #{deptno}
order by deptno desc
]]>
</select>
<select id="selectByDeptnoAndDname"
resultType="Dept" parameterType="Dept">
<![CDATA[
select deptno, dname, loc
from dept
where deptno < #{deptno} and dname = #{dname}
order by deptno desc
]]>
</select>
<select id="selectByDeptnoAndDnameMap"
resultType="Dept" parameterType="hashmap">
<![CDATA[
select deptno, dname, loc
from dept
where deptno < #{xxx} and dname = #{yyy}
order by deptno desc
]]>
</select>
<select id="listPage" resultType="Dept">
<![CDATA[
select deptno, dname, loc
from dept
order by deptno desc
]]>
</select>
<select id="selectOneByDeptno" resultType="Dept" parameterType="int">
<![CDATA[
select deptno, dname, loc
from dept
where deptno = #{deptno}
]]>
</select>
<insert id="deptInsert" parameterType="Dept">
<![CDATA[
insert into dept(deptno, dname, loc)
values(#{deptno}, #{dname}, #{loc})
]]>
</insert>
<insert id="deptInsertMap" parameterType="hashmap">
<![CDATA[
insert into dept(deptno, dname, loc)
values(#{x}, #{y}, #{z})
]]>
</insert>
<update id="deptUpdate" parameterType="Dept">
<![CDATA[
update dept
set dname=#{dname}, loc=#{loc}
where deptno=#{deptno}
]]>
</update>
<delete id="deleteByDeptno" parameterType="int">
<![CDATA[
delete dept
where deptno=#{deptno}
]]>
</delete>
<delete id="deleteByDeptnoOrDname" parameterType="Dept">
<![CDATA[
delete dept
where deptno=#{deptno} or dname=#{dname}
]]>
</delete>
</mapper>package com.config;
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MySqlSessionFactory
{
static SqlSessionFactory x = null;
static
{
//Configuration.xml 읽기
String resource = "com/config/Configuration.xml";
InputStream inputStream = null;
try
{
inputStream = Resources.getResourceAsStream(resource);
}
catch (IOException e)
{
e.printStackTrace();
}
x = new SqlSessionFactoryBuilder().build(inputStream);
}
public static SqlSession getSession()
{
return x.openSession();
}
}-DTO
package com.dto;
public class DeptDTO
{
private int deptno;
private String dname;
private String loc;
public DeptDTO() {}
public DeptDTO(int deptno, String dname, String loc)
{
this.deptno = deptno;
this.dname = dname;
this.loc = loc;
}
public String toString()
{
return "DeptDTO [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
}
//getter
public int getDeptno() { return deptno; }
public String getDname() { return dname; }
public String getLoc() { return loc; }
//setter
public void setDeptno(int deptno) { this.deptno = deptno; }
public void setDname(String dname) { this.dname = dname; }
public void setLoc(String loc) { this.loc = loc; }
}-DAO
package com.dao;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.dto.DeptDTO;
public class DeptDAO
{
public List<DeptDTO> list(SqlSession session)
{
return session.selectList("com.mybatis.dept.DeptMapper.list");
}
public int insert(SqlSession session, DeptDTO dto)
{
return session.insert("com.mybatis.dept.DeptMapper.deptInsert", dto);
}
public int delete(SqlSession session, int deptno)
{
return session.delete("com.mybatis.dept.DeptMapper.deleteByDeptno", deptno);
}
public int update(SqlSession session, DeptDTO dto)
{
return session.update("deptUpdate", dto);
}
}-Service
package com.service;
import java.util.List;
import com.dto.DeptDTO;
public interface DeptService
{
public List<DeptDTO> list();
public int insert(DeptDTO dto) throws Exception;
public int delete(int deptno) throws Exception;
public int update(DeptDTO dto) throws Exception;
}package com.service;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import com.config.MySqlSessionFactory;
import com.dao.DeptDAO;
import com.dto.DeptDTO;
public class DeptServiceImpl implements DeptService
{
//핵심작업
//목록보기
@Override
public List<DeptDTO> list()
{
SqlSession session = MySqlSessionFactory.getSession();
List<DeptDTO> list = null;
try
{
DeptDAO dao = new DeptDAO();
list = dao.list(session);
}
finally
{
session.close();
}
return list;
}
@Override
public int insert(DeptDTO dto) throws Exception
{
SqlSession session = MySqlSessionFactory.getSession();
int n = 0;
try
{
DeptDAO dao = new DeptDAO();
n = dao.insert(session, dto);
session.commit();
}
finally
{
session.close();
}
return n;
}
@Override
public int delete(int deptno) throws Exception
{
SqlSession session = MySqlSessionFactory.getSession();
int n = 0;
try
{
DeptDAO dao = new DeptDAO();
n = dao.delete(session, deptno);
session.commit();
}
finally
{
session.close();
}
return n;
}
@Override
public int update(DeptDTO dto) throws Exception
{
SqlSession session = MySqlSessionFactory.getSession();
int n = 0;
try
{
DeptDAO dao = new DeptDAO();
n = dao.update(session, dto);
session.commit();
}
finally
{
session.close();
}
return n;
}
}-Main
import java.util.List;
import java.util.Scanner;
import com.dto.DeptDTO;
import com.service.DeptService;
import com.service.DeptServiceImpl;
public class DeptMain {
public static void main(String[] args)
{
while(true)
{
System.out.println("메뉴");
System.out.println("1.목록");
System.out.println("2.저장");
System.out.println("3.삭제");
System.out.println("4.수정");
System.out.println("0.종료");
Scanner sc = new Scanner(System.in);
DeptService service = new DeptServiceImpl();
int num = sc.nextInt();
if(num == 1)
{
List<DeptDTO> list = service.list();
for(DeptDTO dto : list)
{
System.out.println(dto);
}
}
else if(num == 2)
{
System.out.println("저장할 부서번호 입력");
int deptno = sc.nextInt();
System.out.println("저장할 부서명 입력");
String dname = sc.next();
System.out.println("저장할 부서위치 입력");
String loc = sc.next();
DeptDTO dto = new DeptDTO(deptno, dname, loc);
try
{
int n = service.insert(dto);
if(n == 1) System.out.println("저장 성공");
}
catch (Exception e)
{
System.out.println("부서번호 정보 에러 발생");
System.out.println("부서번호 확인 요망");
}
}
else if(num == 3)
{
System.out.println("삭제할 부서번호 입력");
int deptno = sc.nextInt();
try
{
int n = service.delete(deptno);
if(n == 1) System.out.println("삭제 성공");
}
catch (Exception e)
{
System.out.println("삭제 에러 발생");
System.out.println("부서번호 확인 요망");
}
}
else if(num == 4)
{
System.out.println("수정할 부서번호 입력");
int deptno = sc.nextInt();
System.out.println("수정할 부서명 입력");
String dname = sc.next();
System.out.println("수정할 부서위치 입력");
String loc = sc.next();
DeptDTO dto = new DeptDTO(deptno, dname, loc);
try
{
int n = service.update(dto);
if(n == 1) System.out.println("수정 성공");
}
catch (Exception e)
{
System.out.println("부서번호 정보 에러 발생");
System.out.println("부서번호 확인 요망");
}
}
else if(num == 0)
{
sc.close();
System.out.println("프로그램 정상 종료");
System.exit(0);
}
}
}
}