english

데이터베이스 설계

SYS 계정으로 접속한다.

 
C:\Documents and Settings\kim> sqlplus scott/tiger

다음에 접속됨:
Oracle Database 11g

SQL> connect sys as sysdba
암호 입력: 
연결되었습니다.
SQL> show user
USER은 "SYS"입니다

접속한 상태에서 다음을 실행한다. 

sqlplus sys as sysdba

Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password: 

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO java IDENTIFIED BY school;

conn java/school
Connected.


create table member (
    email varchar2(60) PRIMARY KEY,
    passwd varchar2(200) NOT NULL,
    name varchar2(20) NOT NULL,
    mobile varchar2(20)
);

create table board (
    boardcd varchar2(20),
    boardnm varchar2(40) NOT NULL,
    boardnm_ko varchar2(40),
    constraint PK_BOARD PRIMARY KEY(boardcd)
);

create table article (
    articleno number,
    boardcd varchar2(20),
    title varchar2(200) NOT NULL,
    content clob NOT NULL,
    email varchar2(60),
    hit number,
    regdate date,
    constraint PK_ARTICLE PRIMARY KEY(articleno),
    constraint FK_ARTICLE FOREIGN KEY(boardcd) REFERENCES board(boardcd)
);

create sequence SEQ_ARTICLE
    increment by 1
    start with 1;

create table comments (
    commentno number,
    articleno number,    
    email varchar2(60),    
    memo varchar2(4000) NOT NULL,
    regdate date, 
    constraint PK_COMMENTS PRIMARY KEY(commentno)
);

create sequence SEQ_COMMENTS
    increment by 1
    start with 1;

create table attachfile (
    attachfileno number,
    filename varchar2(50) NOT NULL,
    filetype varchar2(30),
    filesize number,
    articleno number,
    email varchar2(60),
    constraint PK_ATTACHFILE PRIMARY KEY(attachfileno)
);

create sequence SEQ_ATTACHFILE
    increment by 1
    start with 1;

create table authorities (
    email varchar2(60) NOT NULL,
    authority varchar2(20) NOT NULL,
    constraint fk_authorities FOREIGN KEY(email) REFERENCES member(email)
);

CREATE UNIQUE INDEX ix_authorities ON authorities(email, authority); 

create table views (
    no number,
    articleNo number,
    ip varchar(60),
    yearMonthDayHour char(10),
    constraint PK_VIEWS PRIMARY KEY(no),
    constraint UNIQUE_VIEWS UNIQUE(articleNo, ip, yearMonthDayHour)
);

create sequence SEQ_VIEWS
    increment by 1
    start with 1;

-- for test records  
insert into board values ('chat', 'Chat', '자유게시판');
commit;

DataSource 설정

CATALINA_HOME/conf/Catalina/localhost/ROOT.xml 을 열고 아래와 같이 추가한다.

ROOT.xml
<?xml version="1.0" encoding="UTF-8"?>
<Context
	docBase="C:/www/JSPProject/WebContent"
	reloadable="true">
	
	<Resource
		name="jdbc/jsppjt"
		auth="Container"
		type="javax.sql.DataSource"
		username="java"
		password="school"
		driverClassName="oracle.jdbc.driver.OracleDriver"
		url="jdbc:oracle:thin:@127.0.0.1:1521:XE" 
		maxActive="8"
		maxIdle="4" />
	
			
</Context>

JDBC 드라이버 파일을 CATALINA_HOME/lib에 복사

오라클 JDBC 드라이버 파일인 ojdbc6.jar 파일을 CATALINA_HOME/lib에 복사한다.

테스트

test.jsp 파일을 도큐멘트베이스(C:/www/JSPProject/WebContent)에 생성한다.

test.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" 
	pageEncoding="UTF-8"%>
<%@ page import="java.sql.*,javax.sql.*,javax.naming.*" %>
<%
	Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	
	DataSource ds = null;
	
	int totalRecord = 0;
		
	try {
		Context ic = new InitialContext();
		Context envCtx = (Context) ic.lookup("java:comp/env");
		ds = (DataSource) envCtx.lookup("jdbc/jsppjt");
	} catch (NamingException e) {
		System.out.println(e.getMessage());
	}
	
	try {
		con = ds.getConnection();
		
		String sql = "SELECT count(*) FROM board";
		
		pstmt = con.prepareStatement(sql);
		rs = pstmt.executeQuery();
		rs.next();
		totalRecord = rs.getInt(1);
	} catch (SQLException e) {
		System.out.println(e.getMessage());
		
	} finally {
		try {
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
%>	
<!DOCTYPE html> 
<html lang="ko"> 
<head>
	<meta charset="UTF-8">
	<title>DataSource Test</title>
</head>
<body>
<p>
<%=totalRecord %>
</p>
</body>
</html>

톰캣을 재실행하고 http://localhost:8080/test.jsp를 방문한다. 1를 본다면 성공이다.