NativeQueryで関連持ちエンティティをSELECTしてみる

Posted on Sunday Jan 26, 2014 at 11:05AM in Technology

NativeQueryでSELECTしてみるの続きです。前回は関連の無いエンティティでしたが今回は関連のあるエンティティをSELECTしてみます

環境

Hibernate 4.3.0.Final
WildFly8.0.0.CR1
Oracle JDK7u51
postgresql-9.3-1100.jdbc41.jar
PostgreSQL 9.2.4

用意するエンティティ

Deptエンティティを追加してこういう感じにします。

Hibernateが作るテーブルはこういう感じになります

jpaprac=# \d dept
              Table "public.dept"
  Column  |          Type          | Modifiers 
----------+------------------------+-----------
 id       | bigint                 | not null
 deptname | character varying(255) | 
Indexes:
    "dept_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "employee" CONSTRAINT "fk_1n3sqh4h9gtmwb1o1twpoi30l" FOREIGN KEY (dept_id) REFERENCES dept(id)

jpaprac=# \d employee
            Table "public.employee"
  Column   |          Type          | Modifiers 
-----------+------------------------+-----------
 id        | bigint                 | not null
 firstname | character varying(255) | 
 lastname  | character varying(255) | 
 dept_id   | bigint                 | 
Indexes:
    "employee_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "fk_1n3sqh4h9gtmwb1o1twpoi30l" FOREIGN KEY (dept_id) REFERENCES dept(id)

jpaprac=#

準備

配置図

図中で選択されている資源を作成または編集します。Employee.javaは前回も使いましたが関連を追加するために若干変更します。orm.xmlには新たなクエリを追加します。

Dept.java

package org.arquillian.example;

import java.io.Serializable;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;

@Entity
public class Dept implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    @Column
    private String deptName;
    @OneToMany(mappedBy = "dept")
    private List<Employee> employees;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getDeptName() {
        return deptName;
    }

    public void setDeptName(String deptName) {
        this.deptName = deptName;
    }

    public List<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(List<Employee> employees) {
        this.employees = employees;
    }

    @Override
    public String toString() {
        return "Dept [id=" + id + ", deptName=" + deptName + ", employees=" + employees.size() + "]";
    }

}

Employee.java

package org.arquillian.example;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;

@Entity
public class Employee implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    @Column
    private String firstName;
    @Column
    private String lastName;
    @ManyToOne
    @JoinColumn
    private Dept dept;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public Dept getDept() {
        return dept;
    }

    public void setDept(Dept dept) {
        this.dept = dept;
    }

    @Override
    public String toString() {
        return "Employee [id=" + id + ", firstName=" + firstName + ", lastName=" + lastName + ", dept=" + dept + "]";
    }

}

orm.xml

findEmployeeWithDeptを追加します

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="2.1"
    xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm http://xmlns.jcp.org/xml/ns/persistence/orm_2_1.xsd">

    <named-native-query name="findEmployee" result-set-mapping="employeeResult">
        <query><![CDATA[
            SELECT
                emp.id,
                emp.firstName,
                emp.lastName
            FROM
                Employee AS emp
        ]]></query>
    </named-native-query>

    <named-native-query name="findEmployeeWithDept" result-set-mapping="employeeResult">
        <query><![CDATA[
            SELECT
                emp.id,
                emp.firstName,
                emp.lastName,
                emp.dept_id
            FROM
                Employee AS emp
        ]]></query>
    </named-native-query>

    <sql-result-set-mapping name="employeeResult">
        <entity-result entity-class="org.arquillian.example.Employee"/>
    </sql-result-set-mapping>       
</entity-mappings>

RelativeSelectTest.java

package org.arquillian.example;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;

import org.jboss.arquillian.container.test.api.Deployment;
import org.jboss.arquillian.junit.Arquillian;
import org.jboss.arquillian.persistence.UsingDataSet;
import org.jboss.shrinkwrap.api.Archive;
import org.jboss.shrinkwrap.api.ShrinkWrap;
import org.jboss.shrinkwrap.api.asset.EmptyAsset;
import org.jboss.shrinkwrap.api.spec.WebArchive;
import org.junit.Test;
import org.junit.runner.RunWith;

@RunWith(Arquillian.class)
public class RelativeSelectTest {
    @Deployment
    public static Archive<?> createDeployment() {
        Archive<?> a = ShrinkWrap.create(WebArchive.class, "test.war")
            .addPackage(Employee.class.getPackage())
            .addAsResource("test-persistence.xml", "META-INF/persistence.xml")
            .addAsResource("META-INF/orm.xml", "META-INF/orm.xml")
            .addAsWebInfResource(EmptyAsset.INSTANCE, "beans.xml")
            .addAsWebInfResource("jbossas-ds.xml");
        return a;
    }

    @PersistenceContext
    EntityManager em;

    @Test
    @Transactional
    @UsingDataSet({"datasets/relativeSelect/dept.yml", "datasets/relativeSelect/employees.yml"})
    public void select() throws Exception {
        dumpEntityList(em.createNamedQuery("findEmployeeWithDept", Employee.class).getResultList());
    }

    protected void dumpEntityList(List<Employee> employees){
        for(Employee e : employees){
            String fmt = String.format("id=%s, firstName=%s, lastName=%s, deptName=%s",
                    e.getId(), e.getFirstName(), e.getLastName(), e.getDept().getDeptName());
            System.out.println(fmt);
        }
    }
}

dept.yml

dept:
  - id: -1
    deptName: Sales
  - id: -2
    deptName: Legal

employees.yml

employee:
  - id: -1
    firstname: Taro
    lastname: Yamada
    dept_id: -1
  - id: -2
    firstname: Jiro
    lastname: Suzuki
    dept_id: -1
  - id: -3
    firstname: Saburo
    lastname: Tanaka
    dept_id: -2

実行結果

11:48:22,373 INFO  [stdout] (pool-2-thread-4) Hibernate: SELECT emp.id, emp.firstName, emp.lastName, emp.dept_id FROM Employee AS emp
11:48:22,375 INFO  [stdout] (pool-2-thread-4) Hibernate: select dept0_.id as id1_0_0_, dept0_.deptName as deptName2_0_0_ from Dept dept0_ where dept0_.id=?
11:48:22,376 INFO  [stdout] (pool-2-thread-4) Hibernate: select dept0_.id as id1_0_0_, dept0_.deptName as deptName2_0_0_ from Dept dept0_ where dept0_.id=?
11:48:22,377 INFO  [stdout] (pool-2-thread-4) id=-1, firstName=Taro, lastName=Yamada, deptName=Sales
11:48:22,377 INFO  [stdout] (pool-2-thread-4) id=-2, firstName=Jiro, lastName=Suzuki, deptName=Sales
11:48:22,377 INFO  [stdout] (pool-2-thread-4) id=-3, firstName=Saburo, lastName=Tanaka, deptName=Legal