[10] [제시어] JDBC 설정, DB 설정, Servlet

1. DBOpen,DBClose,Constant

 

utility 패키지에 가져오기

 

WEB-INF/lib 폴더에 ojdbc6.jar 파일가져오기

 

 

2. Java Test

 

>>>>> JDBC_Test.java

 

package utility;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

 

 

/**

 * JAVA Client Test

 */

public class JDBC_Test {

 

    public static void main(String[] args) {

 

        Connection con = DBOpen.getConnection();

        PreparedStatement pstmt = null;

        ResultSet rs = null;

        

        // 테이블의 갯수 리턴

        String sql="SELECT count(*) as cnt FROM tab";

        

        try{

            pstmt = con.prepareStatement(sql);

            

            rs = pstmt.executeQuery();

            if (rs.next() == true){

                System.out.println("현재 생성된 테이블 갯수: " + rs.getInt("cnt"));

            }

        }catch(Exception e){

            System.out.println(e);

        }finally{

            DBClose.close(con, pstmt, rs);

        }

    }

}

 

 

3. DB 설정, Servlet 

 

1) 테이블 구조

 

    CREATE TABLE suggest(

        suggestno NUMBER(7)    NOT NULL,

        sqlpart   VARCHAR(20)  NOT NULL,

        sqlquery  VARCHAR(100) NOT NULL, 

        etc       VARCHAR(100)     NULL,

        PRIMARY KEY(suggestno)

    );

 

 

    DROP SEQUENCE suggest_seq;

 

    CREATE SEQUENCE suggest_seq

    START WITH   10        --시작번호, 0~9는 테스트 일련 번호

    INCREMENT BY 1         --증가값

    MAXVALUE     9999999   --최대값

    CACHE        2         --시쿼스 변경시 자주 update되는 것을 방지하기위한 캐시값

    NOCYCLE;    

    

 

    

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, 'INSERT',

           'INSERT 테이블명 VALUES( ~ )', '');

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, 'INSERT',

           'INSERT 테이블명 ( ~ ) VALUES( ~ )', '');

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, 'SELECT',

           'SELECT * FROM ~', '');    

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, 'SELECT',

           'SELECT * FROM ~ WHERE ~', '');    

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, 'UPDATE',

           'UPDATE ~ SET ~', '');    

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, 'UPDATE',

           'UPDATE ~ SET ~ WHERE ~', '');    

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, 'DELETE',

           'DELETE FROM ~', ''); 

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, 'DELETE',

           'DELETE FROM ~ WHERE ~', ''); 

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '자바코드',

           '자바001', ''); 

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '자바코드',

           '자바002', '');                       

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '자바코드',

           '자바003', '');                       

 

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '모니터',

           'LCD 15inch', '');      

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '모니터',

           'LCD 16inch', '');      

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '모니터',

           'LCD 17inch', '');      

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '모니터',

           'LCD 18inch', '');      

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '모니터',

           'LCD 19inch', '');      

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '모니터',

           'LCD 20inch', '');      

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '모니터',

           'LCD 21inch', '');      

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '모니터',

           'LCD 22inch', '');      

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '모니터',

           'LCD 23inch', '');      

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '모니터',

           'LCD 24inch', '');      

    INSERT INTO suggest(suggestno, sqlpart, sqlquery, etc)

    VALUES(suggest_seq.nextval, '모니터',

           'LCD 25inch', '');      

    

    

    SELECT * FROM suggest;           

    

    

    COMMIT;

 

 

    SELECT COUNT(sqlquery) as cnt

    FROM suggest 

    WHERE sqlquery LIKE 'INS%'

    ORDER BY sqlquery;

 

    SELECT COUNT(sqlquery) as cnt

    FROM suggest 

    WHERE sqlquery LIKE '자%'

    ORDER BY sqlquery;

    

    

    SELECT sqlquery 

    FROM suggest 

    WHERE sqlquery LIKE 'INS%'

    ORDER BY sqlquery;

 

 

    SELECT sqlquery 

    FROM suggest 

    WHERE sqlquery LIKE '자%'

    ORDER BY sqlquery;

 

 

 

2.  web.xml

 

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

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd" id="WebApp_ID" version="3.1">

  <display-name>ojttest</display-name>

  <welcome-file-list>

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

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

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

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

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

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

  </welcome-file-list>

  

  <!-- *************** Suggest Servlet *************** -->

    <servlet><!-- 서블릿 이름과 서블릿 클래스 선언 -->

        <description></description>

        <display-name>Suggest</display-name>

        <servlet-name>Suggest</servlet-name>

        <servlet-class>suggest.Suggest</servlet-class>

    </servlet>

    <servlet-mapping><!-- URL상으로 접근해야 하는 경로명 -->

        <servlet-name>Suggest</servlet-name>

        <url-pattern>/suggest</url-pattern>

    </servlet-mapping>  

  

</web-app>

    

    

    

    

 

3. Servlet

 

 

>>>>> Suggest.java    

 

package suggest;

 

import java.io.IOException;

import java.io.PrintWriter;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

 

import javax.servlet.ServletException;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

 

import utility.DBOpen ;

import utility.DBClose;

 

public class Suggest extends javax.servlet.http.HttpServlet {

    // 서블릿 버전

    static final long serialVersionUID = 1L;

   

    public Suggest() {

        super();

    }    

 

    // Form - get 방식 처리

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

        //출력시 사용할 문자셋 지정

        response.setContentType("text/html;charset=UTF-8");

        request.setCharacterEncoding("utf-8");

        

        //출력시 사용할 객체 생성

        PrintWriter out = response.getWriter();

        

        // 문자셋 테스트

        // Utility.charsetTest(request.getParameter("keyword"));

        

        String keyword = request.getParameter("keyword");

        System.out.println("servlet keyword: " + keyword);

        

        int count = 0;

        

        if (keyword == null){

            keyword ="";

        }

        

        Connection con = DBOpen.getConnection();

        PreparedStatement pstmt = null;

        ResultSet rs = null;

        StringBuffer sql = null;

        

        try {

            sql = new StringBuffer();

            

            sql.append(" SELECT COUNT(sqlquery)");

            sql.append(" FROM suggest");

            sql.append(" WHERE sqlquery LIKE '" + keyword +"%'");

            sql.append(" ORDER BY sqlquery ASC");            

 

            pstmt = con.prepareStatement(sql.toString());

            rs = pstmt.executeQuery(); // 쿼리 실행

            

            if (rs.next()){

             count = rs.getInt(1);  // 수량 산출

            }

            

            sql.delete(0, sql.toString().length()); // buffer clear

            

            sql.append(" SELECT sqlquery");

            sql.append(" FROM suggest");

            sql.append(" WHERE sqlquery LIKE '" + keyword +"%'");

            sql.append(" ORDER BY sqlquery ASC");

            

            pstmt = con.prepareStatement(sql.toString());

            rs = pstmt.executeQuery(); // 쿼리 실행

            

            

            // Client로 지정

            // 문자열 조합: 갯수|

            out.print(count + "|");

            

            while(rs.next()) {

                count = count - 1;

                String key = rs.getString("sqlquery");

                out.print(key);

                if (count > 0){ // 값의 중간만 콤마 출력

                    out.print(",");

                }

            }

            

        }catch(Exception ex) {

            System.out.println("Exception" + ex);

        }finally{

             DBClose.close(con,pstmt, rs);

        }        

        

 

    }   

 

    // Form - post 방식 처리

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

     // GET 방식으로 전달

        doGet(request, response); 

    }           

}