[spring boot 2.1.7] mybatis - mysql 5.7 연동

- [spring boot 2.1.7] 개발 준비   - [spring boot 2.1.7] profile 및 logback 설정   - [spring boot 2.1.7] JSP 설정   - [spring boot 2.1.7] thymelef 설정   - [spring boot 2.1.7] mybatis - mysql 5.7 연동   - [spring boot 2.1.7] hibernate - mysql 5.7 연동 설정

 

소스 : https://github.com/yamoe/spring-boot-example/tree/master/05.mybatis-mysql/arti

 

 

* mysql 5.7 설치 및 준비 (centos)

> wget --no-check-certificate  https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar
> tar xvf mysql-5.7.22-1.el7.x86_64.rpm-bundle.tar
> sudo yum -y localinstall mysql-community-common* mysql-community-libs* mysql-community-client* mysql-community-server-5*

# listen port : 3306
> sudo systemctl start mysqld

# 임시 비밀번호 확인 (JRpdip*S:6a#)
> grep 'temporary password' /var/log/mysqld.log

# root 비밀번호 변경
> mysql -uroot -pJRpdip*S:6a#
mysql> set password = password('Aaaa123$');

# db, user 생성
> create database testdb;
> create user 'user1'@'%' identified by 'user1A!@#';
> grant all privileges on testdb.* to 'user1'@'%';
> flush privileges;

# table, data 생성
> use testdb;
> create table test (
	id bigint unsigned not null auto_increment,
	str varchar(255) not null,
	date datetime not null default current_timestamp,
	primary key (id)
  );
> insert into test (str) values ('AA1');
> insert into test (str) values ('AA2');
> insert into test (str) values ('AA3');
> insert into test (str) values ('AA4');
> insert into test (str) values ('AA5');

 

- mysql clinet tool : sqlyog

  https://github.com/webyog/sqlyog-community/wiki/Downloads

 

- 참고 : 

  https://www.lesstif.com/pages/viewpage.action?pageId=24445108

 

 

* Spring Boot 연동 준비

 

- build.gradle 추가

implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter'
implementation 'org.springframework.boot:spring-boot-starter-jdbc'
implementation 'mysql:mysql-connector-java'

 

- 연결 설정 파일 작성

src/main/srcources/env 의 dev, alpha, prod 디렉토리에 각각 db.yml 작성

spring:
  mysql:
    datasource:
      hikari:
        jdbc-url: jdbc:mysql://192.168.56.101:3306/testdb?useSSL=false&characterEncoding=UTF-8&serverTimezone=UTC
        username: user1
        password: user1A!@#
        driver-class-name: com.mysql.cj.jdbc.Driver
        minimum-idle: 1
        maximum-pool-size: 10
        connection-timeout: 20000
        idle-timeout: 300000
        max-lifetime: 1200000
        auto-commit: true
        connection-test-query: SELECT 1 FROM DUAL

 

- yml 읽을 수 있도록 yaml properties source factory 작성

아래 글데로 package com.example.arti.config.yaml 에 작성.

https://kingbbode.tistory.com/39

 

 

* 소스 작성

- DB 연결용 annotation 클래스 작성

<MysqlConnectionMapper.java>

package com.example.arti.config;

import org.springframework.stereotype.Component;

import java.lang.annotation.*;

/**
 * DB 연결 annotation class
 */
@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Component
public @interface MysqlConnectionMapper {
    String value() default "";
}

 

 

- database config bean 작성

<MysqlDatabaseConfig.java>

package com.example.arti.config;

import com.example.arti.config.yaml.YamlPropertySourceFactory;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;

@Configuration
@MapperScan(value = "com.example.arti.dao", annotationClass = MysqlConnectionMapper.class, sqlSessionFactoryRef = "mysqlSqlSessionFactory")
@EnableTransactionManagement
@PropertySource(value = {
        "classpath:/db.yml"
}, ignoreResourceNotFound = true, factory = YamlPropertySourceFactory.class)
public class MysqlDatabaseConfig {

    @Primary
    @Bean(name = "mysqlDataSource", destroyMethod = "close")
    @ConfigurationProperties(prefix = "spring.mysql.datasource.hikari")
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().type(HikariDataSource.class).build();
    }

    @Primary
    @Bean(name = "mysqlSqlSessionFactory")
    public SqlSessionFactory mysqlSqlSessionFactory(@Qualifier("mysqlDataSource") DataSource mysqlDataSource, ApplicationContext applicationContext) throws Exception {
        SqlSessionFactoryBean factory = new SqlSessionFactoryBean();
        factory.setDataSource(mysqlDataSource);
        factory.setMapperLocations(applicationContext.getResources("classpath*:/mybatis/mysql/**/*.xml"));
        return factory.getObject();
    }

    @Primary
    @Bean(name = "mysqlSqlSessionTemplate")
    public SqlSessionTemplate mysqlSqlSessionTemplate(SqlSessionFactory mysqlSqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(mysqlSqlSessionFactory);
    }

}

 

 

- 테스트를 위한 controller 작성

<MysqlController.java>

package com.example.arti.controller;

import com.example.arti.service.MysqlTestService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
@RequestMapping(value = "mysql")
public class MysqlController {

    @Autowired
    private MysqlTestService svc;

    @RequestMapping("/select")
    public @ResponseBody String select() throws Exception {
        return svc.select().toString();
    }

}

 

 

- service 작성

<MysqlTestService.java>

package com.example.arti.service;

import com.example.arti.vo.MysqlTestVO;
import java.util.List;

public interface MysqlTestService {

    List<MysqlTestVO> select() throws Exception;
}

 

 

<MysqlTestServiceImpl.java>

package com.example.arti.service;

import com.example.arti.dao.MysqlTestDAO;
import com.example.arti.vo.MysqlTestVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;


@Service
public class MysqlTestServiceImpl implements MysqlTestService {

    @Autowired
    private MysqlTestDAO dao;

    public List<MysqlTestVO> select() throws Exception {
        return dao.select();
    }

}

 

 

- DAO (mapper) 작성

<MysqlTestDAO.java>

package com.example.arti.dao;

import com.example.arti.config.MysqlConnectionMapper;
import com.example.arti.vo.MysqlTestVO;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Mapper
@Repository
@MysqlConnectionMapper
public interface MysqlTestDAO {
    List<MysqlTestVO> select() throws Exception;
}

 

 

- Value Object 작성 : row data 용

<MysqlTestVO.java>

package com.example.arti.vo;


import lombok.Builder;
import lombok.Data;
import java.sql.Timestamp;

@Data
@Builder
public class MysqlTestVO {
    public Long id;
    public String str;
    public Timestamp date;
}

 

 

- mybatis xml 작성

<src/main/resources/mybatis/mysql/test.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.example.arti.dao.MysqlTestDAO">

    <select id="select" resultType="com.example.arti.vo.MysqlTestVO">
        select * from test;
    </select>

</mapper>

 

 

* 실행

http://localhost:9999/mysql/select