我想把这个sql转换成querydsl,我应该怎么做?

sz81bmfz  于 2021-07-09  发布在  Java
关注(0)|答案(1)|浏览(485)

我按日期进行sql统计。
这意味着按日期划分的访客总数。

  1. select sum(r.count) from (SELECT DATE_FORMAT(reg_date,'%Y-%m-%d') m, COUNT(distinct client_ip) as count FROM request_log GROUP BY m) as r;

以下是此sql的结果[在此处输入图像描述][1]
所以,我想把这个换成 QueryDSL . 我阅读了以下文件并尝试了这些。http://www.querydsl.com/static/querydsl/3.6.3/reference/ko-kr/html_single/#alias

  1. @Transactional
  2. @SpringBootTest
  3. public class QueryDslTest {
  4. @PersistenceContext
  5. EntityManager em;
  6. @Test
  7. @DisplayName("QueryDSL_TEST")
  8. public void testQuerydsl() throws Exception {
  9. JPAQueryFactory queryFactory = new JPAQueryFactory(em);
  10. QRequestLog requestLog = new QRequestLog("requestLog");
  11. StringTemplate dateFormat = Expressions.stringTemplate(
  12. "DATE_FORMAT({0}, {1})"
  13. , requestLog.regDate
  14. , ConstantImpl.create("%Y-%m-%d"));
  15. JPQLQuery<Long> countDistinct = JPAExpressions.select(requestLog.clientIp.countDistinct());
  16. QueryResults<Tuple> count = queryFactory
  17. .select(requestLog.regDate, requestLog.clientIp.countDistinct().as("count"))
  18. .from(requestLog)
  19. .groupBy(dateFormat, requestLog.regDate)
  20. .fetchResults();
  21. // Long fetch = queryFactory
  22. // .select(Projections.fields(Long.class, ExpressionUtils.as(
  23. // select(requestLog.regDate, requestLog.clientIp.countDistinct().as("c"))
  24. // .from(requestLog)
  25. // .groupBy(dateFormat, requestLog.regDate), "r")
  26. // ))
  27. // .from(requestLog)
  28. // .fetchOne();
  29. // JPAQuery<Tuple> tupleJPAQuery = queryFactory
  30. // .select(dateFormat, countDistinct)
  31. // .from(requestLog)
  32. // .groupBy(dateFormat);
  33. //
  34. // queryFactory
  35. // .select(tupleJPAQuery)
  36. System.out.println("count = " + count);
  37. // queryFactory
  38. // .select(requestLog)
  39. // .from(
  40. // select(requestLog.regDate.as("d"), requestLog.clientIp.countDistinct().as("c"))
  41. // .from(requestLog)
  42. // .groupBy(requestLog.as("d"))
  43. // )
  44. }
  45. }```
  46. I don't know how to use an subquery alias.
  47. Can you give me a hint?
  48. [1]: https://i.stack.imgur.com/WUMkl.jpg
j13ufse2

j13ufse21#

我使用了mysql的date函数。这不是日期格式函数!
但我仍然不知道如何解释子查询。
所以,我使用了select查询和java流求和。
我还是觉得我需要多学习。

  1. @Override
  2. @Transactional(readOnly = true)
  3. public Long allVisitors() {
  4. JPAQueryFactory queryFactory = new JPAQueryFactory(getEntityManager());
  5. return queryFactory
  6. .select(requestLog.clientIp.countDistinct())
  7. .from(requestLog)
  8. .groupBy(functionDate(requestLog.regDate))
  9. .fetch()
  10. .stream()
  11. .reduce(0L, Long::sum);
  12. }
  13. private StringTemplate functionDate(DateTimePath regDate) {
  14. return stringTemplate("date({0})", regDate);
  15. }
展开查看全部

相关问题