비즈니스 컴포넌트 실습 I (복습) - 데이터베이스 드라이버 , JDBCUtil, applicationContext.xml

[ pom.xml ]

 

<properties>
		<java-version>1.11</java-version>
		<org.springframework-version>5.3.9</org.springframework-version>
		<org.aspectj-version>1.6.10</org.aspectj-version>
		<org.slf4j-version>1.6.6</org.slf4j-version>
	</properties>
	
	 <!-- 오라클 드라이버 저장소  -->
	 <repositories>
	   <repository>
	   <id>public</id>
	   <url>https://repo1.maven.org/maven2/</url>
	   </repository>
	  </repositories>	
		
	
	<dependencies>
	
		<!-- 오라클 ojdbc8 -->
		<dependency>
		    <groupId>com.oracle.database.jdbc</groupId>
		    <artifactId>ojdbc8</artifactId>
		    <version>19.7.0.0</version>
		</dependency>
	
	 	<!-- H2 데이터베이스 -->
	 	<dependency>
			<groupId>com.h2database</groupId>
			<artifactId>h2</artifactId>
			<version>2.1.214</version>
		</dependency>   
	
		<!-- lombok 추가  -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.22</version>
		</dependency>	
	
		<!-- Spring -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>${org.springframework-version}</version>
			<exclusions>
				<!-- Exclude Commons Logging in favor of SLF4j -->
				<exclusion>
					<groupId>commons-logging</groupId>
					<artifactId>commons-logging</artifactId>
				 </exclusion>
			</exclusions>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>${org.springframework-version}</version>
		</dependency>
				
		<!-- AspectJ -->
		<dependency>
			<groupId>org.aspectj</groupId>
			<artifactId>aspectjrt</artifactId>
			<version>${org.aspectj-version}</version>
		</dependency>	
		
		<!-- Logging -->
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-api</artifactId>
			<version>${org.slf4j-version}</version>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>jcl-over-slf4j</artifactId>
			<version>${org.slf4j-version}</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.slf4j</groupId>
			<artifactId>slf4j-log4j12</artifactId>
			<version>${org.slf4j-version}</version>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.apache.logging.log4j</groupId>
			<artifactId>log4j-core</artifactId>
			<version>2.12.1</version>
			<exclusions>
				<exclusion>
					<groupId>javax.mail</groupId>
					<artifactId>mail</artifactId>
				</exclusion>
				<exclusion>
					<groupId>javax.jms</groupId>
					<artifactId>jms</artifactId>
				</exclusion>
				<exclusion>
					<groupId>com.sun.jdmk</groupId>
					<artifactId>jmxtools</artifactId>
				</exclusion>
				<exclusion>
					<groupId>com.sun.jmx</groupId>
					<artifactId>jmxri</artifactId>
				</exclusion>
			</exclusions>
			<scope>runtime</scope>
		</dependency>

		<!-- @Inject -->

 

 [ JDBCUtil ]

package com.springbook.biz.common;

import java.sql.*;

public class JDBCUtil {
	Connection con = null;
	PreparedStatement stmt = null;
	ResultSet rs = null;
    
	/*
	String url = "jdbc:h2:tcp://localhost/./test";
	String userId = "sa";
	String pass = "1234";
	*/
	
	String url = "jdbc:oracle:thin:@//localhost:1521/xe";
	String userId = "system";
	String pass = "1234";
	
	private static JDBCUtil db = new JDBCUtil();
	
	private JDBCUtil() { }
	
	public static JDBCUtil getInstance() {
		return db;
	}
	
	public Connection getConnection() {
		try {
			// Class.forName("org.h2.Driver");
			Class.forName("oracle.jdbc.OracleDriver");
			con = DriverManager.getConnection(url, userId, pass);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}

	public void close(PreparedStatement stmt, Connection con) {
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void close(ResultSet rs, PreparedStatement stmt, Connection con) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		if (stmt != null) {
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
========================================================================

​

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!doctype html>
<html>
<head>
<meta charset="UTF-8"> <title>DB 연동</title>
</head>
<body>
<h3> Data base 연동 샘플</h3>
<hr>
<% /* 자바 DB 연동 프로그램 */
try{
Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection
("jdbc:oracle:thin:@//localhost:1521/xe","system","1234");
if(con != null)
{
out.println("Database Connect : [ " + " <b>success</b> ]<br>");
}else{
out.println("Database Connect : [ " + " <b>fail</b> ]<br>");
};
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select sysdate from dual");
while(rs.next()){
out.println("Today date : " + rs.getString(1) + "<hr>");
}
stmt.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
%>
<footer>
<address>
<center>
Web site Layout DB Connection Page.<br>
Home page : http://www.visiongongjang.com <br>
mobile : 010-2222-3333 <br>
</center>
</address>
</footer>
</body>
</html>

[ applicationContext.xml ]

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/beans 
	http://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context 
		http://www.springframework.org/schema/context/spring-context-4.3.xsd">

<context:component-scan base-package="com.springbook.biz" />

</beans>