INSERT INTO restaurants (creation_date, updated_on, address, city, description, name, status) VALUES
('2024-05-01', '2024-05-01 11:00:00', '123 FC Road', 'Pune', 'Traditional Maharashtrian meals', 'Shree Misal House', 1),
('2024-05-02', '2024-05-02 14:00:00', '88 JM Road', 'Pune', 'Popular North Indian and tandoor dishes', 'Tandoor Junction', 1),
('2024-05-03', '2024-05-03 17:30:00', υ'42 Baner Road', 'Pune', 'Modern vegetarian café with fusion food', 'Green Leaf Café', 1);
INSERT INTO food_items (creation_date, updated_on, is_veg, item_description, item_name, price, restaurant_id) VALUES
('2024-05-01', '2024-05-01 11:10:00', b'1', 'Spicy misal topped with farsan and onions', 'Puneri Misal', 70, 1),
('2024-05-01', '2024-05-01 11:15:00', b'1', 'Sabudana khichdi with peanuts and lemon', 'Sabudana Khichdi', 60, 1),
('2024-05-02', '2024-05-02 14:15:00', b'0', 'Char-grilled chicken with Indian spices', 'Tandoori Chicken', 180, 2),
('2024-05-02', '2024-05-02 14:30:00', b'1', 'Soft naan stuffed with spiced potatoes', 'Aloo Kulcha', 90, 2),
('2024-05-03', '2024-05-03 17:45:00', b'1', 'Millet-based pizza with paneer topping', 'Desi Paneer Pizza', 150, 3),
('2024-05-03', '2024-05-03 18:00:00', b'1', 'Chilled kokum-based beverage', 'Kokum Cooler', 50, 3);
Note: There appears to be a typo in the restaurants table insert for the third record (υ before '42 Baner Road'). I’ll assume it’s unintentional and treat it as '42 Baner Road'.
Based on the SQL schema, I’ll define JPA entity classes for Restaurant and FoodItem, including the appropriate relationships.
@Entity
@Table(name = "restaurants")
public class Restaurant {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "creation_date")
private LocalDate creationDate;
@Column(name = "updated_on")
private LocalDateTime updatedOn;
private String address;
private String city;
private String description;
private String name;
private boolean status;
@OneToMany(mappedBy = "restaurant", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<FoodItem> foodItems = new ArrayList<>();
// Getters and setters
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public LocalDate getCreationDate() { return creationDate; }
public void setCreationDate(LocalDate creationDate) { this.creationDate = creationDate; }
public LocalDateTime getUpdatedOn() { return updatedOn; }
public void setUpdatedOn(LocalDateTime updatedOn) { this.updatedOn = updatedOn; }
public String getAddress() { return address; }
public void setAddress(String address) { this.address = address; }
public String getCity() { return city; }
public void setCity(String city) { this.city = city; }
public String getDescription() { return description; }
public void setDescription(String description) { this.description = description; }
public String getName() { return name; }
public void setName(String name) { this.name = name; }
public boolean isStatus() { return status; }
public void setStatus(boolean status) { this.status = status; }
public List<FoodItem> getFoodItems() { return foodItems; }
public void setFoodItems(List<FoodItem> foodItems) { this.foodItems = foodItems; }
}
@Entity
@Table(name = "food_items")
public class FoodItem {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "creation_date")
private LocalDate creationDate;
@Column(name = "updated_on")
private LocalDateTime updatedOn;
@Column(name = "is_veg")
private boolean isVeg;
@Column(name = "item_description")
private String itemDescription;
@Column(name = "item_name")
private String itemName;
private int price;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "restaurant_id")
private Restaurant restaurant;
// Getters and setters
public Long getId() { return id; }
public void setId(Long id) { this.id = id; }
public LocalDate getCreationDate() { return creationDate; }
public void setCreationDate(LocalDate creationDate) { this.creationDate = creationDate; }
public LocalDateTime getUpdatedOn() { return updatedOn; }
public void setUpdatedOn(LocalDateTime updatedOn) { this.updatedOn = updatedOn; }
public boolean isVeg() { return isVeg; }
public void setVeg(boolean veg) { isVeg = veg; }
public String getItemDescription() { return itemDescription; }
public void setItemDescription(String itemDescription) { this.itemDescription = itemDescription; }
public String getItemName() { return itemName; }
public void setItemName(String itemName) { this.itemName = itemName; }
public int getPrice() { return price; }
public void setPrice(int price) { this.price = price; }
public Restaurant getRestaurant() { return restaurant; }
public void setRestaurant(Restaurant restaurant) { this.restaurant = restaurant; }
}
Notes:
status field in Restaurant is mapped as boolean since the SQL uses 1 (true) for active restaurants.is_veg field in FoodItem is mapped as boolean since the SQL uses b'1' (true) and b'0' (false).@OneToMany relationship is defined from Restaurant to FoodItem, and a @ManyToOne relationship from FoodItem to Restaurant.@Repository
public interface RestaurantRepository extends JpaRepository<Restaurant, Long> {
// Derived query: Find active restaurants
List<Restaurant> findByStatusTrue();
// Derived query: Find restaurants by city
List<Restaurant> findByCity(String city);
// Custom query: Find restaurants with a specific food item name
@Query("SELECT r FROM Restaurant r JOIN r.foodItems f WHERE f.itemName = :itemName")
List<Restaurant> findByFoodItemName(@Param("itemName") String itemName);
// Custom query: Delete restaurants by city
@Modifying
@Query("DELETE FROM Restaurant r WHERE r.city = :city")
int deleteByCity(@Param("city") String city);
}