如何编写存储过程的Hibernate映射文件？ [英] How to write Hibernate mapping file for a stored procedure?

问题描述

我有一个MySQL存储过程，它接受两个输入日期并返回 EmpCode ， Name ， Department ， Employee_Absent_Date 和 Total_No_OF_Days

存储过程（两个给定日期范围内的员工缺勤报告）：

pre $ DELIMITER $$

DROP PROCEDURE如果存在`AbsentReportproc` $$

CREATE DEFINER =`root` @ localhost`过程`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'\\\
'）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;


这是我的Hibernate映射文件：

 <？xml version =1.0？> 
<！DOCTYPE hibernate-mapping PUBLIC 
 -  // Hibernate / Hibernate映射DTD 3.0 // EN
http://hibernate.sourceforge.net/hibernate-mapping-3.0 .dtd> 
< hibernate-mapping> 
< class name =edu.model.Mastertable =test_prefixmaster> 
< id name =empcodecolumn =EMPCODElength =10>< / id> 
< property name =dept> 
< column name =DEPARTMENTlength =30/> 
< / property> 
< property name =empname> 
< column name =NAMElength =30/> 
< / property> 
< / class> 
< sql-query name =AbsentReportproccallable =true> 
< return alias =masterclass =edu.model.Master> 
< return-property name =empcodecolumn =EMPCODE/> 
< return-property name =deptcolumn =DEPARTMENT/> 
< return-property name =empnamecolumn =NAME/> 
 
< / return> 
<！[CDATA [CALL AbsentReportproc（：_ fromdate，：_todate）]]> 
< / sql-query> 
< / hibernate-mapping> 
  

这是我的测试类（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（））; 
 
 
 $ b  

当我运行我的Hibernate代码I能够成功打印员工代码，姓名和部门

但是我的实际需求是如何映射列并从临时表中打印d.dte的值命名为 daterange25 ？我应该为我的临时表创建一个Pojo吗？

谢谢

您可以根据SP结果的结构创建POJO，并将其列映射到hibernate映射XML中，而无需指定表名称。不需要返回属性标记。

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

解决方案

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.

这篇关于如何编写存储过程的Hibernate映射文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！