postgresql 使用正则表达式语句在休眠中使用 createSQLQuery 获取计数（*）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12854210/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-10-21 00:24:49 来源:igfitidea点击:
getting count(*) using createSQLQuery in hibernate with regular expression statement
提问by Zhandos
I'm trying to count columns with hibernate 3.2.5
我正在尝试使用 hibernate 3.2.5 计算列数
createSQLQuery,
创建SQL查询，
because I use where statement,
因为我使用 where 语句，
which use regular expression(it doesn't work in HQL).
使用正则表达式（它在 HQL 中不起作用）。
The database is Postgre.
数据库是 Postgre。
This is my query:
这是我的查询：
@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();
}
My Entity:
我的实体：
@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 + " ]";
}
}
But it returns an error like The column id does not exist in this ResultSet
但它返回一个错误，如 The column id does not exist in this 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)
How i can solve this error?
我该如何解决这个错误？
回答by axtavt
.addEntity(Orders.class)tells Hibernate to convert result of the query to entity. It's obviously wrong, because your query returns a scalar value rather than entity. So, you need to get rid of it:
.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();
Note that result of this query may be a value of some numeric type other than Long, in this case you need to add a conversion as shown above.
请注意，此查询的结果可能是除 之外的某种数字类型的值Long，在这种情况下，您需要添加如上所示的转换。
回答by ilgatnau
It happend to me the same and was the query, the problem was the SQL count(*), use instead this (as example):
它发生在我身上，并且是查询，问题是 SQL 计数（*），请改用这个（例如）：
SELECT COUNT(ipony.id) from ipony.orders where entryuser_id = :idUser and lastname ~* '^(John)$'
