Java Hibernate Filter not working

Question

I try use Hibernate filters for get User entity with childs (Filter->Ad) entities by insertTime criteria. My goal is get user object with ads where insertTime > (now-7 days). On test mode I use in-memmory HSQLDB.

But test not green:

getNotOldAds(com.gecars.app.dao.DaoTester) java.lang.AssertionError: expected:<1> but was:<2> ...

Test:

@Before 
public void before() throws ParseException {
    sessionFactory = HibernateUtilTest.getSessionFactory();
    session = sessionFactory.openSession();

    dao = new DAOimpl(session);

    GeUser user = new GeUser();
    user.setId(1);
    Filter filter = new Filter();

    Ad ad = new Ad();
    Ad old = new Ad();

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    Date insertTime = sdf.parse("2015-01-01");

    old.setInsertTime(insertTime);

    filter.addAd(ad);
    filter.addAd(old); // should filter this old ad !!!

    user.addFilter(filter);

    dao.insertUser(user);
}

@Test
public void getNotOldAds() throws Exception{        
    GeUser u = dao.getUser(1);
    assertEquals(1, u.getFilters().size());
    assertEquals(1, u.getFilters().get(0).getAds().size()); // red test !!!
}

User:

@Entity
@Table(name="users")
public class GeUser {
   ...
   @OneToMany(fetch = FetchType.LAZY, cascade=CascadeType.ALL, mappedBy="user") 
   @OrderBy("id")
   private Set<Filter> filters = new HashSet<Filter>(0);

   public void addFilter(Filter filter){
      this.filters.add(filter);
   }
   ...

Filter:

@Entity
@Table(name="filters")
@FilterDef(name="time", parameters={
    @ParamDef( name="adTime", type="date" )
})
public class Filter {
   ...
   @ManyToMany(cascade=CascadeType.ALL)
   @FilterJoinTable(name="time", condition=":adTime >= insertTime")
   private Set<Ad> ads; 
   ...

Also tried @Filter annotation:

@Table(name="filters")
@FilterDef(name="time", parameters={
    @ParamDef( name="adTime", type="date" )
})
public class Filter {
       ...
       @ManyToMany(cascade=CascadeType.ALL)
       @org.hibernate.annotations.Filter(name="time", condition=":adTime >= insertTime"
       private Set<Ad> ads; 
       ...
   )

Also try use like class level annotation by source

@Table(name="filters")
@FilterDef(name="time", parameters={
    @ParamDef( name="adTime", type="date" )
})
@Filters( {
    @org.hibernate.annotations.Filter(name="time", condition=":adTime >= insertTime")
} )
    public class Filter {
           ...
           @ManyToMany(cascade=CascadeType.ALL)
           private Set<Ad> ads; 
           ...
       )

Ad:

@Entity
@Table(name="ads")
public class Ad {
   ...
   private Date insertTime = new Date(); // > no less 7 days
   ...

Used DAO method for load user:

public GeUser getUser(int id){
    Criteria criteria = session.createCriteria(GeUser.class);
    DateTime dt = new DateTime();

    // set up filter
    session.enableFilter("time").setParameter("adTime", dt.minusDays(7).toDate());

    criteria.add(Restrictions.eq("id",id));
    @SuppressWarnings("unchecked")
    List<GeUser> results = criteria.list();
    if(results.size()<1)return null;
    return results.get(0);
 }

also tried:

public GeUser getUser(int id){
    DateTime dt = new DateTime();
    // enable filter top of createCriteria method
    session.enableFilter("time").setParameter("adTime", dt.minusDays(7).toDate());

    Criteria criteria = session.createCriteria(GeUser.class);
    criteria.add(Restrictions.eq("id",id));
    @SuppressWarnings("unchecked")
    List<GeUser> results = criteria.list();
    if(results.size()<1)return null;
    return results.get(0);

}

also I tried clear session before create new query, but then I get bottom exception of assertEquals(1, u.getFilters().size());

java.lang.AssertionError: expected:<1> but was:<0>

DAO method with clear session:

public GeUser getUser(int id){
    this.session.clear();
    DateTime dt = new DateTime();
    session.enableFilter("time").setParameter("adTime", dt.minusDays(7).toDate());
    Criteria criteria = session.createCriteria(GeUser.class);
    criteria.add(Restrictions.eq("id",id));
    @SuppressWarnings("unchecked")
    List<GeUser> results = criteria.list();
    if(results.size()<1)return null;
    return results.get(0);
}

Hibernate logs:

HCANN000001: Hibernate Commons Annotations {4.0.5.Final}
HHH000412: Hibernate Core {4.3.10.Final}
HHH000206: hibernate.properties not found
HHH000021: Bytecode provider name : javassist
HHH000044: Configuring from URL: file:/home/gefalko/git/gecars/WebContent/WEB-INF/classes/hibernate_test.cfg.xml
HHH000223: Recognized obsolete hibernate namespace http://hibernate.sourceforge.net/. Use namespace http://www.hibernate.org/dtd/ instead. Refer to Hibernate 3.6 Migration Guide!
HHH000041: Configured SessionFactory: null
HHH000402: Using Hibernate built-in connection pool (not for production use!)
HHH000401: using driver [org.hsqldb.jdbcDriver] at URL [jdbc:hsqldb:mem:test;sql.enforce_size=false]
HHH000046: Connection properties: {user=car, password=****}
HHH000006: Autocommit mode: false
HHH000115: Hibernate connection pool size: 20 (min=1)
HHH000400: Using dialect: org.hibernate.dialect.HSQLDialect
HHH000399: Using default transaction strategy (direct JDBC transactions)
HHH000397: Using ASTQueryTranslatorFactory
HHH000227: Running hbm2ddl schema export
Hibernate: alter table Filter_meta drop constraint FK_edd3775xix70rbhxbo32wodn4
HHH000389: Unsuccessful: alter table Filter_meta drop constraint FK_edd3775xix70rbhxbo32wodn4
user lacks privilege or object not found: PUBLIC.FILTER_META
Hibernate: alter table ads drop constraint FK_4mfrgdti3fshygqyd6ajyyoxt
HHH000389: Unsuccessful: alter table ads drop constraint FK_4mfrgdti3fshygqyd6ajyyoxt
user lacks privilege or object not found: PUBLIC.ADS
Hibernate: alter table ads drop constraint FK_qm2n3257q8fveo1w82ixtw7f4
HHH000389: Unsuccessful: alter table ads drop constraint FK_qm2n3257q8fveo1w82ixtw7f4
user lacks privilege or object not found: PUBLIC.ADS
Hibernate: alter table filters drop constraint FK_6ona1upsnhrimamvy8vn7brm
HHH000389: Unsuccessful: alter table filters drop constraint FK_6ona1upsnhrimamvy8vn7brm
user lacks privilege or object not found: PUBLIC.FILTERS
Hibernate: alter table filters drop constraint FK_ju0ri0fbfi4y6pls6uu59gucl
HHH000389: Unsuccessful: alter table filters drop constraint FK_ju0ri0fbfi4y6pls6uu59gucl
user lacks privilege or object not found: PUBLIC.FILTERS
Hibernate: alter table filters_ads drop constraint FK_gcri6h0918u8o2ybd6yfquk79
HHH000389: Unsuccessful: alter table filters_ads drop constraint FK_gcri6h0918u8o2ybd6yfquk79
user lacks privilege or object not found: PUBLIC.FILTERS_ADS
Hibernate: alter table filters_ads drop constraint FK_koa5ug12bcrgj2tdnbwfecwjw
HHH000389: Unsuccessful: alter table filters_ads drop constraint FK_koa5ug12bcrgj2tdnbwfecwjw
user lacks privilege or object not found: PUBLIC.FILTERS_ADS
Hibernate: alter table models drop constraint FK_fi4sewhe2m2kvmc49kvlnheat
HHH000389: Unsuccessful: alter table models drop constraint FK_fi4sewhe2m2kvmc49kvlnheat
user lacks privilege or object not found: PUBLIC.MODELS
Hibernate: alter table sources_ads drop constraint FK_gggf7jfe42nh1ivcjj7nt6vmy
HHH000389: Unsuccessful: alter table sources_ads drop constraint FK_gggf7jfe42nh1ivcjj7nt6vmy
user lacks privilege or object not found: PUBLIC.SOURCES_ADS
Hibernate: alter table sources_ads drop constraint FK_rlcr0wrn9f3j92agt0dh7ylin
HHH000389: Unsuccessful: alter table sources_ads drop constraint FK_rlcr0wrn9f3j92agt0dh7ylin
user lacks privilege or object not found: PUBLIC.SOURCES_ADS
Hibernate: alter table users_sources drop constraint FK_l4igmoxgnho61ais4w5em2sro
HHH000389: Unsuccessful: alter table users_sources drop constraint FK_l4igmoxgnho61ais4w5em2sro
user lacks privilege or object not found: PUBLIC.USERS_SOURCES
Hibernate: alter table users_sources drop constraint FK_8roy1p4lpl5aggkq2bucqu211
HHH000389: Unsuccessful: alter table users_sources drop constraint FK_8roy1p4lpl5aggkq2bucqu211
user lacks privilege or object not found: PUBLIC.USERS_SOURCES
Hibernate: drop table Filter_meta if exists
Hibernate: drop table ads if exists
Hibernate: drop table auto if exists
Hibernate: drop table filters if exists
Hibernate: drop table filters_ads if exists
Hibernate: drop table models if exists
Hibernate: drop table sources if exists
Hibernate: drop table sources_ads if exists
Hibernate: drop table users if exists
Hibernate: drop table users_sources if exists
Hibernate: create table Filter_meta (Filter_id integer not null, meta varchar(255), meta_KEY varchar(255) not null, primary key (Filter_id, meta_KEY))
Hibernate: create table ads (id integer generated by default as identity (start with 1), foto varchar(255), fuel_type varchar(50), gearbox varchar(50), insertTime timestamp, price integer, url VARCHAR, year integer, model_id integer, source_id integer, primary key (id))
Hibernate: create table auto (id integer generated by default as identity (start with 1), ebayMake varchar(100), gumtreeMake varchar(100), make varchar(100), primary key (id))
Hibernate: create table filters (id integer generated by default as identity (start with 1), fuel varchar(255), priceFrom integer, priceTo integer, yearFrom integer, yearTo integer, fk_model integer, user integer, primary key (id))
Hibernate: create table filters_ads (filters_id integer not null, ads_id integer not null)
Hibernate: create table models (id integer generated by default as identity (start with 1), autotraderModel varchar(100), ebayModel varchar(100), gumtreeModel varchar(100), mclass varchar(100), model varchar(100), series varchar(100), fk_make integer, primary key (id))
Hibernate: create table sources (id integer generated by default as identity (start with 1), collectClass varchar(100), source varchar(100), primary key (id))
Hibernate: create table sources_ads (sources_id integer not null, ads_id integer not null, primary key (sources_id, ads_id))
Hibernate: create table users (id integer generated by default as identity (start with 1), email varchar(100), primary key (id))
Hibernate: create table users_sources (users_id integer not null, sources_id integer not null, primary key (users_id, sources_id))
Hibernate: alter table sources_ads add constraint UK_gggf7jfe42nh1ivcjj7nt6vmy  unique (ads_id)
Hibernate: alter table Filter_meta add constraint FK_edd3775xix70rbhxbo32wodn4 foreign key (Filter_id) references filters
Hibernate: alter table ads add constraint FK_4mfrgdti3fshygqyd6ajyyoxt foreign key (model_id) references models
Hibernate: alter table ads add constraint FK_qm2n3257q8fveo1w82ixtw7f4 foreign key (source_id) references sources
Hibernate: alter table filters add constraint FK_6ona1upsnhrimamvy8vn7brm foreign key (fk_model) references models
Hibernate: alter table filters add constraint FK_ju0ri0fbfi4y6pls6uu59gucl foreign key (user) references users
Hibernate: alter table filters_ads add constraint FK_gcri6h0918u8o2ybd6yfquk79 foreign key (ads_id) references ads
Hibernate: alter table filters_ads add constraint FK_koa5ug12bcrgj2tdnbwfecwjw foreign key (filters_id) references filters
Hibernate: alter table models add constraint FK_fi4sewhe2m2kvmc49kvlnheat foreign key (fk_make) references auto
Hibernate: alter table sources_ads add constraint FK_gggf7jfe42nh1ivcjj7nt6vmy foreign key (ads_id) references ads
Hibernate: alter table sources_ads add constraint FK_rlcr0wrn9f3j92agt0dh7ylin foreign key (sources_id) references sources
Hibernate: alter table users_sources add constraint FK_l4igmoxgnho61ais4w5em2sro foreign key (sources_id) references sources
Hibernate: alter table users_sources add constraint FK_8roy1p4lpl5aggkq2bucqu211 foreign key (users_id) references users
HHH000230: Schema export complete
Hibernate: insert into users (id, email) values (default, ?)
Hibernate: insert into filters (id, fuel, fk_model, priceFrom, priceTo, user, yearFrom, yearTo) values (default, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into ads (id, foto, fuel_type, gearbox, insertTime, model_id, price, source_id, url, year) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into ads (id, foto, fuel_type, gearbox, insertTime, model_id, price, source_id, url, year) values (default, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into filters_ads (filters_id, ads_id) values (?, ?)
Hibernate: insert into filters_ads (filters_id, ads_id) values (?, ?)
Hibernate: select this_.id as id1_8_0_, this_.email as email2_8_0_ from users this_ where this_.id=?
Hibernate: select filters0_.user as user8_8_0_, filters0_.id as id1_3_0_, filters0_.id as id1_3_1_, filters0_.fuel as fuel2_3_1_, filters0_.fk_model as fk_model7_3_1_, filters0_.priceFrom as priceFro3_3_1_, filters0_.priceTo as priceTo4_3_1_, filters0_.user as user8_3_1_, filters0_.yearFrom as yearFrom5_3_1_, filters0_.yearTo as yearTo6_3_1_ from filters filters0_ where filters0_.user=? order by filters0_.id

I've deleted my previous suggestion as answer because, you're right, it doesn't fix your problem. Have you tried this approach? http://www.concretepage.com/hibernate/hibernate-filter-and-filterjointable-annotation-example In these examples filters (and filterjointable) are defined in the Entity and not as a separated class. — RubioRic, Mar 30 '16 at 11:10

score 4 · Accepted Answer · edited May 23 '17 at 12:15

4

Use @Filter instead of @FilterJoinTable:

@ManyToMany(cascade=CascadeType.ALL)
@Filter(name="time", condition=":adTime >= insertTime")
private Set<Ad> ads;

From the documentation:

When the collection use an association table as a relational representation, you might want to apply the filter condition to the association table itself or to the target entity table. To apply the constraint on the target entity, use the regular @Filter annotation. However, if you want to target the association table, use the @FilterJoinTable annotation.

Secondly, you seem not to close the session after the initial setup, so your entity instances remain in the first level cache (meaning the initial collection with 2 elements is still in the cache and is returned from there).

Either create a new session in the test method, or clear the existing one before executing the query:

session.clear();

Thirdly, take a look at this answer. You have to update the owning side of the association, inverse side (the one containing mappedBy) is not synchronized with the database:

public void addFilter(Filter filter){
    this.filters.add(filter);
    filter.setUser(this);
}

edited May 23 '17 at 12:15

Community

1
1

answered Mar 30 '16 at 11:22

Dragan Bozanovic

23,102
5
43
110

@DrganBozanovic i tried clear session but now get size 0 of user.filters set. See the edited post. – Edgaras Karka Mar 31 '16 at 05:50
@EdgarasKarka Please [enable SQL logging](http://stackoverflow.com/questions/2536829/hibernate-show-real-sql/2536835#2536835) and post the generated SQL. – Dragan Bozanovic Mar 31 '16 at 10:41
@DrganBozanovi see the post. – Edgaras Karka Mar 31 '16 at 11:05
@EdgarasKarka Please post the implementation of `addFilter` method in `GeUser` class. – Dragan Bozanovic Mar 31 '16 at 11:15
@DrganBozanovi see the post – Edgaras Karka Mar 31 '16 at 11:35
Thanks, test now green – Edgaras Karka Mar 31 '16 at 11:49
You nailed it, thanks a lot for the solution – Ahmad Khundaqji Aug 29 '21 at 14:47

Java Hibernate Filter not working

1 Answers1