JSP게시판 만들기 2강 - 라이브러리 세팅 및 DB 연결

1. 라이브러리 세팅

WebContent/WEB-INF/lib 폴더에 자동 빌드 됨

jstl-1.2.jar

0.40MB

lombok-1.18.8.jar

1.65MB

ojdbc8.jar

3.97MB

gson-2.8.5.jar

0.23MB

2. lombok 세팅

https://blog.naver.com/swiniee/221997729166

Lombok 라이브러리 설치 및 사용법

 

3. DB 연결

1) 오라클 12C 사용자 생성 - system 접속(관리자)

alter session set "_ORACLE_SCRIPT"=true;  
CREATE USER apple IDENTIFIED BY bitc5600;

GRANT CREATE SESSION TO apple;
GRANT CREATE TABLESPACE TO apple;
GRANT CREATE TABLE TO apple;
GRANT CREATE SEQUENCE TO apple;
alter user apple default tablespace users quota unlimited on users;

첫번째 줄은 #같은거 안적어도 되게 하려고

마지막 줄은 무슨 용량어쩌구...

 

2) 오라클 테이블 생성 - ssar접속

CREATE TABLE member(
	id number primary key,
    username varchar2(100) not null unique,
    password varchar2(100) not null,
    email varchar2(100) not null,
    createDate timestamp
) ;

CREATE TABLE post(
	id number primary key,
    memberId number,
    title varchar2(100) not null,
    content clob,
    createDate timestamp,
    foreign key (memberId) references member (id)
);

CREATE SEQUENCE MEMBER_SEQ
  START WITH 1
  INCREMENT BY 1;
  
CREATE SEQUENCE POST_SEQ
  START WITH 1
  INCREMENT BY 1;

3) WebContent/WEB-INF/web.xml 오버라이딩

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

<web-app version="3.1"
	xmlns="http://xmlns.jcp.org/xml/ns/javaee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee              
    http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd">

	<resource-ref>
		<description>Oracle Datasource example</description>
		<res-ref-name>jdbc/myoracle</res-ref-name>
		<res-type>javax.sql.DataSource</res-type>
		<res-auth>Container</res-auth>
	</resource-ref>

	<filter>
		<filter-name>setCharacterEncodingFilter</filter-name>
		<filter-class>org.apache.catalina.filters.SetCharacterEncodingFilter</filter-class>
		<init-param>
			<param-name>encoding</param-name>
			<param-value>utf-8</param-value>
		</init-param>
	</filter>
	<filter-mapping>
		<filter-name>setCharacterEncodingFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>
</web-app>

4) WebContent/META-INF/context.xml 오버라이딩

<?xml version="1.0" encoding="UTF-8"?>
<context>

<Resource name="jdbc/myoracle" auth="Container"
              type="javax.sql.DataSource" driverClassName="oracle.jdbc.driver.OracleDriver"
              url="jdbc:oracle:thin:@localhost:1521:xe"
              username="apple" password="bitc5600" maxTotal="20" maxIdle="10"
              maxWaitMillis="-1"/>
</context>

 

5) con.cos.apple.db 패키지 생성 및 DBConn.java파일 생성

package com.cos.apple.db;

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

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DBConn {
	public static Connection getConnection() {
		try {
			Context initContext = new InitialContext();
			Context envContext  = (Context)initContext.lookup("java:/comp/env");
			DataSource ds = (DataSource)envContext.lookup("jdbc/myoracle");
			Connection conn = ds.getConnection();
			System.out.println("DBConn : 데이터베이스 연결 성공");
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("DBConn : 데이터베이스 연결 실패");
			System.out.println("DBConn : Message : "+e.getMessage());
		}
		return null;
	}
}

 

DB연결 파일 생성 참고

studyandlearn.tistory.com/565

 

4. DB연결 테스트

WebContent/test 폴더 생성 후 dbTest.jsp 파일 생성

<%@page import="com.cos.apple.db.DBConn"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	// DB연결 테스트 - 해당 파일 실행
	DBConn.getConnection();
%>