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

关于JPA

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

sping:# JPA configurationjpa:database-platform: org.hibernate.dialect.MySQL5InnoDBDialectshow-sql: falsehibernate:#Only for Development configuration. It will DELETE table before CRATE table.#ddl-auto: createnaming:physical-strategy: org.springframework.boot.orm.jpa.hibernate.SpringPhysicalNamingStrategyhikari:maximum-pool-size: 20minimum-idle: 5auto-commit: trueidle-timeout: 30000#pool-name: DatebookHikariCPmax-lifetime: 1800000connection-timeout: 30000connection-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);}}