도도의 초록누리

좋은 프로그램인가요?

= SQL 쿼리만 다루고자 하는 경우에는 좋을 수도 있다.

완벽하게 좋다고는 볼 수 없다. 제한된 사항, MS윈도에서만 동작하는 한계점 등은 어떻게 할 방법이 없기 때문이다.

고민해볼 만한 주제:

우분투, 리눅스, CentOS, LinuxMint 등 다 기종 운영체제에서는 가능한지?

[설치]

- Microsoft Windows 7, 10
- Oracle Instants Clients (필수)

- Golden

[기타 환경설정]

tnsnames.ora

-> Instant Client for Microsoft Windows (x64)

[참고사항] - Oracle XE 11g Edition을 설치하면 제공하는 경로
C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN

XE =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = Sample)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = XE)

    )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

ORACLR_CONNECTION_DATA = 

  (DESCRIPTION = 

    (ADDRESS_LIST = 

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) 

    ) 

    (CONNECT_DATA = 

      (SID = CLRExtProc) 

      (PRESENTATION = RO) 

    ) 

  ) 

select * from USER_OBJECTS;

[작성환경]

- MS 윈도우 10

- Oracle Databases 설치된 상태

[적용대상]

- Oracle Databases

예) C:\oraclexe\app\oracle\product\11.2.0\server\bin

오라클 설치한 경로를 찾아가보면 아래의 그림처럼 sqlplus의 위치를 찾을 수 있다.

[추가 방법]

-> 1. (컴퓨터 -> 속성 -> 환경변수 -> 시스템 변수 -> 새로만들기 

      "ORACLE_HOME" -> C:\oraclexe\app\oracle\product\11.2.0\server

   2. 시스템 변수 (Path 편집) -> %ORACLE_HOME%\bin

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;



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

1. [Spring-Framework] 36. Spring-JDBCTemplate - 트랜젝션 (어노테이션, Java 설정), 2020-10-10

- https://yyman.tistory.com/1461

IDE: Eclipse 2020-06

- Spring Framework 4.2.4 Releases

- Spring-JDBC

- Spring-TX

- Spring-Core

- AspeetJ Weaver

- AspectJ

DB: Oracle Databases 11g (Express Edition)

((중략) - 추가된 사항)
    
  <!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
  <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>${org.springframework-version}</version>
  </dependency>
  
    
  <!-- AspectJ -->
  <dependency>
   <groupId>org.aspectj</groupId>
   <artifactId>aspectjrt</artifactId>
   <version>${org.aspectj-version}</version>
  </dependency> 
  

  <!-- AspectJWeaver 추가 -->
  <dependency>
   <groupId>org.aspectj</groupId>
   <artifactId>aspectjweaver</artifactId>
   <version>${org.aspectj-version}</version>
  </dependency>
  

package com.website.example.aop;

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.stereotype.Service;

//관점, 서비스
@Aspect
@Service
public class LogAdvisor {

   // 2단계 - 전 단계 시야)
 
  @Before("execution(* com.website.example.aop.service.AccountServiceAOP.*(..))")
  // @Before("execution(public void sum())")
  // 반환값 없어도 무방
  
   public void logBefore() {
           System.out.println("전 단계");

   }

 
}

package com.website.example.aop.service;

import java.sql.SQLException;

import com.website.example.vo.AccountVO;

public interface AccountServiceAOP {

 void accountCreate(AccountVO vo) throws SQLException;
 void accountTransfer(String sender, String receiver, int money) throws SQLException;
 
}

package com.website.example.aop.service;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionSynchronizationManager;

import com.website.example.dao.AccountDAO;
import com.website.example.dao.AccountDAOImpl;
import com.website.example.vo.AccountVO;

@Repository
@Transactional
public class AccountServiceImplAOP implements AccountServiceAOP{

 private AccountDAO accountDAO;
 private DataSource ds = null;
 
 public AccountServiceImplAOP(DataSource ds) {
  this.accountDAO = new AccountDAOImpl(ds);
  this.ds = ds;
 }

 @Override
 @Transactional(propagation=Propagation.NEVER)
 public void accountCreate(AccountVO vo) throws SQLException {
  accountDAO.createAccount(vo); 
  System.out.println("create CurrentTransactionName: " + TransactionSynchronizationManager.getCurrentTransactionName());
 }
 
 @Override
 @Transactional
 public void accountTransfer(String sender, String receiver, int money) throws SQLException {
  
     int balance = accountDAO.getBalance(sender); // 보내는 사람 잔액 체크
     
        if(balance >= money){ // 보내는 돈이 잔액보다 많으면
     
         System.out.println("transfer CurrentTransactionName: " + TransactionSynchronizationManager.getCurrentTransactionName() );
         
   accountDAO.minus(sender, money);
   accountDAO.plus(receiver, money);
   
        } else{

         System.out.println("돈 없음");
         //throw new NoMoneyException();
        }
  
 }

}

package com.website.example.common;

import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.context.annotation.Import;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@Import({DBConfigAOP.class})
@ComponentScan(basePackages = {"com.website.example"})
@EnableAspectJAutoProxy
//@ComponentScan(basePackages = {"com.local.example.beans", "com.local.example.advisor"})
public class RootConfigAOP {

}

package com.website.example.common;

import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.website.example.dao.AccountDAO;
import com.website.example.dao.AccountDAOImpl;
import com.website.example.aop.service.AccountServiceAOP;
import com.website.example.aop.service.AccountServiceImplAOP;

@Configuration
@EnableTransactionManagement
public class DBConfigAOP {

    @Bean
    public PlatformTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }
   
    @Bean
    public DataSource dataSource() {
     
     DataSource dataSource = new MyDataSourceFactory().getOracleDataSource();
     
     /* Apache DBCP
     
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/testDb?useUnicode=true&characterEncoding=utf8");
        dataSource.setUsername("test");
        dataSource.setPassword("test123!@#");
       
        */
        return dataSource;
    }
   
    @Bean
    public AccountDAO accountDAOImpl() {
     
     AccountDAO dao = new AccountDAOImpl(dataSource());
     return dao;
    }
   
    @Bean
    public AccountServiceAOP accountServiceImplAOP() {
     
     AccountServiceAOP service = new AccountServiceImplAOP(dataSource());
     return service;
    }
   
}

package com.website.example.unit;

import java.sql.SQLException;

import java.sql.Timestamp;

import javax.sql.DataSource;

import org.junit.jupiter.api.Test;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;

import com.website.example.common.MyDataSourceFactory;
import com.website.example.common.RootConfigAOP;
import com.website.example.dao.AccountDAOImpl;
import com.website.example.aop.service.AccountServiceAOP;
import com.website.example.aop.service.AccountServiceImplAOP;
import com.website.example.vo.AccountVO;

class MainTestAOP {

 @Test
 void test() throws SQLException {
  
       @SuppressWarnings("resource")
        ApplicationContext applicationContext = new AnnotationConfigApplicationContext(RootConfigAOP.class);
  
        AccountServiceAOP service = (AccountServiceAOP) applicationContext.getBean("accountServiceImplAOP");
  
  AccountVO vo = new AccountVO();
  
  // 1. 계정 생성
  vo.setName("홍길동2");
  vo.setBalance(10000);
  vo.setRegidate(Timestamp.valueOf("2020-01-20 11:05:20"));
  service.accountCreate(vo);
  
  // 2. 계정 생성
  vo.setName("홍길자2");
  vo.setBalance(0);
  vo.setRegidate(Timestamp.valueOf("2020-01-20 23:05:20"));
  service.accountCreate(vo);
  
  // 3. 거래 처리
  service.accountTransfer("홍길동", "홍길자", 500);
  
  
 }

}

[PseudoCode]

public void insertMember(){

    (연결자)
    Connection conn = .....;
    
    try{
        conn.open(); // (연결)

        for ( 1 to 10000 ){

           String sql = "insert into ........";

           conn.executeQuery(sql);

        }

     }catch(Exception e){

     }finally{
           conn.close(); // (종료)
     }

}

[태스트 환경]

1. MS Windows 10

2. Oracle 11g (Express Edition, XE)

ora-12519 TNS:no appropriate handler found

1. 명령 프롬프트 실행하기

   cmd

2. 관리자 계정 접속 명령어

   sqlplus "/as sysdba"

3. 프로세스 조회하기
   SQL> SELECT * FROM v$resource_limit where resource_name='processes';

4. 프로세스 조정하기 크기
   SQL> ALTER SYSTEM SET PROCESSES=500 SCOPE=spfile; -- 200으로 늘려줌

5. 서버 재시작
   SQL> shutdown immediate; --셧다운
   SQL> startup; --재시작

sqlplus "/as sysdba"

SQL> SELECT * FROM v$resource_limit where resource_name='processes';

SQL> ALTER SYSTEM SET PROCESSES=500 SCOPE=spfile;

SQL> SELECT * FROM v$resource_limit where resource_name='processes';

SQL> commit;

SQL> SELECT * FROM v$resource_limit where resource_name='processes';

SQL> shutdown immediate;

SQL> startup;

SQL> SELECT * FROM v$resource_limit where resource_name='processes';

[작성 환경]

1. MS윈도우 10

2. 포멧 후 깨끗한 상태

경로: C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\
찾아야 하는 파일명: scott.sql

SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\scott.sql
SQL> alter user scott identified by tiger;

SQL> commit;

SQL> conn scott/tiger
SQL> show user;

SQL> select * from tab;

SQL> exit

c:\Users\{사용자계정명}>sqlplus
Enter user-name: system
Enter password: (비밀번호 입력하기)

SQL> alter user hr account unlock;

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;

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