JPA2，LEFT JOIN，条件API [英] JPA2, LEFT JOIN, Criteria API

问题描述

我有以下情况需要在我的条件构建器中实施以创建动态查询。

I have the following situation SQL that I need to implement into my criteria builder for dynamic query creation.

select t1.ticketnr 
from tbl_ticket t1 
left join tbl_tickets_updates t2 on t1.ticketnr = t2.ticketnr 
where t1.description ilike '%EXAMPLE%' or t2.description ilike '%EXAMPLE%';

在我的代码中，我有以下内容：

In my code I have the following:

tbl_ticket = Tickets.class
PK = ticketnr

tbl_ticket = Tickets.class PK = ticketnr

tbl_tickets_updates = TicketsUpdates.class和TicketsUpdatesPK.class
PK = ticketnr，更新者，timeofupdate

tbl_tickets_updates = TicketsUpdates.class and TicketsUpdatesPK.class PK = ticketnr, updatedby, timeofupdate

        CriteriaBuilder builder = em.getCriteriaBuilder();
        CriteriaQuery<Tickets> query = builder.createQuery(Tickets.class);
        EntityType<Tickets> type = em.getMetamodel().entity(Tickets.class);
        Root<Tickets> root = query.from(Tickets.class);
        List<Predicate> predicatesAnd = new ArrayList<Predicate>();
...
    if (text.length() != 0) {
                    predicatesAnd.add(builder.or(
                            builder.like(
                            builder.lower(
                            root.get(
                            type.getDeclaredSingularAttribute("description", String.class))), "%" + text.toLowerCase() + "%"),
                            builder.like(
                            builder.lower(
                            root.get(
                            type.getDeclaredSingularAttribute("summary", String.class))), "%" + text.toLowerCase() + "%")));
                    Join<Tickets, TicketsUpdates> tupdates = root.join("ticketnr", JoinType.LEFT);
                    predicatesAnd.add(builder.like(builder.lower(tupdates.get("description").as(String.class)), "%" + text.toLowerCase() + "%"));
                }

我的代码在左连接失败，原因是： java.lang .IllegalStateException：CAN_NOT_JOIN_TO_BASIC

My code is failing at the Left Join with: java.lang.IllegalStateException: CAN_NOT_JOIN_TO_BASIC

我相信这是由于复合PK具有单独的类而发生的，但到目前为止我还没有找到实现方法

I believe this is happening due to having a separate class for the composite PK, but so far I have not found a way to implement this properly.

有什么想法吗？

推荐答案

基于评论克里斯（Chris），我提供的是列名称，而不是实体类中的关系名称（在实体类中标记为Mapped）。

Based on the comment of Chris, I was providing column name instead of the relationship name from the Entity Class ( marked as Mapped in the entity class )

 CriteriaBuilder builder = em.getCriteriaBuilder();
            CriteriaQuery<Tickets> query = builder.createQuery(Tickets.class);
            EntityType<Tickets> type = em.getMetamodel().entity(Tickets.class);
            EntityType<TicketsUpdates> typeTU = em.getMetamodel().entity(TicketsUpdates.class);
            Root<Tickets> root = query.from(Tickets.class);
            Root<TicketsUpdates> rootTicketsUpdates = query.from(TicketsUpdates.class);

            Join<Tickets,TicketsUpdates> tupdates = rootTicketsUpdates.join("tickets");

我必须添加第二个（它丢失了），并将查询更改为：

I had to add the second from ( it was missing ) and change my query to:

if (text.length() != 0) {
                predicatesAnd.add(builder.or(
                        builder.like(
                        builder.lower(
                        root.get(
                        type.getDeclaredSingularAttribute("description", String.class))), "%" + text.toLowerCase() + "%"),
                        builder.like(
                        builder.lower(
                        root.get(
                        type.getDeclaredSingularAttribute("summary", String.class))), "%" + text.toLowerCase() + "%"),
                        builder.like(
                        builder.lower(
                        tupdates.get(
                        typeTU.getDeclaredSingularAttribute("description", String.class))), "%"+text.toLowerCase()+"%")
                        ));
            }

    query.orderBy(builder.desc(root.get("startdate")));
    query.distinct(true);

现在所有功能都按预期运行！

Now all works as expected!

这篇关于JPA2，LEFT JOIN，条件API的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！