SERVLET&JSP 4차시 - memberCRUD 복습, 동적쿼리, 익스프레션언어, JSTL(EL태그)

회원가입, 회원목록 => Mybatis

MYBATIS로 구현하기

 

 

1. [x]member테이블 생성

2. [x]Member객체(직렬화)

3. [x]Member.xml : 네임스페이스, mapstatement <insert><select>

4. [x]mybatis-config.xml -> 추가, typealias추가

5. [x]MemberMapper(인터페이스) => InsertMember(Member m), memberList()

6. [x]MemberDAO 생성 => SqlSession => mybatis호출

7. [x]JSP 출력

 

1. create table member(

seq NUMBER,

id VARCHAR(20),

pass VARCHAR(20),

name VARCHAR(20),

age NUMBER

        );

 

CREATE SEQUENCE member_seq;

DROP SEQUENCE member_seq;

 

 

--member.java

public class Member implements Serializable

 

--member.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">

 

 

 

<mapper namespace="kosta.mapper.MemberMapper">

<cache />

<insert id="insertMember" parameterType="Member">

insert into member values(member_seq.nextval, #{id}, #{pass}, #{name}, #{age})

</insert>

<select id="listMember" resultType="Member">

select * from member order by seq desc

</select>

</mapper>

 

--mybatis-config.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>

<typeAliases>

<typeAlias type="kosta.bean.Board" alias = "Board"/>

<typeAlias type="kosta.bean.Search" alias = "Search"/>

</typeAliases>

<environments default="development">

<environment id="development">

<transactionManager type="JDBC"/>

<dataSource type="JNDI">

<property name="data_source" value="java:comp/env/jdbc/oracle"/>

</dataSource>

</environment>

</environments>

<mappers>

<mapper resource="kosta/mapper/Board.xml"/>

</mappers>

</configuration>

 

 

--membermapper.java(interface)

package kosta.mapper;

 

import java.util.List;

 

import kosta.bean.Member;

 

public interface MemberMapper {

int insertMember(Member member);

List<Member> listMember();

}

 

--MemberDAO2.java

package kosta.bean;

 

import java.io.InputStream;

import java.util.List;

 

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 kosta.mapper.MemberMapper;

 

 

public class MemberDAO2 {

private static MemberDAO2 dao = new MemberDAO2();

   

    public static MemberDAO2 getInstance() {

        return dao;

    }

 

    public SqlSessionFactory getSqlSessionFactory() {

        String resource = "mybatis-config.xml";

        InputStream in = null;

       

        try {

          in = Resources.getResourceAsStream(resource);

        } catch (Exception e) {

          e.printStackTrace();

        }

        return new SqlSessionFactoryBuilder().build(in);

    }

   

   

    public int insertMember(Member member) {

        int re=-1;

        SqlSession sqlSession = getSqlSessionFactory().openSession();

        try {

          re = sqlSession.getMapper(MemberMapper.class).insertMember(member);

          if(re>0) {

              sqlSession.commit(); 

          }else {

              sqlSession.rollback();

          }

        } catch (Exception e) {

          e.printStackTrace();

        }finally {

          if(sqlSession!=null) {

              sqlSession.close();

          }

        }

        return re;

    }

   

   

    public List<Member> listMember() {

        SqlSession sqlSession = getSqlSessionFactory().openSession();

        List<Member> list = null;

       

        try {

          list = sqlSession.getMapper(MemberMapper.class).listMember();

          // list=sqlSession.selectList("kosta.mapper.MemberMapper.listMember");

   

        } catch (Exception e) {

          e.printStackTrace();

        }finally {

          if(sqlSession!=null) {

              sqlSession.close();

          }

        }

        return list;

    }

   

 

}

 

--Advanced Result 매핑

 

--association (nested selection)

->class 안에 있는 class멤버변수 데이터를 가져오고 싶을 때 사용

class Blog{

class Author{}

}

 

--collection 형태

class Blog{

List<Blog> lists

}

 

 

resultType : 해당하는 데이터 값 다 가져옴

resultMap : 컬럼명이랑 id이름이 달라서 map사용해야하고 매칭된 값만 가져오기 위해

 

 

--동적 SQL

• IF

 

->mybatis까지 데이터를 넘겨주기 위한 방법

1. 객체로 만들어서 넣기,

2. MAP

 

title LIKE %aa%

item안에 title이랑 writer 들어가 있어서 쓸 수 있음

${item} -> like 앞에 절 title 문자열 그대로 가져오고 싶을때

#{searchKey} -> 변수값 그대로 가져오고 싶을 때

 

 

parameterType 2가지

1. 객체 search로 만들어서 사용

--list.jsp

 

<%@page import="java.util.HashMap"%>

<%@page import="java.util.Map"%>

<%@page import="kosta.bean.Search"%>

<%@page import="kosta.bean.Board"%>

<%@page import="java.util.List"%>

<%@page import="kosta.bean.BoardDAO2"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>   

   

<%

request.setCharacterEncoding("utf-8");

Search search = new Search();

search.setArea(request.getParameterValues("area")); //search객체 안에 담음

search.setSearchKey("%"+request.getParameter("searchKey")+"%"); //%%안에 문자열 넣을때 ''사용 안해도 됨

BoardDAO2 dao = BoardDAO2.getInstance();

List<Board> list = dao.listBoard(search);

request.setAttribute("list", list);

%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>Insert title here</title>

</head>

<body>

<a href = "insert_form.jsp">글쓰기</a>

<table border = "1">

<tr>

<td>글번호</td>

<td>글제목</td>

<td>작성자</td>

<td>작성일자</td>

<td>조회수</td>

</tr>

<c:forEach var = "board" items = "${list}">

            <tr>

<td>${board.seq }</td>

<td><a href = "detail.jsp?seq=${board.seq}">${board.title }</a></td>

<td>${board.writer }</td>

<td>

<fmt:parseDate var= "dt" value = "${board.regdate}" pattern="yyyy-MM-dd HH:mm:ss"/>

<fmt:formatDate value="${dt}" pattern = "yyyy/MM/dd"/>

</td>

<td>${board.hitcount}</td>

</tr>

</c:forEach>

</table>

<br><br>

<form action = "list.jsp" method = "post">

<input type ="checkbox" name = "area" value ="title">제목

<input type ="checkbox" name = "area" value ="writer">작성자

<input type = "text" name = "searchKey" size = "10">

<input type = "submit" value = "검색"><br>

<a href = "list.jsp">list로 다시 돌아가기</a>

</form>

</body>

</html>

 

 

--Board.xml

<select id="listBoard" parameterType="Search" resultType="Board" >

select * from board

<if test="area != null">

<where>

<!-- where (title LIKE %aa% OR writer LIKE %aa% 조건이3개 이므로 여러개일때-->

<foreach collection="area" item = "item" separator="OR" open ="(" close=")" >

${item} LIKE #{searchKey}

</foreach>

</where>

</if>

order by seq desc

</select>

--mybatis-config.xml

<configuration>

<typeAliases>

<typeAlias type="kosta.bean.Board" alias = "Board"/>

<typeAlias type="kosta.bean.Search" alias = "Search"/>

</typeAliases>

<environments default="development">

<environment id="development">

<transactionManager type="JDBC"/>

<dataSource type="JNDI">

<property name="data_source" value="java:comp/env/jdbc/oracle"/>

</dataSource>

</environment>

</environments>

<mappers>

<mapper resource="kosta/mapper/Board.xml"/>

</mappers>

</configuration>

 

--BoardMapper

List<Board> listBoard(Search search)

 

--BoardDAO2

    public List<Board> listBoard(Search search) {

        SqlSession sqlSession = getSqlSessionFactory().openSession();

        List<Board> list = null;

       

        try {

            list = sqlSession.getMapper(BoardMapper.class).listBoard(search);

            //list=sqlSession.selectList("kosta.mapper.BoardMapper.listBoard");

     

        } catch (Exception e) {

            e.printStackTrace();

        }finally {

            if(sqlSession!=null) {

              sqlSession.close();

            }

        }

        return list;

      }

 

1. java.util.Map 사용

 

        <select id="listBoard" parameterType="java.util.Map" resultType="Board" >

 

 

request.setCharacterEncoding("utf-8");

Map map= new HashMap();

BoardDAO2 dao = BoardDAO2.getInstance();

map.put("area", request.getParameterValues("area"));

map.put("searchKey", "%"+request.getParameter("searchKey")+"%");

        request.setAttribute("list", list);

 

 

 

--익스프레션 언어

 

내장객체 : request 객체

 

**데이터 전달 방식 : 폼태그, 에이태그 &? , session내장객체

servlet&jsp : 페이지페이지마다 데이터를 잘 전달해야 함.

 

★★엠프티 연산자

 

--el_exam

<%

request.setAttribute("name", "kosta");

 

Member m = new Member();

m.setName("홍길동");

request.setAttribute("member", m);

HashMap<String, String> map = new HashMap<String, String>();

map.put("samsung", "이승엽");

request.setAttribute("baseball", map);

%>

<body>

<jsp:forward page="el_result.jsp">

<jsp:param value = "apple" name = "fruit"/>

</jsp:forward>

</body>

 

--el_result(표현식 대체)

before : <%= request.getParameter("fruit") %>

after : ${param.fruit}

 

 

before : <%= request.getAttribute("name") %>

after : ${name}

 

 

 

        before : <%= ((Member)request.getAttribute("member")).getName() %>

 

after : ${member.name}

 

 

before : <%= ((HashMap)request.getAttribute("baseball")).get("samsung") %>

 

after : ${baseball.samsung}

 

 

 

--JSTL

 

JSP페이지를 작성할 때 유용하게 사용할 수 있는 여러가지 커스텀 액션과 함수를 포함하고 있는 라이브러리

로직관련 부분을 태그화할 수 있음.

 

-태그라이브러리 식별자를 처음에 선언해줘야 함

EL태그 : 표현식을 쓰지 않고 간단하게 표현하고자

JSTL의 목적 : JSP상에서 스크립트 코드의 최소화(자바코드의 최소화)

 

--jstl_exam.jsp

<%@page import="java.util.ArrayList"%>

<%@page import="kosta.bean.Member"%>

<%@page import="java.util.List"%>

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

 

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>Insert title here</title>

</head>

<body>

<%

String hello = "Hello!!!";

%>

<c:set var="msg" value="Hello2"/>

<c:out value ="${msg}"></c:out>

 

<!-- 구구단 4단 -->

<ul>

<c:forEach var = "i" begin="1" end ="9">

<li> 4 * ${i} = ${4*1}</li>

</c:forEach>

</ul>

<%

List<Member> list = new ArrayList<Member>();

list.add(new Member("aa","홍길동"));

list.add(new Member("bb","이길동"));

list.add(new Member("cc","박길동"));

 

request.setAttribute("list", list);

%>

<table border="1">

<tr>

<th>아이디</th>

<th>이름</th>

</tr>

<c:forEach var="m" items="${list}">

<tr>

<th>${m.id}</th>

<th>${m.name}</th>

</tr>

</c:forEach>

</table>

 

 

        <c:redirect url="jstl_exam2.jsp">

<c:param name="fruit" value="orange"></c:param>

</c:redirect>

</body>

</html>

 

--jstl_exam2

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>Insert title here</title>

</head>

<body>

<c:set var="fruit" value="${param.fruit}"></c:set>

<!-- test값엔 항상 el태그가 들어가야함 -->

<c:if test="${fruit == 'apple'}">

<c:out value="${fruit}"></c:out>

</c:if>

<!-- if else 역할 -->

<c:choose>

<c:when test="${fruit =='apple'}">

<c:out value="${fruit} : 사과"></c:out>

</c:when>

<c:when test="${fruit =='orange'}">

<c:out value="${fruit} : 오렌지"></c:out>

</c:when>

<c:otherwise>

<c:out value="${fruit} : 기타과일"></c:out>

</c:otherwise>

</c:choose>

</body>

</html>

 

-->결과

orange : 오렌지

 

 

<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>

 

 

<c:set var="now" value="<%=new Date() %>"></c:set>

before : ${now}

 

after : <fmt:formatDate value="${now}" pattern="yyyy-MM-dd"/>

 

 

        before : ${500000000} 원

after : <fmt:formatNumber value="${500000000}" type="currency"                                currencySymbol="$"></fmt:formatNumber>

 

소숫점 둘째짜리까지 표시 : <fmt:formatNumber value = "3.141592" pattern="#.00"/>

 

퍼센트 표시 : <fmt:formatNumber value="0.9" type="percent"/>

 

 

결과:

before : Tue Sep 22 15:56:56 KST 2020

after : 2020-09-22

 

before : 500000000 원 after : $500,000,000.00

소숫점 둘째짜리까지 표시 : 3.14

퍼센트 표시 : 90%

 

--list.jsp (배웠던 EL태그와 JSTL을 사용하여 CRUD 수정하기)

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>

 

request.setAttribute("list", list);

 

<a href = "insert_form.jsp">글쓰기</a>

<table border = "1">

<tr>

<td>글번호</td>

<td>글제목</td>

<td>작성자</td>

<td>작성일자</td>

<td>조회수</td>

</tr>

<td><%= board.getSeq() %></td>

<td><a href = "detail.jsp?seq=<%= board.getSeq() %>"><%= board.getTitle() %></a></td>

<td><%= board.getWriter() %></td>

<td><%= board.getRegdate() %></td>

<td><%= board.getHitcount() %></td>

</tr>

<% } %>

</table>

 

-->

 

<a href = "insert_form.jsp">글쓰기</a>

<table border = "1">

<tr>

<td>글번호</td>

<td>글제목</td>

<td>작성자</td>

<td>작성일자</td>

<td>조회수</td>

</tr>

<c:forEach var = "board" items = "${list}">

                    <tr>

<td>${board.seq }</td>

<td><a href = "detail.jsp?seq=${board.seq}">${board.title }</a></td>

<td>${board.writer }</td>

<td>

<fmt:parseDate var= "dt" value = "${board.regdate}" pattern="yyyy-MM-dd HH:mm:ss"/>

<fmt:formatDate value="${dt}" pattern = "yyyy/MM/dd"/>

</td>

<td>${board.hitcount}</td>

</tr>

</c:forEach>

</table>