Hibernate example source code file (Mappings.hbm.xml)

This example Hibernate source code file (Mappings.hbm.xml) is included in the DevDaily.com "Java Source Code Warehouse" project. The intent of this project is to help you "Learn Java by Example" ^TM.

Java - Hibernate tags/keywords

as, as, employment, employment, from, from, id, organization, person, return, select, sys_refcursor, where, where

The Hibernate Mappings.hbm.xml source code

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
        "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd">

<!--
    This mapping demonstrates the use of Hibernate with all-handwritten SQL!

    Note: this version is for Oracle
-->
<hibernate-mapping package="org.hibernate.test.sql.hand" default-access="field">

    <class name="Organization">
        <id name="id" unsaved-value="0" column="orgid">
            <generator class="increment"/>
        </id>
        <property name="name" not-null="true"/>
        <set name="employments"
             inverse="true"
             order-by="DUMMY">
            <key column="employer"/>
            <!-- only needed for DDL generation -->
            <one-to-many class="Employment"/>
            <loader query-ref="organizationEmployments"/>
        </set>
        <!-- query-list name="currentEmployments"
         query-ref="organizationCurrentEmployments"-->
        <loader query-ref="organization"/>
        <sql-insert>INSERT INTO ORGANIZATION (NAME, ORGID) VALUES ( UPPER(?), ? )
        <sql-update>UPDATE ORGANIZATION SET NAME=UPPER(?) WHERE ORGID=?
        <sql-delete>DELETE FROM ORGANIZATION WHERE ORGID=?
    </class>

    <class name="Person">
        <id name="id" unsaved-value="0" column="perid">
            <generator class="increment"/>
        </id>
        <property name="name" not-null="true"/>
        <loader query-ref="person"/>
        <sql-insert callable="true" check="none">{call createPerson(?,?)}
        <sql-update>UPDATE PERSON SET NAME=UPPER(?) WHERE PERID=?
        <sql-delete>DELETE FROM PERSON WHERE PERID=?
    </class>

    <class name="Employment">
        <id name="employmentId" unsaved-value="0" column="empid">
            <generator class="increment"/>
        </id>
        <many-to-one name="employee" not-null="true" update="false"/>
        <many-to-one name="employer" not-null="true" update="false"/>
        <property name="startDate" not-null="true" update="false" insert="false"/>
        <property name="endDate" insert="false"/>
        <property name="regionCode" update="false"/>
        <property name="salary" type="org.hibernate.test.sql.hand.MonetaryAmountUserType">
            <column name="VALUE" sql-type="float"/>
            <column name="CURRENCY"/>
        </property>
        <loader query-ref="employment"/>
        <sql-insert>
            INSERT INTO EMPLOYMENT
            (EMPLOYEE, EMPLOYER, STARTDATE, REGIONCODE, VALUE, CURRENCY, EMPID)
            VALUES (?, ?, CURRENT_DATE, UPPER(?), ?, ?, ?)
        </sql-insert>
        <sql-update>UPDATE EMPLOYMENT SET ENDDATE=?, VALUE=?, CURRENCY=? WHERE EMPID=?
        <sql-delete>DELETE FROM EMPLOYMENT WHERE EMPID=?
    </class>

    <class name="TextHolder">
        <id name="id" column="id">
            <generator class="increment"/>
        </id>
        <property name="description" type="text" length="15000"/>
        <loader query-ref="textholder"/>
        <sql-insert>
            INSERT INTO TEXTHOLDER
            (DESCRIPTION, ID)
            VALUES (?, ?)
        </sql-insert>
        <sql-update>UPDATE TEXTHOLDER SET DESCRIPTION=? WHERE ID=?
        <sql-delete>DELETE FROM TEXTHOLDER WHERE ID=?
    </class>

    <class name="ImageHolder">
        <id name="id" column="id">
            <generator class="increment"/>
        </id>
        <property name="photo" type="image" length="15000"/>
        <loader query-ref="imageholder"/>
        <sql-insert>
            INSERT INTO IMAGEHOLDER
            (PHOTO, ID)
            VALUES (?, ?)
        </sql-insert>
        <sql-update>UPDATE IMAGEHOLDER SET PHOTO=? WHERE ID=?
        <sql-delete>DELETE FROM IMAGEHOLDER WHERE ID=?
    </class>    

    <resultset name="org-emp-regionCode">
        <return-scalar column="regionCode" type="string"/>
        <return alias="org" class="Organization"/>
        <return-join alias="emp" property="org.employments"/>
    </resultset>

    <resultset name="org-emp-person">
        <return alias="org" class="Organization"/>
        <return-join alias="emp" property="org.employments"/>
        <return-join alias="pers" property="emp.employee"/>
    </resultset>

    <sql-query name="person">
        <return alias="p" class="Person" lock-mode="upgrade"/>
        SELECT NAME AS {p.name}, PERID AS {p.id} FROM PERSON WHERE PERID=? /*FOR UPDATE*/
    </sql-query>

    <sql-query name="organization">
        <return alias="org" class="Organization"/>
        <return-join alias="emp" property="org.employments"/>
        SELECT {org.*}, {emp.*}
        FROM ORGANIZATION org
        LEFT OUTER JOIN EMPLOYMENT emp ON org.ORGID = emp.EMPLOYER
        WHERE org.ORGID=?
    </sql-query>


    <sql-query name="allOrganizationsWithEmployees" flush-mode="never">
        <return alias="org" class="Organization"/>
        SELECT DISTINCT org.NAME AS {org.name}, org.ORGID AS {org.id}
        FROM ORGANIZATION org
        INNER JOIN EMPLOYMENT e ON e.EMPLOYER = org.ORGID
    </sql-query>


    <sql-query name="employment">
        <return alias="emp" class="Employment"/>
        SELECT EMPLOYEE AS {emp.employee}, EMPLOYER AS {emp.employer},
        STARTDATE AS {emp.startDate}, ENDDATE AS {emp.endDate},
        REGIONCODE as {emp.regionCode}, EMPID AS {emp.id}
        FROM EMPLOYMENT
        WHERE EMPID = ?
    </sql-query>


    <sql-query name="organizationEmployments">
        <load-collection alias="empcol" role="Organization.employments"/>
        SELECT {empcol.*}
        FROM EMPLOYMENT empcol
        WHERE EMPLOYER = :id
        ORDER BY STARTDATE ASC, EMPLOYEE ASC
    </sql-query>


    <sql-query name="organizationCurrentEmployments">
        <return alias="emp" class="Employment">
            <return-property name="salary">
                <!-- as multi column properties are not supported via the
                              {}-syntax, we need to provide an explicit column list for salary via <return-property> -->
                <return-column name="VALUE"/>
                <return-column name="CURRENCY"/>
            </return-property>
            <!-- Here we are remapping endDate. Notice that we can still use {emp.endDate} in the SQL. -->
            <return-property name="endDate" column="myEndDate"/>
        </return>
        <synchronize table="EMPLOYMENT"/>
        SELECT EMPLOYEE AS {emp.employee}, EMPLOYER AS {emp.employer},
        STARTDATE AS {emp.startDate}, ENDDATE AS {emp.endDate},
        REGIONCODE as {emp.regionCode}, EMPID AS {emp.id}, VALUE, CURRENCY
        FROM EMPLOYMENT
        WHERE EMPLOYER = :id AND ENDDATE IS NULL
        ORDER BY STARTDATE ASC
    </sql-query>

    <sql-query name="textholder">
        <return alias="h" class="TextHolder" lock-mode="upgrade"/>
        SELECT ID AS {h.id}, DESCRIPTION AS {h.description} FROM TEXTHOLDER WHERE ID=? /*FOR UPDATE*/
    </sql-query>

    <sql-query name="imageholder">
        <return alias="h" class="ImageHolder" lock-mode="upgrade"/>
        SELECT ID AS {h.id}, PHOTO AS {h.photo} FROM IMAGEHOLDER WHERE ID=? /*FOR UPDATE*/
    </sql-query>

    <database-object>
        <create>
            CREATE OR REPLACE FUNCTION testParamHandling (j number, i number)
            RETURN SYS_REFCURSOR AS st_cursor SYS_REFCURSOR;
            BEGIN
            OPEN st_cursor FOR
            SELECT j as value, i as value2 from dual;
            RETURN st_cursor;
            END;
        </create>
        <drop>
            DROP FUNCTION testParamHandling
        </drop>
    </database-object>

    <database-object>
        <create>
            CREATE OR REPLACE FUNCTION simpleScalar (j number)
            RETURN SYS_REFCURSOR AS st_cursor SYS_REFCURSOR;
            BEGIN
            OPEN st_cursor FOR
            SELECT j as value, 'getAll' as name from dual;
            RETURN st_cursor;
            END;
        </create>
        <drop>
            DROP FUNCTION simpleScalar
        </drop>
    </database-object>

    <database-object>
        <create>
            CREATE OR REPLACE FUNCTION allEmployments
            RETURN SYS_REFCURSOR AS st_cursor SYS_REFCURSOR;
            BEGIN
            OPEN st_cursor FOR
            SELECT EMPLOYEE, EMPLOYER, STARTDATE, ENDDATE,
            REGIONCODE, EMPID, VALUE, CURRENCY
            FROM EMPLOYMENT;
            RETURN st_cursor;
            END;
        </create>
        <drop>
            DROP FUNCTION allEmployments
        </drop>
    </database-object>

    <database-object>
        <create>
            CREATE OR REPLACE PROCEDURE createPerson(p_name PERSON.NAME%TYPE, p_id PERSON.PERID%TYPE)
            AS
                rowcount INTEGER;
            BEGIN
                INSERT INTO PERSON ( PERID, NAME ) VALUES ( p_id, UPPER( p_name ) );
                rowcount := SQL%ROWCOUNT;
                IF rowcount = 1 THEN
                    NULL;
                ELSE
                    RAISE_APPLICATION_ERROR( -20001, 'Unexpected rowcount [' || rowcount || ']' );
                END IF;
            END;
        </create>
        <drop>
            DROP PROCEDURE createPerson
        </drop>
    </database-object>

</hibernate-mapping>