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

hibernate postgresql select count resultset
Zhandos picture Zhandos · Oct 12, 2012 · Viewed 16.8k times · Source

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

axtavt picture axtavt · Oct 12, 2012

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

Related questions

Connecting PostgreSQL 9.2.1 with Hibernate

I have a blank Spring MVC project, and I've installed Hibernate and the PostgreSQL drivers using Maven. I'm running short on complete tutorials that show how to connect PostgreSQL with Hibernate. Any help here?

Read More
How do you add PostgreSQL Driver as a dependency in Maven?

I'm trying to develop a Java application with Maven while using Hibernate with a PostgreSQL database for persistence. I don't understand how I'm supposed to connect the PostgreSQL drivers to my application. I get that you add dependencies in Maven's …

Read More
proper hibernate annotation for byte[]

I have an application using hibernate 3.1 and JPA annotations. It has a few objects with byte[] attributes (1k - 200k in size). It uses the JPA @Lob annotation, and hibernate 3.1 can read these just fine on all major databases -- …

Read More