通过在JPA中设置“最大结果"来从表中获取记录数 [英] fetch the count of records from table by setting the Maximum results in JPA

问题描述

我使用JPA通过设置最大结果数(10)"从表Customer中获取记录数.

I use JPA to fetch the count of records from table Customer by setting the Maximum results(10).

期望是，
1.仅接收10作为计数.如果查询返回的计数大于10.
2.如果计数等于或小于10，则接收确切的交易次数

The expectation is,
1. to receive only 10 as count If the count returned by query is greater than 10.
2. to receive the exact number of transactions if the count is equal to or less than 10

我尝试通过以下方式实现.

I try to achieve in the below way.

Query query = em.createQuery("SELECT COUNT(c) FROM Customer c where c.countryId ='Canada' and c.lanuguage ='ENG'");
query.setMaxResults(10);
long customerCount = (Long)query.getSingleResult();

但是我没有得到预期的结果.当查询结果为100(我的意思是大于10)时，我可以看到查询正在返回100.

But I am not getting the result as expected. When the query results is 100 ( I mean more than 10), then I could see 100 is being returned by the query.

如果我的方法有误，谁能建议我?
或
在单个查询中不可能实现这一点吗?
或
还有其他最佳方法吗?

Can anyone please advise me If something is incorrect in my approach?
OR
Is it not possible to achieve this in single query?
OR
Any other best way to achieve this?

非常感谢

推荐答案

您将获得 COUNT ，那么为什么要使用最大结果?

You are getting COUNT so why you are using max result ?

您的结果将始终是单个结果，因为它将有COUNT个客户?

Your result will be always single result as it will be a number of Customers COUNT?

如果要获取总数，您的代码应该位于下一个

Your code should be next if you want to get the total count

Query query = em.createQuery("SELECT COUNT(c) FROM Customer c WHERE c.countryId ='Canada' AND c.lanuguage ='ENG'");
long customerCount = (Long)query.getSingleResult();

更新

AND如果您只想获得10个最大总数，则COUNT个结果比 其他10个您将获得真正的COUNT个结果，因此请尝试下一步

AND If you just want to get 10 as a maximum total count if COUNT result than 10 else you will got the real COUNT result, so please try next

Query query = em.createQuery("SELECT CASE WHEN COUNT(c) > 10 THEN 10 ELSE COUNT(c) END FROM Customer c WHERE c.countryId ='Canada' AND c.lanuguage ='ENG'");
int customerCount = (Integer)query.getSingleResult();

或者如果您需要登录类型，可以在数字中添加 l ，例如10l，例如下一个查询(注意:在下一个查询 10l 不是 101 ，而是10个长字符)

Or if you need it in logn type you can add l to number, for example 10l like in next query (NOTE: in next query 10l is not 101 but 10 in long type)

Query query = em.createQuery("SELECT CASE WHEN COUNT(c) > 10l THEN 10l ELSE COUNT(c) END FROM Customer c WHERE c.countryId ='Canada' AND c.lanuguage ='ENG'");
long customerCount = (Long)query.getSingleResult();

例如，如果要获取客户ID列表，则可以使用最大结果(假设客户具有ID类型为Integer的ID属性)

Anyway you can use maximum result if you want to get list of customer ids for example (assuming customer has id property with type Integer)

Query query = em.createQuery("SELECT c.id FROM Customer c WHERE c.countryId ='Canada' AND c.lanuguage ='ENG'");
query.setMaxResults(10);
List<Integer> customersIds = query.getResultList();

这篇关于通过在JPA中设置“最大结果"来从表中获取记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！