Database design

Connect SQL*PLUS as SYSDBA.

C:\ Command Prompt
C:\Users> sqlplus scott/tiger

Connected to:
Oracle Database 11g

SQL> connect sys as sysdba
Enter password: 
Connected.
SQL> show user
USER is "SYS"

Execute the following while connected.

GRANT CONNECT,RESOURCE,UNLIMITED TABLESPACE TO java IDENTIFIED BY school;
ALTER USER java DEFAULT TABLESPACE USERS;
ALTER USER java TEMPORARY TABLESPACE TEMP;

CONNECT java/school

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

Modify CATALINA_HOME/conf/Catalina/localhost/ROOT.xml as follows.

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>

Copy Oracle JDBC Driver to CATALINA_HOME/lib

Copy ojdbc6.jar to CATALINA_HOME/lib.

Test

Create test.jsp file in the Document Base (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>

Restart Tomcat.
Visit http://localhost:8080/test.jsp and check if "1" is displayed.