A JPQL Terminal

Edward Rayl

October 3, 2012

As part of the development of Capstone's JPA2 course, author Edward Rayl created a great JPQL terminal tool, in the style of SQL terminals such as SQL*Plus. We have found it to be such a useful tool, in classroom situations and in development work, that we are making it generally available here, separate from the course materials and free of charge. Indeed, the author has applied open-source licensing to all of the terminal source code, and will be happy to receive feedback as well as suggested fixes and enhancements.

The terminal can execute any JPQL query and show the results in a reasonably clear manner, showing type and value for each result field.¹ It operates in either a single-line (no terminating semicolon) or multi-line (terminating semicolon) mode. It can be run to show provider-generated SQL inline. It has handy commands to "show entities" and to "describe" that give you a reflected breakdown of known entities and their JPA properties. It can also be used to effect JPQL updates and deletes.

We make the JPQL terminal available in two forms. The first is the most lightweight and flexible, and consists in just the .java and .class files, along with a read-me and a copy of the license. The second is much larger and binds you to specific choices for RDBMS, JPA provider, schema, and domain/entity classes. Of course these choices won't be practically useful in the long run; we offer this bundle as a simple way to try out the JPQL terminal and get a feel for what it can do, and it's a good bet that, if you move forward with the terminal tool, you will throw this example download away in favor of the simple tool-only download eventually.

Tool-Only Download

You can download the binary and source in a single JAR, below.

JPQLTerminal.jar

The JPQL terminal requires several things to be in place, in order to function:

An RDBMS, naturally, up and running
A schema with data worth querying
A JPA provider, in the class path
Entity classes and a declared persistence unit

With your database running, just run the tool with the JPA provider and entity classes on the class path, and pass the name of the persistence unit as a command-line argument.

So, let's say you are using Hibernate, and have a JAR MyEntities.jar with your entity classes and a META-INF/persistence.xml that defines a persistence unit called "MyPersistenceUnit". Typical usage would be:

java -classpath <Hibernate-JARs>;MyEntities.jar;JPQLTerminal.jar cc.jpa.JPQL MyPersistenceUnit

You can also run the tool with an additional command-line parameter, -showsql, to turn SQL logging on for the JPQL terminal session.

Further discussion and examples of terminal usage are found below, making reference to the example download and a specific schema and set of entity classes.

Example Download

To give you a sense of what the JPQL terminal can do, as easily as possible, we also make an all-in-one download available that bundles:

The JPQL terminal binaries
An RDBMS (Derby 10.6)
A prepared and populated schema, which is the Pharmacy schema, as also used in Course 161, and summarized here
A JPA provider (EclipseLink 2.3)
A library of JPA entities that map the schema

Download the example bundle here:

JPQLTerminalExample.jar

Usage is dead-simple: all you need is a Java 6 runtime environment, and to type the following command:

java -jar JPQLTerminalExample.jar PharmacyDerbyPU

You will see the JPQL terminal starting up and connecting to the Pharmacy schema as supported by an embedded Derby server. Following is a typical JPQL terminal session, with many of the commands making reference to specific entities and properties of the Pharmacy schema. You would issue similar commands when using the tool with your own databases and entities, just changing various names.

JPQL Generic Command Processor - Version 1.0

Connection using PharmacyDerbyPU ...

Persistence provider: EclipseLink

jpql> help

Type a JPQL command

or type SQL followed by a SQL command

or type LIST to print the last command

or type HISTORY to print the command history

or type CLEAR to clear command history

or type DESCRIBE <class name> to print just the fields of the class

or type DESCRIBE ALL <class name> to print all members and annotations

or type SHOW ENTITIES to show all entities ordered by entity name

or type SHOW ENTITIES PACKAGE to show all entities and their packages

or type MULTILINE ON (or TRUE) to use multiline mode*

or type MULTILINE OFF (or FALSE) to use single line mode*

Default mode is multi-line mode.

or type QUIT or EXIT to exit.

All commands are case insensitive and require only the first four letters

* When MULTILINE is on (by default at startup), JPQL will wait for a semicolon character before processing any queries or commands. In single-line mode, it will process one line at a time, ignoring any ending semicolons, so all queries must be expressed on a single line.

It is not ever necessary to follow LIST, HISTORY, CLEAR, DESCRIBE [ALL], SHOW ENTITIES [PACKAGE], MULTILINE ON | OFF, or QUIT commands with a semicolon

jpql> show entities

Address

Address.addressPK (@Embedded)

AddressPK (@Embeddable)

CountryCode

Drug

Email.emailPK (@Embedded)

EmailPK (@Embeddable)

Employee

Job

Language

License

Patient

Person

Phone

Phone.phonePK (@Embedded)

PhonePK (@Embeddable)

Prescriber

Prescription

StateProvince

jpql> describe Employee

public class Employee extends Person

Fields:

private Date birthDate

private Date hireDate

private Date terminationDate

private Gender gender

private String username

private String password

private String role

private Boolean accountUnlocked

private Employee supervisor

private Collection<Employee> supervisedEmployees

private Job jobTitle

Person Fields:

private int personId

private String title

private String givenName

private String middleName

private String familyNamePrefix

private String familyName

private String familyNameSuffix

private String degreeSuffix

private String notes

private int version

private Collection<Address> addresses

private Collection<Email> emails

private Collection<Phone> phones

private Collection<License> licenses

private Language preferredLanguageCode

private Address preferredAddress

private Email preferredEmail

private Phone preferredPhone

jpql> select e.firstName, e.lastName from Employee e;

SQL: SELECT t0.FAMILY_NAME

FROM Pharmacy.PERSONS t0, Pharmacy.EMPLOYEES t1

WHERE ((t1.PERSON_ID = t0.PERSON_ID) AND (t0.PERSON_TYPE = ?))

String:Berries

String:Connor

String:Hood

String:Bigboote

String:Wharf

String:Yo

6 results returned

jpql> exit

JPQLe

Especially for users of EclipseLink, there is a special version of the application that can take advantage of certain EclipseLink features. You can run this just as above, except the name of the class is cc.jpa.JPQLe -- note the 'e' at the end. So, with the tool-only download:

java -classpath <EclipseLink-JARs>;MyEntities.jar;JPQLTerminal.jar cc.jpa.JPQLe MyPersistenceUnit

With the example download, you will have to launch the class more explicitly, instead of relying on the application-class setting in the JAR's manifest. So:

java -classpath JPQLTerminalExample.jar cc.jpa.JPQLe

Special features of JPQLe are as follows:

Unlike JPQL proper, JPQLe is able to discover the persistence unit name using EclipseLink specific methods and will only complain if it finds more than one. In that case, a single persistence unit name can be specified on the command line.
JPQLe also supports two switches, in addition to -showsql -- though you may run the tool with only one of these options:

-showsql turns on SQL logging, as in the standard terminal
-performanceprofiler shows performance statistics for every executed query
-querymonitor monitors query executions and cache hits

Once running, JPQLe supports all the capabilities of the standard JPQL terminal, and offers two additional commands, which you can see in the help output during the session, as:

SHOW QUERIES to show all named queries ordered by entity name");

SHOW QUERIES FOR 'xxx' to show all named queries containing 'xxx'");

Finally, you will find that the SQL that is displayed is cleaner and properly formatted, and if the SQL itself generates an error, JPQLe will display a caret symbol (*) below the point in the SQL line where the error was detected.

Contact

As mentioned above, the JPQL terminal is an open-source project, managed by Edward Rayl. Please send any code fixes or suggested enhancements to jpql.terminal@gmail.com.

1. The author would like to acknowledge the inspiration of Mike Keith and Merrick Schincariol, authors of Pro JPA 2: Mastering the Java Persistence API (Expert's Voice in Java Technology), for their ideas in using reflection-based tools to report entity state.