[Querydsl] 동적으로 where 만들기

먼저, 여기에서 말하는 동적 where는 아래와 같은 where절을 의미합니다.

 

WHERE userName = '홍길동' AND ( age = 24 or telno = '010-1111-2222' )

 

Querydsl에서는 BooleanBuilder를 사용하여 이와 같은 복합 검색조건을 만들 수 있습니다.

 

방법 1) and를 기준으로 괄호를 구분 ( and가 나오면 새로운 괄호의 시작 )

ex) and roleCode = 1 and ( roleName = 2 or roleDesc = 3 )

 

String temp = "{\"keyword_0\":{\"selectType\":\"roleCode\",\"andOr\":\"and\",\"select\":\"ROLE-000\",\"input\":\"\",\"ruleId\":\"\"}"
            + ",\"keyword_1\":{\"selectType\":\"roleCode\",\"andOr\":\"and\",\"select\":\"\",\"input\":\"ROLE-000\",\"ruleId\":\"\"}"
            + ",\"keyword_2\":{\"selectType\":\"roleName\",\"andOr\":\"or\",\"select\":\"\",\"input\":\"1234\",\"ruleId\":\"\"}"
            + ",\"keyword_3\":{\"selectType\":\"roleDesc\",\"andOr\":\"or\",\"select\":\"\",\"input\":\"test111\",\"ruleId\":\"\"}"
            + ",\"keyword_4\":{\"selectType\":\"roleDesc\",\"andOr\":\"and\",\"select\":\"\",\"input\":\"test111\",\"ruleId\":\"\"}"
            + ",\"keyword_5\":{\"selectType\":\"roleDesc\",\"andOr\":\"or\",\"select\":\"\",\"input\":\"test111\",\"ruleId\":\"\"}"
             + "}";
 
JSONObject keyword = JSONObject.fromObject(temp);
 
BooleanBuilder builder = new BooleanBuilder();
BooleanBuilder builder2 = new BooleanBuilder();
 
for(int i=0; i<10; i++) {
    try {
        JSONObject obj = keyword.getJSONObject("keyword_" + i);
 
        Field fd = wsmRole.getClass().getDeclaredField(obj.getString("selectType"));
        StringPath path = (StringPath) fd.get(wsmRole);
 
        if(i == 0 || "and".equals(obj.getString("andOr"))) {
            builder.and(builder2);
            builder2 = new BooleanBuilder();
 
            if(!"".equals(obj.getString("input"))) {
                builder2.and(path.like(obj.getString("input")));
            }
            else {
                builder2.and(path.eq(obj.getString("select")));
            }
        }
        else {
            if(!"".equals(obj.getString("input"))) {
                builder2.or(path.like(obj.getString("input")));
            }
            else {
                builder2.or(path.eq(obj.getString("select")));
            }
        }
    }
    catch(Exception e) {
    }
}
 
builder.and(builder2);
List<RoleVO> list = jpaQueryFactory.select(Projections.bean(RoleVO.class, wsmRole.roleCode
    , wsmRole.roleName
    , wsmRole.roleDesc
    , wsmRole.regionCode
    , wsmRole.logsearchGrade
    , wsmRole.emergencyKey.coalesce("N").as("emergencyKey")
    , Expressions.as(
            SQLExpressions.select(wsmRoleHomeMenu.menuCode)
            .from(wsmRoleHomeMenu)
            .where(wsmRoleHomeMenu.roleCode.eq(wsmRole.roleCode)
                    , wsmRoleHomeMenu.product.eq(paramMap.get("sessionProduct").toString())), "homeMenuCode"))
    ).from(wsmRole)
    .where(
            builder
            )
    .fetch();

 

방법 2. 무조건 나열

ex) and roleCode = 1 and roleName =2 or roleDesc = 3

 

String temp = "{\"keyword_0\":{\"selectType\":\"roleCode\",\"andOr\":\"and\",\"select\":\"ROLE-000\",\"input\":\"\",\"ruleId\":\"\"}"
    + ",\"keyword_1\":{\"selectType\":\"roleCode\",\"andOr\":\"and\",\"select\":\"\",\"input\":\"ROLE-000\",\"ruleId\":\"\"}"
    + ",\"keyword_2\":{\"selectType\":\"roleName\",\"andOr\":\"or\",\"select\":\"\",\"input\":\"1234\",\"ruleId\":\"\"}"
    + ",\"keyword_3\":{\"selectType\":\"roleDesc\",\"andOr\":\"or\",\"select\":\"\",\"input\":\"test111\",\"ruleId\":\"\"}"
    + ",\"keyword_4\":{\"selectType\":\"roleDesc\",\"andOr\":\"and\",\"select\":\"\",\"input\":\"test111\",\"ruleId\":\"\"}"
    + ",\"keyword_5\":{\"selectType\":\"roleDesc\",\"andOr\":\"or\",\"select\":\"\",\"input\":\"test111\",\"ruleId\":\"\"}"
    + "}";
 
JSONObject keyword = JSONObject.fromObject(temp);
BooleanBuilder builder = new BooleanBuilder();
 
for(int i=0; i<10; i++) {
    try {
        if("and".equals(obj.getString("andOr"))) {
            if(!"".equals(obj.getString("input"))) {
                builder.and(path.like(obj.getString("input")));
            }
            else {
                builder.and(path.eq(obj.getString("select")));
            }
        }
        else {
            if(!"".equals(obj.getString("input"))) {
                builder.or(path.like(obj.getString("input")));
            }
            else {
                builder.or(path.eq(obj.getString("select")));
            }
        }
    }
    catch(Exception e) {
    }
}
 
List<RoleVO> list = jpaQueryFactory.select(Projections.bean(RoleVO.class, wsmRole.roleCode
        , wsmRole.roleName
        , wsmRole.roleDesc
        , wsmRole.regionCode
        , wsmRole.logsearchGrade
        , wsmRole.emergencyKey.coalesce("N").as("emergencyKey")
        , Expressions.as(
                SQLExpressions.select(wsmRoleHomeMenu.menuCode)
                .from(wsmRoleHomeMenu)
                .where(wsmRoleHomeMenu.roleCode.eq(wsmRole.roleCode)
                        , wsmRoleHomeMenu.product.eq(paramMap.get("sessionProduct").toString())), "homeMenuCode"))
        ).from(wsmRole)
        .where(
            builder
        )
        .fetch();