Jpa 动态结果集用法

spring-data-jpa 在使用的时候，结果集对象应对于 实体对应的Field，而由于项目需要，需要进行DTO的转换，对应转换的数据对象，又希望可以直接使用SQL获取。这时，就需要借助几个注解来实现
总结：直接使用Jpa配置，实现 获取 DAO对象改变成获取DTO对象
GitHub https://github.com/oldguys/KnowledgeManual

源对象实体 Menu

@Entity
@Data
/**
 * @author huangrenhao
 * @date 2018/8/7
 */
public class Menu extends BaseEntity implements Comparable<Menu>{

    /**
     * 目录类别
     */
    private String type;

    private Integer sortOrder;

    /**
     * 文档统计数，不计入总数
     */
    @Transient
    private Integer count;
    /**
     * 父级目录
     */
    @Transient
    private Long parentId;
    /**
     * 父级目录名称
     */
    @Transient
    private String parentNodeName;

    /**
     *  是否叶子节点
     */
    @Transient
    private Boolean isLeaf;

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.PERSIST)
    @JoinColumn(name = "parentId")
    private List<Menu> subMenu = Collections.emptyList();

}

其中，使用 @Transient 注解的字段，将不会映射到表中。如果希望获取 被 @Transient 注解字段 的值，就类似于DTO转换。

解决方式：使用 @NamedNativeQueries 和 @SqlResultSetMapping 来进行jpa接口中的方法特殊调用映射。

package com.hrh.kmanual.modules.dao.entites;

import com.hrh.kmanual.commons.dao.entities.BaseEntity;
import lombok.Getter;
import lombok.Setter;

import javax.persistence.*;
import java.util.Collections;
import java.util.Date;
import java.util.List;



@NamedNativeQueries(
        value = {
                @NamedNativeQuery(name = "Menu.findAllByStatus"
                        , query = "SELECT a.id,a.create_time,a.`name`,a.sort_order,a.type,a.`status`,IFNULL(b.count,0) count,c.id parent_id ,c.`name` parent_node_name FROM menu a " +
                        "LEFT JOIN (  SELECT COUNT(*) count,menu_id FROM knowledge a WHERE  a.status = 1 GROUP BY menu_id ) b on a.id = b.menu_id " +
                        "LEFT JOIN menu c on a.parent_id = c.id " +
                        "WHERE a.status = ?1"
                        , resultSetMapping = "MenuItems"),
                @NamedNativeQuery(name = "Menu.findMenus"
                        , query = "SELECT a.id,a.create_time,a.`name`,a.sort_order,a.type,a.`status`,IFNULL(b.count,0) count,c.id parent_id ,c.`name` parent_node_name FROM menu a " +
                        "LEFT JOIN (  SELECT COUNT(*) count,menu_id FROM knowledge a WHERE  a.status = 1 GROUP BY menu_id ) b on a.id = b.menu_id " +
                        "LEFT JOIN menu c on a.parent_id = c.id "
                        , resultSetMapping = "MenuItems"),
        }
)
@SqlResultSetMapping(name = "MenuItems"
        /**
        *  1.映射注入实体对象：
         *  如果 Field 被 @Transient ，则不会注入成功，
         *   结果值 为 实体对象。
        */
//        ,entities= {
//                @EntityResult(entityClass = Menu.class
////                        ,fields = {
////                        @FieldResult(name = "id",column = "id"),
////                        @FieldResult(name = "name",column = "name"),
////                        @FieldResult(name = "createTime",column = "create_time"),
////                        @FieldResult(name = "sortOrder",column = "sort_order"),
////                        @FieldResult(name = "type",column = "type"),
////                        @FieldResult(name = "status",column = "status"),
////                        @FieldResult(name = "count",column = "count1"), //  @Transient ，注入失效。
////                        }
//                )
//        }
        /***
        *  2.将结果集按照顺序注入写到Array，顺序为注解的排列顺序。返回值不再是 Entity ，
         *  并且由于反射，实体集和 List<Menu> 的 单个Entity 引用数组，但是不会报转换异常。
        */
//        ,columns = {
//                @ColumnResult(name = "id",type = Long.class),
//                @ColumnResult(name = "create_time",type = Date.class),
//                @ColumnResult(name = "name"),
//                @ColumnResult(name = "sort_order"),
//                @ColumnResult(name = "type"),
//                @ColumnResult(name = "status"),
//                @ColumnResult(name = "count1",type = Integer.class),
//        }
        /***
        *  3.构造器模式：
         *  自定义构造方式，使用值注入，注入对象可以认为是DTO。
         *  由于是DTO,所以不会触发级联操作。
        */
        , classes = {
        @ConstructorResult(targetClass = Menu.class,
                columns = {
                        @ColumnResult(name = "id",type = Long.class),
                        @ColumnResult(name = "name"),
                        @ColumnResult(name = "create_time"),
                        @ColumnResult(name = "status"),
                        @ColumnResult(name = "type"),
                        @ColumnResult(name = "sort_order"),
                        @ColumnResult(name = "count",type = Integer.class),
                        @ColumnResult(name = "parent_id",type = Long.class),
                        @ColumnResult(name = "parent_node_name"),
                }
             )
        }
)
@Entity
@Getter
@Setter
/**
 * @author huangrenhao
 * @date 2018/8/7
 */
public class Menu extends BaseEntity implements Comparable<Menu>{

    /**
     * 目录类别
     */
    private String type;

    private Integer sortOrder;

    /**
     * 文档统计数，不计入总数
     */
    @Transient
    private Integer count;
    /**
     * 父级目录
     */
    @Transient
    private Long parentId;
    /**
     * 父级目录名称
     */
    @Transient
    private String parentNodeName;

    /**
     *  是否叶子节点
     */
    @Transient
    private Boolean isLeaf;

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.PERSIST)
    @JoinColumn(name = "parentId")
    private List<Menu> subMenu = Collections.emptyList();

    public Menu() {
    }

    public Menu(Long id) {
        super.setId(id);
    }

    public Menu(Long id, String name, Date createTime, Integer status, String type, Integer sortOrder, Integer count,Long parentId,String parentNodeName) {
        super(id, name, createTime, status);
        this.type = type;
        this.sortOrder = sortOrder;
        this.count = count;
        this.parentId = parentId;
        this.parentNodeName = parentNodeName;
    }

    @Override
    public int compareTo(Menu o) {
        return this.getSortOrder() - o.getSortOrder();
    }
}

Step1: @NamedNativeQuery 修改接口类的不同方法调用

实体类注解映射于 com.hrh.kmanual.modules.dao.jpas.MenuRepository

@NamedNativeQueries(
        value = {
                @NamedNativeQuery(name = "Menu.findAllByStatus"
                        , query = "SELECT a.id,a.create_time,a.`name`,a.sort_order,a.type,a.`status`,IFNULL(b.count,0) count,c.id parent_id ,c.`name` parent_node_name FROM menu a " +
                        "LEFT JOIN (  SELECT COUNT(*) count,menu_id FROM knowledge a WHERE  a.status = 1 GROUP BY menu_id ) b on a.id = b.menu_id " +
                        "LEFT JOIN menu c on a.parent_id = c.id " +
                        "WHERE a.status = ?1"
                        , resultSetMapping = "MenuItems"),
                @NamedNativeQuery(name = "Menu.findMenus"
                        , query = "SELECT a.id,a.create_time,a.`name`,a.sort_order,a.type,a.`status`,IFNULL(b.count,0) count,c.id parent_id ,c.`name` parent_node_name FROM menu a " +
                        "LEFT JOIN (  SELECT COUNT(*) count,menu_id FROM knowledge a WHERE  a.status = 1 GROUP BY menu_id ) b on a.id = b.menu_id " +
                        "LEFT JOIN menu c on a.parent_id = c.id "
                        , resultSetMapping = "MenuItems"),
        }
)

com.hrh.kmanual.modules.dao.jpas.MenuRepository 此时 findAllByStatus() 方法和findMenus() 都不会自动生成SQL，会使用在实体类上注解映射的SQL作为查询语句

package com.hrh.kmanual.modules.dao.jpas;

import com.hrh.kmanual.modules.dao.entites.Menu;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import javax.transaction.Transactional;
import java.util.List;

/**
 * @author huangrenhao
 * @date 2018/8/7
 */
public interface MenuRepository extends JpaRepository<Menu, Long> {

    String COMMON_COLUMNS = " id,create_time,name,sort_order,type,parent_id,status ";

    /**
     *
     * @return
     */
    @Query(nativeQuery = true, value = "select " + COMMON_COLUMNS + " from menu where parent_id is null and status = 1")
    List<Menu> findMenus();

    /**
     * 通过nativeQuery获取，{@link Menu}
     * @param status
     * @return
     */
    List<Menu> findAllByStatus(Integer status);
}

Step2: @SqlResultSetMapping 结果集映射方式

1. 设值注入：
   正常注入方法，会触发级联，这时对象 依然为ORM 实体， 无法注入 其他变量。
   依然具有Hibernate特性，比如cascade等。
2. 顺序注入：
   结果集会变成顺序的List。然后依次注入，如果类型顺序错误，则会报异常
3. 构造方法注入：
   需要生成相同的构造方法，来实现参数注入，此时，对象类似于DTO，不再具备ORM特性。

@SqlResultSetMapping(name = "MenuItems"
        /**
        *  1.映射注入实体对象：
         *  如果 Field 被 @Transient ，则不会注入成功，
         *   结果值 为 实体对象。
        */
//        ,entities= {
//                @EntityResult(entityClass = Menu.class
////                        ,fields = {
////                        @FieldResult(name = "id",column = "id"),
////                        @FieldResult(name = "name",column = "name"),
////                        @FieldResult(name = "createTime",column = "create_time"),
////                        @FieldResult(name = "sortOrder",column = "sort_order"),
////                        @FieldResult(name = "type",column = "type"),
////                        @FieldResult(name = "status",column = "status"),
////                        @FieldResult(name = "count",column = "count1"), //  @Transient ，注入失效。
////                        }
//                )
//        }
        /***
        *  2.将结果集按照顺序注入写到Array，顺序为注解的排列顺序。返回值不再是 Entity ，
         *  并且由于反射，实体集和 List<Menu> 的 单个Entity 引用数组，但是不会报转换异常。
        */
//        ,columns = {
//                @ColumnResult(name = "id",type = Long.class),
//                @ColumnResult(name = "create_time",type = Date.class),
//                @ColumnResult(name = "name"),
//                @ColumnResult(name = "sort_order"),
//                @ColumnResult(name = "type"),
//                @ColumnResult(name = "status"),
//                @ColumnResult(name = "count1",type = Integer.class),
//        }
        /***
        *  3.构造器模式：
         *  自定义构造方式，使用值注入，注入对象可以认为是DTO。
         *  由于是DTO,所以不会触发级联操作。
        */
        , classes = {
        @ConstructorResult(targetClass = Menu.class,
                columns = {
                        @ColumnResult(name = "id",type = Long.class),
                        @ColumnResult(name = "name"),
                        @ColumnResult(name = "create_time"),
                        @ColumnResult(name = "status"),
                        @ColumnResult(name = "type"),
                        @ColumnResult(name = "sort_order"),
                        @ColumnResult(name = "count",type = Integer.class),
                        @ColumnResult(name = "parent_id",type = Long.class),
                        @ColumnResult(name = "parent_node_name"),
                }
             )
        }
)