-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 
  Previous topic | Next topic 
Author Message
difranr
 Post subject: Query problems with a Database view
PostPosted: Mon Dec 29, 2003 4:31 pm 
Beginner
Beginner

Joined: Wed Oct 01, 2003 3:35 pm
Posts: 35
I have the following mapping file for a database view under MS SQL 2000:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>
<!--
Created by Middlegen Hibernate plugin

http://boss.bekk.no/boss/middlegen/
http://hibernate.sourceforge.net/
-->

<class
name="com.tascon.tim.hibernate.RealtorVisitView"
table="realtor_visit_view"
>

<composite-id>
<key-property
name="createDate"
column="create_date"
type="java.sql.Timestamp"
length="20"
/>
<key-property
name="dayFormatted"
column="dayFormatted"
type="java.lang.String"
length="20"
/>
<key-property
name="createMonth"
column="createMonth"
type="java.lang.String"
length="20"
/>
<key-property
name="createYear"
column="createYear"
type="java.lang.String"
length="20"
/>
</composite-id>
<!-- associations -->

</class>
</hibernate-mapping>

implementation class:

package com.tascon.tim.hibernate;

import java.io.Serializable;
import org.apache.commons.lang.builder.EqualsBuilder;
import org.apache.commons.lang.builder.HashCodeBuilder;
import org.apache.commons.lang.builder.ToStringBuilder;

/** @author Hibernate CodeGenerator */
public class RealtorVisitView implements Serializable {

/** identifier field */
private java.util.Date createDate;

/** identifier field */
private java.lang.String dayFormatted;

/** identifier field */
private java.lang.String createMonth;

/** identifier field */
private java.lang.String createYear;

/** full constructor */
public RealtorVisitView(java.util.Date createDate, java.lang.String dayFormatted, java.lang.String createMonth, java.lang.String createYear) {
this.createDate = createDate;
this.dayFormatted = dayFormatted;
this.createMonth = createMonth;
this.createYear = createYear;
}

/** default constructor */
public RealtorVisitView() {
}

public java.util.Date getCreateDate() {
return this.createDate;
}

public void setCreateDate(java.util.Date createDate) {
this.createDate = createDate;
}

public java.lang.String getDayFormatted() {
return this.dayFormatted;
}

public void setDayFormatted(java.lang.String dayFormatted) {
this.dayFormatted = dayFormatted;
}

public java.lang.String getCreateMonth() {
return this.createMonth;
}

public void setCreateMonth(java.lang.String createMonth) {
this.createMonth = createMonth;
}

public java.lang.String getCreateYear() {
return this.createYear;
}

public void setCreateYear(java.lang.String createYear) {
this.createYear = createYear;
}

public String toString() {
return new ToStringBuilder(this)
.append("createDate", getCreateDate())
.append("dayFormatted", getDayFormatted())
.append("createMonth", getCreateMonth())
.append("createYear", getCreateYear())
.toString();
}

public boolean equals(Object other) {
if ( !(other instanceof RealtorVisitView) ) return false;
RealtorVisitView castOther = (RealtorVisitView) other;
return new EqualsBuilder()
.append(this.getCreateDate(), castOther.getCreateDate())
.append(this.getDayFormatted(), castOther.getDayFormatted())
.append(this.getCreateMonth(), castOther.getCreateMonth())
.append(this.getCreateYear(), castOther.getCreateYear())
.isEquals();
}

public int hashCode() {
return new HashCodeBuilder()
.append(getCreateDate())
.append(getDayFormatted())
.append(getCreateMonth())
.append(getCreateYear())
.toHashCode();
}

}

When I try to execute the following query:

"SELECT realtor.dayFormatted, count(realtor) "
+ " FROM RealtorVisitView as realtor"
+ " WHERE realtor.createDate >= :createDate1"
+ " and realtor.createDate - 7 < :createDate2"
+ " group by realtor.createDate, realtor.dayFormatted";

I get the following:

INFO | jvm 1 | 2003/12/29 15:25:09 | 15:25:08,912 INFO [STDOUT] exception: path expression ends in a composite value: realtorvis0_ [SELECT realtor.dayFormatted, count(realtor) FROM com.tascon.tim.hibernate.RealtorVisitView as realtor WHERE realtor.createDate >= :createDate1 and realtor.createDate - 7 < :createDate2 group by realtor.createDate, realtor.dayFormatted]
INFO | jvm 1 | 2003/12/29 15:25:09 | 15:25:08,912 INFO [STDOUT] net.sf.hibernate.QueryException: path expression ends in a composite value: realtorvis0_ [SELECT realtor.dayFormatted, count(realtor) FROM com.tascon.tim.hibernate.RealtorVisitView as realtor WHERE realtor.createDate >= :createDate1 and realtor.createDate - 7 < :createDate2 group by realtor.createDate, realtor.dayFormatted]
INFO | jvm 1 | 2003/12/29 15:25:09 | at net.sf.hibernate.hql.PathExpressionParser.getWhereColumn(PathExpressionParser.java:370)
INFO | jvm 1 | 2003/12/29 15:25:09 | at net.sf.hibernate.hql.SelectParser.token(SelectParser.java:148)
INFO | jvm 1 | 2003/12/29 15:25:09 | at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
INFO | jvm 1 | 2003/12/29 15:25:09 | at net.sf.hibernate.hql.ClauseParser.end(ClauseParser.java:114)
INFO | jvm 1 | 2003/12/29 15:25:09 | at net.sf.hibernate.hql.PreprocessingParser.end(PreprocessingParser.java:143)
INFO | jvm 1 | 2003/12/29 15:25:09 | at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:30)
INFO | jvm 1 | 2003/12/29 15:25:09 | at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
INFO | jvm 1 | 2003/12/29 15:25:09 | at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
INFO | jvm 1 | 2003/12/29 15:25:09 | at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293)
INFO | jvm 1 | 2003/12/29 15:25:09 | at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1493)
INFO | jvm 1 | 2003/12/29 15:25:09 | at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1464)
INFO | jvm 1 | 2003/12/29 15:25:09 | at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
INFO | jvm 1 | 2003/12/29 15:25:09 | at com.tascon.tim.provider.hibernate.RealtorVisitViewProvider.getAssociateReportView(RealtorVisitViewProvider.java:43)
INFO | jvm 1 | 2003/12/29 15:25:09 | at com.tascon.tim.Reporter.trafficReport(Reporter.java:120)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.jsp.report_jsp._jspService(report_jsp.java:61)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:137)
INFO | jvm 1 | 2003/12/29 15:25:09 | at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:210)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
INFO | jvm 1 | 2003/12/29 15:25:09 | at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:220)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.valves.CertificatesValve.invoke(CertificatesValve.java:246)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.jboss.web.tomcat.tc4.statistics.ContainerStatsValve.invoke(ContainerStatsValve.java:76)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2417)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:65)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:577)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:197)
INFO | jvm 1 | 2003/12/29 15:25:09 | at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:781)
INFO | jvm 1 | 2003/12/29 15:25:10 | at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:549)
INFO | jvm 1 | 2003/12/29 15:25:10 | at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:605)
INFO | jvm 1 | 2003/12/29 15:25:10 | at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:677)
INFO | jvm 1 | 2003/12/29 15:25:10 | at java.lang.Thread.run(Thread.java:534)
Top
 Profile  
 
difranr
 Post subject:
PostPosted: Mon Dec 29, 2003 5:13 pm 
Beginner
Beginner

Joined: Wed Oct 01, 2003 3:35 pm
Posts: 35
I asolved my own problem, seems as though the following works as expected:

SELECT realtor.dayFormatted, count(*) FROM RealtorVisitView as realtor
WHERE realtor.createDate >= :date1 and realtor.createDate - 7 :date2
group by realtor.dayFormatted
Top
 Profile  
 
emmanuel
 Post subject:
PostPosted: Mon Dec 29, 2003 5:14 pm 
Hibernate Team
Hibernate Team

Joined: Sun Sep 14, 2003 3:54 am
Posts: 7256
Location: Paris, France
Try to add appropriate parenthesis in the where clause

_________________
Emmanuel

Top
 Profile  
 
difranr
 Post subject:
PostPosted: Tue Dec 30, 2003 4:03 pm 
Beginner
Beginner

Joined: Wed Oct 01, 2003 3:35 pm
Posts: 35
I tried adding parenthesis, but that did not work either. The only thing that seemed to work okay is adding changing the count() statemetn to read count(*).
Top
 Profile  
 
postop
 Post subject:
PostPosted: Wed Mar 24, 2004 4:53 pm 
Newbie

Joined: Sun Jan 18, 2004 5:03 pm
Posts: 12
Location: Texas
Does using (*) versus (object) cause any performance hits on the database?
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 5 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.