CODEWITHSUNDEEP
hibernatespring-mvc
klausch
klausch

Reputation: 662

Hibernate query returns desired result but sometimes empty list on same data

For my (Spring/Hibernate 4.3.11) application for an activity club organizing in- and outdoor activities organizeld by and for members, I have made a Hibernate query against my Activity table which should return the Activities for which the logged in member is the organizer or one of the participants. Conceptually it could be called something like "my activities".

The HQL looks like:

select a from Activity as a where (a.organizer = :member or :member in elements(a.participants)) order by a.dateFrom asc, a.timeFrom asc

After starting the application the query returns the expected result. It appears that a bunch of separate SQL queries are generated (the conversion from HQL to SQL still is somewhat mysterious to me!)

After some clicking around in the application, suddenly the query did not return any results anymore. No data alterations were done.

It appears that the problem occurs after clicking on the Edit link for an activity which triggers the following code:

public String getActivityForm(@ModelAttribute("loggedinMember") Member loggedinMember, @PathVariable("id") int id, ModelMap model) {
    Activity activity = service.findActivityById(id);
    if (! activity.getOrganizer().equals(loggedinMember)) {
        //throw exception, you can only edit an activity organized by you
        System.out.println("You are not the organizer of this activity!");
    }
    model.addAttribute("activity", activity);
    return "activityform";  
}

Nothing strange is done here so I really do not grap what could trigger the issue.

I looked at the generated SQL and this is much shorter in the case no result is returned. The SQL blocks are pretty long but I post them below. Apparently separate SQL queries are generated for each row! I am really lost in this, and the issue occurred for the first time after I went to a newer version of Hibernate. So any ideas...?

SQL generating desired result:

Hibernate: 
    select
        activityca0_.id as id1_1_,
        activityca0_.description as descript2_1_ 
    from
        activitycategory activityca0_
Hibernate: 
    select
        activityre0_.id as id1_2_,
        activityre0_.description as descript2_2_ 
    from
        activityregion activityre0_
Hibernate: 
    select
        activity0_.id as id1_0_,
        activity0_.category_id as categor10_0_,
        activity0_.date_from as date_fro2_0_,
        activity0_.date_to as date_to3_0_,
        activity0_.description as descript4_0_,
        activity0_.meetingpoint as meetingp5_0_,
        activity0_.organizer_id as organiz11_0_,
        activity0_.price as price6_0_,
        activity0_.region_id as region_12_0_,
        activity0_.time_from as time_fro7_0_,
        activity0_.time_to as time_to8_0_,
        activity0_.title as title9_0_ 
    from
        activity activity0_ 
    where
        activity0_.organizer_id=? 
        or ? in (
            select
                participan1_.member_id 
            from
                member_activity_subscription participan1_ 
            where
                activity0_.id=participan1_.activity_id
        ) 
    order by
        activity0_.date_from asc,
        activity0_.time_from asc
Hibernate: 
    select
        activityca0_.id as id1_1_0_,
        activityca0_.description as descript2_1_0_ 
    from
        activitycategory activityca0_ 
    where
        activityca0_.id=?
Hibernate: 
    select
        member0_.id as id1_3_0_,
        member0_.active as active2_3_0_,
        member0_.birthdate as birthdat3_3_0_,
        member0_.city as city4_3_0_,
        member0_.email as email5_3_0_,
        member0_.firstname as firstnam6_3_0_,
        member0_.interests as interest7_3_0_,
        member0_.lastname as lastname8_3_0_,
        member0_.lastname_prefix as lastname9_3_0_,
        member0_.loginname as loginna10_3_0_,
        member0_.mobilephone as mobilep11_3_0_,
        member0_.password as passwor12_3_0_,
        member0_.summary as summary13_3_0_ 
    from
        member member0_ 
    where
        member0_.id=?
Hibernate: 
    select
        activityre0_.id as id1_2_0_,
        activityre0_.description as descript2_2_0_ 
    from
        activityregion activityre0_ 
    where
        activityre0_.id=?
Hibernate: 
    select
        activityca0_.id as id1_1_0_,
        activityca0_.description as descript2_1_0_ 
    from
        activitycategory activityca0_ 
    where
        activityca0_.id=?
Hibernate: 
    select
        member0_.id as id1_3_0_,
        member0_.active as active2_3_0_,
        member0_.birthdate as birthdat3_3_0_,
        member0_.city as city4_3_0_,
        member0_.email as email5_3_0_,
        member0_.firstname as firstnam6_3_0_,
        member0_.interests as interest7_3_0_,
        member0_.lastname as lastname8_3_0_,
        member0_.lastname_prefix as lastname9_3_0_,
        member0_.loginname as loginna10_3_0_,
        member0_.mobilephone as mobilep11_3_0_,
        member0_.password as passwor12_3_0_,
        member0_.summary as summary13_3_0_ 
    from
        member member0_ 
    where
        member0_.id=?
Hibernate: 
    select
        activityre0_.id as id1_2_0_,
        activityre0_.description as descript2_2_0_ 
    from
        activityregion activityre0_ 
    where
        activityre0_.id=?
Hibernate: 
    select
        activityca0_.id as id1_1_0_,
        activityca0_.description as descript2_1_0_ 
    from
        activitycategory activityca0_ 
    where
        activityca0_.id=?
Hibernate: 
    select
        member0_.id as id1_3_0_,
        member0_.active as active2_3_0_,
        member0_.birthdate as birthdat3_3_0_,
        member0_.city as city4_3_0_,
        member0_.email as email5_3_0_,
        member0_.firstname as firstnam6_3_0_,
        member0_.interests as interest7_3_0_,
        member0_.lastname as lastname8_3_0_,
        member0_.lastname_prefix as lastname9_3_0_,
        member0_.loginname as loginna10_3_0_,
        member0_.mobilephone as mobilep11_3_0_,
        member0_.password as passwor12_3_0_,
        member0_.summary as summary13_3_0_ 
    from
        member member0_ 
    where
        member0_.id=?
Hibernate: 
    select
        activityca0_.id as id1_1_0_,
        activityca0_.description as descript2_1_0_ 
    from
        activitycategory activityca0_ 
    where
        activityca0_.id=?
Hibernate: 
    select
        activityre0_.id as id1_2_0_,
        activityre0_.description as descript2_2_0_ 
    from
        activityregion activityre0_ 
    where
        activityre0_.id=?
Hibernate: 
    select
        activityca0_.id as id1_1_0_,
        activityca0_.description as descript2_1_0_ 
    from
        activitycategory activityca0_ 
    where
        activityca0_.id=?
Hibernate: 
    select
        participan0_.activity_id as activity1_0_0_,
        participan0_.member_id as member_i2_4_0_,
        member1_.id as id1_3_1_,
        member1_.active as active2_3_1_,
        member1_.birthdate as birthdat3_3_1_,
        member1_.city as city4_3_1_,
        member1_.email as email5_3_1_,
        member1_.firstname as firstnam6_3_1_,
        member1_.interests as interest7_3_1_,
        member1_.lastname as lastname8_3_1_,
        member1_.lastname_prefix as lastname9_3_1_,
        member1_.loginname as loginna10_3_1_,
        member1_.mobilephone as mobilep11_3_1_,
        member1_.password as passwor12_3_1_,
        member1_.summary as summary13_3_1_ 
    from
        member_activity_subscription participan0_ 
    inner join
        member member1_ 
            on participan0_.member_id=member1_.id 
    where
        participan0_.activity_id=?
Hibernate: 
    select
        participan0_.activity_id as activity1_0_0_,
        participan0_.member_id as member_i2_4_0_,
        member1_.id as id1_3_1_,
        member1_.active as active2_3_1_,
        member1_.birthdate as birthdat3_3_1_,
        member1_.city as city4_3_1_,
        member1_.email as email5_3_1_,
        member1_.firstname as firstnam6_3_1_,
        member1_.interests as interest7_3_1_,
        member1_.lastname as lastname8_3_1_,
        member1_.lastname_prefix as lastname9_3_1_,
        member1_.loginname as loginna10_3_1_,
        member1_.mobilephone as mobilep11_3_1_,
        member1_.password as passwor12_3_1_,
        member1_.summary as summary13_3_1_ 
    from
        member_activity_subscription participan0_ 
    inner join
        member member1_ 
            on participan0_.member_id=member1_.id 
    where
        participan0_.activity_id=?
Hibernate: 
    select
        participan0_.activity_id as activity1_0_0_,
        participan0_.member_id as member_i2_4_0_,
        member1_.id as id1_3_1_,
        member1_.active as active2_3_1_,
        member1_.birthdate as birthdat3_3_1_,
        member1_.city as city4_3_1_,
        member1_.email as email5_3_1_,
        member1_.firstname as firstnam6_3_1_,
        member1_.interests as interest7_3_1_,
        member1_.lastname as lastname8_3_1_,
        member1_.lastname_prefix as lastname9_3_1_,
        member1_.loginname as loginna10_3_1_,
        member1_.mobilephone as mobilep11_3_1_,
        member1_.password as passwor12_3_1_,
        member1_.summary as summary13_3_1_ 
    from
        member_activity_subscription participan0_ 
    inner join
        member member1_ 
            on participan0_.member_id=member1_.id 
    where
        participan0_.activity_id=?
Hibernate: 
    select
        participan0_.activity_id as activity1_0_0_,
        participan0_.member_id as member_i2_4_0_,
        member1_.id as id1_3_1_,
        member1_.active as active2_3_1_,
        member1_.birthdate as birthdat3_3_1_,
        member1_.city as city4_3_1_,
        member1_.email as email5_3_1_,
        member1_.firstname as firstnam6_3_1_,
        member1_.interests as interest7_3_1_,
        member1_.lastname as lastname8_3_1_,
        member1_.lastname_prefix as lastname9_3_1_,
        member1_.loginname as loginna10_3_1_,
        member1_.mobilephone as mobilep11_3_1_,
        member1_.password as passwor12_3_1_,
        member1_.summary as summary13_3_1_ 
    from
        member_activity_subscription participan0_ 
    inner join
        member member1_ 
            on participan0_.member_id=member1_.id 
    where
        participan0_.activity_id=?
Hibernate: 
    select
        participan0_.activity_id as activity1_0_0_,
        participan0_.member_id as member_i2_4_0_,
        member1_.id as id1_3_1_,
        member1_.active as active2_3_1_,
        member1_.birthdate as birthdat3_3_1_,
        member1_.city as city4_3_1_,
        member1_.email as email5_3_1_,
        member1_.firstname as firstnam6_3_1_,
        member1_.interests as interest7_3_1_,
        member1_.lastname as lastname8_3_1_,
        member1_.lastname_prefix as lastname9_3_1_,
        member1_.loginname as loginna10_3_1_,
        member1_.mobilephone as mobilep11_3_1_,
        member1_.password as passwor12_3_1_,
        member1_.summary as summary13_3_1_ 
    from
        member_activity_subscription participan0_ 
    inner join
        member member1_ 
            on participan0_.member_id=member1_.id 
    where
        participan0_.activity_id=?
Hibernate: 
    select
        participan0_.activity_id as activity1_0_0_,
        participan0_.member_id as member_i2_4_0_,
        member1_.id as id1_3_1_,
        member1_.active as active2_3_1_,
        member1_.birthdate as birthdat3_3_1_,
        member1_.city as city4_3_1_,
        member1_.email as email5_3_1_,
        member1_.firstname as firstnam6_3_1_,
        member1_.interests as interest7_3_1_,
        member1_.lastname as lastname8_3_1_,
        member1_.lastname_prefix as lastname9_3_1_,
        member1_.loginname as loginna10_3_1_,
        member1_.mobilephone as mobilep11_3_1_,
        member1_.password as passwor12_3_1_,
        member1_.summary as summary13_3_1_ 
    from
        member_activity_subscription participan0_ 
    inner join
        member member1_ 
            on participan0_.member_id=member1_.id 
    where
        participan0_.activity_id=?
Aantal activiteiten: 6
Handling GET request, activities = [nl.drsklaus.activiteitensite.model.Activity@8070e879, nl.drsklaus.activiteitensite.model.Activity@ac6b814e, nl.drsklaus.activiteitensite.model.Activity@e5b2a54b, nl.drsklaus.activiteitensite.model.Activity@97726734, nl.drsklaus.activiteitensite.model.Activity@2d0defe, nl.drsklaus.activiteitensite.model.Activity@f2f57489]

SQL generating empty result:

Hibernate: 
    select
        activityca0_.id as id1_1_,
        activityca0_.description as descript2_1_ 
    from
        activitycategory activityca0_
Hibernate: 
    select
        activityre0_.id as id1_2_,
        activityre0_.description as descript2_2_ 
    from
        activityregion activityre0_
Hibernate: 
    select
        activity0_.id as id1_0_,
        activity0_.category_id as categor10_0_,
        activity0_.date_from as date_fro2_0_,
        activity0_.date_to as date_to3_0_,
        activity0_.description as descript4_0_,
        activity0_.meetingpoint as meetingp5_0_,
        activity0_.organizer_id as organiz11_0_,
        activity0_.price as price6_0_,
        activity0_.region_id as region_12_0_,
        activity0_.time_from as time_fro7_0_,
        activity0_.time_to as time_to8_0_,
        activity0_.title as title9_0_ 
    from
        activity activity0_ 
    where
        activity0_.organizer_id=? 
        or ? in (
            select
                participan1_.member_id 
            from
                member_activity_subscription participan1_ 
            where
                activity0_.id=participan1_.activity_id
        ) 
    order by
        activity0_.date_from asc,
        activity0_.time_from asc
Aantal activiteiten: 0
Handling GET request, activities = []

The code for the Agenda (My activities) page looks like:

@RequestMapping(value = "/subscribed", method = RequestMethod.GET)
public String home(@ModelAttribute("loggedinMember") Member member, ModelMap model) {

List<Activity> activities = service.findAllActivitiesForMember(member, true);
System.out.println("Aantal activiteiten: " + activities.size());

System.out.println("Handling GET request, activities = " + activities);     
model.addAttribute("activitiessubscribed", activities);
return "myactivities";
}

The service method is delegated to the dao method which looks as follows:

@Override
public List<Activity> findAllActivitiesForMember(Member member, boolean upcoming) {

String hql = "select a from Activity as a where (a.organizer = :member or :member in elements(a.participants))";

if (upcoming) {
    hql += " order by a.dateFrom asc, a.timeFrom asc";
} else {
    hql += " order by a.dateTo desc, a.timeTo desc";
}

System.out.println("HQL: " + hql);
Query query = this.getSession().createQuery(hql);

query.setEntity("member", member);
return query.list();
}

The following URL's are used:

Visiting MY activity agenda first time (normal results: http://localhost:8080/ActiviteitenSite/activities/subscribed

Selecting a specific activity: http://localhost:8080/ActiviteitenSite/activities/3

Going back (still normal results): http://localhost:8080/ActiviteitenSite/activities/subscribed

Edit an activity (screen shows activity details, no commit is made): http://localhost:8080/ActiviteitenSite/activities/3/edit

Again visiting my agenda (result is now empty): http://localhost:8080/ActiviteitenSite/activities/subscribed

Upvotes: 2

Views: 1283

Answers (1)

klausch
klausch

Reputation: 662

I have found out where the Member object (saved in the session during login) gets currupted: When calling the getActivityForm handler method, the id value of the loggedinMember object (initially 1000) is changed to the @PathVariable id parameter, which is the id of the activity. This really should not happen but it does actually happen in this method. And from then the Member object contains a wrong value for the id attribute and the queries do not work anymore. Still a mysetry how and why this happens.

public String getActivityForm(@ModelAttribute("loggedinMember") Member loggedinMember, @PathVariable("id") int id, ModelMap model) {
Activity activity = service.findActivityById(id);
if (! activity.getOrganizer().equals(loggedinMember)) {
    //throw exception, you can only edit an activity organized by you
    System.out.println("You are not the organizer of this activity!");
}
model.addAttribute("activity", activity);
return "activityform";  
}

EDIT: The problem is indeed that the @PathVariable("id) value is matched with the id property of the @ModelAttribute("loggedinMember") object and the last one is modified. According to the SpringMVC documentation this is expected behaviour but IMHO it is unwanted and highly illogical and I consider it as a severe and nasty Spring MVC bug.

The problem is worked around by renaming the "id" Modelattribute to "activityId" so that this unwanted and unexpected matching does not occur anymore.

@RequestMapping(value = "/{activityId}/edit", method = RequestMethod.GET)
public String getActivityForm(@ModelAttribute("loggedinMember") Member loggedinMember, @PathVariable("activityId") int activityId, ModelMap model) {
Activity activity = service.findActivityById(activityId);
if (! activity.getOrganizer().equals(loggedinMember)) {
        //throw exception, you can only edit an activity organized by you
System.out.println("Je bent niet de organisator van deze activiteit!");
}
model.addAttribute("activity", activity);
   return "activityform";   
}

Upvotes: 1

Related Questions