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

This example Hibernate source code file (NativeSQLQueries.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, by, employer, employment, employment, from, from, name, order, organization, organization, person, select, select

The Hibernate NativeSQLQueries.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!
-->

<hibernate-mapping package="org.hibernate.test.sql.hand" default-access="field">
	   
	<class name="Organization" table="ORGANIZATION">
		<id name="id" unsaved-value="0" column="ORGID">
			<generator class="increment"/>
		</id>
		<property name="name" column="NAME" not-null="true"/>
		<set lazy="true" name="employments" 
			inverse="true">
			<key column="EMPLOYER"/> 
			<one-to-many class="Employment"/>
		</set>
	</class>
	
	<class name="Person" table="PERSON">
		<id name="id" unsaved-value="0" column="PERID">
			<generator class="increment"/>
		</id>
		<property name="name" column="NAME" not-null="true"/>
	</class>

	<class name="Group" table="GROUPP">
		<id name="id" unsaved-value="0" column="ID">
			<generator class="increment"/>
		</id>
		<property name="name" column="NAME" not-null="true"/>
		<list name="persons" table="GROUP_PERSON"
			cascade="none" inverse="false" lazy="true">
			<key column="GROUP_ID"	/>
			<list-index column="POS" />
			<many-to-many class="Person" column="PERSON_ID" />
		</list>
	</class>
	
	<class name="Employment" table="EMPLOYMENT">
		<id name="employmentId" unsaved-value="0" column="EMPID">
			<generator class="increment"/>
		</id>
		<many-to-one name="employee" column="EMPLOYEE" not-null="true" update="false"/>
		<many-to-one name="employer" column="EMPLOYER" not-null="true" update="false"/>
		<property name="startDate" column="STARTDATE" not-null="false"/>
		<property name="endDate" column="ENDDATE" insert="false"/>
		<property name="regionCode" column="REGIONCODE" update="false"/>
	    <property name="salary" type="org.hibernate.test.sql.hand.MonetaryAmountUserType">
			<column name="VALUE" sql-type="float"/>
			<column name="CURRENCY"/>			
		</property>
	</class>
	
	<class name="Order" table="TBL_ORDER">
	  <composite-id name="orderId" class="Order$OrderId">
	  	<key-property name="orgid"/>
		<key-property name="ordernumber"/>
	  </composite-id>
	  
	  <many-to-one name="product">
	    <column name="PROD_ORGID"/>
	    <column name="PROD_NO"/>
	  </many-to-one>
	  <many-to-one name="person"/>
	</class>
	
	<class name="Product">
	  <composite-id name="productId" class="Product$ProductId">
	  	<key-property name="orgid"/>
		<key-property name="productnumber"/>
	  </composite-id>
	  
	  <property name="name"/>
	  
	  <set name="orders" inverse="true">
	  	<key>
	  		<column name="PROD_ORGID"/>
	  		<column name="PROD_NO"/>	  		
	  	</key>
	  	<one-to-many class="Order"/>
	  </set>
	</class>

    <class name="Dimension">
        <id name="id" type="long">
            <generator class="increment"/>
        </id>
        <property name="length" column="d_len"/>
        <property name="width" column="d_width"/>
    </class>

    <class name="SpaceShip">
        <id name="id" type="integer">
            <generator class="increment"/>
        </id>
        <property name="name" column="fld_name"/>
        <property name="model" column="fld_model"/>
        <property name="speed" column="fld_speed"/>
        <component name="dimensions">
            <property name="length" column="fld_length"/>
            <property name="width" column="fld_width"/>
        </component>
    </class>

    <class name="Speech">
        <id name="id" type="integer">
            <generator class="increment"/>
        </id>
        <property name="name" column="name"/>
        <property name="length" column="flength"/>
    </class>

    <class name="TextHolder">
        <id name="id" column="id">
            <generator class="increment"/>
        </id>
        <property name="description" type="text" length="15000"/>
    </class>

    <class name="ImageHolder">
        <id name="id" column="id">
            <generator class="increment"/>
        </id>
        <property name="photo" type="image" length="15000"/>
    </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>


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

    <resultset name="spaceship-vol">
        <return alias="sps" class="SpaceShip">
            <return-property name="id" column="id"/>
            <return-property name="name" column="name"/>
            <return-property name="model" column="model"/>
            <return-property name="speed" column="speed"/>
            <return-property name="dimensions.length" column="length"/>
            <return-property name="dimensions.width" column="width"/>
        </return>
        <return-scalar column="surface"/>
        <return-scalar column="volume"/>
    </resultset>

    <resultset name="speech">
        <return alias="sp" class="Speech">
            <return-property name="id" column="id"/>
            <return-property name="name" column="name"/>
            <return-property name="length" column="flength"/>
        </return>
        <return-scalar column="scalarName"/>
    </resultset>

    <sql-query name="spaceship" resultset-ref="spaceship-vol">
		select id as id,
               fld_name as name,
               fld_model as model,
               fld_speed as speed,
               fld_length as length,
               fld_width as width,
               fld_length * fld_width as surface,
               fld_length * fld_width *10 as volume
        from SpaceShip
	</sql-query>

    <sql-query name="orgNamesOnly">
		<return-scalar column="NAME" type="string"/>
		SELECT org.NAME FROM ORGANIZATION org
	</sql-query>

	<sql-query name="orgNamesAndOrgs">
		<return-scalar column="thename" type="string"/>
		<return alias="org" class="Organization"/>
		SELECT org.NAME AS thename, org.NAME AS {org.name}, org.ORGID AS {org.id} 
		FROM ORGANIZATION org
		ORDER BY thename
	</sql-query>

	<sql-query name="orgsAndOrgNames">
		<return alias="org" class="Organization"/>
		<return-scalar column="thename" type="string"/>
		SELECT org.NAME AS thename, org.NAME AS {org.name}, org.ORGID AS {org.id} 
		FROM ORGANIZATION org
		ORDER BY thename
	</sql-query>	

	<sql-query name="orgIdsAndOrgNames">
		<return-scalar column="orgid" type="long"/>
		<return-scalar column="thename" type="string"/>
		SELECT NAME AS thename, ORGID AS orgid
		FROM ORGANIZATION
		ORDER BY thename
	</sql-query>

	<sql-query name="AllEmploymentAsMapped">
		<return class="Employment"/>
		SELECT * FROM EMPLOYMENT
	</sql-query>

	<sql-query name="EmploymentAndPerson">
		<return class="Employment"/>
		<return class="Person"/>
		SELECT * FROM EMPLOYMENT, PERSON
	</sql-query>

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

	<sql-query name="organizationreturnproperty">
		<return alias="org" class="Organization">
            <return-property name="id" column="ORGID"/>
            <return-property name="name" column="NAME"/>
		</return>
		<return-join alias="emp" property="org.employments">
			<return-property name="key" column="EMPLOYER"/>
			<return-property name="element" column="EMPID"/>			
			<return-property name="element.employee" column="EMPLOYEE"/>
			<return-property name="element.employer" column="EMPLOYER"/>			
			<return-property name="element.startDate" column="XSTARTDATE"/>
			<return-property name="element.endDate" column="ENDDATE"/>			
			<return-property name="element.regionCode" column="REGIONCODE"/>			
			<return-property name="element.employmentId" column="EMPID"/>						
			<return-property name="element.salary">
                <return-column name="VALUE"/>
                <return-column name="CURRENCY"/>
			</return-property>
		</return-join>
		SELECT org.ORGID as orgid,
            org.NAME as name,
            emp.EMPLOYER as employer,
            emp.EMPID as empid,
            emp.EMPLOYEE as employee,
            emp.EMPLOYER as employer,
            emp.STARTDATE as xstartDate,
            emp.ENDDATE as endDate,
            emp.REGIONCODE as regionCode,
            emp.VALUE as VALUE,
            emp.CURRENCY as CURRENCY
        FROM ORGANIZATION org
			LEFT OUTER JOIN EMPLOYMENT emp ON org.ORGID = emp.EMPLOYER
	</sql-query>

    
	<sql-query name="organizationautodetect" resultset-ref="org-description">
	<!--  equal to "organizationpropertyreturn" but since no {} nor return-property are used hibernate will fallback to use the columns directly from the mapping -->
		<return alias="org" class="Organization"/>
		<return-join alias="emp" property="org.employments"/>
        SELECT org.ORGID as orgid,
            org.NAME as name,
            emp.EMPLOYER as employer,
            emp.EMPID as empid,
            emp.EMPLOYEE as employee,
            emp.EMPLOYER as employer,
            emp.STARTDATE as startDate,
            emp.ENDDATE as endDate,
            emp.REGIONCODE as regionCode,
            emp.VALUE as VALUE,
            emp.CURRENCY as CURRENCY
		FROM ORGANIZATION org
			LEFT OUTER JOIN EMPLOYMENT emp ON org.ORGID = emp.EMPLOYER
	</sql-query>

	<sql-query name="manyToManyFetch">
        <![CDATA[
        	SELECT 	groupp.ID as group_id,
        			groupp.NAME as group_name,
        			group_person.PERSON_ID as group_person_personId,
        			group_person.GROUP_ID as group_person_groupId,
        			group_person.POS as group_person_pos,
        			person.PERID as person_id,
        			person.NAME as person_name
			FROM 	GROUPP groupp,
					GROUP_PERSON group_person,
					PERSON person
			WHERE 	groupp.ID = group_person.GROUP_ID
					and person.PERID = group_person.PERSON_ID
       ]]>
        <return alias="groupp" class="Group">
            <return-property name="id" column="group_id" />
            <return-property name="name" column="group_name" />
        </return>
        <return-join alias="group_person" property="groupp.persons">
            <return-property name="key" column="group_person_groupId" />
            <return-property name="index" column="group_person_pos" />
            <return-property name="element" column="person_id" />
            <return-property name="element.id" column="person_id" />
            <return-property name="element.name" column="person_name" />
        </return-join>
    </sql-query>

</hibernate-mapping>