[AXBoot] 리팩토링 / JPA, QueryDsl, MyBatis (select, insert, update, delete)

파일 이름 리팩토링

파일 - Refactor - Rename으로 기존 YesjmGrid + a .java 파일들을 DB명과 동일하게 EducationYesjm + a 로 변경했다.
BaseService.java의 qYesjmGrid도 마찬가지로 qEducationYesjm으로 변경했다.

JPA

gets 메소드를 모든 데이터를 가져온 다음 필터로 걸러서 걸러진 부분만 화면에 표시하도록 수정하기
리턴할 변수를 생성하고 ArrayList로 초기화, 파라메터에서 받아온 데이터를 변수로 담고, findAll()의 결과값들을 하나의 객체로 받아 for문을 돌린다.
빈 객체가 아니고 파라메터의 데이터와 동일하면 list에 추가한다.
메소드도 항상 리팩토링을 진행하면서 코드를 작성하자. 한번에 깔끔한 코드가 나오지는 않는다.

YesjmGridController.java

    @RequestMapping(method = RequestMethod.GET, produces = APPLICATION_JSON)
    @ApiImplicitParams({
            @ApiImplicitParam(name="companyNm", value="회사명", dataType = "String", paramType = "query"),
            @ApiImplicitParam(name="ceo", value="대표자", dataType = "String", paramType = "query"),
            @ApiImplicitParam(name="bizno", value="사업자번호", dataType = "String", paramType = "query"),
            @ApiImplicitParam(name="useYn", value="사용여부", dataType = "String", paramType = "query"),
    })
    public Responses.ListResponse list(RequestParams<EducationYesjm> requestParams) {
        List<EducationYesjm> list = educationYesjmService.gets(requestParams);
        return Responses.ListResponse.of(list);
    }

EducationYesjmService.java

    public List<EducationYesjm> gets(RequestParams<EducationYesjm> requestParams) {
        List<EducationYesjm> list = this.getFilter(findAll(), requestParams.getString("companyNm",""),1);
        List<EducationYesjm> list2 = this.getFilter(list, requestParams.getString("ceo",""),2);
        List<EducationYesjm> list3 = this.getFilter(list2, requestParams.getString("bizno",""),3);
        List<EducationYesjm> list4 = this.getFilter(list3, requestParams.getString("useYn", ""),4);

        return list4;
    }

    private List<EducationYesjm> getFilter(List<EducationYesjm> sources, String filter, int typ){
        List<EducationYesjm> targets = new ArrayList<EducationYesjm>();
        for (EducationYesjm entity: sources){
            if("".equals(filter)){
                targets.add(entity);
            }else {
                if (typ == 1){
                    if (entity.getCompanyNm().contains(filter))
                        targets.add(entity);
                }
                else if (typ == 2){
                    if (entity.getCeo().contains(filter))
                        targets.add(entity);
                }
                else if (typ == 3){
                    if (entity.getBizno().equals(filter))
                        targets.add(entity);
                }
                else{
                    if (entity.getUseYn().equals(filter))
                        targets.add(entity);
                }
            }
        }
        return targets;
    }

부가적인 조건들을 주려면 jpa는 손을 많이 타야한다.

---

QueryDsl

컨트롤러와 서비스에 queryDsl 메소드를 추가한다.

YesjmGridFormController.java

package edu.axboot.controllers;

import com.chequer.axboot.core.api.response.ApiResponse;
import com.chequer.axboot.core.api.response.Responses;
import com.chequer.axboot.core.controllers.BaseController;
import edu.axboot.domain._education.EducationYesjm;
import edu.axboot.domain._education.EducationYesjmService;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.inject.Inject;
import java.util.List;

@Controller
@RequestMapping(value = "/api/v1/_education/yesjmGridForm")
public class YesjmGridFormController extends BaseController {

    @Inject
    private EducationYesjmService educationYesjmService;


    @RequestMapping(method = RequestMethod.GET, produces = APPLICATION_JSON)
    public Responses.ListResponse list(
            @RequestParam(value = "companyNm", required = false) String companyNm,
            @RequestParam(value = "ceo",required = false) String ceo,
            @RequestParam(value = "bizno",required = false) String bizno,
            @RequestParam(value = "useYn",required = false) String useYn) {
        List<EducationYesjm> list = educationYesjmService.gets(companyNm,ceo,bizno,useYn);
        return Responses.ListResponse.of(list);
    }

    @RequestMapping(value = "/{id}", method = RequestMethod.GET, produces = APPLICATION_JSON)
    public EducationYesjm view(@PathVariable Long id){
        EducationYesjm entity = educationYesjmService.getByOne(id);
        return entity;
    }

    @RequestMapping(method = RequestMethod.POST, produces = APPLICATION_JSON)
    public ApiResponse save(@RequestBody EducationYesjm request) {
        educationYesjmService.persist(request);
        return ok();
    }

    @RequestMapping(value = "/{id}", method = RequestMethod.DELETE, produces = APPLICATION_JSON)
    public ApiResponse delete(@PathVariable Long id){
        educationYesjmService.remove(id);
        return ok();
    }
}

EducationYesjmService.java
QueryDsl은 insert 기능을 지원하지 않는다고 하여 JPA 기본 메소드 save를 사용했다.

package edu.axboot.domain._education;

import com.querydsl.core.BooleanBuilder;
import org.springframework.stereotype.Service;
import edu.axboot.domain.BaseService;
import javax.inject.Inject;
import com.chequer.axboot.core.parameter.RequestParams;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;

@Service
public class EducationYesjmService extends BaseService<EducationYesjm, Long> {
    private EducationYesjmRepository educationYesjmRepository;

    @Inject
    public EducationYesjmService(EducationYesjmRepository educationYesjmRepository) {
        super(educationYesjmRepository);
        this.educationYesjmRepository = educationYesjmRepository;
    }

    @Inject
    private EducationYesjmMapper educationYesjmMapper;

    public List<EducationYesjm> gets(RequestParams<EducationYesjm> requestParams) {
        List<EducationYesjm> list = this.getFilter(findAll(), requestParams.getString("companyNm",""),1);
        List<EducationYesjm> list2 = this.getFilter(list, requestParams.getString("ceo",""),2);
        List<EducationYesjm> list3 = this.getFilter(list2, requestParams.getString("bizno",""),3);
        List<EducationYesjm> list4 = this.getFilter(list3, requestParams.getString("useYn", ""),4);

        return list4;
    }

    private List<EducationYesjm> getFilter(List<EducationYesjm> sources, String filter, int typ){
        List<EducationYesjm> targets = new ArrayList<EducationYesjm>();
        for (EducationYesjm entity: sources){
            if("".equals(filter)){
                targets.add(entity);
            }else {
                if (typ == 1){
                    if (entity.getCompanyNm().contains(filter))
                        targets.add(entity);
                }
                else if (typ == 2){
                    if (entity.getCeo().contains(filter))
                        targets.add(entity);
                }
                else if (typ == 3){
                    if (entity.getBizno().equals(filter))
                        targets.add(entity);
                }
                else{
                    if (entity.getUseYn().equals(filter))
                        targets.add(entity);
                }
            }
        }
        return targets;
    }

    public List<EducationYesjm> getByQueryDsl(RequestParams<EducationYesjm> requestParams) {
        String companyNm = requestParams.getString("companyNm","");
        String ceo = requestParams.getString("ceo","");
        String bizno = requestParams.getString("bizno","");
        String useYn = requestParams.getString("useYn", "");

        BooleanBuilder builder = new BooleanBuilder();

        if (isNotEmpty(companyNm)) {
            builder.and(qEducationYesjm.companyNm.contains(companyNm));
        }
        if (isNotEmpty(ceo)) {
            builder.and(qEducationYesjm.ceo.contains(ceo));
        }
        if (isNotEmpty(bizno)){
            builder.and(qEducationYesjm.bizno.contains(bizno));
        }
        if (isNotEmpty(useYn)){
            builder.and(qEducationYesjm.useYn.eq(useYn));
        }

        List<EducationYesjm> educationYesjmList = select()
                .from(qEducationYesjm)
                .where(builder)
                .orderBy(qEducationYesjm.companyNm.asc())
                .fetch();

        return educationYesjmList;
    }

    public EducationYesjm getOneByQueryDsl(RequestParams<EducationYesjm> requestParams) {
        Long id = requestParams.getLong("id");
        BooleanBuilder builder = new BooleanBuilder();
        builder.and(qEducationYesjm.id.eq(id));
        EducationYesjm educationYesjm = select().from(qEducationYesjm).where(builder).fetchOne();
        return educationYesjm;
    }

    @Transactional
    public void saveByQueryDsl(List<EducationYesjm> request) {
        for (EducationYesjm educationYesjm :request) {
            if (educationYesjm.isCreated()) {
                save(educationYesjm);
            } else if (educationYesjm.isModified()) {
                update(qEducationYesjm)
                        .set(qEducationYesjm.companyNm, educationYesjm.getCompanyNm())
                        .set(qEducationYesjm.ceo, educationYesjm.getCeo())
                        .set(qEducationYesjm.bizno, educationYesjm.getBizno())
                        .set(qEducationYesjm.tel, educationYesjm.getTel())
                        .set(qEducationYesjm.email, educationYesjm.getEmail())
                        .set(qEducationYesjm.useYn, educationYesjm.getUseYn())
                        .where(qEducationYesjm.id.eq(educationYesjm.getId()))
                        .execute();
            } else if (educationYesjm.isDeleted()){
                delete(qEducationYesjm)
                        .where(qEducationYesjm.id.eq(educationYesjm.getId()))
                        .execute();
            }
        }
    }

    public List<EducationYesjm> getByMyBatis(RequestParams<EducationYesjm> requestParams) {
        EducationYesjm educationYesjm = new EducationYesjm();
        educationYesjm.setCompanyNm(requestParams.getString("companyNm", ""));
        educationYesjm.setCeo(requestParams.getString("ceo",""));
        educationYesjm.setBizno(requestParams.getString("bizno",""));
        educationYesjm.setUseYn(requestParams.getString("useYn",""));

        List<EducationYesjm> educationYesjmList = this.educationYesjmMapper.getByMyBatis(educationYesjm);

        return educationYesjmList;
    }

    public List<EducationYesjm> gets(String companyNm, String ceo, String bizno, String useYn) {
        BooleanBuilder builder = new BooleanBuilder();

        if (isNotEmpty(companyNm)) {
            builder.and(qEducationYesjm.companyNm.like("%" + companyNm + "%"));
        }
        if (isNotEmpty(ceo)) {
            builder.and(qEducationYesjm.ceo.contains(ceo));
        }
        if (isNotEmpty(bizno)){
            builder.and(qEducationYesjm.bizno.contains(bizno));
        }
        if (isNotEmpty(useYn)){
            builder.and(qEducationYesjm.useYn.eq(useYn));
        }

        List<EducationYesjm> educationYesjmList = select()
                .from(qEducationYesjm)
                .where(builder)
                .orderBy(qEducationYesjm.companyNm.asc())
                .fetch();

        return educationYesjmList;
    }

    public EducationYesjm getByOne(Long id) {
        BooleanBuilder builder = new BooleanBuilder();
        builder.and(qEducationYesjm.id.eq(id));
        EducationYesjm educationYesjm = select().from(qEducationYesjm).where(builder).fetchOne();
        return educationYesjm;
    }

    @Transactional
    public void persist(EducationYesjm request) {
        if (request.getId() == null || request.getId() == 0) {
            save(request);
        }else {
            update(qEducationYesjm)
                    .where(qEducationYesjm.id.eq(request.getId()))
                    .set(qEducationYesjm.companyNm, request.getCompanyNm())
                    .set(qEducationYesjm.ceo, request.getCeo())
                    .set(qEducationYesjm.bizno, request.getBizno())
                    .set(qEducationYesjm.tel, request.getTel())
                    .set(qEducationYesjm.zip, request.getZip())
                    .set(qEducationYesjm.address, request.getAddress())
                    .set(qEducationYesjm.addressDetail, request.getAddressDetail())
                    .set(qEducationYesjm.email, request.getEmail())
                    .set(qEducationYesjm.remark, request.getRemark())
                    .set(qEducationYesjm.useYn, request.getUseYn())
                    .execute();
        }
    }

    @Transactional
    public void remove(Long id) {
        BooleanBuilder builder = new BooleanBuilder();
        builder.and(qEducationYesjm.id.eq(id));
        delete(qEducationYesjm).where(builder).execute();
    }
}

eq는 입력받은 데이터와 db의 데이터가 정확하게 일치할때, contains와 like는 입력받은 데이터가 포함되는 데이터를 builder에 추가하도록 한다.

일부 컬럼만 수정할때는 QueryDsl이 유용하고, 전체 컬럼을 수정할때는 JPA가 훨씬 간결하다.

단건의 insert, update, delete 에는 트랜잭션을 안걸기도 한다. 하지만 여러 건의 insert, update, delete를 할 때에는 꼭 트랜잭션을 걸어주어야 한다. 물론 단건의 경우도 습관적으로 트랜잭션을 거는것이 좋다.
Transactional 정리 및 예제

---

MyBatis

권장하지는 않지만 아직도 현직에서 많은 부분을 차지하는 MyBatis. 화면단과 연결하지는 않을거지만 아직 많이 사용되기 때문에 방법을 알아두자.

YesjmGridFormMybatisController.java

package edu.axboot.controllers;

import com.chequer.axboot.core.api.response.ApiResponse;
import com.chequer.axboot.core.api.response.Responses;
import com.chequer.axboot.core.controllers.BaseController;
import edu.axboot.domain._education.EducationYesjm;
import edu.axboot.domain._education.EducationYesjmService;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.inject.Inject;
import java.util.List;

@Controller
@RequestMapping(value = "/api/v1/_education/yesjmGridForm/myBatis")
public class YesjmGridFormMybatisController extends BaseController {

    @Inject
    private EducationYesjmService educationYesjmService;

    @RequestMapping(method = RequestMethod.GET, produces = APPLICATION_JSON)
    public Responses.ListResponse list(
            @RequestParam(value = "companyNm", required = false) String companyNm,
            @RequestParam(value = "ceo",required = false) String ceo,
            @RequestParam(value = "bizno",required = false) String bizno,
            @RequestParam(value = "useYn",required = false) String useYn) {
        List<EducationYesjm> list = educationYesjmService.getsByMyBatis(companyNm,ceo,bizno,useYn);
        return Responses.ListResponse.of(list);
    }

    @RequestMapping(value = "/{id}", method = RequestMethod.GET, produces = APPLICATION_JSON)
    public EducationYesjm view(@PathVariable Long id){
        EducationYesjm entity = educationYesjmService.getOneByMyBatis(id);
        return entity;
    }

    @RequestMapping(method = RequestMethod.POST, produces = APPLICATION_JSON)
    public ApiResponse save(@RequestBody EducationYesjm request) {
        educationYesjmService.saveByMybatis(request);
        return ok();
    }

    @RequestMapping(value = "/{id}", method = RequestMethod.DELETE, produces = APPLICATION_JSON)
    public ApiResponse delete(@PathVariable Long id){
        educationYesjmService.deleteByMybatis(id);
        return ok();
    }

}

EducationYesjmService.java

    //MyBatis
    public List<EducationYesjm> getsByMyBatis(String companyNm, String ceo, String bizno, String useYn) {
        HashMap<String, String> params = new HashMap<String, String>();
        params.put("companyNm", companyNm);
        params.put("ceo", ceo);
        params.put("bizno", bizno);
        params.put("useYn", useYn);

        List<EducationYesjm> list = educationYesjmMapper.select(params);

        return list;
    }

    public EducationYesjm getOneByMyBatis(Long id) {
        return educationYesjmMapper.selectOne(id);
    }

    @Transactional
    public void saveByMybatis(EducationYesjm request) {
        if (request.getId() == null || request.getId() == 0) {
            educationYesjmMapper.insert(request);
        }else {
            educationYesjmMapper.update(request);
        }
    }

    @Transactional
    public void deleteByMybatis(Long id) {
        educationYesjmMapper.delete(id);
    }

EducationYesjmMapper.java
Mapper.java에 선언된 메소드들은 Mapper.xml에 무조건 같은이름으로 두 곳에 모두 존재하고 같은 패키지 경로를 가져야 에러 없이 동작한다. Mapper.xml에서 해당 메소드 이름을 id로 받지만 Mapper.java에는 선언되어 있지 않다면 실행조차 할 수 없다. 당연하게도 반대의 경우는 동작이 안된다.

package edu.axboot.domain._education;

import com.chequer.axboot.core.mybatis.MyBatisMapper;

import java.util.HashMap;
import java.util.List;

public interface EducationYesjmMapper extends MyBatisMapper{

//    List<EducationYesjm> getByMyBatis(EducationYesjm educationYesjm);

    List<EducationYesjm> select(HashMap<String,String> params);
    EducationYesjm selectOne(Long id);

    int insert(EducationYesjm request);
    int update(EducationYesjm request);
    int delete(Long id);
}

EducationYesjmMapper.xml
ModelExtractor에서 mybatis 코드를 가져다 수정한다. 현재 프로젝트에서 BaseService를 참조하고 있기 때문에 insert문의 createAt~ 부분은 지우고 작성했다. JPA 기반으로 하면 작성시간, 수정시간 등을 자동으로 작성해준다.

<?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="edu.axboot.domain._education.EducationYesjmMapper">

    <select id="select" resultType="educationYesjm" parameterType="HashMap" statementType="PREPARED">
        SELECT
            ID AS id,
            COMPANY_NM AS companyNm,
            CEO AS ceo,
            BIZNO AS bizno,
            TEL AS tel,
            ZIP AS zip,
            ADDRESS AS address,
            ADDRESS_DETAIL AS addressDetail,
            EMAIL AS email,
            REMARK AS remark,
            USE_YN AS useYn,
            CREATED_AT AS createdAt,
            CREATED_BY AS createdBy,
            UPDATED_AT AS updatedAt,
            UPDATED_BY AS updatedBy
        FROM
            EDUCATION_YESJM
        <where>
            <if test="companyNm != null and companyNm != ''">
                AND COMPANY_NM LIKE '%' || #{companyNm} || '%'
            </if>
            <if test="ceo != null and ceo != ''">
                AND CEO LIKE '%' || #{ceo} || '%'
            </if>
            <if test="bizno != null and bizno != ''">
                AND BIZNO LIKE '%' || #{bizno} || '%'
            </if>
            <if test="useYn != null and useYn != ''">
                AND USE_YN = #{useYn}
            </if>
        </where>
    </select>
    <select id="selectOne" resultType="educationYesjm" parameterType="educationYesjm" statementType="PREPARED">
        SELECT
            ID AS id,
            COMPANY_NM AS companyNm,
            CEO AS ceo,
            BIZNO AS bizno,
            TEL AS tel,
            ZIP AS zip,
            ADDRESS AS address,
            ADDRESS_DETAIL AS addressDetail,
            EMAIL AS email,
            REMARK AS remark,
            USE_YN AS useYn,
            CREATED_AT AS createdAt,
            CREATED_BY AS createdBy,
            UPDATED_AT AS updatedAt,
            UPDATED_BY AS updatedBy
        FROM
            EDUCATION_YESJM
        WHERE
            ID = #{id}
    </select>
    <insert id="insert" parameterType="educationYesjm" statementType="PREPARED">
        INSERT INTO EDUCATION_YESJM (
        COMPANY_NM,
        CEO,
        BIZNO,
        TEL,
        ZIP,
        ADDRESS,
        ADDRESS_DETAIL,
        EMAIL,
        REMARK,
        USE_YN,
        ) VALUES (
        #{companyNm},
        #{ceo},
        #{bizno},
        #{tel},
        #{zip},
        #{address},
        #{addressDetail},
        #{email},
        #{remark},
        #{useYn},
        )
    </insert>
    <update id="update" parameterType="educationYesjm" statementType="PREPARED">
        UPDATE EDUCATION_YESJM
        SET
        ID = #{id},
        COMPANY_NM = #{companyNm},
        CEO = #{ceo},
        BIZNO = #{bizno},
        TEL = #{tel},
        ZIP = #{zip},
        ADDRESS = #{address},
        ADDRESS_DETAIL = #{addressDetail},
        EMAIL = #{email},
        REMARK = #{remark},
        USE_YN = #{useYn},
        CREATED_AT = #{createdAt},
        CREATED_BY = #{createdBy},
        UPDATED_AT = #{updatedAt},
        UPDATED_BY = #{updatedBy}
        WHERE
        ID = #{id}

    </update>
    <delete id="delete" parameterType="educationYesjm" statementType="PREPARED">
        DELETE FROM
            EDUCATION_YESJM
        WHERE
            ID = #{id}
    </delete>
</mapper>

화면별로 대응하는 것은 컨트롤러에 작성, 모델은 하나의 중심에서 처리한다.