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