본문 바로가기
Database/Oracle

Oracle 03 Spinrg 연동 및 설정

by IT새내기IM 2023. 6. 9.

AllArgsConstructor 쓰면 모든곳에 Inject 처리해줌.

 

============= oex00 프로젝트 생성 ( 오라클 연동 ) =============

01. Fiel > New > Spring Legacy Project 클릭 

 

02. 

Project name : oex00

Templates : Spring MVC Project 선택

 

03. 패키지 명 : com.pkt.controller 입력 > [Finish] 클릭

 

======== Next ========

① Java Compiler 

② ProJect Facets 

③ Java Build Path - 설치 jdk-11 매칭

■ Spring 프레임워크 버전 변경 - pom.xml

<properties>

<java-version>11</java-version>

<org.springframework-version>5.0.7.RELEASE</org.springframework-version>

<org.aspectj-version>1.6.10</org.aspectj-version>

<org.slf4j-version>1.6.6</org.slf4j-version>

</properties>

 

<plugin>

<groupId>org.apache.maven.plugins</groupId>

<artifactId>maven-compiler-plugin</artifactId>

<version>3.5.1</version>

<configuration>

<source>11</source>

<target>11</target>

<compilerArgument>-Xlint:all</compilerArgument>

<showWarnings>true</showWarnings>

<showDeprecation>true</showDeprecation>

</configuration>

</plugin>

● Lombok (롬복) 설치하기

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.26</version>
    <scope>provided</scope>
</dependency>

■ JUnit 4 버전 수정 및 추가

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

■ pom.xml ( Oracle 연동 )

① https://mvnrepository.com/artifact/mysql/mysql-connector-java

② ojdbc 검색 - JDK11 포함

 

③ <dependency> 태그 복사  

<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc10 -->

<dependency>

    <groupId>com.oracle.database.jdbc</groupId>

    <artifactId>ojdbc10</artifactId>

    <version>19.18.0.0</version>

</dependency>

④ pom.xml 추가

ㄴ▲ JdbcTest.java ( oracle 연결 테스트 )

package com.pkt.controller;

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

import org.junit.Test;

@Log4j 
public class JdbcTest {

	String url = "jdbc:oracle:thin:@localhost:1521/XEPDB1"; //서비스 네임 접속방법
	String user="WEB2"; //관리자 아이디
	String password="1111"; //관리자 비밀번호

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


	@Test
	public void testConnection() {
		try {
			Connection con = DriverManager.getConnection(url,user,password);
			System.out.println(con); //객체 주소값 출력
			log.info(con); //객체 주소값 출력 
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

ㄴㄴ▲ @Log4j ( 에러가 날 경우 )

<dependency>

<groupId>log4j</groupId>

<artifactId>log4j</artifactId>

<version>1.2.15</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>

[설명]

pom.xml 파일을 열어 해당 소스를 주석처리 or 삭제

■ pom.xml ( Mybatis 관련 라이브러리 6개 추가 )

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

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

		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-test</artifactId>
		    <version>${org.springframework-version}</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>

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

■ root-context.xml ( oracle 연동 )

 <?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"

xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"

xmlns:jdbc="http://www.springframework.org/schema/jdbc"

xmlns:tx="http://www.springframework.org/schema/tx"

xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.3.xsd

http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd

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

http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

 

<!-- Root Context: defines shared resources visible to all other web components -->

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">

<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>

<property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521/XEPDB1"></property>

<property name="username" value="WEB2"></property>

<property name="password" value="1111"></property>

</bean>

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close"> 

<constructor-arg ref="hikariConfig" />

</bean>

 

<bean id="sqlSessionFactory"

class="org.mybatis.spring.SqlSessionFactoryBean">

<property name="dataSource" ref="dataSource"></property>

</bean>

 

</beans>

[ hikari 오류시 아래로 변경 ]
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property>
<property name="url" value="jdbc:log4jdbc:oracle:thin:@localhost:1521/XEPDB1"></property>
<property name="username" value="WEB2"></property>
<property name="password" value="1111"></property>
</bean>

<bean id="sqlSessionFactory"

class="org.mybatis.spring.SqlSessionFactoryBean">

<property name="dataSource" ref="dataSource"></property>

</bean>

● mapper 설정 및 연결

ㄴTimeMapper.java ( interface )

package com.sse.mapper;

import org.apache.ibatis.annotations.Select;

public interface TimeMapper {

	@Select("SELECT sysdate FROM dual")
	public String getTime();
}

ㄴTimeMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper 
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.pkt.mapper.TimeMapper">

	<select id="getTime" resultType="string">
		select sysdate from dual
	</select>

</mapper>

ㄴroot-context.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"
	xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
	xmlns:jdbc="http://www.springframework.org/schema/jdbc"
	xmlns:tx="http://www.springframework.org/schema/tx"
	xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.3.xsd
		http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
		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
		http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

	<!-- Root Context: defines shared resources visible to all other web components -->
	<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
		<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property>
		<property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521/XEPDB1"></property>
		<property name="username" value="WEB2"></property>
		<property name="password" value="1111"></property>
	</bean>

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

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


<mybatis-spring:scan base-package="com.pkt.mapper"/>


<context:component-scan base-package="com.pkt.model"></context:component-scan>

</beans>

ㄴ▲ TimeMapperTest.java

package com.pkt.controller;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.pkt.mapper.TimeMapper;

import lombok.Setter;
import lombok.extern.log4j.Log4j;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("file:src/main/webapp/WEB-INF/spring/root-context.xml")
@Log4j
public class TimeMapperTest {

	@Setter(onMethod_ = @Autowired)
	private TimeMapper timeMapper;


	@Test
	public void tetGetTime() {

		log.info("====getTime====");
		log.info(timeMapper.getTime());

	}
}

● MyBatis 의 로그 log4jdbc-log4j2 설정 (SQL문 console에 찍히게보여주는거)

MyBatis는 내부적으로 JDBC의 PreparedStatement를 이용해서 SQL을 처리한다.

SQL에 전달되는 파라미터는 JDBC에서와 같이 "?"로 치환되어서 처리된다.

 

콘솔에서 SQL 로그를 제대로 보기 위해서 log4jdbc-log4j2 라이브러리를 사용한다.

ㄴpom.xml ( 추가 )

<!-- https://mvnrepository.com/artifact/org.bgee.log4jdbc-log4j2/log4jdbc-log4j2-jdbc4 -->
		<dependency>
			<groupId>org.bgee.log4jdbc-log4j2</groupId>
			<artifactId>log4jdbc-log4j2-jdbc4</artifactId>
			<version>1.16</version>
		</dependency>

ㄴlog4jdbc.log4j2.properties

■ log4jdbc.log4j2.properties 파일 생성 방법 

 


 

■ log4jdbc.log4j2.properties 파일 소스 내용 

log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

 

■ 파일 생성 오픈이 되면

Save As > 프로젝트 선택 > 파일명 : log4jdbc.log4j2.properties 기입후 저장 > 파일 위치 조정

 

 

ㄴroot-context.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"

xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"

xmlns:jdbc="http://www.springframework.org/schema/jdbc"

xmlns:tx="http://www.springframework.org/schema/tx"

xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.3.xsd

http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd

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

http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd">

 

<!-- Root Context: defines shared resources visible to all other web components -->

<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">

<!-- <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"></property> -->

<!-- <property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521/XEPDB1"></property> -->

<property name="driverClassName" value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property>

<property name="jdbcUrl" value="jdbc:log4jdbc:oracle:thin:@localhost:1521/XEPDB1"></property>

<property name="username" value="WEB2"></property>

<property name="password" value="1111"></property>

</bean>

 

<!-- HikariCP configuration -->

<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">

<constructor-arg ref="hikariConfig" />

</bean>

 

<bean id="sqlSessionFactory"

class="org.mybatis.spring.SqlSessionFactoryBean">

<property name="dataSource" ref="dataSource"></property>

</bean>

 

<mybatis-spring:scan base-package="com.pkt.mapper"/>

 

<context:component-scan base-package="com.pkt.model"></context:component-scan>

 

</beans>

ㄴlog4j.xml ( 추가 )

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">

<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">

 

<!-- Appenders -->

<appender name="console" class="org.apache.log4j.ConsoleAppender">

<param name="Target" value="System.out" />

<layout class="org.apache.log4j.PatternLayout">

<param name="ConversionPattern" value="%-5p: %c - %m%n" />

</layout>

</appender>

 

<!-- Application Loggers -->

<logger name="com.pkt.controller">

<level value="info" />

</logger>

 

<!-- 3rdparty Loggers -->

<logger name="org.springframework.core">

<level value="info" />

</logger>

 

<logger name="org.springframework.beans">

<level value="info" />

</logger>

 

<logger name="org.springframework.context">

<level value="info" />

</logger>

 

<logger name="org.springframework.web">

<level value="info" />

</logger>

 

<!-- SQL Logger : 콘솔창에 쿼리문 미 출력시 추가 -->

<logger name="jdbc.connection" additivity="false">

<level value="warn" />

<appender-ref ref="console"/> 

</logger>

 

<logger name="jdbc.audit" additivity="false"> 

<level value="warn"/>  

<appender-ref ref="console"/> 

</logger>

 

<logger name="jdbc.resultset" additivity="false">

<level value="warn" />

<appender-ref ref="console"/> 

</logger>

 

<logger name="jdbc.sqlonly" additivity="false"> 

<level value="info"/> 

<appender-ref ref="console"/> 

</logger>

 

<logger name="jdbc.resultsettable" additivity="false"> 

<level value="info"/>  

<appender-ref ref="console"/> 

</logger> 

 

<logger name="jdbc.sqltiming" additivity="false">

<level value="warn" />

<appender-ref ref="console"/> 

</logger>

 

<!-- Root Logger -->

<root>

<priority value="warn" />

<appender-ref ref="console" />

</root>

 

</log4j:configuration>

● pom.xml ( servlet 버전 변경 )

<!-- Servlet -->

<dependency>

<groupId>javax.servlet</groupId>

<artifactId>servlet-api</artifactId>

<version>2.5</version>

<scope>provided</scope>

</dependency>

 

위의 기존 코드를 아래와 같이 수정

 

<!-- Servlet -->

<dependency>

<groupId>javax.servlet</groupId>

<artifactId>javax.servlet-api</artifactId>

<version>4.0.0</version>

<scope>provided</scope> 

</dependency>

 

 

 

== 설명 ==

Java 설정 등을 이용하려면 서블릿 3.0 이상을 사용하는 것이 좋다.

가능하면 WAS의 Servlet 스펙 버젼을 일치시키는 것이 좋다. 

 

버젼 확인

- 서버 : <%=application.getServerInfo() %><br>

- 서블릿 : <%= application.getMajorVersion() %>.<%= application.getMinorVersion() %>  //4.0 <br>

 

- JSP : <%= JspFactory.getDefaultFactory().getEngineInfo().getSpecificationVersion() %>

저작자표시 (새창열림)

'Database > Oracle' 카테고리의 다른 글

Oracle 기본01 설정잡기  (0) 2023.06.08
Oracle 기본02  (0) 2023.06.08

관련글

댓글

티스토리툴바