JPA標準WHERE子句

WHERE子句用於對數據庫應用條件並基於該條件獲取數據。在Criteria API中，AbstractQuery接口的where()方法用於設置查詢條件。

標準WHERE示例

在這裏，我們將在student表上執行多個WHERE操作。假設該表包含以下記錄 -

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

現在，請按照以下步驟執行操作: -

第1步: 創建一個實體類。在com.yiibai.jpa包下創建了StudentEntity.java類文件。該類包含三個屬性:s_id，s_name，s_age以及所有必需的註解。

文件: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;
    }

}

第2步: 將實體類和其他數據庫配置映射到 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>

這裏，我們創建了基本實體類並將配置映射到persistence.xml 文件中，可以通過以下方式執行不同類型的SELECT排序操作 -

1. JPQL大於和小於條件

在這裏，我們將通過一個簡單的示例演示如何從數據庫指定大於和小於條件查詢獲取符合條件的數據。

文件:Comparison.java -

package com.yiibai.jpa.jpql;

import com.yiibai.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();
    }
}

執行上面代碼，得到以下輸出結果 -

Students having age greater than 22
s_id     s_name     s_age
101    Gaurav    24
104    Ronit    26
Students having age Less than 22
s_id     s_name     s_age
103    Chris    20
105    Roy    21

2. JPQL Between條件

在這裏，我們將通過一個簡單的示例演示如何從數據庫指定Between條件查詢獲取符合條件的數據。

文件:Between.java -

package com.yiibai.jpa.jpql;

import com.yiibai.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();
    }
}

執行上面代碼，得到以下輸出結果 -

Students having age between 22 and 26
s_id     s_name     s_age
101    Gaurav    24
102    Rahul    22
104    Ronit    26

3. JPQL Like條件

在這裏，我們將通過一個簡單的示例演示如何從數據庫指定Like條件查詢獲取符合條件的數據。

文件:Like.java -

package com.yiibai.jpa.jpql;

import com.yiibai.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();
    }
}

執行上面示例代碼，得到以下結果 -

Students name starting with R
s_id     s_name     s_age
102    Rahul    22
104    Ronit    26
105    Roy    21

4. JPQL In條件

在這裏，我們將通過一個簡單的示例演示如何從數據庫指定In條件查詢獲取符合條件的數據。

文件:Like.java -

package com.yiibai.jpa.jpql;

import com.yiibai.jpa.StudentEntity;
import javax.persistence.*;
import javax.persistence.criteria.*;

import java.util.*;

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

執行上面示例代碼，得到以下結果 -

Students having age 22 and 24
s_id     s_name     s_age
101    Gaurav    24
102    Rahul    22

JPA教程

JPA實體

JPA實體管理

JPA集合映射

JPA映射類型

JPA級聯

JPA JPQL

JPA標準API