웹 프로그래밍 노트

1. 목적 : Spring 프로젝트 생성 후 오라클 DB jdbc 설정하여 정상적으로 연동되어 데이터 조회가 되는지 확인한다.

2. SPEC : 

 1) eclipse : 2020-06 (4.16.0)

 2) STS plug in 설치 : Marketplace에서 sts 검색 후 "Spring Tool 3 Add on ~~ " 설치

 3) DB : oracle 11g R2

스프링 pom.xml 설정

<properties>
	<java-version>1.8</java-version>
	<org.springframework-version>4.3.2.RELEASE</org.springframework-version>
	<org.aspectj-version>1.6.10</org.aspectj-version>
	<org.slf4j-version>1.6.6</org.slf4j-version>
</properties>

<!-- ojdbc 다운받기위해 추가 -->
<repositories>
	<repository>
		<id>oracle</id>
		<name>ORACLE JDBC Repository</name>
		<url>http://mesir.googlecode.com/svn/trunk/mavenrepo</url>
	</repository>
</repositories>

<dependency>
	<groupId>org.springframework</groupId>
	<artifactId>spring-jdbc</artifactId>
	<version>${org.springframework-version}</version>
</dependency> 
<!-- MyBatis -->
<dependency>
	<groupId>org.mybatis</groupId>
	<artifactId>mybatis</artifactId>
	<version>3.4.5</version>
</dependency>
<dependency>
	<groupId>org.mybatis</groupId>
	<artifactId>mybatis-spring</artifactId>
	<version>1.3.2</version>
</dependency>
<dependency>
	<groupId>commons-dbcp</groupId>
	<artifactId>commons-dbcp</artifactId>
	<version>1.4</version>
</dependency> 
<!-- Oracle -->
<dependency>
	<groupId>com.oracle.database.jdbc</groupId>
	<artifactId>ojdbc6</artifactId>
	<version>11.2.0.4</version>
</dependency>

Spring web.xml 설정

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee https://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
</web-app>

Spring TEST 소스 작성 (ex01.java)

package db;

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

public class ex01 {

	public static void main(String[] args) {
		// 1단계 : 데이터 베이스 연결
		try {
			String className = "oracle.jdbc.driver.OracleDriver";
			Class.forName(className);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

		// 2단계 : 연결을 관리하는 객체 생성
		try {
			String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
			/* "jdbc:oracle:thin:@"192.168.0.23":1521:xe"; 로 선택 가능 */
			String id = "scott";
			String pass = "scott";

			Connection conn = DriverManager.getConnection(url, id, pass);

			// 3단계 : 작업관리(Query)
			PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM dept");

			// 4단계 : 결과처리
			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				int deptNo = rs.getInt("deptno");
				String deptName = rs.getString("dname");
				String loc = rs.getString("loc");

				System.out.println(deptNo + "\t" + deptName + "\t" + loc);
			}

			// 5단계 : 종료
			rs.close();
			pstmt.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

}

ex01.java 실행 결과 (Run As - Java Application)

10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES		CHICAGO
40	OPERATIONS	BOSTON

JDBCUtil.java 파일 생성

 - DB 설정 내용을 저장해 놓고 재사용하고 싶은 경우 사용함.

package com.springbook.biz.common;

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

public class JDBCUtil {
	public static Connection getConnection() {
		try {
			//Class.forName("org.h2.Driver");
			//return DriverManager.getConnection("jdbc:h2:tcp://localhost/~/test", "sa", "");
			Class.forName("oracle.jdbc.driver.OracleDriver");
			return DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL",	"scott", "scott");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static void close(PreparedStatement stmt, Connection conn) {
		if (stmt != null) {
			try {
				if (!stmt.isClosed())
					stmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				stmt = null;
			}
		}
		if (conn != null) {
			try {
				if (!conn.isClosed())
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				conn = null;
			}
		}
	}

	public static void close(ResultSet rs, PreparedStatement stmt, Connection conn) {
		if (rs != null) {
			try {
				if (!rs.isClosed())
					rs.close();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				rs = null;
			}
		}
		if (stmt != null) {
			try {
				if (!stmt.isClosed())
					stmt.close();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				stmt = null;
			}
		}
		if (conn != null) {
			try {
				if (!conn.isClosed())
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				conn = null;
			}
		}
	}
}