NativeQueryで関連持ちエンティティをSELECTしてみる
TweetPosted 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
備考
NativeQueryではEmployeeテーブルのデータしか取ってきていないのですが、親レコードへの参照であるdept_idを拾ってきていれば、DeptテーブルのデータはJPA側で勝手に別途SELECT文を発行して取ってきてくれるようですね。
続き
NativeQueryでエンティティとスカラ値を同時にSELECTしてみる
参考文献
Tags: jpa
