Hi there!
I'm trying to execute this (i use the arrays because the query i'm actually trying to execute is a lot longer and i cut out most of it, trying to get it to work):
List list = hibernateSession.createSQLQuery("select {currentTechnician.*} from tbTechnician as {currentTechnician}"
, new String[] {"currentTechnician"}
, new Class[] {Technician.class}).list();
and get the following execption:
net.sf.hibernate.JDBCException: SQLException occurred: The column prefix 'currentTechnician_1_' does not match with a table name or alias name used in the query.
com.jnetdirect.jsql.v: The column prefix 'currentTechnician_1_' does not match with a table name or alias name used in the query.
at com.jnetdirect.jsql.v.a(Unknown Source)
at com.jnetdirect.jsql.av.a(Unknown Source)
at com.jnetdirect.jsql.af.e(Unknown Source)
at com.jnetdirect.jsql.af.new(Unknown Source)
at com.jnetdirect.jsql.af.do(Unknown Source)
at com.jnetdirect.jsql.ap.executeQuery(Unknown Source)
at com.mchange.v2.c3p0.impl.C3P0PreparedStatement.executeQuery(C3P0PreparedStatement.java:188)
at com.mchange.v2.c3p0.impl.C3P0PooledConnection$2.executeQuery(C3P0PooledConnection.java:473)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:81)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:629)
at net.sf.hibernate.loader.Loader.doResultSet(Loader.java:171)
at net.sf.hibernate.loader.Loader.doFind(Loader.java:113)
at net.sf.hibernate.loader.Loader.find(Loader.java:736)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:69)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3612)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:47)
at de.sag.qsee.taskscheduler.business.AppointmentManager.getFreeTechnicianAppointments(AppointmentManager.java:455)
i used the sql server profiler and found out the hibernate creates the following sql statement, which is obviously wrong:
select currentTechnician.technicianActorID as technicianActorID0_
, currentTechnician.technicianNo as technici2_3_0_
, currentTechnician.shortMark as shortMark3_0_
, currentTechnician.mobilePhone as mobilePh4_3_0_
, currentTechnician.birthday as birthday3_0_
, currentTechnician.collectionAccount as collecti6_3_0_
, currentTechnician.carNumber as carNumber3_0_
, currentTechnician_1_.status as status1_0_
, currentTechnician_1_.lastName as lastName1_0_
, currentTechnician_1_.firstName as firstName1_0_
, currentTechnician_1_.userName as userName1_0_
, currentTechnician_1_.loginName as loginName1_0_
, currentTechnician_1_.password as password1_0_
, currentTechnician_1_.numberFailedLogins as numberFa8_1_0_
, currentTechnician_1_.languageID as languageID1_0_
, currentTechnician_1_.countryID as countryID1_0_
, currentTechnician_1_.tbRoleID as tbRoleID1_0_
from tbTechnician as currentTechnician
i guess it's because Technician inherits from User which is stored in a different table in the db. The columns referenced with currentTechnician_1_ are the ones from the user.
the mapping is as follows:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="de.sag.qsee.masterdata.data.Actor" table="tbActor">
<!-- PK -->
<id name="actorID" column="actorID" type="int" unsaved-value="0">
<generator class="identity"/>
</id>
<!-- subclasses -->
<!-- User class -->
<joined-subclass name="de.sag.qsee.masterdata.userdata.data.User" table="tbUser">
<key column="userActorID"/>
<property name="status" column="status" type="int"/>
<property name="lastName" column="lastName" type="string"/>
<property name="firstName" column="firstName" type="string"/>
<property name="userName" column="userName" type="string"/>
<property name="loginName" column="loginName" type="string"/>
<property name="password" column="password" type="string"/>
<property name="numberFailedLogins" column="numberFailedLogins" type="int"/>
<many-to-one name="language" column="languageID" not-null="true"/>
<many-to-one name="country" column="countryID" not-null="true"/>
<many-to-one name="currentRole" column="tbRoleID" not-null="false"/>
<bag name="roles" table="tbUser2Role" lazy="true">
<key column="userActorID"/>
<many-to-many class="de.sag.qsee.masterdata.userdata.data.Role" column="tbRoleID"/>
</bag>
<!-- subclasses -->
<!-- Technician class -->
<joined-subclass name="de.sag.qsee.masterdata.userdata.data.Technician" table="tbTechnician">
<key column="technicianActorID"/>
<!-- local attributes -->
<property name="technicianNo" column="technicianNo" type="string"/>
<property name="shortMark" column="shortMark" type="string"/>
<property name="mobilePhone" column="mobilePhone" type="string"/>
<property name="birthday" column="birthday" type="date"/>
<property name="collectionAccount" column="collectionAccount" type="string"/>
<property name="carNumber" column="carNumber" type="string"/>
<!-- one to many -->
<bag name="repairTypes" table="tbTechnicianQualification" lazy="true">
<key column="technicianActorID"/>
<many-to-many class="de.sag.qsee.masterdata.oemdata.data.RepairType" column="tbRepairTypeID"/>
</bag>
<bag name="addresses" table="tbAddress" inverse="true" lazy="true" cascade="all">
<key column="actorID"/>
<one-to-many class="de.sag.qsee.masterdata.data.Address"/>
</bag>
<bag name="workingDays" table="tbTechnicianWorkingDays" inverse="true" lazy="true">
<key column="technicianActorID"/>
<one-to-many class="de.sag.qsee.masterdata.userdata.data.TechnicianWorkingDay"/>
</bag>
<bag name="appointments" table="tbTechnicianAppointmentSlots" inverse="true" lazy="true">
<key column="technicianActorID"/>
<one-to-many class="de.sag.qsee.taskscheduler.data.TechnicianAppointmentSlot"/>
</bag>
</joined-subclass>
</joined-subclass>
</class>
</hibernate-mapping>
For everything else the mapping works fine, we're using it excessivly. Just the createSQLQuery won't work.
kind regards,
Torsten Liebscher
Login
FAQ
Search
