Hi,
The answer is yes.
I don't claim to be an expert, but I don't understand the reason for the sub-select in your SQL. You are selecting everything from the table in your sub-select, which should be no different than just joining on the table:
SQL:
Code:
SELECT item.itemId, item.description, item.defaultDescription, t.translatedDescription
FROM tblItem item
LEFT OUTER JOIN tblItemTranslation AS t ON item.itemId = t.item_fk
WHERE locale = 'de_DE'
This would mean the HQL would be something like:
HQL:
Code:
FROM Item item
LEFT JOIN itemtranslations tr
WHERE tr.locale = de_DE
The other thing that occurs to me is that if you have mapped both of the tables in, say, a bi-directional one-to-many/many-to-one then a query with an outer join appears unnecessary to me.
Take the example of a department table and an employee table. The SQL would look like:
Code:
create table department (
department_id identity
,department_desc varchar(30));
create table employee (
employee_id identity
,name varchar(30)
,department_id integer
,foreign key (department_id) references department (department_id));
Here the employee has a foreign key which refereneces the department. These can be mapped as follows:
Code:
<class name="Employee" table="EMPLOYEE">
<id name="employeeId" column="EMPLOYEE_ID">
<generator class="native" />
</id>
<many-to-one name="department" column="DEPARTMENT_ID" not-null="false" lazy="false" />
<property name="name" column="NAME" />
</class>
<class name="Department" table="DEPARTMENT">
<id name="departmentId" column="DEPARTMENT_ID">
<generator class="native" />
</id>
<property name="departmentDesc" column="DEPARTMENT_DESC" />
<set name="employees" inverse="true" lazy="false">
<key column="DEPARTMENT_ID" />
<one-to-many class="Employee" />
</set>
</class>
Now if you wrote a function to return all departments like this:
(using Spring's HibernateTemplate)
Code:
public List getAllDepartments() {
return getHibernateTemplate().find("from egjoin.Department dept");
}
Then you could iterate through the departments like so:
Code:
lgr.info("Let's list Departments");
List departments = departmentDao.getAllDepartments();
it = departments.iterator();
while (it.hasNext()) {
Department department = (Department) it.next();
lgr.info("Department is: " + department);
Double sum = 0.0;
Iterator empIt = department.getEmployees().iterator();
while (empIt.hasNext()) {
Employee employee = (Employee) empIt.next();
lgr.info("\tAssociated Employee: " + employee);
}
}
And this code will list all departments whether or not there are any employees associated with them...
Code:
INFO Main - Department is: Department[0,San Fransisco]
INFO Main - Associated Employee: Employee[0,Smith]
INFO Main - Department is: Department[1,New York]
INFO Main - Associated Employee: Employee[1,Smith]
INFO Main - Department is: Department[2,Chicago]
The Chicago department has no employees, but is listed along with the others... the same effect as an SQL left outer join would have had.
Login
FAQ
Search
