| ☰ See All Chapters |
Named Query in JPA
If the JPQL query strings are used many number of times then to reuse the query, it can be declared as a template called as named query. To declare query templates statically use the NamedQuery and NamedQueries annotations. NamedQuery and NamedQueries annotations can be declared over entity class. NamedQuery annotation declares a single named query. This takes two arguments one is the name for the query and second is the JPQL query string. If there are many named queries over the entity then they can be grouped together using NamedQueries annotations. To retrieve named queries use the below method of EntityManager:
public Query createNamedQuery(String name);
Let us see one example on how to use JPA named query.
JPA Named Query Example
Database script (MySQL)
CREATE TABLE ADDRESS( AID INT(5) PRIMARY KEY AUTO_INCREMENT, CITY VARCHAR(30), ZIPCODE VARCHAR(30) );
CREATE TABLE STUDENT( SID INT(5) PRIMARY KEY AUTO_INCREMENT, SNAME VARCHAR(30), AID INT(5), CONSTRAINT FOREIGN KEY (AID) REFERENCES ADDRESS (AID) );
INSERT INTO ADDRESS(AID,CITY,ZIPCODE) VALUES (1,'Bangalore', '560010');
INSERT INTO STUDENT(SID,SNAME,AID) VALUES (1,'Manu Manjunatha', 1); INSERT INTO STUDENT(SID,SNAME,AID) VALUES (2,'Advith Tyagraj', 1); INSERT INTO STUDENT(SID,SNAME,AID) VALUES (3,'Likitha', 1); INSERT INTO STUDENT(SID,SNAME,AID) VALUES (4,'Tyagraj', 1); |
pom.xml
<project xmlns="https://maven.apache.org/POM/4.0.0" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://maven.apache.org/POM/4.0.0 https://maven.apache.org/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.java4coding</groupId> <artifactId>JPQL_NamedQueries</artifactId> <packaging>jar</packaging> <version>1.0-SNAPSHOT</version> <name>JPQL_NamedQueries</name> <url>https://maven.apache.org</url> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>3.8.1</version> <scope>test</scope> </dependency> <dependency> <groupId>org.eclipse.persistence</groupId> <artifactId>javax.persistence</artifactId> <version>2.0.0</version> </dependency>
<dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> <version>4.2.8.Final</version> </dependency>
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.11</version> </dependency> </dependencies> </project> |
persistence.xml
<?xml version="1.0" encoding="UTF-8"?> <persistence xmlns="https://java.sun.com/xml/ns/persistence" xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="https://java.sun.com/xml/ns/persistence https://java.sun.com/xml/ns/persistence/persistence_2_0.xsd" version="2.0">
<persistence-unit name="StudentPU"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <properties> <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/study" /> <property name="hibernate.connection.driver_class" value="com.mysql.cj.jdbc.Driver" /> <property name="hibernate.connection.username" value="root" /> <property name="hibernate.connection.password" value="root" /> <property name="hibernate.archive.autodetection" value="class" /> <property name="hibernate.show_sql" value="true" /> <property name="hibernate.format_sql" value="true" /> <property name="hbm2ddl.auto" value="update" /> </properties> </persistence-unit> </persistence> |
Address.java
package com.java4coding;
import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.Table;
@Entity @Table(name = "ADDRESS") public class Address {
@Id private int aid;
private String city;
private String zipcode;
public int getAid() { return aid; } public void setAid(int aid) { this.aid = aid; } public String getCity() { return city; } public void setCity(String city) { this.city = city; } public String getZipcode() { return zipcode; } public void setZipcode(String zipcode) { this.zipcode = zipcode; } } |
Student.java
package com.java4coding;
import javax.persistence.CascadeType; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.GenerationType; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.ManyToOne; import javax.persistence.Table;
@Entity @Table(name = "STUDENT") @NamedQuery(name = "query1", query = "SELECT x FROM Student x ") @NamedQueries({ @NamedQuery(name = "query2", query = "SELECT x FROM Student x where x.sid < :studentID and x.address.city = :studentCity"), @NamedQuery(name = "query3", query = "SELECT x FROM Student x where x.sid = 1") }) public class Student {
@Id @GeneratedValue(strategy = GenerationType.AUTO) private int sid;
private String sname;
@ManyToOne(cascade = CascadeType.ALL) @JoinColumn(name= "AID") private Address address;
public int getSid() { return sid; } public void setSid(int sid) { this.sid = sid; } public String getSname() { return sname; } public void setSname(String sname) { this.sname = sname; } public Address getAddress() { return address; } public void setAddress(Address address) { this.address = address; } } |
Test.java
package com.java4coding;
import java.util.List; import javax.persistence.EntityManager; import javax.persistence.EntityManagerFactory; import javax.persistence.Persistence; import javax.persistence.Query;
public class Test { public static void main(String[] args) { EntityManagerFactory emf = Persistence.createEntityManagerFactory("StudentPU"); EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
Query q = em.createNamedQuery("query1"); List<Student> results = (List<Student>) q.getResultList();
for(Student s: results) { System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" ); System.out.println("Student name: " + s.getSname()); System.out.println("Student ID: " + s.getSid()); System.out.println("Student City: " + s.getAddress().getCity()); System.out.println("Student Adress ID: " + s.getAddress().getAid()); System.out.println("Student Zipcode: " + s.getAddress().getZipcode()); System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" ); }
q = em.createNamedQuery("query2"); q.setParameter("studentID", 5); q.setParameter("studentCity", "Bangalore");
results = (List<Student>) q.getResultList();
for(Student s: results) { System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" ); System.out.println("Student name: " + s.getSname()); System.out.println("Student ID: " + s.getSid()); System.out.println("Student City: " + s.getAddress().getCity()); System.out.println("Student Adress ID: " + s.getAddress().getAid()); System.out.println("Student Zipcode: " + s.getAddress().getZipcode()); System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" ); }
q = em.createNamedQuery("query3");
results = (List<Student>) q.getResultList();
for(Student s: results) { System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" ); System.out.println("Student name: " + s.getSname()); System.out.println("Student ID: " + s.getSid()); System.out.println("Student City: " + s.getAddress().getCity()); System.out.println("Student Adress ID: " + s.getAddress().getAid()); System.out.println("Student Zipcode: " + s.getAddress().getZipcode()); System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~" ); } em.getTransaction().commit(); } } |
Project directory structure
Output:
Hibernate: select student0_.sid as sid1_1_, student0_.AID as AID3_1_, student0_.sname as sname2_1_ from STUDENT student0_ Hibernate: select address0_.aid as aid1_0_0_, address0_.city as city2_0_0_, address0_.zipcode as zipcode3_0_0_ from ADDRESS address0_ where address0_.aid=? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Student name: Manu Manjunatha Student ID: 1 Student City: Bangalore Student Adress ID: 1 Student Zipcode: 560010 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Student name: Advith Tyagraj Student ID: 2 Student City: Bangalore Student Adress ID: 1 Student Zipcode: 560010 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Student name: Likitha Student ID: 3 Student City: Bangalore Student Adress ID: 1 Student Zipcode: 560010 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Student name: Tyagraj Student ID: 4 Student City: Bangalore Student Adress ID: 1 Student Zipcode: 560010 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Hibernate: select student0_.sid as sid1_1_, student0_.AID as AID3_1_, student0_.sname as sname2_1_ from STUDENT student0_ cross join ADDRESS address1_ where student0_.AID=address1_.aid and student0_.sid<? and address1_.city=? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Student name: Manu Manjunatha Student ID: 1 Student City: Bangalore Student Adress ID: 1 Student Zipcode: 560010 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Student name: Advith Tyagraj Student ID: 2 Student City: Bangalore Student Adress ID: 1 Student Zipcode: 560010 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Student name: Likitha Student ID: 3 Student City: Bangalore Student Adress ID: 1 Student Zipcode: 560010 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Student name: Tyagraj Student ID: 4 Student City: Bangalore Student Adress ID: 1 Student Zipcode: 560010 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Hibernate: select student0_.sid as sid1_1_, student0_.AID as AID3_1_, student0_.sname as sname2_1_ from STUDENT student0_ where student0_.sid=1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Student name: Manu Manjunatha Student ID: 1 Student City: Bangalore Student Adress ID: 1 Student Zipcode: 560010 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ |
All Chapters
