Error fetching audited data which has oneTomany relationship
patel_123 Jan 9, 2011 5:06 PMHello,
I have two entities Quotes and QuotesClient which has @oneTomany relationship.I am able to audit the data properly.But while fetching data i get "org.hibernate.exception.SQLGrammarException:" exception and
Caused by: java.sql.SQLException: Table 'quoteappdb1.quotes_quotesclient_aud' doesn't exist(i pasted whole exception at the end).
Could somebody help me in resolving this error?
From the exception its clear that it s trying to look for "quotes_quotesclient_aud" table which does not exist becuase my audit table name is "quotes_client_aud".
I pasted my mapping files below.
Other information:
audit table names: quotes_aud and quotes_client_aud
Mapping files:
Quotes.hbm.xml:
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping default-access="field">
<class name="quoteapp.vo.Quotes" table="quotes" >
<id name="id" type="java.lang.Integer">
<column name="QUOTE_ID" />
<generator class="native" />
</id>
<many-to-one name="divisions" class="quoteapp.vo.Divisions" fetch="join" lazy="false">
<column name="FK_DIVISION_ID" not-null="true" />
</many-to-one>
<many-to-one name="staff" class="quoteapp.vo.Staff" fetch="join" lazy="false">
<column name="FK_STAFF_ID" not-null="true" />
</many-to-one>
<many-to-one name="jobTypes" class="quoteapp.vo.JobTypes" fetch="join" lazy="false">
<column name="FK_JOB_TYPE_ID" not-null="true" />
</many-to-one>
<many-to-one name="category" class="quoteapp.vo.Categories" fetch="join" lazy="false">
<column name="FK_CATEGORY_ID" not-null="true" />
</many-to-one>
<many-to-one name="reason" class="quoteapp.vo.Reasons" fetch="join" lazy="false">
<column name="FK_REASON_ID" />
</many-to-one>
<property name="quoteDesc" type="java.lang.String">
<column name="QUOTE_DESC" />
</property>
<property name="quoteRefId" type="java.lang.String">
<column name="QUOTE_REF_ID" length="100" />
</property>
<property name="status" type="java.lang.String">
<column name="STATUS" length="100" not-null="true" />
</property>
<property name="createdBy" type="java.lang.Integer">
<column name="CREATED_BY" />
</property>
<property name="createdDate" type="java.util.Date">
<column name="CREATED_DATE" length="19" />
</property>
<property name="updatedBy" type="java.lang.Integer">
<column name="UPDATED_BY" />
</property>
<property name="updatedDate" type="java.util.Date">
<column name="UPDATED_DATE" length="19" />
</property>
<property name="comments" type="java.lang.String">
<column name="COMMENTS" length="255" />
</property>
<property name="assignedBy" type="java.lang.Integer">
<column name="ASSIGNED_BY" />
</property>
<set name="quotesClients" inverse="false" lazy="false" cascade="all" fetch="subselect">
<key>
<column name="FK_QUOTE_ID" not-null="true" />
</key>
<one-to-many class="quoteapp.vo.QuotesClient" />
</set>
</class>
</hibernate-mapping>
QuotesClient.hbm.xml
<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping default-access="field">
<class name="quoteapp.vo.QuotesClient" table="quotes_client" dynamic-update="true">
<id name="id" type="java.lang.Integer">
<column name="QUOTE_CLIENT_ID" />
<generator class="native" />
</id>
<many-to-one name="clients" class="quoteapp.vo.Clients" fetch="join" lazy="false">
<column name="FK_CLIENT_ID" not-null="true" />
</many-to-one>
<many-to-one name="segment" class="quoteapp.vo.Segments" fetch="join" lazy="false">
<column name="FK_SEGMENT_ID" not-null="true" />
</many-to-one>
<many-to-one name="contacts" class="quoteapp.vo.Contacts" fetch="join" lazy="false">
<column name="FK_CONTACT_ID" />
</many-to-one>
<many-to-one name="quotes" class="quoteapp.vo.Quotes" fetch="join" lazy="false">
<column name="FK_QUOTE_ID" not-null="true" />
</many-to-one>
<many-to-one name="reason" class="quoteapp.vo.Reasons" fetch="join" lazy="false">
<column name="FK_Reason_ID"/>
</many-to-one>
<!-- <many-to-one name="supplierProbability" class="quoteapp.vo.Probability" fetch="select" lazy="false">
<column name="FK_SUPPLIER_PROBABILITY" not-null="true" />
</many-to-one>
<many-to-one name="projectProbability" class="quoteapp.vo.Probability" fetch="select" lazy="false">
<column name="PROBABILITY_ID" not-null="true" />
</many-to-one>-->
<property name="closeComments" type="java.lang.String">
<column name="CLOSE_COMMENTS" />
</property>
<property name="supplierProbability" type="java.lang.Integer">
<column name="FK_SUPPLIER_PROBABILITY" />
</property>
<property name="followupDate" type="java.util.Date">
<column name="FOLLOW_UP_DATE" />
</property>
<property name="projectProbability" type="java.lang.Integer">
<column name="FK_PROJECT_PROBABILITY" />
</property>
<property name="recordProbability" type="java.lang.Integer">
<column name="RECORD_PROBABILITY" />
</property>
<property name="probability" type="java.lang.Integer">
<column name="PROBABILITY" />
</property>
<property name="quoteValue" type="java.lang.Double">
<column name="QUOTE_VALUE" precision="22" scale="0" />
</property>
<property name="comments" type="java.lang.String">
<column name="COMMENTS" length="100" />
</property>
<property name="dueDate" type="java.util.Date">
<column name="DUE_DATE" length="10" />
</property>
<property name="revisionId" type="java.lang.Integer">
<column name="REVISION_ID" />
</property>
<property name="clientStatus" type="java.lang.String">
<column name="CLIENT_STATUS" length="15" not-null="true" />
</property>
<property name="createdBy" type="java.lang.Integer">
<column name="CREATED_BY" />
</property>
<property name="createdDate" type="java.util.Date">
<column name="CREATED_DATE" length="19" />
</property>
<property name="updatedBy" type="java.lang.Integer">
<column name="UPDATED_BY" />
</property>
<property name="updatedDate" type="java.util.Date">
<column name="UPDATED_DATE" length="19" />
</property>
<property name="forecast" type="java.lang.String">
<column name="FORECAST" length="1" not-null="true" />
</property>
<property name="optionStatus" type="java.lang.String">
<column name="OPTION_STATUS" length="100" />
</property>
<property name="sellingPrice" type="java.lang.Double">
<column name="SELLING_PRICE" precision="22" scale="0" />
</property>
<set name="clientOptionses" inverse="true" lazy="false" cascade="all" fetch="subselect">
<key>
<column name="FK_QUOTE_CLIENT_ID" />
</key>
<one-to-many class="quoteapp.vo.ClientOptions" />
</set>
</class>
<sql-query name="UpdateQuoteStatus" callable="true" >
<return-scalar column="ROW_COUNT" type="java.lang.Integer"/>
{call QuoteStatus_proc(?,?)}
</sql-query>
<sql-query name="UpdateQuoteClientStatus" callable="true" >
<return-scalar column="ROW_COUNT" type="java.lang.Integer"/>
{call QuoteClientStatus_proc(?,?)}
</sql-query>
</hibernate-mapping>
But when i tried to fetch audited data i get below exception:
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2231)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
at org.hibernate.loader.Loader.list(Loader.java:2120)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at org.hibernate.envers.entities.mapper.relation.lazy.initializor.AbstractCollectionInitializor.initialize(AbstractCollectionInitializor.java:62)
at org.hibernate.envers.entities.mapper.relation.lazy.proxy.CollectionProxy.checkInit(CollectionProxy.java:50)
at org.hibernate.envers.entities.mapper.relation.lazy.proxy.CollectionProxy.iterator(CollectionProxy.java:70)
at quoteapp.envers.AuditReaderImpl.getQuoteRevisions(AuditReaderImpl.java:33)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:131)
at org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
at $Proxy34.getQuoteRevisions(Unknown Source)
at quoteapp.service.LoginManagerImpl.getQuoteRevisions(LoginManagerImpl.java:1136)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at flex.messaging.services.remoting.adapters.JavaAdapter.invoke(JavaAdapter.java:421)
at flex.messaging.services.RemotingService.serviceMessage(RemotingService.java:183)
at flex.messaging.MessageBroker.routeMessageToService(MessageBroker.java:1503)
at flex.messaging.endpoints.AbstractEndpoint.serviceMessage(AbstractEndpoint.java:884)
at flex.messaging.endpoints.amf.MessageBrokerFilter.invoke(MessageBrokerFilter.java:121)
at flex.messaging.endpoints.amf.LegacyFilter.invoke(LegacyFilter.java:158)
at flex.messaging.endpoints.amf.SessionFilter.invoke(SessionFilter.java:44)
at flex.messaging.endpoints.amf.BatchProcessFilter.invoke(BatchProcessFilter.java:67)
at flex.messaging.endpoints.amf.SerializationFilter.invoke(SerializationFilter.java:146)
at flex.messaging.endpoints.BaseHTTPEndpoint.service(BaseHTTPEndpoint.java:278)
at flex.messaging.MessageBrokerServlet.service(MessageBrokerServlet.java:322)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:263)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:584)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: Table 'quoteappdb1.quotes_quotesclient_aud' doesn't exist
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3004)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1128)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1222)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2228)
... 52 more
Could somebaody help me.