스프링 Oracle, mybatis 연동, CRUD 테스트

Oracle, mybatis 연동 준비!

 

 

 

pom.xml

 

<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-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>org.bgee.log4jdbc-log4j2</groupId>
<artifactId>log4jdbc-log4j2-jdbc4</artifactId>
<version>1.16</version>
</dependency>

 

 

 

log4jdbc.log4j2.properties

 

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

 

 

 

 

root-context.xml

 

 

<!-- HikariCP configuration -->
    <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:XE"></property> -->
       <property name="driverClassName" value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property>
       <property name="jdbcUrl" value="jdbc:log4jdbc:oracle:thin:@localhost:1521:XE"></property>
       <property name="username" value="book_ex"></property>
       <property name="password" value="book_ex"></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>

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

 

 

 

 

JDBCTests

 

package com.mystudy.persistence;

import static org.junit.Assert.fail;

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

import org.junit.Test;


import lombok.extern.log4j.Log4j;

@Log4j
public class JDBCTests {

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


@Test
public void testConnection() {

try(Connection con =
DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:XE",
"book_ex",
"book_ex")){
log.info(con);
}catch (Exception e) {
fail(e.getLocalizedMessage());
}


}

}

 

 

 

 

---

 

 

 

 

 

 

BoardVO

 

package com.mystudy.domain;

import java.util.Date;

import lombok.Data;

@Data
public class BoardVO {

private Long bno;
private String title;
private String content;
private String writer;
private Date regdate;
private Date updatedate;

}

 

 

 

 

 

BoardMapper (인터페이스!!!)

 

package com.mystudy.mapper;

import java.util.List;


import com.mystudy.domain.BoardVO;

public interface BoardMapper {

//@Select("select * from tbl_board where bno > 0")
public List<BoardVO> getList();

public void insert(BoardVO boardVO);

public void insertSelectKey(BoardVO boardVO);

public BoardVO read(Long bno);

public int delete(Long bno);

public int update(BoardVO boardVO);

}

 

 

 

 

 

 

BoardMapper.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.mystudy.mapper.BoardMapper">
  
  
   <select id="getList" resultType="com.mystudy.domain.BoardVO">
   <![CDATA[
   select * from tbl_board where bno > 0
   ]]>
   </select>

<insert id="insert">
insert into tbl_board (bno, title, content, writer)
values (seq_board.nextval, #{title}, #{content}, #{writer})
</insert>

<insert id="insertSelectKey">
<selectKey keyProperty="bno" order="BEFORE" resultType="long">
select seq_board.nextval from dual
</selectKey>

insert into tbl_board (bno, title, content, writer)
values (#{bno}, #{title}, #{content}, #{writer})
</insert>

<select id="read" resultType="com.mystudy.domain.BoardVO">
select * from tbl_board where bno = #{bno}
</select>

<delete id="delete">
delete from tbl_board where bno = #{bno}
</delete>
  
   <update id="update" parameterType="com.mystudy.domain.BoardVO">
   update tbl_board 
   set title = #{title},
   content = #{content},
   writer = #{writer},
   updateDate = sysdate 
   where bno = #{bno}
   </update>
  
  </mapper>

 

 

 

 

 

BoardMapperTests

 

package com.mystudy.mapper;


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.mystudy.domain.BoardVO;

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 BoardMapperTests {

@Setter(onMethod_ = @Autowired)
private BoardMapper mapper;

@Test
public void testGetList() {
mapper.getList().forEach(board -> log.info(board));
}

@Test
public void testInsert() {

BoardVO boardVO = new BoardVO();
boardVO.setTitle("새글");
boardVO.setContent("새내용");
boardVO.setWriter("새작성자");

mapper.insert(boardVO);

log.info(boardVO);
}

@Test
public void testInsertSelectKey() {

BoardVO boardVO = new BoardVO();
boardVO.setTitle("새글 SelectKey");
boardVO.setContent("새내용 SelectKey");
boardVO.setWriter("새작성자");

mapper.insert(boardVO);

log.info(boardVO);
}

@Test
public void testRead() {
// 존재하는 게시물 번호로 테스트
BoardVO boardVO = mapper.read(5L);

log.info(boardVO);
}

@Test
public void testDelete() {

log.info("delete count : " + mapper.delete(10L));
}

@Test
public void testUpdate() {
BoardVO boardVO = new BoardVO();

boardVO.setBno(5L);
boardVO.setTitle("수정된 제목");
boardVO.setContent("수정된 내용");
boardVO.setWriter("나는야싼쵸");

int count = mapper.update(boardVO);
log.info("update Count : "+ count);
}


}