~~~ JPA中的復雜查詢 JPQL全稱 Java Persistence Query Language 基于首次在EJB2.0中引入的EJB查詢EJB查詢語言(EJB QL),Java持久化查詢語言(JPQL) 是一種可移植的查詢語言,旨在以面向對象表達式語言的表達式,將SQL語法和簡單查詢語義 綁定在一起,使用這種語言編寫的查詢是可移植的,可以被編譯成所有主流數據庫服務器上的 SQL 其特征與原生的SQL語句類似,并且完全面向對象,通過類名和屬性訪問,而不是表名和表的屬性 JPQL查詢 SQL:查詢的是表和表中的字段 jpql: 查詢的是實體類和類中的屬性 *jpql和sql語句的語法相似 1.查詢全部 2.分頁查詢 3.統計查詢 4.條件查詢 5.排序 ~~~ ***** 測試代碼 `JPQLTest.java` ~~~ package net.youworker; import net.youworker.domain.Customer; import net.youworker.utils.JpaUtils; import org.junit.Test; import javax.persistence.EntityManager; import javax.persistence.EntityTransaction; import javax.persistence.Query; import java.util.List; /** * @author: hcf * @qq: 46914685 * @email: 46914685@qq.com * @date: 2020-01-06 14:43 */ public class JPQLTest { /** * 查詢全部: * jqpl: from net.youworker.domain.Customer 或 from Customer * sql:select * from cst_customer */ @Test public void testFindAll() { // 1.獲取entityManager對象 EntityManager entityManager = JpaUtils.getEntityManager(); //開啟事務 EntityTransaction transaction = entityManager.getTransaction(); transaction.begin(); //查詢全部 // String jpql = "from net.youworker.domain.Customer"; //查詢實體類 String jpql = "from Customer"; //查詢實體類 Query query = entityManager.createQuery(jpql); //發送查詢,并封裝結果集 List resultList = query.getResultList(); System.out.println(resultList); //提交事務 transaction.commit(); //釋放資源 entityManager.close(); } /** * 排序查詢: * jqpl: from Customer order by custId desc * sql:select * from cst_customer order by cust_id desc */ @Test public void testSort() { // 1.獲取entityManager對象 EntityManager entityManager = JpaUtils.getEntityManager(); //開啟事務 EntityTransaction transaction = entityManager.getTransaction(); transaction.begin(); //查詢全部 String jpql = "from Customer order by custId desc"; //查詢實體類 Query query = entityManager.createQuery(jpql); //發送查詢,并封裝結果集 List resultList = query.getResultList(); System.out.println(resultList); //提交事務 transaction.commit(); //釋放資源 entityManager.close(); } /** * 使用jpql查詢,統計客戶的總數: * jqpl: select count(custId) from Customer * sql:select count(*) from cst_customer */ @Test public void testCount() { // 1.獲取entityManager對象 EntityManager entityManager = JpaUtils.getEntityManager(); //開啟事務 EntityTransaction transaction = entityManager.getTransaction(); transaction.begin(); //查詢全部 //根據jpql語句創建Query查詢對象 String jpql = "select count(custId) from Customer"; //查詢實體類 Query query = entityManager.createQuery(jpql); //發送查詢,并封裝結果集 /** * getResultList():直接將查詢結果封裝為list集合 * getSingleResult():得到唯一的結果集 */ Object singleResult = query.getSingleResult(); Long ii = (Long) singleResult; System.out.println(singleResult); System.out.println(ii); //提交事務 transaction.commit(); //釋放資源 entityManager.close(); } /** * 使用jpql分頁: * jqpl: from Customer * sql:select count(*) from cst_customer limit ?,? */ @Test public void testPage() { // 1.獲取entityManager對象 EntityManager entityManager = JpaUtils.getEntityManager(); //開啟事務 EntityTransaction transaction = entityManager.getTransaction(); transaction.begin(); //查詢全部 //根據jpql語句創建Query查詢對象 // String jpql = "from Customer"; //查詢實體類 String jpql = "from Customer order by custId desc "; //查詢實體類 Query query = entityManager.createQuery(jpql); //對參數賦值 -- 分頁參數 //起始索引 query.setFirstResult(0); //每頁查詢的條數 query.setMaxResults(2); //發送查詢,并封裝結果集 List resultList = query.getResultList(); System.out.println(resultList); //提交事務 transaction.commit(); //釋放資源 entityManager.close(); } /** * 條件查詢: * jqpl: from Customer where custName like ? * sql:select * from cst_customer where cust_name like ? */ @Test public void testSearch() { // 1.獲取entityManager對象 EntityManager entityManager = JpaUtils.getEntityManager(); //開啟事務 EntityTransaction transaction = entityManager.getTransaction(); transaction.begin(); //查詢全部 String jpql = "from Customer where custName like ?1 "; //查詢實體類 // String jpql = "from Customer"; //查詢實體類 Query query = entityManager.createQuery(jpql); //對參數賦值 -- 占位符參數 //第一個參數:占位符的索引位置(1開始),第二個參數:取值 List<Customer> resultList = query.setParameter(1, "%有%").getResultList(); //發送查詢,并封裝結果集 // List resultList = query.getResultList(); for (Customer obj : resultList) { System.out.println(obj.getCustId()); System.out.println(obj.getCustName()); } //提交事務 transaction.commit(); //釋放資源 entityManager.close(); } } ~~~