The spring-data-jpa-projections-example from sohangp

spring-data-jpa-projections-example's People

Contributors

Stargazers

Watchers

spring-data-jpa-projections-example's Issues

params in sql and paging

hi, I have read your medium post about this project, and run the project, it's great to put native query in orm.xml and map two tables.

But for real-world cases, queries often have params and the result need paging, how to do it with JPA-projection, would you please make the example go deeper?

I have a complex sql as below, the beenUsed field is neither from a nor b table, how do I mapping it to a dto?

    select
        a.*,
        (case
            when count(b.rule_id) > 0 then 'Y'
            else 'N'
        end) beenUsed
    from
        t_rule a
    left join
        t_ruleset_rule_rel b
            on a.id = b.rule_id
    where
        if(? is not null ,a.name like concat('%',?,'%') ,1=1)
        and if(? > 0 ,a.type in (?, ?) ,1=1)
        and if(? is not null ,a.create_time >= ? ,1=1)
        and if(? is not null ,a.create_time <= ? ,1=1)
    group by
        a.id
    order by
        a.create_time desc limit ?

currently, I write sql like this, but get syntax error, would you please give some suggestion?

    @Query(value = "select new com.coolproject.dto.RuleRec(a.id as id,a.name as name, a.desc as desc, a.type as type, a.value as value, a.status as status, a.create_time as createTime, a.create_user as createUser, a.update_time as updateTime, a.update_user as updateUser, (case when count(b.rule_id) > 0 then 'Y' else 'N' end) as beenUsed) " +
            "from t_rule a left join t_ruleset_rule_rel b on a.id = b.rule_id where " +
            "if(:#{#param.name} is not null ,a.name like concat('%',:#{#param.name},'%') ,1=1) " +
            "and if(:#{#param.type.size()} > 0 ,a.type in :#{#param.type} ,1=1)" +
            "and if(:#{#param.startDate} is not null ,a.create_time >= :#{#param.startDate} ,1=1) " +
            "and if(:#{#param.endDate} is not null ,a.create_time <= :#{#param.endDate} ,1=1) " +
            "group by a.id", nativeQuery = true)
    Page<RuleRec> ruleRecList(@Param("param") RuleQueryParam param, Pageable pageable);

Recommend Projects