8

I have recently started to refactor my project because I had to add an extra column to some of my table. The extra column is an Enum (Pending, or Active). Because of that change I would need now to refactor ALL my queries to only retrieves a row if the status is ACTIVE.

After some research I found that we can annotate an Entity with the @Where annotation. it works fine where I use it on a simple column but my table look like this:

@Where(clause = 'state='ACTIVE'")
@Entity
public class Place {

  @Column(name="id_place")
  private String placeId;

  @Column(name="name")
  private String palceName;

@OneToMany(mappedBy = "place")
    private Set<PlaceTag> placeTag;

  ...
  ...
}

@Where(clause = 'state='ACTIVE'")
@Entity
public class Tag {

  @Column(name="id_tag")
  private String tagId;

  @Column(name="name")
  private String tagName;

   @OneToMany(mappedBy = "tag")
    private Set<PlaceTag> placeTag;
   ... 
   ...
}

@Where(clause = 'poi.state='ACTIVE' AND tag.state='ACTIVE")
@Entity
public class PlaceTag {

  @Column(name="id")
  private String id;

  @ManyToOne(cascade = CascadeType.DETACH, fetch = FetchType.LAZY)
  @JoinColumn(name = "place_id")
  private Place place;

  @ManyToOne(cascade = CascadeType.DETACH, fetch = FetchType.LAZY)
  @JoinColumn(name = "tag_id")
  private Tag tag;

  ...
  ...

}

Now my question would be how can make this statement ONLY return the places and tags that are ACTIVE ?

SELECT pt FROM PlaceTag pt;

Is this possible? Or will I have to write the query Explicitly ?

Thank you

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
Johny19
  • 5,364
  • 14
  • 61
  • 99

1 Answers1

0

As you already discovered, or simply use cases the @Where clause is just fine, but in your case, you want to filter PlaceTag by the place and tag too, so a joined is required in this situation.

So, you can keep the @Where clause for Place and Tag, while for PlaceTags you need to use a JPQL query:

select pt
from PlaceTag pt
join pt.tag t
join pt.place p
where 
    t.state='ACTIVE' and p.state='ACTIVE'

At least until @WhereJoinTable annotation is made to work for many-to-one associations too.

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • Thank you for you answer! I have tried this but it seems hibernate doesn't use the filter. in my database I have set ALL the place to INACTIVE (except 1) and ALL the tags to INACTIVE (except 1) and when I run this query List test = placeTagRepository.findAll(); findAll return me the entire PlaceTag table – Johny19 Nov 27 '14 at 19:33
  • 1
    @Johny19 did you enable the filter using session.enableFilter as per the documentation? – bradleyfitz Nov 27 '14 at 19:46
  • Thank you both for the replies. my project is currently using entityManager. So I did something like this: Session session = entityManager.unwrap(Session.class); session.enableFilter("place_active"); session.enableFilter("tag_active"); but is says:org.hibernate.HibernateException: No such filter configured [place_active] – Johny19 Nov 27 '14 at 20:08
  • Yep, it now doesn't complain about the filter anymore. But still this query @Query("SELECT pt FROM PlaceTag"); return me totality of PlaceTag – Johny19 Nov 27 '14 at 20:46
  • @VladMihalcea Also Is the fact that my relation is ManyToOne as problem? Because in the hibernate filter doc they only talk about OneToMany – Johny19 Nov 27 '14 at 21:34
  • I think that was a @Where limitation only. I think [it should work for ManyToOne](http://stackoverflow.com/questions/10691527/using-filter-with-manytoone-relation). – Vlad Mihalcea Nov 27 '14 at 21:40
  • Yes I had stubbed on that thread but the creator even if the answer is accepted wrote a comment in which he says it s still not working as in my case. It looks like if the filter is not enabled – Johny19 Nov 27 '14 at 22:09
  • 3
    Not working for me either. I think not working answer shouldn't be accepted for not confusing people. – Sanghyun Lee Jul 28 '15 at 08:06