Blog / 2020 / April / 2

Spring Boot JPA Repositories

April 2, 2020

Spring Boot JPA Repositories allow access to the database at an abstracted level, with queries being written in JPQL.

At their simplest, they look like this:

    /**
     * Find a calendar day given a room and a date.
     *
     * @param calendarDate The date to find the calendar day for
     * @param roomId       The id of the room to find the calendar day for
     * @return Single Calendar Day that matches the supplied room and date.
     */
    @Query("SELECT cd FROM RoomDayEntity cd WHERE cd.calendarDate = ?1 AND cd.room.id = ?2")
    public Optional<RoomDayEntity> findByDateAndRoom(LocalDate calendarDate, long roomId);

For more complex queries, it's possible to used named parameters to the method, rather than positional ones:

    /**
     * This just returns valid appointments, not blocked ones.
     * 
     * @param appointmentDate Date to filter appointments to
     * @param storeId         Store to show appointments for
     * @return List of valid appointments for that day and store
     */
    // @formatter:off
    @Query("SELECT a " + 
            "FROM AppointmentEntity a " + 
            "INNER JOIN a.roomSlot c " +
            "INNER JOIN c.slotType t " +
            "INNER JOIN c.roomDay d " + 
            "INNER JOIN d.staffMember p " + 
            "INNER JOIN d.room r " + 
            "INNER JOIN r.store s " +
            "INNER JOIN a.appointmentStatus st " +
            "WHERE st.id != 2" + // cancelled
            "AND d.calendarDate = :appointmentDate " + 
            "AND s.id = :storeId " + 
            "AND t.blockedReason IS NULL " + // No block on slot type
            "AND c.blockedReason IS NULL " + // No block on slot  
            "")
    // @formatter:on
    public List<AppointmentEntity> fetchAllValidForDay(@Param("appointmentDate") LocalDate appointmentDate, 
                                                       @Param("storeId") long storeId);

It's possible to build up queries by crafting a specifically named method in the Repository interface. This allows some additional features that are not available within JPQL, but does make the code intent harder to understand sometimes.

    // @formatter:off
    /**
     * Find the most recent calendar day for a given a room, looking back from the
     * specified date (inclusive)
     *
     * Effectively:
     * SELECT cd
     * FROM RoomDayEntity cd
     * WHERE cd.calendarDate <= ?1 AND cd.room.id = ?2
     * ORDER BY cd.calendarDate DESC
     * LIMIT 1; -- JPQL does not support LIMIT/TOP statements
     *
     * @param calendarDate The date to look backwards from
     * @param roomId       The id of the room to find the calendar day for
     * @return Single Calendar Day that matches the supplied room and date.
 */
    // @formatter:on
    public Optional<RoomDayEntity> findFirstByCalendarDateLessThanEqualAndRoomIdOrderByCalendarDateDesc(final LocalDate calendarDate, 
                                                                                                        final long roomId);

It is also possible to issue an update query using this mechanism, but I've had trouble with cached data when using this approach. Hibernate/JPA seemed to keep hold of the pre-updated data and returned that instead despite the request being done after the transaction has committed.

    /**
     * Update the cached room name on a particular room's RoomDay table
     *
     * @param currentDate Today's date - we only update RoomDays today or in the
     *                    future
     * @param roomId      The room we are changing the name of
     * @param newRoomName The new room name
     */
    @Modifying(flushAutomatically = true)
    @Query("UPDATE RoomDayEntity rd SET rd.cacheRoomName = ?3 WHERE rd.room.id = ?2 AND rd.calendarDate >= ?1")
    public void updateRoomName(LocalDate currentDate, long roomId, String newRoomName);

Tags: springboot spring jpa repository query modifying jpql