Mybatis2

준동이·2023년 5월 11일
MyBatis - Model1구조 and POJOMyBatis - POJOMyBatis - 데이터를 가져오기(여러 개 / 단일)mapper file 여러개 사용mapper 다중을 선택하여 실행
0
post-custom-banner

프레임워크와 라이브러리의 차이는 ?

프레임 워크

프레임워크
	프리젠테이션 티어
	비즈니스 티어
	데이터 티어 
			sql mapper 프레임워크 => myBatis
			java + sql => java / sql
        
			설정
        		java project
            	web project
            
            	* 프레임워크의 위치(상관X)
            	* xml
            		log4j.xml
                
                	(사용자가 편리하게 이름 바꿔도 됨)
                	myBatisConfig.xml
                	mapper.xml



게시판에 적용할 때 흐름

model1
		client		jsp			dao			mybatis			db
        
model2
		client		servlet		XXXAction		dao			mybatis

mapper file 여러개 사용 / 해보기

mapper file
		1개 	- 여러개...(테이블 / 프로그램 종류)



MyBatisEx04 프로젝트 생성



mapper.xml 두 개 만들기

mapper1.xml과 mapper2.xml 이렇게 두 개를 만들고 namespace의 이름은 두 개 다르게 설정



mapper.xml 다중으로 있을때 등록시키기



mapper가 다중으로 있을때 선택하여 실행시키는 방법

이렇게 namespace.id 로 선택해준다.
List<EmpTO> lists = sqlSession.selectList("mybatis2.selectparamone2", "S%");



mapper에서 sql문 중복 선언되었을 때

mapper1.xml에서 설정해줬음

중복되는 코드는
<sql id="userColumns">중복되는코드</sql> 로 하나로 묶어주고 
<include refid="userColumns" />이렇게 사용하기
<?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="mybatis1">
	<sql id="userColumns">empno,ename, job, mgr, hiredate, sal, comm, deptno</sql>

	<select id="selectparamone1" parameterType="String" resultType="model1.EmpTO">
		select <include refid="userColumns" />
		from emp
		where ename=#{ename}
	</select>
	
	<select id="selectparamone2" parameterType="String" resultType="model1.EmpTO">
		select <include refid="userColumns" />
		from emp
		where ename like#{ename}
	</select>
</mapper>

똑같이 이렇게 사용하면 됨.
List<EmpTO> lists = sqlSession.selectList("mybatis1.selectparamone2", "S%");



POJO / 해보기

1.xml
2.java(POJO) + annotation (이 방법으로 해보기)
POJO - 아무것도 구현이나 상속을 받지않은 순수한 자바파일 형태

MyBatisEx05 프로젝트 생성 / 설정 파일들, 라이브러리 넣기



myBatisConfig.xml

myBatisConfig.xml에서 mapper없어도 됨

지워줬다.



mapper 패키지 생성 후 SqlMapperInter인터페이스 클래스 생성

package mapper;

import org.apache.ibatis.annotations.Select;

import model1.DeptTO;

public interface SqlMapperInter {
	@Select ("select deptno, dname, loc from dept where deptno=10")
	public DeptTO selectByDeptno();
}



mybatis01.jsp 에서 사용 / 실행

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.io.IOException" %>
<%@ page import="java.io.InputStream" %>

<%@ page import="org.apache.ibatis.io.Resources" %>
<%@ page import="org.apache.ibatis.session.SqlSession" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactory" %>
<%@ page import="org.apache.ibatis.session.SqlSessionFactoryBuilder" %>

<%@ page import="model1.DeptTO" %>
<%@ page import="mapper.SqlMapperInter" %>

<%
String resource = "myBatisConfig.xml";

InputStream is = null;

SqlSession sqlSession = null;

StringBuilder sbHtml = new StringBuilder();
try {
	is = Resources.getResourceAsStream(resource);
	SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
	
	sqlSession = sqlSessionFactory.openSession(true);
	
	// mapper 불러오기 / 이름만 좀 달라졌지 기존 방법과 같음
	sqlSession.getConfiguration().addMapper(SqlMapperInter.class);
	SqlMapperInter mapper = (SqlMapperInter)sqlSession.getMapper(SqlMapperInter.class);
	
	DeptTO to = mapper.selectByDeptno();
	sbHtml.append("<table>");
	sbHtml.append("<tr>");
	sbHtml.append("<td>" + to.getDeptno() + "</td>");
	sbHtml.append("<td>" + to.getDname() + "</td>");
	sbHtml.append("<td>" + to.getLoc() + "</td>");
	sbHtml.append("</tr>");
	sbHtml.append("</table>");
} catch(IOException e) {
	System.out.println("[에러] : " + e.getMessage());
}finally {
	if(sqlSession != null) sqlSession.close();
	if(is != null) is.close();
}
%>

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%=sbHtml %>
</body>
</html>



여러개의 데이터를 가져오기 / 선언하기

SqlMapperInter 클래스에 추가

package mapper;

import java.util.List;

import org.apache.ibatis.annotations.Select;

import model1.DeptTO;

public interface SqlMapperInter {
	@Select ("select deptno, dname, loc from dept where deptno=10")
	public DeptTO selectByDeptno();
	
	// 여러 개의 데이터 가져오기 / select 데이터 가져오기
	@Select("select deptno, dname, loc from dept")
	public List<DeptTO> selectList();
}

mybatis02.jsp에서 실행

기존 코드에서 변경해준 것

List<DeptTO> lists = mapper.selectList();
	sbHtml.append("<table>");
	for(DeptTO to : lists) {
	sbHtml.append("<tr>");
	sbHtml.append("<td>" + to.getDeptno() + "</td>");
	sbHtml.append("<td>" + to.getDname() + "</td>");
	sbHtml.append("<td>" + to.getLoc() + "</td>");
	sbHtml.append("</tr>");
	}
	sbHtml.append("</table>");



전체가 아닌 일부 데이터 가져오기(조회)

SqlMapperInter 클래스에 추가

@Select("select deptno, dname, loc from dept where deptno=#{deptno}")
	// 밑의 두 가지 방법 다 가능
	public List<DeptTO> selectListByDeptno(String deptno);
	// public List<DeptTO> selectListByDeptno(DeptTO to);

mybatis03.jsp

// 10번부서 조회하겠다는 것
	List<DeptTO> lists = mapper.selectListByDeptno("10");
	sbHtml.append("<table>");
	for(DeptTO to : lists) {
	sbHtml.append("<tr>");
	sbHtml.append("<td>" + to.getDeptno() + "</td>");
	sbHtml.append("<td>" + to.getDname() + "</td>");
	sbHtml.append("<td>" + to.getLoc() + "</td>");
	sbHtml.append("</tr>");
	}
	sbHtml.append("</table>");



문제

emp 테이블에서 사원명을 조회(시작 글자로 조회) 하는 jsp 작성

EmpTO 클래스 생성

package model1;

public class EmpTO {
	private String empno;
	private String ename;
	private String job;
	private String mgr;
	private String hiredate;
	private String sal;
	private String comm;
	private String deptno;
	public String getEmpno() {
		return empno;
	}
	public void setEmpno(String empno) {
		this.empno = empno;
	}
	public String getEname() {
		return ename;
	}
	public void setEname(String ename) {
		this.ename = ename;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	public String getMgr() {
		return mgr;
	}
	public void setMgr(String mgr) {
		this.mgr = mgr;
	}
	public String getHiredate() {
		return hiredate;
	}
	public void setHiredate(String hiredate) {
		this.hiredate = hiredate;
	}
	public String getSal() {
		return sal;
	}
	public void setSal(String sal) {
		this.sal = sal;
	}
	public String getComm() {
		return comm;
	}
	public void setComm(String comm) {
		this.comm = comm;
	}
	public String getDeptno() {
		return deptno;
	}
	public void setDeptno(String deptno) {
		this.deptno = deptno;
	}
	
	
}

SqlMapperInter 클래스에 추가

@Select("select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp where ename like#{ename}")
	public List<EmpTO> selectListByEname(String ename);

mybatis04.jsp에서 실행

List<EmpTO> lists = mapper.selectListByEname("s%");
	sbHtml.append("<table border='1'>");
	for(EmpTO to : lists) {
	sbHtml.append("<tr>");
	sbHtml.append("<td>" + to.getEmpno() + "</td>");
	sbHtml.append("<td>" + to.getEname() + "</td>");
	sbHtml.append("<td>" + to.getJob() + "</td>");
	sbHtml.append("<td>" + to.getMgr() + "</td>");
	sbHtml.append("<td>" + to.getHiredate() + "</td>");
	sbHtml.append("<td>" + to.getSal() + "</td>");
	sbHtml.append("<td>" + to.getComm() + "</td>");
	sbHtml.append("<td>" + to.getDeptno() + "</td>");
	sbHtml.append("</tr>");
	}
	sbHtml.append("</table>");



insert, update, delete 해보기

SqlMapperInter 클래스에 추가

@Insert("insert into dept2 values(#{deptno}, #{dname}, #{loc}")
	public int insert(DeptTO to);
	
	@Update("update dept2 set dname=#{dname} where deptno=#{deptno}")
	public int update(DeptTO to);
	
	@Delete("delete from dept2 where deptno=#{deptno}")
	public int delete(DeptTO to);

mybatis05.jsp에서 insert구문 실행

DeptTO to = new DeptTO();
	to.setDeptno("60");
	to.setDname("건두부");
	to.setLoc("마석");
	
	int result = mapper.insert(to);
	sbHtml.append("결과 : " + result);


mybatis05.jsp에서 update구문 실행

DeptTO to = new DeptTO();
	to.setDeptno("60");
	to.setDname("축구부");
	
	int result = mapper.update(to);
	sbHtml.append("결과 : " + result);

건두부에서 축구부로 변경


mybatis05.jsp에서 delete구문 실행

DeptTO to = new DeptTO();
	to.setDeptno("60");
	
	int result = mapper.delete(to);
	sbHtml.append("결과 : " + result);

60번 삭제시켰음.



문제 / 우편번호 검색기 - 어노테이션 구조로 해보기



SqlMapperInter

package mapper;

import java.util.ArrayList;

import org.apache.ibatis.annotations.Select;

import model1.ZipcodeTO;

public interface SqlMapperInter {
	
	@Select("select zipcode, sido, gugun, dong, ri, bunji, seq from zipcode where dong like#{dong}")
	public ArrayList<ZipcodeTO> selectList(String strDong);
}

ZipcodeTO

package model1;

public class ZipcodeTO {
	private String zipcode;
	private String sido;
	private String gugun;
	private String dong;
	private String ri;
	private String bunji;
	private String seq;
	
	public String getZipcode() {
		return zipcode;
	}
	public void setZipcode(String zipcode) {
		this.zipcode = zipcode;
	}
	public String getSido() {
		return sido;
	}
	public void setSido(String sido) {
		this.sido = sido;
	}
	public String getGugun() {
		return gugun;
	}
	public void setGugun(String gugun) {
		this.gugun = gugun;
	}
	public String getDong() {
		return dong;
	}
	public void setDong(String dong) {
		this.dong = dong;
	}
	public String getRi() {
		return ri;
	}
	public void setRi(String ri) {
		this.ri = ri;
	}
	public String getBunji() {
		return bunji;
	}
	public void setBunji(String bunji) {
		this.bunji = bunji;
	}
	public String getSeq() {
		return seq;
	}
	public void setSeq(String seq) {
		this.seq = seq;
	}
}

ZipcodeDAO

package model1;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import mapper.SqlMapperInter;

public class ZipcodeDAO {
	private SqlSession sqlSession;
	
	SqlMapperInter mapper;
	public ZipcodeDAO() {
		// TODO Auto-generated constructor stub
		String resource = "myBatisConfig.xml";
		
		InputStream is = null;
		
		try {
			is = Resources.getResourceAsStream(resource);
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
			
			this.sqlSession = sqlSessionFactory.openSession(true);
			
			this.sqlSession.getConfiguration().addMapper(SqlMapperInter.class);
			this.mapper = (SqlMapperInter)sqlSession.getMapper(SqlMapperInter.class);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			System.out.println("[에러] : " + e.getMessage());
		}  finally {
			if (is != null) try {is.close();} catch (IOException e) {}
		}
	}
	public ArrayList<ZipcodeTO> selectList(String strDong) {
		ArrayList<ZipcodeTO> lists = mapper.selectList(strDong);
		if(sqlSession != null) sqlSession.close();
		return lists;
	}
	
}

zipcode.jsp

<%@page import="java.util.ArrayList"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page import="model1.ZipcodeTO"%>
<%@page import="model1.ZipcodeDAO"%>
<%@ page import="java.util.List" %>

<%
request.setCharacterEncoding("utf-8");

String strDong = null;
if(request.getParameter("dong") != null) {
	strDong = request.getParameter("dong");
}

ZipcodeDAO dao = new ZipcodeDAO();
ArrayList<ZipcodeTO> lists = dao.selectList(strDong + "%");

StringBuilder sbHtml = new StringBuilder();

sbHtml.append("<table border='1'>");
for(ZipcodeTO to : lists) {
sbHtml.append("<tr>");
sbHtml.append("<td>" + to.getZipcode() + "</td>");
sbHtml.append("<td>" + to.getSido() + "</td>");
sbHtml.append("<td>" + to.getGugun() + "</td>");
sbHtml.append("<td>" + to.getDong() + "</td>");
sbHtml.append("<td>" + to.getRi() + "</td>");
sbHtml.append("<td>" + to.getBunji() + "</td>");
sbHtml.append("<td>" + to.getSeq() + "</td>");
sbHtml.append("</tr>");
}
sbHtml.append("</table>");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="zipcode.jsp" method="post" >
동이름 입력 : <input type="text" name="dong" />
<input type="submit" value="전송" />
</form>
<%=sbHtml %>
</body>
</html>



Model1 구조 and POJO로 만들어보기



SqlMapperInter

package mapper;

import java.util.ArrayList;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import model1.BoardTO;

public interface SqlMapperInter {
	
	@Select("select seq, subject, writer, wdate, hit from board1 order by seq desc")
	public ArrayList<BoardTO> boardList();
	
	@Select("select seq, subject, writer, mail, wip, wdate, hit, content from board1 where seq=#{seq}")
	public BoardTO boardView(BoardTO to);
	
	@Update("update board1 set hit=hit+1 where seq=#{seq}")
	public int boardViewHit(BoardTO to);
	
	@Insert("insert into board1 values(0,#{subject},#{writer},#{mail},#{password},#{content},0,#{wip},now())")
	public int boardWriteOk(BoardTO to);
	
	@Select("select seq, subject, writer, mail, content from board1 where seq=#{seq}")
	public BoardTO boardModify(BoardTO to);
	
	@Insert("update board1 set subject=#{subject},  mail=#{mail}, content=#{content} where seq=#{seq} and password=#{password}")
	public int boardModifyOk(BoardTO to);
	
	@Select("select seq, subject, writer from board1 where seq=#{seq}")
	public BoardTO boardDelete(BoardTO to);
	
	@Delete ("delete from board1 where seq=#{seq} and password=#{password}")
	public int boardDeleteOk(BoardTO to);
}

BoardTO

package model1;

public class BoardTO {
	// 게시판의 데이터를 처리하기 위해 선언
	private String seq;
	private String subject;
	private String writer;
	private String mail;
	private String password;
	private String content;
	private String hit;
	private String wip;
	private String wdate;
	private int wgap;
	
	
	public String getSeq() {
		return seq;
	}
	public void setSeq(String seq) {
		this.seq = seq;
	}
	public String getSubject() {
		return subject;
	}
	public void setSubject(String subject) {
		this.subject = subject;
	}
	public String getWriter() {
		return writer;
	}
	public void setWriter(String writer) {
		this.writer = writer;
	}
	public String getMail() {
		return mail;
	}
	public void setMail(String mail) {
		this.mail = mail;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getHit() {
		return hit;
	}
	public void setHit(String hit) {
		this.hit = hit;
	}
	public String getWip() {
		return wip;
	}
	public void setWip(String wip) {
		this.wip = wip;
	}
	public String getWdate() {
		return wdate;
	}
	public void setWdate(String wdate) {
		this.wdate = wdate;
	}
	public int getWgap() {
		return wgap;
	}
	public void setWgap(int wgap) {
		this.wgap = wgap;
	}
	
	
}

BoardDAO

package model1;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import mapper.SqlMapperInter;

public class BoardDAO {
	private SqlSession sqlSession;
	
	SqlMapperInter mapper;
	
	// 생성자가 필요
	public BoardDAO() {
		String resource = "myBatisConfig.xml";
		
		InputStream is = null;
		
		try {
			is = Resources.getResourceAsStream(resource);
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
			
			this.sqlSession = sqlSessionFactory.openSession(true);
			
			this.sqlSession.getConfiguration().addMapper(SqlMapperInter.class);
			this.mapper = (SqlMapperInter)sqlSession.getMapper(SqlMapperInter.class);
		} catch(IOException e) {
			System.out.println("[에러] : " + e.getMessage());
		} finally {
			if (is != null) try {is.close();} catch (IOException e) {}
		}
	} 
	/*
	 이것들에 해당하는 메서드 선언
	 write / write_ok / list/ view / modify / modify_ok / delete / delete_ok
	 */
	public void boardWrite() {
		
	}
	
	// BoardTO to는 write에서 받은 데이터를 집어넣는다
	public int boardWriteOk(BoardTO to) {
		int flag = 1;
		
		int result = mapper.boardWriteOk(to);
		if(result == 1) {
			flag = 0;
		}
		
		if(sqlSession != null) sqlSession.close();
		return flag;
	}
	
	// list는 ArrayList로 받아온다
	public ArrayList<BoardTO> boardList() {
		System.out.println("boardList() 호출");
		ArrayList<BoardTO> lists = mapper.boardList();
		
		if(sqlSession != null) sqlSession.close();
		return lists;
	}
	
	// to에 seq가 들어가서 update하고 select 함
	public BoardTO boardView(BoardTO to) {
		System.out.println("boardView() 호출");
		
		mapper.boardViewHit(to);
		
		to = mapper.boardView(to);
		
		if(sqlSession != null) sqlSession.close();
		return to;
	}
	
	public BoardTO boardModify(BoardTO to) {
		to = mapper.boardModify(to);
		
		if(sqlSession != null) sqlSession.close();
		return to;
	}
	
	public int boardModifyOk(BoardTO to) {
		int flag = 2;
		
		int result = mapper.boardModifyOk( to);
		if(result == 1) {
			flag = 0;
		} else if(result == 0) {
			flag = 1;
		}
		if(sqlSession != null) sqlSession.close();
		return flag;
	}
	
	public BoardTO boardDelete(BoardTO to) {
		to = mapper.boardDelete(to);
		if(sqlSession != null) sqlSession.close();
		return to;
	}
	
	public int boardDeleteOk(BoardTO to) {
		int flag = 2;
		
		int result = mapper.boardDeleteOk(to);
		if(result == 1) {
			flag = 0;
		} else if(result == 0) {
			flag = 1;
		}
		
		return flag;
	}
}

나머지 게시판 클래스파일들은 그대로



게시판 게시물 검색기능 구현하기

1.model1
2.mybatis
를 이용하여 만들기

profile
준동이
개발자 꿈나무
이전 포스트

Mybatis

다음 포스트

Ajax

post-custom-banner

0개의 댓글