R2DBC 동적 쿼리는...쥐쥐s

# 동적 쿼리를 쓰기에는 아직 무리가 보인다.

 

public interface UserRepository extends R2dbcRepository<User, Long> {
    Mono<User> findByEmail(String email);
    @Query("select * from user limit  :#{[0]} ,:#{[1]}")
    Flux<User> findAllPaging(int offset, int limit);
}

 

어여 JPA 만큼 올러와야 하는데.. 뜨뜻미지근한 느낌이다. ㅎㅎ 좋다면서?? 왜?

 

사용자 이름으로 검색을 하려 했더니...새롭게 interface를 추가하는 방법 말고는 없네.

 

날짜 조건에.. 기타 조건 넣고 빼고 하면.... 멘붕s 오겠다..

@Query("select * from user where name like :#{[0]} limit  :#{[1]} ,:#{[2]}")
Flux<User> findByNameLikePaging(String name, int offset, int limit);

일단 간단하니깐 로직에서 분기 태우자... 방법을 알려줘요..

 

시간이 없으매!

 

with QueryDsl -> ㅋㅋ

https://velog.io/@jhvictor4/R2DBC-QueryDsl-%EA%B0%99%EC%9D%B4-%EC%93%B0%EA%B8%B0-Webflux0

R2DBC & QueryDsl 같이 쓰기 - Webflux(0)

 

 

일단 DatabaseClient 로 Try!!!

    private String buildQuery(Map<String, String> param, String baseQuery) {
        StringBuffer baseSql = new StringBuffer(baseQuery);
        if(param.containsKey(Constants.NAME)){
            baseSql.append( " AND name = :name ");
        }
        if(param.containsKey(Constants.NICKNAME)){
            baseSql.append( " AND nickname = :nickname ");
        }
        if(param.containsKey(Constants.EMAIL)){
            baseSql.append( " AND email LIKE :email");
        }
        if(param.containsKey(Constants.MOBILE)){
            baseSql.append( " AND mobile = :mobile");
        }
        if(param.containsKey(Constants.FROM)){
            baseSql.append( " AND createDate >= :from");
        }
        if(param.containsKey(Constants.TO)){
            baseSql.append( " AND createDate <= :to");
        }
        baseSql.append( " ORDER BY createDate DESC");
        baseSql.append( " LIMIT :pageIndex , :pageSize" );
        return baseSql.toString();
    }
    private DatabaseClient.GenericExecuteSpec bindParametersToExecuteSpec(Map<String, String> param, String query) {
        DatabaseClient.GenericExecuteSpec executeSpec = databaseClient.sql(query)
                .bind(Constants.STATE,param.get(Constants.STATE));

        if(param.containsKey(Constants.NAME) && !param.get(Constants.NAME).isBlank()){
            executeSpec = executeSpec.bind( Constants.NAME, param.get(Constants.NAME));
        }
        if(param.containsKey(Constants.EMAIL) && !param.get(Constants.EMAIL).isBlank() ){
            executeSpec = executeSpec.bind( Constants.EMAIL, param.get(Constants.EMAIL) + "@%");
        }
        if(param.containsKey(Constants.NICKNAME) && !param.get(Constants.NICKNAME).isBlank()){
            executeSpec = executeSpec.bind(Constants.NICKNAME, param.get(Constants.NICKNAME));
        }
        if(param.containsKey(Constants.FROM) && !param.get(Constants.FROM).isBlank()){
            executeSpec = executeSpec.bind(Constants.FROM, param.get(Constants.FROM));
        }
        if(param.containsKey(Constants.TO) && !param.get(Constants.TO).isBlank()){
            executeSpec = executeSpec.bind(Constants.TO, param.get(Constants.TO));
        }
        if(param.containsKey(Constants.MOBILE) && !param.get(Constants.MOBILE).isBlank()){
            executeSpec = executeSpec.bind(Constants.MOBILE, param.get(Constants.MOBILE));
        }

        executeSpec = executeSpec.bind("pageIndex", param.getOrDefault("pageIndex", "0"));
        executeSpec = executeSpec.bind("pageSize", param.getOrDefault("pageSize", "10"));

        return executeSpec;
    }

    public DatabaseClient.GenericExecuteSpec getQuery(Map<String, String> param, String baseQuery){
        String query = buildQuery(param, baseQuery);
        return bindParametersToExecuteSpec(param, query);
    }

 

 

public Flux<UserGrpc.User> searchDynamicUserList(Map<String, String> param){
    return getQuery(param, " SELECT uid, nickname, email, name, mobile, createDate  FROM USER WHERE state = :state ")
            .map( (row, metadata)-> {
                String tell [] = row.get(Constants.MOBILE, String.class).split("-");
                return UserGrpc.User.newBuilder()
                        .setUid(row.get(Constants.UID, String.class))
                        .setNickname(row.get(Constants.NICKNAME, String.class))
                        .setEmail(row.get(Constants.EMAIL, String.class))
                        .setName(row.get(Constants.NAME, String.class))
                        .setMobile(tell[0] + "-****-" +tell[2])
                        .setCreateDate(row.get("createDate", ZonedDateTime.class).format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")))
                        .build();
    }).all();
}

public Mono<Long> countDynamicUserList(Map<String, String> param){
    return getQuery(param, " SELECT count(1) cnt FROM USER WHERE state = :state ")
            .fetch()
            .one()
            .map(row -> (Long)row.get("cnt"));
}