[MariaDB] 스프링(Spring)에 MariaDB 연동하기 [Mybatis와 HikariCP로 MariaDB 연동]

---

1. 스프링(Spring)에 MariaDB 연동하기

- pom.xml

<!-- mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.7</version>
</dependency>

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

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

<!-- mariaDB -->
<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>2.2.1</version>
</dependency>

<!-- HikariCP -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>4.0.3</version>
</dependency>

<!-- log4jdbc -->
<dependency>
    <groupId>org.apache.logging.log4j</groupId>
    <artifactId>log4j-core</artifactId>
    <version>2.17.1</version>
</dependency>

<dependency>
    <groupId>org.bgee.log4jdbc-log4j2</groupId>
    <artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
    <version>1.16</version>
</dependency>

- 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:jdbc="http://www.springframework.org/schema/jdbc"
	xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
	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 https://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">

	<!-- MariaDB JDBC DataSource -->
	<bean class="com.zaxxer.hikari.HikariConfig" id="hikariConfig">
		<property name="driverClassName" value="org.mariadb.jdbc.Driver"/>
		<property name="jdbcUrl" value="jdbc:mariadb://localhost:3306/counsel" />
		<property name="username" value="root" />
		<property name="password" value="1234" />
	</bean>
    
	<bean class="com.zaxxer.hikari.HikariDataSource" id="dataSource" destroy-method="close">
		<constructor-arg ref="hikariConfig" />
	</bean>
	
	<bean class="org.mybatis.spring.SqlSessionFactoryBean" id="sqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="typeAliasesPackage" value="com.crizen.domain" />
	</bean>
	
    <mybatis-spring:scan base-package="com.crizen.mapper" />
</beans>

- log4jdbc.log4j2.properties

log4jdbc.drivers=org.mariadb.jdbc.Driver
log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator

properties는 src/main/resources 하위에 생성

---

2. DB 연동 테스트

1) VO, Mapper 코드

프로젝트 패키지 구조

- Counsel.java

package com.crizen.domain;

import org.apache.ibatis.type.Alias;
import lombok.Data;

@Data
@Alias("counsel")
public class Counsel {
	
	private int counsel_bno;
	private String counsel_title;
	private String counsel_content;
	private String counsel_writer;
	private String counsel_regDate;
}

- BoardMapper.java

package com.crizen.mapper;

import java.util.List;
import org.springframework.stereotype.Repository;
import com.crizen.domain.Counsel;

@Repository
public interface BoardMapper {
	
	List<Counsel> list();
	
}

- 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.crizen.mapper.BoardMapper">
	<select id="list" resultType="counsel">
		SELECT * FROM COUNSEL_INFO ORDER BY 5
	</select>
</mapper>

---

2) Test 코드

테스트 패키지 및 클래스 구조

- DBConnectionTest.java

package com.crizen.test;

import static org.junit.Assert.assertNotNull;

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.crizen.mapper.BoardMapper;

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 DBConnectionTest {
	
	@Setter @Autowired
	private BoardMapper mapper;
	
	@Test
	public void testExist() {
		assertNotNull(mapper);
	}
	
	@Test
	public void testList() {
		mapper.list().forEach(log::info);
	}
}

testExist() 결과 / DB 연동 성공!

testList() 결과 / DB 내용 출력 완료!