5. Queries

1. Hibernate Query Language

Full documentation can be found here:

• In spanish
• In english

The HQL language (Hibernate Query Language) was born with the aim of saving the relational model, since it is a supraset of SQL (extension of SQL that includes SQL). The first consideration is that, by default, its functionality is to retrieve objects from the database, not tables as we did in the SQL language through ResultSet. The queries with HQL will be made from a Query interface, which will be the place where we will specify what we want to get. Optionally we can add to the query the necessary parameters for its execution, to avoid hard-coded queries.

Firstly, the query will be prepared. When creating it, we have to take into account two options, with four possible combinations:

• It will return one result or more than one and,
• It will return the result encapsulated in objects or in a composite way.

Depending on the answer obtained, in the second step we will choose one of the four options seen below.

1 Query q=session.createQuery("HQL statement");

2.1 List<Object> resultsObjects=q.list();

2.2 List<Object []>mixedresults=q.list();

2.3 Object result=q.uniqueResult();

2.4 Object[] result=q.uniqueResult();

1. // process information obtained

1. In first option the query returns a collection of objects (several rows, on object in a row).
2. This option return a collection with a composite of objects and another types: for example a Student a number of topics and number of teachers.
3. Both last options is when, instead of a collection, the query returns a unique object/composite.

Note

• session.createQuery allow to write an HQL query, with or without parameters
• Query.list() get all the objects (as a List) from the database that satisfy the query.
• Query.uniqueResult() get a single object from the database.

Before details, here you will find some general ideas:

• We can remove the Select *, to indicate that we want it all attributes.
• There is a medium case sensitivity: HQL reserved words are case-insensitive, but references to objects (their names) are case-sensitive: it does not matter to put From or fROm, but a Student is not the same as a student. It is recommended to write sentences in lower case, in order to distinguish from the classes.
• After from, it appears the name of a class(es), not tables.
• Assigning aliases is highly recommended. We can use joins in similar way to SQL.
• Then the where can appear, with its SQL-like conditions.
• Text constants are enclosed in single quotes.
• We can query from collections that appear inside the objects (from the teacher's set of students, for instace).
• There is no limit operator.
• order by is used similarly to SQL.
• group by is allowed, and aggregate functions are: COUNT, AVG, SUM, MIN and MAX.

2. Recovering objects

From now, the samples are based on this schema, that you will find here Docencia

{width=90%}

Tip

Now is a good moment to try to do a reverse engineering approach to build your project from database.

3. Queries

3.1. Getting simple objects

These queries are the ones that we want to retrieve an object or collection of these from the database (one or more rows, in comparison with ResultSet). Each result is a single object from a table in the database. They will be characterized because the Select word may not appear or simply indicate the alias of the class we have. Let's see the examples.

The following example shows all students:

// complete and polite
Query<Alumno> q=laSesion.createQuery("Select a from Alumno a");

// OR

// in a quick way
Query<Alumno> q=laSesion.createQuery("from Alumno");

// quickest (without type)
Query q=laSesion.createQuery("from Alumno");

List<Student> losAlumnos=q.list();

for (Alumno alumno : losAlumnos) {
            System.out.println(alumno);
        }

If we want to filter or to search for any student, we need to use where clause, as a normal SQL query. Expressions can be:

{width=90%}

When the where filter only one object, we could store it in a List, ant then access it, but is better to get the object directly, as follows:

Query<Alumno> q = laSesion.createQuery("Select a from Alumno a where a.idAlumno=1");
Alumno alumno = q.uniqueResult();
System.out.println(alumno);

notice that we create our query with diamond operator, saying that it will return a student. Then, the query return obvously the student. The alternative is casting the returned object of uniqueResult:

Query q = laSesion.createQuery("Select a from Alumno a where a.idAlumno=1");
Alumno alumno = (Alumno)q.uniqueResult();
System.out.println(alumno);

Careful

Be careful when retrieving a unique object and the query returns several.

Query q=laSesion.createQuery("Select a from Student a where a.age=24");
Alumno a=(Student) q.uniqueResult();
// org.hibernate.NonUniqueResultException: query did not return a unique result: 2

When we have lots of results, it is still not convenient to recover them all at once, but to access them by 10 by 10 or similar, just like the search pages of Google or Amazon. We can achieve this by launching smaller queries repeatedly, applying to the query:

• Q.setFirstResult(int start) → indicates the first object (row) to return.
• Q.setMaxResult(int howMany) → indicates how many rows it will return.

With an appropriate designed algorithm, we can perform a loop, shifting the start in each iteration, incrementing it by the number of rows retrieved in the previous iteration. It would involve a lot of small queries versus one big one.

3.2. Getting composite objects

If we need to get more (or less) data than an entire object, we must do it in the select part of the clause, but when we get the data we can't store it in an Object. For this reason, and using the polymorphism property of Java, we get the results in a generic array of objects. Therefore, we must be very careful with the type of each cell, as well as the size of said array, since it will be strongly linked to the query itself. Let's look at the following query: Display name and age of students:

Query q = laSesion.createQuery("Select a.nombre,a.edad from Alumno a");

List<Object[]> result = q.list();

for (Object[] objects : result) {
  System.out.print("Alumno: ");
  for (int i = 0; i < objects.length; i++) {
    System.out.print(objects[i] + " ");
  }
  System.out.println();
}

Notice than if you only want to print the information, no extra work is needed, due to (almost) all objects can be cast to String. If you want to use in another way, be very careful to convert to the accurate data type.

Collections

We are going to check the name of the students and how many exams they have taken. That information is in the Exam Set, so we'll need to manipulate that collection:

Query q = laSesion.createQuery("Select a.nombre,size(a.examens) from Alumno a");
List<Object[]> result = q.list();

for (Object[] alu : result) {
  System.out.println("Alumno: " + alu[0] + " has done " + alu[1] + " exams.");
}

As you can see, we have applied the size() function to the collection to see its size. We can therefore apply:

• size(collection) → retrieve the size of the collection.
• collection is empty | collection is not empty → to determine if it is empty. It is equivalent to comparing the size with 0
• Operators in, all can be combined using the operator elements(collection)

As an advanced query, you can use exists and not exists in your queries.

4. Parameters and NamedQueries

Normally most queries will need some parameters, usually for object filtering in the where clause. The benefits of parameterizing queries to avoid SQL injection or similar problems were already discussed in previous unit.

The management of parameters is carried out in the same way as with prepared statements (preparedStatements), and can be done through positional or nominal parameters. Let's go to see some examples:

4.1. Positional parameters

We must add placeholders inside our query, but instead of ? we add a number after the question mark. Then we must set the parameter according to the placeholder number we have set before, as follows:

Query<Alumno> q = laSesion.createQuery("Select a from Alumno a where a.edad=?1");
q.setParameter(1, 24);
List<Alumno> losAlumnos = q.list();

for (Alumno alumno : losAlumnos) {
  System.out.println(alumno);
}

Tip

Notice than The setParameter method is overloaded to set every kind of data type.

4.2. Named parameters

The positions are fine, but if we can indicate the parameters in a nominal way, it will be a much readable program. The parameters are indicated with :parameterName and will be assigned with method setParameter(parameterName,value), indicating the name of the parameter (without the colon):

4.3. Named queries

We can write queries in our methods as we need them. However, it is a good practice to create the most important queries or that we have forecast that they will be the most used together with the same class, through the mechanism that is presented below. In this section we create and label our queries, inside a collection of queries, and then we can call it. Is like we create a queries library.

Outside the class definition, a @NamedQueries collection will be created, which will contain an array (indicated by braces) of @NamedQuery elements, each of them with a name and a query definition.

To invoke them, instead of creating a Query object, we will create it through a NamedQuery, indicating its name, and assigning parameters, if any.

In Alumno class:

@Entity
@Table(name = "Alumno", catalog = "DocenciaConsultas")
@NamedQueries({
    @NamedQuery(name="Repetidores",
            query="select a from Student a where a.repeater=true "),
    @NamedQuery(name="AlumnosEntre",
            query="select a from Student a where a.age between :min and :max"),
    @NamedQuery(name="AlumnosDescAge",
            query="select a from Student a order by a.age desc")
})
public class Alumno implements java.io.Serializable {
    static final long serialVersionUID=13L;
  ...

Whern we want to use it, instead of create a Query we must create a NamedQuery, as follows:

Query<Alumno> q = laSesion.createNamedQuery("Repetidores", Alumno.class);
System.out.println("Alumnos repetidores: ");
List<Alumno> losAlumnos = q.list();

q = laSesion.createNamedQuery("AlumnosEntre", Alumno.class);
q.setParameter("min", 23);
q.setParameter("max", 28);

q = laSesion.createNamedQuery("AlumnosDescAge", Alumno.class);
System.out.println("Alumnos de mayor a menor: ");

5. CRUD

Finally, we are going to analyze the rest of the CRUD operations. It should be noted that these operations can be performed directly on the objects, which is why they are explained as complementary rather than main.

5.1. Create (insert)

If we want to insert a new object in the database, is very easy, as we can remember in first sections of this unit.

Alumno a=new Alumno();
a.setApellidos("Hamilton");
a.setNombre("Lewis");
a.setEdad(42);
a.setRepetidor(false);

laSesion.persist(a);

Simply we create a new object, set its values, via setters or all argument's constructor, and finally store in the database with persist method. You can use save or saveOrUpdate indistinctly. You can see some differences between them here. Both of them fires a insert sql statement.

In the same way that SQL, we could insert into a table with the result of a query, as follows:

insert into class(properties) select_hql;

5.2. Update

In order to change the value of one or more object properties, we could load the object from database, change values via getters methods, and the save the changes:

Alumno a=laSesion.get(Alumno.class, 7L);
a.setEdad(44);
a.setRepetidor(true);

laSesion.update(a);
System.out.println(a);

If we want to set a massive update, who affect several records in the database, we could use in the same way to SQL:

Query q=laSesion.createQuery("Update from Alumno a set a.edad=a.edad+1 where a.repetidor=true");
int howMany=q.executeUpdate();

System.out.println(howMany + " alumnos actualizados");

5.3. Deletion

As previous sections, we could delete a single object or a set of records who satisfy a condition. In case of a single object, we need to load it, and then delete it:

Alumno a=laSesion.get(Alumno.class, 7L);
laSesion.delete(a);

System.out.println(a);

Notice that the object remains in memory or until the end of the method, but when the session is commited, the object will be detached. The detached status means that this object is out of any kind of track. We do not have possibility to save or update again this object.

To do a massive deletion, in the same way that SQL:

Query q=laSesion.createQuery("delete from Alumno a where a.repetidor=true");
int howMany=q.executeUpdate();

System.out.println(howMany + " alumnos borrados");

Tip

Remember this general tips:

• These statements may contain parameters (in sentences that contains where clause).
• The where is optional, but it will delete or update everything if missing.
• These queries are all executed using executeUpdate(), because they change the database status, and it will return an integer with the number of affected rows.

You have to pay attention to the deletions, since depending on how we have defined the relationships (deletions in cascade, leaving orphan rows, etc.) they may or may not occur, and throw java.sql.SQLIntegrityConstraintViolationException.

As a final comment in this section, remember what was said previously: in the manipulation of objects (deletions, modifications, etc.) we have enough tools to do it without HQL queries. These queries are more suitable for processing large volumes of information without uploading said information to our program to have it processed.

6. Appendix, Objects status

As we have said along this unit, objects are loaded and tracked by the session manager. Each object could have a state inside the session, and it is interesting to now all possibilities. In this picture, you can see all different status, and the method to change the object status.

{width=90%}

• transient → The object has identity (exist in memory), but it has not a persistent identity, or primary key.
• persistent → The object has been marked to be saved (persisted), and the memory identity is related to the database identity. When we load an object from the database this is the initial status.
• detached → The object have been persisted recently, but this context is closed, with detach() method. Operations when is detached will not be reflected on the database.
• removed → The object has been marked to be deleted (but is not effected yet). When the session is closed it will be removed effectively, but we can reload it and set into a persistent context.

You can get more information here.