Normally, when we need to manipulate any database we have to write SQL based on the information of the columns, the tables in the database, and possibly for different database types, the syntax of the SQL statement is different. To remove these drawbacks, you guys can use JPA Query Language (JPQL). How is it in details? Let’s find out in this tutorial.
First, I will create a Maven project as an example:
Since I’m going to use JPA with Hibernate’s implementation, so I’ll add Hibernate dependencies like this:
|
1 2 3 4 5 6 7 8 9 10 |
<dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>5.2.12.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> <version>5.2.12.Final</version> </dependency> |
Project Lombok:
|
1 2 3 4 5 6 |
<dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.16.18</version> <scope>provided</scope> </dependency> |
The first thing I need to tell you about JPA Query Language is that it allows us to define queries based on entities rather than the names of columns and tables in the database.
The structure and syntax of JPA Query Language are very similar to the structure and syntax of SQL statements. For example, for SELECT statements, the JPA Query Language syntax is as follows:
|
1 2 3 4 |
SELECT ... FROM ... [WHERE ...] [GROUP BY ... [HAVING ...]] [ORDER BY ...] |
or with DELETE,
|
1 |
DELETE FROM ... [WHERE ...] |
UPDATE
|
1 |
UPDATE ... SET ... [WHERE ...] |
This makes it easy to define queries using the JPA Query Language, but you should remember that although we define queries using entities, in practice, at runtime, Hibernate or any library that implements JPA will transform those query statements into SQL statements for the database with the column name and table name of the database.
Let’s say, I will now use the MySQL database as an example for this tutorial. I have a table like this:
|
1 2 3 4 5 |
CREATE TABLE `clazz` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
Entity Clazz as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
package com.huongdanjava.jpa; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.Table; import lombok.Getter; import lombok.Setter; @Entity @Table @Getter @Setter public class Clazz { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id; @Column private String name; } |
To work with MySQL database, we need to use MySQL Driver dependency:
|
1 2 3 4 5 |
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>6.0.6</version> </dependency> |
JPA configuration file for MySQL database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence_2_1.xsd"> <persistence-unit name="jpaexample" transaction-type="RESOURCE_LOCAL"> <class>com.huongdanjava.jpa.Clazz</class> <exclude-unlisted-classes>true</exclude-unlisted-classes> <properties> <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" /> <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/jpa_example" /> <property name="javax.persistence.jdbc.user" value="root" /> <property name="javax.persistence.jdbc.password" value="123456" /> <property name="hibernate.format_sql" value="true" /> <property name="hibernate.show_sql" value="true" /> <property name="hibernate.use_sql_comments" value="true" /> </properties> </persistence-unit> </persistence> |
In the database, I have the following data:
Now I will try to use JPA Query Language to get information from the clazz table to try to see how it is.
First, we will get the EntityManager object:
|
1 2 |
EntityManagerFactory emf = Persistence.createEntityManagerFactory("jpaexample"); EntityManager em = emf.createEntityManager(); |
Next, we will use the EntityManager object to get the Query object based on the Clazz entity’s information:
|
1 |
Query query = em.createQuery("SELECT c FROM Clazz c"); |
In the above code, we passed the createQuery() method a JPA Query Language. Do you find it like the SQL that we usually use to query the database? 😀 The only different thing is that it now uses the name of the entity of clazz table, not the name of the table clazz.
You also see that here we have given the entity Clazz an alias c just like in the SQL statement. This alias can be used as a variable in Java, meaning that if you want to get information about the properties of the Clazz entity, you can use “c.<properties>.” For example, if you want to retrieve the column name information in the clazz table, you can rewrite the JPA Query Language as follows:
|
1 |
SELECT c.name FROM Clazz c |
Now that you have the Query object, you can use it to get the results you want:
|
1 |
List<Clazz> resultList = query.getResultList(); |
This Query object has many methods, depending on what your JPA Query Language is, then use its method accordingly. Here, my JPA Query Language statement retrieves all records in the clazz table, so I use the getResultList() method.
After you have finished, you can print the result with the following code:
|
1 |
resultList.stream().forEach(c -> System.out.println(c.getName())); |
Result:
The entire code is as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
package com.huongdanjava.jpa; import javax.persistence.*; import java.util.List; public class Application { public static void main(String[] args) { EntityManagerFactory emf = Persistence.createEntityManagerFactory("jpaexample"); EntityManager em = emf.createEntityManager(); Query query = em.createQuery("SELECT c FROM Clazz c"); List<Clazz> resultList = query.getResultList(); resultList.stream().forEach(c -> System.out.println(c.getName())); } } |
