JPA JPQL高级操作

本文概述

• JPQL高级查询示例
• JPQL过滤器
• JPQL汇总
• JPQL排序

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

让我们考虑具有以下记录的学生表。

JPQL高级查询示例

在此示例中, 我们将使用基本实体类（在本例中为StudentEntity.java）, 并对它执行不同的操作。

• 在com.srcmini.jpa包下创建一个名为StudentEntity.java的实体类。

StudentEntity.java

package com.srcmini.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

<persistence>
<persistence-unit name="Student_details">
   
      <class>com.srcmini.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/studentdata"/>
         <property name="javax.persistence.jdbc.user" value="root"/>
         <property name="javax.persistence.jdbc.password" value=""/>
         <property name="eclipselink.logging.level" value="SEVERE"/>
         <property name="eclipselink.ddl-generation" value="create-or-extend-tables"/>
      </properties>
      
   </persistence-unit>
</persistence>

• 现在, 我们可以对StudentEntity.java类执行以下任何操作。

JPQL过滤器

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

Filter.java

package com.srcmini.jpa.jpql;
import com.srcmini.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();  
	 }
}

输出：

JPQL汇总

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

Aggregate.java

package com.srcmini.jpa.jpql;
import com.srcmini.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();  
	 }
}

输出：

JPQL排序

在这里, 我们将基于s_age属性对表的元素进行排序。

Sorting.java

package com.srcmini.jpa.jpql;
import com.srcmini.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();  
	 }
}

输出：