JPA 高级操作

使用 JPQL，我们可以执行任何类型的数据库操作。在这里，我们将使用简单的示例来执行 JPQL 的一些高级操作。

让我们以下面的学生表记录来做一些高级查询。

JPQL 高级查询示例

项目结构如下：

在本例中，我们将采用一个基本实体类（在本例中为 StudentEntity.java）并对其执行不同的操作。

StudentEntity实体类

package com.yiidian.pojo;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@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文件

<?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.yiidian.pojo.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/jpa?serverTimezone=UTC&amp;useSSL=false" />
            <property name="javax.persistence.jdbc.user" value="root" />
            <property name="javax.persistence.jdbc.password" value="root" />
            <property name="eclipselink.logging.level" value="SEVERE" />
            <property name="eclipselink.ddl-generation" value="create-or-extend-tables" />
        </properties>
    </persistence-unit>

</persistence>

JPQL 过滤操作

在这里，我们将对表执行一些过滤操作。

package com.yiidian.test;

import com.yiidian.pojo.StudentEntity;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import java.util.List;

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");

          List<StudentEntity> l1 =  (List<StudentEntity>)q1.getResultList();
           
         System.out.println("Between条件");
         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)");

         List<StudentEntity> l2 =  (List<StudentEntity>)q2.getResultList();
               
         System.out.println("IN条件");
         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%'");

          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();    
     }  
}  

输出结果为：

JPQL 聚合操作

在这里，我们将对表执行一些聚合操作。

package com.yiidian.test;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;

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("学生总数 : "+q1.getSingleResult());
               
          Query q2 = em.createQuery("Select MAX(s.s_age) from StudentEntity s");  
          System.out.println("最大年龄 : "+q2.getSingleResult());
            
          Query q3 = em.createQuery("Select MIN(s.s_age) from StudentEntity s");  
          System.out.println("最小年龄 : "+q3.getSingleResult());
          em.getTransaction().commit();
          em.close();  
          emf.close();    
     }  
}  

输出结果为：

JPQL 排序操作

在这里，我们将根据 s_age 属性对 table 的元素进行排序。

package com.yiidian.test;

import com.yiidian.pojo.StudentEntity;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.Query;
import java.util.List;

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");

          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();    
     }  
}  

输出结果为：