org.hibernate.QueryException: illegal attempt to dereference collection [events0_.id.typesOfEvents] with element property reference [type_id]

huangapple
未分类评论52阅读模式
英文:

org.hibernate.QueryException: illegal attempt to dereference collection [events0_.id.typesOfEvents] with element property reference [type_id]

问题

  1. 我正在使用 Spring Boot 和 JPA 构建 RESTful API。当我尝试获取事件列表时，出现了以下错误 - org.hibernate.QueryException: 尝试使用元素属性引用[type_id] 非法地取消引用集合 [events0_.id.typesOfEvents]。
  3. 我将 List 对象作为参数传递到 JPA 自定义查询的构造函数中，但是该查询不会执行，而会触发错误。
  5. Event 是主要实体，在此实体内，我使用 ManyToMany 关系获取了类型事件的 List，并且这两个表的 Id 存储在第三个表中。
  7. 1. Event.java
  8. （原文已省略）
  10. 2. EventDTO.java
  11. （原文已省略）
  13. 3. 查询
  14. （原文已省略）
  16. 4. 错误
  17. （原文已省略）
英文:

I am using spring boot and jpa with restful api. when i am trying to get list of event then this type of error occurs - org.hibernate.QueryException: illegal attempt to dereference collection [events0_.id.typesOfEvents] with element property reference [type_id]

I am passing List Object to constructor as args into jpa custom query but that query will not execute it will fire error.

Event is Main entity inside this entity i have taken List of typeOfEvents with using ManyToMany relation and that two table of Id is stored into third table.

1.Event.java

  1. @Builder
  2. @ToString
  3. @Entity
  4. @Table(name = "events")
  5. public @Data
  6. class Events implements Comparable<Events> {
  8.     @Id
  9.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  10.     @Column
  11.     private Long id;
  13.     @ManyToOne(cascade = CascadeType.REFRESH)
  14.     @JoinColumn(name = "site_id")
  15.     private Sites sites;
  17.     @Column
  18.     private boolean multipleDays;
  20.     @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
  21.     @JoinTable(name = "events_types",
  22.             joinColumns = @JoinColumn(name = "e_id"),
  23.             inverseJoinColumns = @JoinColumn(name = "t_id", unique = false))
  24.     private List<TypesOfEvents> typesOfEvents = new ArrayList<TypesOfEvents>(Arrays.asList());
  26.     @ManyToMany(cascade = CascadeType.DETACH)
  27.     @JoinTable(name = "events_format",
  28.             joinColumns = @JoinColumn(name = "e_id"),
  29.             inverseJoinColumns = @JoinColumn(name = "fs_id", unique = false))
  30.     private List<FormatStructure> formatStructure = new ArrayList<>();
  32.     @Column
  33.     private String startDate;
  35.     @Column
  36.     private String startTime;
  38.     @Column
  39.     private String endDate;
  41.     @Column
  42.     private String endTime;
  44.     @Column
  45.     private String eventName;
  47.     @Column
  48.     private String subTitle;
  50.     @Column
  51.     private String description;
  53.     @Column
  54.     private String location;
  56.     @ManyToOne(cascade = CascadeType.REFRESH)
  57.     @JoinColumn(name = "city_id")
  58.     private City city;
  60.     @ManyToOne(cascade = CascadeType.REFRESH)
  61.     @JoinColumn(name = "venue_one_off_option_id")
  62.     private VenueOneOffOptions venueOneOffOptions;
  64.     @Column
  65.     private double fee;
  67.     @Column
  68.     private boolean paid;
  70.     @Column
  71.     private String eventImg;
  73.     @ManyToOne(cascade = CascadeType.REFRESH)
  74.     @JoinColumn(name = "event_passport_option_id")
  75.     private EventPassportOptions eventPassportOptions;
  77.     @Column
  78.     private int passportTickets;
  80.     @Column
  81.     private int remainingTickets;
  83.     @Column
  84.     private boolean passport;
  86.     @Column
  87.     private int passportDiscountPercent;
  89.     @Column
  90.     private String promoCode;
  92.     @Column
  93.     private String siteUrl;
  95.     @Column
  96.     private String ticketPurchaseSite;
  98.     @Column
  99.     private String videoUrl;
  102.     @ManyToOne(cascade = CascadeType.REFRESH)
  103.     @JoinColumn(name = "primary_contact_member_id")
  104.     private Members primary_contact_member_id;
  107.     @ManyToOne(cascade = CascadeType.REFRESH)
  108.     @JoinColumn(name = "created_member_id")
  109.     private Members created_member_id;
  111.     @ManyToOne(cascade = CascadeType.REFRESH)
  112.     @JoinColumn(name = "group_id")
  113.     private Groups groups;
  115.     @Column
  116.     private String expectedAttendance;
  118.     @Column
  119.     private boolean myFavourite;
  121.     @ManyToOne(cascade = CascadeType.REFRESH)
  122.     @JoinColumn(name = "eventStatusId")
  123.     private EventStatus eventStatus;
  125.     @Column
  126.     private boolean purchasedPromotionalEmails;
  128.     @Column
  129.     private boolean featured;
  131.     @ManyToMany(cascade = CascadeType.DETACH)
  132.     @JoinTable(name = "events_audience_types",
  133.             joinColumns = @JoinColumn(name = "e_id"),
  134.             inverseJoinColumns = @JoinColumn(name = "at_id", unique = false))
  135.     private List<AudienceTypes> audienceTypes = new ArrayList<>();
  137.     @ManyToMany(cascade = CascadeType.DETACH)
  138.     @JoinTable(name = "events_food_options",
  139.             joinColumns = @JoinColumn(name = "e_id"),
  140.             inverseJoinColumns = @JoinColumn(name = "fo_id", unique = false))
  141.     private List<FoodOptions> foodOptions = new ArrayList<>();
  143.     public Events() {
  144.     }
  147.     @Override
  148.     public int compareTo(Events o) {
  149.         return (int) (this.id - o.id);
  150.     }
  151. }

2.EventDTO.java

  1. @ToString
  2. @AllArgsConstructor
  3. @NoArgsConstructor
  4. @JsonIgnoreProperties(ignoreUnknown = true)
  5. @JsonInclude(JsonInclude.Include.CUSTOM)
  6. public @Data
  7. class TestDTO implements Comparable<TestDTO>{
  9.     private Long id;
  11.     private boolean multipleDays;
  13.     private List<TypesOfEvents> eventType = new ArrayList<>(Arrays.asList());
  15.     private String startDate;
  17.     private String startTime;
  19.     private String endDate;
  21.     private String endTime;
  23.     private String eventName;
  25.     private String subTitle;
  27.     private String location;
  29.     private String city;
  31.     private double fee;
  33.     private boolean paid;
  35.     private String eventImg;
  37.     private Long passportOption;
  39.     private int passportTickets;
  41.     private int remainingTickets;
  43.     private boolean passport;
  45.     private String promoCode;
  47.     private String expectedAttendance;
  49.     private boolean myFavourite;
  51.     private String eventStatus;
  53.     private boolean featured;
  55.     public TestDTO(Long id, boolean multipleDays, List<TypesOfEvents> eventType, String startDate, String startTime, String endDate, String endTime, String eventName, String subTitle, String location, String city, double fee, boolean paid, String eventImg, Long passportOption, int passportTickets, int remainingTickets, boolean passport, String promoCode, String expectedAttendance, boolean myFavourite, String eventStatus, boolean featured) {
  56.         this.id = id;
  57.         this.multipleDays = multipleDays;
  58.         this.eventType = eventType;
  59.         this.startDate = startDate;
  60.         this.startTime = startTime;
  61.         this.endDate = endDate;
  62.         this.endTime = endTime;
  63.         this.eventName = eventName;
  64.         this.subTitle = subTitle;
  65.         this.location = location;
  66.         this.city = city;
  67.         this.fee = fee;
  68.         this.paid = paid;
  69.         this.eventImg = eventImg;
  70.         this.passportOption = passportOption;
  71.         this.passportTickets = passportTickets;
  72.         this.remainingTickets = remainingTickets;
  73.         this.passport = passport;
  74.         this.promoCode = promoCode;
  75.         this.expectedAttendance = expectedAttendance;
  76.         this.myFavourite = myFavourite;
  77.         this.eventStatus = eventStatus;
  78.         this.featured = featured;
  79.     }
  80. }

3.Query

  1. @Query("SELECT new com.test.responseDTO.TestDTO(e.id, e.multipleDays, e.typesOfEvents, e.startDate, e.startTime, e.endDate, e.endTime, e.eventName, e.subTitle, e.location, c.name, e.fee, e.paid, e.eventImg, p.event_passport_option_id, e.passportTickets, e.remainingTickets, e.passport, e.promoCode, e.expectedAttendance, e.myFavourite, s.status, e.featured)" +
  2.             " from Events e LEFT JOIN e.typesOfEvents.type_id t JOIN e.city c JOIN e.eventPassportOptions p JOIN e.eventStatus s WHERE e.startDate BETWEEN :startDate AND :endDate AND e.endDate BETWEEN :startDate AND :endDate ORDER BY e.startDate, e.id ASC")
  3.     List<TestDTO> findAllListOfEventsWithoutEventTypeIdWithOnlyTwoDateTest1(String startDate, String endDate);

4.Error

  1. org.hibernate.QueryException: illegal attempt to dereference collection [events0_.id.typesOfEvents] with element property reference [type_id]

答案1

得分: 0

以下是翻译好的部分：

  1. @Query("SELECT new com.test.responseDTO.TestDTO(e.id, e.multipleDays, e.typesOfEvents, e.startDate, e.startTime, e.endDate, e.endTime, e.eventName, e.subTitle, e.location, c.name, e.fee, e.paid, e.eventImg, p.event_passport_option_id, e.passportTickets, e.remainingTickets, e.passport, e.promoCode, e.expectedAttendance, e.myFavourite, s.status, e.featured)" +
  2.         " from Events e LEFT JOIN e.typesOfEvents t /*this fixed*/ JOIN e.city c JOIN e.eventPassportOptions p JOIN e.eventStatus s WHERE e.startDate BETWEEN :startDate AND :endDate AND e.endDate BETWEEN :startDate AND :endDate ORDER BY e.startDate, e.id ASC")
  3. List<TestDTO> findAllListOfEventsWithoutEventTypeIdWithOnlyTwoDateTest1(String startDate, String endDate);

我的更改后的查询：

  1. @Query("SELECT new com.techavidus.networkingPhoniex.responseDTO.TestDTO(e.id, e.multipleDays, t, e.startDate, e.startTime, e.endDate, e.endTime, e.eventName, e.subTitle, e.location, c.name, e.fee, e.paid, e.eventImg, p.event_passport_option_id, e.passportTickets, e.remainingTickets, e.passport, e.promoCode, e.expectedAttendance, e.myFavourite, s.status, e.featured)" +
  2.         " from Events e LEFT JOIN e.typesOfEvents t JOIN e.city c JOIN e.eventPassportOptions p JOIN e.eventStatus s WHERE e.startDate BETWEEN :startDate AND :endDate AND e.endDate BETWEEN :startDate AND :endDate ORDER BY e.startDate, e.id ASC")
  3. List<TestDTO> findAllListOfEventsWithoutEventTypeIdWithOnlyTwoDateTest1(String startDate, String endDate);

输出：

  1. {
  2.   "success": true,
  3.   "message": "记录成功查找。",
  4.   "data": [
  5.     {
  6.       "date": "2020/05/28",
  7.       "count": 2,
  8.       "events": [
  9.         {
  10.           "id": 1,
  11.           "multipleDays": true,
  12.           "eventType": {
  13.             "type_id": 1,
  14.             "type": "Musician1",
  15.             "paid_listing": false,
  16.             "color": "Black",
  17.             "_enabled": true
  18.           },
  19.           "startDate": "2020/05/28",
  20.           "startTime": "10:12:15",
  21.           "endDate": "2020/05/30",
  22.           "endTime": "20:18:25",
  23.           "eventName": "测试",
  24.           "subTitle": "测试演示",
  25.           "location": "字符串",
  26.           "city": "布胡吉",
  27.           "fee": 49,
  28.           "paid": false,
  29.           "eventImg": "图片网址",
  30.           "passportOption": 1,
  31.           "passportTickets": 0,
  32.           "remainingTickets": 0,
  33.           "passport": false,
  34.           "promoCode": "TEST123",
  35.           "expectedAttendance": "1500",
  36.           "myFavourite": false,
  37.           "eventStatus": "待批准",
  38.           "featured": false
  39.         },
  40.         {
  41.           "id": 1,
  42.           "multipleDays": true,
  43.           "eventType": {
  44.             "type_id": 2,
  45.             "type": "个人成长",
  46.             "paid_listing": true,
  47.             "color": "Black",
  48.             "_enabled": true
  49.           },
  50.           "startDate": "2020/05/28",
  51.           "startTime": "10:12:15",
  52.           "endDate": "2020/05/30",
  53.           "endTime": "20:18:25",
  54.           "eventName": "测试",
  55.           "subTitle": "测试演示",
  56.           "location": "字符串",
  57.           "city": "布胡吉",
  58.           "fee": 49,
  59.           "paid": false,
  60.           "eventImg": "图片网址",
  61.           "passportOption": 1,
  62.           "passportTickets": 0,
  63.           "remainingTickets": 0,
  64.           "passport": false,
  65.           "promoCode": "TEST123",
  66.           "expectedAttendance": "1500",
  67.           "myFavourite": false,
  68.           "eventStatus": "待批准",
  69.           "featured": false
  70.         }
  71.       ]
  72.     }
  73.   ]
  74. }
英文:

You reference type_id from list which its not allowed here, because query expression referring type_id to list, not to entity itself. So only replace this LEFT JOIN e.typesOfEvents.type_id t with LEFT JOIN e.typesOfEvents t and it would be work fine. And it would reference variable t to element from list(Relation).

  1.     @Query(&quot;SELECT new com.test.responseDTO.TestDTO(e.id, e.multipleDays, e.typesOfEvents, e.startDate, e.startTime, e.endDate, e.endTime, e.eventName, e.subTitle, e.location, c.name, e.fee, e.paid, e.eventImg, p.event_passport_option_id, e.passportTickets, e.remainingTickets, e.passport, e.promoCode, e.expectedAttendance, e.myFavourite, s.status, e.featured)&quot; +
  2.             &quot; from Events e LEFT JOIN e.typesOfEvents t /*this fixed*/ JOIN e.city c JOIN e.eventPassportOptions p JOIN e.eventStatus s WHERE e.startDate BETWEEN :startDate AND :endDate AND e.endDate BETWEEN :startDate AND :endDate ORDER BY e.startDate, e.id ASC&quot;)
  3.     List&lt;TestDTO&gt; findAllListOfEventsWithoutEventTypeIdWithOnlyTwoDateTest1(String startDate, String endDate);

My Query with changes into TestDTO

  1.     @Query(&quot;SELECT new com.techavidus.networkingPhoniex.responseDTO.TestDTO(e.id, e.multipleDays, t, e.startDate, e.startTime, e.endDate, e.endTime, e.eventName, e.subTitle, e.location, c.name, e.fee, e.paid, e.eventImg, p.event_passport_option_id, e.passportTickets, e.remainingTickets, e.passport, e.promoCode, e.expectedAttendance, e.myFavourite, s.status, e.featured)&quot; +
  2.             &quot; from Events e LEFT JOIN e.typesOfEvents t JOIN e.city c JOIN e.eventPassportOptions p JOIN e.eventStatus s WHERE e.startDate BETWEEN :startDate AND :endDate AND e.endDate BETWEEN :startDate AND :endDate ORDER BY e.startDate, e.id ASC&quot;)
  3.     List&lt;TestDTO&gt; findAllListOfEventsWithoutEventTypeIdWithOnlyTwoDateTest1(String startDate, String endDate);

output :

  1. {
  2.   &quot;success&quot;: true,
  3.   &quot;message&quot;: &quot;Record&#39;s find successfully.&quot;,
  4.   &quot;data&quot;: [
  5.     {
  6.       &quot;date&quot;: &quot;2020/05/28&quot;,
  7.       &quot;count&quot;: 2,
  8.       &quot;events&quot;: [
  9.         {
  10.           &quot;id&quot;: 1,
  11.           &quot;multipleDays&quot;: true,
  12.           &quot;eventType&quot;: {
  13.             &quot;type_id&quot;: 1,
  14.             &quot;type&quot;: &quot;Musician1&quot;,
  15.             &quot;paid_listing&quot;: false,
  16.             &quot;color&quot;: &quot;Black&quot;,
  17.             &quot;_enabled&quot;: true
  18.           },
  19.           &quot;startDate&quot;: &quot;2020/05/28&quot;,
  20.           &quot;startTime&quot;: &quot;10:12:15&quot;,
  21.           &quot;endDate&quot;: &quot;2020/05/30&quot;,
  22.           &quot;endTime&quot;: &quot;20:18:25&quot;,
  23.           &quot;eventName&quot;: &quot;Test&quot;,
  24.           &quot;subTitle&quot;: &quot;Testing demo&quot;,
  25.           &quot;location&quot;: &quot;string&quot;,
  26.           &quot;city&quot;: &quot;Bhuj&quot;,
  27.           &quot;fee&quot;: 49,
  28.           &quot;paid&quot;: false,
  29.           &quot;eventImg&quot;: &quot;Imgage url&quot;,
  30.           &quot;passportOption&quot;: 1,
  31.           &quot;passportTickets&quot;: 0,
  32.           &quot;remainingTickets&quot;: 0,
  33.           &quot;passport&quot;: false,
  34.           &quot;promoCode&quot;: &quot;TEST123&quot;,
  35.           &quot;expectedAttendance&quot;: &quot;1500&quot;,
  36.           &quot;myFavourite&quot;: false,
  37.           &quot;eventStatus&quot;: &quot;Pending Approval&quot;,
  38.           &quot;featured&quot;: false
  39.         },
  40.         {
  41.           &quot;id&quot;: 1,
  42.           &quot;multipleDays&quot;: true,
  43.           &quot;eventType&quot;: {
  44.             &quot;type_id&quot;: 2,
  45.             &quot;type&quot;: &quot;Personal Growth&quot;,
  46.             &quot;paid_listing&quot;: true,
  47.             &quot;color&quot;: &quot;Black&quot;,
  48.             &quot;_enabled&quot;: true
  49.           },
  50.           &quot;startDate&quot;: &quot;2020/05/28&quot;,
  51.           &quot;startTime&quot;: &quot;10:12:15&quot;,
  52.           &quot;endDate&quot;: &quot;2020/05/30&quot;,
  53.           &quot;endTime&quot;: &quot;20:18:25&quot;,
  54.           &quot;eventName&quot;: &quot;Test&quot;,
  55.           &quot;subTitle&quot;: &quot;Testing demo&quot;,
  56.           &quot;location&quot;: &quot;string&quot;,
  57.           &quot;city&quot;: &quot;Bhuj&quot;,
  58.           &quot;fee&quot;: 49,
  59.           &quot;paid&quot;: false,
  60.           &quot;eventImg&quot;: &quot;Imgage url&quot;,
  61.           &quot;passportOption&quot;: 1,
  62.           &quot;passportTickets&quot;: 0,
  63.           &quot;remainingTickets&quot;: 0,
  64.           &quot;passport&quot;: false,
  65.           &quot;promoCode&quot;: &quot;TEST123&quot;,
  66.           &quot;expectedAttendance&quot;: &quot;1500&quot;,
  67.           &quot;myFavourite&quot;: false,
  68.           &quot;eventStatus&quot;: &quot;Pending Approval&quot;,
  69.           &quot;featured&quot;: false
  70.         }
  71.       ]
  72.     }
  73.   ]
  74. }

huangapple
  • 本文由 发表于 2020年5月29日 20:31:38
  • 转载请务必保留本文链接：https://java.coder-hub.com/62086072.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定