[Spring Boot] MyBatis + Mysql 샘플2

스프링부트에서 MyBatis 샘플 프로그램입니다.

 

1. MySQL DDL & DML 생성

CREATE TABLE MEMBER (ID INT PRIMARY KEY, NAME VARCHAR(50), PHONE VARCHAR(20)); 

INSERT INTO MEMBER (ID, NAME, PHONE) VALUES (1, 'xml', '010-9912-1234'); 
INSERT INTO MEMBER (ID, NAME, PHONE) VALUES (2, 'annotation', '010-2333-4321'); 

mysql> desc MEMBER;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | int(11)     | NO   | PRI | NULL    |       |
| NAME  | varchar(50) | YES  |     | NULL    |       |
| PHONE | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from MEMBER;
+----+------------+---------------+
| ID | NAME       | PHONE         |
+----+------------+---------------+
|  1 | xml        | 010-9912-1234 |
|  2 | annotation | 010-2333-4321 |
+----+------------+---------------+
2 rows in set (0.00 sec)

2. pom.xml 파일에 dependency 추가 

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>2.0.0</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-tomcat</artifactId>
			<scope>provided</scope>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
</dependencies>

3. application.yml 파일 생성

spring:
  datasource:
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://172.21.111.144:3306/test
    username: root
    password: password
logging:
  level:
    #org.springframework.boot: DEBUG
    sample.springboot.mybatis: DEBUG

4. DataBaseConfig.java 파일 생성

package com.example.demo;

import javax.sql.DataSource;

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.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.transaction.annotation.EnableTransactionManagement;

 
@Configuration
@MapperScan(basePackages="com.example.demo.dao")
@EnableTransactionManagement
public class DataBaseConfig {
	
    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        sessionFactory.setMapperLocations(resolver.getResources("classpath:com/example/dao/*.xml"));
        return sessionFactory.getObject();
    }
    
    @Bean
    public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) throws Exception {
      final SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory);
      return sqlSessionTemplate;
    }
}

5. Member.java 파일 생성 

package com.example.demo.model;

public class Member {
	private Long id;
	private String name;
	private String phone;
	
	public Long getId() {
		return id;
	}
	public void setId(Long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	
	@Override
	public String toString() {
		return "Member [id=" + id + ", name=" + name + ", phone=" + phone + "]";
	}
}

6. MemberMapper.java 파일 생성

package com.example.demo.dao;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import com.example.demo.model.Member;

@Mapper
public interface MemberMapper {
	List<Member> findAll();
	Member findById(Long id);
}

7. Member.xml 파일 생성

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.demo.dao.MemberMapper">

	<select id="findAll" resultType="com.example.demo.model.Member">
		SELECT ID, NAME, PHONE FROM MEMBER
	</select>
  
	<select id="findById" resultType="com.example.demo.model.Member">
		SELECT ID, NAME, PHONE FROM MEMBER WHERE ID = #{id}
	</select>
  
</mapper>

8. MemberController.java 파일 생성

package com.example.demo.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.example.demo.dao.MemberMapper;
import com.example.demo.model.Member;

@RestController
public class MemberController {
	
	@Autowired
	MemberMapper memberMapper;
	
	@RequestMapping("members")
	public List<Member> members() {
		return memberMapper.findAll();
	}
	
	@RequestMapping("member/{id}")
	public Member member(@PathVariable("id") Long id) {
		return memberMapper.findById(id);
	}
}

9. 실행 및 결과 확인
http://localhost:8080/members
[{"id":1,"name":"xml","phone":"010-9912-1234"},{"id":2,"name":"annotation","phone":"010-2333-4321"}]

http://localhost:8080/member/1
{"id":1,"name":"xml","phone":"010-9912-1234"}

# 참고
https://github.com/sbcoba/spring-boot-mybatis/   --> spring-boot-mybatis-sample-xml