JPA Criteria ORDER BY排序 JPA Criteria GROUP BY条件

JPA Criteria WHERE条件

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

JPA Criteria WHERE条件示例

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

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.TypedQuery;
import javax.persistence.criteria.AbstractQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import java.util.List;

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

package com.yiidian.test;

import com.yiidian.pojo.StudentEntity;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.AbstractQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import java.util.List;

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操作

package com.yiidian.test;

import com.yiidian.pojo.StudentEntity;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.AbstractQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import java.util.List;

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"), "%花%"));
        CriteriaQuery<StudentEntity> select = ((CriteriaQuery<StudentEntity>) cq).select(stud);
        TypedQuery<StudentEntity> tq = em.createQuery(select);
        List<StudentEntity> list = tq.getResultList();

        System.out.println("学生姓名中包含'花'：");

        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操作

package com.yiidian.test;

import com.yiidian.pojo.StudentEntity;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.AbstractQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Root;
import java.util.List;

public class In {

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

输出结果为：

JPA Criteria ORDER BY排序 JPA Criteria GROUP BY条件

JPA Criteria WHERE条件

JPA Criteria WHERE条件示例

StudentEntity实体类

persistence.xml文件

JPQL 大于和小于

JPQL Between

JPQL Like操作

JPQL In操作

微信关注