#54 Spring Boot MyBatis로 MySQL DB 연동

 2019.07.29

​

 DB 연결을 위한 application.properties

server.port=80
spring.thymeleaf.cache=false
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/DB명?serverTimezone=UTC&characterEncoding=UTF8
spring.datasource.username=DB아이디
spring.datasource.password=DB비밀번호

 

 매핑파일에 기재된 SQL을 호출하기 위한 인터페이스

// MemberMapper.java
 
package kr.or.ksmart.ksmart_layout.mapper;
 
import java.util.List;
 
import org.apache.ibatis.annotations.Mapper;
 
import kr.or.ksmart.ksmart_layout.vo.Member; 
 
@Mapper
public interface MemberMapper {    
    public List<Member> getMemberList();
}

 

 DAO가 DB에서 받아온 데이터를 전달받아 가공하는 Service

// MemberService.java
 
package kr.or.ksmart.ksmart_layout.service;
 
import java.util.List;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
 
import kr.or.ksmart.ksmart_layout.mapper.MemberMapper;
import kr.or.ksmart.ksmart_layout.vo.Member;
 
@Service
public class MemberService { 
 
    @Autowired 
    private MemberMapper memberMapper;
 
    public List<Member> getMemberList() {
        return memberMapper.getMemberList();    
    }
}

 

 SQL 문을 작성하는 Mapper xml, 위치는 Mapper 인터페이스와 같은 패키지

<!-- MemberMapper.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="kr.or.ksmart.ksmart_layout.mapper.MemberMapper">
    <!-- id는 메서드명 -->
    <select id="getMemberList" resultType="kr.or.ksmart.ksmart_layout.vo.Member">
        SELECT       
             m_id AS memberId
            ,m_pw AS memberPw
            ,m_level AS memberLevel
            ,m_name AS memberName
            ,m_email AS memberEmail 
        FROM tb_member; 
    </select>
</mapper>

 

컨트롤러

// MemberController.java
 
package kr.or.ksmart.ksmart_layout.controller;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
 
import kr.or.ksmart.ksmart_layout.service.MemberService;
 
@Controller
public class MemberController { 
 
    @Autowired
    private MemberService memberService;  
 
    @GetMapping("/memberList.bo")
    public String getMemberList(Model model) {
        model.addAttribute("list", memberService.getMemberList());
        return "/member/mlist/memberList";        
    }
}