In this blog I will add relevant materials for the courses I teach at the University of Coimbra, Portugal

quinta-feira, 14 de maio de 2015

Queries in the Java Persistence Query Language - an Overview

In this message I want to give an overview of the Java Persistence Query Language, by presenting examples that make use of all of the six available clauses. The general structure of a SELECT query is the following (I recommend this url for details):

SELECT...FROM...[WHERE...][GROUPBY...[HAVING...]][ORDERBY...]

SELECT and FROM are mandatory in retrieval queries, the remaining ones are optional (although the SELECT might be left implicit in the queries, as we shall see).

Setup

Before we can run any queries, we need to have persisted data. For the sake of exemplifying, I will use two classes related by a many-to-many relation (see this other message for a different example): a Professor and a Student class. Let us start by the professor. We should notice that we defined a named query, called "orderProfs", which we will use ahead. We need a @Temporal annotation to convert from java.util.Date to the database date format and vice-versa. The other annotations are sort of standard, @Id for the primary key, @GeneratedValue, to let the database generate ids automatically, and @ManyToMany to define the number of the relation with the students. This means that a professor may have any number of students and vice-versa, i.e., each student may have any number of professors (including zero).

package data;

import java.util.Date;

import java.util.List;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.ManyToMany;

import javax.persistence.NamedQueries;

import javax.persistence.NamedQuery;

import javax.persistence.Temporal;

import javax.persistence.TemporalType;

@Entity

@NamedQueries({

@NamedQuery(name="orderProfs", query="select p from Professor p order by p.name")

}

)

publicclass Professor {

@Id@GeneratedValue(strategy=GenerationType.AUTO)

privateintid;

@Temporal(TemporalType.DATE)

private Date birthdate;

privatefloatsalary;

private String name;

@ManyToMany(mappedBy="profs")

private List<Student> students;

public Professor() {}

public Professor(String name, Date birthdate, floatsalary) {

super();

this.name = name;

this.birthdate = birthdate;

this.salary = salary;

}

public List<Student> getStudents() {

returnstudents;

}

publicvoid setStudents(List<Student> students) {

this.students = students;

}

publicint getId() {

returnid;

}

publicvoid setId(intid) {

this.id = id;

}

public Date getBirthdate() {

returnbirthdate;

}

publicvoid setBirthdate(Date birthdate) {

this.birthdate = birthdate;

}

publicfloat getSalary() {

returnsalary;

}

publicvoid setSalary(floatsalary) {

this.salary = salary;

}

public String getName() {

returnname;

}

publicvoid setName(String name) {

this.name = name;

}

@Override

public String toString() {

returnthis.name;

}

}

Now the Student class, which is simpler. We just have a name and an average (grade), which we will use for a few more complex queries.

package data;

import java.util.ArrayList;

import java.util.List;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import javax.persistence.ManyToMany;

@Entity

publicclass Student {

@Id@GeneratedValue(strategy=GenerationType.AUTO)

privateintid;

private String name;

privateintaverage;

@ManyToMany

private List<Professor> profs;

public Student() {}

public Student(String name, intaverage) {

super();

this.name = name;

this.average = average;

}

publicint getId() {

returnid;

}

publicvoid setId(intid) {

this.id = id;

}

public String getName() {

returnname;

}

publicvoid setName(String name) {

this.name = name;

}

public List<Professor> getProfs() {

returnprofs;

}

publicvoid setProfs(List<Professor> profs) {

this.profs = profs;

}

publicvoid addProf(Professor prof) {

if (this.profs == null)

this.profs = new ArrayList<>();

this.profs.add(prof);

}

publicint getAverage() {

returnaverage;

}

publicvoid setAverage(intaverage) {

this.average = average;

}

@Override

public String toString() {

returnthis.name;

}

}

Time for some management now. I used EclipseLink as my persistence engine, with the following configuration (in the persistence.xml file). Note that this will not create the database, only the tables, you need to do the database creation manually. The name of the database is PlayJPQL. You also need to set your username and password properly:

This said, let us start by populating the database. Since we use the "mappedBy" keyword on the Professor side, the Student class owns the relation. This means that we need to add professors to the students. Adding students to the professors could result in data losses:

All professors

First, we list all the professors (note that we omit the select clause, but it is implicit):

System.out.println("\n\nAll professors");

Query q = em.createQuery("from Professor p");

List<Professor> list = q.getResultList();

for (Professor p : list) {

if (firstid == - 1)

firstid = p.getId();

System.out.println("Professor " + p.getName());

}

With the following result

All professors

Professor Paula Queiroz

Professor Antonio Fagundes

Professor Silvia Matos

Professor Alder Dante

Professor Paula Alves

Professor Mario Castro

Limit the number of professors

Next, only the three first professors

System.out.println("\n\nThe first 3 professors");

q = em.createQuery("from Professor p");

q.setMaxResults(3);

list = q.getResultList();

for (Professor p : list)

System.out.println("Professor " + p.getName());

With the following result:

The first 3 professors

Professor Paula Queiroz

Professor Antonio Fagundes

Professor Silvia Matos

A named query with a sort

Next, we use the named query "orderProfs", which me mentioned before. The advantage of using a named query is that we can use it multiple times in the code having only one definition.

We don't need the firstid manipulation here. We get the id of the first professor here, for a future query.

System.out.println("\n\nSorted professors");

q = em.createNamedQuery("orderProfs");

list = q.getResultList();

for (Professor p : list) {

if (firstid == - 1)

firstid = p.getId();

System.out.println("Professor " + p.getName());

}

The result is now:

Sorted professors

Professor Alder Dante

Professor Antonio Fagundes

Professor Mario Castro

Professor Paula Alves

Professor Paula Queiroz

Professor Silvia Matos

A WHERE clause with a parameter

We now want to list the professors whose name starts with an 'A'. We will use a parameter to specify the pattern we are looking for. The use of this parameter, lets the JDBC driver sanitize the input (not that there is any in this case, because we are using a fixed 'A%'), thus reducing or even eliminating the possibility of SQL injection attacks:

A LEFT OUTER JOIN

You may have noticed that professor Mario Castro didn't show up in the former query, because he has no students. To make all the professors show up, even those without students, we may use a left outer join:

MEMBER OF and NOT MEMBER OF

Something similar could be achieved with the member of, which checks whether some element belongs to a list. For instance, we could look for the professors of Andreia Lopes or for the professors that do not teach her.

Professors and their students average
Professor Paula Queiroz Students average 16.0
Professor Antonio Fagundes Students average 14.5
Professor Silvia Matos Students average 13.6667
Professor Alder Dante Students average 15.0

Professor Paula Alves Students average 12.6667

GROUP BY HAVING

And what if we wanted the list of professors that have at least two students with an average above 15?

System.out.println("\n\nProfessors with two or more students having average above 15");

Professors with two or more students having average above 15
Professor Paula Queiroz
Professor Antonio Fagundes

Professor Alder Dante

This is example is quite long, so I provide the complete version here in a .zip Eclipse project. It will not run on your computer unless you setup the database, the persistence.xml and download the MySQL (or other) JDBC driver.