info.michael-simons.eu

Note: The following stuff is not Hibernate specific but is true for every JPA2 provider and falls into the category of JPA criteria query.

Some days ago i just saw this video Christin Gorman about Hibernate. Although i don’t agree with her and i actually do like hibernate as a great tool, there are certainly some flaws and pitfalls in some of the designs, especially if the stuff you want to select isn’t totally easy to get.

I’m writing on a little scheduler whose data model looks like so:

There are tasks that have an one-to-many relation to parameters which are their default parameters. Jobs consists of a task (a many-to-one relation) and optional a list of concrete parameters (an one-to-many relation from jobs to job_parameters).

There is a foreign key that ensures that concrete parameters can only be from the jobs task.

Nothing fancy so far on the database site. The JPA mapping already get’s kinda complicated as i want to reproduce the constraint on job_parameters (on columns parameter_id and task_id) that references a unique constraint on tasks so i must use an @EmbeddedId on the task parameter entity.

That’s not the hard part.

I want a query that returns the names of all default parameter for a given job and have not been used on that job. In SQL i would write it down like so:

SELECT defaultParameter.name FROM sch_jobs jobs 
  JOIN sch_tasks tasks ON tasks.id = jobs.task_id
  JOIN sch_task_parameters defaultParameter ON defaultParameter.task_id = tasks.id
 WHERE jobs.id = :job_id
   AND defaultParameter.name LIKE '%' || '%'
   AND NOT EXISTS (
	   SELECT '' FROM sch_job_parameters concreteParameters
	    WHERE concreteParameters.job_id = jobs.id
	      AND concreteParameters.task_parameter_id = defaultParameter.id
   );

and be done with it.

If i would for whatever reason use a JPA Query along with the typesafe JPA Metamodel the method that gets me the list of unused parameters looks like this:

final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
final CriteriaQuery<SchedulerDefaultParameter> criteriaQuery = cb.createQuery(SchedulerDefaultParameter.class);
 
// The "root" of the query... See how it differs from the result type?
final Root<SchedulerJob> jobs = criteriaQuery.from(SchedulerJob.class);
// Joining to other entities
final Join<SchedulerTask, SchedulerDefaultParameter> defaultParameter = jobs.join(SchedulerJob_.task).join(SchedulerTask_.parameters);
 
// Preparing the subquery for the not exists clause		
final Subquery<SchedulerConcreteParameter> subQuery = criteriaQuery.subquery(SchedulerConcreteParameter.class);
final Root<SchedulerConcreteParameter> concreteParameter = subQuery.from(subQuery.getResultType());
 
return entityManager.createQuery(
		criteriaQuery.select(defaultParameter).where(
				cb.and(
						cb.equal(jobs.get(SchedulerJob_.id), jobId),
						cb.like(cb.function("lower", String.class, defaultParameter.get(SchedulerDefaultParameter_.name)), name.toLowerCase() + "%"),
						cb.not(cb.exists(
								subQuery.select(concreteParameter).where(
										cb.and(
												cb.equal(concreteParameter.get(SchedulerConcreteParameter_.job), jobs),
												cb.equal(concreteParameter.get(SchedulerConcreteParameter_.defaultParameter), defaultParameter)														
										)
								)
							)
						)
				)
		)
).getResultList();

What do i have now? A complete typesafe query that gives me a compilation error as soon as i change the mapping in an incompatible way.

And the price? As soon as i have written this post i forget how this stuff actually works.

As much as i like the automatic generation of standard joins and lookups and stuff that JPA respectively Hibernate in my case does, i’d never advocate to enforce some rules “only use JPA criteria” just for sake of compile time query checking.

I think that many people forget about the one true advantage of using sql: Telling the database what you want to have and not how you want to have it selected…