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);
实测不行.
写原生sql的话,返回的字段值又全是Object的,无法使用NEW XXX ,得全部强转一遍.
有没有更好得办法 
你的意思是返回的数据集处理起来不方便？
我还用过一个方法，用数据库的view（数据库视图），然后对这个view创建实体；这样jpql直接写 select * from viewName 就可以了。
但是这样需要针对一个查询建立一个view。
或者jpql的返回绑定到一个keyValueDataSource上。
我是需要从某个持久化实体(数据库表)查询数据,然后进行group,sum等聚合操作后,将返回的值输出到新的实体上,所以我采用了jpql的select NEW一个非持久化实体来接收属性.
但是如果我使用原生sql的话,就没有select NEW这种用法了,无法返回对应的TypeQueue,返回值都是Object类型,需要我一个个进行强转,类似这样.
所以我的问题可以简化为能否使用原生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 classReturns:
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);
实体部分
报错日志
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
另外我搜索别人出现这种问题,最后也是强转解决的…
感谢回复,但是k/v方式局限性较大,一次只能聚合出一个值,我大部分业务逻辑都是需要多个聚合值的. 