도도의 초록누리

IDE: Spring-Tool Suite 4-4.7.2 RELEASES

Databases: Oracle Databases 19g

 - Maven 3.6.3/1.16.0.20200610-1735

 - javax.servlet-api (4.0.1) - servlet(서블렛)

 - jstl (1.2) - jstl 태그 적용

 - taglibs (1.1.2) - c태그 적용

 - HikariCP (3.4.2) - 커넥션 풀

 - com.oracle.database.jdbc - ojdbc8 (19.7.0.0)

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

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>

  <groupId>com.boardMaven</groupId>

  <artifactId>web</artifactId>

  <version>0.0.1-SNAPSHOT</version>

  <packaging>war</packaging>

  <name>board Maven Webapp</name>

  <!-- FIXME change it to the project's website -->

  <url>http://www.example.com</url>

  <properties>

    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

    <maven.compiler.source>1.7</maven.compiler.source>

    <maven.compiler.target>1.7</maven.compiler.target>

  </properties>

  <dependencies>

    <dependency>

      <groupId>junit</groupId>

      <artifactId>junit</artifactId>

      <version>4.11</version>

      <scope>test</scope>

    </dependency>

<!-- Servlet -->

<!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->

<dependency>

    <groupId>javax.servlet</groupId>

    <artifactId>javax.servlet-api</artifactId>

    <version>4.0.1</version>

    <scope>provided</scope>

</dependency>

<!-- JSTL -->

<dependency>

<groupId>javax.servlet</groupId>

<artifactId>jstl</artifactId>

<version>1.2</version>

</dependency>

<!-- taglibs -->

<dependency>

<groupId>taglibs</groupId>

<artifactId>standard</artifactId>

<version>1.1.2</version>

<scope>compile</scope>

</dependency>

<!-- https://mvnrepository.com/artifact/com.zaxxer/HikariCP -->

<dependency>

    <groupId>com.zaxxer</groupId>

    <artifactId>HikariCP</artifactId>

    <version>3.4.2</version>

</dependency>

<dependency>

<groupId>com.oracle.database.jdbc</groupId>

<artifactId>ojdbc8</artifactId>

<version>19.7.0.0</version>

</dependency>

  </dependencies>

  <build>

    <finalName>board</finalName>

    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->

      <plugins>

        <plugin>

          <artifactId>maven-clean-plugin</artifactId>

          <version>3.1.0</version>

        </plugin>

        <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->

        <plugin>

          <artifactId>maven-resources-plugin</artifactId>

          <version>3.0.2</version>

        </plugin>

        <plugin>

          <artifactId>maven-compiler-plugin</artifactId>

          <version>3.8.0</version>

        </plugin>

        <plugin>

          <artifactId>maven-surefire-plugin</artifactId>

          <version>2.22.1</version>

        </plugin>

        <plugin>

          <artifactId>maven-war-plugin</artifactId>

          <version>3.2.2</version>

        </plugin>

        <plugin>

          <artifactId>maven-install-plugin</artifactId>

          <version>2.5.2</version>

        </plugin>

        <plugin>

          <artifactId>maven-deploy-plugin</artifactId>

          <version>2.8.2</version>

        </plugin>

      </plugins>

    </pluginManagement>

  </build>

</project>

폴더명: Resource

파일명: db.properties

jdbcUrl=jdbc:oracle:thin:@localhost:1521:orcl

dataSourceClassName=oracle.jdbc.driver.OracleDriver

dataSource.user=userName

dataSource.password=password

cachePrepStmts=true

prepStmtCacheSize=250

prepStmtCacheSqlLimit=2048

<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>Archetype Created Web Application</display-name>

  <servlet>

  <servlet-name>FrontController</servlet-name>

  <servlet-class>com.smile.web.controller.FrontController</servlet-class>

  <init-param>

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

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

</init-param>

  </servlet>

  <servlet-mapping>

  <servlet-name>FrontController</servlet-name>

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

  </servlet-mapping>

</web-app>

폴더1: /src/main/webapp/WEB-INF/settings

폴더2: /src/main/webapp/WEB-INF/views

파일: /src/main/webapp/WEB-INF/settings/root-servlet.xml

실제경로: /target/generated-sources/annotations/com/smile/web/controller

패키지명: com.smile.web.controller

파일명: FrontController.java

package com.smile.web.controller;

import java.io.IOException;

import java.sql.SQLException;

import javax.servlet.ServletConfig;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.smile.web.db.DBFactory;

public class FrontController extends HttpServlet {

private static final long serialVersionUID = 1L;

private String charset = null;

/**

* @see HttpServlet#doGet(HttpServletRequest req, HttpServletResponse res)

*/

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

doAction(req, res);

}

/**

* @see HttpServlet#doPost(HttpServletRequest req, HttpServletResponse res)

*/

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

doAction(req, res);

}

// FrontController 패턴 & Command 패턴

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

ServletConfig sc = this.getServletConfig();

charset = sc.getInitParameter("charset");

req.setAttribute("charset", charset);

req.setCharacterEncoding(charset);

res.setContentType("text/html; charset=" + charset);

String uri = req.getRequestURI();

String conPath = req.getContextPath();

String command = uri.substring(conPath.length());

Controller subController = null;

if(command.equals("/board/list.do")){

System.out.println("-----------------------------");

System.out.println("게시판 목록");

System.out.println("-----------------------------");

req.setAttribute("controllerName", "list");

subController = new com.smile.web.controller.board.ListController();

subController.execute(req, res);

}

}

}

실제경로: /target/generated-sources/annotations/com/smile/web/controller

패키지명: com.smile.web.controller

파일명: Controller.java

package com.smile.web.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;

}

실제경로: /target/generated-sources/annotations/com/smile/web/controller/board

패키지명: com.smile.web.controller.board

파일명: ListController.java

package com.smile.web.controller.board;

import java.io.IOException;

import java.net.URLEncoder;

import java.util.List;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.smile.web.controller.Controller;

import com.smile.web.logic.Paging;

import com.smile.web.model.Board;

import com.smile.web.service.BoardService;

import com.smile.web.util.HttpUtil;

public class ListController implements Controller {

@Override

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

String controllerName = (String) req.getAttribute("controllerName");

BoardService service = BoardService.getInstance();

List<Board> boardList = null;

// long totalCount = boardList.size();

long currentPage = 1; // 기본값

long pageSize = 10;

long totalCount = service.getTotalCount();

long startNum, endNum;

String keyword = null;

String pagingUrl = controllerName + ".do?";

// 페이지 번호 존재할 때

if (req.getParameter("page") != null){

currentPage = Integer.valueOf( req.getParameter("page") );

}

// 키워드가 존재할 때

if (req.getParameter("keyword") != null) {

keyword = req.getParameter("keyword");

totalCount = service.getTotalKeywordCount(keyword);

// 키워드 값이 하나라도 존재할 때

if ( keyword.length() > 0) {

pagingUrl = pagingUrl + "keyword=" + URLEncoder.encode(keyword, "UTF-8") + "&";

}

}

        Paging paging = new Paging();

        /*

        paging.setPageNo(1);

        paging.setPageSize(10);

        paging.setTotalCount(totalCount);

        */

        paging.setPageNo(currentPage);

        paging.setPageSize(pageSize);

        paging.setTotalCount(totalCount);

        System.out.println("현재페이지번호:" + currentPage);

        System.out.println("페이지크기:" + pageSize);

        System.out.println("키워드:" + keyword);

        System.out.println("페이징URL:" + pagingUrl);

        startNum = paging.getDbStartNum();

        endNum = paging.getDbEndNum();

// 키워드가 존재할 때

if (keyword != null) {

// 키워드 값이 하나라도 존재할 때

if ( keyword.length() > 0) {

boardList = service.getBoardKeywordList(keyword, startNum, endNum);

}

else {

boardList = service.getBoardList(startNum, endNum);

}

}

else {

        boardList = service.getBoardList(startNum, endNum);

}

        req.setAttribute("paging", paging);

        req.setAttribute("list", boardList);

        req.setAttribute("pagingUrl", pagingUrl);

HttpUtil.forward(req, res, "/WEB-INF/views/board/" + controllerName + ".jsp");

}

}

실제경로: /target/generated-sources/annotations/com/smile/web/util

패키지명: com.smile.web.controller.util

파일명: HttpUtil.java

package com.smile.web.util;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import java.io.PrintWriter;

import javax.servlet.RequestDispatcher;

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 HttpUtil extends HttpServlet {

private static final long serialVersionUID = 1L;

private static String charset = null;

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 e) {

e.printStackTrace();

}

}

public static void fileUpload(HttpServletRequest req, HttpServletResponse res,

String path) throws ServletException, IOException {

charset = (String) req.getAttribute("charset");

System.out.println(charset);

PrintWriter out = res.getWriter();

// 파일 업로드된 경로

String root = req.getSession().getServletContext().getRealPath("/");

String savePath = root + "upload" + File.separator + "upload";

// 서버에 실제 저장된 파일명

String filename = "1600955663095" ;

System.out.println("파일 실제 폴더경로:" + savePath);

// 실제 내보낼 파일명

String orgfilename = "license한글.txt" ;

req.setCharacterEncoding(charset);

res.setCharacterEncoding(charset);

InputStream in = null;

OutputStream os = null;

File file = null;

boolean skip = false;

String client = "";

try{

    // 파일을 읽어 스트림에 담기

    try{

        file = new File(savePath, filename);

        in = new FileInputStream(file);

    }catch(FileNotFoundException fe){

        skip = true;

    }

    client = req.getHeader("User-Agent");

    // 파일 다운로드 헤더 지정

    res.reset() ;

    res.setContentType("application/octet-stream");

    res.setHeader("Content-Description", "JSP Generated Data");

    if(!skip){

        // IE

        if(client.indexOf("MSIE") != -1){

            res.setHeader ("Content-Disposition", "attachment; filename="+new String(orgfilename.getBytes("KSC5601"),"ISO8859_1"));

        }else{

            // 한글 파일명 처리

            orgfilename = new String(orgfilename.getBytes("KSC5601"),"iso-8859-1");

            res.setHeader("Content-Disposition", "attachment; filename=\"" + orgfilename + "\"");

            res.setHeader("Content-Type", "application/octet-stream; charset=utf-8");

        }  

        res.setHeader ("Content-Length", ""+file.length() );

        os = res.getOutputStream();

        byte b[] = new byte[(int)file.length()];

        int leng = 0;

        while( (leng = in.read(b)) > 0 ){

            os.write(b,0,leng);

        }

    }else{

    // 한글 깨짐 - 해결

    res.setContentType("text/html;charset=" + charset);

        out.println("<html><head>");

        out.println("<script language='javascript'>alert('파일을 찾을 수 없습니다.');history.back();</script>");

        out.println("</head><body></body></html>");

    }

    in.close();

    os.close();

}catch(Exception e){

e.printStackTrace();

}

}

}

실제경로: /target/generated-sources/annotations/com/smile/web/db

패키지명: com.smile.web.controller.db

파일명: DBFactory.java

package com.smile.web.db;

import java.io.IOException;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

import java.io.IOException;

import java.io.InputStream;

import java.io.Reader;

import java.util.Properties;

import java.sql.Connection;

import java.sql.SQLException;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import com.zaxxer.hikari.HikariConfig;

import com.zaxxer.hikari.HikariDataSource;

import oracle.jdbc.pool.OracleDataSource;

public class DBFactory {

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

    private static String CLASSNAME;

    private static String JDBC_URL;

    private static String USERNAME;

    private static String PASSWORD;

    private static String CACHE_PREP_STMTS;

    private static HikariDataSource ds;

    private HikariConfig config;

    public DBFactory() {

    InputStream inputStream;

    config = new HikariConfig();

        String resource = "db.properties";

        Properties properties = new Properties();

        try {

        inputStream = getClass().getClassLoader().getResourceAsStream(resource);

            properties.load(inputStream);

            System.out.println("jdbcurl:" + properties.getProperty("jdbcUrl"));

            System.out.println("className" + properties.getProperty("dataSourceClassName"));

            CLASSNAME = properties.getProperty("dataSourceClassName");

            JDBC_URL = properties.getProperty("jdbcUrl");

            USERNAME = properties.getProperty("dataSource.user");

            PASSWORD = properties.getProperty("dataSource.password");

            CACHE_PREP_STMTS = properties.getProperty("cachePrepStmts");

            config.setDriverClassName(CLASSNAME);

            config.setJdbcUrl( JDBC_URL );

            config.setUsername( USERNAME );

            config.setPassword( PASSWORD );

            config.addDataSourceProperty( "cachePrepStmts" , CACHE_PREP_STMTS );

            config.addDataSourceProperty( "prepStmtCacheSize" , "250" );

            config.addDataSourceProperty( "prepStmtCacheSqlLimit" , "2048" );

            ds = new HikariDataSource( config );

        } catch (IOException e) {

            e.printStackTrace();

        }

    }

    public Connection getConnection() throws SQLException {

    /*

        try(Connection con = ds.getConnection()){

            System.out.println("연결상태확인:" + con);

            String sql = "SELECT * from board where id > 4 and id < 10";

            PreparedStatement pstmt = con.prepareStatement(sql);

            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {

                System.out.println("진짜 연결되었는가:" + rs.getString(2));

            }

        }

        catch(Exception e) {

        System.out.println("연결실패확인:" + e.getMessage());

        }

    */

        return ds.getConnection();

    }

    public void close(Connection conn, PreparedStatement ps, ResultSet rs) {

if ( rs != null ) {

try {

rs.close();

}

catch(Exception ex) {

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

}

close(conn, ps); // Recursive 구조 응용(재귀 함수)

} // end of if

}

public void close(Connection conn, PreparedStatement ps) {

if (ps != null ) {

try {

ps.close();

}

catch(Exception ex) {

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

}

} // end of if

if (conn != null ) {

try {

conn.close();

}

catch(Exception ex) {

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

}

} // end of if

}

}

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

https://yyman.tistory.com/1429