시골청년

먼저 mysql이 설치되어 있어야한다.
리눅스에서 docker container로 mysql을 설치하는 방법이다.
https://palyoung.tistory.com/76

1. pom.xml 파일에 dependency 추가

<dependency>
	<groupId>org.mybatis</groupId>
	<artifactId>mybatis</artifactId>
	<version>3.3.0</version>
</dependency>

<dependency>
	<groupId>org.mybatis</groupId>
	<artifactId>mybatis-spring</artifactId>
	<version>1.2.3</version>
</dependency>

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

2. application.properties 파일에 아래 내용 추가
db id, pw는 설치하신 MySQL 정보 입력

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://172.21.111.144:3306/test
spring.datasource.username=root
spring.datasource.password=password

3. 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")
@EnableTransactionManagement
public class DataBaseConfig {
	
	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;
	    }
	}
}

4. DbController.java 파일 추가

package com.example.demo.controller;

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

import com.example.demo.service.DbService;

@RestController
public class DbController {
	
    @Autowired
    DbService dbService;
     
    @RequestMapping("/")
    public @ResponseBody String root_test() throws Exception{  
        return "Hello World";
    }
 
    @RequestMapping("/now")
    public @ResponseBody String now() throws Exception{
        return dbService.getDual();
    }
}

5. DbService.java 파일 추가

package com.example.demo.service;

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

import com.example.demo.dao.DbMapper;

@Service
public class DbService {
    @Autowired
    DbMapper dbMapper;
 
    /* select dual */
	public String getDual() throws Exception{
	    return dbMapper.getDual();
	}
}

6. DbMapper.java 파일 추가

package com.example.demo.dao;

public interface DbMapper {
    /* DB Select  */
    public String getDual() throws Exception;
}

7. Db.xml 파일 추가
src/main/resources/com/exmaple/dao/Db.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.example.demo.dao.DbMapper">
 
    <!-- /* select dual */ -->
    <select id="getDual" resultType="java.lang.String">
        SELECT NOW() FROM DUAL
    </select>
 
</mapper>

8. 웹브라우져 확인
http://localhost:8080/now

2019-03-29 02:27:57

1. 도커 이미지를 통해 mysql 컨테이너 생성
$ docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=password --name mysql_test mysql

-p 3306:3306 : 호스트의 3306포트와 컨테이너의 3306포트를 연결한다. 즉 호스트에 3306포트 접근이 발행하면 해당 컨테이너에 접속이 된다.
-e MYSQL_ROOT_PASSWORD=password : 컨테이너를 생성하면서 환경변수를 지정한다. root계정의 비밀번호를 설정한다.
-name mysql_test : 컨테이너의 이름은 mysql_test로 지정한다.

2. mysql 컨네이너 접속
$ docker exec -it mysql_test bash

3. mysql 접속 

docker run에서 설정한 root 계정의 password를 입력하면 mysql 서버에 접속

$ root@03e1724628e8:/# mysql -u root -p
Enter password: password 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.15 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>

# DAO(Data Access Object)
- Database의 data에 접근을 위한 객체
- Database에 접근을 하기위한 로직과 비즈니스 로직을 분리하기 위해서 사용
- DB를 사용해 데이터를 조화하거나 조작하는 기능을 전담하도록 만든 오브젝트
- Service와 DB를 연결하는 고리의 역할을 한다
- DAO의 경우는 DB와 연결할 Connection 까지 설정되어 있는 경우가 많음

# DAO 클래스

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestDao {
    public void add(TestDto dto) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "root");

        PreparedStatement preparedStatement = connection.prepareStatement("insert into users(id,name,password) value(?,?,?)");

        preparedStatement.setString(1, dto.getName());
        preparedStatement.setInt(2, dto.getValue());
        preparedStatement.setString(3, dto.getData());
        preparedStatement.executeUpdate();
        preparedStatement.close();
        
        connection.close();
    }
}

# DTO((Data Transfer Object)
- DB에서 데이터를 얻어 Service나 Controller 등으터 보낼 때 사용하는 객체
- 로직을 갖고 있지 않는 순수한 데이터 객체이며 속성과 그 속성에 접근하기 위한 getter, setter 메소드만 가진 클래스

# DTO 클래스

public class TestDto {

    private String name;
    private int value;
    private String data;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getValue() {
        return value;
    }

    public void setValue(int value) {
        this.value = value;
    }

    public String getData() {
        return data;
    }

    public void setData(String data) {
        this.data = data;
    }
}

# VO(Value Object) vs DTO
- VO는 DTO와 동일한 개념이지만 read only 속성을 갖는다.
- VO는 특정한 비즈니스 값을 담는 객체이고, DTO는 Layer간의 통신 용도로 오고가는 객체를 말한다.

# 참고 사이트
https://jungwoon.github.io/common%20sense/2017/11/16/DAO-VO-DTO/
https://gmlwjd9405.github.io/2018/12/25/difference-dao-dto-entity.html
https://lemontia.tistory.com/591
https://lazymankook.tistory.com/30