JPA Criteria Lazy Container - How to extend Filter System?

Flow
1

Hi all.

Im using the JPA Criteria Lazy Container (Version 0.4.3 for now), from the Author Jean-François Lamy (Many thanks).

Now, im working on a filter component, that will build a Window, with the entity properties (and nested properties too), and build a criteria filter.

But I have the following doubts:

Im extending the CriteriaQueryDefinition, and overriding the method defineQuery();

So far, so good. But I wanna know how can I notify the container from changes made to the my Custom QueryDefinition, and so, the container ask again for the defineQuery() method. Is in this method that I build all filters passed from the GUI.
Is this the best approach? Or is better to reeimplement the existing filter system? I Wanna use some features that is not possible to use with him.

And I wanna know to, if I can change the QueryDefinition initially defined on the Container, suposing that a User can initially criate a CriteriaContainer, with a standard CriteriaQueryDefinition object, and so, refresh than, without loose the inicial their initial configuration. This will make possible to use the filter without change the original code that is already in production. I Cant find methods to set a QueryDefinition on the CriteriaContainer.

Here, a working screenshot of the component, but this version is replacing the original container of the AbstractSelect components:



Sorry by the bad english, and many thanks by atention!

2

setQueryDefinition() is a method on the CriteriaQueryFactory (there is a constructor for the container where you can pass a query factory). All that the query factory does is return the query you provide.

The easiest way is to propagate a change to your query is to refresh() the container, it will re-parse the query, reexecute it, and notify the graphical component. All automatic.

Personally, I find it easier to just have one CriteriaQueryDefinition with methods that set/reset my filters.
I.e. I typically add methods setFilterXXX, setFilterYYY and a method “resetFilters”. The method defineQuery creates one predicate for each filter and adds them to a list of predicates. It then converts the list to an array and passes it to the query builder.

3

Understood…
But I want to create a model where will be possible to pass gruops of predicates. In this groups, I will can use complex rules, like in JPQL:

SELECT x FROM ENTITY x WHERE (x.field1 = value1 AND x.field2 = value2 OR x.field3 >= value3) AND (x.field1 = value1 AND x.field2 = value2 OR x.field3 >= value3) OR (x.field1 = value1 AND x.field2 = value2 OR x.field3 >= value3)

Something like that.

If I pass all Predicates as an Array on where() it will consider all with (AND) junction.

In this case, Ive extended CriteriaQueryDefinition, and created a method where is possible to pass a List of a Special type that contains all information necessary to build the Predicate, and know where to put it, etc…

In this moment, im refactorying the GUI, to make possible to define groups of filter rules, etc.

As soon as possible, I will post news here.

Many thanks by your help!

4

Exactly. defineQuery uses the JPA 2.0 definitions as is, and there are no restrictions. I added a simple filtering mechanism because a) there was one in EntityContainer and b) I thought it would be useful. My preference is to write/create/modify a predicate structure, just like you are.

And you are right, If you need a richer structure than just ANDs, you need to gather your information from the interface and convert it to a nested Predicate structure with ANDs and ORs and NOTs and whatever else you need.

Looking forward to your work, jfl
(p.s. you can contact me by e-mail through the address visible on the criteriacontainer.googlecode.com page)

5

Hi Jean.

Now, it works :smiley:

Ive extended the CriteriaQueryDefintion, and prepared the Root query to parse the filters reported by GUI.

In this model, is possible to combine filters with AND or OR, and create groups of filters, and combine them.

This component reads the Class properties recursivelly, to found relations with another entities. In this way, you can filter using fields for another entities too (In many levels). For now, is working, but I will improve, for detect Lists (@ManyToMany, etc) and filter in it too.

Here a screenshot:



Usage:


FilterQueryDefinition<Job>cd = new FilterQueryDefinition<Job>(controller.getDaoEntityManger(), true, 50, Job.class);
CriteriaContainer<Job> container = new CriteriaContainer<Job>(cd);
CriteriaContainerFilter filter = new CriteriaContainerFilter<Job>(container, cd);

searchButton.addListener(new Button.ClickListener() {
            @Override
            public void buttonClick(ClickEvent event) {
                filter.setParentWindow(getApplication().getMainWindow());
                filter.showFilter();
            }
        });

And here, the Query parsed by the JPA Vendor (Hibernate):


LOG:  execute <unnamed>: select job0_.t010_job_iu as t1_7_, 
job0_.t010_data_inicio as t2_7_,job0_.t010_data_termino as t3_7_, 
job0_.t010_job_class as t4_7_, job0_.t010_job_name as t5_7_ from t010_jobs
job0_ cross join t011_job_sentenca_sql jobsentenc1_ 
cross join t003_conexao conexao2_ cross join t002_databases database3_ 
where job0_.t010_job_iu=jobsentenc1_.t011_job_e and
jobsentenc1_.t011_conexao_e=conexao2_.t003_conexao_iu and
conexao2_.t003_database=database3_.t002_database_iu 
and (1=1 and 1=1 and job0_.t010_job_name=$1 and
(job0_.t010_data_inicio between $2 and $3) or 1=1 and database3_.t002_sigla=$4 and
conexao2_.t003_db_port=1521 or 1=1 and database3_.t002_sigla=$5) limit $6

DETAIL:  parameters: $1 = 'test', $2 = '2011-01-01 17:34:42.179', 
$3 = '2011-02-28 17:34:46.18', $4 = 'Oracle', $5 = 'PostgreSQL', $6 = '1'

This is not a finished job yet, and I will need to make more tests, and add more useful resources, like ilimited subgroups of groups, etc… ( This will be hard :slight_smile: ). I will need to support internationationalization too.

If you want to see the sources, I can send…

Att,

6

Good job! That was exactly the intended use, such filters are a pain to do when manipulating textual SQL,

I think it would be even nicer if your FilterQueryDefinition constructor obtained your UI data structure as a parameter – right now it looks like it is hard-wired into FilterQueryDefinition

7

Yes. Ive made it…

On the FilterQueryDefinition exist a method where you can pass a filter structure, that will be used to build the Predicates.


public void setFilterGroups(List<FilterGroup> filterValues) {
        this.filterGroups = filterValues;
    }
    
    public List<FilterGroup> getFilterGroups() {
        return this.filterGroups;
    }


public interface FilterGroup {

    public List<FilterLineValueProvider> getFilterValueProviders();
    public int getGroupAppendClause();
    
}


public interface FilterLineValueProvider {
    public int getWhereConditionClause();
    public int getWhereAppendClause();
    public String getPropertyName();
    public Object getValue1();
    public Object getValue2();
    public Class<?> getPropertyClass();
    public void setFilterGroup(Integer filterGroup);
    public Integer getFilterGroup();
}

If you use the CriteriaContainerFilter, it parses the GUI, and pass this values to the FilterQueryDefinition.
Im making tests, to see if the filter is parsed right, and right translated to the database query, etc.