Kohei Nozaki's blog 

NativeQueryで複数のエンティティを同時にSELECTしつつconstructor-resultを使ってみる


Posted on Sunday Jan 26, 2014 at 03:18PM in Technology


NativeQueryでconstructor-resultを使ってSELECTしてみるの続きです。2つのエンティティ分のデータを取得するSELECT文から、エンティティ2つに加えて、DTOクラスのオブジェクト1つをconstructor-resultを使って取得してみます。だからなんだって感じもしますが。

環境

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

何をするか

エンティティと関連の構造は前回と同じです。

今回は以下をSELECT一回で取ってきます

  • Employeeエンティティ
  • Deptエンティティ
  • deptName, firstName, lastNameをフィールドにもつDTO

準備

配置図

図中で選択されている資源を作成または編集します。

  • orm.xmlにNamedNativeQuery “findVarious” とResultSetMapping “variousResult” を追加します
  • テストクラスVariousResultSelectTestを作ります
  • DTOクラスEmployeeProfileを作ります
  • テストデータは前回のを流用します

EmployeeProfile.java

package org.arquillian.example;

public class EmployeeProfile {

    private String deptName;
    private String firstName;
    private String lastName;

    public EmployeeProfile(String deptName, String firstName, String lastName) {
        super();
        this.deptName = deptName;
        this.firstName = firstName;
        this.lastName = lastName;
    }

    @Override
    public String toString() {
        return "EmployeeProfile [deptName=" + deptName + ", firstName=" + firstName + ", lastName=" + lastName + "]";
    }

}

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="findVarious" result-set-mapping="variousResult">
        <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="variousResult">
        <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>
        <constructor-result target-class="org.arquillian.example.EmployeeProfile">
            <column name="deptName" class="java.lang.String" />
            <column name="firstName" class="java.lang.String" />
            <column name="lastName" class="java.lang.String" />
        </constructor-result>
    </sql-result-set-mapping>   

</entity-mappings>

VariousResultSelectTest.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 VariousSelectTest {
    @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("findVarious").getResultList());
    }

    protected void dumpEntityList(List<?> list){
        for(Object o : list){
            Object[] array = (Object[])o;
            Employee employee = (Employee) array[0];
            Dept dept = (Dept) array[1];
            EmployeeProfile employeeProfile = (EmployeeProfile) array[2];
            System.out.println("");
            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(employeeProfile);
            System.out.println("---------------------------------");
        }
    }
}

実行結果

15:15:55,405 INFO  [stdout] (pool-2-thread-20) 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
15:15:55,409 INFO  [stdout] (pool-2-thread-20) 
15:15:55,409 INFO  [stdout] (pool-2-thread-20) ---------------------------------
15:15:55,409 INFO  [stdout] (pool-2-thread-20) id=-1, firstName=Taro, lastName=Yamada, dept_id=-1, deptName=Sales, employee=true, dept=true
15:15:55,409 INFO  [stdout] (pool-2-thread-20) EmployeeProfile [deptName=Sales, firstName=Taro, lastName=Yamada]
15:15:55,410 INFO  [stdout] (pool-2-thread-20) ---------------------------------
15:15:55,410 INFO  [stdout] (pool-2-thread-20) 
15:15:55,410 INFO  [stdout] (pool-2-thread-20) ---------------------------------
15:15:55,410 INFO  [stdout] (pool-2-thread-20) id=-2, firstName=Jiro, lastName=Suzuki, dept_id=-1, deptName=Sales, employee=true, dept=true
15:15:55,410 INFO  [stdout] (pool-2-thread-20) EmployeeProfile [deptName=Sales, firstName=Jiro, lastName=Suzuki]
15:15:55,410 INFO  [stdout] (pool-2-thread-20) ---------------------------------
15:15:55,410 INFO  [stdout] (pool-2-thread-20) 
15:15:55,410 INFO  [stdout] (pool-2-thread-20) ---------------------------------
15:15:55,411 INFO  [stdout] (pool-2-thread-20) id=-3, firstName=Saburo, lastName=Tanaka, dept_id=-2, deptName=Legal, employee=true, dept=true
15:15:55,411 INFO  [stdout] (pool-2-thread-20) EmployeeProfile [deptName=Legal, firstName=Saburo, lastName=Tanaka]
15:15:55,411 INFO  [stdout] (pool-2-thread-20) ---------------------------------

あんまり意味ないかもしれませんが柔軟に出来ているなぁと。

備考

今回使ったsql-result-set-mappingに、さらに以下を追加してもXML的には不正ではないのですが、

<column-result name="deptName"/>

動かそうとすると何故か以下のような例外が出て動きません。まああまり困ることもなさそうですが。

15:26:45,836 SEVERE [org.jboss.arquillian.protocol.jmx.JMXTestRunner] (pool-2-thread-21) Failed: org.arquillian.example.VariousSelectTest.select: javax.persistence.PersistenceException: org.hibernate.loader.custom.NonUniqueDiscoveredSqlAliasException: Encountered a duplicated sql alias [deptName] during auto-discovery of a native-sql query

参考文献

  1. JavaEE 7 JPA 2.1の新機能 ネイティブクエリの改善 - しんさんの出張所 はてな編



Comments:

[url=https://banksnear-me.com/arvest/]closest arvest atm machine[/url]

Posted by Phyllis on April 23, 2022 at 08:42 AM JST #


Leave a Comment

HTML Syntax: NOT allowed