(Spring-data-jpa) How to get POJO (other than entity class) using JPA2.1

Introduction

Normally when you use spring-data-jpa to get the value, whether you use JPQL or NativeQuqery, If an entity (table class) is received as a return value, for example, the return value when joining tables, etc. Describes how to receive a value in a class other than an entity.

This time, let's get the partition list as follows.

query

SELECT partition_name, table_rows  FROM information_schema.partitions where table_name = 'user';

Version

This time, we will use the following version. spring-data-jpa: 1.10.5.RELEASE hibernate-entitymanager: 5.1.0.Final

--maven definition

        <dependency>
            <groupId>org.springframework.data</groupId>
            <artifactId>spring-data-jpa</artifactId>
            <version>1.10.5RELEASE</version>
        </dependency>
        <!-- JPA -provider(Hibernate) -->
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>5.1.0.Final</version>
        </dependency>

--Notes on Version

JPA is included in the above framework, but please be sure to use it with the above version or higher. The method of receiving other than this entity class can be used from JPA2.1. For example, hibernate-entity manage4 series cannot be used with JPA 2.0, so be careful. In addition, the above spring-data-jpa must also be used with a version that matches the hibernate 5 series.

The drawbacks are the poor visibility of version control and the annoying odor around here. ..

Get using @SqlResultSetMapping

--Target POJO

Try to get the partition name and the number of records in the user table using SqlResultSetMapping.

Execute query

SELECT partition_name, table_rows  FROM information_schema.partitions where table_name = 'user';

Partition.class(Return value storage class)

@AllArgsConstructor
@Data
public class Partition implements Serializable {
    private static final long serialVersionUID = -9071293948637991593L;
    /**Partition name*/
    private String partitionName;
    /**Number of records*/
    private String table_rows;
}

-@SqlResultSetMapping definition

From Jpa2.1, ConstructorResult is available in SqlResultSetMapping. To receive in the above Partition.class, define as follows. The name can be freely defined, and @ConstructorResult specifies the class and column definition to receive.

** Be sure to implement this @SqlResultSetMapping on the entity class. ** ** If you define it outside the entity class, a MappingException will occur.

SqlResultSetMapping definition method

@SqlResultSetMapping(
  name = "User.Partition", 
  classes = { 
    @ConstructorResult(
      targetClass = com.sample.orm.entity.transience.Partition.class,
      columns = { 
        @ColumnResult(name = "partition_name", type = String.class)
        @ColumnResult(name = "table_rows", type = String.class)
      }
    )
  }
)
@Data
@Entity
@Table(name = "user")
public class User implements Serializable {

   ︙
   ︙
}

--SQL execution method

When you get it with NativeQuery, you can get it by specifying the above mapping name. Of course, it is also available in JQPL.

List<Partition> results = em
          .createNativeQuery(
            "SELECT partition_name, table_rows FROM information_schema.partitions where table_name = 'user'",
            "User.Partition" ) 
          .getResultList()

Use sql-result-set-mapping in orm.xml.

--orm.xml placement location

Since the method using @SqlResultSetMapping mentioned above must be defined on the entity class, the entity tends to be complicated when multiple definitions are made. (Because the method of defining each POJO cannot be used) Use orm.xml to avoid it. If you place the following "package configuration" in orm.xml directly under META-INF, it will be read even if it is not defined in applicationContext.xml.

Package configuration

-Java
-Resources
　　　　　｜
　　　　　｜＿conf
　　　　　　　　　　　　　　｜＿☓☓☓
　　　　　　　　　　　　　　｜＿☓☓☓
　　　　　｜＿META-INF
　　　　　　　　　　　　　　｜＿orm.xml
　　　　　｜＿applicationContext.xml

--orm.xml definition

Below is the definition of orm.xml. ** Please note that the definition of "entity-mappings" is different between version 2.1 and others. ** ** ** Be sure to specify 2.1 because it is a function from 2.1 to define POJO in "constructor-result". ** **

The name of "sql-result-set-mapping" is arbitrary, but it should match "result-set-mapping" of "named-native-query". Also, "named-native-query" is not required. It works even if you write SQL directly in the implementation class.

orm.xml

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

    <named-native-query name="User.findPartition" result-set-mapping="User.Partition">
        <query><![CDATA[SELECT partition_name, table_rows FROM information_schema.partitions where table_name = 'user']]></query>
    </named-native-query>

    <sql-result-set-mapping name="User.Partition">
        <constructor-result target-class="com.sample.orm.entity.transience.Partition">
            <column name="partitionName" class="java.lang.String"/>
            <column name="tableRows" class="java.lang.Integer"/>
        </constructor-result>
    </sql-result-set-mapping>
</entity-mappings>

--SQL execution

It can be obtained by specifying the name of "named-native-query" as shown below.

List<Partition> results = em
          .createNamedQuery("User.findPartition") 
          .getResultList()