group by 问题

jpql中想对日期(年月日),进行分组统计, group by e.scsj, 按2019-05-07,类似于这样的格式统计,需要怎么做

从文档中看,cast, function都不能用,需要怎么处理

建议直接用native sql。因为你的日期格式转换函数也不是每个数据库都通用的。

function可以用吧,这是JPQL支持的。比如下面的例子是使用了PostGres数据库自己的’convert_to’方法。

LoadContext<Company> l = LoadContext.create(Company.class);
l.setQueryString("select u from my$Company u order by function('convert_to', u.name,'GB18030')");
List<Company> companies = dataManager.loadList(l);

image
实测不行.
写原生sql的话,返回的字段值又全是Object的,无法使用NEW XXX ,得全部强转一遍.
有没有更好得办法 :upside_down_face:

你的意思是返回的数据集处理起来不方便?

我还用过一个方法,用数据库的view(数据库视图),然后对这个view创建实体;这样jpql直接写 select * from viewName 就可以了。
但是这样需要针对一个查询建立一个view。

或者jpql的返回绑定到一个keyValueDataSource上。

我是需要从某个持久化实体(数据库表)查询数据,然后进行group,sum等聚合操作后,将返回的值输出到新的实体上,所以我采用了jpql的select NEW一个非持久化实体来接收属性.

但是如果我使用原生sql的话,就没有select NEW这种用法了,无法返回对应的TypeQueue,返回值都是Object类型,需要我一个个进行强转,类似这样.
image

所以我的问题可以简化为能否使用原生sql,返回值可以直接对应到实体,无需强转.
或者能否使用jpql,执行group by年月日这种操作.

原生sql也可以对应到实体:

createNativeQuery

<T extends Entity> TypedQuery createNativeQuery(java.lang.String sqlString, java.lang.Class resultClass)

Create an instance of Query for executing a native SQL statement and map its result to an entity.

Parameters:

sqlString - a native SQL query string

resultClass - expected result class

Returns:

the new query instance

原生SQL看下楼上的回复。
另外,如果用JPQL,就需要返回到keyValueEntity上,参见这个例子

List<KeyValueEntity> list = dataManager.loadValues(
        "select o.customer, sum(o.amount) from demo_Order o " +
        "where o.date >= :date group by o.customer")
    .store("legacy_db") 
    .properties("customer", "sum") 
    .parameter("date", orderDate)
    .list();

我有尝试这种的,以下为代码:

            TypedQuery<Fee> query = em.createNativeQuery("SELECT SUM(c.duration_time) AS 'time',SUM(IF(c.call_result=1,1,0)) AS 'member',COUNT(DISTINCT(c.robot_id)) AS 'robot', any_value(p.price) AS 'price' " +
                    "FROM `tbl_call_records` c , `tbl_price` p " +
                    "WHERE c.created_by = 'wjc'", Fee.class);

实体部分
image

报错日志

Exception [EclipseLink-6007] (Eclipse Persistence Services - 2.7.3.6-cuba): org.eclipse.persistence.exceptions.QueryException
Exception Description: Missing descriptor for [class com.voiceai.cubarest.entity.business.home.Fee].
Query: ReadAllQuery(referenceClass=Fee sql="SELECT SUM(c.duration_time) AS 'time',SUM(IF(c.call_result=1,1,0)) AS 'member',COUNT(DISTINCT(c.robot_id)) AS 'robot', any_value(p.price) AS 'price' FROM `tbl_call_records` c , `tbl_price` p WHERE c.created_by = 'wjc'")
javax.persistence.PersistenceException: Exception [EclipseLink-6007] (Eclipse Persistence Services - 2.7.3.6-cuba): org.eclipse.persistence.exceptions.QueryException
Exception Description: Missing descriptor for [class com.voiceai.cubarest.entity.business.home.Fee].
Query: ReadAllQuery(referenceClass=Fee sql="SELECT SUM(c.duration_time) AS 'time',SUM(IF(c.call_result=1,1,0)) AS 'member',COUNT(DISTINCT(c.robot_id)) AS 'robot', any_value(p.price) AS 'price' FROM `tbl_call_records` c , `tbl_price` p WHERE c.created_by = 'wjc'")
	at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:493)
	at com.haulmont.cuba.core.sys.QueryImpl.getResultFromCache(QueryImpl.java:764)
	at com.haulmont.cuba.core.sys.QueryImpl.getResultList(QueryImpl.java:412)
	at com.voiceai.cubarest.core.RecordServiceBeanTest.recordStatistic(RecordServiceBeanTest.java:78)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:675)
	at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:125)
	at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:132)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:124)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:74)
	at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:104)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:62)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:43)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:35)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:202)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:198)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:135)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:69)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at java.util.ArrayList.forEach(ArrayList.java:1249)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at java.util.ArrayList.forEach(ArrayList.java:1249)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
	at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
	at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:220)
	at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:188)
	at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:202)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:181)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:128)
	at org.gradle.api.internal.tasks.testing.junitplatform.JUnitPlatformTestClassProcessor$CollectAllTestClassesExecutor.processAllTestClasses(JUnitPlatformTestClassProcessor.java:102)
	at org.gradle.api.internal.tasks.testing.junitplatform.JUnitPlatformTestClassProcessor$CollectAllTestClassesExecutor.access$000(JUnitPlatformTestClassProcessor.java:82)
	at org.gradle.api.internal.tasks.testing.junitplatform.JUnitPlatformTestClassProcessor.stop(JUnitPlatformTestClassProcessor.java:78)
	at org.gradle.api.internal.tasks.testing.SuiteTestClassProcessor.stop(SuiteTestClassProcessor.java:61)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:36)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
	at org.gradle.internal.dispatch.ContextClassLoaderDispatch.dispatch(ContextClassLoaderDispatch.java:33)
	at org.gradle.internal.dispatch.ProxyDispatchAdapter$DispatchingInvocationHandler.invoke(ProxyDispatchAdapter.java:94)
	at com.sun.proxy.$Proxy2.stop(Unknown Source)
	at org.gradle.api.internal.tasks.testing.worker.TestWorker.stop(TestWorker.java:132)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:36)
	at org.gradle.internal.dispatch.ReflectionDispatch.dispatch(ReflectionDispatch.java:24)
	at org.gradle.internal.remote.internal.hub.MessageHubBackedObjectConnection$DispatchWrapper.dispatch(MessageHubBackedObjectConnection.java:182)
	at org.gradle.internal.remote.internal.hub.MessageHubBackedObjectConnection$DispatchWrapper.dispatch(MessageHubBackedObjectConnection.java:164)
	at org.gradle.internal.remote.internal.hub.MessageHub$Handler.run(MessageHub.java:412)
	at org.gradle.internal.concurrent.ExecutorPolicy$CatchAndRecordFailures.onExecute(ExecutorPolicy.java:64)
	at org.gradle.internal.concurrent.ManagedExecutorImpl$1.run(ManagedExecutorImpl.java:48)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
	at org.gradle.internal.concurrent.ThreadFactoryImpl$ManagedThreadRunnable.run(ThreadFactoryImpl.java:56)
	at java.lang.Thread.run(Thread.java:745)
Caused by: Exception [EclipseLink-6007] (Eclipse Persistence Services - 2.7.3.6-cuba): org.eclipse.persistence.exceptions.QueryException
Exception Description: Missing descriptor for [class com.voiceai.cubarest.entity.business.home.Fee].
Query: ReadAllQuery(referenceClass=Fee sql="SELECT SUM(c.duration_time) AS 'time',SUM(IF(c.call_result=1,1,0)) AS 'member',COUNT(DISTINCT(c.robot_id)) AS 'robot', any_value(p.price) AS 'price' FROM `tbl_call_records` c , `tbl_price` p WHERE c.created_by = 'wjc'")
	at org.eclipse.persistence.exceptions.QueryException.descriptorIsMissing(QueryException.java:480)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.checkDescriptor(ObjectLevelReadQuery.java:877)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:834)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.execute(ObjectLevelReadQuery.java:1180)
	at org.eclipse.persistence.queries.ReadAllQuery.execute(ReadAllQuery.java:466)
	at org.eclipse.persistence.queries.ObjectLevelReadQuery.executeInUnitOfWork(ObjectLevelReadQuery.java:1268)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:3020)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1892)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1874)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1839)
	at org.eclipse.persistence.internal.jpa.QueryImpl.executeReadQuery(QueryImpl.java:262)
	at org.eclipse.persistence.internal.jpa.QueryImpl.getResultList(QueryImpl.java:482)
	... 91 more

另外我搜索别人出现这种问题,最后也是强转解决的…
:slightly_smiling_face:

感谢回复,但是k/v方式局限性较大,一次只能聚合出一个值,我大部分业务逻辑都是需要多个聚合值的. :innocent: