Maria DB - DB 생성, 테이블 생성, Spring Boot 연동

소스코드 : https://github.com/HyeongJunMin/springboot/tree/master/MariaDBExam

설치방법 : https://dotheright.tistory.com/102?category=790771

개발환경 : MariaDB(10.2.14), Connector(2.2.3)

참고페이지 : https://taetaetae.github.io/2019/04/21/spring-boot-mybatis-mysql-xml/

1. DB 생성, 테이블 생성, 사용자 등록

2. Spring Boot 연동

3. 문제 해결

1. DB 생성

---

1. HeidiSQL

  > DB 생성 : 좌측 빈곳에 오른클릭 -> 새로 생성 -> 데이터베이스 -> 이름입력 -> 확인

  > 사용자 생성 : DB클릭(선택) -> 상단 사람모양 아이콘 클릭 -> 추가

  > 생성한 사용자로 접속 : 좌측 세션관리자표시 클릭 -> 신규 -> 사용자/암호 입력

2. Spring Boot 연동

---

1. pom.xml

<!-- DB관련, boot mybatis랑 mysql-connector만 위에 있음 -->
<!-- H2 for oracle db connection -->
<!-- https://mvnrepository.com/artifact/com.h2database/h2 -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>
<!-- maria db 관련 디펜던시 -->
<dependency>
          <groupId>org.mariadb.jdbc</groupId>
          <artifactId>mariadb-java-client</artifactId>
      </dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-configuration-processor</artifactId>
    <optional>true</optional>
</dependency>

2. application.yml

#Maria DB Exam Project
server:
  port: 18088
#JSP config
  servlet:
    jsp:
      init-parameters:
        development: true
#custume error page
  error:
    whitelabel:
      enabled: false
 
#db config
spring:
  mvc:
    view:
      prefix: /WEB-INF/views/
      suffix: .jsp
  datasource:
    hikari:
      jdbc-url: jdbc:mysql://localhost:3306/bmarket?characterEncoding=UTF-8&serverTimezone=UTC
      pool-name: hikari-cp
      maximum-pool-size: 30
      minimum-idle: 2
      driver-class-name: com.mysql.cj.jdbc.Driver
      username: 
      password: 

3. MariaDBConfiguration.java

package com.mde.config;
 
import javax.sql.DataSource;
 
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
 
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
 
@Configuration
@PropertySource("classpath:/application.yml")
public class MariaDBConfiguration {
 
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    public HikariConfig hikariConfig() {
        return new HikariConfig();
    }
 
    @Bean
    public DataSource dataSource() {
        DataSource dataSource = new HikariDataSource(hikariConfig());
        return dataSource;
    }    
    
    @Autowired
    private ApplicationContext applicationContext;
 
    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setMapperLocations(applicationContext.getResources("classpath:/mapper/**/*.xml"));
        return sqlSessionFactoryBean.getObject();
    }
 
    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }    
}

4. TestMapper.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.mde.app.common.dao"> -->
<mapper namespace="common">
    <select id="getList" resultType="com.mde.app.common.model.TestDTO">
        SELECT * FROM MEMBER_MHJ
    </select>
</mapper>

5. 프로젝트 구조

3. 문제해결

---