Current Guide [4.0]

Download

Add it to the build path of your application:

<dependency>
    <groupId>uaihebert.com</groupId>
    <artifactId>uaiCriteria</artifactId>
    <version>4.0.0</version>
</dependency>


Guide for older versions

Click here if you want to see the documentation to the version 1.x: Guide 1.x

 

Click here if you want to see the documentation to the version 2.x: Guide 2.x

 

Click here if you want to see the documentation to the version 3.x: Guide 3.x

How to use

Add the following code the to your application:

UaiCriteria<YOUR_CLASS> uaiCriteria = UaiCriteriaFactory.createQuery(entityManager, YOUR_ENTITY.class);

In this tutorial the following criteria will be used:

UaiCriteria<Person> uaiCriteria = UaiCriteriaFactory.createQuery(entityManager, Person.class);

The entityManager can be provided by any of the supported of JPA implementation. This API was tested and suports: Hibernate, EclipseLink, OpenJPA and BATOO.

You will need an application which a supported JPA implementation up and running to use the UaiCriteria.


Get a result List or a Single result

You can get a list of objects by:

List<Person> persons = uaiCriteria.getResultList();

If you look at the implementation of this library you will see that the method entityManager.getResultList() is invoked (yes, we are open source ;)).

If you want to get only one result you can use the method bellow:

uaiCriteria.getSingleResult();

This method will invoke the entityManager.getSingleResult() method. Be aware that this method may throw two run time expcetions.
About getSingleResult() your can see it here [in English] [in Portuguese].


Using the = symbol to any kind of object

To use the = symbol you can use the method bellow:

uaiCriteria.andEquals("hairSize", 9f);
uaiCriteria.andEquals("weight", 15.5d);
uaiCriteria.andEquals("houseNumber", 150L);
uaiCriteria.andEquals("id", 1); // id is int
uaiCriteria.andEquals("name", "Anna");
uaiCriteria.andEquals("dateOfBirth", dateOfBirth); // dateOfBirth is a java.util.Date
uaiCriteria.andEquals("birthDay",birthDay); // birthDay is a java.util.Calendar
uaiCriteria.andEquals("isOld", true);
uaiCriteria.andEquals("address", address); // address is a class

Using Greater, Greater Than, Less, Less Than



You can use this method with a double, float, integer, long, date, calendar, string.

uaiCriteria.andGreaterThan("hairSize", 10.4f); // >

uaiCriteria.andGreaterOrEqualTo("height", 11.00d); // >=

uaiCriteria.andLessThan("totalBooksOwned", 30L); // <

uaiCriteria.andLessOrEqualTo("firstJobDate", firstJobDate); // <=

Notice that the value is typed with the “f”, “d”, “L” letters.

Using between


You can use this method with a double, float, integer, long, date, calendar, string

uaiCriteria.andBetween("shoesInCloset", 9, 21); // integer

uaiCriteria.andBetween("totalBooksOwned", 19L, 31L); // long

uaiCriteria.andBetween("height", 9.00d, 12.00d); // double

uaiCriteria.andBetween("weight", 9.00f, 12.00f); // float

uaiCriteria.andBetween("name", "A", "L"); // string

uaiCriteria.andBetween("firstJobDate", dateA, dateB); // date

uaiCriteria.andBetween("birthDayDate", calendarA, calendarB); // calendar

Using String with Like, In, toLowerCase

uaiCriteria.andStringLike("name", "M%");

uaiCriteria.andStringNotLike("name", "%y");

List<String> names = new ArrayList<String>();
names.add("Mark");
names.add("John");
uaiCriteria.andStringIn("name", names);
uaiCriteria.andStringNotIn("name", names);

// both the entity attribute and the parameter will be lowered
boolean toLowerCase = true;
uaiCriteria.andEquals(toLowerCase, "name", "Luke");
uaiCriteria.andBetween(toLowerCase, "name", "A", "L");

// you can use orLike condition
uaiCriteria.orStringLike(toLowerCase, "name", "%oseph%").orStringLike("name", "Mar%");


// you will find several methods that will allow the toLowerCase option

Inner Join and Inner Join Fetch

uaiCriteria.innerJoin("dogs");
uaiCriteria.innerJoinFetch("dogs");
uaiCriteria.innerJoinFetch("dogs.toys");
uaiCriteria.innerJoinFetch("dogs.toys.manufacturer");
// Check the Join Functions tab to see how to use the join methods

Left Join and Left Join Fetch

uaiCriteria.leftJoin("dogs");
uaiCriteria.leftJoinFetch("dogs");
uaiCriteria.leftJoinFetch("dogs.toys");
uaiCriteria.leftJoinFetch("dogs.toys.manufacturer");
// Check the Join Functions tab to see how to use the join methods

Join Functions

It is possible to do joins and use

uaiCriteria.innerJoin("dogs"); // you must create a join before using it
uaiCriteria.andEquals("dogs.age", 15); // =
uaiCriteria.andStringLike("dogs.name", "%y"); // like '%value%'
uaiCriteria.andCollectionIsEmpty("dogs.foods", "bones");// is empty

uaiCriteria.innerJoin("dogs..toys.manufacturer"); // you must create a join before using it
uaiCriteria.andStringIn("dogs.toys.manufacturer.name", names); // value in (x, i, z, ...)
uaiCriteria.andBetween("dogs.toys.manufacturer.foundationDate", startDate, finishDate);

Distinct result

uaiCriteria.setDistinctTrue();

Is Null

// String
uaiCriteria.andIsNull("nickName");
uaiCriteria.andIsNotNull("nickName");

// Calendar or Date
uaiCriteria.andIsNull("birthDay");
uaiCriteria.andIsNotNull("birthDay");

// int
uaiCriteria.andIsNull("age");
uaiCriteria.andIsNotNull("age");

// an object OneToOne
uaiCriteria.andIsNull("car");
uaiCriteria.andIsNotNull("car");

// Enum
uaiCriteria.andIsNull("doorType");
uaiCriteria.andIsNotNull("doorType");

Collection, Set, List Is Empty


This method should be used with java.util.Map, java.util.Collection, java.util.Set and java.util.List

// List
uaiCriteria.andCollectionIsEmpty("dogs");
uaiCriteria.andCollectionIsNotEmpty("dogs");

// Collection
uaiCriteria.andCollectionIsEmpty("tires");
uaiCriteria.andCollectionIsNotEmpty("tires");

// Set
uaiCriteria.andCollectionIsEmpty("cars");
uaiCriteria.andCollectionIsNotEmpty("cars");

// Map
uaiCriteria.andCollectionIsEmpty("entityMap");
uaiCriteria.andCollectionIsNotEmpty("entityMap");

Order by asc/desc

uaiCriteria.orderByAsc("age");
uaiCriteria.orderByDesc("name");
// OR
uaiCriteria.orderByAsc("age").orderByDesc("name");

Linking commands


You can link the codes like:

uaiCriteria.andEquals("houseNumber", 150L).orderByAsc("age");
uaiCriteria.andIsNull("birthDay").leftJoinFetch("dogs");

Query Pagination


You can paginate your query with UaiCriteria:

UaiCriteria<Dog> eascyCriteria = UaiCriteriaFactory.createQueryCriteria(getEntityManager(), Dog.class);
eascyCriteria.setFirstResult(0);
eascyCriteria.setMaxResults(5);
eascyCriteria.getResultList()

After you get the result, the pagination values are reseted.

Using OR


You can use OR in several ways.

1) To create simple ors like:

select s from Song s where s.id = 1 or s.length = 40 or s.artist = 'Group 1 Crew'

Just create the code below

UaiCriteria<Song> uaiCriteria = UaiCriteriaFactory.createQueryCriteria(getEntityManager(), Song.class);

uaiCriteria.orEquals("id", 1).orEquals("length", 40).orEquals("artist", "Group 1 Crew");

It is possible to do they query bellow:

select s from Song s where (s.id = 1) or (s.id = 2) or (s.length = 40) or (s.artist = 'Group 1 Crew')

With the code

uaiCriteria.orEquals("id", 1, 2).orEquals("length", 40).orEquals("artist", "Group 1 Crew");

2) You can mix AND with OR:

select s from Song s where s.artist = 'Red' and (s.id = 11 or s.id = 12 or s.id = 13)

With the code:

uaiCriteria.andEquals("artist", "Red").orEquals("id", 11, 12, 13);

3) You can use several AND with OR.

This is what we call complex or. Take a look in the query bellow:

select s from Song s where (s.totalDownloads = 20 or s.weight = 10.00) and (s.price = 20.00 or s.type = :type)

We can see two groups of ORs, a first group with totalDownloads and weight and a second group with price and type. In order to use this methods properly the UaiCriteria can group “ORs” like below:

uaiCriteria.orEquals(1, "totalDownloads", 20L).orEquals(1, "weight", 10.00f).orEquals(2, "price", 20.00d).orEquals(2, "type", SongType.PRAISE);

Notice that we can find a number that is the index in each OR. These numbers represent the index that the OR belongs to.

The default index, is always one, so the criteria above could be used like:

uaiCriteria.orEquals("totalDownloads", 20L).orEquals("weight", 10.00f).orEquals(2, "price", 20.00d).orEquals(2, "type", SongType.PRAISE);

4) AND separated by OR

It is possible to use AND with OR between it. Take a look at the query below:

select s from Song s where (s.id = 1 and s.name = 'Sing Out') or (s.id = 2 and s.name = 'Alive')

This would be translated to UaiCriteria like this:

uaiCriteria.addAndSeparatedByOr(1, "id", 1).addAndSeparatedByOr(1, "name", "Sing Out").addAndSeparatedByOr(2, "id", 2).addAndSeparatedByOr(2, "name", "Alive");

The index is used again to split the AND groups.

Criteria Transfer Object – CTO


It is possible to add all parameters in the view layer to an object, and run it in the business layer later.

Imagine the code below will be created in the Controller class:

UaiCriteria uaiCTO = UaiCriteriaFactory.createEasyCTO();
uaiCTO.innerJoinFetch("dogs");
uaiCTO.andEquals("weight", 10f);
uaiCTO.andEquals("name", "Mary");

No Entity Manager is needed to create a CTO object, and the CTO API is the same of the UaiCriteria used in the query.

The advantage of this approach is how do we send the parameters to the DAO/Repository. It will be easier to send a UaiCriteria object than several parameters as argument.

And to use it in the DAO/Repository just do as below:

UaiCriteria uaiCriteria = UaiCriteriaFactory.createQueryCriteria(getEntityManager(), Person.class, uaiCTO);
Person person = uaiCriteria.getSingleResult();

All the parameters added in the view layer will automatically be configured in the query.

Once the parameters are set, just run it. =D


Count Regular Criteria


It is possible to count the result of a criteria.

Imagine the code below:

UaiCriteria uaiCriteria = UaiCriteriaFactory.createQueryCriteria(getEntityManager(), Manufacturer.class);
uaiCriteria.innerJoin("products.nickNames");
uaiCriteria.andEquals("products.nickNames.name", "JungleFight");
uaiCriteria.setFirstResult(0);
uaiCriteria.setMaxResults(5);

Just a regular query but with a limited size of results. What if you wanted to know the size of all results that could be found in the database?

With the uaiCriteria object created above, just do:

uaiCriteria.countRegularCriteria();

The count method ignores the firstResult and maxResults.

Regular Criteria is a criteria that will count only entities, and not a tuple query. For this type of count there is no need to do a group by.

Member Of


If you want to execute a JPQL with Member Of like:

select p from Person p sub where :dog member of p.dogList
uaiCriteria.andIsMemberOf(dog, "dogList");

// you have the not member of available 
uaiCriteria.andIsNotMemberOf(dog, "dogList");

SubQuery with AttributeIn


If you want to execute a JPQL with SubQuery like:

select p from Person p
where p.id in 
    (select e.personId from Email e where e.status = 'NOT_READ')

You will need to create an SubQuery instance and create a code like:

final UaiCriteria<Person> uaiCriteria = UaiCriteriaFactory.createQueryCriteria(entityManager, Person.class);

final UaiCriteria<Email> subQuery = uaiCriteria.subQuery("personId", Email.class);
subQuery.andEquals("status", 'NOT_READ');

uaiCriteria.andAttributeIn("id", subQuery);

The attribute that is passed in the subQuery method is the value that will be returned by the subQuery. With the subQuery you can do several actions like join, comparison methods, etc.

SubQuery is not doing any kind of arithmetic function like sum, avg, etc. UaiCriteriaCTO is not allowed to do SubQuery [YET]. This new method still experimental, so we are taking small steps about it.

MultiSelect Query


If you want to execute a JPQL selecting with attributes to return, like:

select p.name, p.age, p.nickname from Person p

You will need to create a MultiSelect criteria like:

final UaiCriteria<Person> uaiCriteria = UaiCriteriaFactory.createMultiSelectCriteria(entityManager, Person.class);

uaiCriteria.addMultiSelectAttribute("name")
           .addMultiSelectAttribute("age")
           .addMultiSelectAttribute("nickname");

final List multiselectList = uaiCriteria.getMultiSelectResult();

Notice that you will receive a List of objects (or object[], it is up to the JPA provider). It is not possible to use a Wrapper class [YET]. EclipseLink is returning a Vector instead of a List.


CountAttributes


If you want to execute a JPQL that will count an attribute like:

select count(e.amount), e.status from Email e group by e.status

You will need to create a MultiSelect criteria like:

final UaiCriteria<Email> uaiCriteria = UaiCriteriaFactory.createMultiSelectCriteria(entityManager, Email.class);

uaiCriteria.countAttribute("amount")
                .addMultiSelectAttribute("status")
                .groupBy(status);

final List multiselectList = uaiCriteria.getMultiSelectResult();

Notice that you will receive a List of objects (or object[], it is up to the JPA provider). It is not possible to use a Wrapper class [YET]. EclipseLink is returning a Vector instead of a List.

Sum


If you want to execute a JPQL that will sum an attribute like:

select sum(e.amount), e.status from Email e group by e.status

You will need to create a MultiSelect criteria like:

final UaiCriteria<Email> uaiCriteria = UaiCriteriaFactory.createMultiSelectCriteria(entityManager, Email.class);

uaiCriteria.sum("amount")
                .addMultiSelectAttribute("status")
                .groupBy(status);

final List multiselectList = uaiCriteria.getMultiSelectResult();

Notice that you will receive a List of objects (or object[], it is up to the JPA provider). It is not possible to use a Wrapper class [YET]. EclipseLink is returning a Vector instead of a List.
This method have overloaded methods that you can use like:

uaiCriteria.sum("amount", 10); //can use any number type

// or 

uaiCriteria.sum(10, "amount"); //can use any number type

Diff


If you want to execute a JPQL that will diff an attribute like:

select e.deliveredTime - e.sentTime from Email e group by e.status

You will need to create a MultiSelect criteria like:

final UaiCriteria<Email> uaiCriteria = UaiCriteriaFactory.createMultiSelectCriteria(entityManager, Email.class);

uaiCriteria.diff("deliveredTime", "sentTime");

final List multiselectList = uaiCriteria.getMultiSelectResult();

Notice that you will receive a List of objects (or object[], it is up to the JPA provider). It is not possible to use a Wrapper class [YET]. EclipseLink is returning a Vector instead of a List.
This method have overloaded methods that you can use like:

uaiCriteria. diff("deliveredTime", 10); //can use any number type

// or 

uaiCriteria.diff(10, "deliveredTime"); //can use any number type


Multiply


If you want to execute a JPQL that will multiply an attribute like:

select e.deliveredTime * e.sentTime from Email e group by e.status

You will need to create a MultiSelect criteria like:

final UaiCriteria<Email> uaiCriteria = UaiCriteriaFactory.createMultiSelectCriteria(entityManager, Email.class);

uaiCriteria.multiply("deliveredTime", "sentTime");

final List multiselectList = uaiCriteria.getMultiSelectResult();

Notice that you will receive a List of objects (or object[], it is up to the JPA provider). It is not possible to use a Wrapper class [YET]. EclipseLink is returning a Vector instead of a List.
This method have overloaded methods that you can use like:

uaiCriteria. multiply("deliveredTime", 10); // can use any number type

// or 

uaiCriteria.multiply(10, "deliveredTime");  // can use any number type


Divide


If you want to execute a JPQL that will divide an attribute like:

select e.deliveredTime / e.sentTime from Email e group by e.status

You will need to create a MultiSelect criteria like:

final UaiCriteria<Email> uaiCriteria = UaiCriteriaFactory.createMultiSelectCriteria(entityManager, Email.class);

uaiCriteria.divide("deliveredTime", "sentTime");

final List multiselectList = uaiCriteria.getMultiSelectResult();

Notice that you will receive a List of objects (or object[], it is up to the JPA provider). It is not possible to use a Wrapper class [YET]. EclipseLink is returning a Vector instead of a List.
This method have overloaded methods that you can use like:

uaiCriteria. divide("deliveredTime", 10); // can use any number type

// or 

uaiCriteria.divide(10, "deliveredTime");  // can use any number type

Module


If you want to execute a JPQL that will module an attribute like:

select e.deliveredTime % e.sentTime from Email e group by e.status

You will need to create a MultiSelect criteria like:

final UaiCriteria<Email> uaiCriteria = UaiCriteriaFactory.createMultiSelectCriteria(entityManager, Email.class);

uaiCriteria.module("deliveredTime", "sentTime");

final List multiselectList = uaiCriteria.getMultiSelectResult();

Notice that you will receive a List of objects (or object[], it is up to the JPA provider). It is not possible to use a Wrapper class [YET]. EclipseLink is returning a Vector instead of a List.
This method have overloaded methods that you can use like:

uaiCriteria. module("deliveredTime", 10); // can use any number type

// or 

uaiCriteria.module(10, "deliveredTime");  // can use any number type


Average


If you want to execute a JPQL that will average an attribute like:

select average(e.deliveredTime) from Email e group by e.status

You will need to create a MultiSelect criteria like:

final UaiCriteria<Email> uaiCriteria = UaiCriteriaFactory.createMultiSelectCriteria(entityManager, Email.class);

uaiCriteria.average("deliveredTime");

final List multiselectList = uaiCriteria.getMultiSelectResult();

Notice that you will receive a List of objects (or object[], it is up to the JPA provider). It is not possible to use a Wrapper class [YET]. EclipseLink is returning a Vector instead of a List.


Square


If you want to execute a JPQL that will square an attribute like:

select square(e.deliveredTime) from Email e group by e.status

You will need to create a MultiSelect criteria like:

final UaiCriteria<Email> uaiCriteria = UaiCriteriaFactory.createMultiSelectCriteria(entityManager, Email.class);

uaiCriteria.square("deliveredTime");

final List multiselectList = uaiCriteria.getMultiSelectResult();

Notice that you will receive a List of objects (or object[], it is up to the JPA provider). It is not possible to use a Wrapper class [YET]. EclipseLink is returning a Vector instead of a List.