最新消息:雨落星辰是一个专注网站SEO优化、网站SEO诊断、搜索引擎研究、网络营销推广、网站策划运营及站长类的自媒体原创博客

what is the best way to do custom query projection in spring boot jpa with nested structure - Stack Overflow

programmeradmin3浏览0评论

I have an entity like this

public class Venue extends BaseEntity {

    @Column(nullable = false, unique = true)
    private String name;

    @OneToOne()
    @JoinColumn(name = "address_id")
    private Address address;

    @Column(length = 1000)
    private String description;

    @OneToMany(mappedBy = "venue", fetch = FetchType.LAZY)
    private List<Court> courts = new ArrayList<>();

}

currently this is my VenueResponseDTO

@Getter
@Setter
public class VenueResponseDTO {
    private Long venueId;
    private String name;
    private String description;
    private AddressResponseDTO address;

    public VenueResponseDTO(Long venueId, String name, String description, String street, String zipCode,
            Long addressId, String city) {
        this.venueId = venueId;
        this.name = name;
        this.description = description;
        this.address = new AddressResponseDTO(street, zipCode, addressId, city);
    }

    @Getter
    @Setter
    public static class AddressResponseDTO {
        private String street;
        private String zipCode;
        private Long addressId;
        private String city;

        public AddressResponseDTO(String street, String zipCode, Long addressId, String city) {
            this.street = street;
            this.zipCode = zipCode;
            this.addressId = addressId;
            this.city = city;
        }

    }
}

which returns the response like this

{
    "venue_id": 1,
    "name": "Pfeffer LLC",
    "description": "Pariatur harum beatae laborum. Aut assumenda earum. Animi sapiente eos alias est natus. Molestias vel recusandae dolor culpa. Nobis quasi pariatur enim ipsum.",
    "address": {
        "street": "8394 Ricarda Underpass",
        "zip_code": "12087-4708",
        "address_id": 1,
        "city": "Emilville"
    }
}
  • is it possible to do it with spring interfaces ?

I tried doing

package com.mohamedsobhy292.playspot.DTO;

public interface VenueResponseDTO {
    Long getId();

    String getName();

    String getDescription();

    AddressResponseDTO getAddress();

    interface AddressResponseDTO {
        String getStreet();

        String getZipCode();

        CityResponseDTO getCity();

        interface CityResponseDTO {
            String getName();

            CountryResponseDTO getCountry();
        }

        interface CountryResponseDTO {
            String getName();
        }

    }

}

with my query like this


    @Query("""
                    SELECT
                       v.name AS name,
                       v.id AS id,
                       v.description AS description,
                        a as address,
                        c as city
                   FROM Venue v
                   LEFT JOIN Address a ON v.address.id = a.id
                   LEFT JOIN City c ON a.city.id = c.id
                   LEFT JOIN Country co ON c.country.id = co.id
                   WHERE v.id = :id
            """)
    VenueResponseDTO findVenueCustom(@Param("id") Long id);

but then I will have a lot of nested objects in the response which I don't like, like address => city => country

{
    "description": "Ut nisi sunt odio odit vel quo. Perspiciatis quo odit officia non sed est. Deleniti provident beatae.",
    "name": "Schimmel-Schinner",
    "id": 1,
    "address": {
        "street": "89716 Langosh Row",
        "zip_code": "77147-5713",
        "city": {
            "name": "Port Hymanshire",
            "country": {
                "name": "Germany"
            }
        }
    }
}

my questions will be

  • what is the best way to do custom query projections in spring jpa is it with class constructors or interfaces and why ?
  • what is the better way in my example to return the response the way I do, without all of this nesting
发布评论

评论列表(0)

  1. 暂无评论