도도의 초록누리

1. [JSP] 17. JSP/Servlet MVC2 - 페이징네이션과 검색 그리고 오라클 프로젝트 (1), 2020-09-30
- https://yyman.tistory.com/1428

2. [JSP] 18. JSP/Servlet MVC2 - 페이징네이션과 검색 그리고 오라클 프로젝트 (2), 2020-09-30
- https://yyman.tistory.com/1429

- 사용 프로그램: SQL Developer (Oracle)

- DB: Oracle Databases [11, 19g]

-- Oracle 11 - 자동번호 생성 테이블 정의
-- Table 생성 (BOARD)
-- NEW.ID (Table의 id를 가리킴)
CREATE TABLE board
(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(30),
    subject VARCHAR2(30),
    memo NCLOB,
    count NUMBER,
    regidate DATE
);

-- Sequence 정의
CREATE SEQUENCE board_sequence
START WITH 1
INCREMENT BY 1;

-- Trigger 생성
-- BEFORE INSERT on '테이블명'
CREATE OR REPLACE TRIGGER board_trigger
BEFORE INSERT
    ON board
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT board_sequence.nextval INTO :NEW.ID FROM dual;
END;

/* 데이터 추가 */
INSERT INTO board (name, subject, memo, count, regidate) VALUES ('홍길동', '안녕하세요.', '메모메모', '0', '2020-09-29 11:11:00');

/* 데이터 등록 후 커밋할 것(대량 정보 처리 후) */
COMMIT;

-- 싱글 쿼리 (페이징)
SELECT * FROM (
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM (
SELECT * from board order by id desc
) Z WHERE ROWNUM <= 10
) WHERE RNUM >= 1

-- 특정 싱글 쿼리 SQL
SELECT * FROM (
SELECT /*+ INDEX_DESC(Z OP_SAMPLE_PK) */ ROWNUM AS RNUM, Z.* FROM (
SELECT * from board where subject like '%야해해%' order by id desc
) Z WHERE ROWNUM <= 10
) WHERE RNUM >= 1

CREATE TABLE foodmenu_tbl
(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(30),
    price NUMBER,
    store_id NUMBER,
    cnt NUMBER,
    regidate DATE
);

-- Table 생성 (FOODSTORE_TBL)
CREATE TABLE foodstore_tbl
(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(30),
    address VARCHAR2(30),
    regidate DATE
);

-- 2020-10-07 SQL Paging Upgrade

-- 조회 관련 태스트
select f1.id, f1.name, f1.price, f2.name as storename from foodmenu_tbl f1, foodstore_tbl f2 where f1.store_id = f2.id
select count(*) from foodmenu_tbl f1, foodstore_tbl f2 where f1.store_id = f2.id

-- Oracle 11 - 자동번호 생성 테이블 정의
-- Table 생성 (FOODMENU_TBL)
-- NEW.ID (Table의 id를 가리킴)
CREATE TABLE foodmenu_tbl
(
    id NUMBER PRIMARY KEY,
    name VARCHAR2(30),
    price NUMBER,
    store_id NUMBER,
    cnt NUMBER,
    regidate DATE
);

-- Sequence 정의
CREATE SEQUENCE foodmenu_sequence
START WITH 1
INCREMENT BY 1;

-- Trigger 생성
-- BEFORE INSERT on '테이블명'
CREATE OR REPLACE TRIGGER foodmenu_trigger
BEFORE INSERT
    ON foodmenu_tbl
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT foodmenu_sequence.nextval INTO :NEW.ID FROM dual;
END;



----------------------------------------------------------------------

일반 범용 "게시판"에서 사용될 수 있는 것 위주로 작성하였다.

* IDE: Spring Tool-Suite 4(Eclipse)

-> Spring Legacy Project의 Spring MVC Project를 기반으로 작성함.

이 프로젝트의 특징은 추후에 MyBatis 등의 라이브러리까지 고민하여 작성하였다.

그림 4. 프로젝트 구성(Controller와 VO)

그림 5. 프로젝트 구성(view영역의 jsp 파일)

package com.springMVC1.web.vo;

public class MemberVO {

private String id;

private String passwd;

public String getId() {

return id;

}

public void setId(String id) {

this.id = id;

}

public String getPasswd() {

return passwd;

}

public void setPasswd(String passwd) {

this.passwd = passwd;

}

}

package com.springMVC1.web.controller;

import java.text.DateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Locale;

import java.util.Map;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.stereotype.Controller;

import org.springframework.ui.Model;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import com.springMVC1.web.vo.MemberVO;

@Controller

@RequestMapping(value="/board")

public class BoardController {

private static final Logger logger = LoggerFactory.getLogger(BoardController.class);

@RequestMapping(value = "/list", method = RequestMethod.GET)

public String list(Locale locale, Model model) {

logger.info("Welcome board - list! The client locale is {}.", locale);

Date date = new Date();

DateFormat dateFormat = DateFormat.getDateTimeInstance(DateFormat.LONG, DateFormat.LONG, locale);

String formattedDate = dateFormat.format(date);

model.addAttribute("serverTime", formattedDate );

return "/board/list";

}

// GET, POST 방식 모두 가능

@RequestMapping(value = "insert")

public String insert(Locale locale, Model model) {

return "/board/insert";

}

// POST방식만 사용

@RequestMapping(value = "insertResult", method=RequestMethod.POST)

public String insertResult(Model model, MemberVO memberVO) {

model.addAttribute("serverTime", "1군");

logger.info("로거 사용"); // 로거

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

MemberVO node1 = new MemberVO();

// 1번 회원

node1.setId("user1");

node1.setPasswd("1234");

map.put("1", node1);

// 2번 회원

node1 = new MemberVO();

node1.setId("user2");

node1.setPasswd("9876");

map.put("2", node1);

// model 속성으로 등록

model.addAttribute("map", map);

return "/board/insert_result_post";

}

// GET방식만 사용 - 편법(매개변수 하나 더 추가) 

@RequestMapping(value = "insertResult", method=RequestMethod.GET)

public String insertResult(Model model, MemberVO memberVO, String s) {

model.addAttribute("serverTime", "2군");

logger.info("로거 사용"); // 로거

// 다중 회원 목록 출력 실험

List<MemberVO> member = new ArrayList<MemberVO>();

MemberVO node1 = new MemberVO();

// 1번 회원

node1.setId("user1");

node1.setPasswd("1234");

member.add(node1);

// 2번 회원

node1 = new MemberVO();

node1.setId("user2");

node1.setPasswd("9876");

member.add(node1);

model.addAttribute("list", member);

return "/board/insert_result_get";

}

}

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

    pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>글쓰기 - 양식</title>

</head>

<body>

<%

String context = request.getContextPath();

%>

<h3>POST 방식</h3>

<form action="<%=context%>/board/insertResult" method="POST">

ID: <input type="text" name="id"><br/>

비밀번호: <input type="password" name="passwd"><br/>

<input type="submit" value="전송">

</form>

<br />

<h3>GET 방식</h3>

<form action="<%=context%>/board/insertResult" method="GET">

ID: <input type="text" name="id"><br/>

비밀번호: <input type="password" name="passwd"><br/>

<input type="submit" value="전송">

</form>

</body>

</html>

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

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

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

    pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>등록 결과 - GET방식(실험)</title>

</head>

<body>

<!-- 결과 -->

<h3>GET 방식 - 실험</h3>

<h5>GET으로 전송받은 데이터</h5>

<table border="1" style="width:500px">

<tr>

<td>서버시간</td>

<td>${serverTime }</td>

</tr>

<tr>

<td>ID</td>

<td>${memberVO.id}</td>

</tr>

<tr>

<td>비밀번호</td>

<td>${memberVO.passwd }</td>

</tr>

</table>

<br />

<h5>ArrayList 결과 출력하기</h5>

<!-- 다중 회원 출력 -->

<table border="1" style="width:500px">

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

<tr>

<td>ID</td>

<td>${list.id}</td>

<td>비밀번호</td>

<td>${list.passwd }</td>

</tr>

</c:forEach>

</table>

</body>

</html>

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

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

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

    pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>등록 결과 - Post방식(실험)</title>

</head>

<body>

<!-- 결과 -->

<h3>POST 방식 - 실험</h3>

<br/>

<table border="1" style="width:500px">

<tr>

<td>서버시간</td>

<td>${serverTime }</td>

</tr>

<tr>

<td>ID</td>

<td>${memberVO.id}</td>

</tr>

<tr>

<td>비밀번호</td>

<td>${memberVO.passwd }</td>

</tr>

</table>

<h5>Map 결과 출력하기</h5>

<!-- 다중 회원 출력 -->

<table border="1" style="width:700px">

<c:forEach items="${map}" var="i">

<tr>

<td style="width:50px">1. Key값</td>

<td style="width:150px">${i.key}</td>

<td style="width:50px">2. 객체주소</td>

<td style="width:200px">${i.value}</td>

<%

%>

<td style="width:50px">3. 객체값(ID)</td>

<td style="width:100px">${i.value.id}</td>

<td style="width:50px">3. 객체값(Passwd)</td>

<td style="width:100px">${i.value.passwd}</td>

</tr>

</c:forEach>

</table>

</body>

</html>

* 형식문자

-> 년도 출력

<fmt:formatNumber value="${변수명}" pattern="yyyy-mm-dd" />

<fmt:formatNumber value="${객체명.멤버변수명}" pattern="yyyy-mm-dd" />

디자인 패턴은 프로그램을 개발할 때 사용하는 일정한 뼈대(프레임) 또는 틀이라고 생각하면 된다.

* 모델은 서비스와 데이터베이스 처리를 담당하는 역할을 하며, 각 로직처리, DB 질의 처리 기능을 수행한다.

* 뷰는 JSP, CSS, HTML를 사용하여 구현함. 

* 컨트롤러는 뷰와 모델을 중개하는 역할을 수행하며, 클라이언트가 전달한 파라마터를 추출하여 모델로 전달하고, 처리결과를 보여주는 기능을 한다.

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">

  <display-name>edu-jsp-MVC</display-name>

  <welcome-file-list>

    <welcome-file>index.html</welcome-file>

    <welcome-file>index.htm</welcome-file>

    <welcome-file>index.jsp</welcome-file>

    <welcome-file>default.html</welcome-file>

    <welcome-file>default.htm</welcome-file>

    <welcome-file>default.jsp</welcome-file>

  </welcome-file-list>

<!-- Front Controller 패턴 -->

<servlet>

<servlet-name>front</servlet-name>

<servlet-class>com.eduJsp.controller.FrontController</servlet-class>

<init-param>

<param-name>charset</param-name>

<param-value>UTF-8</param-value>

</init-param>

</servlet>

<servlet-mapping>

<servlet-name>front</servlet-name>

<url-pattern>*.do</url-pattern>

</servlet-mapping>

</web-app>

https://tomcat.apache.org/download-90.cgi

package com.eduJsp.controller;

import java.io.IOException;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

public interface Controller {

public void execute(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException;

}

두 개를 만들어야 함. (그림 17, 그림 18과정 - 반복)
- ClassName(클래스명): AddressInsertController

- ClassName(클래스명): AddressListController

package com.eduJsp.controller;

import java.io.IOException;

import java.util.HashMap;

import javax.servlet.ServletConfig;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

public class FrontController extends HttpServlet {

private static final long serialVersionUID = 1L;

private String charset = null;

private HashMap<String, Controller> list = null;

    public FrontController() {

        super();

    }

    @Override

public void init(ServletConfig sc) throws ServletException {

    charset = sc.getInitParameter("charset");

    list = new HashMap<String, Controller>();

    list.put("/board/insert.do", new AddressInsertController());

    list.put("/board/list.do", new AddressListController());

}

    @Override

protected void service(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {

    req.setCharacterEncoding(charset);

    String url = req.getRequestURI();

    String contextPath = req.getContextPath();

    String path = url.substring(contextPath.length());

    Controller subController = list.get(path);

    subController.execute(req, res);

}

}

package com.eduJsp.controller;

import java.io.IOException;

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

public class HttpUtil extends HttpServlet {

private static final long serialVersionUID = 1L;

public static void forward(HttpServletRequest req, HttpServletResponse res, 

String path) throws ServletException, IOException {

try {

RequestDispatcher dispatcher = req.getRequestDispatcher(path);

dispatcher.forward(req, res);

}catch(Exception ex) {

System.out.println("forward 오류:" + ex);

}

}

}

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

    pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>목록 - MVC 페이지</title>

</head>

<body>

<h3>목록 - MVC 페이지</h3>

</body>

</html>

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

    pageEncoding="UTF-8"%>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>삽입 - MVC 페이지</title>

</head>

<body>

<h3>삽입 - MVC 페이지</h3>

</body>

</html>

package com.eduJsp.controller;

import java.io.IOException;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

public class AddressInsertController implements Controller {

@Override

public void execute(HttpServletRequest req, HttpServletResponse res) throws

ServletException, IOException{

HttpUtil.forward(req, res, "/WEB-INF/view/addressInsert.jsp");

}

}

package com.eduJsp.controller;

import java.io.IOException;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

public class AddressListController implements Controller {

@Override

public void execute(HttpServletRequest req, HttpServletResponse res) throws

ServletException, IOException {

HttpUtil.forward(req, res, "/WEB-INF/view/addressList.jsp");

}

}

* UML 작성 프로그램: Modelio Open Source 4.0

package com.eduJsp.vo;

import java.sql.Date;

public class AddressVO {

private int num;

private String name;

private String address;

private Date birthdate;

public int getNum() {

return num;

}

public void setNum(int num) {

this.num = num;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

public Date getBirthdate() {

return birthdate;

}

public void setBirthdate(Date birthdate) {

this.birthdate = birthdate;

}

}

package com.eduJsp.service;

import java.util.List;

import com.eduJsp.vo.AddressVO;

public interface IAddress {

public AddressVO getAddress(Integer num) ;

public List<AddressVO> allData();

public int insertAddress(AddressVO addressVO);

public int updateAddress(AddressVO addressVO);

public int deleteAddress(AddressVO addressVO);

}

package com.eduJsp.service;

import java.util.List;

import com.eduJsp.vo.AddressVO;

public class AddressService implements IAddress{

@Override

public AddressVO getAddress(Integer num) {

return null;

}

@Override

public List<AddressVO> allData() {

// TODO Auto-generated method stub

return null;

}

@Override

public int insertAddress(AddressVO addressVO) {

// TODO Auto-generated method stub

return 0;

}

@Override

public int updateAddress(AddressVO addressVO) {

// TODO Auto-generated method stub

return 0;

}

@Override

public int deleteAddress(AddressVO addressVO) {

// TODO Auto-generated method stub

return 0;

}

}

여기 부분에 예를 들면, Resultset, Preparedstatement, Connection 등의 기능을 활용하여 SQL문장 등을 정의하는 것이다.

package com.eduJsp.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import com.eduJsp.vo.AddressVO;

public class AddressDAO {

    public AddressVO selectAddress(Integer num) {

    Connection conn = null;

    PreparedStatement pstmt = null;

    ResultSet rs = null;

    AddressVO node = new AddressVO();

    String sql = "select NUM, NAME, ADDRESS, BIRTHDATE " +

      " from addressbook" + 

      " where num=?";

    System.out.println(sql);

    // 달력 날짜 출력 버그 개선

    try {

    //conn = session.connect();

    pstmt = conn.prepareStatement(sql);

    pstmt.setInt(1, num);

    rs = pstmt.executeQuery();

    if ( rs.next() ) {

    node.setNum(rs.getInt(1));

    node.setName(rs.getNString(2));

    node.setAddress(rs.getNString(3));

    node.setBirthdate(rs.getDate(4));

    }

    }catch(Exception ex) {

    System.out.println("오류 발생: " + ex);

    }

    finally {

    // session.close(conn, pstmt, rs);

    }

        return node;

    }

}