715
No
[동영상강의] 보아스 - Jsp21_mybatis 예제(방명록 - 테이블 만들기, 시퀀스 만들기 )
MybatisManager
package sqlmap;
import java.io.Reader;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
public class MybatisManager {
//싱글톤 패턴으로 SqlSessionFactory 생성
//SqlSessionFactoryBuilder => SqlSessionFactory
//=>SqlSession
//(sql 실행 객체 : JDBC의 Statement + Result 객체 )
private static SqlSessionFactory instance;
private MybatisManager() {} //private 생성자
public static SqlSessionFactory getInstance(){
Reader reader=null;
try {
// Java Resources의 src
//mybatis 설정파일 정보를 읽음
reader = Resources.getResourceAsReader(
"sqlmap/sqlMapConfig.xml");
//SqlSessionFactory 생성기
instance = new SqlSessionFactoryBuilder().build(reader);
} catch (Exception e) {
e.printStackTrace();
}finally{
try{
if(reader!=null)reader.close();
}catch(Exception e){
e.printStackTrace();
}
}
return instance;
}
}
sqlMapConfig.xml
<?xml version="1.0" encoding="UTF-8"?>
<!-- xml 지시어, xml directive -->
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 알리아스 설정 -->
<typeAliases>
<!-- typeAlias type="전체경로" alias="별칭" -->
<!-- <typeAlias type="emp.dto.EmpDTO" alias="e" /> -->
</typeAliases>
<!-- db연결 참조코드 -->
<!-- <environments default="">
<environment id="">
<transactionManager type="JDBC" />
<dataSource type="JNDI">
<property name="data_source"
value="java:comp/env/jdbc/myoracle" />
</dataSource>
</environment>
</environments> -->
<!--
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/testDB" />
<property name="username" value="test" />
<property name="password" value="test12" />
</dataSource>
</environment>
</environments>
-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="oracle.jdbc.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:xe" />
<property name="username" value="java" />
<property name="password" value="1111" />
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="/memo/mapper/memo.xml"/>
<mapper resource="guestbook/mapper/guestbook.xml"/>
</mappers>
<!-- 실제 sql query -->
<!-- <mappers>
<mapper resource="emp/mapper/emp.xml" />
<mapper resource=
"student/mapper/student.xml" />
<mapper resource=
"student/mapper/dept.xml" />
<mapper resource=
"student/mapper/prof.xml" />
<mapper resource=
"student/mapper/lecture.xml" />
<mapper resource=
"memo/mapper/memo.xml" />
<mapper resource=
"board/mapper/board.xml" />
</mappers> -->
</configuration>
guestbook.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">
<!-- memo.xml -->
<!-- 저장 프로시저 호출 방법 { call 프로시저이름(매개변수) } -->
<mapper namespace="guestbook">
<!-- 네임스페이스 id는 중복이 될 수 없음 -->
<select id="gbList" resultType="guestbook.dto.GuestBookDTO">
select * from guestbook
order by idx desc
</select>
<insert id="gbInsert">
insert into guestbook
(idx, name, email, passwd, content)
values
(guestbook_seq.nextval, #{name}, #{email}, #{passwd}, #{content})
</insert>
<select id="passwdCheck" resultType="int">
select count(*) from guestbook where idx=#{idx} and passwd=#{passwd}
</select>
<!-- 상세페이지 가기 -->
<select id="gbDetail" resultType="guestbook.dto.GuestBookDTO">
select * from guestbook where idx=#{idx}
</select>
<!-- 수정 -->
<update id="gbUpdate">
update guestbook set name=#{name}, email=#{email},
passwd= #{passwd}, content= #{content} where idx=#{idx}
</update>
<!-- 삭제 -->
<delete id="gbDelete">
delete from guestbook where idx=#{idx}
</delete>
<!-- 동 적SQL -->
<sql id="search">
<choose>
<when test="searchKey =='writer_memo'.toString">
select * from memo
where writer like '%'||#{search}||'%'
union
select * from memo
where memo like '%'||#{search}||'%'
</when>
<otherwise>
select * from memo
where ${searchKey} like '%'||#{search}||'%'
order by idx desc
</otherwise>
</choose>
</sql>
<!-- <select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose>
<when test="title != null">
AND title like #{title}
</when>
<when test="author != null and author.name != null">
AND author_name like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>
</select> -->
<!--
<insert id="insertMemo">
{ call memo_insert(#{writer},#{memo} ) }
</insert> -->
<!-- <select id="listMemo"
resultType="memo.dto.MemoDTO">
select * from
memo order by idx desc
</select> -->
<!-- statementType
CALLABLE(저장프로시저용),
PREPARED(PreparedStatement), STATEMENT(Statement)
mode=OUT 출력매개변수
jdbcType 오라클의 자료형
javaType 자바의 자료형
resultMap 해시맵의 자료형(리턴타입)
Map<String,Object> map=new HashMap<String,Object>();
map.put("result", new ArrayList.....);
-->
<!-- <delete id="deleteMemo">
{ call memo_del( #{idx} ) }
</delete>
-->
</mapper>
GuestBookDTO
package guestbook.dto;
public class GuestBookDTO {
private int idx;
private String name;
private String email;
private String passwd;
private String content;
private String post_date;
public GuestBookDTO() {
}
public GuestBookDTO(String name, String email, String passwd, String content) {
super();
this.name = name;
this.email = email;
this.passwd = passwd;
this.content = content;
}
public int getIdx() {
return idx;
}
public void setIdx(int idx) {
this.idx = idx;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPasswd() {
return passwd;
}
public void setPasswd(String passwd) {
this.passwd = passwd;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getPost_date() {
return post_date;
}
public void setPost_date(String post_date) {
this.post_date = post_date;
}
@Override
public String toString() {
return "GuestBookDTO [idx=" + idx + ", name=" + name + ", email=" + email + ", passwd=" + passwd + ", content="
+ content + ", post_date=" + post_date + "]";
}
}
GuestBookDAO
package guestbook.dao;
import java.util.List;
import org.apache.ibatis.session.SqlSession;
import guestbook.dto.GuestBookDTO;
import sqlmap.MybatisManager;
public class GuestBookDAO {
public List<GuestBookDTO> getList(){
List<GuestBookDTO> list=null;
SqlSession session=null;
try{
session=MybatisManager.getInstance().openSession();
list=session.selectList("guestbook.gbList");
for(GuestBookDTO dto: list){
String content=dto.getContent();
//공백 처리
content=content.replaceAll(" ", " ");
//태그 문자 처리
content=content.replaceAll("<", "<");
content=content.replaceAll(">", ">");
//줄바꿈 처리
content=content.replaceAll("\n", "<br>");
dto.setContent(content);
}
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(session!=null)session.close();
}catch(Exception e){
e.printStackTrace();
}
}
return list;
}
public void gbInsert(GuestBookDTO dto) {
SqlSession session=null;
try{
session=MybatisManager.getInstance().openSession();
session.insert("guestbook.gbInsert", dto);
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
if(session!=null)session.close();
}
}
public boolean passwdCheck(int idx, String passwd){
boolean result=false;
SqlSession session
=MybatisManager.getInstance().openSession();
GuestBookDTO dto=new GuestBookDTO();
dto.setIdx(idx);
dto.setPasswd(passwd);
//레코드갯수가 1개이면 selectOne(), 2개 이상 selectList()
int count =session.selectOne("guestbook.passwdCheck", dto);
//비번이 맞으면 1, 틀리면 0이 리턴됨
result= count==1? true : false;
session.close();
return result;
}
//상세페이지
public GuestBookDTO gbDetail(int idx){
GuestBookDTO dto =new GuestBookDTO();
SqlSession session=null;
try{
session=MybatisManager.getInstance().openSession();
dto=session.selectOne("guestbook.gbDetail", idx);
}catch(Exception e){
e.printStackTrace();
}finally{
if(session!=null)session.close();
}
return dto;
}
//수정
public void gbUpdate(GuestBookDTO dto){
SqlSession session=null;
try{
session=MybatisManager.getInstance().openSession();
session.update("guestbook.gbUpdate", dto);
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
if(session!=null)session.close();
}
}
//삭제
public void gbDelete(int idx){
SqlSession session=null;
try{
session=MybatisManager.getInstance().openSession();
session.delete("guestbook.gbDelete", idx);
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
if(session!=null)session.close();
}
}
}
GuestBookController
package guestbook;
import java.io.IOException;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import guestbook.dao.GuestBookDAO;
import guestbook.dto.GuestBookDTO;
@WebServlet("/guestbook_servlet/*")
public class GuestBookController extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//사용자가 요청한 주소에 따라 분기 처리
String url=request.getRequestURL().toString();
GuestBookDAO dao=new GuestBookDAO();
if(url.indexOf("list")!=-1){
List<GuestBookDTO> items=dao.getList();
request.setAttribute("list", items);
// System.out.println("list.do");
String page="/guestbook/list.jsp";
RequestDispatcher rd=request.getRequestDispatcher(page);
rd.forward(request, response);
}else if(url.indexOf("insert.do")!=-1){
GuestBookDTO dto=new GuestBookDTO();
dto.setContent(request.getParameter("content"));
dto.setEmail(request.getParameter("email"));
dto.setName(request.getParameter("name"));
dto.setPasswd(request.getParameter("passwd"));
dao.gbInsert(dto);
String page=request.getContextPath()+"/guestbook_servlet/list.do";
response.sendRedirect(page);
}else if(url.indexOf("passwd_check.do")!=-1){
int idx=Integer.parseInt(request.getParameter("idx"));
String passwd=request.getParameter("passwd");
//테이블에 저장된 비밀번호와 체크
String page="";
if(dao.passwdCheck(idx, passwd)){
//맞으면
page="/guestbook/edit.jsp";
//상세페이지 이동
GuestBookDTO dto =dao.gbDetail(idx);
request.setAttribute("dto", dto);
//response.sendRedirect(page);
RequestDispatcher rd =request.getRequestDispatcher(page);
rd.forward(request, response);
}else{
//틀리면
page=request.getContextPath()
+"/guestbook_servlet/list.do?message=error";
response.sendRedirect(page);
}
}else if(url.indexOf("update.do")!=-1){
GuestBookDTO dto =new GuestBookDTO();
dto.setContent(request.getParameter("content"));
dto.setEmail(request.getParameter("email"));
dto.setIdx(Integer.parseInt(request.getParameter("idx")));
dto.setName(request.getParameter("name"));
dto.setPasswd(request.getParameter("passwd"));
System.out.println(dto.toString());
dao.gbUpdate(dto);
String page=request.getContextPath()+"/guestbook_servlet/list.do";
response.sendRedirect(page);
}else if(url.indexOf("delete.do")!=-1){
int idx=Integer.parseInt(request.getParameter("idx"));
dao.gbDelete(idx);
String page="/guestbook_servlet/list.do";
response.sendRedirect(page);
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ include file="../include/header.jsp" %>
<!-- 컨텍스 패스가 포함됨 -->
<c:redirect url="/guestbook_servlet/list.do" />
list.jsp
방명록 |
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ include file="../include/header.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script src="http://code.jquery.com/jquery-3.2.1.min.js"></script>
</head>
<body>
<div data-role="content" style="margin-left:100px;">
<h1>방명록</h1>
<c:if test="${param.message=='error' }">
<span style="color:red;">
비밀번호가 일치하지 않습니다.
</span>
</c:if>
<p>
<input type="button" value="글쓰기" onclick="location.href='${path}/guestbook/write.jsp';" />
</p>
<c:forEach var="dto" items="${list}">
<form action="${path}/guestbook_servlet/passwd_check.do" method="post">
<table border="1" style="width:600px; margin-bottom:20px;" >
<tr>
<td>이름</td>
<td>${dto.name }</td>
<td>날짜</td>
<td>${dto.post_date }</td>
</tr>
<tr>
<td>이메일</td>
<td colspan="3">${dto.email }</td>
</tr>
<tr>
<td colspan="4">${dto.content }</td>
</tr>
<tr>
<td colspan="4">비밀번호 <input type="password" name="passwd" >
<input type="hidden" name="idx" value="${dto.idx }">
<input type="submit" value="수정/삭제" >
</td>
</tr>
</table>
</form>
</c:forEach>
</div>
</body>
</html>
write.jsp
방명록 등록 |
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ include file="../include/header.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script src="http://code.jquery.com/jquery-3.2.1.min.js"></script>
<script>
function check(){
var name=$("#name");//id가 name 인 태그
//var name=document.name;
var email=$("#email"); //i가 email 인 태그
var passwd=$("#passwd");
var content=$("#content");
if(name.val()==""){// 태그.val 태그에 입력된 value
alert("이름을 입력하세요");
name.focus();//태그에 입력포커스 설정
return ;//함수 종료 (서버에 전송되지 않음)
}
if(!passwd.val()){
alert("비밀번호를 입력하세요");
passwd.focus();
return;
}
if(!content.val()){
alert("내용을 입력하세요");
content.focus();
return;
}
//폼데이터를 처리할 주소 지정
document.form1.action="${path}/guestbook_servlet/insert.do";
//$("#form1").attr("action", "${path}/guestbook_servlet/insert.do");
document.form1.submit();
}
</script>
</head>
<body>
<h2>방명록 등록</h2>
<form method="post" id="form1" name="form1">
<table border="1" style="width:500px;">
<tr>
<td>이름</td>
<td><input name="name" id="name" size="40"></td>
</tr>
<tr>
<td>이메일</td>
<td><input type="email" name="email" id="email"></td>
</tr>
<tr>
<td>비밀번호</td>
<td><input type="password" name="passwd" id="passwd" size="40"></td>
</tr>
<tr align="center">
<td colspan="2">
<textarea rows="5" cols="55" name="content" id="content"></textarea>
</td>
</tr>
<tr align="center">
<td colspan="2">
<input type="button" value="확인" onclick="check()">
<input type="reset" value="취소">
</td>
</tr>
</table>
</form>
</body>
</html>
edit.jsp
방명록 수정 및 삭제 |
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ include file="../include/header.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script src="http://code.jquery.com/jquery-3.2.1.min.js"></script>
<script type="text/javascript">
function gbUpdate(){
document.form1.action="${path}/guestbook_servlet/update.do";
document.form1.submit();
}
</script>
</head>
<body>
<h2>방명록 수정 및 삭제 </h2>
<form method="post" name="form1">
<table border="1" style="width:600px; margin-bottom:20px;" >
<tr>
<td>이름</td>
<td><input type="text" name="name" value="${dto.name }"></td>
<tr>
<tr>
<td>날짜</td>
<td>${dto.post_date }</td>
</tr>
<tr>
<td>이메일</td>
<td><input type="email" name="email" id="email" value="${dto.email }"></td>
</tr>
<tr>
<td colspan="2" ><textarea cols="100" rows="20" name="content" id="content">${dto.content }</textarea></td>
</tr>
<tr>
<td colspan="4">비밀번호 <input type="password" name="passwd" value="${dto.passwd }">
</tr>
<tr>
<td colspan="2">
<input type="hidden" name="idx" value="${dto.idx }">
<input type="button" value="수정하기" onclick="gbUpdate()" >
<input type="button" value="삭제하기" onclick="btnUpdate()">
<input type="button" value="목록가기" onclick="btnList()">
</td>
</tr>
</table>
</form>
</body>
</html>
댓글 ( 4)
댓글 남기기