How to create a get request for many-to-one columns?-CodePudding

I currently have made a spring boot project which is for an event system. In the model for booking class, I have two objects one is an event and the other one is the user. Now I want to create a get request that allows me to get all bookings made by a single user and all the bookings for a single event respectively. I have managed to create the other requests which are getting all the bookings and getting a booking by the booking id.

Right now if I try to make create any sort of implementation it either gives me a null pointer error or tells me the table relation "booking" doesn't exist. Please let me know if it's possible to write such a get request. Thanks

Model:

@Id
@SequenceGenerator(
        name = "booking_sequence",
        sequenceName = "booking_sequence",
        allocationSize = 1
)
@GeneratedValue(
        strategy = GenerationType.SEQUENCE,
        generator = "booking_sequence"
)
private Long id;

@ManyToOne
@JoinColumn(
        name = "event_id",
        referencedColumnName = "id"
)
private Event event;

@ManyToOne
@JoinColumn(
        name = "user_id",
        referencedColumnName = "id"
)
private User user;

private Integer tickets;
@Transient
private Integer amount;

Repository:

@Repository
public interface BookingRepository extends JpaRepository<Booking, Long > {
    @Query
    Optional<Booking> findBookingById(Long id);
}

Service:

@Autowired
public BookingService(BookingRepository bookingRepository) {
    this.bookingRepository = bookingRepository;
}

public List<Booking> getBookingList() {
    return bookingRepository.findAll();
}

public Booking getSingleBooking(Long bookingId) {
    return bookingRepository.findBookingById(bookingId).orElseThrow();
}

Controller:

@GetMapping
public List<Booking> getBookings() {
    return bookingService.getBookingList();
}

@GetMapping(path = "{bookingId}")
public Booking getSingleBooking(@PathVariable("bookingId") Long bookingId) {
    return bookingService.getSingleBooking(bookingId);}

@GetMapping(path = "/user/{userId}")
public List<Booking> getUserBookings(@PathVariable("userId") Long userId) {
    return bookingService.getBookingByUser(userId);}

@GetMapping(path = "/event/{eventId}")
public List<Booking> getEventBookings(@PathVariable("eventId") Long eventId) {
    return bookingService.getBookingForEvent(eventId);}

CodePudding user response：

you don't need the line

@Query
Optional<Booking> findBookingById(Long id);

the default repository implementation already gives you a findById so you can use it

And @Query can't be used like it used to, you need to pass the query you want, you can find out more here(https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query)

Or you can use this strategy to make your queries https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.sample-app.finders.strategies

CodePudding user response：

So it is possible to make such requests, all I did was use "nativeQuery" so that it would function the way I want it to. As mentioned I wanted to make two get-requests and here is how I wrote the queries for them.

Getting all user bookings of a specific user using its "ID":

@Query(value = "SELECT * from bookings, user where bookings.user_id = :id", nativeQuery = true)
List<Booking> findByUserid(Long id);

Getting all event bookings of a specific event using its "ID":

@Query(value = "SELECT * from bookings, user where bookings.event_id = :id", nativeQuery = true)
List<Booking> findByEventid(Long id);