05 데이터 베이스

알재·2023년 6월 24일
0

JSP & Servlet

목록 보기
5/16

5.2 오라클 설치

설치 참고
[MacOS] M1 맥북 도커로 ORACLE DB 실행하기
ORA-28040: No matching authentication protocol 해결방법

5.3 사용자 계정 생성 및 권한 설정

5.4 테이블 및 시퀀스 생성

회원제 게시판을 만들어보기 위해 회원 정보를 저장할 수 있는 member 테이블과 게시글을 저장할 board 테이블을 생성한다.

5.4.1 테이블 생성

  • id : 최대 10문자까지이며, 값이 없을수 없다. member테이블에서 다른 회원과 구분할 기준이므로 기본키로 지정.
  • pass : 최대 10문자까지이며 값이 없을수 없다.
  • name : 최대 10문자까지이며 값이 없을수 없다.
  • regidate : date 타입의 값을 저장. 현재 시스템 시간이 주어짐.

  • number : 입력한 값만큼 공간이 자동으로 할당.
  • number(6) : 소수점을 포함한 전체 자릿수를 6으로 지정.

5.4.2 외래키로 테이블 사이의 관계 설정

5.4.3 일련번호용 시퀀스 객체 생성

시퀀스는 순차적으로 증가하는 순번을 반환하는 데이터베이스 객체이다.

1부터 무한대까지 1씩 증가하는 시퀀스이다.

5.4.4 동작 확인

테이블 테스트

member 테이블에 아직 참조가 될 부모 레코드가 없어서 오류가 발생.


5.5 JDBC 설정 및 데이터베이스 연결

JDBC란 자바로 데이터베이스 연결 및 관련 작업을 할 때 사용하는 API이다.
JDBC API를 사용하기 위해서는 JDBC 드라이버가 있어야한다.

5.5.1 JDBC 드라이버 설정

ojdbc(X).jar 파일을 /WEB-INF/lib 폴더에 위치시킨다.

5.5.2 연결 관리 클래스 작성

JDBConnect.java

package common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.ServletContext;

public class JDBConnect {
    public Connection con;
    public Statement stmt;  
    public PreparedStatement psmt;  
    public ResultSet rs;

    // 기본 생성자
    public JDBConnect() {
        try {
            // JDBC 드라이버 로드
            Class.forName("oracle.jdbc.OracleDriver");

            // DB에 연결
            String url = "jdbc:oracle:thin:@localhost:1521:xe";  
            String id = "musthave";
            String pwd = "1234"; 
            con = DriverManager.getConnection(url, id, pwd); 

            System.out.println("DB 연결 성공(기본 생성자)");
        }
        catch (Exception e) {            
            e.printStackTrace();
        }
    }

    // 연결 해제(자원 반납)
    public void close() { 
        try {            
            if (rs != null) rs.close(); 
            if (stmt != null) stmt.close();
            if (psmt != null) psmt.close();
            if (con != null) con.close(); 

            System.out.println("JDBC 자원 해제");
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}
  • Connection : 데이터베이스와 연결을 담당.
  • Statement : 인파라미터가 없는 정적 쿼리문을 실행 할 때 사용.
  • PrepareStatement : 인파라미터가 있는 동적 쿼리문을 실행할 때 사용. 인파라미터는 쿼리문 작성 시 매개변수로 전달된 값을 설정할 때 사용. ? 로 표현한다.
  • ResultSet : SELECT 쿼리문의 결과를 저장할 때 사용.

5.5.3 동작 확인

ConnectionTest.jsp

<%@ page import="common.JDBConnect"%> 
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<html>
<head><title>JDBC</title></head>
<body>
    <h2>JDBC 테스트 1</h2>
    <%
    JDBConnect jdbc1 = new JDBConnect(); 
    jdbc1.close(); 
    %>
</body>
</html>

5.5.4 연결 설정 개선

접속 정보가 변경 되는 경우 클래스를 수정한 후 다시 컴파일하는 불편함이 있기 때문에 서버 환경과 관련된 정보를 한 곳에서 관리하는것이 좋다.
주로 web.xml에 입력하여놓고 필요할 때마다 application 내장 객체를 통해 얻어온다.

web.xml

<!-- 오라클 데이터베이스 접속 정보 -->
  <context-param>
    <param-name>OracleDriver</param-name>    <!-- 드라이버 이름 -->
    <param-value>oracle.jdbc.OracleDriver</param-value>
  </context-param>
  <context-param>
    <param-name>OracleURL</param-name>       <!-- 접속 URL -->
    <param-value>jdbc:oracle:thin:@localhost:1521:xe</param-value>
  </context-param>
  <context-param>
    <param-name>OracleId</param-name>       <!-- 계정 아이디 -->
    <param-value>musthave</param-value>
  </context-param>
  <context-param>
    <param-name>OraclePwd</param-name>      <!-- 패스워드 -->
    <param-value>1234</param-value>
  </context-param>
JDBConnect.java

// 두 번째 생성자
    public JDBConnect(String driver, String url, String id, String pwd) {
        try {
            // JDBC 드라이버 로드
            Class.forName(driver);  

            // DB에 연결
            con = DriverManager.getConnection(url, id, pwd);

            System.out.println("DB 연결 성공(인수 생성자 1)");
        }
        catch (Exception e) {            
            e.printStackTrace();
        }
    }
ConnectionTest.jsp

<h2>JDBC 테스트 2</h2>
    <%
    String driver = application.getInitParameter("OracleDriver"); 
    String url = application.getInitParameter("OracleURL");
    String id = application.getInitParameter("OracleId");
    String pwd = application.getInitParameter("OraclePwd");

    JDBConnect jdbc2 = new JDBConnect(driver, url, id, pwd); 
    jdbc2.close();
    %>

getInitParameter()로 web.cml의 컨텍스트 초기화 매개변수를 가져올 수 있다.

5.5.5 연결 설정 개선2

DB접속이 필요할 때마다 JSP에서 동일한 코드를 반복해야하는것을 개선.

JDBConnect.java

    // 세 번째 생성자
    public JDBConnect(ServletContext application) {
        try {
            // JDBC 드라이버 로드
            String driver = application.getInitParameter("OracleDriver"); 
            Class.forName(driver); 

            // DB에 연결
            String url = application.getInitParameter("OracleURL"); 
            String id = application.getInitParameter("OracleId");
            String pwd = application.getInitParameter("OraclePwd");
            con = DriverManager.getConnection(url, id, pwd);

            System.out.println("DB 연결 성공(인수 생성자 2)"); 
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
ConnectionTest.jsp

    <h2>JDBC 테스트 3</h2>
    <%
    JDBConnect jdbc3 = new JDBConnect(application); 
    jdbc3.close();
    %>

5.6 커넥션 풀로 성능 개선

웹은 클라이언트의 요청에 서버가 응답하는 구조이다. 요청이 있을 때마다 DB와 새로 연결하였다가 해제한다면 비용이 많이 발생하여 성능에 영향을 미친다. 이러한 문제의 해결책으로 커넥션 풀을 사용한다.
Connection 객체를 미리 생성하여 풀에 넣어두고, 요청이 있을 때 이미 생성된 Connection 객체를 가져다가 사용하는 방법이다. 사용완료된 객체는 풀에 반납하여 다시 재사용할수 있도록 한다.
유니티에서 쓰이던 오브젝트 풀링과 같은 원리이다.

5.6.1 커넥션 풀과 JNDI

커넥션 풀은 WAS가 제공하는 것을 이용하는것이 좋다. 대부분의 WAS는 커넥션 풀을 비롯한 여러 자원을 JNDI 서비스로 제공한다. JNDI란 자바 소프트웨어에서 객체나 데이터를 전체 경로를 몰라도 이름만으로 찾아 쓸 수 있는 디렉터리 서비스이다. 이름과 실제 객체와의 연결은 외부의 설정 파일에서 관리하므로 다른 객체로 교체하거나 세부 설정을 바꿀 때도 소스 코드를 수정하고 다시 컴파일할 필요가 없다.
DNS가 도메인과 IP 주소를 연결해 주듯 객체 이름과 실제 객체를 연결해주는 역할을 한다.

  1. WAS(tomcat)가 시작할 때 server.xml과 content.xml에 설정한 대로 커넥션 풀을 생성.
  2. JSP 코드에서 JNDI서버(WAS가 제공)로부터 데이터소스 객체을 얻어옴.
  3. 데이터소스로부터 커넥션 객체를 가져옴.
  4. DB 작업을 수행.
  5. 모든 작업이 끝나면 커넥션 객체를 풀로 반환.

5.6.2 커넥션 풀 설정

server.xml은 서버 전체와 관련한 설정을 담고 있으며, content.xml은 각각의 웹 애플리케이션마다 하나씩 주어지는 자원을 설정한다.
server.xml에서 커넥션 풀을 전역 자원으로 선언하고, content.xml에서 이를 참조하는 링크를 추가한다.

server.xml

 <Resource auth="Container"
              driverClassName="oracle.jdbc.OracleDriver"
              type="javax.sql.DataSource" 
              initialSize="0"
              minIdle="5"
              maxTotal="20"
              maxIdle="20"
              maxWaitMillis="5000"
              url="jdbc:oracle:thin:@localhost:1521:xe"
              name="dbcp_myoracle"
              username="musthave"
              password="1234" />
  </GlobalNamingResources>
  • driverClassName : JDBC 드라이버의 클래스명
  • type : 데이터 소스로 사용할 클래스명
  • initialSize : 풀의 초기화 과정에서 미리 만들어놓을 연결 개수(기본값은 0)
  • minIdle : 최소한으로 유지할 연결 개수(기본값은 0)
  • maxTotal : 동시에 사용할 수 있는 최대 연결 개수(기본값은 8)
  • maxIdle : 풀에 반납할 때 최대로 유지될 수 있는 연결 개수(기본값은 8)
  • maxWaitMillis : 새로운 요청이 들어왔을 때 얼마나 대기할지를 밀리초 단위로 기술
  • url : 오라클 연결을 위한 URL
  • name : 생성할 자원(현재는 풀)의 이름
  • username : 계정 아이디
  • password : 계정 패스워드
content.xml

    <ResourceLink global="dbcp_myoracle" name="dbcp_myoracle" 
                  type="javax.sql.DataSource"/>

5.6.3 커넥션 풀 동작 검증

DBConnPool.java

package common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DBConnPool {
    public Connection con;
    public Statement stmt;
    public PreparedStatement psmt;
    public ResultSet rs;

    // 기본 생성자
    public DBConnPool() {
        try {
            // 커넥션 풀(DataSource) 얻기
            Context initCtx = new InitialContext();
            Context ctx = (Context)initCtx.lookup("java:coamp/env");
            DataSource source = (DataSource)ctx.lookup("dbcp_myoracle");

            // 커넥션 풀을 통해 연결 얻기
            con = source.getConnection();

            System.out.println("DB 커넥션 풀 연결 성공");
        }
        catch (Exception e) {
            System.out.println("DB 커넥션 풀 연결 실패");
            e.printStackTrace();
        }
    }

    // 연결 해제(자원 반납)
    public void close() {
        try {            
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (psmt != null) psmt.close();
            if (con != null) con.close();  // 자동으로 커넥션 풀로 반납됨

            System.out.println("DB 커넥션 풀 자원 반납");
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

InitialContext 객체는 JNDI에서 이름과 실제 객체를 연결해주는 개념의 Context이며, lookup() 메서드에 이름을 건네 원하는 객체를 찾아올 수 있다.

Context ctx = (Context)initCtx.lookup("java:coamp/env");

에서는 "java:coamp/env"라는 인수로 Context객체를 얻었다.
"java:coamp/env"는 웹 애플리케이션의 루트 디렉터리이다.
웹 애플리케이션이 사용할 수 있는 모든 자원은 "java:coamp/env"아래에 위치한다.

DataSource source = (DataSource)ctx.lookup("dbcp_myoracle");

"java:coamp/env"아래에 위치한 "dbcp_myoracle" 커넥션 풀을 얻어온다.
"java:coamp/env"은 context.xml 에 추가한 name 속성 값이다.

ConnectionTest.jsp

<%@ page import="common.DBConnPool"%> 

<h2>커넥션 풀 테스트</h2>
    <%
    DBConnPool pool = new DBConnPool();
    pool.close();
    %>

5.7 간단한 쿼리 작성 및 실행

JDBC에서 쿼리문은 java.sql.Statement 인터페이스로 표현되며, Statement 객체는 Connection 객체를 통해 얻어온다.

  • Statement : 인파라미터가 없는 정적 쿼리를 처리할 때 사용
  • PreparedStatement : 인파라미터가 있는 동적 쿼리를 처리할 때 사용
  • CallableStatement : 프로시져나 함수를 호출할 때 사용

Statement 계열의 객체로 쿼리문을 실행할 때는 다음의 두 메서드를 사용한다.

  • executeUpdate() : INSERT, UPDATE, DELETE 쿼리문을 실행할 때 사용. 실행 후 영향을 받은 행의 개수가 int형으로 반환.
  • executeQuery() : SELECT 쿼리문을 실행할 때 사용. 조회한 레코드들의 집합인 ResultSet 객체를 반환.

5.7.1 동적 쿼리문으로 회원 추가

ExeUpdate.jsp

<%@ page import="java.sql.PreparedStatement"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="common.JDBConnect"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<html>
<head><title>JDBC</title></head>
<body>
    <h2>회원 추가 테스트(executeUpdate() 사용)</h2>
    <%
    // DB에 연결
    JDBConnect jdbc = new JDBConnect();
    
    // 테스트용 입력값 준비 
    String id = "test1";
    String pass = "1111";
    String name = "테스트1회원";

    // 쿼리문 생성
    String sql = "INSERT INTO member VALUES (?, ?, ?, sysdate)";  
    PreparedStatement psmt = jdbc.con.prepareStatement(sql);  
    psmt.setString(1, id);
    psmt.setString(2, pass);
    psmt.setString(3, name);

    // 쿼리 수행
    int inResult = psmt.executeUpdate(); 
    out.println(inResult + "행이 입력되었습니다.");

    // 연결 닫기
    jdbc.close(); 
    %>
</body>
</html>
String sql = "INSERT INTO member VALUES (?, ?, ?, sysdate)";

?는 인파라미터라고 하며, 값을 나중에 채워주겠다는 뜻이다.

psmt.setString(1, id);
psmt.setString(2, pass);
psmt.setString(3, name);

setString()의 첫 번째 매개변수는 인파라미터의 순서를 뜻한다.


5.7.2 정적 쿼리문으로 회원 조회

ExeQuery.jsp

<%@ page import="java.sql.ResultSet"%>
<%@ page import="java.sql.Statement"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="common.JDBConnect"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
       pageEncoding="UTF-8"%>  
<html>
<head><title>JDBC</title></head>
<body>
    <h2>회원 목록 조회 테스트(executeQuery() 사용)</h2>
    <%
    // DB에 연결
    JDBConnect jdbc = new JDBConnect();

    // 쿼리문 생성   
    String sql = "SELECT id, pass, name, regidate FROM member";  
    Statement stmt = jdbc.con.createStatement();  

    // 쿼리 수행
    ResultSet rs = stmt.executeQuery(sql);  

    // 결과 확인(웹 페이지에 출력)
    while (rs.next()) { 
        String id = rs.getString(1);
        String pw = rs.getString(2);
        String name = rs.getString("name");
        java.sql.Date regidate = rs.getDate("regidate");
        
        out.println(String.format("%s %s %s %s", id, pw, name, regidate) + "<br/>"); 
    }

    // 연결 닫기
    jdbc.close();
    %>
</body>
</html>

profile
알재
저장소
이전 포스트

04 쿠키(Cookie)

다음 포스트

06 세션(Session)

0개의 댓글

관련 채용 정보