IntelliJ에서 MySQL을 Spring MyBatis CRUD 하기

JPA를 사용하면 아주 간단하고 좋고 편하겠지만 아직도 MyBatis를 사용하는 방법도 알아야 하기에 한번 정리 해보려 합니다.

아주아주아주아주 간단한 CRUD만을 위한 부분입니다.

 

저는 디비는 잘하는 편이 아니기 때문에 복잡한 매핑은 여기서 다루지 않습니다. :D

 

일단 프로젝트는 이것을 참조 합니다.

2020/01/15 - [Web/Server] - IntelliJ IDEA CE. spring mvc + maven + jetty

IntelliJ IDEA CE. spring mvc + maven + jetty

다른 프로젝트에 이식하는것도 큰 문제는 없습니다.

 

1. servlet-context.xml 파일 수정

다른 파일에 들어가 있을수도 있습니다. 왜냐면 저는 프로젝트 안에 web.xml을 다음과 같이 만들었습니다.

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.0"
         xmlns="http://java.sun.com/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd">
    <servlet>
        <servlet-name>spring</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <init-param>
            <param-name>contextConfigLocation</param-name>
            <param-value>/WEB-INF/servlet-context.xml</param-value>
        </init-param>
        <load-on-startup>1</load-on-startup>
    </servlet>
    <servlet-mapping>
        <servlet-name>spring</servlet-name>
        <url-pattern>/</url-pattern>
    </servlet-mapping>
    <filter>
        <filter-name>encodingFilter</filter-name>
        <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
        <init-param>
            <param-name>encoding</param-name>
            <param-value>UTF-8</param-value>
        </init-param>
    </filter>
    <filter-mapping>
        <filter-name>encodingFilter</filter-name>
        <url-pattern>/*</url-pattern>
    </filter-mapping>
</web-app>

내용중에 servlet-context.xml로 되어 있죠?

이것을 추가하여 줍니다.

    <!-- MySQL dataSource -->
    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://ip:port/scheme"/>
        <property name="username" value="id"/>
        <property name="password" value="password"/>
    </bean>

    <!-- mybatis SqlSessionFactoryBean -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="configLocation" value="classpath:/mybatis-config.xml"/>
        <property name="mapperLocations" value="classpath:/mappers/*.xml"/>
    </bean>

    <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
        <constructor-arg ref="sqlSessionFactory"/>
    </bean>

3번째, 4번째, 5번째 라인의 적당한 부분은 작성하시는 분의 정보로 입력합니다. url, username, password

 

2. pom.xml 수정

		<!-- MyBatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.4</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>2.0.3</version>
        </dependency>

        <!-- MySQL -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.5</version>
        </dependency>

이렇게 되면 MyBatis와 MySQL Connector를 받아 옵니다.

 

3. mapper 파일을 만듭니다.

src > main > resources > mappers 폴더를 만들어 줍니다.

그 안에 파일을 만들어 줍니다. 예를 들어서 member-mappers.xml이라고 하겠습니다.

이것과 mybatis-config.xml 파일을 만들어 줍시다.

자 이렇게 하고 파일의 내용을 넣어줍시다.

mabatis-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>
    <typeAliases>
        <typeAlias alias="userDto" type="dh.test.dto.UserDto"/>
    </typeAliases>
</configuration>

userDto의 이름을 넣어줍니다.

member-mappers.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="dh.test.mapper.UserMapper">
    <select id="select" parameterType="String" resultType="userDto">
        select name, id
        from test
        where id = #{id}
    </select>

    <select id="list" parameterType="String" resultType="userDto">
        select name, id
        from test
    </select>

    <insert id="insert">
        insert into test (name)
        values (#{value})
    </insert>

    <delete id="delete">
        delete from test
        where name = #{name}
    </delete>

    <update id="update" parameterType="dh.test.dto.UserDto">
        update test
        set name = #{name}
        where id = #{id}
    </update>
</mapper>

위에서 부터 seleft, list, insert, delete, update에 대해서 넣어줍니다.

 

4. Controller 만들기

package dh.test.controller;

import dh.test.model.ResponseBase;
import dh.test.model.ResultCode;
import dh.test.service.UserService;
import dh.test.dto.UserDto;
import dh.test.util.ResponseHelper;
import org.mybatis.spring.MyBatisSystemException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

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

@RestController
public class UserController {

    @Autowired
    private UserService userService;

    @RequestMapping(value = "/user/{id}", method = RequestMethod.GET)
    @ResponseBody
    public ResponseEntity<ResponseBase> select(@PathVariable String id) {
        try {
            Map<String, String> map = new HashMap<>();
            UserDto dto = userService.select(id);
            String name = dto.getName();
            if (name == null) {
                name = "";
            }

            map.put("name", name);

            return ResponseHelper.success(map);
        } catch (MyBatisSystemException e) {
            return ResponseHelper.fail(ResultCode.JDBC_CONNECTION);
        } catch (Exception e) {
            return ResponseHelper.fail(e);
        }
    }

    @RequestMapping(value = "/users", method = RequestMethod.GET)
    @ResponseBody
    public ResponseEntity<ResponseBase> list() {
        try {
            List<Map<String, String>> list = new ArrayList<>();

            for (UserDto element : userService.list()) {
                String name = element.getName();
                if (name == null) {
                    name = "";
                }

                Map<String, String> object = new HashMap<>();
                object.put("id", element.getId());
                object.put("name", name);

                list.add(object);
            }

            return ResponseHelper.success(list);
        } catch (MyBatisSystemException e) {
            return ResponseHelper.fail(ResultCode.JDBC_CONNECTION);
        } catch (Exception e) {
            return ResponseHelper.fail(e);
        }
    }

    @RequestMapping(value="/user", method=RequestMethod.POST)
    @ResponseBody
    public ResponseEntity<ResponseBase> insert(String name) {
        try {
            int result = userService.insert(name);

            if (result == 1) {
                return ResponseHelper.success();
            } else {
                return ResponseHelper.fail(ResultCode.DB_FAIL);
            }
        } catch (MyBatisSystemException e) {
            return ResponseHelper.fail(ResultCode.JDBC_CONNECTION);
        } catch (Exception e) {
            return ResponseHelper.fail(e);
        }
    }

    @RequestMapping(value="/user", method=RequestMethod.DELETE)
    @ResponseBody
    public ResponseEntity<ResponseBase> delete(String name) {
        try {
            int result = userService.delete(name);

            if (result == 1) {
                return ResponseHelper.success();
            } else {
                return ResponseHelper.fail(ResultCode.DB_FAIL);
            }
        } catch (MyBatisSystemException e) {
            return ResponseHelper.fail(ResultCode.JDBC_CONNECTION);
        } catch (Exception e) {
            return ResponseHelper.fail(e);
        }
    }

    @RequestMapping(value="/user/{id}", method={RequestMethod.PUT, RequestMethod.PATCH})
    @ResponseBody
    public ResponseEntity<ResponseBase> update(@PathVariable("id") final String id,
                                               @RequestBody final UserDto update) {
        try {
            update.setId(id);

            int result = userService.update(update);
            if (result == 1) {
                return ResponseHelper.success();
            } else {
                return ResponseHelper.fail(ResultCode.DB_FAIL);
            }
        } catch (MyBatisSystemException e) {
            return ResponseHelper.fail(ResultCode.JDBC_CONNECTION);
        } catch (Exception e) {
            return ResponseHelper.fail(e);
        }
    }
}

 

위에서 부터 매핑되도록 슬며시 만들어 주었습니다.

당연히 컴파일 안됩니다.

 

5. UserDao 만들기

package dh.test.dao;

import dh.test.mapper.UserMapper;
import dh.test.dto.UserDto;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class UserDao {

    @Autowired
    private SqlSession sqlSession;

    public UserDto selectUser(final String id) {
        return sqlSession
                .getMapper(UserMapper.class)
                .select(id);
    }

    public List<UserDto> selectUsers() {
        return sqlSession
                .getMapper(UserMapper.class)
                .list();
    }

    public int insertUser(final String name) {
        return sqlSession
                .getMapper(UserMapper.class)
                .insert(name);
    }

    public int delete(String name) {
        return sqlSession
                .getMapper(UserMapper.class)
                .delete(name);
    }

    public int update(UserDto dto) {
        return sqlSession
                .getMapper(UserMapper.class)
                .update(dto);
    }
}

6. UserDto 만들기

package dh.test.dto;

import lombok.Getter;
import lombok.Setter;

import java.io.Serializable;

public class UserDto implements Serializable {

    @Getter
    @Setter
    private String id;

    @Getter
    @Setter
    private String name;
}

7. Mapper 만들기

package dh.test.mapper;

import dh.test.dto.UserDto;

import java.util.List;

public interface UserMapper {
    UserDto select(String id);
    List<UserDto> list();
    int insert(String name);
    int delete(String name);
    int update(UserDto dto);
}

 8. UserService 만들기

package dh.test.service;

import dh.test.dto.UserDto;

import java.util.List;

public interface UserService {
    UserDto select(String id);
    List<UserDto> list();
    int insert(String name);
    int delete(String name);
    int update(UserDto dto);
}

9. UserServiceImpl 만들기

package dh.test.service;

import dh.test.dao.UserDao;
import dh.test.dto.UserDto;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;

    @Override
    public UserDto select(String id) {
        return userDao.selectUser(id);
    }

    @Override
    public List<UserDto> list() {
        return userDao.selectUsers();
    }

    @Override
    public int insert(String name) {
        return userDao.insertUser(name);
    }

    @Override
    public int delete(String name) {
        return userDao.delete(name);
    }

    @Override
    public int update(UserDto dto) {
        return userDao.update(dto);
    }
}

기본적인 부분들은 이렇게 구성이 됩니다. 이걸로 게시판 하나 정도는 만들수 있을겁니다. ^^

 

-----

 

추가 Response 부분

저렇게 해도 에러가 날겁니다. 센스가 있으신분들은 알아서 나머지 부분을 수정하시겠지만... 아무튼 다시 올립니다.

 

10. ResponseBase

package dh.test.model;

import lombok.Getter;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;

public class ResponseBase {

    @Getter
    private String code;

    @Getter
    private String message;

    public ResponseBase() {
        setResultCode(ResultCode.SUCCESS);
    }

    public ResponseBase(ResultCode resultCode) {
        setResultCode(resultCode);
    }

    public ResponseBase(Exception e) {
        this.code = "known";
        this.message = e.getClass().getCanonicalName() + " / " + e.getMessage();
    }

    protected void setResultCode(final ResultCode resultCode) {
        code = resultCode.getCode();
        message = resultCode.getMessage();
    }
}

11. ResponseData

package dh.test.model;

import lombok.Getter;

public class ResponseData extends ResponseBase {

    @Getter
    private Object data = null;

    public ResponseData(Object data) {
        setResultCode(ResultCode.SUCCESS);
        this.data = data;
    }
}

12. ResultCode

package dh.test.model;

import lombok.Getter;
import org.springframework.http.HttpStatus;

public enum ResultCode {
    SUCCESS("0000", "Success"),
    JDBC_CONNECTION("5000", "JDBC connection error"),
    DB_FAIL("5001", "Database operation fail");

    @Getter
    private String code;

    @Getter
    private String message;

    ResultCode(String code, String message) {
        this.code = code;
        this.message = message;
    }
}

대략 그렇습니다...

13. ResponseHelper

빠진 부분이 있다고 그래서 추가하였습니다.

package dh.test.util;

import dh.test.model.ResponseBase;
import dh.test.model.ResponseData;
import dh.test.model.ResultCode;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;

public class ResponseHelper {
    static public ResponseEntity<ResponseBase> success() {
        return getEntity(new ResponseBase());
    }

    static public ResponseEntity<ResponseBase> success(Object data) {
        return getEntity(new ResponseData(data));
    }

    static public ResponseEntity<ResponseBase> fail(ResultCode resultCode) {
        return getEntity(new ResponseBase(resultCode));
    }

    static public ResponseEntity<ResponseBase> fail(Exception e) {
        return getEntity(new ResponseBase(e));
    }

    static public ResponseEntity<ResponseBase> getEntity(ResponseBase responseBase) {
        HttpStatus httpStatus;

        if (ResultCode.SUCCESS.getCode().equals(responseBase.getCode())) {
            httpStatus = HttpStatus.OK;
        } else {
            httpStatus = HttpStatus.INTERNAL_SERVER_ERROR;
        }

        return new ResponseEntity<>(responseBase, httpStatus);
    }
}

Response를 쉽게 하기 위해서 만든 부분입니다.