I have an Entity Class like this:
@Entity
@Table(name = "CUSTOMER")
class Customer{
@Id
@Column(name = "Id")
Long id;
@Column(name = "EMAIL_ID")
String emailId;
@Column(name = "MOBILE")
String mobile;
}
How to write findBy method for the below query using crudrepository spring data jpa?
select * from customer where (email, mobile) IN (("[email protected]","8971"), ("[email protected]", "8888"))
I'm expecting something like
List<Customer> findByEmailMobileIn(List<Tuple> tuples);
I want to get the list of customers from given pairs
I think this can be done with org.springframework.data.jpa.domain.Specification
. You can pass a list of your tuples and proceed them this way (don't care that Tuple is not an entity, but you need to define this class):
public class CustomerSpecification implements Specification<Customer> {
// names of the fields in your Customer entity
private static final String CONST_EMAIL_ID = "emailId";
private static final String CONST_MOBILE = "mobile";
private List<MyTuple> tuples;
public ClaimSpecification(List<MyTuple> tuples) {
this.tuples = tuples;
}
@Override
public Predicate toPredicate(Root<Customer> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
// will be connected with logical OR
List<Predicate> predicates = new ArrayList<>();
tuples.forEach(tuple -> {
List<Predicate> innerPredicates = new ArrayList<>();
if (tuple.getEmail() != null) {
innerPredicates.add(cb.equal(root
.<String>get(CONST_EMAIL_ID), tuple.getEmail()));
}
if (tuple.getMobile() != null) {
innerPredicates.add(cb.equal(root
.<String>get(CONST_MOBILE), tuple.getMobile()));
}
// these predicates match a tuple, hence joined with AND
predicates.add(andTogether(innerPredicates, cb));
});
return orTogether(predicates, cb);
}
private Predicate orTogether(List<Predicate> predicates, CriteriaBuilder cb) {
return cb.or(predicates.toArray(new Predicate[0]));
}
private Predicate andTogether(List<Predicate> predicates, CriteriaBuilder cb) {
return cb.and(predicates.toArray(new Predicate[0]));
}
}
Your repo is supposed to extend interface JpaSpecificationExecutor<Customer>
.
Then construct a specification with a list of tuples and pass it to the method customerRepo.findAll(Specification<Customer>)
- it returns a list of customers.
It is maybe cleaner using a projection :
@Entity
@Table(name = "CUSTOMER")
class CustomerQueryData {
@Id
@Column(name = "Id")
Long id;
@OneToOne
@JoinColumns(@JoinColumn(name = "emailId"), @JoinColumn(name = "mobile"))
Contact contact;
}
The Contact Entity :
@Entity
@Table(name = "CUSTOMER")
class Contact{
@Column(name = "EMAIL_ID")
String emailId;
@Column(name = "MOBILE")
String mobile;
}
After specifying the entities, the repo :
CustomerJpaProjection extends Repository<CustomerQueryData, Long>, QueryDslPredicateExecutor<CustomerQueryData> {
@Override
List<CustomerQueryData> findAll(Predicate predicate);
}
And the repo call :
ArrayList<Contact> contacts = new ArrayList<>();
contacts.add(new Contact("[email protected]","8971"));
contacts.add(new Contact("[email protected]", "8888"));
customerJpaProjection.findAll(QCustomerQueryData.customerQueryData.contact.in(contacts));
Not tested code.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With