JPA联合查询问题解决

1、因为业务查询需要，有个查询需要嵌套关联三张表，in 关键字JPA查询实体太多注解方式不适合，后来查询很多方法，问了人改了sql结构（结果视图里面不能有UNION 关键字，我真是醉了，mysql,单独执行union就没问题，一旦放在视图的创建语句里面，就报错），后来不得已建了两个简单的视图，也许现在写着觉得总算解决问题了，后面过段时间再看肯定会觉得很傻 哈哈，我有这种预见，不过事务都在再越来越了解的情况下才会有提升和升华；

视图一：

DELIMITER $$

USE `trading_db`$$

DROP VIEW IF EXISTS `use_leader`$$

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `use_leader` AS (
SELECT
  `team_member`.`id`      AS `id`,
  `team_member`.`team_id` AS `team_id`,
  `team_member`.`user_id` AS `user_id`,
  `team_member`.`name`    AS `name`,
  `team_member`.`level`   AS `level`,
  `team_department`.`id`  AS `department_id`
FROM (`team_department`
   LEFT JOIN `team_member`
     ON ((`team_department`.`department_leader` = `team_member`.`id`))))$$

DELIMITER ;

视图二：

DELIMITER $$

USE `trading_db`$$

DROP VIEW IF EXISTS `use_member`$$

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `use_member` AS (
SELECT
  `team_member`.`id`                       AS `id`,
  `team_member`.`team_id`                  AS `team_id`,
  `team_member`.`user_id`                  AS `user_id`,
  `team_member`.`name`                     AS `name`,
  `team_member`.`level`                    AS `level`,
  `team_department_member`.`department_id` AS `department_id`
FROM (`team_department_member`
   LEFT JOIN `team_member`
     ON ((`team_department_member`.`member_id` = `team_member`.`id`))))$$

DELIMITER ;

然后针对视图分别建了各自的实体，useLeader, userMember,实际上字段都一样，就是不能用union，mysql是数据库如果有人能解答；

后面就是针对业务逻辑来写了，这都不是问题，

@Query("SELECT a.id FROM UseLeader a  WHERE a.teamId = ?1 ")
public List<Integer> findUseLeaderIds(Integer teamId);

@Query("select a.id from UseMember a where a.teamId = ?1")
public List<Integer> findUseMemberIds(Integer teamId);

用注解 不能识别union ，但是可以识别in，可以直接传递一个list,所以先把id查出来

@Query("select a from TeamMember a where a.team.id = ?1 and a.id not in ?2")
List<TeamMember> findIdleTeamMembers(Integer teamId, List<Integer> useIds);

总算解决问题了；

后面再补充第二种方式，nativeQuery,这种直接用SQL的查询方式也许更适合我，用习惯了mybatis 没办法