本文共 7611 字，大约阅读时间需要 25 分钟。

单表操作

条件查询

占位符

• 位置参数：“ ?+ 位置编号”
• 命名参数：“ : + 参数名”

位置参数

@Testpublic void selectByTest(){
       EntityManager entityManager = JpaUtils.getEntityManager();    EntityTransaction transaction = entityManager.getTransaction();    transaction.begin();    String spql = "from Customer where custName = ?1";    Query query = entityManager.createQuery(spql);    //设置参数    query.setParameter(1, "万达");    Object singleResult = query.getSingleResult();    System.out.println(singleResult);    transaction.commit();    entityManager.close();}

控制台打印：

Hibernate: select customer0_.cust_id as cust_id1_0_, customer0_.cust_address as cust_add2_0_, customer0_.cust_industry as cust_ind3_0_, customer0_.cust_level as cust_lev4_0_, customer0_.cust_name as cust_nam5_0_, customer0_.cust_phone as cust_pho6_0_, customer0_.cust_source as cust_sou7_0_ from cst_customer customer0_ where customer0_.cust_name=?Customer{
   custId=3, custName='万达', custSource='null', custIndustry='房地产', custLevel='三', custAddress='大连', custPhone='33333333'}

命名参数

@Testpublic void selectByTest(){
       EntityManager entityManager = JpaUtils.getEntityManager();    EntityTransaction transaction = entityManager.getTransaction();    transaction.begin();    String spql = "from Customer where custAddress = :address";    Query query = entityManager.createQuery(spql);    //设置参数    query.setParameter("address", "广州");    List resultList = query.getResultList();    for(Object o : resultList)        System.out.println(o);    transaction.commit();    entityManager.close();}

控制台打印：

Hibernate: select customer0_.cust_id as cust_id1_0_, customer0_.cust_address as cust_add2_0_, customer0_.cust_industry as cust_ind3_0_, customer0_.cust_level as cust_lev4_0_, customer0_.cust_name as cust_nam5_0_, customer0_.cust_phone as cust_pho6_0_, customer0_.cust_source as cust_sou7_0_ from cst_customer customer0_ where customer0_.cust_address=?Customer{
   custId=4, custName='恒大', custSource='null', custIndustry='矿泉水', custLevel='四', custAddress='广州', custPhone='44444444'}Customer{
   custId=5, custName='网易', custSource='null', custIndustry='游戏', custLevel='五', custAddress='广州', custPhone='55555555'}

统计查询

• 聚集函数
  查询可以返回作用于属性之上的聚集函数的计算结果：
  受支持的聚集函数如下：
  avg(…), sum(…), min(…), max(…)
  count(*)
  count(…), count(distinct…), count(all…)
  可以选择子句中使用数学操作符、连接以及经过验证的SQL函数：
  关键字distinct 与 all 也可以使用，它们具有与SQL相同的语义。

@Testpublic void selectCountTest(){
       EntityManager entityManager = JpaUtils.getEntityManager();    EntityTransaction transaction = entityManager.getTransaction();    transaction.begin();    String jpql1 = "select count(*) from pers.zhang.domain.Customer";    String jpql2 = "select sum(custId) from pers.zhang.domain.Customer";    String jpql3 = "select avg(custId) from pers.zhang.domain.Customer";    String jpql4 = "select max(custId) from pers.zhang.domain.Customer";    String jpql5 = "select min(custId) from pers.zhang.domain.Customer";    Query query1 = entityManager.createQuery(jpql1);    Query query2 = entityManager.createQuery(jpql2);    Query query3 = entityManager.createQuery(jpql3);    Query query4 = entityManager.createQuery(jpql4);    Query query5 = entityManager.createQuery(jpql5);    Object singleResult1 = query1.getSingleResult();    Object singleResult2 = query2.getSingleResult();    Object singleResult3 = query3.getSingleResult();    Object singleResult4 = query4.getSingleResult();    Object singleResult5 = query5.getSingleResult();    System.out.println(singleResult1);    System.out.println(singleResult2);    System.out.println(singleResult3);    System.out.println(singleResult4);    System.out.println(singleResult5);    transaction.commit();    entityManager.close();}

控制台打印：

Hibernate: select count(*) as col_0_0_ from cst_customer customer0_Hibernate: select sum(customer0_.cust_id) as col_0_0_ from cst_customer customer0_Hibernate: select avg(customer0_.cust_id) as col_0_0_ from cst_customer customer0_Hibernate: select max(customer0_.cust_id) as col_0_0_ from cst_customer customer0_Hibernate: select min(customer0_.cust_id) as col_0_0_ from cst_customer customer0_5153.051

排序查询

@Testpublic void selectByOrderTest(){
       EntityManager entityManager = JpaUtils.getEntityManager();    EntityTransaction transaction = entityManager.getTransaction();    transaction.begin();    String spql = "from Customer order by custId desc";    Query query = entityManager.createQuery(spql);    //设置参数    List resultList = query.getResultList();    for(Object o : resultList)        System.out.println(o);    transaction.commit();    entityManager.close();}

控制台打印：

Hibernate: select customer0_.cust_id as cust_id1_0_, customer0_.cust_address as cust_add2_0_, customer0_.cust_industry as cust_ind3_0_, customer0_.cust_level as cust_lev4_0_, customer0_.cust_name as cust_nam5_0_, customer0_.cust_phone as cust_pho6_0_, customer0_.cust_source as cust_sou7_0_ from cst_customer customer0_ order by customer0_.cust_id descCustomer{
   custId=5, custName='网易', custSource='null', custIndustry='游戏', custLevel='五', custAddress='广州', custPhone='55555555'}Customer{
   custId=4, custName='恒大', custSource='null', custIndustry='矿泉水', custLevel='四', custAddress='广州', custPhone='44444444'}Customer{
   custId=3, custName='万达', custSource='null', custIndustry='房地产', custLevel='三', custAddress='大连', custPhone='33333333'}Customer{
   custId=2, custName='腾讯', custSource='null', custIndustry='互联网', custLevel='二', custAddress='深圳', custPhone='22222222'}Customer{
   custId=1, custName='阿里巴巴', custSource='修改后的', custIndustry='电商', custLevel='一', custAddress='杭州', custPhone='11111111'}

分组查询

@Testpublic void selectByGroupTest(){
       EntityManager entityManager = JpaUtils.getEntityManager();    EntityTransaction transaction = entityManager.getTransaction();    transaction.begin();    String spql = "select custLevel,count(*) from Customer group by custLevel";    Query query = entityManager.createQuery(spql);    //设置参数    List

控制台打印：

Hibernate: select customer0_.cust_level as col_0_0_, count(*) as col_1_0_ from cst_customer customer0_ group by customer0_.cust_level登记：个数一:1三:1二:1五:1四:1

分页查询

JPQL的分页查询与MySql的limit十分相似，使用两个方法：

• query.setFirstResult(arg)，设置第一条结果从哪个索引开始，相当于limit中的第一个?。
• query.setMaxResults(arg)，设置一次查询多少条数据，相遇于limit中的第二个？。

@Testpublic void selectPageTest(){
       EntityManager entityManager = JpaUtils.getEntityManager();    EntityTransaction transaction = entityManager.getTransaction();    transaction.begin();    String spql = "from Customer";    Query query = entityManager.createQuery(spql);    //设置参数    query.setFirstResult(0);    query.setMaxResults(2);    List resultList = query.getResultList();    for(Object o : resultList)        System.out.println(o);    transaction.commit();    entityManager.close();}

控制台打印：

Hibernate: select customer0_.cust_id as cust_id1_0_, customer0_.cust_address as cust_add2_0_, customer0_.cust_industry as cust_ind3_0_, customer0_.cust_level as cust_lev4_0_, customer0_.cust_name as cust_nam5_0_, customer0_.cust_phone as cust_pho6_0_, customer0_.cust_source as cust_sou7_0_ from cst_customer customer0_ limit ?Customer{
   custId=1, custName='阿里巴巴', custSource='修改后的', custIndustry='电商', custLevel='一', custAddress='杭州', custPhone='11111111'}Customer{
   custId=2, custName='腾讯', custSource='null', custIndustry='互联网', custLevel='二', custAddress='深圳', custPhone='22222222'}

转载地址：http://czpqb.baihongyu.com/