CODEWITHSUNDEEP
javasqlpostgresqlhibernatespring-data-jpa
Maksim Rybalkin
Maksim Rybalkin

Reputation: 277

Get joined objects with Spring Data Jpa

I am writing a java project based on Spring-Boot. I have one-to-many relationship entities(I have excluded getters and setters):

@Entity
@Table(name = "restaurants")
public class Restaurant {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @NotBlank(message = "Please fill the name")
    private String name;

    @NotBlank(message = "Please fill the address")
    private String address;

    private LocalDateTime registered;

    private boolean isEnabled = true;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "restaurant")
    private List<Meal> meals;

    public Restaurant() {
    }

    public Restaurant(String name, String address, List<Meal> meals) {
        this.name = name;
        this.address = address;
        this.meals = meals;
    }
}

@Entity 
@Table(name = "meals") 
public class Meal {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    private String description;

    private Integer price;

    private LocalDate date;

    @JsonIgnore
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "restaurant_id", nullable = false)
    private Restaurant restaurant;

    public Meal() {
    }

    public Meal(String description, Integer price, Restaurant restaurant) {
        this.description = description;
        this.price = price;
        this.restaurant = restaurant;
    } 
}

Here are SQL scripts to create tables:

CREATE TABLE restaurants
(
    id         INTEGER   DEFAULT nextval('hibernate_sequence') PRIMARY KEY,
    address    VARCHAR(255)            NOT NULL,
    name       VARCHAR(255)            NOT NULL,
    registered TIMESTAMP DEFAULT now() NOT NULL,
    is_enabled BOOLEAN   DEFAULT TRUE  NOT NULL
);

CREATE UNIQUE INDEX restaurants_unique_name_address_idx ON restaurants (name, address);

CREATE TABLE meals
(
    id            INTEGER DEFAULT nextval('hibernate_sequence') PRIMARY KEY,
    date          DATE    DEFAULT now() NOT NULL,
    description   VARCHAR(255)          NOT NULL,
    price         INTEGER               NOT NULL,
    restaurant_id INTEGER               NOT NULL,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants (id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX meals_unique_restId_date_description_idx ON meals (restaurant_id, date, description);

I need to get all the restaurants from the repository:

public interface RestaurantRepository extends CrudRepository<Restaurant, Integer>

, but connected meals must have only today's date.

How it can be done using Spring Data Jpa? Maybe it's possible to use a method like findBy() or a @Query annotation - but I don't know how it works.

Upvotes: 3

Views: 1508

Answers (2)

Shekhar Rai
Shekhar Rai

Reputation: 2058

Your query should look like this,

public interface RestaurantRepository extends CrudRepository<Restaurant, Integer> {

    @Query("select distinct r from Restaurant r inner join r.meals m where m.date =:date")
    List<Restaurant> findByRestaurantMealDate(@Param("date") LocalDate date);

}

Now fetch it,

List<Restaurant> restaurants = repository.findByRestaurantMealDate(LocalDate.now());

Upvotes: 2

Maksim Rybalkin
Maksim Rybalkin

Reputation: 277

@Query("select distinct r from Restaurant r join fetch r.meals m where m.date=?1")
List<Restaurant> findByRestaurantMealDate(LocalDate date);

It works! Thanks everybody!

Upvotes: 1

Related Questions