NativeQueryで複数のエンティティを1度にSELECTしてみる
TweetPosted on Sunday Jan 26, 2014 at 01:51PM in Technology
NativeQueryでエンティティとスカラ値を同時にSELECTしてみるの続きです。今回は2つのエンティティを1回のSELECT文で取得してみます。
環境
- Hibernate 4.3.0.Final
- WildFly8.0.0.CR1
- Oracle JDK7u51
- postgresql-9.3-1100.jdbc41.jar
- PostgreSQL 9.2.4
何をするか
エンティティと関連の構造は前回と同じです。
今回はEmployeeエンティティとDeptエンティティを同時にSELECTしてみます。
準備
配置図
図中で選択されている資源を作成または編集します。
- orm.xmlにNamedNativeQuery “findEmployeeWithDeptAtOnce” とResultSetMapping “employeeWithDeptResult” を追加します
- MultiEntitySelectTestを作ります
- テストデータは前回のを流用します
orm.xml
<?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="findEmployeeWithDeptAtOnce" result-set-mapping="employeeWithDeptResult">
<query><![CDATA[
SELECT
emp.id,
emp.firstName,
emp.lastName,
emp.dept_id,
dep.deptName
FROM
Employee AS emp,
Dept AS dep
WHERE
emp.dept_id = dep.id
]]></query>
</named-native-query>
<sql-result-set-mapping name="employeeWithDeptResult">
<entity-result entity-class="org.arquillian.example.Employee"/>
<entity-result entity-class="org.arquillian.example.Dept">
<field-result name="id" column="dept_id"/>
<field-result name="deptName" column="deptName"/>
</entity-result>
</sql-result-set-mapping>
</entity-mappings>
ResultSetMappingのfield-result要素で、Deptエンティティのidフィールドの値はクエリ中のdept_idだよ、と教えてやります
MultiEntitySelectTest.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 MultiEntitySelectTest {
@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("findEmployeeWithDeptAtOnce").getResultList());
}
protected void dumpEntityList(List<?> list){
for(Object o : list){
Object[] array = (Object[])o;
Employee employee = (Employee) array[0];
Dept dept = (Dept) array[1];
System.out.println("");
System.out.printf("id=%d, firstName=%s, lastName=%s, dept_id=%s, deptName=%s, employee=%s, dept=%s\n",
employee.getId(), employee.getFirstName(), employee.getLastName(),
dept.getId(), dept.getDeptName(),
em.contains(employee), em.contains(dept));
System.out.println("---------------------------------");
System.out.println("checking relation references");
System.out.println(dept.getEmployees());
System.out.println(employee.getDept());
System.out.println("---------------------------------");
}
}
}
実行結果
4,688 INFO [stdout] (pool-2-thread-15) Hibernate: SELECT emp.id, emp.firstName, emp.lastName, emp.dept_id, dep.deptName FROM Employee AS emp, Dept AS dep WHERE emp.dept_id = dep.id 14:11:54,691 INFO [stdout] (pool-2-thread-15) 14:11:54,692 INFO [stdout] (pool-2-thread-15) id=-1, firstName=Taro, lastName=Yamada, dept_id=-1, deptName=Sales, employee=true, dept=true 14:11:54,692 INFO [stdout] (pool-2-thread-15) --------------------------------- 14:11:54,692 INFO [stdout] (pool-2-thread-15) checking relation references 14:11:54,693 INFO [stdout] (pool-2-thread-15) [Employee [id=-1, firstName=Taro, lastName=Yamada, dept=Dept [id=-1, deptName=Sales, employees=2]], Employee [id=-2, firstName=Jiro, lastName=Suzuki, dept=Dept [id=-1, deptName=Sales, employees=2]]] 14:11:54,693 INFO [stdout] (pool-2-thread-15) Dept [id=-1, deptName=Sales, employees=2] 14:11:54,693 INFO [stdout] (pool-2-thread-15) --------------------------------- 14:11:54,693 INFO [stdout] (pool-2-thread-15) 14:11:54,693 INFO [stdout] (pool-2-thread-15) id=-2, firstName=Jiro, lastName=Suzuki, dept_id=-1, deptName=Sales, employee=true, dept=true 14:11:54,694 INFO [stdout] (pool-2-thread-15) --------------------------------- 14:11:54,694 INFO [stdout] (pool-2-thread-15) checking relation references 14:11:54,694 INFO [stdout] (pool-2-thread-15) [Employee [id=-1, firstName=Taro, lastName=Yamada, dept=Dept [id=-1, deptName=Sales, employees=2]], Employee [id=-2, firstName=Jiro, lastName=Suzuki, dept=Dept [id=-1, deptName=Sales, employees=2]]] 14:11:54,694 INFO [stdout] (pool-2-thread-15) Dept [id=-1, deptName=Sales, employees=2] 14:11:54,694 INFO [stdout] (pool-2-thread-15) --------------------------------- 14:11:54,694 INFO [stdout] (pool-2-thread-15) 14:11:54,694 INFO [stdout] (pool-2-thread-15) id=-3, firstName=Saburo, lastName=Tanaka, dept_id=-2, deptName=Legal, employee=true, dept=true 14:11:54,695 INFO [stdout] (pool-2-thread-15) --------------------------------- 14:11:54,695 INFO [stdout] (pool-2-thread-15) checking relation references 14:11:54,695 INFO [stdout] (pool-2-thread-15) [Employee [id=-3, firstName=Saburo, lastName=Tanaka, dept=Dept [id=-2, deptName=Legal, employees=1]]] 14:11:54,696 INFO [stdout] (pool-2-thread-15) Dept [id=-2, deptName=Legal, employees=1] 14:11:54,696 INFO [stdout] (pool-2-thread-15) ---------------------------------
SELECT文が1回になったので状況によってはクエリを軽くするのに寄与するかもしれません。また、こういう取り方をしてもお互いの参照はちゃんと設定されていて、実際にアクセスしてもよけいなクエリを実行する事もないようです。賢い。
と思ったのだがorg.hibernate.SQLのログレベルを上げて見ると、別途SELECT文が実行されている気配。persistence.xmlにhibernate.show_sqlを設定しただけでは全てのSQLは出ないという事なのか、あるいは見落としたのか…。PostgreSQL側にSQLをログ出力させる設定を加えて見てみるとこんなのが出ている。
Feb 2 11:35:46 kyle-no-MacBook.local postgres[1120]: [877-1] 2014-02-02 11:35:46 JST LOG: execute <unnamed>: SELECT emp.id, emp.firstName, emp.lastName, emp.dept_id, dep.deptName FROM Employee AS emp, Dept AS dep WHERE emp.dept_id = dep.id Feb 2 11:35:46 kyle-no-MacBook.local postgres[1120]: [878-1] 2014-02-02 11:35:46 JST LOG: execute <unnamed>: select employees0_.dept_id as dept_id4_0_0_, employees0_.id as id1_1_0_, employees0_.id as id1_1_1_, employees0_.dept_id as dept_id4_1_1_, employees0_.firstName as firstNam2_1_1_, employees0_.lastName as lastName3_1_1_ from Employee employees0_ where employees0_.dept_id=$1 Feb 2 11:35:46 kyle-no-MacBook.local postgres[1120]: [878-2] 2014-02-02 11:35:46 JST DETAIL: parameters: $1 = '-1' Feb 2 11:35:46 kyle-no-MacBook.local postgres[1120]: [879-1] 2014-02-02 11:35:46 JST LOG: execute <unnamed>: select employees0_.dept_id as dept_id4_0_0_, employees0_.id as id1_1_0_, employees0_.id as id1_1_1_, employees0_.dept_id as dept_id4_1_1_, employees0_.firstName as firstNam2_1_1_, employees0_.lastName as lastName3_1_1_ from Employee employees0_ where employees0_.dept_id=$1 Feb 2 11:35:46 kyle-no-MacBook.local postgres[1120]: [879-2] 2014-02-02 11:35:46 JST DETAIL: parameters: $1 = '-2'
続き
NativeQueryでconstructor-resultを使ってSELECTしてみる
参考文献
Tags: jpa
