Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JPA Specification equivalent of ResultTransformer.DISTINCT_ROOT_ENTITY?

I was wondering if there's an equivalent of ResultTransformer.DISTINCT_ROOT_ENTITY to use in JPA Specifications? Right now, I'm trying to implement search functionality and I must use JPA Specifications for that (I'm not allowed to use CriteriaQuery or other alternatives).

I have to execute a JOIN in the search query because there's a one to many relationship between entities.

So, right now, I'm doing this:

String type = "admin";
SpecificationBuilder sb = SpecificationBuilder.getInstance();
Specification<WebContentImpl> spec = new Specification<WebContentImpl>() {
    @Override
    public Predicate toPredicate(Root<WebContentImpl> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
        return cb.equal(root.join("users", JoinType.LEFT).get("type"), type);
    }
};
sb.addSpecification(spec);

As you can see, I can include a JOIN. Several users are mapped to a single web content item. However, when there are for example 3 users mapped to 1 web content item, the web content item should only be shown 1 time, but it's shown 3 times. I had this problem in the past, and the flag ResultTransformer.DISTINCT_ROOT_ENTITY helped me out then, but I can't use that functionality now.

Sure, I can implement my own solution to leave out the doubles, but it will be terrible for the performance. And I already tried changing the JoinType, but the result was always the same.

Anyone out there with some tips to help me? :-)

Thanks in advance!

Kind regards, K

like image 264
Braek Avatar asked May 08 '14 10:05

Braek


People also ask

What is criteria Distinct_root_entity?

DISTINCT_ROOT_ENTITY. Each row of results is a distinct instance of the root entity. static String. ROOT_ALIAS. The alias that refers to the "root" entity of the criteria query.

What is the use of JpaSpecificationExecutor?

The JpaSpecificationExecutor<T> interface declares the methods that can be used to invoke database queries that use the JPA Criteria API. This interface has one type parameter T that describes the type of the queried entity.

What is SPring JPA specification?

SPring Data Jpa Specifications helps us to create dynamic queries based on the requirement at run time. Spring Data Jpa Specifications allows a combination of the attributes or properties of a domain or entity class and creates a query.

How to retrieve a distinct product by its name in JPA?

We need to create a method starting with prefix find followed by Distinct and then field name - Example: Consider the following Product entity class and if we want to retrieve a distinct product by its name field then here is the Spring data JPA query method: Let's create a complete example to understand end to end.

What is resulttransformer in JPA?

The ResultTransformer is a very powerful mechanism, allowing you to customize a JPA or Hibernate query result set programmatically.

What is the result size of spring data JPA?

If we omit it, Spring Data JPA assumes a result size of 1. Remembering that we want to know what is the first occupied seat and who is occupying it, we can get it omitting the number in these two ways:

How to use specifications in JPA?

To use Specifications we also have to extend our repository interface with JpaSpecificationExecutor interface. This interface provides methods to execute Specifications. Here's this interface snippet: Let's see an example to understand how that works.


2 Answers

Try using distinct(boolean) in CriteriaQuery.

like image 109
Thomsonshow Avatar answered Oct 13 '22 00:10

Thomsonshow


You can de-duplicate via group by based on primary key column of root table:

cq.groupBy(root.get("id"));

Assuming that WebContentImpl.is is primary key column of that table.

like image 38
luboskrnac Avatar answered Oct 13 '22 01:10

luboskrnac