我经常使用单独的查询来计算行数,以便在查询分页结果时设置总变量。
返回表中的用户总数
SELECT Count(*) FROM users WHERE organizationId = :organizationId
返回分页结果
SELECT * FROM users WHERE organizationId = :organizationId LIMIT :start, :end
public SearchResults findAll(User user, Paginator paginator) {
HashMap<String, Object> namedParameters = new HashMap<String, Object>();
namedParameters.put("organizationId", user.getOrganizationId());
namedParameters.put("userId", user.getId());
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
//Get count
int total = namedParameterJdbcTemplate.queryForInt(SQL_FIND_ALL_COUNT, namedParameters);
int start = 0;
int end = total;
if(paginator != null) {
start = paginator.getFirst();
end = paginator.getCount();
}
namedParameters.put("start", start);
namedParameters.put("end", end);
SqlRowSet results = namedParameterJdbcTemplate.queryForRowSet(SQL_FIND_ALL, namedParameters);
return new SearchResults(mapUser(results), total);
}
收集总数的唯一原因是我可以将其作为搜索结果的一部分返回,因为它是分页所必需的,因此我知道要向用户显示多少页结果,而无需实际传递结果并查找数据库中的每一条记录。
有没有办法在一个查询中做到这一点?因此,换句话说,获取总数,同时仍使用 LIMIT 条件仅查询特定数量(例如一次 10 条记录)。如果是这样,从性能的角度来看,将它们保留在单独的查询中是否更有意义?
像这样的东西?
SELECT *, (SELECT Count(*) FROM users WHERE organizationId = :organizationId) FROM users WHERE organizationId = :organizationId"
如果上述查询有效,我如何实际获取该子查询列,它是否有我可以引用的名称?
results.getInt("count"); //?
在子查询后添加一个ALIAS
SELECT *,
(
SELECT Count(*)
FROM users
WHERE organizationId = :organizationId
) `COUNT`
FROM users
WHERE organizationId = :organizationId
查询中的替代解决方案是使用CROSS JOIN
SELECT *, x.totalCount
FROM users,
(
SELECT Count(*) totalCount
FROM users
WHERE organizationId = :organizationId
) x
WHERE organizationId = :organizationId
两个观察结果:
1> 计数查询不应使用"*",而是可以编写如下查询:
从用户中选择计数(1),其中组织 ID = :组织ID
或
从用户中选择计数(列 1),其中组织 ID = :组织ID
2> 使用 count 作为子查询不是好主意,因为它可能会多次执行并增加性能开销。当然,您在性能上没有任何收获,因为它仍然是两个查询。
如果预期的"结果集"很大,我建议使用 2 个不同的查询。计数查询在开始时仅使用一次。对于后续页面,只能执行详细信息查询。
如果预期的"结果集"很小,则可以加载所有结果并执行客户端分页以获得最佳性能。在这种情况下,只对所有页面执行一个查询。