[포트폴리오 페이지]_3단계 DB연결 (feat. oracle)

사용 툴 : IntelliJ

사용 DB : Oracle

사용 DB framework : Mybatis

빌더 : Maven

환경 : Spring

 

oracle 버전] 19버전

*참고로 19부터 사용자 계정 생성시 앞에 붙는 c##도 db연결시 아이디에 동일하게 작성해야 합니다.

 

1단계]

Pom.xml 파일에 Dependency 추가 후 maven 동기화

<!-- oracle -->
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc7</artifactId>
    <version>12.1.0.2</version>
</dependency>

<!-- Test -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
</dependency>


<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>3.4.2</version>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-context</artifactId>
    <version>5.3.3</version>
</dependency>


<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.3</version>
</dependency>

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>2.0.3</version>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-tx</artifactId>
    <version>${org.springframework-version}</version>
</dependency>

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>${org.springframework-version}</version>
</dependency>

* SQL Developer를 설치했다면 해당 jar파일을 직접 추가해줘도 됩니다.

* 만약 mybatis가 정상 설치가 안된다면, xml 파일을 통해 경로를 지정해줘도 됩니다.

 

2번] rootContext.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:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
       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://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">


    <bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
        <property name="driverClassName" value="oracle.jdbc.pool.OracleDataSource"></property>
        <property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521:orcl"></property>
        <!-- Oracle 11g 경우 value="jdbc:oracle:thin:@URL주소:1521:XE" -->
        <!-- Oracle 19  경우 value="jdbc:oracle:thin:@URL주소:1521:orcl" -->
        <property name="username" value="db아이디"></property>
        <property name="password" value="db비번"></property>
    </bean>

    <bean id="datasource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
        <constructor-arg ref="hikariConfig"></constructor-arg>
    </bean>

    <mybatis-spring:scan base-package="com.yoon.mapper"/>
    <context:component-scan base-package="com.yoon.mapper"></context:component-scan>
    <context:component-scan base-package="com.yoon.model"></context:component-scan>
    <context:component-scan base-package="com.yoon.service"></context:component-scan>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="datasource"></property>
    </bean>

</beans>

* 참고로 과거 포스팅 (Esclipse) 사용시 namespace라는 부분이 나오는데 Intellij에서는 그냥 타이핑만 해줘도 자동으로 추가가 됩니다.

 

3] DB 테스트

package com.yoonweb;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;

import static org.springframework.test.util.AssertionErrors.fail;

public class JDBCTest {

    static {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        } catch(Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    public void testConnection() {

        try(Connection con =
                    DriverManager.getConnection(
                            // Oracle19 버전인 경우 => "jdbc:oracle:thin:@localhost:1521:orcl"
                            // Oracle11 버전인 경우 => "jdbc:oracle:thin:@localhost:1521:XE"
                            "jdbc:oracle:thin:@localhost:1521:orcl",
                            "db아이디",
                            "db비번")){
            System.out.println(con);
        } catch (Exception e) {
            fail(e.getMessage());
        }

    }

}

성공시 oracle db에 연결된 것입니다.

 

 

도움이 많이 된 블로그]

https://kimvampa.tistory.com/55?category=765899 

[Spring][03-01]JDBC 연결 테스트 (Oracle DB 연결)