JPA--单表操作二、（条件查询、统计查询、排序查询、分组查询、分页查询）

单表操作

表与数据与上篇相同，点击跳转

条件查询

占位符
JPQL 语句支持两种方式的参数定义方式 : 命名参数和位置参数 。 在同一个查询语句中只允许使用一种参数定义方式。

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

位置参数

@Test
public 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'}

命名参数

@Test
public 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相同的语义。

@Test
public 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_
5
15
3.0
5
1

排序查询

@Test
public 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 desc
Customer{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'}

分组查询

@Test
public 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<Object> resultList = query.getResultList();
    System.out.println("登记：个数");
    for(Object o : resultList){
        Object[] objs = (Object[])o;
        System.out.println(objs[0] + ":" + objs[1]);
    }

    transaction.commit();
    entityManager.close();
}

控制台打印：

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中的第二个？。

@Test
public 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'}