MySQL Provider 에서 사용하는 유틸 만들기

 

MySQL을 사용하면서 동적 쿼리를 생성하여 사용하는 경우가 더럿 있습니다. 

동적 쿼리는 기존의 .xml 방식을 사용하면 <if> 등의 태그를 사용해야 하기에 소스의 양이 매우 길어집니다.

(이런 예시가 있죠. goodteacher.tistory.com/249)

04. MyBatis - 동적 쿼리

 

 

자바 소스 내의 SQL 클래스를 사용하면 동적 쿼리를 자바 소스로 만들어서 하나의 쿼리로 여러 개의 기능에서 사용할 수 있도록 만들 수 있습니다. 예를 들어서 쿼리가 짜여진다면 아래와 같을 것입니다. 아래 두개의 쿼리는 테이블은 같지만 WHERE 조건이 달라지게 됩니다.

SELECT * 
FROM project
WHERE 1 = 1 
AND project_id = #{projectId}

---

SELECT * 
FROM project 
WHERE 1 = 1
AND title LIKE #{title}

먼저 이렇게 WHERE 조건이 다른 경우는 어떻게 사용하면 될까요?

쉽게 이해를 돕기 위해 직접 Java 소스를 가져와 보았습니다.

 

ProjectMapper.class

@Mapper
@ThisProjectDataSource
public interface ProjectMapper {

    @SelectProvider(type = ProjectProvider.class, method = "selectProject")
    List<Project> selectProject(Map<String, Object> param);
}

 

ProjectProvider.class

public String selectProject(Map<String, Object> param) {
    return new SQL() {{
        SELECT(ProviderBuilder.getColumns(Project.class, "p"))
        .SELECT(ProviderBuilder.getColumns(Member.class, "m"))

        .FROM("project p")
        .LEFT_OUTER_JOIN("member m ON m.corporation_id = c.id AND m.is_deleted = false")
        .WHERE("1 = 1" + getWhereClauses(param))
                .ORDER_BY("p.created_at DESC");
    }}.toString() + addLimit(param);
}

public String getWhereClauses(Map<String, Object> param) {
    StringBuilder builder = new StringBuilder();
    param.forEach((key, value) -> {
        String col = ProviderUtils.toLowerCase(key);
            if("id".equals(key)) {
                builder.append("\nAND p.id").append(ProviderUtils.is(value));
            }
            else if(ProviderUtils.isExistColumn(Project.class, key)) {
                builder.append("\nAND p.").append(col).append(ProviderUtils.like(value));
            }
            else if("memberId".equals(key)) {
                builder.append("\nAND m.id").append(ProviderUtils.is(value));
            }
            else if(ProviderUtils.isExistColumn(Member.class, key)) {
                builder.append("\nAND m.").append(col).append(ProviderUtils.like(value));
            }
        });
    builder.append("\nAND p.is_deleted = false");
    return builder.toString();
}

위에서 사용하는 ProviderBuilder.getColumns 함수는 특정 클래스에 선언되어 있는 property들을 각각 컬럼에 매칭시켜줄 수 있는 함수입니다.

ProviderBuilder.class

@Slf4j
public class ProviderBuilder {

    public static String getColumns(Class domain, String alias) {
        StringBuilder result = new StringBuilder();
        Arrays.stream(domain.getDeclaredFields()).forEach(d -> {
            result.append(alias)
                    .append(".")
                    .append(ProviderUtils.toLowerCase(d.getName()))
                    .append(" AS ")
                    .append(alias)
                    .append(ProviderUtils.toCamelCase(d.getName()))
                    .append(",\n");
        });
        result.deleteCharAt(result.toString().length()-2);
        return result.toString();
    }

}

이를 응용해서 쿼리를 만들어보고자 합니다.

예를 들어 Project.class 내에 아래와 같이 정의가 되어 있다고 가정해볼게요.

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class Project {
    private String id;
    private ProjectType type;
    private String title;
    private LocalDateTime startDate;
    private LocalDateTime endDate;
    private boolean isDeleted;
    private String createdBy;
}

 

여기서 추출되는 쿼리는 이렇게 될 수 있습니다. 

p.id AS pId, p.type AS pType, p.title AS pTitle, ...

이를 SELECT 문에 컬럼값에 넣으면 자동으로 적용 되겠죠.

 

그 다음으로 WHERE 조건에서 사용하는 동적 쿼리를 만드는 함수인 getWhereClauses라는 함수입니다. 

이 함수는 Project.class 에 조건문에 들어가는 파라미터가 있는지 비교해서 있는 경우 WHERE 조건의 String에 붙여주는 역할을 합니다. 쉽게 말해 이런 경우가 있을 것입니다.

// 오늘이 2021년 1월 19일 일때

getWhereClauses(Map.of("id", "12345", "status", "READY", "startDate", LocalDate.now()))
// WHERE 1 = 1 AND p.id = '12345' AND p.start_date = '2021-01-19'

여기서 status는 Project.class 에 명시되지 않은 property이므로 들어가지 않게 됩니다.

그리고 쿼리 조건절에서 없는 컬럼이 들어가면 에러가 나기 때문에 이와 같이 적용해주어야 합니다.

 

ProviderUtils.toLowerCase는 Project.class에 들어가는 property를 대소문자+언더스코어(_)가 들어간 문자열로 변환해주는 역할을 합니다. 예를 들면 이와 같아요. ProiderUtils.toCamelCase는 이와 반대로 카멜케이스를 만들어주죠.

ProviderUtils.toLowerCase("startDate"); // start_date
ProviderUtils.toCamelCase("member_id"); // memberId

 

아래 내용은 이 외에도 유용하게 쓸 수 있는 동적쿼리 유틸을 만들어보았습니다.

is(=), like(LIKE '%%'), 페이징(addLimit), 컬럼 존재 유무(isExistColumn) 등이 적혀있으니 유용하게 사용하면 되겠습니다! 

public class ProviderUtils {

    /**
     * @return 어퍼스트로피(')를 양쪽에 붙인 데이터 반
     */
    public static String apostrophe(String param) {
        return "'" + param + "'";
    }

    public static String apostrophe(Object param) {
        if(Objects.isNull(param))
            return null;
        return "'" + param + "'";
    }

    /**
     * 파라미터로 맵을 받을 때 where 절의 = 조건
     * @param param Map
     * @param key   Map.get(key)
     */
    public static String is(Map<String, Object> param, String key) {
        return is(param.get(key));
    }

    /**
     * 파라미터로 객체를 받을 때 where 절의 = 조건
     */
    public static String is(Object o) {
        if(o instanceof String) {
            return " = '" + o.toString() + "'";
        }
        else if(o instanceof Integer) {
            return " = " + ((Integer) o).intValue();
        }
        else if(o instanceof Long) {
            return " = " + ((Long) o).longValue();
        }
        else if(o instanceof Number){
            return " = " + ((Number) o).floatValue();
        }
        else if(o instanceof  Boolean) {
            return " = " + ((Boolean) o).booleanValue();
        }
        else {
            throw new NumberFormatException();
        }
    }

    public static String in(List<String> list) {
        return " IN (\'" + StringUtils.join(list, "\',\'") + "\')";
    }

    /**
     * where 절의 like 조건
     * @param param Map
     * @param key   map.get(key)
     */
    public static String like(Map<String, Object> param, String key) {
        return like(param.get(key));
    }

    /**
     * where 절의 like 조건
     */
    public static String like(Object o) {
        return " LIKE '%" + o.toString() + "%'";
    }

    /**
     * 페이지네이션에서 사용하는 limit
     * @param param Map
     */
    public static String addLimit(Map<String, Object> param) {
        return addLimit(param, "startIndex", "listSize");
    }

    public static String addLimit(Map<String, Object> param, String startIndex, String listSize) {
        if(param.get("startIndex") == null && param.get("listSize") == null) {
            return "";
        }

        if(param.get("startIndex") == null) {
            return " LIMIT " + param.get("listSize");
        }
        return " LIMIT " + param.get(startIndex) + ", " + param.get(listSize);
    }

    /**
     * @return 날짜(yyyy-MM-dd) 포맷 반환
     */
    public static String getDate(String datetime) {
        return getStartDate(datetime, false);
    }

    /**
     * @return Timestamp(yyyy-MM-dd HH:mm:ss) 포맷 반환
     */
    public static String getStartDate(String datetime, boolean isTimestamp) {
        return StringUtils.isNotBlank(datetime) ? datetime.substring(0, 10) + (isTimestamp ? " 00:00:00" : "") : "";
    }

    /**
     * @return Timestamp(yyyy-MM-dd HH:mm:ss) 포맷 반환
     */
    public static String getEndDate(String datetime) {
        return StringUtils.isNotBlank(datetime) ? datetime.substring(0, 10) + " 23:59:59" : "";
    }

    public static boolean hasPagination(Map<String, Object> map) {
        return map.containsKey("listSize") || map.containsKey("currentPage") || map.containsKey("startIndex");
    }

    public static boolean isSearchParam(Map.Entry<String, Object> entry) {
        return !entry.getKey().equals("listSize") && !entry.getKey().equals("currentPage") && !entry.getKey().equals("startIndex");
    }

    public static boolean isExistColumn(Class cls, Map.Entry<String, Object> entry) {
        return Arrays.stream(cls.getDeclaredFields())
                .anyMatch(field -> entry.getKey().equals(field.getName()));
    }

    public static boolean isExistColumn(Class cls, String key) {
        return Arrays.stream(cls.getDeclaredFields())
                .anyMatch(field -> key.equals(field.getName()));
    }


    public static String toLowerCase(String str) {
        String regex = "([a-z])([A-Z]+)";
        String replacement = "$1_$2";
        return str.replaceAll(regex, replacement).toLowerCase();
    }

    public static String toCamelCase(String str) {
        str = str.substring(0, 1).toUpperCase() + str.substring(1);
        StringBuilder builder = new StringBuilder(str);
        for (int i = 0; i < builder.length(); i++) {
            if (builder.charAt(i) == '_') {
                builder.deleteCharAt(i);
                builder.replace( i, i + 1, String.valueOf(Character.toUpperCase(builder.charAt(i))));
            }
        }
        return builder.toString();
    }
}