713
No
[동영상강의] 보아스 - Jsp19_mybatis의 개요, 설정, 한줄메모장(목록, 추가)
memo.jsp 화면
한줄 메모장이름 : 메모 :
|
수정 및 삭제 화면
|
메모 편집
|
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"/>
</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>
memo.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="memo">
<resultMap type="memo.dto.MemoDTO" id="memoMap" />
<resultMap type="memo.dto.MemoDTO" id="memo_list"/>
<select id="list" resultType="memo.dto.MemoDTO" >
select * from memo order by idx desc
</select>
<!--
#{변수} 입력매개변수(따옴표 포함)
${변수} 따옴표 포함되지 않음
parameterType 입력매개변수의 자료형(생략가능)
-->
<insert id="insert" parameterType="memo.dto.MemoDTO">
insert into memo(idx, writer, memo) values
((select nvl(max(idx)+1, 1) from memo) , #{writer}, #{memo} )
</insert>
<select id="view" resultType="memo.dto.MemoDTO" parameterType="int">
select * from memo where idx=#{idx}
</select>
<update id="update">
update memo
set writer =#{writer}, memo=#{memo}
where idx=#{idx}
</update>
<delete id="del">
delete from memo where idx=#{idx}
</delete>
<select id="searchList" resultType="memo.dto.MemoDTO">
<include refid="search"/>
</select>
<!-- 동 적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>
class 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;
}
}
class MemoDTO
package memo.dto;
import java.sql.Date;
public class MemoDTO {
private int idx;
private String writer;
private String memo;
private Date post_date;
public MemoDTO() {
}
public MemoDTO(String writer, String memo) {
super();
this.writer = writer;
this.memo = memo;
}
public int getIdx() {
return idx;
}
public void setIdx(int idx) {
this.idx = idx;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public String getMemo() {
return memo;
}
public void setMemo(String memo) {
this.memo = memo;
}
public Date getPost_date() {
return post_date;
}
public void setPost_date(Date post_date) {
this.post_date = post_date;
}
@Override
public String toString() {
return "MemoDTO [idx=" + idx + ", writer=" + writer + ", memo=" + memo + ", post_date=" + post_date + "]";
}
}
class MemoDAO
package memo.dao;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import memo.dto.MemoDTO;
import sqlmap.MybatisManager;
public class MemoDAO {
public List<MemoDTO> listMemo(){
List<MemoDTO> list=null; //ArrayList 사용 안됨
//SqlSessionFactory 생성
//SqlSessionFactory로 SqlSession 생성
SqlSession session
=MybatisManager.getInstance().openSession();
//selectList("네임스페이스.태그의id")목록이 리턴됨
//selectList(), selectOne()
list=session.selectList("memo.list");
session.close();
return list;
}
public void insertMemo(MemoDTO dto) {
//SqlSession 객체 생성
SqlSession session=null;
try{
session=MybatisManager.getInstance().openSession();
// memo 네임스페이스의 insert 태그 호출
session.insert("memo.insert", dto);
session.commit();//auto commit이 아님
}catch(Exception e){
e.printStackTrace();
}finally{
if(session!=null) session.close();//SqlSession 반납
}
}
public MemoDTO viewMemo(int idx) {
SqlSession session=null;
MemoDTO dto=null;
try{
session=MybatisManager.getInstance().openSession();
dto =session.selectOne("memo.view", idx);
//selectOne() 레코드 한건이 리턴될 경우
//selectList() 레코드 2개 이상 리턴
}catch(Exception e){
e.printStackTrace();
}finally{
if(session!=null)session.close(); //SqlSession 반남
}
return dto;
}
//수정
public void updateMemo(MemoDTO dto) {
SqlSession session=null;
try{
//mybatis 실행 객체 생성
session=
MybatisManager.getInstance().openSession();
//memo 네임스페이스의 id가 update 인 쿼리 실행
session.update("memo.update", dto);
session.commit();//auto commit 이 아님
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(session!=null)session.close();//mybatis 객체 반납
}catch(Exception e){
e.printStackTrace();
}
}
}
public void deleteMemo(int idx) {
SqlSession session=null;
try{
session=MybatisManager.getInstance().openSession();
session.delete("memo.del", idx);
session.commit();
}catch(Exception e){
e.printStackTrace();
}finally{
if(session!=null)session.close();//mybatis 객체 반납
}
}
public List<MemoDTO> listSearchMemo(String search, String searchKey) {
List<MemoDTO> list=null; //ArrayList 사용 안됨
//SqlSessionFactory 생성
//SqlSessionFactory로 SqlSession 생성
SqlSession session
=MybatisManager.getInstance().openSession();
//selectList("네임스페이스.태그의id")목록이 리턴됨
//selectList(), selectOne()
Map<String, Object> map=new HashMap<>();
map.put("searchKey", searchKey);
map.put("search", search);
list=session.selectList("memo.searchList", map);
session.close();
return list;
}
}
class MemoController
package memo;
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 member.MemberDTO;
import memo.dao.MemoDAO;
import memo.dto.MemoDTO;
//url mapping(현재 클래스외 url 을 연결시킴)
@WebServlet("/memo_servlet/*")
public class MemoController extends HttpServlet {
private static final long serialVersionUID = 1L;
//get 방식 호출
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//사용자가 요청한 url 정보에 따라 분기
String url =request.getRequestURL().toString();
//dao 객체 생성
MemoDAO dao=new MemoDAO();
if(url.indexOf("insert.do")!=-1){
MemoDTO dto=new MemoDTO();
dto.setWriter(request.getParameter("writer"));
dto.setMemo(request.getParameter("memo"));
dao.insertMemo(dto);
}else if(url.indexOf("list.do")!=-1){
String search=request.getParameter("search");
String searchKey=request.getParameter("searchKey");
//메모 목록이 리턴됨
List<MemoDTO> list=null;
if(search==null){
list =dao.listMemo();
}else{
list=dao.listSearchMemo(search, searchKey);
}
//출력 페이지에서 공유할 변수 저장
request.setAttribute("list", list);
//페이지 이동(포워딩)
String page="/memo/memo_list.jsp";
RequestDispatcher rd=
request.getRequestDispatcher(page);
rd.forward(request, response);
}else if(url.indexOf("view.do")!=-1){
int idx=Integer.parseInt(request.getParameter("idx"));
MemoDTO dto =dao.viewMemo(idx); //레코드 1개 린턴
request.setAttribute("dto", dto);
//출력 페이지로 포워딩
String page="/memo/memo_view.jsp";
RequestDispatcher rd
=request.getRequestDispatcher(page);
rd.forward(request, response);
}else if(url.indexOf("del.do")!=-1){
//삭제할 번호
int idx=Integer.parseInt(request.getParameter("idx"));
//dao 에 삭제 요청
dao.deleteMemo(idx);
//페이지 이동
String page=request.getContextPath()+"/memo/memo.jsp";
response.sendRedirect(page);
}else if(url.indexOf("delete_all.do")!=-1){
//체크되 체크박스값들이 배열로 넘어옴
String[] idx=request.getParameterValues("idx");
//모두 체크가 안된 경우는 null로 넘어옴
if(idx!=null){
for(String num : idx){
dao.deleteMemo(Integer.parseInt(num));
}
}
String page=request.getContextPath()+"/memo/memo.jsp";
response.sendRedirect(page);
}else if(url.indexOf("update.do")!=-1){
MemoDTO dto=new MemoDTO();
dto.setIdx(Integer.parseInt(request.getParameter("idx")));
dto.setMemo(request.getParameter("memo"));
dto.setWriter(request.getParameter("writer"));
dao.updateMemo(dto);
String page=request.getContextPath()+"/memo/memo.jsp";
response.sendRedirect(page);
}
}
//post 방식 호출
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
memo.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(){ //페이지 로딩이 완료되면 자동 실행
list();
//검색 버튼을 누르면 목록 갱신
$("#btnSearch").click(function(){
list();
});
//id가 btnSave 인 버튼을 누르면 insert() 가 호출됨
$("#btnSave").click(function(){
insert();
});
});
function list(){
var searchKey=$("#searchkey").val();
var search=$("#search").val();
$.ajax({
type:"post",
url:"${path}/memo_servlet/list.do",
data:{
searchKey:searchKey,
search:search
},
success:function(result){//콜백함수
//memo_list.jsp 에 출력된 html이 result 에 리턴됨
// id 가 result 인 태그에 목록이 출력됨
$("#result").html(result);
}
});
}
function insert(){
var writer =$("#writer").val();
var memo=$("#memo").val();
$.ajax({
type:"post",
url:"${path}/memo_servlet/insert.do",
data:{
writer:writer,
memo:memo
},
success:function(){
list();//입력 완료되면 리스트 갱신
$("#writer").val("");
$("#memo").val("");
}
});
}
</script>
</head>
<body>
<h2>한줄 메모장</h2>
<!-- 입력 -->
이름 :<input id="writer">
메모 : <input id="memo">
<input type="button" id="btnSave" value="확인">
<p>
<!-- 검색 -->
<select id="searchkey">
<option value="writer">이름</option>
<option value="memo">메모</option>
<option value="writer_memo">이름+메모</option>
</select>
<input id="search">
<input type="button" id="btnSearch" value="조회">
<!-- 메모 목록이 출력될 영역 -->
<div id="result"></div>
<p>
</body>
</html>
memo_list.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ include file="../include/header.jsp" %>
<script type="text/javascript">
$(function(){
$("#chkAll").click(function(){//체크박스 클릭
//선택자.prop("속성")속성값 읽기
//선택자.prop("속성", "값") 속성값 변경
if($("#chkAll").prop("checked")){
//input 태그 중에서 name 이 idx 인 모든 태그 선택
$("input[name=idx]").prop("checked", true);
}else{
$("input[name=idx]").prop("checked", false);
}
});
//선택삭제 버튼 클릭
$("#btnAllDel").click(function(){
document.form1.action="${path}/memo_servlet/delete_all.do";
document.form1.submit();
});
});
function memo_del(idx){
if(confirm("삭제 하시겠습니까?")){
location.href="${path}/memo_servlet/del.do?idx="+idx;
}
}
</script>
<form method="post" name="form1">
<table border="1">
<tr>
<!-- 전체선택 체크박스 -->
<th><input type="checkbox" id="chkAll"></th>
<th>번호</th>
<th>이름</th>
<th>메모</th>
<th>날짜</th>
<th><input type="button" value="선택삭제" id="btnAllDel"></th>
</tr>
<!-- forEach var="개별값" items="집합데이터" -->
<c:forEach var="row" items="${list }">
<tr>
<td><input type="checkbox" name="idx" value="${row.idx}" ></td>
<td>${row.idx}</td> <!-- getIdx() 가 호출됨 -->
<td>${row.writer}</td>
<td>
<a href="${path}/memo_servlet/view.do?idx=${row.idx}">${row.memo }</a></td>
<td><fmt:formatDate value="${row.post_date }" type="date"/></td>
<td><input type="button" value="삭제"
onclick="memo_del('${row.idx}')"></td>
</tr>
</c:forEach>
</table>
</form>
memo_view.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(){
$("#btnUpdate").click(function(){
var writer=$("#writer");
var memo=$("#memo");
var idx=$("#idx");
if(!writer.val()){
alert("이름을 입력하세요");
writer.focus();
return;
}
if(!memo.val()){
alert("메모를 입력하세요");
memo.focus();
return;
}
//폼 데이터를 처리할 주소 지정
document.form1.action="${path}/memo_servlet/update.do";
//폼 데이터를 서버로 제출
document.form1.submit();
});
$("#btnDelete").click(function(){
if(confirm("삭제 하시겠습니까?")){
document.form1.action="${path}/memo_servlet/del.do";
document.form1.submit();
}
});
});
</script>
</head>
<body>
<h2>메모 편집</h2>
<form name="form1" id="form1" method="post">
<table border="1">
<tr>
<td>이름</td>
<td>
<input name="writer" id="writer" value="${dto.writer }">
</td>
</tr>
<tr>
<td>메모</td>
<td><input name="memo" id="memo" value="${dto.memo}"></td>
</tr>
<tr align="center">
<td colspan="2">
<input type="hidden" name="idx" value="${dto.idx }" >
<input type="button" value="수정" id="btnUpdate">
<input type="button" value="삭제" id="btnDelete">
</td>
</tr>
</table>
</form>
</body>
</html>
댓글 ( 4)
댓글 남기기