도도의 초록누리

1. [JSP] 12. Jsp/Servlet(MVC) Maven 기반의 다중 파일 업로드, 다운로드 구현(1)

https://yyman.tistory.com/1414

2. [JSP] 12. Jsp/Servlet(MVC) Maven 기반의 다중 파일 업로드, 다운로드 구현(2)

https://yyman.tistory.com/1415

package com.fileWeb.controller;

import java.io.IOException;

import java.util.HashMap;

import java.util.Map;

import javax.servlet.ServletConfig;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import javax.servlet.http.HttpSession;

import com.fileWeb.util.HttpUtil;

/**

 * Servlet implementation class HomeController

 */

public class FrontController extends HttpServlet {

private static final long serialVersionUID = 1L;

private String charset = null;

    public FrontController() {

        super();

    }

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

doAction(req, 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);

System.out.println(charset);

String uri = req.getRequestURI();

System.out.println("uri : " + uri);

String conPath = req.getContextPath();

System.out.println("conPath : " + conPath);

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

System.out.println("command : " + command);

Controller subController = null;

System.out.println("reqMapSize : " + req.getParameterMap().size());

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

System.out.println("insert");

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

    subController = new BoardInsertController();

    subController.execute(req, res);

}else if (command.equals("/board/insertResult.do")) {

System.out.println("insertResult");

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

    subController = new BoardInsertResultController();

subController.execute(req, res);

}

else if(command.equals("/board/insertMultiResult.do")){

System.out.println("insertResult");

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

    subController = new BoardInsertMultiResultController();

subController.execute(req, res);

}else if(command.equals("/board/download.do")) {

System.out.println("download");

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

HttpUtil.fileDownload(req, res, null);

}else if(command.equals("/board/fileRemove.do")) {

System.out.println("remove");

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

    HttpUtil.removeFile(req, res);

}

}

}

package com.fileWeb.controller;

import java.io.IOException;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import com.fileWeb.util.HttpUtil;

public class BoardInsertMultiResultController implements Controller {

@Override

public void execute(HttpServletRequest req, HttpServletResponse res) throws

ServletException, IOException {

HttpUtil.uploadFile(req, res);

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

}

}

변경 전: com.fileWeb.controller.HttpUtil.java

변경 후: com.fileWeb.util.HttpUtil.java

package com.fileWeb.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 java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

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;

import org.apache.commons.fileupload.FileItem;

import org.apache.commons.fileupload.disk.DiskFileItemFactory;

import org.apache.commons.fileupload.servlet.ServletFileUpload;

public class HttpUtil extends HttpServlet {

private static final long serialVersionUID = 1L;

private static String charset = null; // 문자열

private static int SIZETHRESHOLD = 4096;

private static String UPLOAD_FOLDER = "upload";

private static String UPLOAD_TMP_FOLDER = File.separator + "WEB-INF" + File.separator + "temp";

private static long MAX_UPLOAD_SIZE = 3 * 1024 * 1024;

private static Map<String, Object> reqMap = null; // req 정보(MultiRequest)

private static Map<Integer, Map<String, Object>> fileMap = null; // 다중 파일 지원

private static int num = 0;

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 uploadFile(HttpServletRequest req, HttpServletResponse res) throws

ServletException, IOException {

fileMap = new HashMap<Integer, Map<String, Object>>(); 

reqMap = new HashMap<>();

num = 1;

        PrintWriter out = res.getWriter();

        out.println("<HTML><HEAD><TITLE>Multipart Test</TITLE></HEAD><BODY>");

try {

            //디스크상의 프로젝트 실제 경로얻기

            //String contextRootPath = "c:" + File.separator + "upload";

String dirName = UPLOAD_FOLDER ; 

// String dirName = "upload"; 

String contextRootPath = req.getSession().getServletContext().getRealPath("/") + dirName;

            System.out.println("실제경로:" + contextRootPath);

            //1. 메모리나 파일로 업로드 파일 보관하는 FileItem의 Factory 설정

            DiskFileItemFactory diskFactory = new DiskFileItemFactory(); // 디스크 파일 아이템 공장

            diskFactory.setSizeThreshold(SIZETHRESHOLD); // 업로드시 사용할 임시 메모리

            // diskFactory.setSizeThreshold(4096); // 업로드시 사용할 임시 메모리

            diskFactory.setRepository(new File(contextRootPath + UPLOAD_TMP_FOLDER)); // 임시저장폴더

            // diskFactory.setRepository(new File(contextRootPath + UPLOAD_TMP_FOLDER)); // 임시저장폴더

            //2. 업로드 요청을 처리하는 ServletFileUpload생성

            ServletFileUpload upload = new ServletFileUpload(diskFactory);

            // upload.setSizeMax(3 * 1024 * 1024); //3MB : 전체 최대 업로드 파일 크기

            upload.setSizeMax(MAX_UPLOAD_SIZE); // 전체 최대 업로드 파일 크기

            //3. 업로드 요청파싱해서 FileItem 목록구함​​

            List<FileItem> items = upload.parseRequest(req); 

            Iterator<FileItem> iter = items.iterator(); //반복자(Iterator)로 받기​            

            while(iter.hasNext()) { //반목문으로 처리​    

                FileItem item = (FileItem) iter.next(); //아이템 얻기

                 //4. FileItem이 폼 입력 항목인지 여부에 따라 알맞은 처리

                if(item.isFormField()){ 

                //파일이 아닌경우

                    processFormField(out, item);

                } else {

                //파일인 경우

                System.out.println("오류:" + item.getName());

                // 버그 개선 item 이름값 비어있을 때

                if ( item.getName() != "") {

                processUploadFile(out, item, contextRootPath);

                }

                System.out.println("오류2:");

                }

            }

        } catch(Exception e) {

            out.println("<PRE>");

            e.printStackTrace(out);

            out.println("</PRE>");

        }

out.println( "usrID(Map): " + reqMap.get("usrID") );

out.println( "usrPasswd(Map):" + reqMap.get("usrPasswd") );

        out.println("</BODY></HTML>");

// req.setAttribute("usrID", reqMap.get("usrID"));

// req.setAttribute("login", 1);//Object Type으로 넘어감

        req.setAttribute("reqMap", reqMap);

        req.setAttribute("fileMap", fileMap);

        // 방법3

        for( Integer key : fileMap.keySet() ){

            Map<String, Object> fileMapNode = fileMap.get(key);

            System.out.println( String.format("키 : %s, 값: %s", key, fileMapNode.get("fileName") ));

        }

// System.out.println("오류3:" + reqMap.get("usrID"));

}

//업로드한 정보가 파일인경우 처리

private static void processUploadFile(PrintWriter out, FileItem item, String contextRootPath)

throws Exception {

Map<String, Object> fileNode = new HashMap<String, Object>();

String dirName = UPLOAD_FOLDER ; 

String name = item.getFieldName(); // 파일의 필드 이름 얻기

String fileName = item.getName(); // 파일명 얻기

// 임시 - 실제 원본 이름 추출

File originalFile = new File(fileName);

String originalFileName = originalFile.getName();

System.out.println("임시:" + originalFileName );

String contentType = item.getContentType(); // 컨텐츠 타입 얻기

long fileSize = item.getSize(); // 파일의 크기 얻기

// 업로드 파일명을 현재시간으로 변경후 저장

String fileExt = fileName.substring(fileName.lastIndexOf("."));

String uploadedFileName = System.currentTimeMillis() + ""; 

System.out.println(fileExt);

System.out.println(uploadedFileName);

// 저장할 절대 경로로 파일 객체 생성

String realUploadFile = File.separator + dirName + File.separator + uploadedFileName;

System.out.println("실제 저장직전폴더:" + contextRootPath + realUploadFile);

File uploadedFile = new File(contextRootPath + realUploadFile);

item.write(uploadedFile); //파일 저장

//========== 뷰단에 출력 =========//

out.println("<P>");

out.println("파라미터 이름:" + name + "<BR>");

out.println("파일 이름:" + fileName + "<BR>");

out.println("콘텐츠 타입:" + contentType + "<BR>");

out.println("파일 사이즈:" + fileSize + "<BR>");

//확장자가 이미지인겨우 이미지 출력

if(".jpg.jpeg.bmp.png.gif".contains(fileExt.toLowerCase())) {

out.println("<IMG SRC='upload/" 

+ uploadedFileName 

+ "' width='300'><BR>");

}

out.println("</P>");

out.println("<HR>");

out.println("실제저장경로 : "+uploadedFile.getPath()+"<BR>");

out.println("<HR>");

// 파일 정보

fileNode.put("name", name);

fileNode.put("fileName", originalFileName);

fileNode.put("contentType", contentType);

fileNode.put("fileSize", fileSize);

fileNode.put("fileExt", fileExt);

fileNode.put("uploadedFileName", uploadedFileName);

fileNode.put("realName", uploadedFile.getName());

fileNode.put("realPath", uploadedFile.getPath());

fileMap.put(num, fileNode);

num++;

}

private static void processFormField(PrintWriter out, FileItem item) 

throws Exception{

String name = item.getFieldName(); //필드명 얻기

Object value = item.getString("UTF-8"); //UTF-8형식으로 필드에 대한 값읽기

// out.println(name + ":" + value + "<BR>"); //출력

reqMap.put(name, value);

}

/*

* 다운로드(Download)

* 

*/

public static void fileDownload(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_FOLDER + File.separator + UPLOAD_FOLDER ;

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

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

String filename = "1601561525229" ;

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

System.out.println("실제 파일명:" + filename);

// 실제 내보낼 파일명

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();

}

}

/*

*  파일 삭제, 폴더 삭제

*/

public static void removeFile(HttpServletRequest req, HttpServletResponse res) 

throws ServletException, IOException{

// 파일 업로드된 경로

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

String savePath = root + UPLOAD_FOLDER + File.separator + UPLOAD_FOLDER ;

String filename = "1601561525229" ;

        File file = new File(savePath, filename);

        // 파일이 존재할 떄

        if (file.exists()) {

        file.delete();

        }

        removeDirectory(req, res, savePath);

}

/*

* 

*/

private static boolean removeDirectory(HttpServletRequest req, HttpServletResponse res,

String path) throws ServletException, IOException {

boolean result = false;

File usrDir = new File(path);

if(!usrDir.exists()) {                  // 경로 존재 여부

            result = false;     

        }

else {

        File[] lowFiles = usrDir.listFiles();      // 경로 내의 파일 리스트

        // 폴더 삭제

        if ( usrDir.isDirectory() 

        && lowFiles.length == 0 ) {

        System.out.println("폴더 삭제처리 완료");

        usrDir.delete();

        return true;

    }else{

    result = false;

    }

        }

        return result;

}

}

* XML로 설정한 MyBatis에서는 가능한가요?

= 불가능하다. (미지원)

- IDE: Spring-Tool-Suite 4-4.7.2 Releases (2020-06 최신)

- DB: Oracle Databases 19g (2020-09 최신)

- Maven 3.6.3/1.16.0.20200610-1735 (2020-09 최신)

- JAR: javax.servlet-api.4.0.1.jar (2020-09 최신)

         ojdbc8-19.7.0.0.jar (2020-09 최신)

         MyBatis 3.5.5 (2020-09 최신)

         HikariCP 3.4.2 (2020-09 최신)

* 정말로 Spring-Framework는 사용하지 않았나요?

= 그렇다.

관련 글)

1. [JSP] 19. MyBatis-3.5.5 와 Maven / Servlet 연동하기 (Oracle 19g) - Java 방식, https://yyman.tistory.com/1434, 2020-10-01

-> 이 글의 소스 코드 이어서 계속(해당 게시글에서는 변형됨.)

* 이전의 프로젝트와 차이점: exmple 탈자를 정정하였음. (example로)

- 패키지명을 잘 조정해서 사용하면 크게 무리없이 동작할 것이다.

<?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.exmplebatis</groupId>

  <artifactId>web</artifactId>

  <version>0.0.1-SNAPSHOT</version>

  <packaging>war</packaging>

  <name>web 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>

    <!-- 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>

<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->

<dependency>

    <groupId>org.mybatis</groupId>

    <artifactId>mybatis</artifactId>

    <version>3.5.5</version>

</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>web</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>

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

dataSourceClassName=oracle.jdbc.driver.OracleDriver

dataSource.user=UserName

dataSource.password=Password

cachePrepStmts=true

prepStmtCacheSize=250

prepStmtCacheSqlLimit=2048

package com.example.web.db;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.util.Properties;

import javax.sql.DataSource;

import org.apache.ibatis.mapping.Environment;

import org.apache.ibatis.session.Configuration;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.apache.ibatis.transaction.TransactionFactory;

import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;

import com.example.web.mapper.CompUsersMapper;

import com.zaxxer.hikari.HikariConfig;

import com.zaxxer.hikari.HikariDataSource;

import oracle.jdbc.pool.OracleDataSource;

public class SqlMapSessionFactory {

private static SqlMapSessionFactory factory = new SqlMapSessionFactory();

public static SqlMapSessionFactory getInstance() {

return factory;

}

public static SqlSessionFactory ssf;

    private static String CLASSNAME;

    private static String JDBC_URL;

    private static String USERNAME;

    private static String PASSWORD;

    private static String CACHE_PREP_STMTS;

    private HikariDataSource ds;

    private HikariConfig config;

private SqlMapSessionFactory() {

/* HikariCP 로드 */

    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 );

    System.out.println("성공:" + ds);

    } catch (IOException e) {

    System.out.println("오류:" + e.getMessage());

        e.printStackTrace();

    }

}

// iBatis(MyBatis 반환)

public SqlSessionFactory getSqlSessionFactory() {

DataSource hDs = ds;

    // DataSource dataSource = getOracleDataSource();

System.out.println(hDs);

    TransactionFactory transactionFactory = new JdbcTransactionFactory();

    Environment environment = new Environment("development", transactionFactory, hDs);

    Configuration configuration = new Configuration(environment);

    configuration.addMapper(CompUsersMapper.class); // Mapper 클래스

//    System.out.println("성공2");

        return new SqlSessionFactoryBuilder().build(configuration);

    }

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

}

}

package com.example.web.controller;

import java.io.IOException;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import com.example.web.db.SqlMapSessionFactory;

import com.example.web.mapper.CompUsersMapper;

import com.example.web.model.CompUsers;

public class FrontController extends HttpServlet {

private static final long serialVersionUID = 1L;

    SqlSessionFactory factory = null;

    SqlMapSessionFactory sqlMapFactory = null;

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

*/

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

doAction(request, response);

}

/**

* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)

*/

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

doAction(request, response);

}

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

sqlMapFactory = SqlMapSessionFactory.getInstance();

try {

factory = sqlMapFactory.getSqlSessionFactory();

} catch (Exception e) {

e.printStackTrace();

}

try (SqlSession session = factory.openSession()) {

  CompUsersMapper mapper = session.getMapper(CompUsersMapper.class);

  CompUsers user = mapper.findByUsername("user");

  System.out.println("계정명:" + user.getUsername());

}

}

}

package com.example.web.model;

public class CompUsers {

private String username;

private String password;

private int enabled;

public String getUsername() {

return username;

}

public void setUsername(String username) {

this.username = username;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

public int getEnabled() {

return enabled;

}

public void setEnabled(int enabled) {

this.enabled = enabled;

}

}

CREATE TABLE comp_users (

username VARCHAR(50) NOT NULL,

password VARCHAR(300) NOT NULL,

enabled INT NOT NULL,

PRIMARY KEY (username)

);

-- 계정

INSERT INTO comp_users (username, password, enabled) VALUES ('user', '$2a$10$x04djNV2e9rpcPPRyXoLk.rMm6iZe2/vYdzpqHQcLeNSYdt7kc30O', 1);

INSERT INTO comp_users (username, password, enabled) VALUES ('admin', '$2a$10$QUddY3O/6ZgkYCR6MFlv9.nqA501Fm0cc/ZxQHX5pwb1o0CYCTiIS', 1);

package com.example.web.mapper;

import org.apache.ibatis.annotations.Mapper;

import org.apache.ibatis.annotations.Select;

import com.example.web.model.CompUsers;

@Mapper

public interface CompUsersMapper {

  @Select("SELECT * FROM comp_users WHERE username = #{username}")

  public CompUsers findByUsername(String username);

}

1. [JSP] 8. 영속프레임워크 MyBatis를 활용한 CRUD 구현 - JSP와 Oracle (XML 방식), 2020-9-19
https://yyman.tistory.com/1390 

-> XML 방식으로 구현됨.

- IDE: Spring-Tool-Suite 4-4.7.2 Releases (2020-06 최신)

- DB: Oracle Databases 19g (2020-09 최신)

- Maven 3.6.3/1.16.0.20200610-1735 (2020-09 최신)

- JAR: javax.servlet-api.4.0.1.jar (2020-09 최신)

         ojdbc8-19.7.0.0.jar (2020-09 최신)

         MyBatis 3.5.5 (2020-09 최신)

CREATE TABLE comp_users (

username VARCHAR(50) NOT NULL,

password VARCHAR(300) NOT NULL,

enabled INT NOT NULL,

PRIMARY KEY (username)

);

-- 계정

INSERT INTO comp_users (username, password, enabled) VALUES ('user', '$2a$10$x04djNV2e9rpcPPRyXoLk.rMm6iZe2/vYdzpqHQcLeNSYdt7kc30O', 1);

INSERT INTO comp_users (username, password, enabled) VALUES ('admin', '$2a$10$QUddY3O/6ZgkYCR6MFlv9.nqA501Fm0cc/ZxQHX5pwb1o0CYCTiIS', 1);

<?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.examplebatis</groupId>

  <artifactId>web</artifactId>

  <version>0.0.1-SNAPSHOT</version>

  <packaging>war</packaging>

  <name>web 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>

    <!-- 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>

<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->

<dependency>

    <groupId>org.mybatis</groupId>

    <artifactId>mybatis</artifactId>

    <version>3.5.5</version>

</dependency>

<dependency>

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

<artifactId>ojdbc8</artifactId>

<version>19.7.0.0</version>

</dependency>

  </dependencies>

  <build>

    <finalName>web</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>

package com.exmple.web.db;

import java.io.IOException;

import java.io.InputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.ibatis.io.Resources;

import org.apache.ibatis.mapping.Environment;

import org.apache.ibatis.session.Configuration;

import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import org.apache.ibatis.transaction.TransactionFactory;

import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;

import com.exmple.web.mapper.CompUsersMapper;

import oracle.jdbc.pool.OracleDataSource;

public class SqlMapSessionFactory {

private static SqlMapSessionFactory factory = new SqlMapSessionFactory();

private SqlMapSessionFactory() {}

private final static String driverName = "oracle.jdbc.driver.OracleDriver";

private final static String dbUrl = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";

private final static String userName = "사용자계정명";

private final static String userPassword = "비밀번호";

public static SqlMapSessionFactory getInstance() {

return factory;

}

public static SqlSessionFactory ssf;

    static{

    DataSource dataSource = getOracleDataSource();

    TransactionFactory transactionFactory = new JdbcTransactionFactory();

    Environment environment = new Environment("development", transactionFactory, dataSource);

    Configuration configuration = new Configuration(environment);

    configuration.addMapper(CompUsersMapper.class); // Mapper 클래스

        ssf = new SqlSessionFactoryBuilder().build(configuration);

    }

// iBatis(MyBatis 반환)

public static SqlSessionFactory getSqlSessionFactory(){

        return ssf;

    }

/*

*     public static DataSource getMySQLDataSource() {

        Properties props = new Properties();

        FileInputStream fis = null;

        MysqlDataSource mysqlDS = null;

        try {

            fis = new FileInputStream("db.properties");

            props.load(fis);

            mysqlDS = new MysqlDataSource();

            mysqlDS.setURL(props.getProperty("MYSQL_DB_URL"));

            mysqlDS.setUser(props.getProperty("MYSQL_DB_USERNAME"));

            mysqlDS.setPassword(props.getProperty("MYSQL_DB_PASSWORD"));

        } catch (IOException e) {

            e.printStackTrace();

        }

        return mysqlDS;

    }

    */

/*

* Description: 순정 오라클 데이터소스

*/

    private static DataSource getOracleDataSource(){

    OracleDataSource oracleDS = null;

    try {

            oracleDS = new OracleDataSource();

            oracleDS.setURL(dbUrl);

            oracleDS.setUser(userName);

            oracleDS.setPassword(userPassword);

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return oracleDS;

    }

public Connection connect() {

Connection conn = null;

try {

Class.forName(driverName);

conn = DriverManager.getConnection(dbUrl, userName, userPassword);

}

catch(Exception ex) {

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

}

return conn;

}

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

}

}

비고: MySQL의 지원에 대해서 코드를 적어놓았다.

<!DOCTYPE web-app PUBLIC

 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"

 "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>

  <display-name>Archetype Created Web Application</display-name>

  <servlet>

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

  <display-name>FrontController</display-name>

  <description></description>

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

  </servlet>

  <servlet-mapping>

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

  <url-pattern>/sample</url-pattern>

  </servlet-mapping>

</web-app>

package com.exmple.web.model;

public class CompUsers {

private String username;

private String password;

private int enabled;

public String getUsername() {

return username;

}

public void setUsername(String username) {

this.username = username;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

public int getEnabled() {

return enabled;

}

public void setEnabled(int enabled) {

this.enabled = enabled;

}

}

package com.exmple.web.mapper;

import org.apache.ibatis.annotations.Mapper;

import org.apache.ibatis.annotations.Select;

import com.exmple.web.model.CompUsers;

@Mapper

public interface CompUsersMapper {

  @Select("SELECT * FROM comp_users WHERE username = #{username}")

  public CompUsers findByUsername(String username);

}

대응하는 방식에는 XML Mapper를 적용하는 방법도 있다.
(참고로 자바방식으로 구현했다면, XML Mapper는 사용할 수 없다.)

(DB를 설계하면서 View도 만들 수도 있고 각종 조합을 할 수도 있는데, 엔터티를 잘 파악해서 구현하면 될 것 같다.)

[Spring Boot 버전의 MyBatis 핵심 사용법]

@Mapper

public interface StudentMyBatisRepository {

@Select("select * from student")

public List<Student> findAll();

@Select("SELECT * FROM student WHERE id = #{id}")

public Student findById(long id);

@Delete("DELETE FROM student WHERE id = #{id}")

public int deleteById(long id);

@Insert("INSERT INTO student(id, name, passport) VALUES (#{id}, #{name}, #{passport})")

public int insert(Student student);

@Update("Update student set name=#{name}, passport=#{passport} where id=#{id}")

public int update(Student student);

}

package com.exmple.web.controller;

import java.io.IOException;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.ibatis.session.SqlSession;

import org.apache.ibatis.session.SqlSessionFactory;

import com.exmple.web.db.SqlMapSessionFactory;

import com.exmple.web.mapper.CompUsersMapper;

import com.exmple.web.model.CompUsers;

public class FrontController extends HttpServlet {

private static final long serialVersionUID = 1L;

    SqlSessionFactory factory = SqlMapSessionFactory.getSqlSessionFactory();

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

*/

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

doAction(request, response);

}

/**

* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)

*/

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

doAction(request, response);

}

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

try (SqlSession session = factory.openSession()) {

  CompUsersMapper mapper = session.getMapper(CompUsersMapper.class);

  CompUsers user = mapper.findByUsername("user");

  System.out.println("계정명:" + user.getUsername());

}

}

}

1. [JSP] 20. MyBatis-3.5.5, HikariCP 3.4.2 연동 - Maven(Servlet) Spring 제거버전 (Oracle 19g) - Java 방식, 2020-10-01

https://yyman.tistory.com/1435

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

https://yyman.tistory.com/1428

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

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

파일명: Paging.java

package com.smile.web.logic;

public class Paging {

    private long pageSize; // 게시 글 수

    private long firstPageNo; // 첫 번째 페이지 번호

    private long prevPageNo; // 이전 페이지 번호

    private long startPageNo; // 시작 페이지 (페이징 네비 기준)

    private long pageNo; // 페이지 번호

    private long endPageNo; // 끝 페이지 (페이징 네비 기준)

    private long nextPageNo; // 다음 페이지 번호

    private long finalPageNo; // 마지막 페이지 번호

    private long totalCount; // 게시 글 전체 수

    private long dbStartNum; // db 조회 (시작번호)

    private long dbEndNum; // db 조회 (종료번호)

    /**

     * @return the pageSize

     */

    public long getPageSize() {

        return pageSize;

    }

    /**

     * @param pageSize the pageSize to set

     */

    public void setPageSize(long pageSize) {

        this.pageSize = pageSize;

    }

    /**

     * @return the firstPageNo

     */

    public long getFirstPageNo() {

        return firstPageNo;

    }

    /**

     * @param firstPageNo the firstPageNo to set

     */

    public void setFirstPageNo(long firstPageNo) {

        this.firstPageNo = firstPageNo;

    }

    /**

     * @return the prevPageNo

     */

    public long getPrevPageNo() {

        return prevPageNo;

    }

    /**

     * @param prevPageNo the prevPageNo to set

     */

    public void setPrevPageNo(long prevPageNo) {

        this.prevPageNo = prevPageNo;

    }

    /**

     * @return the startPageNo

     */

    public long getStartPageNo() {

        return startPageNo;

    }

    /**

     * @param startPageNo the startPageNo to set

     */

    public void setStartPageNo(long startPageNo) {

        this.startPageNo = startPageNo;

    }

    /**

     * @return the pageNo

     */

    public long getPageNo() {

        return pageNo;

    }

    /**

     * @param pageNo the pageNo to set

     */

    public void setPageNo(long pageNo) {

        this.pageNo = pageNo;

    }

    /**

     * @return the endPageNo

     */

    public long getEndPageNo() {

        return endPageNo;

    }

    /**

     * @param endPageNo the endPageNo to set

     */

    public void setEndPageNo(long endPageNo) {

        this.endPageNo = endPageNo;

    }

    /**

     * @return the nextPageNo

     */

    public long getNextPageNo() {

        return nextPageNo;

    }

    /**

     * @param nextPageNo the nextPageNo to set

     */

    public void setNextPageNo(long nextPageNo) {

        this.nextPageNo = nextPageNo;

    }

    /**

     * @return the finalPageNo

     */

    public long getFinalPageNo() {

        return finalPageNo;

    }

    /**

     * @param finalPageNo the finalPageNo to set

     */

    public void setFinalPageNo(long finalPageNo) {

        this.finalPageNo = finalPageNo;

    }

    /**

     * @return the totalCount

     */

    public long getTotalCount() {

        return totalCount;

    }

    /**

     * @param totalCount the totalCount to set

     */

    public void setTotalCount(long totalCount) {

        this.totalCount = totalCount;

        this.makePaging();

        this.makeDbPaging();

    }

    /**

     * 페이징 생성 (setTotalCount에서 호출함)

     */

    private void makePaging() {

        if (this.totalCount == 0) 

        return; 

        // 게시 글 전체 수가 없는 경우

        if (this.pageNo == 0) 

        this.setPageNo(1); // 기본 값 설정

        if (this.pageSize == 0) 

        this.setPageSize(10); // 기본 값 설정

        long finalPage = (totalCount + (pageSize - 1)) / pageSize; // 마지막 페이지

        if (this.pageNo > finalPage) 

        this.setPageNo(finalPage); // 기본 값 설정

        if (this.pageNo < 0 || this.pageNo > finalPage) 

        this.pageNo = 1; // 현재 페이지 유효성 체크

        boolean isNowFirst = pageNo == 1 ? true : false; // 시작 페이지 (전체)

        boolean isNowFinal = pageNo == finalPage ? true : false; // 마지막 페이지 (전체)

        long startPage = ((pageNo - 1) / 10) * 10 + 1; // 시작 페이지 (페이징 네비 기준)

        long endPage = startPage + 10 - 1; // 끝 페이지 (페이징 네비 기준)

        if (endPage > finalPage) { // [마지막 페이지 (페이징 네비 기준) > 마지막 페이지] 보다 큰 경우

            endPage = finalPage;

        }

        this.setFirstPageNo(1); // 첫 번째 페이지 번호

        if (isNowFirst) {

            this.setPrevPageNo(1);    // 이전 페이지 번호

        } else {

            this.setPrevPageNo(((pageNo - 1) < 1 ? 1 : (pageNo - 1))); // 이전 페이지 번호

        }

        this.setStartPageNo(startPage); // 시작 페이지 (페이징 네비 기준)

        this.setEndPageNo(endPage); // 끝 페이지 (페이징 네비 기준)

        if (isNowFinal) {

            this.setNextPageNo(finalPage); // 다음 페이지 번호

        } else {

            this.setNextPageNo(((pageNo + 1) > finalPage ? finalPage : (pageNo + 1))); // 다음 페이지 번호

        }

        this.setFinalPageNo(finalPage); // 마지막 페이지 번호

    }

    private void makeDbPaging() {

    long current = this.getPageNo();

    long weight = this.getPageSize();

    long endNum = 0;

    this.setDbEndNum( current * weight );

    endNum = this.getDbEndNum();

    this.setDbStartNum( (endNum - weight) + 1 );

    }

    /**

     * @return the dbStartNum

     */

public long getDbStartNum() {

return dbStartNum;

}

    /**

     * @param dbStartNum the dbStartNum to set

     */

public void setDbStartNum(long dbStartNum) {

this.dbStartNum = dbStartNum;

}

    /**

     * @return dbEndNum

     */

public long getDbEndNum() {

return dbEndNum;

}

    /**

     * @param dbEndNum the dbEndNum to set

     */

public void setDbEndNum(long dbEndNum) {

this.dbEndNum = dbEndNum;

}

}

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

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

파일명: Board.java

package com.smile.web.model;

import java.sql.Timestamp;

public class Board {

private long id;

private String name;

private String subject;

private String memo;

private long count;

private Timestamp regidate;

public long getId() {

return id;

}

public void setId(long id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getSubject() {

return subject;

}

public void setSubject(String subject) {

this.subject = subject;

}

public String getMemo() {

return memo;

}

public void setMemo(String memo) {

this.memo = memo;

}

public long getCount() {

return count;

}

public void setCount(long count) {

this.count = count;

}

public Timestamp getRegidate() {

return regidate;

}

public void setRegidate(Timestamp regidate) {

this.regidate = regidate;

}

}

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

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

파일명: BoardService.java

package com.smile.web.service;

import java.util.List;

import com.smile.web.model.Board;

public class BoardService {

private static BoardService service = null;

private static BoardServiceImpl dao = null;

private BoardService() {}

public static BoardService getInstance() {

        if(service == null){

        service = new BoardService();

    dao = BoardServiceImpl.getInstatnce();

        }

        return service;

}

public List<Board> getBoardList(long startNum, long endNum){

return dao.getBoardList(startNum, endNum);

}

public long getTotalCount() {

return dao.getTotalCount();

}

public List<Board> getBoardKeywordList(String keyword, long startNum, long endNum){

return dao.getBoardKeywordList(keyword, startNum, endNum);

}

public long getTotalKeywordCount(String keyword) {

return dao.getTotalKeywordCount(keyword);

}

}

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

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

파일명: BoardServiceImpl.java

package com.smile.web.service;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.smile.web.db.DBFactory;

import com.smile.web.model.Board;

public class BoardServiceImpl {

private static BoardServiceImpl boardDAO;

private static DBFactory dbPool;

private BoardServiceImpl() {}

public static BoardServiceImpl getInstatnce() {

if ( boardDAO == null ) {

boardDAO = new BoardServiceImpl();

dbPool = new DBFactory();

}

return boardDAO;

}

public List<Board> getBoardList(long startNum, long endNum){

Connection conn = null;

    PreparedStatement pstmt = null;

    ResultSet rs = null;

    Board node = null;

    List<Board> boardList = new ArrayList<Board>();

    String sql = "select * from (select /*+INDEX_DESC(tbl_board pk_board) */ rownum rn, A.*" + 

    " from board A order by id desc) " + 

    "where rn >= ? and rn <= ?";

    //System.out.println(sql);

    //System.out.println(startNum + "/" + endNum);

    try {

    conn = dbPool.getConnection();

    pstmt = conn.prepareStatement(sql);

    pstmt.setLong(1, startNum);

    pstmt.setLong(2, endNum);

    rs = pstmt.executeQuery();

    while ( rs.next() ) {

    // 데이터가 존재할 때, 노드 생성

    node = new Board();

    node.setId(rs.getLong(2));

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

    node.setMemo(rs.getNString(4));

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

    boardList.add(node);

    System.out.println("rs:" + rs.getLong(1));

    }

    }catch(Exception ex) {

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

    }

    finally {

    dbPool.close(conn, pstmt, rs);

    }

return boardList;

}

public List<Board> getBoardKeywordList(String keyword, long startNum, long endNum){

Connection conn = null;

    PreparedStatement pstmt = null;

    ResultSet rs = null;

    Board node = null;

    String allKeyword = "%" + keyword + "%";

    List<Board> boardList = new ArrayList<Board>();

    String sql = "select * " + 

    "from (select /*+INDEX_DESC(tbl_board pk_board) */ rownum rn, A.* " + 

    "from board A where subject like ? order by id desc) " + 

    "where rn >= ? and rn <= ?";

    //System.out.println(sql);

    try {

    conn = dbPool.getConnection();

    pstmt = conn.prepareStatement(sql);

    pstmt.setNString(1, allKeyword);

    pstmt.setLong(2, startNum);

    pstmt.setLong(3, endNum);

    rs = pstmt.executeQuery();

    while ( rs.next() ) {

    // 데이터가 존재할 때, 노드 생성

    node = new Board();

    node.setId(rs.getLong(2));

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

    node.setMemo(rs.getNString(4));

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

    boardList.add(node);

    //System.out.println("rs:" + rs.getLong(1));

    }

    }catch(Exception ex) {

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

    }

    finally {

    dbPool.close(conn, pstmt, rs);

    }

return boardList;

}

public long getTotalCount() {

Connection conn = null;

    PreparedStatement pstmt = null;

    ResultSet rs = null;

    Board node = null;

    long cnt = 0;

    String sql = "select count(*) from board";

    System.out.println("sql:" + sql);

    try {

    conn = dbPool.getConnection();

    //System.out.println( conn.getSchema() );

    pstmt = conn.prepareStatement(sql);

    rs = pstmt.executeQuery();

    //System.out.println( rs.getFetchSize() );

    if ( rs.next() ) {

    cnt = rs.getLong(1);

        System.out.println("전체갯수:" + cnt + "/" + rs.getLong(1));

    }

    }catch(Exception ex) {

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

    }

    finally {

    dbPool.close(conn, pstmt, rs);

    }

return cnt;

}

public long getTotalKeywordCount(String keyword) {

Connection conn = null;

    PreparedStatement pstmt = null;

    ResultSet rs = null;

    Board node = null;

    String allKeyword = "%" + keyword + "%";

    long cnt = 0;

    String sql = "select count(*) from board where subject like ?";

    //System.out.println("sql:" + sql + "/키:" + keyword);

    try {

    conn = dbPool.getConnection();

    /// System.out.println( conn.getSchema() );

    pstmt = conn.prepareStatement(sql);

    pstmt.setNString(1, allKeyword);

    rs = pstmt.executeQuery();

    /// System.out.println( rs.getFetchSize() );

    if ( rs.next() ) {

    cnt = rs.getLong(1);

        System.out.println("특정 갯수:" + cnt + "/" + rs.getLong(1));

    }

    }catch(Exception ex) {

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

    }

    finally {

    dbPool.close(conn, pstmt, rs);

    }

return cnt;

}

}

[개선 후 SQL 파일 내용]

-- 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

페이징 로직 설계가 되었다면, 다음 계획해야 할 작업이 실질적으로는 이 작업이다.

의외로 작업을 하다보면, 골치가 아픈 문제들이 많이 생긴다.

[Oracle Databases 코너에 쿼리 관련 작업에 대해서 추가로 보충하여 소개하도록 하겠다.]

- 단일 테이블 게시물 쿼리 방법, 뷰 테이블 쿼리 방법, 테이블 조인(2개 이상) 사용 방법

-> 태스트 결과로는 "Java 페이징 로직"은 크게 문제가 없다. 
    다만, 나머지 부분은 쿼리라고 본다.

생성할 폴더: 

/src/main/webapp/board

/src/main/webapp/board/css

생성할 파일

/src/main/web/app/board/css/style.css

@charset "utf-8";

/* 1. 제목 */

h3{

font-size:20px;

font-family:'Nanum Gothic';

text-align:center;

}

/* 2. 게시판 */

/* 게시판 목록 출력 */

.board_list{

border-top:1px solid #e2e2e2;

border-bottom:1px solid #e2e2e2;

font-size:12px;

font-family:'Nanum Gothic';

width:900px;

margin:auto;

text-align:center;

}

/* 제목 */

.board_list th{

border-right:1px solid #666;

border-bottom:1px solid #666;

font-size:15px;

font-family:'Nanum Gothic';

height:20px;

text-align:center;

background-color:#eeeeee;

}

/* 내용 */

.board_list td{

border-right:1px solid #e2e2e2;

font-size:15px;

font-family:'Nanum Gothic';

height:20px;

text-align:center;

}

/* 3. 페이징 네이션 */

.paginate{

font-size:15px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

}

.paginate .first{

font-size:15px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

margin-right:10px;

}

.paginate .prev{

font-size:15px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

margin-right:10px;

}

.paginate .next{

font-size:15px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

margin-right:10px;

}

.paginate .last{

font-size:15px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

margin-right:10px;

}

.paginate .choice{

font-size:20px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

margin-right:10px;

}

/* 4. 링크 */

a{

text-decoration:none;

color:#666;

}

/* 5. 검색 영역 */

.searchArea{

font-size:15px;

font-family:'Nanum Gothic';

font-color:#666;

text-align: center;

}

생성할 폴더: 

/src/main/webapp/WEB-INF/views/

/src/main/webapp/WEB-INF/views/board

생성할 파일

/src/main/webapp/WEB-INF/views/board/list.jsp

/src/main/webapp/WEB-INF/views/board/search.jsp

/src/main/webapp/WEB-INF/views/board/paging.jsp

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

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

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

<%@ page import = "com.smile.web.model.*" %>

<%@ page session="false" %>

<!DOCTYPE html>

<html>

<head>

<meta charset="UTF-8">

<title>게시물 목록</title>

<style>

@import url('https://fonts.googleapis.com/css?family=Nanum+Gothic:400,700,800');

</style>

<link href="css/style.css" rel="stylesheet" type="text/css" />

</head>

<body>

<%

List<Board> boardList = (List<Board>)request.getAttribute("list");

%>

<h3>게시물 목록</h3>

<!-- 목록 출력 -->

<table class="board_list">

<tr>

<th style="width:15%;">

번호(Num)

</th>

<th>

제목(Subject)

</th>

<th style="width:13%;">

글쓴이(Author)

</th>

<th style="width:13%; border-right:none;">

조회수(Count)

</th>

</tr>

<%

for(Board board:boardList){

%>

<tr>

<td style="width:15%;">

<%= board.getId() %>

</td>

<td>

<%= board.getSubject() %>

</td>

<td style="width:13%;">

<%= board.getName() %>

</td>

<td style="width:13%; border-right:none;">

<%= board.getCount() %>

</td>

</tr>

<%

}

%>

</table>

<!-- 페이징 -->

<jsp:include page="/WEB-INF/views/board/paging.jsp">

<jsp:param name="customURL" value="${pagingUrl}" />

    <jsp:param name="firstPageNo" value="${paging.firstPageNo}" />

    <jsp:param name="prevPageNo" value="${paging.prevPageNo}" />

    <jsp:param name="startPageNo" value="${paging.startPageNo}" />

    <jsp:param name="pageNo" value="${paging.pageNo}" />

    <jsp:param name="endPageNo" value="${paging.endPageNo}" />

    <jsp:param name="nextPageNo" value="${paging.nextPageNo}" />

    <jsp:param name="finalPageNo" value="${paging.finalPageNo}" />

</jsp:include>

<!-- 검색 -->

<jsp:include page="/WEB-INF/views/board/search.jsp" />

</body>

</html>

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

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

<div class="paginate">

    <a href="${param.customURL}page=${param.firstPageNo}" class="first">처음 페이지</a>

    <a href="${param.customURL}page=${param.prevPageNo}" class="prev">이전 페이지</a>

    <span>

        <c:forEach var="i" begin="${param.startPageNo}" end="${param.endPageNo}" step="1">

            <c:choose>

                <c:when test="${i eq param.pageNo}"><a href="${param.customURL}page=${i}" class="choice">${i}</a></c:when>

                <c:otherwise><a href="${param.customURL}page=${i}">${i}</a></c:otherwise>

            </c:choose>

        </c:forEach>

    </span>

    <a href="${param.customURL}page=${param.nextPageNo}" class="next">다음 페이지</a>

    <a href="${param.customURL}page=${param.finalPageNo}" class="last">마지막 페이지</a>

</div>

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

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

<div class="searchArea">

<form id="searchForm" action="list.do" method='get'>

<select name="type">

<option value="T">제목</option>

<option value="C">내용</option>

<option value="W">작성자</option>

</select>

<input type="text" name="keyword">

<button class="">검색</button>

</form>

</div>

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;