Database Design

Connect SQL*PLUS as SYSDBA.

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.

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

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 (ojdbc6.jar) and Paste it to CATALINA_HOME/lib.

Test

Create a 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 to visit http://localhost:8080/test.jsp to check if "1" is displayed.