JAVA/JSP 15. 데이터베이스 - 테이블 및 시퀀스 생성, JDBC 설정 및 데이터베이스 연결

*회원제 게시판 만들기-회원 인증 필요

4. 테이블 및 시퀀스 생성

1) 테이블 생성

(1) member 테이블 - 아이디, 패스워드, 이름, 가입 날짜

• 테이블 정의

컬럼명	데이터 타입	null 허용	키	기본값	설명
id	varchar2(10)	N	기본키(Primary Key)		아이디
pass	varchar2(10)	N			패스워드
name	varchar2(30)	N			이름
regidate	date	N		sysdate	가입 날짜

 

• 테이블 생성 쿼리문

create table member (
  id varchar2(10) not null,
  pass varchar2(10) not null,
  name varchar2(30) not null,
  regidate date default sysdate not null,
  primary key (id)
);

 

(2) board 테이블 - 게시판 글 번호, 게시글 제목, 게시글 내용, 작성자 아이디, 작성일, 조회수

• 테이블 정의

컬럼명	데이터 타입	null 허용	키	기본값	설명
num	number	N	기본키		일련번호, 기본키
title	varchar2(200)	N			게시물의 제목
content	varchar2(2000)	N			내용
id	varchar2(10)	N	외래키		작성자의 아이디,  테이블의 id를 참조하는 외래키
postdate	date	N		sysdate	작성일
visitcount	number(6)	Y			조회수

 

• 테이블 생성 쿼리문

create table board (
  num number primary key,
  title varchar2(200) not null,
  content varchar2(2000) not null,
  id varchar2(10) not null,
  postdate date default sysdate not null,
  visitcount number(6)
);

 

(3) 외래키로 테이블 사이의 관계 설정

• board 테이블의 id 칼럼이 member 테이블의 id 칼럼을 참조하도록 해주는 외래키 생성

alter table board
  add constraint board_mem_fk foreign key (id)
  references member (id);

• table altered → 테이블이 변경됨

 

(4) 일련번호용 시퀀스 객체 생성

• 시퀀스(sequence): 순차적으로 증가하는 순번을 반환하는 데이터베이스 객체
• board 테이블의 일련번호 컬럼에서 사용 예정

create sequence seq_board_num
  increment by 1    //1씩 증가
  start with 1    //시작값 1
  minvalue 1    //최솟값 1
  nomaxvalue    //최댓값은 무한대
  nocycle    //순환하지 않음 - cycle로 설정할 경우 최솟값부터 다시 시작
  nocache;    //캐시 안 함 - cache로 설정할 경우 메모리에 시퀀스 값을 미리 할당해둠

 

2) 테이블들이 의도대로 잘 동작하는지 확인

(1) member, board 테이블에 더미 데이터(테스트를 위해 입력하는 가짜 데이터) 입력

• 외래키로 board 테이블의 id 컬럼이 member 테이블의 id 컬럼을 참조하는 제약조건을 설정했기에,
• member 테이블에 먼저 데이터(참조될 부모 레코드)를 채워 넣어야 함

insert into member(id, pass, name) values ('hello', '1234', '헬로');

insert into board (num, title, content, id, postdate, visitcount)
  values (seq_board_num.nextval, '제목1입니다', '내용1입니다', 'hello',
sysdate, 0);

 

(2) 커밋(commit) - 입력된 레코드를 오라클 외부에서 조회하기 위해

• 처음 레코드를 입력하면 오라클은 입력된 레코드를 내부의 임시 테이블에만 저장함
• 명령 프롬프트의 ‘SQL>’옆에 “commit;”이라고 작성하고 엔터 키를 누름

 

5. JDBC 설정 및 데이터베이스 연결

1) JDBC API란?

• Java Database Connectivity로, 자바로 데이터베이스 연결 및 관련 작업을 할 때 사용
• 사용하기 위해서는 JDBC 드라이버가 있어야 함
• 각 DBMS에 맞는 드라이버를 다운로드한 후 설정하면, DBMS 종류에 상관없이 동일한 방식으로 프로그래밍 가능
• JDBC 구조도

 

*API란?

• Application Programing Interface로, 프로그램들이 서로 상호작용하는 것을 도와주는 매개체라 할 수 있음
• 웹 서비스 쪽에서는 대표적으로 카카오나 네이버에서 제공하는 지도 API가 있음 → 간단한 몇 줄의 코드로 우리가 만든 웹 애플리케이션에 지도 출력 가능

2) JDBC 드라이버 설정

• 윈도우 탐색기에서 오라클 설치 경로(oraclexe 폴더 있는 위치)로 가서, oraclexe\app\oracle\product\11.2.0\server\jdbc\lib 경로로 들어감 → 체크표시한 ojdbc6.jar 파일이 바로 오라클 JDBC 드라이버!

 

*프로젝트와 연결

• 드라이버 파일을 개별 프로젝트의 WEB-INF 하위의 lib 폴더에 추가
  • 프로젝트마다 동일한 설정을 반복해야 해서 번거롭지만, 작업 공간을 변경하거나 배포 시에도 드라이버가 함께 따라가기 때문에 편리함
  → 윈도우 탐색기에서 ojdbc6.jar 파일을 이클립스의 {프로젝트 루트}/WebContent/WEB-INF/lib 폴더로 드래그 앤 드롭하면 JDBC 드라이버 설정이 완료됨

 

3) 연결 관리 클래스 작성

• {프로젝트 루트}/Java Resources/src에서 마우스 우클릭 → [New] → [Class]를 클릭하여, common 패키지에 JDBConnect 클래스 생성

// Java Resources/src/common/JDBConnect.java

package common;

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

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

  //2.기본 생성자
  public JDBConnect() {
    try {
      //JDBC 드라이버 코드
      Class.forName("oracle.jdbc.OracleDriver");   //3.
			
      //DB에 연결
      String url = "jdbc:oracle:thin:@localhost:1521:xe";    //4.
      String id = "hello";    //5.
      String pwd = "1234";    //6.
      con = DriverManager.getConnection(url, id, pwd);    //7.
		
      System.out.println("DB 연결 성공(기본 생성자)");    
    } catch (Exception e) {
      e.printStackTrace();
    }
  }

  //8.연결 해제(자원 반납)
  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();
    }
  }
}

(1) 총 4개의 멤버 변수 선언

• Connection: 데이터베이스와의 연결 담당
• Statement: 인파라미터가 없는 정적 쿼리문 실행 시 사용
• PreparedStatement: 인파라미터가 있는 동적 쿼리문 실행 시 사용
• ResultSet: select 쿼리문의 결과 저장 시 사용

(*인파라미터: 쿼리문 작성 시 매개변수로 전달된 값 설정 시 사용 ?(물음표)로 표현)

(2) 생성자: JDBC 드라이버를 이용해 오라클 DB에 연결하는 두 가지 일을 함

(3) JDBC 드라이버를 메모리에 로드

• Class 클래스의 forName()은 new 키워드 대신, 클래스명을 통해 직접 객체 생성 후 메모리에 로드하는 메소드 (인수: 오라클 드라이버 이름)
• JDBC 드라이버로 사용될 클래스는 DB 종류에 따라 달라지므로, 오라클이 아니라면 클래스명 다르게 지정하기

(4), (5), (6) DB에 연결하기 - URL, ID, 패스워드

• DB에 연결하려면 URL, ID, 패스워드가 필요한데, 그 중 URL의 형식은 다음과 같음
• → 프로토콜은 이미 정해져 있는 값이므로 그대로 사용하면 되고, IP 주소와 포트번호, sid는 설치 환경에 따라 달라짐

//오라클 프로토콜(jdbc:oracle:thin까지):@호스트명(혹은 IP 주소):포트번호:sid
"jdbc:oracle:thin:@localhost:1521:xe"

//sid란?
//-> 오라클 인스턴스의 식별자. sqlplus로 system(관리자) 계정으로 접속 후, select instance from v$thread;명령을 실행 시 확인 가능

• ID, 패스워드: 앞에서 생성한 hello 사용자 계정의 것 (각각 hello, 1234)

(7) 준비한 URL, ID, 패스워드를 인수로 DriverManager 클래스의 getConnection() 호출

• 성공적으로 연결되었다면 Connection 객체가 반환됨
• → 여기서 구한 Connection 객체를 통해, 오라클에 연결하는 것

(8) close() 메소드

• DB 관련 작업을 모두 마친 후, 자원 절약을 위해 연결 해제
• 멤버 변수로 선언된 객체 각각을 닫아줌 (이번에는 if문을 이용해 사용된 객체들만 자원 해제)

4) 동작 확인

• 3)의 JDBConnect 클래스를 이용해, 실제로 DB 연결을 테스트하는 jsp 파일 생성

// WebContent/ConnectionTest.jsp
<%@ page import="common.JDBConnect"%>    //1.
<%@ 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();    //2.
  jdbc1.close();    //3.
  %>
</body>
</html>

1. 3)의 JDBConnect 클래스를 임포트
2. 객체 생성
3. 객체를 바로 닫아서 자원 해제

• ConnectionTest.jsp 파일 실행 결과-console 창만 확인하면 되는데, IO 오류발생!! (The Network Adapter could not establish the connection)

 

• 아래 방법으로, 방화벽에서 ‘인바운드 규칙’을 추가하여 해결(참고-https://jhnyang.tistory.com/272)

 

• 해결된 결과

 

5) 연결 설정 개선

(1) 서버 환경 관련 정보들 한 곳에서 관리하기 - web.xml 파일

• web.xml 파일에 입력해놓고 필요할 때마다 application 내장 객체를 통해 얻어 옴
• web.xml 파일에 코드 추가 (오라클 접속 정보를 컨텍스트 초기화 매개변수<(context-param>)로 입력)

// WebContent/WEB-INF/web.xml

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

...

	<context-param>
	    <param-name>OracleDriver</param-name>    //1.드라이버 이름
	    <param-value>oracle.jdbc.OracleDriver</param-value>
	</context-param>
	<context-param>
	    <param-name>OracleURL</param-name>    //2.접속 URL
	    <param-value>jdbc:oracle:thin:@호스트명(혹은 IP 주소):1521:xe</param-value>
	</context-param>
	<context-param>
	    <param-name>OracleId</param-name>    //3.계정 아이디
	    <param-value>hello</param-value>
	</context-param>
	<context-param>
	    <param-name>OraclePwd</param-name>    //4.계정 패스워드
	    <param-value>1234</param-value>
	</context-param>
</web-app>

• JDBConnect 클래스에, 접속 정보를 외부로부터 받는 생성자 추가

// Java Resources/src/common/JDBConnect.java

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

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

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

1. 생성자를 추가하여 하드코딩했던 값들(오라클 접속 정보)을 모두 외부에서 전달받도록 함 (나중에 web.xml에서 정보를 읽어와 이 생성자를 호출함)
2. 생성자가 전달받은 값으로 드라이버를 로드함
3. 생성자가 전달받은 값으로 데이터베이스에 연결

• ConnectionTest.jsp에 코드 추가

// WebContent/Connectiontest.jsp
... 생략 ...
    <h2>JDBC 테스트 2</h2>
    //1.(String pwd~ 부분까지)
    <%
    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);    //2.
    jdbc2.close();
    %>
</body>
</html>

1. application 내장 객체의 getInParameter()로 web.xml의 컨텍스트 초기화 매개변수를 가져옴
2. 가져온 web.xml의 컨텍스트 초기화 매개변수(4가지 설정값)를, 새로운 생성자에 전달

 

• ConnectionTest.jsp 실행 결과

 

• 처음에 web.xml 파일에 DB 정보를 잘못 추가했는데 invalid username/password; logon denied 오류가 뜨길래, cmd에서 오라클 관리자 계정(system, 123456) 접속 후 hello 계정에 권한 부여 (당연히.. 이 방법으로 해결되지 않음)

 

(2) 서버 환경 관련 정보들 한 곳에서 관리하기 - web.xml에서 컨텍스트 초기화 매개변수 직접 가져오기

• (1)도 개선된 코드이지만, DB 접속이 필요할 때마다 동일한 코드를 JSP에서 반복해서 기술해야 함
• → 컨텍스트 초기화 매개변수를 생성자에서 직접 가져올 수 있도록 정의
• JDBConnect.java 파일에 코드 추가

// JavaResources/src/common/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.ServletContest;    //1.

public class JDBConnect {
...생략...

  // 2.(세 번째 생성자)
  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();
  }
}

...생략...

1. application 내장 객체의 타입인 ServletContext를 사용할 수 있도록 임포트
2. 세 번째 생성자 - 매개변수르 application 내장 객체를 받도록 정의. application 내장 객체를 이용해 web.xml로부터 접속정보를 직접 가져옴

• ConnectionTest.jsp에 코드 추가
• → DB에 접속할 때마다 JSP에서 컨텍스트 초기화 매개변수를 읽어오지 않아도 되므로 편리하며, 코드 중복을 줄일 수 있음

// WebContent/ConnectionTest.jsp
...생략...

	<h2>JDBC 테스트 3</h2>
	<%
	JDBConnect jdbc3 = new JDBConnect(application);    //1.application 내장 객체를 인수로 전달함
	jdbc3.close();
	%>
</body>
</html>

• ConnectionTest.jsp 파일 실행 결과 - 위와 같은 오류발생! (The Network Adapter could not establish the connection)
• 해결방법 - 내 컴퓨터의 [서비스] 우클릭 → [관리자 권한으로 실행] 후, ‘Oracle’이 들어간 모든 서비스들을 중지/재시작해준다 (참고-https://jhnyang.tistory.com/272)

 

• ConnectionTest.jsp 파일 다시 실행 - 오류 해결된 콘솔창