[Spring] Infearn 스프링 입문3 - (응용)MySQL & Mybatis 사용

* 기존의 수업과 다르게 JPA대신 Mybatis 사용

 

4.1 MySQL 환경 설정하기

1. Mysql 설치하기 (https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.30)
2. MySQL Product Archives 설치하기(MySQL :: Download MySQL Connector/J (Archived Versions)

 

MySQL :: Download MySQL Connector/J (Archived Versions)

3. mysql version 확인 후 gradle.build에 의존성 추가

4. sql 연결확인

package hello.hellospring.domain;

import java.sql.*;

public class sql {
    public static void main(String[] args) {
        Connection c = null;

        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.err.println("JDBC 드라이버를 로드하는데 문제 발생" + e.getMessage());
            e.printStackTrace();
        }

        try {
            c = DriverManager.getConnection("jdbc:mysql://localhost:3306", "root", "toor");
            System.out.println("연결 완료!!!");
        } catch (SQLException e) {
            System.out.println("연결 오류" + e.getMessage());
            e.printStackTrace();
        }

        try {
            if(c != null) {
                c.close();
            }
        } catch (SQLException e) {}
    }
}

5. SQL 테스트 완.

 

4.2 데이터 베이스 활용

1. 데이터베이스 생성.

CREATE DATABASE sample default CHARACTER SET UTF8;

2. 생성 데이터베이스 조회.

SHOW DATABASES;

3. 테이블 생성

create table member(
_id bigint primary key auto_increment, 
name varchar(255)) 
ENGINE=INNODB; DESCRIBE professor;

USE sample;

4. 생성한 테이블을 조회한다.

desc 생성 테이블 조회

5.MySQL server 실행

$ mysql.server start

 

6. application.properties에 아래 내용 추가
데이터 베이스 이름, 비번 아이디 연결

# database
spring.datasource.url=jdbc:mysql://localhost:3306/Databasename?characterEncoding=UTF-8&serverTimezone=Asia/Seoul
spring.datasource.username=root
spring.datasource.password=pw
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

 

 

5. Mybatis 연동

5.1 환경 설정

1. build.gradle에 아래 구문 추가

//    mybatis설정
	implementation 'org.springframework.boot:spring-boot-starter-jdbc'
	implementation 'com.googlecode.json-simple:json-simple:1.1.1'
	implementation 'org.springframework.boot:spring-boot-configuration-processor'
	implementation 'org.mybatis:mybatis:3.5.6'
	implementation 'org.mybatis:mybatis-spring:2.0.6'

2-1. MybatisConfig를 생성

package hello.hellospring.config;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = {"hello.hellospring.mapper"}, sqlSessionFactoryRef = "sqlSessionFactory") //  java 맵퍼interface가 있는 패키지
public class MybatisConfig {

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(dataSource);
        sqlSessionFactoryBean.setTypeAliasesPackage("hello.hellospring.domain,hello.hellospring.model"); // vo, entity object가 있는 패키
        sqlSessionFactoryBean.setConfigLocation(new PathMatchingResourcePatternResolver().getResource("classpath:mybatis-config.xml"));
        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/*-mapper.xml"));
        return sqlSessionFactoryBean.getObject();
    }
}

2-2. mybitis-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>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <setting name="callSettersOnNulls" value="true"/>
        <setting name="jdbcTypeForNull" value="NULL"/>
    </settings>
</configuration>

2-3 application.properties에 해당 구문 추가

spring.devtools.livereload.enabled=true

 

---

[실습 부분]
3. 도메인 작성
Member.java

package hello.hellospring.domain;

public class Member {
    private Long id;
    private String name;


    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;
    }

    @Override
    public String toString() {
        return "Member{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}

4. member-mapper.xml에 SQL문 작성

http://mybatis.org/dtd/mybatis-3-mapper.dtd>">
        select id, name from member where id=#{id,jdbcType=NUMERIC}
        delete from member where id = #{id,jdbcType=NUMERIC}

5. MembermMapper.java에 getMember

package hello.hellospring.mapper;

import hello.hellospring.domain.Member;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

@Repository
@Mapper
public interface MemberMapper {
    Member getMember(Member member);
    void deleteMember(Member member);
}

6. MemberController.java 작성

 @GetMapping("/membertest")
    public String membertest(@RequestParam String id,  Model model){

        // 조회
        Member member2 = new Member();
        member2.setId(Long.valueOf(id));
        Member member = memberMapper.getMember(member2);
        System.out.println("===================="+member.toString());
        System.out.println("===================="+id);

        // 삭제
//        Member member3 = new Member();
//        member3.setId(2l);
//        memberMapper.deleteMember(member3);

        model.addAttribute("member", member);

        return "members/member";
    }

7. 테스트