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

柔光的暖阳◎ 2021-08-19 12:11 780阅读 0赞

### 单表操作 ###

[表与数据与上篇相同，点击跳转][Link 1]

### 条件查询 ###

**占位符**  
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();
    }

控制台打印：

命名参数

@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();
    }

控制台打印：

### 统计查询 ###

*  聚集函数  
    查询可以返回作用于属性之上的聚集函数的计算结果：  
    受支持的聚集函数如下：  
    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();
    }

控制台打印：

[Link 1]: https://blog.csdn.net/cold___play/article/details/103551744