when i try to use native sql i've passed the aliases and their classes but three columns aliases are from a composite id and i get the error below saying the the alias is not specified. so what i then did is include the composite id class in the list of aliases but when
i tried that i got a mapping error saying their was no persister for the composite id class, it wouldn't have one because it's mapped in LicenceProduct xml below. does this mean that you cannot use composite id attr in native sql or have i just specified the alias incorrectly??
Thanks
Hibernate version:
2.1.6
Mapping documents:
<class
name="uk.org.ofcom.assignment.vo.LicenceProduct"
table="LICENCE_PRODUCTS"
>
<composite-id name="comp_id" class="uk.org.ofcom.assignment.vo.ProductPK">
<key-property
name="secCode"
column="SEC_CODE"
type="byte"
length="2"
/>
<key-property
name="lclCode"
column="LCL_CODE"
type="byte"
length="2"
/>
<key-property
name="no"
column="NO"
type="byte"
length="2"
/>
</composite-id>
<property
name="productType"
type="java.lang.String"
column="PRODUCT_TYPE"
not-null="true"
length="3"
/>
<property
name="shortName"
type="java.lang.String"
column="SHORT_NAME"
not-null="true"
unique="true"
length="12"
/>
<property
name="description"
type="java.lang.String"
column="DESCRIPTION"
not-null="true"
length="80"
/>
<property
name="endDate"
type="java.sql.Timestamp"
column="END_DATE"
length="7"
/>
<property
name="startDate"
type="java.sql.Timestamp"
column="START_DATE"
length="7"
/>
<property
name="datedl"
type="java.sql.Timestamp"
column="DATEDL"
length="7"
/>
<property
name="tradable"
type="java.lang.String"
column="TRADABLE"
/>
<!-- associations -->
</class>
Code between sessionFactory.openSession() and session.close():
String [] aliases = new String[]{"l", "lp", "cu", "su", "c", "sf"};
Class [] classes = new Class[]{Licence.class, LicenceProduct.class, Customer.class, Assignment.class, Channel.class, Location.class};
String sql = " select l.LIC_NO {l.licNo}, cu.name {cu.name}, lp.DESCRIPTION {lp.description}, l.SEC_CODE {lp.comp_id.secCode}, l.LCL_CODE {lp.comp_id.lclCode}, l.LPR_NO {lp.comp_id.no}, sf.MAP_SQUARE {sf.mapSquare}, sf.UK_EASTING {sf.ukEasting}, sf.UK_NORTHING {sf.ukNorthing}, decode ( c.use_type2, 'BASE', c.d_centre_freq, 'B', c.d_centre_freq, c.centre_freq ) {c.centreFreq}, decode ( c.use_type, 'MOB', c.centre_freq, 'M', c.centre_freq, c.d_centre_freq ) {c.dCentreFreq}, decode ( c.use_type2, 'BASE', c.d_channel_width, 'B', c.d_channel_width, c.channel_width ) {c.channelWidth}, decode ( c.use_type, 'MOB', c.channel_width, 'M', c.channel_width, c.d_channel_width ) {c.dChannelWidth} from licences l, licence_products lp, customers cu, spectrum_uses su, channels c, station_formations sf where l.term_date is null and l.CUS_REF = cu.REF and l.SEC_CODE || l.LCL_CODE || l.lpr_no in (501010, 301010) and lp.END_DATE is null and lp.SEC_CODE = l.SEC_CODE and lp.LCL_CODE = l.LCL_CODE and lp.NO = l.LPR_NO and su.END_DATE is null and sf.END_DATE is null and su.LIC_NO = l.LIC_NO and su.CHA_REFERENCE = c.REFERENCE and su.CHP_NAME = c.CHP_NAME and su.SPB_BAND_CODE = c.SPB_BAND_CODE and l.lic_no = sf.lic_no and sf.LIC_NO = su.LIC_NO and sf.station_no = su.sfm_station_no and l.LIC_TRADABLE = 'Y' AND l.SEC_CODE=:secCode "
Query lqu = session.createSQLQuery(sql, aliases, classes).setCacheable(true);
Full stack trace of any exception that occurs:
2004-10-13 17:38:42,618 [HttpRequestHandler-6044039] ERROR uk.org.ofcom.dao.HibernateDataLoader - Oops
net.sf.hibernate.QueryException: Alias [lp.comp_id] does not correspond to any of the supplied return aliases = {[l,lp,cu,su,c,sf]} [select l.LIC_NO {l.licNo}, cu.name {cu.name}, lp.DESCRIPTION {lp.description}, l.SEC_CODE {lp.comp_id.secCode}, l.LCL_CODE {lp.comp_id.lclCode}, l.LPR_NO {lp.comp_id.no}, sf.MAP_SQUARE {sf.mapSquare}, sf.UK_EASTING {sf.ukEasting}, sf.UK_NORTHING {sf.ukNorthing}, decode ( c.use_type2, 'BASE', c.d_centre_freq, 'B', c.d_centre_freq, c.centre_freq ) {c.centreFreq}, decode ( c.use_type, 'MOB', c.centre_freq, 'M', c.centre_freq, c.d_centre_freq ) {c.dCentreFreq}, decode ( c.use_type2, 'BASE', c.d_channel_width, 'B', c.d_channel_width, c.channel_width ) {c.channelWidth}, decode ( c.use_type, 'MOB', c.channel_width, 'M', c.channel_width, c.d_channel_width ) {c.dChannelWidth} from licences l, licence_products lp, customers cu, spectrum_uses su, channels c, station_formations sf where l.term_date is null and l.CUS_REF = cu.REF and l.SEC_CODE || l.LCL_CODE || l.lpr_no in (501010, 301010) and lp.END_DATE is null and lp.SEC_CODE = l.SEC_CODE and lp.LCL_CODE = l.LCL_CODE and lp.NO = l.LPR_NO and su.END_DATE is null and sf.END_DATE is null and su.LIC_NO = l.LIC_NO and su.CHA_REFERENCE = c.REFERENCE and su.CHP_NAME = c.CHP_NAME and su.SPB_BAND_CODE = c.SPB_BAND_CODE and l.lic_no = sf.lic_no and sf.LIC_NO = su.LIC_NO and sf.station_no = su.sfm_station_no and l.LIC_TRADABLE = 'Y' AND l.SEC_CODE=:secCode ]
at net.sf.hibernate.loader.SQLLoader.substituteBrackets(SQLLoader.java:131)
at net.sf.hibernate.loader.SQLLoader.renderStatement(SQLLoader.java:85)
at net.sf.hibernate.loader.SQLLoader.<init>(SQLLoader.java:71)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3758)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at uk.org.ofcom.dao.HibernateDataLoader.getCollectionFromSql(HibernateDataLoader.java:183)
at uk.org.ofcom.dao.HibernateDataLoader.getCollectionFromNativeSql(HibernateDataLoader.java:139)
at uk.org.ofcom.rules.AbstractRulesReader.getCollectionFromNativeSql(Unknown Source)
at uk.org.ofcom.rules.LicenceManager.listLicencesByAssignments(Unknown Source)
at uk.org.ofcom.rules.LicenceManager.listLicences(Unknown Source)
at java.lang.reflect.Method.invoke(Native Method)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:402)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:309)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:333)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:71)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:150)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:120)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:481)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:323)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:854)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:339)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:721)
at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:803)
at java.lang.Thread.run(Thread.java:479)
Name and version of the database you are using:
oracle 9i
The generated SQL (show_sql=true):
Debug level Hibernate log excerpt:
2004-10-13 17:38:42,606 [HttpRequestHandler-6044039] DEBUG net.sf.hibernate.impl.SessionImpl - SQL query: select l.LIC_NO {l.licNo}, cu.name {cu.name}, lp.DESCRIPTION {lp.description}, l.SEC_CODE {lp.comp_id.secCode}, l.LCL_CODE {lp.comp_id.lclCode}, l.LPR_NO {lp.comp_id.no}, sf.MAP_SQUARE {sf.mapSquare}, sf.UK_EASTING {sf.ukEasting}, sf.UK_NORTHING {sf.ukNorthing}, decode ( c.use_type2, 'BASE', c.d_centre_freq, 'B', c.d_centre_freq, c.centre_freq ) {c.centreFreq}, decode ( c.use_type, 'MOB', c.centre_freq, 'M', c.centre_freq, c.d_centre_freq ) {c.dCentreFreq}, decode ( c.use_type2, 'BASE', c.d_channel_width, 'B', c.d_channel_width, c.channel_width ) {c.channelWidth}, decode ( c.use_type, 'MOB', c.channel_width, 'M', c.channel_width, c.d_channel_width ) {c.dChannelWidth} from licences l, licence_products lp, customers cu, spectrum_uses su, channels c, station_formations sf where l.term_date is null and l.CUS_REF = cu.REF and l.SEC_CODE || l.LCL_CODE || l.lpr_no in (501010, 301010) and lp.END_DATE is null and lp.SEC_CODE = l.SEC_CODE and lp.LCL_CODE = l.LCL_CODE and lp.NO = l.LPR_NO and su.END_DATE is null and sf.END_DATE is null and su.LIC_NO = l.LIC_NO and su.CHA_REFERENCE = c.REFERENCE and su.CHP_NAME = c.CHP_NAME and su.SPB_BAND_CODE = c.SPB_BAND_CODE and l.lic_no = sf.lic_no and sf.LIC_NO = su.LIC_NO and sf.station_no = su.sfm_station_no and l.LIC_TRADABLE = 'Y' AND l.SEC_CODE=:secCode
2004-10-13 17:38:42,638 [HttpRequestHandler-6044039] DEBUG net.sf.hibernate.impl.SessionImpl - closing session
2004-10-13 17:38:42,698 [Finalizer] DEBUG net.sf.hibernate.impl.SessionImpl - running Session.finalize()
Login
FAQ
Search
