CODEWITHSUNDEEP
javarestspring-bootspring-data-jpacomposite-primary-key
Keka Bron
Keka Bron

Reputation: 439

How to filter by one column when that column belongs to a composite primary key in Spring

Having a DAO like this:

@Entity
@Table(name="booking")

public class BookingObject implements Serializable{

private static final long serialVersionUID = 1L;

@EmbeddedId
private CompositeId compositePK;
private LocalDate init_date;
private LocalDate end_date;
private int confirmation;
// getters and setters

And composite primary key:

@Embeddable
public class CompositeId implements Serializable{

private static final long serialVersionUID = 1L;
@NotNull
private String guest;
@NotNull
private String idhousing;
//getters and setters

So I can now call findById(new CompositeId(guest, idhousing)); from my @RestController. The question is: what about if I want to filter by one of the composite primary key's columns, like guest.

I can't do findByguest since guest no longer exists in my DAO BookingObject. How can I get then the results of "give me all rows where guest is equal to..."

Upvotes: 3

Views: 1855

Answers (3)

Shivam Agrawal
Shivam Agrawal

Reputation: 491

You can do this by declaring a query method in the repository class like

List<BookingObject> findByCompositePKGuest(String guest);

And use this method to get the data from your controller.

The method is written in standard JPA format. The method name is resolved into object and field notion and converted to an actual SQL query in the background.

For more parameters, it'll be like this

List<BookingObject> findByCompositePKGuestAndCompositePKIdhousing(String guest, String idhousing);

findByCompositePKGuestAndCompositePKIdhousing is converted to -> findBy + compositePK.guest + And + compositePK.idhousing

Another option is to use Spring JPA filter by Example

Declare findAll(Example) method in the repository class

List<BookingObject> findAll(Example<BookingObject> bookingObject);

And call this method from the controller like this

BookingObject bookingObject = new BookingObject();
CompositeId compositeId = new CompositeId();
compositeId.setGuest(guest);
bookingObject.setCompositeId(compositeId);
Example<BookingObject> bookingObjectExample = Example.of(bookingObject);
List<BookingObject> bookingObjectList = bookingRepository.findAll(bookingObjectExample);

Refer this link for more detailed answer

Upvotes: 1

Musaddique S
Musaddique S

Reputation: 1567

You can try with findByExample

    CompositeId compId= new CompositeId();
    compId.setGuest(guestVal);

    BookingObject bookingObj= new BookingObject ();
    bookingObj.setCompositeId(compId);
    Example<BookingObject> bookExample = Example.of(bookingObj);
    List<BookingObject>  objList=repository.findAll(bookExample);

Upvotes: 2

Keka Bron
Keka Bron

Reputation: 439

An alternative approach could be to create an autoincremental key. It's an integer that automatically increments itself when a new row is inserted in the table. That way the composite primary key is not needed. Only problems is that users can book the same house infinite times, on the same date etc. But it's possible to implement some logic in your servlet in order to check if that user has already booked that house. Refer to: w3schools autoincrement

Upvotes: -1

Related Questions