'Joining two tables with case sensitive data using criteria query

I am joining 2 tables with same data but one table has uppercase data and other one has lower case values. eg: kl2004 and KL2004 and this is causing issue on join and not able to fetch all the records. So I am trying to build this query in postgresql using spring boot java service.

    @Entity
    @Table(name = "ci.b")
    public class B implements Serializable {
    @Id
    @Column(name ="b_id")
    private Integer bId;
    @Column(name="lan_id")
    private String lanId;
    @Column(name="first_name")
    private String firstName;
    @Column(name="last_name")
    private String lastName;
    @Column(name="create_timestamp")
    private LocalDateTime createTimestamp;

    @Fetch(value= FetchMode.SELECT)
    @OneToMany(mappedBy = "b", fetch = FetchType.LAZY,cascade = CascadeType.ALL)
    private List<A> list;

   }


    @Entity
    @Table(name = "public.a")
    public class A implements Serializable {
    @Id
    @Column(name = "id")
    private Long id;
    @Column(name = "user_id", insertable = false,updatable = false)
    private String  userId;
    
    @Fetch(value=FetchMode.SELECT)
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id",referencedColumnName="lan_id")
    private B b;
        
   }

    RepositoryImpl class
    @Override
    public List<A> searchReport(Request request) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<A> criteriaQuery = criteriaBuilder.createQuery(A.class);
    Root<A> root = criteriaQuery.from(A.class);     
    Join<A, B> view = root.join("b", JoinType.LEFT);
    ....some search criteria builders coming from request.....
    Predicate[] predArray = new Predicate[getPredicateList().size()];
    getPredicateList().toArray(predArray);
    List<Order> orderList = new ArrayList<>();
    criteriaQuery.where(predArray);
    orderList.add(criteriaBuilder.asc(root.get("names")));
    criteriaQuery.orderBy(orderList);
    if (!getPredicateList().isEmpty()) {
        getPredicateList().clear();
    }
    return entityManager.createQuery(criteriaQuery).getResultList();

   }

Current query : select * from A left join B on A.user_id= B.lan_id

Expected query: select * from A left join B on lower(A.user_id)= lower(B.lan_id)

Can someone please help here to build the query as expected.I have tried @columntransformer,@join formula but didnot work.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source