getting count(*) using createSQLQuery in hibernate with regular expression statement

Question

I'm trying to count columns with hibernate 3.2.5

createSQLQuery,

because I use where statement,

which use regular expression(it doesn't work in HQL).

The database is 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

окт 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?

Answer 1

.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:

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.