JPQL(JPAの照会文)

21852 ワード
勉強します
一JPQLとSQL
  • 1.JPQLとSQLは似ています。検索キーワードは同じ
    • です。
  • .唯一の違いは、JPQLがオブジェクトに向けられた
    • であることです。
    二、JPQL表記のルール
    JPAの検索言語はsqlに似ています。
  • .中には表名、列名が現れません。javaの類名、属性名、大文字と小文字を区別します。
  • .出現するsqlキーワードは同じ意味で、キーワードは大文字と小文字を区別しません。
  • .selectを書いてはいけません。selectの別名を書いてください。
    • 三、JPQLの簡単な検索
    package cn.itsource.test;

import cn.itsource.pojo.Department;
import cn.itsource.pojo.Employee;
import cn.itsource.pojo.Project;
import cn.itsource.utils.JpaUtils;
import org.junit.Test;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.List;

public class JpqlTest {

    /**
     * 2.2.1.      【      】
     * @throws Exception
     */
    @Test
    public void test01() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //  Query  
        Query query = entityManager.createQuery("from Employee");

        query.getResultList().forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     * 2.2.2.                【      】
     * @throws Exception
     */
    @Test
    public void test02() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //  Query  
        //                 【         】
        //String jpql = "select e.name,d.name from Employee e join e.department d";
        String jpql = "select e.name,e.department.name from Employee e";
        Query query = entityManager.createQuery(jpql);

        //                      ，             ，List           ，  Object[]
        //java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to cn.itsource.pojo.Employee
        List list = query.getResultList();

        list.forEach(e -> System.out.println(Arrays.asList(e)));

        JpaUtils.close(entityManager);
    }

    /**
     *                 【      】
     * @throws Exception
     */
    @Test
    public void test03() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //  Query  
        //                 【         】
        //String jpql = "select e from Employee e where e.department.city = '  ' or e.department.city = '  '";

        //String jpql = "select e from Employee e where e.department.city in ('  ','  ')";

        //              ，                
        //String jpql = "select e from Employee e where e.department.city in (?,?)";

        //            ，    【    】
        String jpql = "select e from Employee e where e.department.city in (?1,?2)";

        //     :        ?       ，                 [   ，     ]
        //String jpql = "select e from Employee e where e.department.city in (:city1,:city2)";


        Query query = entityManager.createQuery(jpql);
        //         【    1  ， JDBC PreparedStatement  】
        query = query.setParameter(1, "  ");
        query = query.setParameter(2, "  ");

        List list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     * 2.2.4.         ，      【    】
     * @throws Exception
     */
    @Test
    public void test04() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //  Query  [   SQL         ]
        String jpql = "select e from Employee e order by e.salary desc";

        Query query = entityManager.createQuery(jpql);


        List list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }

    /**
     *          ，        【           】
     * @throws Exception
     */
    @Test
    public void test05() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //  Query  [   SQL         ]
        String jpql = "select e from Employee e order by e.department.sn asc";

        Query query = entityManager.createQuery(jpql);


        List list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     *                   【  IN】
     * @throws Exception
     */
    @Test
    public void test06() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //  Query  [   SQL         ]
        String jpql = "select e from Employee e where e.department.street in (?1, ?2)";

        Query query = entityManager.createQuery(jpql);

        //    
        query = query.setParameter(1, "   ").setParameter(2, "   ");

        List list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     * 2.2.7.      5000-6000   【  BETWEEN..AND..】
     * @throws Exception
     */
    @Test
    public void test07() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //  Query  [   SQL         ]
        String jpql = "select e from Employee e where e.salary between ?1 and ?2";

        Query query = entityManager.createQuery(jpql);

        //    [java.lang.IllegalArgumentException: Parameter value [5000] did not match expected type [java.math.BigDecimal]
        query = query.setParameter(1, new BigDecimal(5000)).setParameter(2, new BigDecimal(6000));

        List list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }

    /**
     *        er  en   【  LIKE】
     * @throws Exception
     */
    @Test
    public void test08() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //  Query  [   SQL         ]
        String jpql = "select e from Employee e where e.name like ?1 or e.name like ?2";

        Query query = entityManager.createQuery(jpql);

        //    [       ，      ]
        query = query.setParameter(1, "%er%").setParameter(2, "%en%");

        List list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     *          【distinct】
     * @throws Exception
     */
    @Test
    public void test09() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //  Query  
        String jpql = "select distinct e.department.name from Employee e";

        Query query = entityManager.createQuery(jpql);

        List list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     *          【size】
     *  1）          size【        、     /      】
     *  2）size          、      、        
     * @throws Exception
     */
    @Test
    public void test10() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //       ，               
        //String jpql = "select distinct e.department from Employee e";

        //      ，            0
        String jpql = "select d from Department d where d.employees.size > 0";

        Query query = entityManager.createQuery(jpql);

        List list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }

    /**
     *        ，           【      】
     * @throws Exception
     */
    @Test
    public void test11() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

     
        String jpql = "select d from Department d order by d.employees.size";

        Query query = entityManager.createQuery(jpql);

        List list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }

    /**
     *             【     size】
     * @throws Exception
     */
    @Test
    public void test12() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //      ，            0
        String jpql = "select p from Project p where p.employees.size = 0";

        Query query = entityManager.createQuery(jpql);

        List list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     *               【     size】
     * @throws Exception
     */
    @Test
    public void test13() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        String jpql = "select p.name, p.employees.size from Department p";

        Query query = entityManager.createQuery(jpql);

        List list = query.getResultList();

        list.forEach(e -> System.out.println(Arrays.asList(e)));

        JpaUtils.close(entityManager);
    }


    /**
     *               【JOIN/LEFT JOIN】
     * @throws Exception
     */
    @Test
    public void test14() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //        
        //String jpql = "select e.name, e.department.name from Employee e";

        //       
        //String jpql = "select e.name, d.name from Employee e inner join e.department d";

        //      
        //String jpql = "select e.name, d.name from Employee e left join e.department d";

        //      
        String jpql = "select e.name, d.name from Department d right join d.employees e";


        Query query = entityManager.createQuery(jpql);

        List list = query.getResultList();

        list.forEach(e -> System.out.println(Arrays.asList(e)));

        JpaUtils.close(entityManager);
    }

    /**
     *              
     * @throws Exception
     */
    @Test
    public void test15() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //JPQL           ，       .  .  .  ....
        String jpql = "select p.employee.name, p.number from Phone p where p.employee.department.name = ?1";


        Query query = entityManager.createQuery(jpql);

        List list = query.setParameter(1, "   ").getResultList();

        list.forEach(e -> System.out.println(Arrays.asList(e)));

        JpaUtils.close(entityManager);
    }


    /**
     *       
     *                    【      】
     * @throws Exception
     */
    @Test
    public void test16() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //JPQL           ，       .  .  .  ....
        String jpql = "select e.department.name, max(e.salary), avg(e.salary) from Employee e group by e.department.name";


        Query query = entityManager.createQuery(jpql);

        List list = query.getResultList();

        list.forEach(e -> System.out.println(Arrays.asList(e)));

        JpaUtils.close(entityManager);
    }


    /**
     *              
     * @throws Exception
     */
    @Test
    public void test17() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //JPQL           ，       .  .  .  ....
        //String jpql = "select p.name,p.employees.size from Project p";
        String jpql = "select p.name,count(e.name) from Project p left join p.employees e group by p.name";


        Query query = entityManager.createQuery(jpql);

        List list = query.getResultList();

        list.forEach(e -> System.out.println(Arrays.asList(e)));

        JpaUtils.close(entityManager);
    }


    /**
     *    ：              
     * @throws Exception
     */
    @Test
    public void test18() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //JPQL           ，       .  .  .  ....
        String jpql = "select e from Employee e where e.salary > (select avg(ee.salary) from Employee ee)";

        Query query = entityManager.createQuery(jpql);

        List list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     *    ，    10   
     * @throws Exception
     */
    @Test
    public void test19() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //JPQL           ，       .  .  .  ....
        String jpql = "select e from Employee e";

        Query query = entityManager.createQuery(jpql);

        /**
         * setFirstResult：     limit            (pageNo - 1) * pageSize
         * setMaxResults：     limit           pageSize
         *          
         * pageNo pageSize             Controller ，      DAO 
         */
        query = query.setFirstResult(10).setMaxResults(10);


        List list = query.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }


    /**
     *    ，    10   --    
     * @throws Exception
     */
    @Test
    public void test20() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        //JPQL           ，       .  .  .  ....
        String jpql = "select count(e) from Employee e";

        Query query = entityManager.createQuery(jpql);

        //  count()            ，    getSingleResult       Single    
        //         Long    
        Long total = (Long)query.getSingleResult();

        System.out.println("    " + total + "    ");

        JpaUtils.close(entityManager);
    }


    /**
     *     SQL
     *  1）  createNativeQuery
     *  2）             
     *  3）      JPQL  
     * @throws Exception
     */
    @Test
    public void test21() throws Exception {
        EntityManager entityManager = JpaUtils.getEntityManager();

        String sql = "select * from employee where name like ?1";

        //  NativeQuery
        //Query nativeQuery = entityManager.createNativeQuery(sql);
        Query nativeQuery = entityManager.createNativeQuery(sql, Employee.class);

        //     
        nativeQuery.setParameter(1, "%en%");

        //      ，          Object[]    
        List list = nativeQuery.getResultList();

        list.forEach(e -> System.out.println(e));

        JpaUtils.close(entityManager);
    }

}
    pojo:Department.java
    package cn.itsource.pojo;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name="department")
public class Department {


    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;
    private String provice;// 
    private String city;//  
    private String street;

    //              【     】
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="manager_id")
    private Employee manager;//  

    private String sn;//   

    //          
    @OneToMany(fetch = FetchType.LAZY,mappedBy = "department")//      
    private List employees = new ArrayList<>();

    public List getEmployees() {
        return employees;
    }

    public void setEmployees(List employees) {
        this.employees = employees;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getProvice() {
        return provice;
    }

    public void setProvice(String provice) {
        this.provice = provice;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getStreet() {
        return street;
    }

    public void setStreet(String street) {
        this.street = street;
    }

    public Employee getManager() {
        return manager;
    }

    public void setManager(Employee manager) {
        this.manager = manager;
    }

    public String getSn() {
        return sn;
    }

    public void setSn(String sn) {
        this.sn = sn;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", provice='" + provice + '\'' +
                ", city='" + city + '\'' +
                ", street='" + street + '\'' +
                ", sn='" + sn + '\'' +
                '}';
    }
}
    Employee.java
    package cn.itsource.pojo;

import javax.persistence.*;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Entity
@Table(name="employee")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;

    //           【     】
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="department_id")
    private Department department;

    private BigDecimal salary;//  
    private Date hireDate;//    

    //           ，            【     】
    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name="project_employee",
        joinColumns = {@JoinColumn(name="EMPLOYEE_ID")},
        inverseJoinColumns = {@JoinColumn(name="PROJECT_ID")}
    )
    private List projects = new ArrayList<>();

    public List getProjects() {
        return projects;
    }

    public void setProjects(List projects) {
        this.projects = projects;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    public BigDecimal getSalary() {
        return salary;
    }

    public void setSalary(BigDecimal salary) {
        this.salary = salary;
    }

    public Date getHireDate() {
        return hireDate;
    }

    public void setHireDate(Date hireDate) {
        this.hireDate = hireDate;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", salary=" + salary +
                ", hireDate=" + hireDate +
                ", department_id=" + department.getSn() +
                '}';
    }
}
    Phone.java
    package cn.itsource.pojo;

import javax.persistence.*;

@Entity
@Table(name="phone")
public class Phone {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String types;//  
    private String number;

    //           【     】
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="employee_id")
    private Employee employee;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTypes() {
        return types;
    }

    public void setTypes(String types) {
        this.types = types;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public Employee getEmployee() {
        return employee;
    }

    public void setEmployee(Employee employee) {
        this.employee = employee;
    }
}
    Project.java
    package cn.itsource.pojo;

import javax.persistence.*;
import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name="project")
public class Project {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;

    //              【     】
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name="manager_id")
    private Employee manager;

    @ManyToMany(fetch = FetchType.LAZY,mappedBy = "projects")
    private List employees = new ArrayList<>();

    public List getEmployees() {
        return employees;
    }

    public void setEmployees(List employees) {
        this.employees = employees;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Employee getManager() {
        return manager;
    }

    public void setManager(Employee manager) {
        this.manager = manager;
    }

    @Override
    public String toString() {
        return "Project{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}
    My97 DatePicker日付コントロールの使い方（詳細な手順はケース付き）
    IUnityOfWork~私のIUnity OfWork+Repositoryアーキテクチャを教えてください。