(Spring) 2. MariaDB와 Mybatis 연동

• 참고: https://devfunpj.tistory.com/6?category=859537

1. MariaDB와 MySQL Workbench 설치

• https://javaplant.tistory.com/31

2. Spring, MariaDB, MyBatis 연동

1. pom.xml 수정
• MariaDB, Mybatis 관련 dependency 추가

• <!-- DB -->
  <!-- Maria DB -->
  <dependency>
      <groupId>org.mariadb.jdbc</groupId>
      <artifactId>mariadb-java-client</artifactId>
      <version>2.0.3</version>
  </dependency>
   
  <!-- DBCP 데이터베이스 풀 커넥션 -->
  <dependency>
      <groupId>commons-dbcp</groupId>
      <artifactId>commons-dbcp</artifactId>
      <version>1.4</version>
  </dependency>
   
  <!-- Spring JDBC -->
  <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>4.3.9.RELEASE</version>
  </dependency>
   
  <!-- Mybatis -->
  <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.4</version>
  </dependency>
   
  <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.3.1</version>
  </dependency>

2. root-context.xml 수정
1. datasource 추가
2. SqlSessionFactoryBean 추가
1. MariaDB 설정 기증을 사용하도록 설정
3. SqlSessionTemplate 추가
1. 트랜잭션 관리와 쓰레드 처리
2. DB 연결 및 종료를 관리하는 영역

3. <!-- Root Context: defines shared resources visible to all other web components -->
   <bean id="dataSource"
         class="org.springframework.jdbc.datasource.DriverManagerDataSource">
       <property name="driverClassName"
                 value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property>
       <property name="url"
                 value="jdbc:log4jdbc:mariadb://127.0.0.1:3306/databaseName" />
       <property name="username" value="username" />
       <property name="password" value="password" />
   </bean>
   <bean id="sqlSessionFactory"
         class="org.mybatis.spring.SqlSessionFactoryBean">
       <property name="dataSource" ref="dataSource"></property>
       <property name="configLocation"
                 value="classpath:/mybatis/mybatis-config.xml"></property>
       <property name="mapperLocations"
                 value="classpath*:/mybatis/sql/*.xml"></property>
   </bean>
   <bean id="sqlSession"
         class="org.mybatis.spring.SqlSessionTemplate">
       <constructor-arg name="sqlSessionFactory"
                        ref="sqlSessionFactory"></constructor-arg>
   </bean>
    
   <!-- <mybatis-spring:scan base-package="com.jremind.dao"/ -->
   <context:component-scan
                           base-package="com.jremind.dao"></context:component-scan>
   <context:component-scan
                           base-package="com.jremind.service"></context:component-scan>
    

3. Mybatis 관련 폴더 및 파일 생성
• 
1. mybatis-config.xml 생성

• <?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE configuration
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-config.dtd">
      
  <configuration>
      <typeAliases>
          <typeAlias type="com.jremind.vo.WineVO" alias="WineVO"/>
      </typeAliases>
  </configuration>

2. test.xml 생성
• mybatis-config.xml에서 typeAlise에 등록하지 않았으면 경로 입력(com.jremind.vo.WineVO)
• 쿼리 결과를 resultType으로 데이터를 담겠다는 의미

• <?xml version="1.0" encoding="UTF-8" ?>
  <!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   
  <mapper namespace="com.jremind.mybatis.sql.test">    
      <select id="selectWine" resultType="WineVO">
          SELECT PRODUCT_SEQ, FIXED_ACIDITY, VOLATILE_ACIDITY, CITRIC_ACID, RESIDUAL_SUGAR, CHLORIDES, FREE_SULFUR_DIOXIDE, TOTAL_SULFUR_DIOXIDE, DENSITY, PH, SULPHATES ,ALCOHOL, QUALITY FROM PRODUCT_QUALITY
      </select> 
  </mapper>

3. log4jdbc 설정

1. pom.xml 수정
• log4jdbc관련 dependency 추가

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

2. log4jdbc 관련 파일 생성
• 
1. log4j.xml 생성
   

• <?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN" "log4j.dtd">
  <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
   
      <!-- Appenders -->
      <appender name="console" class="org.apache.log4j.ConsoleAppender">
          <param name="Target" value="System.out" />
          <layout class="org.apache.log4j.PatternLayout">
              <param name="ConversionPattern" value="%-5p: %c - %m%n" />
          </layout>
      </appender>
      
      <!-- Application Loggers -->
      <logger name="com.jremind.visualization">
          <level value="info" />
      </logger>
      
      <!-- 3rdparty Loggers -->
      <logger name="org.springframework.core">
          <level value="info" />
      </logger>
      
      <logger name="org.springframework.beans">
          <level value="info" />
      </logger>
      
      <logger name="org.springframework.context">
          <level value="info" />
      </logger>
   
      <logger name="org.springframework.web">
          <level value="info" />
      </logger>
   
      <!-- Root Logger -->
      <root>
          <priority value="info" />
          <appender-ref ref="console" />
      </root>
  </log4j:configuration>

• Root Logger에서 warn을 info로 변경
2. log4jdbc.log4j2.properties 생성

• log4jdbc.drivers=org.mariadb.jdbc.Driver
  log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator
  log4jdbc.dump.sql.maxlinelength=0

3. logback.xml 생성

• <?xml version="1.0" encoding="UTF-8"?>
  <configuration>
      <include resource="org/springframework/boot/logging/logback/base.xml"/>
      
      <!-- log4jdbc-log4j2 -->
      <logger name="jdbc.sqlonly" level="DEBUG"/>
      <logger name="jdbc.sqltiming" level="INFO"/>
      <logger name="jdbc.audit" level="WARN"/>
      <logger name="jdbc.resultset" level="ERROR"/>
      <logger name="jdbc.resultsettable" level="ERROR"/>
      <logger name="jdbc.connection" level="INFO"/>
  </configuration>