JPA JPQL高級操作
使用JPQL，我們可以執行任何類型的數據庫操作。 在這裏，我們將使用簡單的例子來執行JPQL的一些高級操作。
假設當前student表有如下數據 -
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`S_ID` int(11) NOT NULL,
`S_NAME` varchar(255) DEFAULT NULL,
`S_AGE` int(11) DEFAULT NULL,
PRIMARY KEY (`S_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('101', 'Gaurav', '24');
INSERT INTO `student` VALUES ('102', 'Rahul', '22');
INSERT INTO `student` VALUES ('103', 'Chris', '20');
INSERT INTO `student` VALUES ('104', 'Ronit', '26');
INSERT INTO `student` VALUES ('105', 'Roy', '21');JPQL高級查詢示例
在這個例子中，我們將採用一個基本的實體類(StudentEntity.java)並對其執行不同的操作。
在com.yiibai.jpa包下創建一個名爲StudentEntity.java的實體類。
文件:StudentEntity.java -
package com.yiibai.jpa;
import javax.persistence.*;
@Entity
@Table(name = "student")
public class StudentEntity {
@Id
private int s_id;
private String s_name;
private int s_age;
public StudentEntity(int s_id, String s_name, int s_age) {
super();
this.s_id = s_id;
this.s_name = s_name;
this.s_age = s_age;
}
public StudentEntity() {
super();
}
public int getS_id() {
return s_id;
}
public void setS_id(int s_id) {
this.s_id = s_id;
}
public String getS_name() {
return s_name;
}
public void setS_name(String s_name) {
this.s_name = s_name;
}
public int getS_age() {
return s_age;
}
public void setS_age(int s_age) {
this.s_age = s_age;
}
}現在，將實體類和其他數據庫配置映射到Persistence.xml文件中。
文件:persistence.xml -
<?xml version="1.0" encoding="UTF-8"?>
<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/persistence_2_1.xsd">
<persistence-unit name="Student_details">
<class>com.yiibai.jpa.StudentEntity</class>
<properties>
<property name="javax.persistence.jdbc.driver"
value="com.mysql.jdbc.Driver" />
<property name="javax.persistence.jdbc.url"
value="jdbc:mysql://localhost:3306/testdb?serverTimezone=UTC" />
<property name="javax.persistence.jdbc.user" value="root" />
<property name="javax.persistence.jdbc.password"
value="123456" />
<property name="eclipselink.logging.level" value="SEVERE" />
<property name="eclipselink.ddl-generation"
value="create-or-extend-tables" />
</properties>
</persistence-unit>
</persistence>現在，我們可以在StudentEntity.java類中執行以下操作。
1. JPQL篩選器
在這裏，我們將在student表上執行一些過濾操作。
文件:Filter.java -
package com.yiibai.jpa.jpql;
import com.yiibai.jpa.StudentEntity;
import javax.persistence.*;
import java.util.*;
public class Filter {
public static void main(String args[]) {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("Student_details");
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
Query q1 = em.createQuery("Select s from StudentEntity s where s.s_age between 22 and 28");
@SuppressWarnings("unchecked")
List<StudentEntity> l1 = (List<StudentEntity>) q1.getResultList();
System.out.println("Between Clause");
System.out.print("s_id");
System.out.print("\t s_name");
System.out.println("\t s_age");
for (StudentEntity s : l1) {
System.out.print(s.getS_id());
System.out.print("\t" + s.getS_name());
System.out.println("\t" + s.getS_age());
}
Query q2 = em.createQuery("Select s from StudentEntity s where s.s_age IN(20,22,23)");
@SuppressWarnings("unchecked")
List<StudentEntity> l2 = (List<StudentEntity>) q2.getResultList();
System.out.println("IN Clause");
System.out.print("s_id");
System.out.print("\t s_name");
System.out.println("\t s_age");
for (StudentEntity s : l2) {
System.out.print(s.getS_id());
System.out.print("\t" + s.getS_name());
System.out.println("\t" + s.getS_age());
}
Query q3 = em.createQuery("Select s from StudentEntity s where s.s_name like '%a%'");
@SuppressWarnings("unchecked")
List<StudentEntity> l3 = (List<StudentEntity>) q3.getResultList();
System.out.println("Like Clause");
System.out.print("s_id");
System.out.print("\t s_name");
System.out.println("\t s_age");
for (StudentEntity s : l3) {
System.out.print(s.getS_id());
System.out.print("\t" + s.getS_name());
System.out.println("\t" + s.getS_age());
}
em.getTransaction().commit();
em.close();
emf.close();
}
}執行上面示例代碼，得到以下結果 -
Between Clause
s_id s_name s_age
101 Gaurav 24
102 Rahul 22
104 Ronit 26
IN Clause
s_id s_name s_age
102 Rahul 22
103 Chris 20
Like Clause
s_id s_name s_age
101 Gaurav 24
102 Rahul 222. JPQL聚合
在這裏，我們將在student表上執行一些聚合操作。
文件:Aggregate.java -
package com.yiibai.jpa.jpql;
import com.yiibai.jpa.StudentEntity;
import javax.persistence.*;
import java.util.*;
public class Aggregate {
public static void main(String args[]) {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("Student_details");
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
Query q1 = em.createQuery("Select count(s) from StudentEntity s");
System.out.println("Number of Student : " + q1.getSingleResult());
Query q2 = em.createQuery("Select MAX(s.s_age) from StudentEntity s");
System.out.println("Maximum age : " + q2.getSingleResult());
Query q3 = em.createQuery("Select MIN(s.s_age) from StudentEntity s");
System.out.println("Minimum age : " + q3.getSingleResult());
em.getTransaction().commit();
em.close();
emf.close();
}
}執行上面示例代碼，得到以下結果 -
Number of Student : 5
Maximum age : 26
Minimum age : 203. JPQL排序
在這裏，我們將基於s_age屬性(列)對錶的元素進行排序。
文件: Sorting.java -
package com.yiibai.jpa.jpql;
import com.yiibai.jpa.StudentEntity;
import javax.persistence.*;
import java.util.*;
public class Sorting {
public static void main(String args[]) {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("Student_details");
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
Query q1 = em.createQuery("Select s from StudentEntity s order by s.s_age desc");
@SuppressWarnings("unchecked")
List<StudentEntity> l1 = (List<StudentEntity>) q1.getResultList();
System.out.print("s_id");
System.out.print("\t s_name");
System.out.println("\t s_age");
for (StudentEntity s : l1) {
System.out.print(s.getS_id());
System.out.print("\t" + s.getS_name());
System.out.println("\t" + s.getS_age());
}
em.getTransaction().commit();
em.close();
emf.close();
}
}執行上面示例代碼，得到以下結果 -
s_id s_name s_age
104 Ronit 26
101 Gaurav 24
102 Rahul 22
105 Roy 21
103 Chris 20