JPA Criteria WHERE 子句

2024 年 8 月 29 日 | 阅读 3 分钟

WHERE 子句用于在数据库上应用条件，并根据该条件获取数据。在 Criteria API 中，AbstractQuery 接口的 where() 方法用于设置条件。

Criteria WHERE 示例

在这里，我们将在学生表上执行几个 WHERE 操作。假设该表包含以下记录：-

现在，按照以下步骤执行操作：-

在 com.javatpoint.jpa 包下创建一个名为 StudentEntity.java 的实体类。该类包含三个属性 s_id、s_name、s_age 以及所有必需的注解。

StudentEntity.java

package com.javatpoint.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.javatpoint.jpa.StudentEntity</class>

      <properties>
         <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
         <property name="javax.persistence.jdbc.url" value="jdbc:mysql://: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>

一旦我们创建了基本的实体类并将配置映射到 persistence.xml 文件中，我们可以按以下方式执行不同类型的 select 操作：-

JPQL 大于和小于

Comparison.java

package com.javatpoint.jpa.jpql;
import com.javatpoint.jpa.StudentEntity;
import javax.persistence.*;
import javax.persistence.criteria.*;

import java.util.*;
public class Comparison {
	
	
	public static void main( String args[]) {
		   
		 
		 EntityManagerFactory emf = Persistence.createEntityManagerFactory( "Student_details" );
	      EntityManager em = emf.createEntityManager();
	      em.getTransaction().begin( );
	      
	      CriteriaBuilder cb=em.getCriteriaBuilder();
	      
	      AbstractQuery<StudentEntity> cq1=cb.createQuery(StudentEntity.class);
	      AbstractQuery<StudentEntity> cq2=cb.createQuery(StudentEntity.class);
	      
	     Root<StudentEntity> stud1=cq1.from(StudentEntity.class);
         
	    cq1.where(cb.greaterThan(stud1.get("s_age"), 22));
	    
	      CriteriaQuery<StudentEntity> select1 = ((CriteriaQuery<StudentEntity>) cq1).select(stud1);
	      TypedQuery<StudentEntity> tq1 = em.createQuery(select1);
	      List<StudentEntity> list1 = tq1.getResultList();
          
	      System.out.println("Students having age greater than 22");
	      
	      System.out.print("s_id");
		     System.out.print("\t s_name");
		     System.out.println("\t s_age");
	      
	      for(StudentEntity s:list1)
	      {
	      System.out.print(s.getS_id());
	      System.out.print("\t"+s.getS_name());
	      System.out.println("\t"+s.getS_age());
	      }
	      
	      Root<StudentEntity> stud2=cq2.from(StudentEntity.class);
	         
		    
		    cq2.where(cb.lessThan(stud2.get("s_age"), 22));
		    
		      CriteriaQuery<StudentEntity> select2 = ((CriteriaQuery<StudentEntity>) cq2).select(stud2);
		      TypedQuery<StudentEntity> tq2 = em.createQuery(select2);
		      List<StudentEntity> list2 = tq2.getResultList();

		      System.out.println("Students having age Less than 22");
		      
		      System.out.print("s_id");
			     System.out.print("\t s_name");
			     System.out.println("\t s_age");
		      
		      for(StudentEntity s:list2)
		      {
		      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 Between

Between.java

package com.javatpoint.jpa.jpql;
import com.javatpoint.jpa.StudentEntity;
import javax.persistence.*;
import javax.persistence.criteria.*;

import java.util.*;
public class Between {
	
	
	public static void main( String args[]) {
		   
		 
		 EntityManagerFactory emf = Persistence.createEntityManagerFactory( "Student_details" );
	      EntityManager em = emf.createEntityManager();
	      em.getTransaction().begin( );
	      
	      CriteriaBuilder cb=em.getCriteriaBuilder();
	      
	      AbstractQuery<StudentEntity> cq=cb.createQuery(StudentEntity.class);
	     
	      
	     Root<StudentEntity> stud=cq.from(StudentEntity.class);
         
	    
	     cq.where(cb.between(stud.get("s_age"), 22, 26)) ;
	      CriteriaQuery<StudentEntity> select = ((CriteriaQuery<StudentEntity>) cq).select(stud);
	      TypedQuery<StudentEntity> tq = em.createQuery(select);
	      List<StudentEntity> list = tq.getResultList();
          
	    System.out.println("Students having age between 22 and 26");
	      
	      System.out.print("s_id");
		     System.out.print("\t s_name");
		     System.out.println("\t s_age");
	      
	      for(StudentEntity s:list)
	      {
	      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 Like

Like.java

package com.javatpoint.jpa.jpql;
import com.javatpoint.jpa.StudentEntity;
import javax.persistence.*;
import javax.persistence.criteria.*;

import java.util.*;
public class Like {
	
	
	public static void main( String args[]) {
		   
		 
		 EntityManagerFactory emf = Persistence.createEntityManagerFactory( "Student_details" );
	      EntityManager em = emf.createEntityManager();
	      em.getTransaction().begin( );
	      
	      CriteriaBuilder cb=em.getCriteriaBuilder();
	      
	      AbstractQuery<StudentEntity> cq=cb.createQuery(StudentEntity.class);
	     
	      
	     Root<StudentEntity> stud=cq.from(StudentEntity.class);
         
	
	     cq.where(cb.like(stud.get("s_name"), "R%"));
	      CriteriaQuery<StudentEntity> select = ((CriteriaQuery<StudentEntity>) cq).select(stud);
	      TypedQuery<StudentEntity> tq = em.createQuery(select);
	      List<StudentEntity> list = tq.getResultList();
          
	    System.out.println("Students name starting with R");
	      
	      System.out.print("s_id");
		     System.out.print("\t s_name");
		     System.out.println("\t s_age");
	      
	      for(StudentEntity s:list)
	      {
	      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 In

In.java

package com.javatpoint.jpa.jpql;
import com.javatpoint.jpa.StudentEntity;
import javax.persistence.*;
import javax.persistence.criteria.*;

import java.util.*;
public class In.java {
	
	
	public static void main( String args[]) {
		   
		 
		 EntityManagerFactory emf = Persistence.createEntityManagerFactory( "Student_details" );
	      EntityManager em = emf.createEntityManager();
	      em.getTransaction().begin( );
	      
	      CriteriaBuilder cb=em.getCriteriaBuilder();
	      
	      AbstractQuery<StudentEntity> cq=cb.createQuery(StudentEntity.class);
	     
	      
	     Root<StudentEntity> stud=cq.from(StudentEntity.class);
         
	    
	     cq.where(cb.in(stud.get("s_age")).value(22).value(24));
	      CriteriaQuery<StudentEntity> select = ((CriteriaQuery<StudentEntity>) cq).select(stud);
	      TypedQuery<StudentEntity> tq = em.createQuery(select);
	      List<StudentEntity> list = tq.getResultList();
          
	    System.out.println("Students having age 22 and 24");
	      
	      System.out.print("s_id");
		     System.out.print("\t s_name");
		     System.out.println("\t s_age");
	      
	      for(StudentEntity s:list)
	      {
	      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();  
	 }
}

输出