posted 13 years ago
hi i am new to hibernate. and i use native sql query in my project. and make use of named query.
below is the code i used in dao
Query q=session.getNamedQuery("ipsQuery");
q.setString("shipdate", a_CBillingInvoiceDetailBO.getBihBO().getShipdate());
q.setString("storenum", a_CBillingInvoiceDetailBO.getBihBO().getStorenum().toString());
q.setString("suplyrnum", a_CBillingInvoiceDetailBO.getSuplyrnum().toString());
and below is the mapping done in hbm file
<resultset name="billingInvoiceDetailBO">
<return alias="ipsBO" class="com.shaws.it.ips.BO.BillingInvoiceDetailBO">
<return-property name="suplyrnum" column="suplyr"></return-property>
<return-property name="itemcode" column="code"></return-property>
<return-property name="billqty" column="qty"></return-property>
<return-property name="tagdescr" column="descr"></return-property>
<return-property name="ship" column="ship"></return-property>
<return-property name="billpack" column="pack"></return-property>
<return-property name="untcst" column="cstunt"></return-property>
<return-property name="cost" column="cstext"></return-property>
<return-property name="untrtl" column="retunt"></return-property>
<return-property name="retail" column="retext"></return-property>
<return-property name="gpp" column="GPP"></return-property>
</return>
<return-scalar column="suplyr" type="string"/>
<return-scalar column="code" type="string"/>
<return-scalar column="qty" type="string"/>
<return-scalar column="descr" type="string"/>
<return-scalar column="ship" type="string"/>
<return-scalar column="pack" type="string"/>
<return-scalar column="cstunt" type="string"/>
<return-scalar column="cstext" type="string"/>
<return-scalar column="retunt" type="string"/>
<return-scalar column="retext" type="string"/>
<return-scalar column="GPP" type="string"/>
</resultset>
<sql-query name="ipsQuery" resultset-ref="billingInvoiceDetailBO" >
<![CDATA[ SELECT
bid.suplyr_num suplyr,
ih.item_code code,
bid.bill_qty qty,
SUBSTR(ih.tag_descr,1,15) descr,
TO_CHAR(bih.ship_date,'MM/DD/YY') ship,
bid.bill_pack pack,
TO_CHAR((ROUND(SUM(bid.bill_cost*bid.bill_qty),2)/SUM(bid.bill_qty))/
DECODE(bid.bill_pack,0,1,bid.bill_pack),'9990.000') cstunt,
ROUND(bid.bill_cost * bid.bill_qty, 2) cstext,
ROUND(bid.retail, 2) retunt,
TO_CHAR((NVL(SUM(bid.retail),1)) * (bid.bill_qty) *
DECODE(bid.bill_pack,0,1,bid.bill_pack),'9990.000') retext,
ROUND(100 *(1 - (DECODE(SIGN((SUM( DECODE(bid.retail,0,1,bid.retail) * bid.bill_pack ) / 1) -1), -1, 1,
TO_CHAR((ROUND(SUM(bid.bill_cost*bid.bill_qty),2) / SUM(bid.bill_qty)) /
DECODE(bid.bill_pack,0,1,bid.bill_pack),'9990.000')) /
DECODE(SIGN((SUM( DECODE(bid.retail,0,1,bid.retail) * bid.bill_pack ) / 1) -1), -1, 1,
SUM( DECODE(bid.retail,0,1,bid.retail) * bid.bill_pack )))), 3) GPP
FROM billing_invoice_header bih,
billing_invoice_detail bid,
item_history ih
WHERE bih.ship_date = TO_DATE(:shipdate,'MM/DD/RR')
AND bih.store_num = TO_NUMBER(:storenum)
AND bih.status_code IN (600, 601)
AND bih.billing_invoice_key = bid.billing_invoice_key
AND bid.status_code = 651
AND bid.suplyr_num = DECODE(TO_NUMBER(:suplyrnum),0,bid.suplyr_num,TO_NUMBER(:suplyrnum))
AND bid.item_key = ih.item_key
AND TO_DATE(:shipdate, 'MM/DD/RR') BETWEEN ih.eff_date_from
AND NVL(ih.eff_date_to, SysDate)
GROUP BY bid.suplyr_num,
ih.item_code,
bid.bill_qty,
ih.tag_descr,
bih.ship_date,
bid.bill_pack,
bid.bill_cost,
bid.retail
ORDER BY suplyr, ih.item_code ]]>
</sql-query>
i get the following error when i run my project
org.xml.sax.SAXParseException: Element type "resultset" must be declared.
at org.apache.xerces.util.ErrorHandlerWrapper.createSAXParseException(Unknown Source)
at org.apache.xerces.util.ErrorHandlerWrapper.error(Unknown Source)
at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)
at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)
at org.apache.xerces.impl.dtd.XMLDTDValidator.handleStartElement(Unknown Source)
at org.apache.xerces.impl.dtd.XMLDTDValidator.startElement(Unknown Source)
at org.apache.xerces.impl.XMLNSDocumentScannerImpl.scanStartElement(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source)
at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source)
at org.apache.xerces.parsers.XMLParser.parse(Unknown Source)
at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source)
at org.dom4j.io.SAXReader.read(SAXReader.java:465)
at org.hibernate.cfg.Configuration.addInputStream(Configuration.java:398)
at org.springframework.orm.hibernate3.LocalSessionFactoryBean.afterPropertiesSet(LocalSessionFactoryBean.java:683)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1099)
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:400)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:234)
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:144)
at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:277)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:321)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:87)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:72)
at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:63)
at com.shaws.it.ips.assembler.DsdBillingAssembler.<clinit>(DsdBillingAssembler.java:35)
at com.shaws.it.ips.delegate.DsdBillingDelegate.retriveDsdBilling(DsdBillingDelegate.java:38)
at com.shaws.it.ips.action.DsdBillingAction.execute(DsdBillingAction.java:80)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1194)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
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:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
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:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:619)
can any one help please!!
