【Java系列】JPA中实现NatveSql进行多表关联查询

关于JPA

JPA 是一个基于O/R映射的标准规范，定义了标准接口和相关的注解。具体的实现由各厂家自己接接口规范来做，比如本示例中用到的是Hibernate。下面给出相关的配置。

sping:
  # JPA configuration
  jpa:
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
    show-sql: false
    hibernate:
      #Only for Development configuration. It will DELETE table before CRATE table.
      #ddl-auto: create
      naming:
        physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategy
  hikari:
    maximum-pool-size: 20
    minimum-idle: 5
    auto-commit: true
    idle-timeout: 30000
    #pool-name: DatebookHikariCP
    max-lifetime: 1800000
    connection-timeout: 30000
    connection-test-query: SELECT 1

创建entity

@Entity
@Table(name = "inf_menu")
public class InfMenu {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Integer id;

    @Column(name = "page_url", length = 45)
    private String pageUrl;

    @Column(name = "page_name", length = 45)
    private String pageName;

    @Column(name = "code", length = 45)
    private String code;

    @Column(name = "parent_menu_id")
    private Integer parentMenuId;

}

@Entity
@Table(name = "inf_menu_role")
public class InfMenuRole {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Integer id;

    @Column(name = "menu_id")
    private Integer menuId;

    @Column(name = "user_id")
    private Integer userId;

}

创建repository

JPA的Repository已经默认支持如findAll()之类的查询。如果要加上相应的SELECCT * FROM x WHERE x;条件，可以通过增加如下的方法来实现，示例中把userId作为过滤条件。

public interface InfMenuRoleRepository extends JpaRepository<InfMenuRole, Integer> {
    List<InfMenuRole> findByUserId(Integer userId);
}

创建nativeQuery查询

通过@Query()注解来完成。

public interface InfMenuRoleRepository extends JpaRepository<InfMenuRole, Integer> {
    List<InfMenuRole> findByUserId(Integer userId);

    @Query(nativeQuery = true, value = "SELECT b.user_id, b.menu_id, a.id AS role_id, a.parent_menu_id, a.page_name, a.page_url "
            +"FROM inf_menu_role b "
            +"LEFT JOIN inf_menu a ON b.menu_id=a.id WHERE b.user_id=?1")
    List<Object[]> findAllMenuByUserId(Integer userId);
}

查询结果集的处理

由于nativeQuery方式查询出来的结果是以Object[Object[]] 的方式来存储的，所以，使用的时候需要进行转换。

List<SidebarMenu> sidebarMenus = new ArrayList<>();

        List<Object[]> rows = infMenuRoleRepository.findAllMenuByUserId(sysUser.getUserUid());
        for (Object[] cells : rows) {
            int menuId = JPAUtils.toInt(cells, 1);
            int parentMenuId = JPAUtils.toInt(cells, 3);

            if (parentMenuId == 0) {
                SidebarMenu sidebarMenuItem = new SidebarMenu();

                sidebarMenuItem.setPageName(JPAUtils.toString(cells, 4));
                sidebarMenuItem.setPageUrl(JPAUtils.toString(cells, 5));

                List<SidebarMenuSubMenuItem> sidebarMenuSubMenuItems = new ArrayList<>();
                for (Object[] object1 : rows) {
                    if (JPAUtils.toInt(object1, 3) == menuId) {
                        SidebarMenuSubMenuItem sidebarMenuSubMenuItem = new SidebarMenuSubMenuItem();
                        sidebarMenuSubMenuItem.setName(JPAUtils.toString(object1, 4));
                        sidebarMenuSubMenuItem.setHref(JPAUtils.toString(object1, 5));
                        sidebarMenuSubMenuItems.add(sidebarMenuSubMenuItem);
                    }
                }
                // 二级加一级
                sidebarMenuItem.setSubMenuItems(sidebarMenuSubMenuItems);
                sidebarMenus.add(sidebarMenuItem);
            }
        }