I have this query to select a department name of a specific user.

The argument is name of the user, it would return the department object


User.JAVA
public class User
{
private Integer id;
private String userName;

//many other properties
private Department fkDept;

public Integer getId()
{
return id;
}

public void setId(Integer theId)
{
id = theId;
}

public String getUserName()
{
return userName;
}

public void setUserName(String theUserName)
{
userName = theUserName;
}

//many other properties's methods

public Department getFkDept() {
return fkDept;
}

public void setFkDept(Department theDepartment) {
fkDept = theDepartment;
}

}

Department.JAVA
public class Department
{
private Integer id;
private String departmentName;
private Set fkUser;


public String toString()
{
return "Department : " + departmentName;
}

public Integer getId() {
return id;
}

public String getDepartmentName() {
return departmentName;
}

public void setId(Integer theId)
{
id = theId;
}

public void setDepartmentName(String theDepartmentName)
{
departmentName = theDepartmentName;
}

public Set getFkUser() {
return fkUser;
}

public void setFkUser(Set theUsers) {
fkUser = theUsers;
}

}

Department.hbm.xml
<?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="Department" table="DEPARTMENT">
<id name="id" column="ID">
<generator class="native" />
</id>

<property name="departmentName" column="DEPARTMENT_NAME" not-null="true" />
</class>
</hibernate-mapping>

User.hbm.xml
<?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="User" table="USERS">
<id name="id" column="ID">
<generator class="native" />
</id>

<property name="userName" column="USERNAME" not-null="true" unique="true" />

<many-to-one name="fkDept" class="Department" column="DEPT_ID" not-null="true" />
</class>
</hibernate-mapping>


My query :

public String nameByUserName(String name) throws HibernateException
{
System.out.println("Retrieving the Department from a specific User");
sess = sessFact.openSession();
String deptName;
Transaction tx = null;
try
{
tx = sess.beginTransaction();
/*Start Query*/
String query =
"select dept from User as user inner join user.fkDept as dept "
+ "where lower(user.userName) = lower(:name) AND (user.fkDept.departmentName = dept.departmentName)"; deptList = sess.find(query, name,Hibernate.STRING);
/*End Query*/

if (deptList.size() == 0)
{
System.out.println("No department named " + name);
return null;
}
else
{
theDepartment = (Department)deptList.get(0);
deptName = theDepartment.getDepartmentName();
System.out.println("Department is successfully retrieved");
System.out.println(theDepartment);
}
tx.commit();

}
catch(HibernateException he)
{
if(tx!=null) tx.rollback();
throw he;
}
finally
{
closeSession();
}
return deptName;
}

I got a feeling what it actually does is comparing the DEPT_ID in USER of that USERNAME to itself!

"select dept from User as user inner join user.fkDept as dept "
+ "where lower(user.userName) = lower(:name) AND (user.fkDept.departmentName = dept.departmentName)";
user.fkDept is the same as dept?? Am I right? or since it's HIBERNATE we're talking about, should I just remove the part after AND coz it's useless??

coz in SQL it would be something like
select * from DEPARTMENT as dept, USER as user WHERE (user.USERNAME="John") AND (user.DEPT_ID=dept.ID);

yes.

All you need is:

select dept from User as user inner join user.fkDept as dept where lower(user.userName) = lower(:name)

Or, if your data is such that a user has to have a dept, simply do:

select user.fkDept from User as user where lower(user.userName) = lower(:name)

The second form is an implicit outer-join...

many thanks Steve.. you've cleared my doubt. :)

Which one is faster though? I bet the second one, eh?

try both and look at sql generated.... you'll have the answer