JPA JPQL高级操作

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

假设当前student表有如下数据 -

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

SQL

JPQL高级查询示例

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

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

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

}

Java

现在，将实体类和其他数据库配置映射到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>

XML

现在，我们可以在StudentEntity.java类中执行以下操作。

1. JPQL筛选器

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

文件:Filter.java -

package com.yiibai.jpa.jpql;

import com.yiibai.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("	 s_name");
        System.out.println("	 s_age");

        for (StudentEntity s : l1) {
            System.out.print(s.getS_id());
            System.out.print("	" + s.getS_name());
            System.out.println("	" + 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("	 s_name");
        System.out.println("	 s_age");

        for (StudentEntity s : l2) {
            System.out.print(s.getS_id());
            System.out.print("	" + s.getS_name());
            System.out.println("	" + 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("	 s_name");
        System.out.println("	 s_age");

        for (StudentEntity s : l3) {
            System.out.print(s.getS_id());
            System.out.print("	" + s.getS_name());
            System.out.println("	" + s.getS_age());
        }

        em.getTransaction().commit();
        em.close();
        emf.close();
    }
}

Java

执行上面示例代码，得到以下结果 -

Between Clause
s_id     s_name     s_age
101    Gaurav    24
102    Rahul    22
104    Ronit    26
IN Clause
s_id     s_name     s_age
102    Rahul    22
103    Chris    20
Like Clause
s_id     s_name     s_age
101    Gaurav    24
102    Rahul    22

Shell

2. JPQL聚合

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

文件:Aggregate.java -

package com.yiibai.jpa.jpql;

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

Java

执行上面示例代码，得到以下结果 -

Number of Student : 5
Maximum age : 26
Minimum age : 20

Shell

3. JPQL排序

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

文件: Sorting.java -

package com.yiibai.jpa.jpql;

import com.yiibai.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("	 s_name");
        System.out.println("	 s_age");

        for (StudentEntity s : l1) {
            System.out.print(s.getS_id());
            System.out.print("	" + s.getS_name());
            System.out.println("	" + s.getS_age());
        }

        em.getTransaction().commit();
        em.close();
        emf.close();
    }
}

Java

执行上面示例代码，得到以下结果 -

s_id     s_name     s_age
104    Ronit    26
101    Gaurav    24
102    Rahul    22
105    Roy    21
103    Chris    20