Certification/Spring(2V0-72.22, SCP)

Spring WEB - DB 연결과 Test

엘호리스 2018. 8. 5. 21:30

라이브러리 사용을 위한 dependency

  • 오라클

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<!-- 커넥션 풀 -->
<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.4</version>
</dependency>
<!-- 스프링 JDBC  -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>${org.springframework-version}</version>
</dependency>
<!--  ojdbc 오라클 JDBC 드라이버 -->
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.3</version>
</dependency>
<!-- 마이바티스 라이브러리 -->
<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.1</version>
</dependency>
cs

root-context.xml

1
2
3
4
5
6
7
8
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <!-- 드라이버 클래스 이름이 변경됨 -->
    <property name="driverClassName" value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property>
    <!-- 연결문자열에 log4jdbc가 추가됨 -->
    <property name="url" value="jdbc:log4jdbc:oracle:thin:@localhost:1521:xe" />
    <property name="username" value="spring" />
    <property name="password" value="1234" />
</bean>
cs
  • MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<!-- 커넥션 풀 -->
<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.4</version>
</dependency>
<!-- 스프링 JDBC  -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>${org.springframework-version}</version>
</dependency>
<!-- MySQL Connector JAVA 드라이버 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>${mysql-version}</version>
</dependency>
<!-- 마이바티스 라이브러리 -->
<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.1</version>
</dependency>
cs

root-context.xml

1
2
3
4
5
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
    <property name="url" value="jdbc:mysql://localhost:port/스키마명"></property>
    <property name="username" value="username"></property> <property name="password" value="password"></property>
</bean>
cs
  • MariaDB

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<!-- 커넥션 풀 -->
<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.4</version>
</dependency>
<!-- 스프링 JDBC  -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-jdbc</artifactId>
    <version>${org.springframework-version}</version>
</dependency>
<!--  maria DB -->
<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>2.2.3</version>
</dependency>
<!-- 마이바티스 라이브러리 -->
<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.1</version>
</dependency>
cs

root-context.xml

1
2
3
4
5
6
<bean id="dataSource" class="org.springframework.jdbc.datasource.SimpleDriverDataSource">
    <property name="driverClass" value="org.mariadb.jdbc.Driver"></property>
    <property name="url" value="jdbc:mariadb://localhost:3306/데이터베이스명"></property>
    <property name="username" value="아이디"></property>
    <property name="password" value="비밀번호"></property>
</bean>
cs

JUnit Test를 위한 dependency

1
2
3
4
5
6
7
<!-- Test -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
</dependency>
cs


로깅을 위한 dependency

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
<dependency>
    <groupId>org.bgee.log4jdbc-log4j2</groupId>
    <artifactId>log4jdbc-log4j2-jdbc4</artifactId>
    <version>1.16</version>
</dependency>
 
<!-- Logging -->
<!-- logback 로깅 관련 -->
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.2.3</version>
</dependency>
<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>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</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>
cs


  • 오라클 연결 테스트

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package com.example.spring01;
 
import java.sql.Connection;
import java.sql.DriverManager;
 
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
public class OracleConnectionTest {
    //로깅을 위한 변수 선언
    private static final Logger logger=LoggerFactory.getLogger(OracleConnectionTest.class);
    
    //DB연결 관련 변수 선언
    private static final String DRIVER="oracle.jdbc.driver.OracleDriver";
    //연결문자열 jdbc:oracle:thin:@호스트:포트:sid
    private static final String URL="jdbc:oracle:thin:@localhost:1521:xe";
    private static final String USER="spring"//아이디
    private static final String PW="1234"//비번
    
    @Test // JUnit이 테스트하는 method
    public void test() throws Exception {
        Class.forName(DRIVER); //드라이버 로딩
        try(Connection conn=DriverManager.getConnection(URL,USER,PW)){
            //로깅을 사용한 예제
            logger.info("오라클에 연결되었습니다.");
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}
cs


  • MySQL 연결 테스트

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package com.example.spring01;
 
import java.sql.Connection;
import java.sql.DriverManager;
 
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
public class MySQLConnectionTest {
    //로깅을 위한 변수 선언
    private static final Logger logger=
            LoggerFactory.getLogger(OracleConnectionTest.class);
    private static final String DRIVER
        ="com.mysql.jdbc.Driver";
    //연결문자열 jdbc:oracle:thin:@호스트:포트:sid
    private static final String URL
        ="jdbc:mysql://127.0.0.1:3306/spring_ex?useSSL=false&serverTimezone=Asia/Seoul";
    private static final String USER="spring"//아이디
    private static final String PW="1234"//비번
    @Test // JUnit이 테스트하는 method
    public void test() throws Exception {
        Class.forName(DRIVER); //드라이버 로딩
        try(Connection conn=
                DriverManager.getConnection(URL,USER,PW)){
            logger.info("MySQL에 연결되었습니다.");
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}
cs



  • MariaDB 연결 테스트

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
package org.zerock.web;
 
import java.sql.Connection;
import java.sql.DriverManager;
 
import org.junit.Test;
 
public class MariaDBConnectionTest {
    private static final String DRIVER = "org.mariadb.jdbc.Driver";
    private static final String URL    = "jdbc:mariadb://192.168.0.4:3306/study";
    private static final String USER   = "study";
    private static final String PW     = "study";
    
    @Test
    public void testConnection() throws Exception {
        Class.forName(DRIVER);
        try {
            Connection con = DriverManager.getConnection(URL, USER, PW);
            //sysout으로 콘솔 로그 출력
            System.out.println(con);
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }
}
cs


'Certification > Spring(2V0-72.22, SCP)' 카테고리의 다른 글

Spring MVC DTO / VO  (0) 2018.08.07
Spring MVC Controller  (0) 2018.08.06
Spring WEB MVC 프로세스 - Home.jsp 동작순서  (0) 2018.08.05
Spring MVC 웹프로젝트 기본 설정  (0) 2018.08.05
Spring MVC 환경 설정  (0) 2018.08.04