java查询语句
String hql = " select NEW com.hadventure.zonlolo.payment.dao.dto.WorkerPaymentRecord"
+ " (date(createTime) as typeDate,count(1) as count,imgType) "
+ "from PaymentRecord "
+ "where workerId=:workerId and date(createTime) in ("
+ dateStr
+ ")"
+ " group by date(createTime), imgType "
+ " order by date(createTime) desc ";
// 日期可能重复的数据集合
List<WorkerPaymentRecord> datalist = sessionFactory
.getCurrentSession().createSQLQuery(hql)
.setString("workerId", workerId).list();
查询返回的model模型(get,set方法没有贴, 代码片长 影响阅读, 都是自动生成的, 保证没问题)
public class WorkerPaymentRecord{
private String businessName;
private String taskUuid;
private long cnt;
private BigDecimal amount;
private Date creatTime;
private BigDecimal errorAmount;
private long errorCnt;
private BigDecimal total;
private String id;
// 一下字段用于前台显示每日收入明细使用
private String imgType;
private Date typeDate; // 时间 yyyy-MM-dd 格式
private String typeOneName;// 第一种类型名称
private int typeOneAmount;// 第一种类型数量
private String typeTwoName;// 第二种类型名称
private int typeTwoAmount;// 第二种类型数量
private int count; // 碎片数量
public WorkerPaymentRecord() {
}
public WorkerPaymentRecord(String id, long cnt, long errorCnt,
Date creatTime) {
this.id = id;
this.cnt = cnt;
this.errorCnt = errorCnt;
this.creatTime = creatTime;
}
public WorkerPaymentRecord(String businessName, String taskUuid, long cnt,
BigDecimal amount, Date creatTime) {
this.businessName = businessName;
this.taskUuid = taskUuid;
this.cnt = cnt;
this.amount = amount;
this.creatTime = creatTime;
}
public WorkerPaymentRecord(String businessName, String taskUuid, long cnt,
BigDecimal amount, Date creatTime, BigDecimal errorAmount,
long errorCnt) {
this.businessName = businessName;
this.taskUuid = taskUuid;
this.cnt = cnt;
this.amount = amount;
this.creatTime = creatTime;
this.errorAmount = errorAmount;
this.errorCnt = errorCnt;
}
public WorkerPaymentRecord(Date typeDate, int count, String imgType) {
this.typeDate = typeDate;
this.count = count;
this.imgType = imgType;
}
}错误log:
2015-09-30 15:26:31,312 DEBUG [com.hadventure.zonlolo.ps.web.worker.WorkerAccountRestController] (http-bio-8888-exec-8:) incoming rest call /worker/account/income/{page} with : workerId=, page=1, pageSize=10
2015-09-30 15:26:31,687 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-bio-8888-exec-8:) SQL Error: 1064, SQLState: 42000
2015-09-30 15:26:31,687 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-bio-8888-exec-8:) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.hadventure.zonlolo.payment.dao.dto.WorkerPaymentRecord (date(createTime) as typ' at line 1
2015-09-30 15:26:31,796 INFO [com.hadventure.zonlolo.common.web.rest.RestResponseEntityExceptionHandler] (http-bio-8888-exec-8:) REST return ErrorResponse: RestErrorResponse[status=500, message=Data access layer error, exception=could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet]
九月 30, 2015 3:26:31 下午 org.apache.catalina.core.ApplicationDispatcher invoke
SEVERE: Servlet.service() for servlet jsp threw exception
java.lang.IllegalStateException: getOutputStream() has already been called for this response
at org.apache.catalina.connector.Response.getWriter(Response.java:639)
at org.apache.catalina.connector.ResponseFacade.getWriter(ResponseFacade.java:214)
at javax.servlet.ServletResponseWrapper.getWriter(ServletResponseWrapper.java:105)
at org.apache.jasper.runtime.JspWriterImpl.initOut(JspWriterImpl.java:125)
at org.apache.jasper.runtime.JspWriterImpl.flushBuffer(JspWriterImpl.java:118)
at org.apache.jasper.runtime.PageContextImpl.release(PageContextImpl.java:182)
at org.apache.jasper.runtime.JspFactoryImpl.internalReleasePageContext(JspFactoryImpl.java:126)
at org.apache.jasper.runtime.JspFactoryImpl.releasePageContext(JspFactoryImpl.java:80)
at org.apache.jsp.WEB_002dINF.errors._500_jsp._jspService(_500_jsp.java:113)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:432)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:390)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:334)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:749)
at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:605)
at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:544)
at org.apache.catalina.core.StandardHostValve.custom(StandardHostValve.java:461)
at org.apache.catalina.core.StandardHostValve.throwable(StandardHostValve.java:412)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:201)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:947)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1009)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:722)
九月 30, 2015 3:26:31 下午 org.apache.catalina.core.StandardHostValve custom
SEVERE: Exception Processing ErrorPage[exceptionType=java.lang.Exception, location=/WEB-INF/errors/500.jsp]------解决思路----------------------
可以这样写select new map(createTime as typeDate,count(1) as count,imgType) from WorkerPaymentRecord u ，返回的是一个Object[]希望对你对你有帮助
------解决思路----------------------
这个不是SQL的错误吧。。。
------解决思路----------------------
你这是直接把异常抛出去了没有自己捕获吧？