我正在尝试使用hibernate 3.2.5计算列数
createsqlQuery,
因为我用where语句,
它使用正则表达式(它在HQL中不起作用).
数据库是Postgre.
这是我的查询：
@Override
public Long CountFilterListOrder(Integer idUser) {
return (Long)sessionFactory.getCurrentSession()
.createsqlQuery("select COUNT(*) from ipony.orders where entryuser_id = :idUser and lastname ~* '^(John)$'")
.addEntity(Orders.class)
.setInteger("idUser",idUser)
.uniqueResult();
}
我的实体：
@Entity
@Table(name = "orders")
@NamedQueries({
@NamedQuery(name = "Orders.findAll",query = "SELECT o FROM Orders o"),})
public class Orders implements Serializable {
// COLUMNS-------------
private final static SimpleDateFormat ft =new SimpleDateFormat ("yyyy-MM-dd H:mm:ss");
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;
// ЗАКАЗ
@NotEmpty(message = "Поле не может быть пустым")
private String numberOrder;
// ПОЛУЧАТЕЛЬ
@NotEmpty(message = "Поле не может быть пустым")
private String consumerFname;
@NotEmpty(message = "Поле не может быть пустым")
private String consumerLname;
@NotEmpty(message = "Поле не может быть пустым")
private String consumerMname;
@NotEmpty(message = "Поле не может быть пустым")
private String consumerPhone;
@ManyToOne
private StreetType streettype;
//private Region region;
// ЗАКАЗ
@NotEmpty(message = "Поле не может быть пустым")
private String numberOrder;
// ПОЛУЧАТЕЛЬ
@NotEmpty(message = "Поле не может быть пустым")
private String consumerFname;
@NotEmpty(message = "Поле не может быть пустым")
private String consumerLname;
@NotEmpty(message = "Поле не может быть пустым")
private String consumerMname;
@NotEmpty(message = "Поле не может быть пустым")
private String consumerPhone;
@ManyToOne
private StreetType streettype;
//private Region region;
@NotEmpty(message = "Поле не может быть пустым")
private String street;
@NotEmpty(message = "Поле не может быть пустым")
private String house;
@NotEmpty(message = "Поле не может быть пустым")
private String houseroom;
private String consumerDescr;
private Integer confirmOrder = 0; //
private Boolean isDeleted = false;
@Basic(optional = true)
@ManyToOne(fetch= FetchType.LAZY,cascade = CascadeType.MERGE)
private City orderCity;
@Basic(optional = true)
@Temporal(javax.persistence.TemporalType.TIMESTAMP)
private Date entryDate = Calendar.getInstance().getTime();
@Basic(optional=true)
@Temporal(javax.persistence.TemporalType.TIMESTAMP)
private Date onDate;
@Basic(optional = true)
@ManyToOne(cascade= CascadeType.MERGE)
private Puser entryUser;
@OneToMany(mappedBy = "orderId",fetch= FetchType.LAZY,cascade= CascadeType.MERGE )
private List<OrderItem> orderItems;
@ManyToOne(fetch= FetchType.LAZY,cascade= CascadeType.MERGE)
private PaymentMethods paymentMethodItem;
@ManyToOne(fetch= FetchType.LAZY,cascade= CascadeType.MERGE)
private DeliveryMethods deliveryMethods;
//---------------------- GETSET
@DateTimeFormat(pattern="yyyy-MM-dd")
public Date getOnDate() {
return onDate;
}
public Boolean getIsDeleted() {
return isDeleted;
}
public void setIsDeleted(Boolean isDeleted) {
this.isDeleted = isDeleted;
}
public void setOnDate(Date onDate) {
this.onDate = onDate;
}
public DeliveryMethods getDeliveryMethods() {
return deliveryMethods;
}
public void setDeliveryMethods(DeliveryMethods deliveryMethods) {
this.deliveryMethods = deliveryMethods;
}
public Orders(Integer id) {
this.id = id;
}
public Orders() {
}
public boolean addItems(OrderItem i) {
if (orderItems == null) {
orderItems = new ArrayList<OrderItem>();
}
if (i != null && !orderItems.contains(i)) {
orderItems.add(i);
i.setOrderId(this);
return true;
}
return false;
}
public String getHouse() {
return house;
}
public void setHouse(String house) {
this.house = house;
}
public String getHouseroom() {
return houseroom;
}
public void setHouseroom(String houseroom) {
this.houseroom = houseroom;
}
public String getStreet() {
return street;
}
public void setStreet(String street) {
this.street = street;
}
public StreetType getStreettype() {
return streettype;
}
public void setStreettype(StreetType streettype) {
this.streettype = streettype;
}
// GET SET :::::::::::::::::::::::::::::::::::::::::
public PaymentMethods getPaymentMethodItem() {
return paymentMethodItem;
}
public void setPaymentMethodItem(PaymentMethods paymentMethodItem) {
this.paymentMethodItem = paymentMethodItem;
}
public Integer getConfirmOrder() {
return confirmOrder;
}
public void setConfirmOrder(Integer confirmOrder) {
this.confirmOrder = confirmOrder;
}
public List<OrderItem> getOrderItems() {
return (orderItems != null) ? Collections.unmodifiableList(orderItems) : Collections.EMPTY_LIST;
}
public void setOrderItems(List<OrderItem> orderItems) {
this.orderItems = orderItems;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getNumberOrder() {
return numberOrder;
}
public void setNumberOrder(String numberOrder) {
this.numberOrder = numberOrder;
}
public City getOrderCity() {
return orderCity;
}
public void setOrderCity(City orderCity) {
this.orderCity = orderCity;
}
public String getConsumerDescr() {
return consumerDescr;
}
public void setConsumerDescr(String consumerDescr) {
this.consumerDescr = consumerDescr;
}
public String getConsumerFname() {
return consumerFname;
}
public void setConsumerFname(String consumerFname) {
this.consumerFname = consumerFname;
}
public String getConsumerLname() {
return consumerLname;
}
public void setConsumerLname(String consumerLname) {
this.consumerLname = consumerLname;
}
public String getConsumerMname() {
return consumerMname;
}
public void setConsumerMname(String consumerMname) {
this.consumerMname = consumerMname;
}
public String getConsumerPhone() {
return consumerPhone;
}
public void setConsumerPhone(String consumerPhone) {
this.consumerPhone = consumerPhone;
}
public String getEntryDate() {
return ft.format(entryDate);
}
public void setEntryDate(Date entryDate) {
this.entryDate = entryDate;
}
public Puser getEntryUser() {
return entryUser;
}
public void setEntryUser(Puser entryUser) {
this.entryUser = entryUser;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof Orders)) {
return false;
}
Orders other = (Orders) object;
if((this.id == null && other.id != null) || (this.id != null &&
this.id.equals(other.id))) {
return false;
}
return true;
}
@Override
public String toString() {
return "org.ironlizard.domain.MasterTarif[ id=" + id + " ]";
}
}
但它返回一个错误
此ResultSet中不存在列标识
окт 12,2012 1:40:11 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet [app] in context with path [] threw exception [Request processing Failed; nested exception is rg.hibernate.exception.sqlGrammarException: could not execute query] with root cause org.postgresql.util.PsqlException: Имя колонки id не найдено в этом ResultSet'е. atorg.postgresql.jdbc2.AbstractJdbc2ResultSet.findColumn(AbstractJdbc2ResultSet.java:2562) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getInt(AbstractJdbc2ResultSet.java:2426) at org.hibernate.type.IntegerType.get(IntegerType.java:28) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154) at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1097) at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:565) at org.hibernate.loader.Loader.doQuery(Loader.java:701) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at org.hibernate.loader.Loader.doList(Loader.java:2220) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104) at org.hibernate.loader.Loader.list(Loader.java:2099) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289) at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695) at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142) at org.hibernate.impl.sqlQueryImpl.list(sqlQueryImpl.java:152) at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:811) at dao.OrderDAOImpl.CountFilterListOrder(OrderDAOImpl.java:75) at service.OrderServiceImpl.FilterOrderAllCount(OrderServiceImpl.java:352) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) at $Proxy324.FilterOrderAllCount(Unknown Source) at web.OrderController.serverSideOrderlist(OrderController.java:631) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:436) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:424) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:669) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:585) at javax.servlet.http.HttpServlet.service(HttpServlet.java:641) at javax.servlet.http.HttpServlet.service(HttpServlet.java:722) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:369) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:109) at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:381) at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:97) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:381) at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:100) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:381) at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:78) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:381) at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:119) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:381) at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:381) at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:35) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:381) at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:187) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:381) at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:79) at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:381) at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:168) at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:237) at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1001) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603) at java.lang.Thread.run(Thread.java:722)
.addEntity(Orders.class)告诉Hibernate将查询结果转换为实体.这显然是错误的,因为您的查询返回标量值而不是实体.所以,你需要摆脱它：
return ((Number) sessionFactory.getCurrentSession()
.createsqlQuery("select COUNT(*) from ipony.orders where entryuser_id = :idUser and lastname ~* '^(John)$'")
.setInteger("idUser",idUser)
.uniqueResult()).longValue();
