title: How to write Hibernate mapping file for a stored procedure?
body: <p>I have a MySQL stored procedure which accepts two input dates and returns <code>EmpCode</code>, <code>Name</code>, <code>Department</code>, <code>Employee_Absent_Date</code> and <code>Total_No_OF_Days</code></p>
  
  <p>Stored procedure (employee absent report for two given dates range): </p>
  
  <pre><code>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 &lt; 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 ;
  </code></pre>
  
  <p>Here is my Hibernate mapping file:</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
  &lt;!DOCTYPE hibernate-mapping PUBLIC 
      "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
      "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"&gt;
  &lt;hibernate-mapping&gt;
      &lt;class name="edu.model.Master" table="test_prefixmaster"&gt;
          &lt;id name="empcode" column="EMPCODE" length="10" &gt;&lt;/id&gt;
          &lt;property name="dept"&gt;
              &lt;column name="DEPARTMENT" length="30" /&gt;
          &lt;/property&gt;
          &lt;property name="empname"&gt;
              &lt;column name="NAME" length="30" /&gt;
          &lt;/property&gt;
      &lt;/class&gt;
      &lt;sql-query name="AbsentReportproc" callable="true"&gt;
      &lt;return alias="master" class="edu.model.Master"&gt;
          &lt;return-property name="empcode" column="EMPCODE" /&gt;
          &lt;return-property name="dept" column="DEPARTMENT" /&gt;
         &lt;return-property name="empname" column="NAME" /&gt; 
  
         &lt;/return&gt;
          &lt;![CDATA[CALL AbsentReportproc(:_fromdate,:_todate)]]&gt;
    &lt;/sql-query&gt;
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>Here is my test class (Main):</p>
  
  <pre><code>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 &lt; 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());         
  
              }
  }
  </code></pre>
  
  <p>When I run my Hibernate code I'm able to print Employee Code, Name and Department successfully</p>
  
  <p>But my actual requirement is how can I map the column and print the values of d.dte from my temporary table named <code>daterange25</code>? Should I create a Pojo for my temporary table?</p>
  
  <p>Thanks</p>