'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 |
|---|
