How to write Hibernate mapping file for a stored procedure?

Question

I have a MySQL stored procedure which accepts two input dates and returns EmpCode, Name, Department, Employee_Absent_Date and Total_No_OF_Days

Stored procedure (employee absent report for two given dates range):

DELIMITER $$

DROP PROCEDURE IF EXISTS `AbsentReportproc`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `AbsentReportproc`(IN _fromdate DATETIME, IN _todate DATETIME)
BEGIN 
    CREATE TEMPORARY TABLE daterange25 (dte DATE); 

    SET @counter := -1;
    WHILE (@counter < DATEDIFF(DATE(_todate), DATE(_fromdate))) DO 
        INSERT INTO daterange25 VALUES (DATE_ADD(_fromdate, INTERVAL @counter:=@counter + 1 DAY));
    END WHILE;

SELECT tp.EMPCODE,tp.NAME,tp.DEPARTMENT, Group_Concat(d.dte order by d.dte SEPARATOR '\n')AbsentDate, COUNT(tp.EMPCODE) Totalnoofabsentdates
FROM test_prefixmaster tp
JOIN daterange25 d
LEFT JOIN test_prefixtransactions tpt ON (tp.EMPCODE = tpt.empcode) AND DATE(S_DateTime) = d.dte
WHERE tpt.empcode IS NULL
GROUP BY tp.EMPCODE;   

    DROP TABLE daterange25;
END$$

DELIMITER ;

Here is my Hibernate mapping file:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC 
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="edu.model.Master" table="test_prefixmaster">
        <id name="empcode" column="EMPCODE" length="10" ></id>
        <property name="dept">
            <column name="DEPARTMENT" length="30" />
        </property>
        <property name="empname">
            <column name="NAME" length="30" />
        </property>
    </class>
    <sql-query name="AbsentReportproc" callable="true">
    <return alias="master" class="edu.model.Master">
        <return-property name="empcode" column="EMPCODE" />
        <return-property name="dept" column="DEPARTMENT" />
       <return-property name="empname" column="NAME" /> 

       </return>
        <![CDATA[CALL AbsentReportproc(:_fromdate,:_todate)]]>
  </sql-query>
</hibernate-mapping>

Here is my test class (Main):

public static void main(){
Query query = session.getNamedQuery("AbsentReportproc");
            query.setParameter("_fromdate", "2012-11-22");
            query.setParameter("_todate", "2012-11-23");
            List absentiesList=new ArrayList();
             absentiesList = query.list();

            for (int i = 0; i < absentiesList.size(); i++) {

                Master master = (Master) absentiesList.get(i);

        System.out.println("Employee Code:::" + master.getEmpcode());
        System.out.println("Employee Name:::"master.getEmpname());
   System.out.println("Employee Department:::" + master.getDept());         

            }
}

When I run my Hibernate code I'm able to print Employee Code, Name and Department successfully

But my actual requirement is how can I map the column and print the values of d.dte from my temporary table named daterange25? Should I create a Pojo for my temporary table?

Thanks

Answer 1

I think there is no way to map result of SP directly to entity. You should write and instantiate your own pojo, or yes you can map entity to temporary table where your SP store your result.

Answer 2

You can create a POJO based on the structure of the SP result, and map its columns in the hibernate mapping XML without specifying a table name. The return-property tags are not needed.