Spring Boot : MySQL 연동 MyBatis 설정

출처

• [spring] mapper 어노테이션을 통한 springboot, mybatis 세팅하기 : 얼음연못
• [Spring boot] 스프링부트, Mybatis 세팅 및 샘플 예시 : Steve Jang

Maven pom.xml 설정

    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-tx</artifactId>
        <version>5.2.3.RELEASE</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>2.1.1</version>
    </dependency>
    
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.6</version>
    </dependency>
    <!-- mybatis -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.2</version>
    </dependency>
    <!-- mybatis spring -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.3.1</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.4</version>
      <scope>provided</scope>
    </dependency>

application.properties

## MySQL
spring.datasource.driver-class-name=org.gjt.mm.mysql.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test_db?user=user1&useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=false
spring.datasource.username=user1
spring.datasource.password=user1!!

# 패키지 result tpye에 명을 생략할 수 있도록 alias 설정
mybatis.type-aliases-package=com.bluexmas.domain

# model 프로퍼티 camel case 설정
# mybatis.configuration.map-underscore-to-camel-case=true

# Mybatis mapper 위치 설정
mybatis.mapper-locations=classpath:/**/**.xml

Domain

package com.bluexmas.domain;

import java.sql.Date;

import lombok.Data;

@Data
public class Notice {
	
	// pk
	private int notice_no;
	
	private String notice_type;
	private String title;
	private String content;
	private int hit;
	private int reg_no;
	private Date reg_dt;
	private Date upt_dt;
	private String del_yn;
	private Date del_dt;
	
}

Persistence

package com.bluexmas.persistence;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;

import com.bluexmas.domain.Notice;

@Mapper
public interface NoticeMapper {

	public Notice selectNotice(Map<String, Object> params);

	public int insertNotice(Notice notice);

	public int updateNotice(Notice notice);

	public int deleteNotice(Map<String, Object> params);

	public int getCount();

	public List listNotice(Map<String, Object> map);

}

Service

package com.bluexmas.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.bluexmas.domain.Notice;
import com.bluexmas.persistence.NoticeMapper;

@Service
public class NoticeService {

	@Autowired
	private NoticeMapper noticeMapper;

	public Notice selectNotice(int noticeNo) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("notice_no",noticeNo);
		return noticeMapper.selectNotice(params);
	}

	public int insertNotice(Notice notice) {
		return noticeMapper.insertNotice(notice);
	}

	public int updateNotice(Notice notice) {
		return noticeMapper.updateNotice(notice);
	}

	public int deleteNotice(int noticeNo) {
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("notice_no",noticeNo);
		return noticeMapper.deleteNotice(params);
	}

	public int getCount() {
		return noticeMapper.getCount();
	}

	public List listNotice() throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		return noticeMapper.listNotice(params);
	}
}

src\main\mybatis\com\bluexmas\persistence\NoticeMapper.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.bluexmas.persistence.NoticeMapper">

	<!-- selectNotice -->
	<select id="selectNotice" parameterType="map" resultType="com.bluexmas.domain.Notice">
		select *
		  from notice
		 where notice_no = #{notice_no}
	</select>

	<!-- insertNotice -->
	<insert id="insertNotice" parameterType="com.bluexmas.domain.Notice" statementType="PREPARED">
		insert into notice(
		    <trim suffixOverrides=",">
		      <if test="notice_type != null">notice_type ,</if>
		      <if test="title != null">title ,</if>
		      <if test="content != null">content ,</if>
		      <if test="hit != null">hit ,</if>
		      <if test="reg_no != null">reg_no ,</if>
		      <if test="reg_dt != null">reg_dt ,</if>
		      <if test="upt_dt != null">upt_dt ,</if>
		      <if test="del_yn != null">del_yn ,</if>
		      <if test="del_dt != null">del_dt ,</if>
		    </trim>
		    ) values (
		    <trim suffixOverrides=",">
		      <if test="notice_type != null">#{notice_type, jdbcType=VARCHAR} ,</if>
		      <if test="title != null">#{title, jdbcType=VARCHAR} ,</if>
		      <if test="content != null">#{content, jdbcType=VARCHAR} ,</if>
		      <if test="hit != null">#{hit, jdbcType=INTEGER} ,</if>
		      <if test="reg_no != null">#{reg_no, jdbcType=INTEGER} ,</if>
		      <if test="reg_dt != null">#{reg_dt, jdbcType=TIMESTAMP} ,</if>
		      <if test="upt_dt != null">#{upt_dt, jdbcType=TIMESTAMP} ,</if>
		      <if test="del_yn != null">#{del_yn, jdbcType=VARCHAR} ,</if>
		      <if test="del_dt != null">#{del_dt, jdbcType=TIMESTAMP} ,</if>
		    </trim>
		)
		<selectKey keyProperty="notice_no" resultType="Integer">
			SELECT LAST_INSERT_ID()
		</selectKey>
	</insert>

	<!-- updateNotice -->
	<update id="updateNotice" parameterType="com.bluexmas.domain.Notice" statementType="PREPARED">
		update notice
		    <trim prefix="SET" suffixOverrides=",">
		      <if test="notice_type != null">notice_type = #{notice_type, jdbcType=VARCHAR} ,</if>
		      <if test="title != null">title = #{title, jdbcType=VARCHAR} ,</if>
		      <if test="content != null">content = #{content, jdbcType=VARCHAR} ,</if>
		      <if test="hit != null">hit = #{hit, jdbcType=INTEGER} ,</if>
		      <if test="reg_no != null">reg_no = #{reg_no, jdbcType=INTEGER} ,</if>
		      <if test="reg_dt != null">reg_dt = #{reg_dt, jdbcType=TIMESTAMP} ,</if>
		      <if test="upt_dt != null">upt_dt = #{upt_dt, jdbcType=TIMESTAMP} ,</if>
		      <if test="del_yn != null">del_yn = #{del_yn, jdbcType=VARCHAR} ,</if>
		      <if test="del_dt != null">del_dt = #{del_dt, jdbcType=TIMESTAMP} ,</if>
		    </trim>
		 where notice_no = #{notice_no}
	</update>

	<!-- deleteNotice -->
	<delete id="deleteNotice" parameterType="map" statementType="PREPARED">
		delete from notice
		 where notice_no = #{notice_no}
	</delete>

	<!-- getCount -->
	<select id="getCount" resultType="int">
		select count(*)
		  from notice
	</select>

	<!-- listNotice -->
	<select id="listNotice" parameterType="map" resultType="com.bluexmas.domain.Notice">
		select a.*
		  FROM notice a
	</select>

</mapper>

Controller 소스

package com.bluexmas.controller;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.bluexmas.domain.Notice;
import com.bluexmas.service.NoticeService;

@Controller
public class NoticeController {

	@Autowired
	private NoticeService noticeService;
	
	@RequestMapping(value="/notice_list.do", method=RequestMethod.GET)
	public @ResponseBody Map<String, Object> notice_list(ModelMap modelMap) throws Exception {
		//
		Map<String, Object> result = new HashMap<> ();
		
		//
		List<Notice> listNotice = noticeService.listNotice();
		result.put("notice_list", listNotice);
		
		//
		return result;
	}
}

실행