Criteria Container Joins using EclipseLink

warsame · May 6, 2012, 4:49pm

I am trying to do a simple join between a Department entity and a School entity using the BeanTupleQueryDefinition, After extending this class, I am having no luck understanding how to implement the defineQuery method following the addons example. Below i attempt to create the Root element from the program (the one side of the many-to-one) relationship and join to school, then selecting the school and program variables. I keep getting a NullpointerException with this Query definition not being reached at all.



          */
         @Override
         protected Path<?> defineQuery(
                         CriteriaBuilder cb,
                         CriteriaQuery<?> cq) {
                 
                 // FROM program JOIN School 
				Root<Program> program = cq.from(Program.class);
				Join<School,Program> school =  program.join("school" , JoinType.LEFT);
                 
                 // SELECT task as Task, person as Person, ... 
                 cq.multiselect(school.get("school"),program.get("school"));
                 System.out.println("In here but");
                 return program;  // EclipseLink requires a joined entity for the count
         }

jean-francois.lamy · May 6, 2012, 8:03pm

It may help speed the process if you are able to run a junit or Java main() with your query (no criteriaquery container) and just uses the list() method to see what is produced. I don’t see how what you’re doing is any different from the demo, at first glance.

If that works, I suggest you use the issue reporting for the criteriacontainer project on googlecode and post the null pointer exception there – without the trace I can’t guess.

warsame · May 6, 2012, 10:43pm

I am actually able to now avoid the Nullpointer Exception and just having trouble pulling in records from one table joined to another table with a (One-to-Many) relationship. My schema is as follows:


[b]
[u]

[/u]School
[/b]

school_id

                      (MappedBy = "school")
<set> programs  = new HashSet<Program>;

                      (MappedBy = "school")
<set> departments = new HashSet<Department>;

The stack trace shows that a ReadAllQuery is happening where it references my Department class even though i only want to reference the Program entity.

[EL Fine]
: sql: 2012-05-06 17:34:52.886--ServerSession(1839972036)--Connection(131165903)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--SELECT t0.id, t0.CODE, t0.NAME, t0.version, t1.programID, t1.ACTIVE, t1.CODE, t1.DESCRIPTION, t1.NAME, t1.PROGRAMTYPE, t1.REQUIREDCREDITS, t1.version, t1.SCHOOL_id FROM SCHOOL t0 LEFT OUTER JOIN PROGRAM t1 ON (t1.SCHOOL_id = t0.id), SCHOOL t2 WHERE (t2.id = t1.SCHOOL_id)
[EL Finest]
: connection: 2012-05-06 17:34:52.888--ServerSession(1839972036)--Connection(354961667)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--Connection released to connection pool [read]
.
2012-05-06 17:34:52,896 ["http-bio-8080"-exec-3]
 DEBUG org.springframework.beans.factory.annotation.InjectionMetadata - Processing injected method of bean 'org.bixin.dugsi.domain.School': PersistenceElement for transient javax.persistence.EntityManager org.bixin.dugsi.domain.School.entityManager
2012-05-06 17:34:52,896 ["http-bio-8080"-exec-3]
 DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Returning cached instance of singleton bean 'entityManagerFactory'
2012-05-06 17:34:52,899 ["http-bio-8080"-exec-3]
 DEBUG org.springframework.beans.factory.annotation.InjectionMetadata - Processing injected method of bean 'org.bixin.dugsi.domain.School': PersistenceElement for transient javax.persistence.EntityManager org.bixin.dugsi.domain.School.entityManager
2012-05-06 17:34:52,900 ["http-bio-8080"-exec-3]
 DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Returning cached instance of singleton bean 'entityManagerFactory'
2012-05-06 17:34:52,901 ["http-bio-8080"-exec-3]
 DEBUG org.springframework.beans.factory.annotation.InjectionMetadata - Processing injected method of bean 'org.bixin.dugsi.domain.School': PersistenceElement for transient javax.persistence.EntityManager org.bixin.dugsi.domain.School.entityManager
2012-05-06 17:34:52,901 ["http-bio-8080"-exec-3]
 DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Returning cached instance of singleton bean 'entityManagerFactory'
2012-05-06 17:34:52,902 ["http-bio-8080"-exec-3]
 DEBUG org.springframework.beans.factory.annotation.InjectionMetadata - Processing injected method of bean 'org.bixin.dugsi.domain.Program': PersistenceElement for transient javax.persistence.EntityManager org.bixin.dugsi.domain.Program.entityManager
2012-05-06 17:34:52,903 ["http-bio-8080"-exec-3]
 DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Returning cached instance of singleton bean 'entityManagerFactory'
[EL Finest]
: query: 2012-05-06 17:34:52.904--ServerSession(1839972036)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--Execute query ReadObjectQuery(name="school" referenceClass=School )
2012-05-06 17:34:52,908 ["http-bio-8080"-exec-3]
 DEBUG org.springframework.beans.factory.annotation.InjectionMetadata - Processing injected method of bean 'org.bixin.dugsi.domain.Program': PersistenceElement for transient javax.persistence.EntityManager org.bixin.dugsi.domain.Program.entityManager
2012-05-06 17:34:52,908 ["http-bio-8080"-exec-3]
 DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Returning cached instance of singleton bean 'entityManagerFactory'
[EL Finest]
: transaction: 2012-05-06 17:34:52.908--UnitOfWork(2138845270)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--[EL Finest]
: query: 2012-05-06 17:34:52.91--ServerSession(1839972036)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--Execute query ReadAllQuery(name="departments" referenceClass=Department )
[EL Finest]
: connection: 2012-05-06 17:34:52.911--ServerSession(1839972036)--Connection(32490450)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--Connection acquired from connection pool [read]
.
[EL Finest]
: connection: 2012-05-06 17:34:52.911--ServerSession(1839972036)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--reconnecting to external connection pool
[EL Fine]
: sql: 2012-05-06 17:34:52.912--ServerSession(1839972036)--Connection(606146812)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--SELECT id, ACTIVE, CODE, DESCRIPTION, NAME, version, SCHOOLDEPARTMENT_id FROM DEPARTMENT WHERE (SCHOOLDEPARTMENT_id = ?)
	bind => [1]

[EL Finest]
: connection: 2012-05-06 17:34:52.913--ServerSession(1839972036)--Connection(32490450)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--Connection released to connection pool [read]
.
2012-05-06 17:34:52,914 ["http-bio-8080"-exec-3]
 DEBUG org.springframework.beans.factory.annotation.InjectionMetadata - Processing injected method of bean 'org.bixin.dugsi.domain.Department': PersistenceElement for transient javax.persistence.EntityManager org.bixin.dugsi.domain.Department.entityManager
2012-05-06 17:34:52,914 ["http-bio-8080"-exec-3]
 DEBUG org.springframework.beans.factory.support.DefaultListableBeanFactory - Returning cached instance of singleton bean 'entityManagerFactory'
[EL Finest]
: query: 2012-05-06 17:34:52.915--ServerSession(1839972036)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--Execute query ReadObjectQuery(name="schoolDepartment" referenceClass=School )
[EL Finest]
: query: 2012-05-06 17:34:52.915--ServerSession(1839972036)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--Execute query ReadAllQuery(name="programs" referenceClass=Program )
[EL Finest]
: connection: 2012-05-06 17:34:52.916--ServerSession(1839972036)--Connection(63558014)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--Connection acquired from connection pool [read]
.
[EL Finest]
: connection: 2012-05-06 17:34:52.916--ServerSession(1839972036)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--reconnecting to external connection pool
[EL Fine]
: sql: 2012-05-06 17:34:52.917--ServerSession(1839972036)--Connection(920168739)--Thread(Thread["http-bio-8080"-exec-3,5,main]
)--SELECT programID, ACTIVE, CODE, DESCRIPTION, NAME, PROGRAMTYPE, REQUIREDCREDITS, version, SCHOOL_id FROM PROGRAM WHERE (SCHOOL_id = ?)

jean-francois.lamy · May 6, 2012, 11:22pm

That’s where the mystery part comes in. The join behaviour in EclipseLink JPA is very different, and as far as I am concerned, coming from Hibernate, very unsettling. On the other hand, EclipseLink seems to have richer annotations to control what is actually going on.

The criteria container does nothing special, it is entirely dependent on the whims and fantasies of the underlying container. I suspect that your problem will be solved with EclipseLink-specific annotations. Please share whatever you find, I would love to be able to use EclipseLink proficiently myself, and improve CriteriaContainer further.

warsame · May 6, 2012, 11:46pm

The things is i am able to get the correct records back and even confirmed that the table’s container has the right properties yet adding this table to a layout and showing all columns just never happens. Vaadin timeouts as it keeps trying to Join records that are not there

jean-francois.lamy · May 7, 2012, 12:46am

Vaadin does not do joins. Neither does the container. JPA does, as implemented by EclipseLink. You seem well on your way to figuring out where things get stuck,. I can only, at this point, sit in the stands and encourage you to persevere (as it always happens, at some point the newcomer outdoes the veteran). Should you find anything about the container, I will gladly look at fixing it, with your help. Such is the spirit of open source.

warsame · May 7, 2012, 4:07pm

Jean-Francois,

I appreciate your help on taking a brief look at this. My last question is do i even need to use the
BeanTupleQueryDefinition
to get a container full of records? I guess what I mean to say is there another to just run a SELECT * FROM Entity regardless of what it JOINS to? I just want all rows back from a table which I have done successfully with Entities that have no FK references? What is the usage for the BeanTupleQueryDefinition, is it more for Dynamic Queries rather than a static READ ALL?

jean-francois.lamy · May 7, 2012, 5:22pm

BeanTuple is used when you need to retrieve several items at once (i.e. the columns in your table belong to several entities)
If you want a show all for a single type, but need to restrict by doing joins or where clauses, then you can use CriteriaContainer proper.

warsame · May 9, 2012, 5:52pm

I ended up adding fetch = FetchType.Eager to the OneToMany side of the relationship but i feel this is a performance problem as it loads all records and since my app has several schools with several different programs, departments it would be nice to limit it to a specific school ID. How can i filter in the CriteriaQuery to Join on a specifc ID(Person.schoolID = School.schoolID)

jean-francois.lamy · May 9, 2012, 7:14pm

Just adjust the criteria query accordingly (in the criteria_test application, the examples with “custom” in the class name show this kind of filtering.

			// FROM task LEFT OUTER JOIN PERSON 
			Root<Task> task = (Root<Task>) cq.from(Task.class);
			Join<Task, Person> person = task.join(Task_.assignedTo,JoinType.LEFT); 
			
			// SELECT task as Task, person as Person, ... 
			cq.multiselect(task,person);
			
			// WHERE t.name LIKE nameFilterValue
			final Predicate pred1 = cb.like(
					person.get(Person_.lastName),
					nameFilterValue);
			cq.where(pred1);

			return task;

This is an example of how to restrict the joined entity. In your case, you probably want a equals() predicate.

Another option is to use the Filterable interface, and add a filter. It should work the same (in effect, adding a where clause to the JPA query definition).