[Spring] Day05 (Code)

1. JDBCTests

package com.test.persistence;

import static org.junit.Assert.*;

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

import org.junit.Test;

import lombok.extern.log4j.Log4j;

//JDBC 드라이버 배치가 잘 되었는지 확인하는 테스트
@Log4j
public class JDBCTests {

	//초기화 블럭에서 oracle 드라이버 로딩
	static {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	
	@Test
	public void testConnection() {
		try(Connection conn = DriverManager.getConnection(
				"jdbc:oracle:thin:@javaking.iptime.org:1521:ORCL",//외부에서 접근할 수 있는 디비 경로
				"java06", //DB 유저네임
				"java06") ) { //DB 패스워드
			log.info(conn);
		}catch(Exception e){
			fail(e.getMessage());
		}
	}

}

log4jdbc.log4j2.properties

#comment: no whitespace, no Korean
log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

 

2. root-context

<?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"
	xsi:schemaLocation="http://www.springframework.org/schema/beans 	https://www.springframework.org/schema/beans/spring-beans.xsd
	http://www.springframework.org/schema/context
	https://www.springframework.org/schema/context/spring-context.xsd
	http://mybatis.org/schema/mybatis-spring
	http://mybatis.org/schema/mybatis-spring-1.2.xsd"
	>
	
	<!-- 서비스와 관련된 것, DAO, DB 관련 등 모든 곳에서 참조해야 되는 설정들 여기에 쓰면 된다. -->
	<!-- Root Context: defines shared resources visible to all other web components -->
	
	<!-- Date date = new Date(); 원래 객체 생성 방법. 밑과 동일 -->
	<bean id="date" class="java.util.Date"/>
	<!-- 같은 클래스로 빈 두개 설정: 같은 타입의 객체 두개 생성 -->
	<bean id="day" class="java.util.Date"/>
	<!-- 스프링빈으로 등록. 스프링아~ 객체 생성해서 관리해줘. -->	
	
	<!-- DB 관련 세팅 -->
	<!-- 커넥션 관련 설정 정보 -->
	<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
		<property name="driverClassName" value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property>
		<property name="jdbcUrl" value="jdbc:log4jdbc:oracle:thin:@javaking.iptime.org:1521:ORCL"></property>
		<property name="username" value="java06"></property>
		<property name="password" value="java06"></property>
	</bean>
	<!-- 커넥션 맺어줄때 사용하는 클래스 등록 -->
	<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource" destroy-method="close">
		<constructor-arg ref="hikariConfig"/>
	</bean>
	<!-- 커섹션 풀 역할 connection(java) == session(mybatis)-->
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource"></property>
	</bean>
	
	<!-- mybatis mapper 스캔 추가 -->
	<mybatis-spring:scan base-package="com.test.mapper"/>
	<context:component-scan base-package="com.test.sample"/> 
</beans>

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/maven-v4_0_0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.basic</groupId>
	<artifactId>controller</artifactId>
	<name>myspring</name>
	<packaging>war</packaging>
	<version>1.0.0-BUILD-SNAPSHOT</version>
	<properties>
		<java-version>11</java-version>
		<org.springframework-version>5.1.5.RELEASE</org.springframework-version>
		<org.aspectj-version>1.9.3</org.aspectj-version>
		<org.slf4j-version>1.7.30</org.slf4j-version>
	</properties>
	<dependencies>
		<!-- 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>
		
		<!-- lombok: 자바 클래스 getter, setter, toString 생성자 등 자동으로 생성
		https://mvnrepository.com/artifact/org.projectlombok/lombok -->
		<dependency>
		    <groupId>org.projectlombok</groupId>
		    <artifactId>lombok</artifactId>
		    <version>1.18.22</version>
		    <scope>provided</scope>
		</dependency>
		
		<!-- 스프링 테스트:
		https://mvnrepository.com/artifact/org.springframework/spring-test -->
		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-test</artifactId>
		    <version>${org.springframework-version}</version>
		</dependency>
		
		<!-- DB 연결 관련 -->
		<!-- Oracle 드라이버 https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 -->
		<dependency>
		    <groupId>com.oracle.database.jdbc</groupId>
		    <artifactId>ojdbc8</artifactId>
		    <version>19.3.0.0</version>
		</dependency>
		<!-- DB커넥션 https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-jdbc</artifactId>
		    <version>${org.springframework-version}</version>
		</dependency>
		<!-- 커넥션풀: https://mvnrepository.com/artifact/com.zaxxer/HikariCP -->
		<dependency>
		    <groupId>com.zaxxer</groupId>
		    <artifactId>HikariCP</artifactId>
		    <version>3.3.1</version>
		</dependency>
		<!-- MyBatis: https://mvnrepository.com/artifact/org.mybatis/mybatis -->
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis</artifactId>
		    <version>3.5.6</version>
		</dependency>
		<!-- MyBatis-Spring :https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis-spring</artifactId>
		    <version>2.0.6</version>
		</dependency>
		<!--Log4Jdbc Log4j2 JDBC 4 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>
		
		
		
		
				
		<!-- 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>
		
		<!--log4j (보안은 무시하고 구버전 사용.) 
		https://mvnrepository.com/artifact/log4j/log4j -->
		<dependency>
		    <groupId>log4j</groupId>
		    <artifactId>log4j</artifactId>
		    <version>1.2.17</version>
		</dependency>
		
		
		<!--  
		<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>
		-->

		<!-- @Inject -->
		<dependency>
			<groupId>javax.inject</groupId>
			<artifactId>javax.inject</artifactId>
			<version>1</version>
		</dependency>
				
		<!-- Servlet 
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>servlet-api</artifactId>
			<version>2.5</version>
			<scope>provided</scope>
		</dependency>
		-->
		<!-- servelt 3.1로 변경
		https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
		<dependency>
		    <groupId>javax.servlet</groupId>
		    <artifactId>javax.servlet-api</artifactId>
		    <version>3.1.0</version>
		    <scope>provided</scope>
		</dependency>
		
		<dependency>
			<groupId>javax.servlet.jsp</groupId>
			<artifactId>jsp-api</artifactId>
			<version>2.1</version>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>
	
		<!-- Test: 충돌 이슈때문에 4.12로 변경 -->
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>4.12</version>
			<scope>test</scope>
		</dependency>        
	</dependencies>
    <build>
        <plugins>
            <plugin>
                <artifactId>maven-eclipse-plugin</artifactId>
                <version>2.9</version>
                <configuration>
                    <additionalProjectnatures>
                        <projectnature>org.springframework.ide.eclipse.core.springnature</projectnature>
                    </additionalProjectnatures>
                    <additionalBuildcommands>
                        <buildcommand>org.springframework.ide.eclipse.core.springbuilder</buildcommand>
                    </additionalBuildcommands>
                    <downloadSources>true</downloadSources>
                    <downloadJavadocs>true</downloadJavadocs>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.5.1</version>
                <configuration>
                    <source>11</source>
                    <target>11</target>
                    <compilerArgument>-Xlint:all</compilerArgument>
                    <showWarnings>true</showWarnings>
                    <showDeprecation>true</showDeprecation>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.2.1</version>
                <configuration>
                    <mainClass>org.test.int1.Main</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

 

3. DataSourceTests

package com.test.persistence;

import static org.junit.Assert.fail;

import java.sql.Connection;

import javax.sql.DataSource;

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 lombok.Setter;
import lombok.extern.log4j.Log4j;

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

	@Setter(onMethod_=@Autowired)
	private DataSource dataSource;
	
	
	@Test
	public void testConnection(){
		try(Connection conn=dataSource.getConnection()){
			log.info(conn);
		}catch(Exception e) {
			fail(e.getMessage());
		}
		
	}
}

 

4.

com.test.mapper > TimeMapper.xml 

폴더 구조 참조

 

5. TimeMapper.java

package com.test.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Select;

import com.test.model.TestDTO;

public interface TimeMapper {

	//어노테이션으로 쿼리문 작성/실행 -> 간단한 쿼리문 실행시킬 때
	@Select("select sysdate from dual")
	public String getTime();
	
	//2. 인터페이스 + XML 쿼리문 작성/ 실행
	//리턴타입: 쿼리문 실행 결과로 받고싶은 타입
	//레코드가 여러개 결과로 나온다 -> List
	//레코드가 한개나 0개라면 
	public String getTime2();
	
	//select id from test: 레코드가 2개 이상 나옴 -> List로 담아주는 게 좋다.
	public List<String> getAllId();
	
	//회원 정보 1명 가져오기 select * from test where id=#{id}
	public TestDTO getMember(String id);
	
	//회원 추가: insert into test values();
	public int insertMember(TestDTO dto);
	
	//회원 한명 비밀번호 수정:modifyPw: update test set pw=#{pw} where id=#{id}: 매퍼 태그 <update>
	public int modifyPw(TestDTO dto); //아이디, 패스워드 보내야하니 테스트디티오. 업데이트 갱신된거 알고싶으면 int 아니면void
	
	
	//회원 삭제 deleteMember: delete from test where id=#{id} 매퍼 태그 <delete>
	public int deleteMember(String id); //id만 던져주면 되니까 스트링

}

 

6. 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.test.mapper.TimeMapper"> 
  <!-- 인터페이스랑 혼용해서 쓰기 때문에 인터페이스 경로를 똑바로 적어줘야함. -->
  
  <select id="getTime2" resultType="string"> 
  	select sysdate from dual
  </select>
  
  <select id="getAllId" resultType="String">
  select id from test
  </select>
  	
  <select id="getMember" resultType="com.test.model.TestDTO">
  	select * from test where id=#{id}
  </select>
  
  <insert id="insertMember">
  	insert into test values(#{id},#{pw},#{age},sysdate)
  </insert>
  
  <update id="modifyPw">
  	update test set pw=#{pw} where id=#{id}
  </update>
  
  <delete id="deleteMember">
  delete from test where id=#{id}
  </delete>
  
  </mapper>

 

7. TimeMapperTests

package com.test.persistence;

import java.util.List;

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.test.mapper.TimeMapper;
import com.test.model.TestDTO;

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 TimeMapperTests {
	
	@Setter(onMethod_=@Autowired)
	private TimeMapper timeMapper;
	
	
	@Test
	public void testGetAllId() {
		log.info("*************************************************");
		List<String> list = timeMapper.getAllId();
		log.info(list);
		log.info("*************************************************");
	}
	
	@Test
	public void testGetMember() {
		TestDTO dto = timeMapper.getMember("java");
		log.info(dto);
		log.info(dto.getId());
	}
	
	@Test
	public void testInsertMember() {
		TestDTO dto = new TestDTO();
		dto.setId("spring01");
		dto.setPw("dfa34");
		dto.setAge(30);
		int result = timeMapper.insertMember(dto);
		log.info("****************" + result);
	}
	
	@Test
	public void testmodifyPw() {
		TestDTO dto = new TestDTO();
		dto.setId("spring01");
		dto.setPw("0000");
		int result = timeMapper.modifyPw(dto);
		log.info(result);
		
	}
	
	@Test
	public void testDeleteMember() {
		int result = timeMapper.deleteMember("java");
		log.info(result);
	}
	
	@Test
	public void testGetTime() {
		log.info(timeMapper.getClass().getName());
		log.info(timeMapper.getTime()); //인터페이스의 메서드 호출하여 쿼리문 실행하기
	}

	@Test
	public void testGetTime2() {
		log.info("*******************");
		log.info(timeMapper.getTime2());
		log.info("*******************");
	}*/
	
	
}