Hi,
I've just a problem with HQL and joins. I've used hibernate to create the classes and mappings from an existing database.
I've two tables where I want to retrieve data from, SubjectBox and SubBox. SubBox contains a foreinkey from SubjectBox. But there are several other tables involved. SubjectBox keeps the data and in SubBox you can find information if an entry in SubjectBox has a parent which is also an entry in SubjectBox:
e.g:
name1 (parent)
- name2 (children of name1)
- name3 (children of name1)
structure of the tables:
Code:
--
-- Tabellenstruktur für Tabelle `SUBJECT_BOX`
--
CREATE TABLE IF NOT EXISTS `SUBJECT_BOX` (
`ID` varchar(40) NOT NULL,
`CREATORUSERID` varchar(40) DEFAULT NULL,
`AUTHORITYID` varchar(40) DEFAULT NULL,
`SUBJECTBOXTYPEID` varchar(40) NOT NULL,
`CREATIONTIME` datetime NOT NULL,
`MODIFICATIONTIME` datetime NOT NULL,
`NAME` varchar(255) NOT NULL,
`ORIGINALSUBJECTBOXID` varchar(40) NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `UK_t42g8skasnmwamlbi4x6ngg8c` (`NAME`),
KEY `FK_qq1x26taqmg18893rsn55m6ej` (`CREATORUSERID`),
KEY `FK_ir380cdhmlb2rbdfh075bp8np` (`AUTHORITYID`),
KEY `FK_d0w8mgy95lkjldnoo1oifyk0d` (`SUBJECTBOXTYPEID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Constraints der exportierten Tabellen
--
--
-- Constraints der Tabelle `SUBJECT_BOX`
--
ALTER TABLE `SUBJECT_BOX`
ADD CONSTRAINT `FK_d0w8mgy95lkjldnoo1oifyk0d` FOREIGN KEY (`SUBJECTBOXTYPEID`) REFERENCES `SUBJECT_BOX_TYPE` (`ID`),
ADD CONSTRAINT `FK_ir380cdhmlb2rbdfh075bp8np` FOREIGN KEY (`AUTHORITYID`) REFERENCES `AUTHORITY` (`ID`),
ADD CONSTRAINT `FK_qq1x26taqmg18893rsn55m6ej` FOREIGN KEY (`CREATORUSERID`) REFERENCES `USER` (`ID`);
--
-- Tabellenstruktur für Tabelle `SUB_BOX`
--
CREATE TABLE IF NOT EXISTS `SUB_BOX` (
`CHILDID` varchar(40) NOT NULL,
`PARENTID` varchar(40) NOT NULL,
PRIMARY KEY (`CHILDID`,`PARENTID`),
KEY `FK_1hxmwffq9gpry0rlnarym9f8m` (`PARENTID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Constraints der exportierten Tabellen
--
--
-- Constraints der Tabelle `SUB_BOX`
--
ALTER TABLE `SUB_BOX`
ADD CONSTRAINT `FK_1hxmwffq9gpry0rlnarym9f8m` FOREIGN KEY (`PARENTID`) REFERENCES `SUBJECT_BOX` (`ID`);
this resulted in 3 classes, SubjectBox, SubBox and SubBoxId
and mapping files for SubjectBox and SubBox
SubBox.hbm.xml looks like:
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">
<!-- Generated 11.06.2014 17:42:29 by Hibernate Tools 4.0.0 -->
<hibernate-mapping>
<class name="xxx.db.hibernate.SubBox" table="SUB_BOX"
catalog="gamedb">
<composite-id name="id"
class="xxx.db.hibernate.SubBoxId">
<key-property name="childid" type="string">
<column name="CHILDID" length="40" />
</key-property>
<key-property name="parentid" type="string">
<column name="PARENTID" length="40" />
</key-property>
</composite-id>
<many-to-one name="subjectBox"
class="xxx.db.hibernate.SubjectBox" update="false"
insert="false" fetch="select">
<column name="PARENTID" length="40" not-null="true" />
</many-to-one>
</class>
</hibernate-mapping>
The sql query I want to use looks like:
Code:
"select SUBJECT_BOX.id, SUB_BOX.CHILDID from SUBJECT_BOX
left join SUB_BOX on SUBJECT_BOX.ID = SUB_BOX.CHILDID
where SUB_BOX.CHILDID is NULL
I've tried to use Criteria as for other simple queries, but I'm not familiar enough with all stuff to so I ended up in trying to use HQL. I'm now a little bit confused because the output of my query looks like:
Code:
QueryImpl(from SubjectBox subject, SubBox box left join box.id on subject.id = box.id.childid)
but the query hibernate created contains a cross join
Code:
Hibernate:
select
subjectbox0_.ID as ID1_14_0_,
subbox1_.CHILDID as CHILDID1_16_1_,
subbox1_.PARENTID as PARENTID2_16_1_,
subjectbox0_.CREATORUSERID as CREATORU2_14_0_,
subjectbox0_.AUTHORITYID as AUTHORIT3_14_0_,
subjectbox0_.SUBJECTBOXTYPEID as SUBJECTB4_14_0_,
subjectbox0_.CREATIONTIME as CREATION5_14_0_,
subjectbox0_.MODIFICATIONTIME as MODIFICA6_14_0_,
subjectbox0_.NAME as NAME7_14_0_,
subjectbox0_.ORIGINALSUBJECTBOXID as ORIGINAL8_14_0_
from
gamedb.SUBJECT_BOX subjectbox0_ cross
join
gamedb.SUB_BOX subbox1_
and the result contains all entries of both tables. But I only want to get the entry "name1".
Could anybody give me a hint what I can do to solve my problem?
(with existing database and generated code please ... no changes possible ... )
best regards
re
Login
FAQ
Search