Hibernate Community • View topic

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

SQL filter cache

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

marvinsouza

Post subject: SQL filter cache

Posted: Wed Jul 07, 2010 5:49 pm

Newbie

Joined: Wed Oct 24, 2007 2:39 pm
Posts: 7

Hello all.
I have a dynamic filter that returns a different number of values according with the logged user.
The first time I execute the filtered query with the 'x' values returned by the filter, everything works normally.
After this, if the result of filter return 'y' values, it happens a DB failure, saying 'the length of parameters is invalid'.
Depuring the hibernate code, he caches the SQL of the query including the parameters to be populated by the filter.

Example:
Filter query:

Code:

select id from table where user = :USER

Query:

Code:

from Entity

In the first execution with the user 'A', hibernate generates:

Code:

select * from entity_table where id in (?)

Assuming the query filter returns 1 result for user 'A'. Everything works fine.

In the second execution with the user 'B', the SQL filter returns 2 results, but the hibernate has in cache:

Code:

select * from entity_table where id in (?)

The correct code would be:

Code:

select * from entity_table where id in (?,?)

When the DB executes the query, it happens a error, because there is less parameters than configured (filter.setParameterList()).

Somebody knows where or what configuration disable this behaviour ?

Thanks in advance

Top

michael.conway

Post subject: Re: SQL filter cache

Posted: Tue Jul 20, 2010 11:41 am

Newbie

Joined: Tue Jul 20, 2010 9:34 am
Posts: 1

I'm suffering the same problem right now and it does look very much like Hibernate is using a cached version of the resulting SQL for the filter rather than re-generating the code.

My problem is pretty similar to yours as described, using an "IN" directive in a filter but changing the number of parameters in the associated parameter list. Once the filter has been activated once it is holding on to the SQL that was generated and then re-using it indefinately. It takes a restart of the application to reset the filter.

I've spent most of the afternoon searching for a solution and can't find one. This looks very much like a bug to me.

I've decided to work around this by using a number of parameters instead of a single "IN" statement. I've posted my work around filter definition here just in case you've not thought of this already and it's of any help.

Code:

@FilterDef(name="ranks", parameters={
            @ParamDef(name="rankCode0", type="java.math.BigDecimal"),
            @ParamDef(name="rankCode1", type="java.math.BigDecimal"),
            @ParamDef(name="rankCode2", type="java.math.BigDecimal"),
            @ParamDef(name="rankCode3", type="java.math.BigDecimal"),
            @ParamDef(name="rankCode4", type="java.math.BigDecimal"),
            @ParamDef(name="rankCode5", type="java.math.BigDecimal"),
            @ParamDef(name="rankCode6", type="java.math.BigDecimal"),
            @ParamDef(name="rankCode7", type="java.math.BigDecimal"),
            @ParamDef(name="rankCode8", type="java.math.BigDecimal"),
            @ParamDef(name="rankCode9", type="java.math.BigDecimal")
            }, defaultCondition="(LABOURGRADECODE = :rankCode0 OR LABOURGRADECODE = :rankCode1 OR LABOURGRADECODE = :rankCode2 OR LABOURGRADECODE = :rankCode3 OR LABOURGRADECODE = :rankCode4 OR LABOURGRADECODE = :rankCode5 OR LABOURGRADECODE = :rankCode6 OR LABOURGRADECODE = :rankCode7 OR LABOURGRADECODE = :rankCode8 OR LABOURGRADECODE = :rankCode9)")

The code above always matches 10 rankCode parameters. I set however many I have, and the remainder are set to unsed values (in this case -1). For instance rankCode0 set to 12345 and rankCode1 through to rankCode9 set to -1.

If anyone has a more workable solution that would be great.

Top

Page 1 of 1

[ 2 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

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