프레임워크와 라이브러리의 차이는 ?
프레임 워크
프레임워크
프리젠테이션 티어
비즈니스 티어
데이터 티어
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 mybatismapper 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
를 이용하여 만들기
개발자 꿈나무