JPQL Tool and query help


I am currently having my JPA class and I am new to JPQL.
We are asked to run our queries via JUnit tests.

Now when the query is incorrect the compilation fails.
I would like to know, is there any tool comparable to LINQPad for .NET that would help me build and try queries against a database/datasource?

This is extremely convenient to have such tools especially when you don’t master the syntax yet.

The problem I am trying to solve with JPQL is solved by this SQL query

SELECT p.* FROM product p
JOIN order o ON p.ID_ORDER = o.ID_ORDER 
JOIN client cl ON o.ID_CLIENT = cl.ID_CLIENT 
WHERE cl.ZIP IN(12345,54321)

As for the objects there are @OneToMany relations as follows:

  • A client has many orders
  • An order has many products

I need to find the products ordered by clients with zip codes 12345 and 54321 and ordered above 3 in quantity.

EDIT: My JPQL attempt

SELECT p FROM Product p
JOIN p.order o 
JOIN o.client cl
WHERE cl.zipCode IN(12345,54321)
AND o.quantity > 3

Many thanks.


The query seem to be correct but writing it on several lines made it fail.

EDIT 3: I made the query work on several line. So just if you know any tool equivalent to LINQPad for JPQL, that would be much appreciated.

Sorry, no familiarity with JPQL or LINQPad.

How come the godlike Java dev in this community do not know that thing?
I am shocked ! :fearful:

Now for a bit of context

Now as for what JPQL is, it stands for Java Persistance Query Language and is related to JPA standard. This is a language to query over objects (AFAIK because I have 3 days of training on JPA, no more). It is very close to SQL. Simply, instead of working with SQL entities, we work with Java objects.

One example from this afternoon.

I did prepare my JPQL with SQL solution ahead so it drove how I wrote my queries.

One of them would look like this:

SELECT p FROM Product p
JOIN p.order o 
JOIN o.client cl
WHERE cl.zipCode IN(12345,54321)
AND o.quantity > 3

could have been written like this because:

  • Product type has an order field
  • Order type has a client field
  • Client type has a zipCode field
SELECT p FROM Product p
WHERE p.order.client.zipCode IN(12345,54321)
AND o.quantity > 3

I actually have a Discord server with people that were on a previous training with me and part of the class took Java (was my initial pick) and the rest took .NET/C# (where I landed in the end by reconsidering my choice).
In C# we have LINQ (Language INtegrated Query) which splits into sub-techs (LINQ to XML, LINQ to SQL, LINQ to Objects…).
So I asked the Java mates if there was such thing as LINQ in Java. I was told there isn’t. In my own experience since I started my current training, my guess would be a bit like this (though some differences may apply) :

.NET / C# Java (Stack)
LINQ to Objects Streams
LINQ Query Syntax JPQL

As of LINQ, it needs a bit of practice of course.
We have a convenience tool called LINQPad which allows us to practice outside of an IDE, without having to compile anything.
With it, we can query databases in different fashion (for the most part):

  • SQL
  • C# Expression
  • C# Statement
  • C# Program

It can also give advanced information such as

IL (Intermediate Language → equivalent to byte code if I’m not mistaken)

Or Expression Tree

In the following picture, we can see on the left a panel showing the available connections and tables. The main usage for LINQPad is trying out queries.
I wrote the same query in different ways as sample.


SELECT * from Orders
AND OrderDate BETWEEN '1997-09-25' AND '1997-10-10'
ORDER BY OrderDate;

LINQ Query Syntax

from o in Orders
where o.ShippedDate != null
	&& o.OrderDate >= new DateTime(1997,09,25)
	&& o.OrderDate <= new DateTime(1997,10,10)
orderby o.OrderDate
select o

LINQ Method/Operator Syntax

For that one there is a Dump() extension method available to actually
display the result. It does not appear in C# code.

Orders.Where(o => o.ShippedDate != null 
	&& o.OrderDate >= new DateTime(1997,09,25)
	&& o.OrderDate <= new DateTime(1997,10,10))
	.OrderBy(o=> o.OrderDate).Dump();

A “JPQLPad” software would definitely be a nice bonus. At least for querying with JPQL.

I know. It is amazing, but true that I do not, in fact, know everything.

What you wrote looks like plain old SQL to me. I do not see anything particularly Java about it. For example, I do not see any Java classes, methods, annotations, etc. Presumably, if it works like regular SQL, it can be used to query any database that supports the query language you want to use. I would imagine it is possible to create an in-memory database that you could write JUnit tests against using JPQL. There is probably even a guide for such a thing. But I would be more surprised if there was a convenience tool comparable to the one you were using. There may be, but it is not something I am familiar with.

Honestly, it is probably best to get used to trying things out in JUnit when you can since that is the most directly similar to the real code you will be writing. Learning to write good tests is a core skill.

Sure thing. We end up more and more specialised over time. I was surprised as I thought it is basic and JPA/Hibernate is very common.

Anyways should you wish to have a look, I found the JPQL docs.