Test JPQL / HQL without a deploy

Hebert CoelhoJuly 5th, 2012Last Updated: October 22nd, 2012

1 319 9 minutes read

Have you ever wanted to test your JPQL / HQL without doing a full deploy of your application?
What we will see here today is simple solution that works for any JPA implementation: Hibernate, OpenJPA, EclipseLink and others.

The base source code found in this post came from this book: “Pro JPA 2: Mastering the Java™ Persistence API – Mike Keith, Merrick Schincariol”. This post will add to the original code: query parameters and NamedQuery test.

Model classes and data Generation

Bellow are the model classes’ code:

package com.model;
 
import java.util.*;
 
import javax.persistence.*;
 
@Entity
@NamedQueries({
  @NamedQuery(name="Person.findByName", query="select p from Person p where p.name = :name"),
  @NamedQuery(name="Person.findByAge", query="select p from Person p where p.age = :age", hints={@QueryHint(name="org.hibernate.timeout", value="1000")})
})
 
public class Person {
 
 public static final String FIND_BY_NAME = "Person.findByName";
 public static final String FIND_BY_AGE = "Person.findByAge";
 
 @Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 private int id;
 
 private String name;
 private int age;
 
 public Person() {
 
 }
 
 public Person(int id) {
  this.id = id;
 }
 
 public Person(String name, int age) {
  this.name = name;
  this.age = age;
 }
 
 @OneToMany(mappedBy = "person", cascade = CascadeType.ALL)
 private List<Dog> dogs;
 
 @OneToOne(cascade = CascadeType.ALL)
 @JoinColumn(name="address_id")
 private Address address;
 
 public int getId() {
  return id;
 }
 
 public void setId(int id) {
  this.id = id;
 }
 
 public String getName() {
  return name;
 }
 
 public void setName(String name) {
  this.name = name;
 }
 
 public int getAge() {
  return age;
 }
 
 public void setAge(int age) {
  this.age = age;
 }
 
 public List<Dog> getDogs() {
  if (dogs == null) {
   dogs = new ArrayList<Dog>();
  }
 
  return dogs;
 }
 
 public void setDogs(List<Dog> dogs) {
  this.dogs = dogs;
 }
 
 public Address getAddress() {
  return address;
 }
 
 public void setAddress(Address address) {
  this.address = address;
 }
 
 @Override
 public int hashCode() {
  return getId();
 }
 
 @Override
 public boolean equals(Object obj) {
  if (obj instanceof Person) {
   Person person = (Person) obj;
   return person.getId() == getId();
  }
 
  return false;
 }
 
 @Override
 public String toString() {
  return "Person name: " + name;
 }
}

package com.model;
 
import java.util.Date;
 
import javax.persistence.*;
 
@Entity
@NamedQueries(value={@NamedQuery(name="Dog.FindByDateOfBirth", query="select d from Dog d where d.dateOfBirth = :dateOfBirth"),
  @NamedQuery(name="Dog.FindByPerson", query="select d from Dog d where d.person = :personObject")})
public class Dog {
 
 public static final String FIND_BY_DATE_OF_BIRTH = "Dog.FindByDateOfBirth";
 
 @Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 private int id;
 
 private String name;
 private double weight;
 
 @Temporal(TemporalType.TIMESTAMP)
 private Date dateOfBirth;
 
 public Dog() {
 
 }
 
 public Dog(String name, double weight, Date dateOfBirth) {
  this.name = name;
  this.weight = weight;
  this.dateOfBirth = dateOfBirth;
 }
 
 public static void main(String[] args) {
 
  System.out.println(new Date());
 }
 
 @ManyToOne
 private Person person;
 
 public int getId() {
  return id;
 }
 
 public void setId(int id) {
  this.id = id;
 }
 
 public String getName() {
  return name;
 }
 
 public void setName(String name) {
  this.name = name;
 }
 
 public double getWeight() {
  return weight;
 }
 
 public void setWeight(double weight) {
  this.weight = weight;
 }
 
 public Date getDateOfBirth() {
  return dateOfBirth;
 }
 
 public void setDateOfBirth(Date dateOfBirth) {
  this.dateOfBirth = dateOfBirth;
 }
 
 public Person getPerson() {
  return person;
 }
 
 public void setPerson(Person person) {
  this.person = person;
 }
 
 @Override
 public int hashCode() {
  return getId();
 }
 
 @Override
 public boolean equals(Object obj) {
  if (obj instanceof Dog) {
   Dog dog = (Dog) obj;
   return dog.getId() == getId();
  }
 
  return false;
 }
 
 @Override
 public String toString() {
  return "Dog name: " + name;
 }
}

package com.model;
 
import javax.persistence.*;
 
@Entity
@NamedQuery(name="Address.FindAll", query="select a from Address a")
public class Address {
 
 @Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 private int id;
 
 private String streetName;
 private int houseNumber;
 
 public Address() {
 
 }
 
 public Address(String streetName, int houseNumber) {
  this.streetName = streetName;
  this.houseNumber = houseNumber;
 }
 
 public int getId() {
  return id;
 }
 
 public void setId(int id) {
  this.id = id;
 }
 
 public String getStreetName() {
  return streetName;
 }
 
 public void setStreetName(String streetName) {
  this.streetName = streetName;
 }
 
 public int getHouseNumber() {
  return houseNumber;
 }
 
 public void setHouseNumber(int houseNumber) {
  this.houseNumber = houseNumber;
 }
 
 @Override
 public int hashCode() {
  return getId();
 }
 
 @Override
 public boolean equals(Object obj) {
  if (obj instanceof Address) {
   Address address = (Address) obj;
   return address.getId() == getId();
  }
 
  return false;
 }
 
 @Override
 public String toString() {
  return "Adress street name: " + streetName;
 }
}

In the code above we got several JPA relationships as a sample.

Bellow is the class that will handle the transaction and the data that will be written into the HSQLDB database:

package com.main;
 
import java.text.*;
import java.util.Date;
 
import javax.persistence.*;
 
import com.model.*;
 
public class CodeGenerator {
 private static EntityManagerFactory emf;
 private static EntityManager em;
 
 public static final String PERSON01_NAME = "John";
 public static final String PERSON02_NAME = "Mary";
 public static final String PERSON03_NAME = "Anna";
 public static final String PERSON04_NAME = "Joseph";
 public static final String PERSON05_NAME = "Mark";
 public static final String PERSON06_NAME = "I will not have any relationship";
 
 public static void startConnection() {
  emf = Persistence.createEntityManagerFactory("QueryTester");
  em = emf.createEntityManager();
  em.getTransaction().begin();
 }
 
 public static void closeConnection() {
  em.getTransaction().commit();
  emf.close();
 }
 
 public static void generateData() {
  int year = 1995;
  int month = 1;
  int day = 10;
 
  Dog dog01 = new Dog("Yellow", 3.5d, createNewDate(day, month, year));
  Dog dog02 = new Dog("Brown", 8.5d, createNewDate(++day, ++month, ++year));
  Dog dog03 = new Dog("Dark", 15.5d, createNewDate(++day, ++month, ++year));
  Dog dog04 = new Dog("Kaka", 4.3d, createNewDate(++day, ++month, ++year));
  Dog dog05 = new Dog("Pepe", 8.2d, createNewDate(++day, ++month, ++year));
  Dog dog06 = new Dog("Casillas", 6.1d, createNewDate(++day, ++month, ++year));
  Dog dog07 = new Dog("Fish", 6.7d, createNewDate(++day, ++month, ++year));
  Dog dog08 = new Dog("Lion", 3.1d, createNewDate(++day, ++month, ++year));
  Dog dog09 = new Dog("Cat", 5.5d, createNewDate(++day, ++month, ++year));
  Dog dog10 = new Dog("Java", 21.7d, createNewDate(++day, ++month, ++year));
  Dog dog11 = new Dog("JSF", 23.65d, createNewDate(++day, ++month, ++year));
  Dog dog12 = new Dog("VRaptor", 24.0d, createNewDate(++day, ++month, ++year));
  Dog dog13 = new Dog("Ferrari", 3.7d, createNewDate(++day, ++month, ++year));
  Dog dog14 = new Dog("Porshe", 1.33d, createNewDate(++day, ++month, ++year));
  Dog dog15 = new Dog("Bike", 4.44d, createNewDate(++day, ++month, ++year));
  Dog dog16 = new Dog("Rambo", 5.44d, createNewDate(++day, ++month, 2015));
  Dog dog17 = new Dog("Terminator", 3.88d, createNewDate(++day, ++month, 2016));
  Dog dog18 = new Dog("John McClan", 3.88d, createNewDate(++day, ++month, 2016));
 
  Person person01 = new Person(PERSON01_NAME, 33);
  person01.getDogs().add(dog01);
  person01.getDogs().add(dog02);
  person01.getDogs().add(dog03);
  person01.setAddress(new Address("Street A", 30));
  dog01.setPerson(person01);
  dog02.setPerson(person01);
  dog03.setPerson(person01);
 
  Person person02 = new Person(PERSON02_NAME, 27);
  person02.getDogs().add(dog04);
  person02.getDogs().add(dog05);
  person02.getDogs().add(dog06);
  person02.setAddress(new Address("Street B", 60));
  dog04.setPerson(person02);
  dog05.setPerson(person02);
  dog06.setPerson(person02);
 
  Person person03 = new Person(PERSON03_NAME, 7);
  person03.getDogs().add(dog07);
  person03.getDogs().add(dog08);
  person03.getDogs().add(dog09);
  person03.setAddress(new Address("Street B", 90));
  dog07.setPerson(person03);
  dog08.setPerson(person03);
  dog09.setPerson(person03);
 
  Person person04 = new Person(PERSON04_NAME, 43);
  person04.getDogs().add(dog10);
  person04.getDogs().add(dog11);
  person04.getDogs().add(dog12);
  person04.setAddress(new Address("Street C", 120));
  dog10.setPerson(person04);
  dog11.setPerson(person04);
  dog12.setPerson(person04);
 
  Person person05 = new Person(PERSON05_NAME, 70);
  person05.getDogs().add(dog13);
  person05.getDogs().add(dog14);
  person05.getDogs().add(dog15);
  person05.getDogs().add(dog16);
  person05.setAddress(new Address("Street D", 150));
  dog13.setPerson(person05);
  dog14.setPerson(person05);
  dog15.setPerson(person05);
  dog16.setPerson(person05);
 
  Person person06 = new Person(PERSON06_NAME, 45);
 
  em.persist(person01);
  em.persist(person02);
  em.persist(person03);
  em.persist(person04);
  em.persist(person05);
  em.persist(person06);
 
  em.persist(dog17);
  em.persist(dog18);
 
  em.flush();
 }
 
 private static Date createNewDate(int day, int month, int year) {
  SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
  try {
   return formatter.parse("" + day + "/" + month + "/" + year);
  } catch (ParseException e) {
   e.printStackTrace();
   return null;
  }
 }
 
 public static EntityManager getEntityManager() {
  return em;
 }
}

The “persistence.xml” file can be found in the “src/META-INF” folder with the code bellow:

<?xml version="1.0" encoding="UTF-8"?>
 
<persistence version="2.0"
 xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
 
 <persistence-unit name="QueryTester"
  transaction-type="RESOURCE_LOCAL">
  <provider>org.hibernate.ejb.HibernatePersistence</provider>
 
  <properties>
   <property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver" />
   <property name="javax.persistence.jdbc.url" value="jdbc:hsqldb:mem:." />
   <property name="javax.persistence.jdbc.user" value="sa" />
   <property name="javax.persistence.jdbc.password" value="" />
   <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" />
   <property name="hibernate.connection.shutdown" value="true" />
   <property name="hibernate.hbm2ddl.auto" value="update" />
   <property name="hibernate.show_sql" value="false" />
            <property name="hibernate.format_sql" value="false"/>
  </properties>
 </persistence-unit>
</persistence>

Abstract test class

package com.main;
 
import javax.persistence.Query;
 
import org.apache.commons.lang3.builder.ReflectionToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
 
import com.model.Person;
 
/**
 * @author Pro JPA 2 book
 * @Empowered by uaiHebert
 * 
 */
public abstract class AbstractQueryTester {
 
 protected static void populateParameters(Query query, String parameters) {
  for (String parameter : parameters.split(";")) {
   String parameterKey = parameter.split("-")[0];
   String parameterValue = parameter.split("-")[1];
   String parameterType = parameter.split("-")[2];
 
   query.setParameter(parameterKey, configureParameterValue(parameterValue, parameterType));
  }
 }
 
 private static Object configureParameterValue(String parameterValue, String parameterType) {
  if (parameterType.equalsIgnoreCase("integer")) {
   try {
    return Integer.parseInt(parameterValue);
   } catch (Exception e) {
    throw new IllegalArgumentException("Invalid parameter value as number: " + parameterValue);
   }
  }
 
  if (parameterType.equalsIgnoreCase("string")) {
   return parameterValue;
  }
 
  if (parameterType.equalsIgnoreCase("person")) {
   int personId;
 
   try {
    personId = Integer.valueOf(parameterValue);
   } catch (Exception e) {
    throw new IllegalArgumentException("Invalid parameter value as number: " + parameterValue);
   }
 
   return new Person(personId);
  }
 
  throw new IllegalArgumentException("Invalid parameter type: " + parameterType);
 }
 
 protected static void printResult(Object result) throws Exception {
  if (result == null) {
   System.out.print("NULL");
  } else if (result instanceof Object[]) {
   Object[] row = (Object[]) result;
   System.out.print("[");
   for (int i = 0; i < row.length; i++) {
    printResult(row[i]);
   }
   System.out.print("]");
  } else if (result instanceof Long || result instanceof Double || result instanceof String) {
   System.out.print(result.getClass().getName() + ": " + result);
  } else {
   System.out.print(ReflectionToStringBuilder.toString(result, ToStringStyle.SHORT_PREFIX_STYLE));
  }
 
  System.out.println();
 } 
}

About the above code:

The populateParameters method will populate all query parameters automatically. The Eclipse console will require that the developer type the Query information and the Query parameters if any is required. The Query parameters should follow the syntax requirements bellow:
- 0 parameters: just press “Enter”
- 1 parameter: id-4-integer
- 2 or more parameters: name-John-string;age-33-integer
- The developer may use class as parameter: dog-33-Dog
The configureParameterValue method will “cast” the parameterValue to the correct type required by the Query. It allows primitive values and classes.
The printResult method will display the Query result.

Dynamic Query test

package com.main;
 
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.List;
 
import javax.persistence.EntityManager;
import javax.persistence.Query;
 
/**
 * @author Pro JPA 2 book
 * @Empowered by uaiHebert
 * 
 */
public class DynamicQueryTester extends AbstractQueryTester {
 public static void main(String[] args) throws IOException {
  CodeGenerator.startConnection();
 
  CodeGenerator.generateData();
 
  EntityManager em = CodeGenerator.getEntityManager();
 
  BufferedReader reader = new BufferedReader(new InputStreamReader(System.in));
 
  for (;;) {
   System.out.print("Type your JPQL and press Enter > ");
   String dynamicQuery = reader.readLine();
 
   if (dynamicQuery.equals("quit")) {
    break;
   }
 
   if (dynamicQuery.length() == 0) {
    continue;
   }
 
   System.out.println("Type the namedQuery parameters.");
   System.out.println("All paramters should be like: id-2-integer;name-John-string");
   System.out.println("Or just press enter for 0 parameters");
   String parameters = reader.readLine();
 
   try {
    Query query = em.createQuery(dynamicQuery);
 
    if (parameters.length() > 0) {
     populateParameters(query, parameters);
    }
 
    @SuppressWarnings("rawtypes")
    List result = query.getResultList();
 
    if (result.size() > 0) {
     int count = 0;
     for (Object o : result) {
      System.out.print(++count + " ");
      printResult(o);
     }
    } else {
     System.out.println("0 results returned");
    }
   } catch (Exception e) {
    e.printStackTrace();
   }
  }
 
  CodeGenerator.closeConnection();
 }
}

The code above will start a transaction, create the database data in runtime memory and allow a developer to test any kind of dynamic query. To test a JPQL / HQL the developer just need to type the Query code in the console.

To finish the loop do not type any Query data, just type “quit” and press “Enter”.

NamedQuery test

package com.main;
 
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.List;
 
import javax.persistence.EntityManager;
import javax.persistence.Query;
 
/**
 * @author Pro JPA 2 book
 * @Empowered by uaiHebert
 * 
 */
public class NamedQueryTester extends AbstractQueryTester {
 public static void main(String[] args) throws IOException {
  CodeGenerator.startConnection();
 
  CodeGenerator.generateData();
 
  EntityManager em = CodeGenerator.getEntityManager();
 
  BufferedReader reader = new BufferedReader(new InputStreamReader(System.in));
 
  for (;;) {
   System.out.print("Type the NamedQuery name > ");
   String namedQueryName = reader.readLine();
 
   if (namedQueryName.equals("quit")) {
    break;
   }
 
   if (namedQueryName.length() == 0) {
    continue;
   }
 
   System.out.println("Type the namedQuery parameters.");
   System.out.println("Press enter for 0 parameters");
   System.out.println("Or type all paramters like: id-2,name-4");
   String parameters = reader.readLine();
 
   try {
    Query query = em.createNamedQuery(namedQueryName);
 
    if (parameters.length() > 0) {
     populateParameters(query, parameters);
    }
 
    @SuppressWarnings("rawtypes")
    List result = query.getResultList();
 
    if (result.size() > 0) {
     int count = 0;
     for (Object o : result) {
      System.out.print(++count + " ");
      printResult(o);
     }
    } else {
     System.out.println("0 results returned");
    }
   } catch (Exception e) {
    System.err.println(e.getClass() + e.getMessage());
   }
  }
 
  CodeGenerator.closeConnection();
 }
}

The code above will start a transaction, create the database data in runtime memory and allow the test of NamedQueries that are configured in the model classes. To test the NamedQueries just type their name in the console.

To finish the loop do not type any NamedQuery name, just type “quit” and press “Enter”.

Running the application

Run the class DynamicQueryTester and type the following text in the console: “select p from Person p”. Press enter twice and the following text will appear in the console:

At the first “Enter” key hit a message will be displayed asking for parameters if there are any.

Type in the console: “select p from Person p where p.age > :age” and press “Enter”. Type the parameter: “age-69-integer”. Press enter and the result bellow will be displayed:

To finish the code that is running type the word “quit” and press the “Enter” button.

Run now the code of the class NamedQueryTester.

Type the NamedQuery name “Dog.FindByPerson” in the console and press enter. Type “personObject-1-person” parameter and the result bellow will be displayed:

Using this post code with your application code

You can use the code of this post in two ways: add your application model class to the project of this post or use the code found in the main package of this project in your application.

Add your model class to the project of this post:

Copy the model class to the “com.model” package.
Set up the persistence.xml to access the database

Use the code found in the main package in your application:

Set up the PersistenceUnit found in the CodeGenerator class.
Use the Apache library found in the libs folder: “commons-lang3-3-1.jar”.

For both approaches the step bellow are required:

Edit the method “AbstractQueryTester.configureParameterValue” to accept all attributes values/types/classes that will be used with the queries.
Edit the “hibernate.hbm2ddl.auto” configuration to “none” or “validate”. This configuration is found in the “persistence.xml” file.
Only invoke the methods to start/close the connection in the CodeGenerator class.

Proposals

Bellow you will find some proposals to do with this code:

Use the Reflection technique to create the classes in the “AbstractQueryTester.configureParameterValue” method.
Apply Jenkins to validate if some of the JPQLs are with the right syntax after a commit.

The End

I hope this post might help you.

Click here to download the source code of this post.

If you have any doubt/suggestion just post it.

Reference: How to test your JPQL / HQL without a Deploy from our JCG partner Hebert Coelho at the uaiHebert blog.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial