Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Fundamentals › JPA

JPQL

What is JPQL, and how is it different from SQL?

em.find finds only one entity, by one primary key. To find several by a condition, such as all members who are 20 or older, you have to write a query (text that asks the database), and in JPA you write that query in a language called JPQL. JPQL looks like SQL, but you write it with the names of entity classes and fields, not tables and columns. The Member class on the screen is paired with the member_tbl table by @Table, and its name field is paired with the user_name column by @Column, so the two names are different. When em.createQuery passes the text in the jpql variable to JPA, JPA reads that text. When you call query.getResultList(), JPA turns the JPQL into SQL, sends it to the database, builds Member objects from the rows that come back, and puts them in list. In this lab you change the name that you write in JPQL from an entity name to a table name and to a column name, run the same code each time, and see the SQL that was sent to the database, what came back in list, and the error.

Checked against real execution · Compiled and ran 4 combinations with the code on the screen as it is, on Hibernate 7.4.5 and H2 2.4.240, and checked the SQL that was sent, the objects that came back, and the line on which the error occurred against the results

Concepts in this lab

JPQL
The JPA query language that you write against entities and their fields, not tables. Its syntax is similar to SQL, and when a query runs, the JPA provider translates it into SQL for the database in use. Its advantage is that it does not depend on the syntax of a specific database.
em.createQuery and getResultList
em.createQuery(text, result class) is a method that passes text written in JPQL to JPA, makes a query object (TypedQuery), and returns it, and getResultList() of that query object is a method that runs a SELECT query and returns the result as a list (List). The API documentation says that createQuery throws IllegalArgumentException if the text is wrong or the result class does not fit. Observed on Hibernate 7.4.5, an error caused by a wrong entity name or field name in the text occurred on the createQuery line, not on getResultList, and it was the getResultList line that sent SQL to the database. On the other hand, em.find(class, primary key) is a method that finds one entity by one primary key value, so when you find several by a condition, you use a query.
Entity names and field names
JPQL is a query that you write about entities and their fields, not tables and columns, so you write an entity name after from, and the field names of that entity in the conditions. Section 4.3.1 of the Jakarta Persistence specification says that a query string refers to an entity by its entity name, and that the name is the name of @Entity, and if it is not set, it is the unqualified name of the entity class (the class name without the package). The Hibernate guide says that you cannot refer to the tables and columns of the database directly in HQL and JPQL. So even if a class is paired with the member_tbl table by @Table and a field is paired with the user_name column by @Column, you write the class name Member and the field name name in JPQL, and writing the table name or the column name causes an error.
@Table
@Table is an annotation that you put above an entity class to set the table (the primary table) that the entity is paired with. You write the table name in name, and the API documentation says that if you do not write it, the entity name is used, and this default also applies when you do not put @Table at all. This name is only the name of the table in the SQL that is sent, so you cannot use it after from in JPQL, and you have to write the entity name of the class. What sets the name of the column that corresponds to a field is the name of @Column.

Try these

Running with the default settings

  1. The database setup: before the code runs, the database has a member_tbl table, and it holds three rows: (id 1, user_name kim, age 25), (id 2, user_name lee, age 17), and (id 3, user_name park, age 30). The Member class on the screen is paired with this table. @Table sets the table name member_tbl, and @Column sets the column name user_name for the name field. em in the code is an EntityManager, the object that gives work to JPA. The em.find method finds only one row by one primary key (id), so to find several by a condition, such as all members who are 20 or older, you have to use a query.
  2. String jpql = "select m from Member m where m.age >= 20": the query written in JPQL is put in the jpql variable as text. It is still only text, so neither JPA nor the database has read this text, and no SQL has been sent. In this text, Member is the name of the class we wrote, and m.age is the age field of that class.
  3. TypedQuery<Member> query = em.createQuery(jpql, Member.class): em passes the jpql text to JPA, and while reading the text, JPA checks the names that are written. Member is the name of an entity that JPA knows (the name of an entity is by default the class name), and m.age is a field of that entity. All the names are right, so JPA makes a query object and puts it in query. SQL statements sent up to this line: 0. A query object was made, and the database has not been asked yet.
  4. List<Member> list = query.getResultList(): now JPA turns the JPQL into SQL and sends it to the database. The SQL that Hibernate 7.4.5 built and sent in this run is "select m1_0.id,m1_0.age,m1_0.user_name from member_tbl m1_0 where m1_0.age>=20". Member in the JPQL has become the table name member_tbl, and m.age in the condition has become the column name age. This SQL is not in the code we wrote, and JPA did the work of turning JPQL into SQL. The text of the SQL can differ by Hibernate version.
  5. The database returns the rows that meet the condition, 2 in all (kim, park), and for each row JPA builds a Member object and fills the values of the columns into the fields. The value of the user_name column goes into the name field. These objects come back in list. What came back is not rows but objects of the Member class (2 in all), and in this run the class of every object in list was Member. lee, who is 17, does not meet the condition and did not come back.

Result of this run

Code

@Entity
// Table: one Member class is paired with one member_tbl table
@Table(name = "member_tbl")
public class Member {
    @Id
    private Long id;

    // Column: the name field is paired with the user_name column
    @Column(name = "user_name")
    private String name;

    private int age;
}

// The side that queries
// JPQL: the query text that JPA reads
String jpql = "select m from Member m where m.age >= 20";
TypedQuery<Member> query = em.createQuery(jpql, Member.class);
List<Member> list = query.getResultList();

Assumptions of this model

This was checked against real execution with JDK 21 on Hibernate 7.4.5 and H2 2.4.240 (in-memory database). Spring was not used, and Hibernate was started directly in code (the classes were registered in Configuration, and the tables were created with hbm2ddl.auto=create-drop). All 4 combinations were compiled and run with the code on the screen exactly as it is. The Member class on the screen was made into Member.java with only the import lines added, and the four lines of "The side that queries" were put in as the body of a method that receives an EntityManager. What does not appear on the screen is the import lines, the database connection settings, and the code that gets and closes the EntityManager, and since this lab only reads, no transaction was started. For each combination, the database was created anew, and before the run, three rows ((1, kim, 25), (2, lee, 17), (3, park, 30)) were put into member_tbl with JDBC. The SQL of this setup was not counted. What was checked is whether an error occurred and on which line (the line of code was found from the stack trace), the kind and the message of the exception, the text and the number of the SQL captured with Hibernate's StatementInspector and the line that sent that SQL, and the size of the list that came back and the class and the values of its elements. The facts that the step sentences rely on were also measured in a second run that inserts a measuring line right below each line of code. They are that the SQL sent was 0 after passing the jpql variable and the createQuery line and 1 after passing the getResultList line, that in the two combinations that cause an error, the error occurs on the createQuery line, the next line is not run, and no SQL is ever sent, and that the class of the objects that came back is Member as it is. What the documentation defines is section 4.1 of the Jakarta Persistence 3.2 specification (JPQL is the language of dynamic queries written as strings), section 4.3 (the targets of a query are the abstract schema types of entities and embeddables), section 4.3.1 (in a query string an entity is referred to by its entity name, and the name is the name of @Entity, and the default is the unqualified name of the entity class), the API documentation of EntityManager.createQuery, TypedQuery.getResultList, Table, and EntityManager.find (createQuery throws IllegalArgumentException if the query string is wrong, getResultList runs a SELECT query and returns a list, the name of @Table defaults to the entity name, and find finds one by the primary key), and section 1.1 of the Hibernate Query Language guide (in HQL and JPQL you cannot refer to tables and columns directly, and you refer to entity classes and their fields). The specification page came back only as a summary, so only the sentences of the relevant sections were checked, and the sections were not read in full. What the documentation does not define and was observed in this version is that reading the JPQL and checking the names happens on the createQuery line (the error occurred on that line), that the SQL is sent on the getResultList line, the kind and the message of the exception of the error, and the text of the SQL that was sent (the alias m1_0 and so on). These texts can differ by Hibernate version. The run was not checked against MySQL. This lab does not cover joins and fetch joins, bulk queries that do UPDATE and DELETE, dynamic queries whose conditions change, parameter binding, or the several ways to receive results, and these are covered in the Learn-tab labs "JPQL and fetch joins", "Bulk update and delete operations", and "Dynamic queries and Querydsl".

Previous lab: Lazy fetching · Next lab: Tables, rows, and columns

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these