Java using filtering at different models before and after the projection

Question

Consider the following JAVA model for hibernate:

@Entity
@Table
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Long id;

    @Column
    public String firstName;

    @Column
    public String lastName;

    @Column
    public Boolean active;
}

and the following model for API serialization (using spring boot rest controller):

public class PersonVO {
    public Long id;
    public String fullName;
}

What i want is to:

Have some filtering applied at the Person (statically defined)
Have some filtering applied at the PersonVO (get from @RequestParam)

In C# .NET i could make like:

IQueryable<Person> personsQuery = entityFrameworkDbContext.Persons;
// FIRST POINT - Here i could make some predefined filtering like 'only active', 'from the same city'... at the database model
personsQueryWithPreDefinedFilters = personsQuery.Where(person => person.active == true);


IQueryable<PersonVO> personsProjectedToVO = personsQueryWithPreDefinedFilters.Select(person => new PersonVO()
{
    id = person.id,
    fullName = person.firstName + " " + person.lastName
});
// SECOND POINT - At this point i could add more filtering based at PersonVO model
if (!String.IsNullOrWhiteSpace(fullNameRequestParameter)) {
    personsProjectedToVO = personsProjectedToVO.Where(personVO => personVO.FullName == fullNameRequestParameter);
}

// The generated SQL at database is with both where (before and after projection)
List<PersonVO> personsToReturn = personsProjectedToVO.ToList();

What i got in Java is:

CriteriaBuilder cb = this.entityManager.getCriteriaBuilder();
CriteriaQuery<PersonVO> cq = cb.createQuery(PersonVO.class);
Root<Person> root = cq.from(Person.class);
// FIRST POINT - Here i could make some predefined filtering like 'only active', 'from the same city'... at the database model
cq.where(cb.equal(root.get(Person_.active), true));         

Expression<String> fullName = cb.concat(root.get(Person_.firstName), root.get(Person_.lastName));
cq.select(cb.construct(
        PersonVO.class,
        root.get(Person_.id),
        fullName
        ));
// SECOND POINT - At this point i could add more filtering based at PersonVO model??? HOW???
if (fullNameRequestParameter != null) {
    cq.where(cb.equal(fullName, fullNameRequestParameter));
// i only could use based at the fullName expression used, but could i make a Predicate based only on PersonVO model without knowing or having the expression?
}

I want to have separated the "projection to the VO model" from the "where expression" applied to it, but have it indirectly applied if used a projected column (like fullName).

Is this possible in Java? Using what? Criteria? Querydsl? Stream? (don't get necessarily stick to the java sample)

Using `Stream`s you could have done something like - `personList.stream().filter(p -> p.active).map(p -> new PersonV0(p.id, p.firstName + " " + p.lastName)).filter(pv -> pv.fullName.equals(fullNameRequestParameter)).collect(Collectors.toList());` where the `Predicate` used in the `filter` after `map`ping is based on `PersonV0` — Naman, Mar 12 '20 at 03:58
But for streams, all the "query" will be solved at database generating the sql (using hibernate) or it only work with in-memory objects? — jvitor83, Mar 12 '20 at 04:06
The above would work with only in memory objects. Its just a hint of how you can deal with the code in Java and not how you should choose to implement it with hibernate in the picture.(that's why a comment and not an answer) — Naman, Mar 12 '20 at 04:10
Got it! Thanks for the comment @Naman ! I see that this ORM https://speedment.com/stream/ can allow to use `stream()` to query the database. I think this can partially answer my question. But i will keep it open to see if someone can answer that with a concrete example (preferably using hibernate as orm). — jvitor83, Mar 12 '20 at 04:20
Are you sure Entity Framework performs the filter on FullName via SQL (and not in memory)? — Olivier, Mar 14 '20 at 10:23
Yes @Olivier! I am sure. While you don't execute the IQueryable it will keep including the transformation/filtering/etc to generate the SQL and execute on ToList(); — jvitor83, Mar 14 '20 at 16:10
Really? It would need to analyze the code of the closures (which can be arbitrarily complex) to infer the correct SQL statement. It would be pretty hard to do... — Olivier, Mar 14 '20 at 16:35

Eugene Khyst · Answer 1 · 2020-03-16T06:46:22.103

JPA Criteria API doesn't have such functionality. Also, it is not easy to read

JPA Criteria API

In the Criteria API you need to reuse the Expression.

The working code looks like this:

public List<PersonVO> findActivePersonByFullName(String fullName) {
  CriteriaBuilder cb = entityManager.getCriteriaBuilder();
  CriteriaQuery<PersonVO> cq = cb.createQuery(PersonVO.class);
  Root<Person> root = cq.from(Person.class);

  List<Predicate> predicates = new ArrayList<>();
  predicates.add(cb.equal(root.get("active"), true));

  Expression<String> fullNameExp = 
      cb.concat(cb.concat(root.get("firstName"), " "), root.get("lastName"));

  cq.select(cb.construct(
      PersonVO.class,
      root.get("id"),
      fullNameExp
  ));

  if (fullName != null) {
    predicates.add(cb.equal(fullNameExp, fullName));
  }

  cq.where(predicates.toArray(new Predicate[0]));

  return entityManager.createQuery(cq).getResultList();
}

The generated SQL code:

select
    person0_.id as col_0_0_,
    ((person0_.first_name||' ')||person0_.last_name) as col_1_0_ 
from
    person person0_ 
where
    person0_.active=? 
    and (
        (
            person0_.first_name||?
        )||person0_.last_name
    )=?

JPA Criteria API and `@org.hibernate.annotations.Formula`

Hibernate has an annotation org.hibernate.annotations.Formula that can simplify the code a little.

Add to the entity a computed field annotated with @Formula("first_name || ' ' || last_name"):

@Entity
public class Person {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  public Long id;

  @Column
  public String firstName;

  @Column
  public String lastName;

  @Column
  public boolean active;

  @Formula("first_name || ' ' || last_name")
  private String fullName;

  //...getters and setters
}

And in the JPA Criteria API query reference the field fullName:

public List<PersonVO> findActivePersonByFullName(String fullName) {
  CriteriaBuilder cb = entityManager.getCriteriaBuilder();
  CriteriaQuery<PersonVO> cq = cb.createQuery(PersonVO.class);
  Root<Person> root = cq.from(Person.class);

  List<Predicate> predicates = new ArrayList<>();
  predicates.add(cb.equal(root.get("active"), true));

  cq.select(cb.construct(
      PersonVO.class,
      root.get("id"),
      root.get("fullName")
  ));

  if (fullName != null) {
    predicates.add(cb.equal(root.get("fullName"), fullName));
  }

  cq.where(predicates.toArray(new Predicate[0]));

  return entityManager.createQuery(cq).getResultList();
}

And the generated SQL:

select
    person0_.id as col_0_0_,
    person0_.first_name || ' ' || person0_.last_name as col_1_0_ 
from
    person person0_ 
where
    person0_.active=? 
    and person0_.first_name || ' ' || person0_.last_name=?

Hibernate Criteria API

Hibernate Criteria API (deprecated since Hibernate 5.2 in favour of JPA Criteria API) allows to use aliases. But not all databases allows to use aliases (e.g. (full_name || ' ' || last_name) as full_name) in a where clause.

According to the PostgreSQL docs:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

It means the SQL query

select p.id, 
      (p.first_name || ' ' || p.last_name) as full_name 
  from person p
 where p.active = true
   and full_name = 'John Doe'

doesn't work in PostgreSQL.

So, using an alias in a where clause is not an option.

score 0 · Answer 2 · answered Mar 19 '20 at 05:22

public interface PersonVO{
  String getFirstName();
  String getLastName();
}

public interface PersonFullNameView{
  PersonVO getFullName();
}

public interface PersonRepository<Person, Long>{

  @Query("SELECT first_name lastName || ' ' || last_name lastName as fullName" + 
         "FROM Person p" +  
         "WHERE p.active = :active AND p.first_name=:firstName AND" + 
         "p.last_name=:lastname"), nativeQuery = true)
  PersonFullNameView methodName(
                     @Param("active" boolean active, 
                     @Param("firstName") String firstName, 
                     @Param("lastName") String lastNam
                     );

}

Notice that you must call your column names equal to "getters" in interfaces(getFirstName = firstName)

It calls interface-based projection. Then you can create instance of PersonVO:

PersonFullNameView pfnv = repository.methodName(args...);
PersonVo personVO = pfnv.getFullName();

Is that what you needed?

Not entirely. I want to apply the logic at some "Model Based API". But thanks for the answer. — jvitor83, Mar 21 '20 at 01:34

jvitor83 · Accepted Answer · 2020-03-21T01:38:58.657

Using this http://www.jinq.org/ library i could do it and get applied to the hibernate (and consequently database).

JinqJPAStreamProvider jinqJPAStreamProvider = new JinqJPAStreamProvider(this.entityManager.getMetamodel());

JPAJinqStream<Person> personStream = jinqJPAStreamProvider.streamAll(this.entityManager, Person.class);
personStream = personStream.where(person -> person.getFirstName().equals("Joao"));

// The only trouble is that we have to register the Model we want to project to (i believe it could be solved with reflection)
jinqJPAStreamProvider.registerCustomTupleConstructor(PersonVO.class.getConstructor(Long.class, String.class), PersonVO.class.getMethod("getId"), PersonVO.class.getMethod("getFullName"));

JPAJinqStream<PersonVO> personVOStream = personStream.select(person -> new PersonVO(person.getId(), person.getFirstName() + person.getLastName()));
personVOStream = personVOStream.where(person -> person.getFullName().equals("JoaoCarmo"));

List<PersonVO> resultList = personVOStream.toList();

Thanks all for the help!

Java using filtering at different models before and after the projection

3 Answers3

JPA Criteria API

JPA Criteria API and @org.hibernate.annotations.Formula

Hibernate Criteria API

JPA Criteria API and `@org.hibernate.annotations.Formula`