JPA strange behavior when using SELECT

Tags: ,



I am new to Java and try developing a SWing app for library using JPA controller generated.

When I try to select result from sql server database, I use this command

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<BookTitles> cq = criteriaBuilder.createQuery(BookTitles.class);
cq.select(cq.from(BookTitles.class)).where(criteriaBuilder.isNull(cq.from(BookTitles.class).get("status")));

This command, however, returns 9 times of rows in db. For example, if db has 10 rows, it will repeat this 10 rows around 9 times and return a list with 90 elements.

Instead of this code, I changed to

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<BookTitles> cq = criteriaBuilder.createQuery(BookTitles.class);
Root<BookTitles> root = cq.from(BookTitles.class);
cq.select(root).where(criteriaBuilder.isNull(root.get("status")));

and the results will be the same as listed in db.

The only different between these two codes is that instead of passing cq.from(...) directly to select(), I pass result of cq.from(...).

Personally, I donot think there is any differences between these two ways of coding, but the results tell the other way.

Can someone take time to explain?

Answer

It’s not strange behavior

By using the CriteriaBuilder method twice, you are setting two tables in that clause for what the Cartesian product does.

As you can see in the documentation

https://docs.oracle.com/javaee/7/api/javax/persistence/criteria/AbstractQuery.html#from-java.lang.Class-

“Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.”

So the correct way is the second one, storing the table that forms the from clause in a variable, and using this instead of adding more tables to the from clause with the criteriaquery from method.



Source: stackoverflow