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); 

-- for test records  
insert into board values ('small-talk', 'Small talk', '자유게시판');
insert into board values ('qna', 'Q and A', '묻고 답하기');

INSERT INTO member VALUES ('john@gmail.org','1111','John Doe','123-4567-1111');
INSERT INTO member VALUES ('jane@gmail.org','1111','Jane Doe','123-4567-2222');

INSERT INTO authorities VALUES ('john@gmail.org','ROLE_USER');
INSERT INTO authorities VALUES ('john@gmail.org','ROLE_ADMIN');
INSERT INTO authorities VALUES ('jane@gmail.org','ROLE_USER');

commit;

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;

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 "3" is displayed.