-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 12 posts ] 
Author Message
 Post subject: can I filter a collection got using @Query
PostPosted: Wed Sep 27, 2017 1:47 am 
Newbie

Joined: Fri Sep 01, 2017 2:40 am
Posts: 17
I have an entity called UserSchedule like following
Code:
@Entity
@Table(name = "user_schedule")
public class UserSchedule {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name = "user_id")
    private long userId;

    @OneToMany(cascade = CascadeType.ALL)
    @JoinColumn(name = "schedule_id", referencedColumnName = "id")
    private Set<Schedule> schedules;
}

In my UserScheduleRepository I want to filter the schedules. Something like this
Code:
@Query("select us from UserSchedule us join us.schedules s where s.id = 1")

but apparently this doesn't work because hibernate complains
Code:
2017-09-27T05:42:06.110646+00:00 app[web.1]: Hibernate: select userschedu0_.id as id1_12_, userschedu0_.user_id as user_id2_12_ from user_schedule userschedu0_ inner join user_schedule_schedules schedules1_ on userschedu0_.id=schedules1_.user_schedule_lite_id inner join schedule scheduleli2_ on schedules1_.schedules_id=scheduleli2_.id where userschedu0_.user_id=? and scheduleli2_.is_active=1 and scheduleli2_.start_date<=? and scheduleli2_.end_date>=?
2017-09-27T05:42:06.114618+00:00 app[web.1]: 2017-09-26 22:42:06.114  WARN 4 --- [io-11487-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42P01
2017-09-27T05:42:06.114771+00:00 app[web.1]: 2017-09-26 22:42:06.114 ERROR 4 --- [io-11487-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: relation "user_schedule_schedules" does not exist

which, yes, it doesn't exist, but how can I filter the collection properly?


Top
 Profile  
 
 Post subject: Re: can I filter a collection got using @Query
PostPosted: Wed Sep 27, 2017 3:10 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
Your query looks like it was generated for a unidirectional @OneToMany without @JoinColumn.

Anyway, unidirectional associations are bad for performance, so use a bidirectional one.


Top
 Profile  
 
 Post subject: Re: can I filter a collection got using @Query
PostPosted: Wed Sep 27, 2017 7:06 pm 
Newbie

Joined: Fri Sep 01, 2017 2:40 am
Posts: 17
vlad wrote:
Your query looks like it was generated for a unidirectional @OneToMany without @JoinColumn.

Anyway, unidirectional associations are bad for performance, so use a bidirectional one.


Hi Vlad,

I still can't get it to work. but anyway, I try the other direction and hibernate seems to be happy with it.
All errors are gone, but the problem is that I don't receive any data.

I first made sure the data is there.
Image
and I think I have the exact same query in hql
Image
but I got nothing in response
Image

I guess hibernate isn't able to map the data because it has no column names? but I can't find any other tutorial online that does differently. What did I do wrong?


Top
 Profile  
 
 Post subject: Re: can I filter a collection got using @Query
PostPosted: Wed Sep 27, 2017 7:59 pm 
Newbie

Joined: Fri Sep 01, 2017 2:40 am
Posts: 17
I found this works
Code:
@Query("select s from Schedule s")

but this does not
Code:
@Query("select s from Schedule s join s.userSchedule")

so hibernate is able to map the values but why would join table breaks?

I have changed my entities to have
Code:
   
    @Setter
    @Getter
    @JoinColumn(name = "id", referencedColumnName = "schedule_id")
    @OneToOne(cascade = CascadeType.ALL)
    private UserSchedule userSchedule;

Code:
    @Setter
    @Getter(onMethod = @__(@JsonIgnore))
    @JoinColumn(name = "schedule_id", referencedColumnName = "id")
    @OneToOne(cascade = CascadeType.ALL)
    private Schedule schedule;


Top
 Profile  
 
 Post subject: Re: can I filter a collection got using @Query
PostPosted: Wed Sep 27, 2017 8:46 pm 
Newbie

Joined: Fri Sep 01, 2017 2:40 am
Posts: 17
I think I found the reason
Code:
select schedule0_.id as id1_10_, schedule0_.created_at as created_2_10_, schedule0_.end_date as end_date3_10_, schedule0_.end_time as end_time4_10_, schedule0_.frequency as frequenc5_10_, schedule0_.frequency_val as frequenc6_10_, schedule0_.is_active as is_activ7_10_, schedule0_.start_date as start_da8_10_, schedule0_.start_time as start_ti9_10_, schedule0_.updated_at as updated10_10_, schedule0_.description as descrip11_10_, schedule0_.lat as lat12_10_, schedule0_.lon as lon13_10_, schedule0_.project_id as project15_10_, schedule0_.title as title14_10_, (select v.status from voucher v where v.schedule_id = schedule0_.id) as formula1_ from schedule schedule0_ inner join user_schedule userschedu1_ on schedule0_.id=userschedu1_.id limit ?

near the very last where schedule0_.id=userschedu1_.id should be schedule0_.id=userschedu1_.schedule_id. why does hibernate use id instead of schedule_id?
Code:
em.createQuery("select s from Schedule s join s.userSchedule us").getResultList()

the above gives the same query too.


Top
 Profile  
 
 Post subject: Re: can I filter a collection got using @Query
PostPosted: Thu Sep 28, 2017 5:10 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
It's impossible to tell without providing all the mappings and data access logic code. There's also a formula1_ there, for instance.

So, just add all the entries and everything you do in the current data access layer method.


Top
 Profile  
 
 Post subject: Re: can I filter a collection got using @Query
PostPosted: Thu Sep 28, 2017 11:22 am 
Newbie

Joined: Fri Sep 01, 2017 2:40 am
Posts: 17
Hi Vlad,
Sorry, I thought only providing the ones I think are relevant will make the post cleaner.
Here are my schedule and user_schedule entity.

Code:
@MappedSuperclass
public class ScheduleBase extends BaseEntity {
    @Setter
    @Getter
    @Column(name = "frequency")
    private int frequency;

    @Setter
    @Getter
    @Column(name = "frequency_val")
    private Integer frequencyValue;

    @Setter
    @Getter
    @Column(name = "is_active", insertable = false)
    private Integer isActive;

    @Setter
    @Getter
    @Column(name = "start_date")
    private Date startDate;

    @Setter
    @Getter
    @Column(name = "end_date")
    private Date endDate;

    @Setter
    @Getter
    @Column(name = "start_time")
    private Date startTime;

    @Setter
    @Getter
    @Column(name = "end_time")
    private Date endTime;

    @Setter
    @Getter
    @Column(name = "created_at", insertable = false, updatable = false)
    private Date createdAt;

    @Setter
    @Getter
    @Version
    @Column(name = "updated_at", insertable = false, updatable = false)
    private Date updatedAt;

    @Setter
    @Getter
    @JoinColumn(name = "id", referencedColumnName = "schedule_id")
    @OneToOne(cascade = CascadeType.ALL)
    private UserSchedule userSchedule;
}

@Entity
@Table(name = "schedule")
public class Schedule extends ScheduleBase {
    @Setter
    @Getter
    @OneToOne
    @JoinColumn
    private ProjectLite project;

    @Setter
    @Getter
    private Long lat;

    @Setter
    @Getter
    private Long lon;

    @Setter
    @Getter
    private String title;

    @Setter
    @Getter
    private String description;

    @Getter
    @Formula("(select v.status from voucher v where v.schedule_id = id)")
    private Integer redemptionStatus;

    public Schedule() {
    }
}


@Entity
@Table(name = "user_schedule")
public class UserSchedule extends BaseEntity {
    @Setter
    @Getter(onMethod = @__(@JsonIgnore))
    @Column(name = "user_id")
    private long userId;

    @Setter
    @Getter(onMethod = @__(@JsonIgnore))
    @JoinColumn(name = "schedule_id", referencedColumnName = "id")
    @OneToOne(cascade = CascadeType.ALL)
    private Schedule schedule;

    public UserSchedule() {
    }
}

@MappedSuperclass
public class BaseEntity {
    @Id
    @Setter
    @Getter
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected long id;
}

@Repository
public interface ScheduleRepository extends PagingAndSortingRepository<Schedule, Long> {
    @Query("select s from Schedule s join s.userSchedule")
    Page<Schedule> findAllSchedule(Pageable page);
}

I've simplify my @Query just to test the join behavior

Thanks


Top
 Profile  
 
 Post subject: Re: can I filter a collection got using @Query
PostPosted: Thu Sep 28, 2017 12:08 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
The Schedule has a @OneToOne association with UserSchedule via a FK.

But then, the UserSchedule also has @OneToOne to Schedule which is not mappedBy.

That's not how you definie a bidirectional association. Check out this article for more details of how you should do it.

The use IDENTITY is also problematic if the underlying DB supports sequences.

The default EAGER fetching for all @OneToOne and @ManyToOne can cause serious performance problems.

Fix the bidirectional association issue as explained in the linked article, and the problem should be fixed.


Top
 Profile  
 
 Post subject: Re: can I filter a collection got using @Query
PostPosted: Thu Sep 28, 2017 2:15 pm 
Newbie

Joined: Fri Sep 01, 2017 2:40 am
Posts: 17
vlad wrote:
The Schedule has a @OneToOne association with UserSchedule via a FK.

But then, the UserSchedule also has @OneToOne to Schedule which is not mappedBy.

That's not how you definie a bidirectional association. Check out this article for more details of how you should do it.

The use IDENTITY is also problematic if the underlying DB supports sequences.

The default EAGER fetching for all @OneToOne and @ManyToOne can cause serious performance problems.

Fix the bidirectional association issue as explained in the linked article, and the problem should be fixed.


I initially had mappedBy on UserSchedule but didn't work and after googleing around, saw a post that have @JoinColumn on both sides but still no luck.

Anyway, I've just made changes to my Schedule and UserSchedule entities.
Code:
@Entity
@Table(name = "schedule")
public class Schedule extends ScheduleBase {
    @Setter
    @Getter
    @OneToOne
    @JoinColumn
    private ProjectLite project;

    @Setter
    @Getter
    private Long lat;

    @Setter
    @Getter
    private Long lon;

    @Setter
    @Getter
    private String title;

    @Setter
    @Getter
    private String description;

    @Getter
    @Formula("(select v.status from voucher v where v.schedule_id = id)")
    private Integer redemptionStatus;

    @Setter
    @Getter
    @JoinColumn(name = "id", referencedColumnName = "schedule_id")
    @OneToOne(fetch = FetchType.LAZY)
    private UserSchedule userSchedule;

@Entity
@Table(name = "user_schedule")
public class UserSchedule extends BaseEntity {
    @Setter
    @Getter(onMethod = @__(@JsonIgnore))
    @Column(name = "user_id")
    private long userId;

    @Setter
    @Getter
    @OneToOne(fetch = FetchType.LAZY, mappedBy = "userSchedule")
    private Schedule schedule;
}


and I still get
Code:
Hibernate: select schedule0_.id as id1_10_, schedule0_.created_at as created_2_10_, schedule0_.end_date as end_date3_10_, schedule0_.end_time as end_time4_10_, schedule0_.frequency as frequenc5_10_, schedule0_.frequency_val as frequenc6_10_, schedule0_.is_active as is_activ7_10_, schedule0_.start_date as start_da8_10_, schedule0_.start_time as start_ti9_10_, schedule0_.updated_at as updated10_10_, schedule0_.description as descrip11_10_, schedule0_.lat as lat12_10_, schedule0_.lon as lon13_10_, schedule0_.project_id as project15_10_, schedule0_.title as title14_10_, (select v.status from voucher v where v.schedule_id = schedule0_.id) as formula1_ from schedule schedule0_ inner join user_schedule userschedu1_ on schedule0_.id=userschedu1_.id limit ?

where I think userschedu1_.id should be userschedu1_.schedule_id,


Top
 Profile  
 
 Post subject: Re: can I filter a collection got using @Query
PostPosted: Fri Sep 29, 2017 1:20 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
If you followed the advice I gave in my article, you would have solved it all along.

So, let's tale this mapping:

Code:
@Setter
@Getter
@JoinColumn(name = "id", referencedColumnName = "schedule_id")
@OneToOne(fetch = FetchType.LAZY)
private UserSchedule userSchedule;


Basically, you are telling Hibernate that the @OneToOne association is done via the id attribute which plays the role of the FK. However, the id is also the PK and the entity identifier. If these two go out of sync, how would Hibernate know which was is the source of truth?

In my article, I said that you should use @MapsId for that.

Here, I suppose you should have simply mapped it as:

Code:
@Entity
@Table(name = "user_schedule")
public class UserSchedule extends BaseEntity {
    @Setter
    @Getter(onMethod = @__(@JsonIgnore))
    @Column(name = "user_id")
    private long userId;

    @Setter
    @Getter
    @JoinColumn(name = "schedule_id")
    private Schedule schedule;
}

@Entity
@Table(name = "schedule")
public class Schedule extends ScheduleBase {

    @Setter
    @Getter
    @OneToOne(fetch = FetchType.LAZY, mappedBy = "schedule")
    @OneToOne(fetch = FetchType.LAZY)
    private UserSchedule userSchedule;
}


That's it!


Top
 Profile  
 
 Post subject: Re: can I filter a collection got using @Query
PostPosted: Fri Sep 29, 2017 1:16 pm 
Newbie

Joined: Fri Sep 01, 2017 2:40 am
Posts: 17
vlad wrote:
If you followed the advice I gave in my article, you would have solved it all along.

So, let's tale this mapping:

Code:
@Setter
@Getter
@JoinColumn(name = "id", referencedColumnName = "schedule_id")
@OneToOne(fetch = FetchType.LAZY)
private UserSchedule userSchedule;


Basically, you are telling Hibernate that the @OneToOne association is done via the id attribute which plays the role of the FK. However, the id is also the PK and the entity identifier. If these two go out of sync, how would Hibernate know which was is the source of truth?

In my article, I said that you should use @MapsId for that.

Here, I suppose you should have simply mapped it as:

Code:
@Entity
@Table(name = "user_schedule")
public class UserSchedule extends BaseEntity {
    @Setter
    @Getter(onMethod = @__(@JsonIgnore))
    @Column(name = "user_id")
    private long userId;

    @Setter
    @Getter
    @JoinColumn(name = "schedule_id")
    private Schedule schedule;
}

@Entity
@Table(name = "schedule")
public class Schedule extends ScheduleBase {

    @Setter
    @Getter
    @OneToOne(fetch = FetchType.LAZY, mappedBy = "schedule")
    @OneToOne(fetch = FetchType.LAZY)
    private UserSchedule userSchedule;
}


That's it!


I thought mappedBy would not matter which entity in @OneToOne relationship, but switching it solves the issue.
Thank you so much for the help vlad.


Top
 Profile  
 
 Post subject: Re: can I filter a collection got using @Query
PostPosted: Fri Sep 29, 2017 1:28 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1628
Location: Romania
You're welcome.

Make sure you read my tutorials if you liked my answer.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 12 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.