下面是我的模型类

客户

public class Customer {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Long customerid;

    @NotNull
    String name;

    @NotNull
    @Column(name = "phone_no")
    @JsonProperty("phone_no")
    String phoneNo;

    String referer;

    @NotNull
    String email;

    @OneToOne (fetch = FetchType.LAZY)
    @JoinColumn(name = "agent_id",nullable = true)
    @JsonProperty(required = false)
    Agent agent;

    @OneToOne (fetch = FetchType.LAZY)
    @JoinColumn(name = "partner_id",nullable = true)
    @JsonProperty(required = false)
    Partner partner;
}

代理

public class Agent extends UserDetails
{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Long agentid;

    String city;

    String type;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="user_id",nullable=false)
    private User user;
}

合作伙伴

public class Partner extends UserDetails
{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Long partnerid;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="user_id",nullable=false)
    private User user;
}

映射超类

@MappedSuperclass
public class UserDetails 
{

    @NotNull
    @Column(name="name")
    String name;
    
    @NotNull
    @Column(name="mobileno")
    String mobileno;
    
    @NotNull
    @Column(name="email")
    String email;

业务逻辑 - 客户要么填充“推荐人”字段，要么是代理或合作伙伴。如果他与代理有关，我们将填充 agent_id，否则填充 partner_id。

我想编写一个规范，它会尝试根据这些可选字段进行过滤。搜索应该是

IF customer.referer MATCHES OR agent.name MATCHES or partner.name MATCHED -> 返回记录。

我必须对多个字符串执行此搜索。因此，我编写了可重用的方法来生成规范。下面是我的规范类

public class CustomerSpecification {

    SpecificationsBuilder<Customer> specbldr = new SpecificationsBuilder<Customer>();

    public Specification<Customer> getSpecification(FilterDataListV2 filterDataList) throws Exception {
        
        Specification<Customer> finalSpec = null;
        List<String> globalSearch = SpecificationsBuilder.fetchValueFromFilterList(filterDataList,"globalSearch");

        if (globalSearch != null && globalSearch.size() > 0) {
            
            Specification<Customer> internalSpec1 = whereDirectFieldContains(Customer_.REFERER,globalSearch);
            Specification<Customer> internalSpec2 = whereChildFieldContains(Customer_.AGENT,Agent_.NAME,globalSearch);
            Specification<Customer> internalSpec3 = whereChildFieldContains(Customer_.PARTNER,Partner_.NAME,globalSearch);

            Specification<Customer> internalSpec = internalSpec1.or(internalSpec2).or(internalSpec3);

            finalSpec = specbldr.specAndCondition(finalSpec,internalSpec);
        }
        return finalSpec;
    }


    public Specification<Customer> whereDirectFieldContains(String key,List<String> names)
    {
        Specification<Customer> finalSpec = null;
        for (String name : names)
        {
            Specification<Customer> internalSpec = (Root<Customer> root,CriteriaQuery<?> query,CriteriaBuilder cb) -> cb
                    .like(root.get(key),"%" + name + "%");
            finalSpec = specOrCondition(finalSpec,internalSpec);
        }
        return finalSpec;
    }

    public Specification<Customer> whereChildFieldContains(String childTable,String childFiledName,CriteriaBuilder cb) -> cb
                    .like(root.get(childTable).get(childFiledName),internalSpec);
        }
        return finalSpec;
    }
    
    public Specification<Customer> specOrCondition(Specification<Customer> finalSpec,Specification<Customer> internalSpec)
    {
        if (finalSpec == null)
            return internalSpec;
        else
            return finalSpec.or(internalSpec);
    }

    public Specification<Customer> specAndCondition(Specification<Customer> finalSpec,Specification<Customer> internalSpec)
    {
        if (finalSpec == null)
            return internalSpec;
        else
            return finalSpec.and(internalSpec);
    }
}

如果任何字段与搜索条件匹配，我希望它返回结果。但是，没有返回任何记录。

预期的 SQL - 对于 2 个搜索字符串

SELECT c.* from customer c 
LEFT JOIN agent a on c.agent_id=a.agentid
LEFT JOIN partner p on c.partner_id=p.partnerid
WHERE 
    c.referer LIKE '%MOHAN%' OR c.referer LIKE '%NIRANJAN%'
    OR a.name LIKE '%MOHAN%' OR c.referer LIKE '%NIRANJAN%'
    OR p.name LIKE '%MOHAN%' OR c.referer LIKE '%NIRANJAN%';

生成的 SQL

select
            customer0_.customerid as customer1_3_,customer0_.agent_id as agent_id6_3_,customer0_.email as email2_3_,customer0_.name as name3_3_,customer0_.partner_id as partner_7_3_,customer0_.phone_no as phone_no4_3_,customer0_.referer as referer5_3_ 
        from
            customer customer0_ cross 
        join
            agent agent1_ cross 
        join
            partner partner3_ 
        where
            customer0_.agent_id=agent1_.agentid 
            and customer0_.partner_id=partner3_.partnerid -- This AND condition should not be there
            and (
                customer0_.referer like ? 
                or customer0_.referer like ? 
                or agent1_.name like ? 
                or agent1_.name like ? 
                or partner3_.name like ? 
                or partner3_.name like ?
            ) 
        order by
            customer0_.customerid

有人可以帮忙我哪里出错了