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?

Please show the full, exact text of the error, including backtrace. — Craig Ringer
– Craig Ringer, Commented Oct 12, 2012 at 7:35
Ah, you're working on a system in Russian so you translated the error message, which is why you said "something like". Makes sense and thanks for doing it, it's just worth mentioning that kind of thing. — Craig Ringer
– Craig Ringer, Commented Oct 12, 2012 at 8:18
What about '\dt orders` from psql? Does the id column certainly exist? Could it be upper-case ("ID" or "Id") in the DB? Also, what JDBC driver version and PostgreSQL version are you using? — Craig Ringer
– Craig Ringer, Commented Oct 12, 2012 at 8:21
All the columns exist in database,also the "id".The jdbc driver is "postgresql-9.0-801.jdbc3". PostgreSQL version is "1.16.0". But I think the problem is not in jdbc driver or PostgreSQL, because the query as "select * from ipony.orders where entryuser_id = :idUser and lastname ~* '^(John)$'" works fine, but if I use one like "select name , login from ipony.orders where entryuser_id = :idUser and lastname ~* '^(John)$'" it returns the same error,even though I added the column id, it requires the other columns which weren't use. — Zhandos
– Zhandos, Commented Oct 12, 2012 at 8:33
1.16.0 isn't a PostgreSQL version. What does "select version()" say? — Craig Ringer
– Craig Ringer, Commented Oct 12, 2012 at 8:39

axtavt · Accepted Answer · 2012-10-12 09:05:35Z

15

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

answered Oct 12, 2012 at 9:05

axtavt

243k42 gold badges518 silver badges486 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zhandos Over a year ago

Thank you for your answer! In addition can you answer why the query like

@Override 	public List<Orders> listOrder(Integer idUser, Integer pageSize, 			Integer pageNumber,String search) { 		 		 		return sessionFactory.getCurrentSession() 				.createSQLQuery("select name,login from ipony.orders where entryuser_id = :idUser and lastname ~* '^(John)$'").addEntity(Orders.class) 				.setInteger("idUser", idUser) 				.setFirstResult(pageNumber) 				.setMaxResults(pageSize).list(); 	}

returns the same error

araqnid Over a year ago

@Zhandos: same reason, you are only returning two columns of the entity, not the entire thing: Hibernate will try to load every property declared in the entity from the result set.

araqnid Over a year ago

@Zhandos: You can write select {Orders.*} from ... to have Hibernate automatically put in all the requisite columns.

ilgatnau · Accepted Answer · 2013-10-01 11:00:01Z

0

It happend to me the same and was the query, the problem was the SQL count(*), use instead this (as example):

SELECT COUNT(ipony.id) from ipony.orders where entryuser_id = :idUser and lastname ~* '^(John)$'

answered Oct 1, 2013 at 11:00

ilgatnau

19

2 Comments

Germann Arlington Over a year ago

You are about 12 months late with your answer - in any case the difference is that count(*) will return full count of records the table after any where clauses are applied and count(anyField) will not include null values in that field in count.

Sandy Simonton Over a year ago

Agree, though I personally assumed ipony.id is the primary key (unique, not null) and so couldn't have null values in the count anyway.

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related