how to call stored procedure with multiple tables

问题:

SELECT emp.employeeId, emp.employeeFirstName, comp.companyname, dept.departmentname 
FROM Employeetable emp, Companytable comp, departmenttable dept 
WHERE emp.employeeCompanyId=comp.companyId AND emp.EmployeeDepartmentID=dept.DepartmentID;

The employeetable hbm file:

<sql-query name="callrealtimeprocedure">
    <return alias="employeetable" class="com.AdiSys.eRMS.entity.Employeetable"/>
    <return-join alias="companytable" property="com.AdiSys.eRMS.entity.Employeetable"/>
    <return-join alias="companytable" property="com.AdiSys.eRMS.entity.Employeetable"/>
</sql-query>

The employeetable pojo:

@NamedNativeQueries({
@NamedNativeQuery(
name = "callrealtimeprocedure",
query = "CALL realtime(:employeeId,:employeeFirstName)",
resultClass = Employeetable.class
)
})
@Entity

Similarly department hbm and pojo.

My java class after creating session:

Query EmplyeeQuery = session.createSQLQuery("CALL realtime(:employeeId,:employeeFirstName,:companyname,:departmentname)")
        .addEntity(Employeetable.class)
        .addEntity(Companytable.class)
        .addEntity(Departmenttable.class)
        .setParameter("employeeId", "")
        .setParameter("employeeFirstName","")
        .setParameter("companyname", "")
        .setParameter("departmentname", "");

I'm not able to find out how to link up stored procedure sql query in hbm and pojo and java class

回答1:

Well, for a start, I wouldn't have bothered with the stored procedure. The whole point of using Hibernate is that you don't need to write any SQL. If you're going to write SQL, then there's not much to be gained by using Hibernate.

A really good source of Hibernate documentation is http://www.hibernate.org/docs - if you go to the "Core Reference Manual" for whichever version of Hibernate you have, you'll find some really good examples on how to set up your POJOs.

Also, I don't know if you're planning to use Hibernate annotations. I like these - they're a lot less messy than using separate XML files. Have a look at the "Hibernate Annotations Reference Guide" (from the same link, under v3.5) for guidance on using them.