java/jsp 프레임워크
프레임워크
- 프리젠테이션 티어
- 비즈니스 티어
- 데이터 티어
sql mapper -> myBatis
java + sql -> java / sql
myBatis설정
- java project
- web project
- 프레임워크 위치
- xml
log4j.xml
myBatisConfig.xml
mapper.xml
게시판에 적용할 때
model1
client -> jsp -> dao -> myBatis -> DB
model2
client -> servlet XXAction -> dao -> myBatis -> DB
mapper file
다중처리가능(테이블/프로그램 종류)
프레임워크 설정
1. xml
2. java(POJO) + annotationPOJO를 emp테이블 출력하기
!기존에 만들었던 emp테이블 소스를 사용하여 POJO모델로 변경하는작업
프로젝트 구조
SqlMapperInter.java
package mapper;
import java.util.List;
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.DeptTO;
import model1.EmpTO;
public interface SqlMapperInter {
@Select("select deptno, dname, loc from dept where deptno=10")
public DeptTO selectByDeptno();
@Select("select deptno, dname, loc from dept")
public List<DeptTO> selectList();
@Select("select deptno, dname, loc from dept where deptno=#{deptno}")
public List<DeptTO> selectListByDeptno(String deptno);
// public DeptTO selectListByDeptno(String to);
//@Select("select empno, ename, job, sal from emp where ename like #{ename}")
@Select("select empno, ename, job, sal from emp where ename like concat (#{ename}, '%')")
public List<EmpTO> selectListByEname(String ename);
@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);
}myBatisConfig.xml
<configuration>
<environments default="mariadb1">
<environment id="mariadb1">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="org.mariadb.jdbc.Driver"/>
<property name="url" value="jdbc:mariadb://localhost:3306/sample"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
</configuration>mybatis01
<%@ 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="java.util.List" %>
<%@ 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();
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>mybatis02
<%
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();
sqlSession.getConfiguration().addMapper(SqlMapperInter.class);
SqlMapperInter mapper = (SqlMapperInter)sqlSession.getMapper(SqlMapperInter.class);
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>");
} catch(IOException e) {
System.out.println("에러 : " + e.getMessage());
} finally {
if(sqlSession != null) sqlSession.close();
if(is != null) is.close();
}
%>mybatis03
<%
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();
sqlSession.getConfiguration().addMapper(SqlMapperInter.class);
SqlMapperInter mapper = (SqlMapperInter)sqlSession.getMapper(SqlMapperInter.class);
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>");
} catch(IOException e) {
System.out.println("에러 : " + e.getMessage());
} finally {
if(sqlSession != null) sqlSession.close();
if(is != null) is.close();
}
%>mybatis04
<%
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();
sqlSession.getConfiguration().addMapper(SqlMapperInter.class);
SqlMapperInter mapper = (SqlMapperInter)sqlSession.getMapper(SqlMapperInter.class);
//List<EmpTO> lists = mapper.selectListByEname("S%");
List<EmpTO> lists = mapper.selectListByEname("S");
sbHtml.append("<table>");
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.getSal() + "</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();
}
%>mybatis05
<%
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);
sqlSession.getConfiguration().addMapper(SqlMapperInter.class);
SqlMapperInter mapper = (SqlMapperInter)sqlSession.getMapper(SqlMapperInter.class);
DeptTO to = new DeptTO();
to.setDeptno("100");
to.setDname("우리집");
to.setLoc("의정부");
int result = mapper.insert(to);
sbHtml.append("결과 : " + result);
} catch(IOException e) {
System.out.println("에러 : " + e.getMessage());
} finally {
if(sqlSession != null) sqlSession.close();
if(is != null) is.close();
}
%>update/delete도 직접 해보기
POJO를 사용하여 우편번호 검색기 만들기
!기존에 만들었던 우편번호 소스를 사용하여 POJO모델로 변경하는작업
프로젝트 구조
SqlMapperInter.java
package mapper;
import java.util.ArrayList;
import java.util.List;
import org.apache.ibatis.annotations.Select;
import model1.ZipcodeTO;
public interface SqlMpperInter {
@Select("select zipcode, sido, gugun, dong, ri, bunji from zipcode where dong like concat (#{ename}, '%')")
public List<ZipcodeTO> SelectListByDong();
@Select("select zipcode, sido, gugun, dong, ri, bunji from zipcode where dong like concat (#{ename}, '%')")
public ArrayList<ZipcodeTO> SelectAListByDong(String strDong);
}ZipcodeDAO.java
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.SqlMpperInter;
public class ZipcodeDAO {
private SqlSession sqlSession;
private SqlMpperInter 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(SqlMpperInter.class);
this.mapper = (SqlMpperInter)sqlSession.getMapper(SqlMpperInter.class);
} catch (IOException e) {
// TODO Auto-generated catch block
System.out.println("에러 : " + e.getMessage());
e.printStackTrace();
} finally {
if(is != null) try {is.close();} catch(IOException e) {}
}
}
public ArrayList<ZipcodeTO> selectlist(String strDong) {
ArrayList<ZipcodeTO> lists = mapper.SelectAListByDong(strDong);
if(sqlSession != null) sqlSession.close();
return lists;
}
}myBatisConfig.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>
<environments default="mariadb2">
<environment id="mariadb2">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="org.mariadb.jdbc.Driver"/>
<property name="url" value="jdbc:mariadb://localhost:3306/project"/>
<property name="username" value="project"/>
<property name="password" value="1234"/>
</dataSource>
</environment>
</environments>
</configuration>POJO를 사용하여 우편번호 게시판 만들기
!기존에 만들었던 게시판 소스를 사용하여 POJO모델로 변경하는작업
프로젝트 구조
BoardMapperInter.java
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 BoardMapperInter {
@Select("select seq, subject, writer, date_format(wdate, '%Y-%m-%d') wdate, hit, datediff(now(), wdate) wgap 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);
@Update("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);
}BoardDAO.java
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.BoardMapperInter;
public class BoardDAO {
private SqlSession sqlSession;
private BoardMapperInter mapper;
public BoardDAO() {
// 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(BoardMapperInter.class);
this.mapper = (BoardMapperInter)sqlSession.getMapper(BoardMapperInter.class);
} catch (IOException e) {
// TODO Auto-generated catch block
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() {
}
public int boardWriteOk(BoardTO to) {
System.out.println("boardWriterOk() 호출");
// 0 : 정상 / 1 : 비정상
int flag = 1;
int result = mapper.boardWriteOk(to);
if(result == 1) {
flag = 0;
}
if(sqlSession != null) sqlSession.close();
return flag;
}
public ArrayList<BoardTO> boardList() {
System.out.println("boardList() 호출");
ArrayList<BoardTO> datas = mapper.boardList();
//System.out.println("boardList() 호출" + datas);
if(sqlSession != null) sqlSession.close();
return datas;
}
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) {
System.out.println("boardModify() 호출");
to = mapper.boardModify(to);
if(sqlSession != null) sqlSession.close();
return to;
}
public int boardModifyOk(BoardTO to) {
System.out.println("boardModifyOk() 호출");
int flag = 2;
int result = mapper.boardModifyOk(to);
if(result == 1) {
flag = 0;
}
if(sqlSession != null) sqlSession.close();
return flag;
}
public BoardTO boardDelete(BoardTO to) {
System.out.println("boardDelete() 호출");
to = mapper.boardDelete(to);
if(sqlSession != null) sqlSession.close();
return to;
}
public int boardDeleteOk(BoardTO to) {
System.out.println("boardDeleteOk() 호출");
int flag = 2;
int result = mapper.boardDeleteOk(to);
if(result == 1) {
flag = 0;
} else if(result == 0) {
flag = 1;
}
if(sqlSession != null) sqlSession.close();
return flag;
}
}myBatisConfig.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>
<environments default="mariadb2">
<environment id="mariadb2">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="org.mariadb.jdbc.Driver"/>
<property name="url" value="jdbc:mariadb://localhost:3306/board"/>
<property name="username" value="board"/>
<property name="password" value="1234"/>
</dataSource>
</environment>
</environments>
</configuration>