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 没办法