用于构建列表的java复杂jpql查询<>使用嵌套联接的对象筛选
我需要根据分配给给定用户的角色构建Menu/Operation数据结构，但我在应用程序中找不到正确的查询。让我们看看细节：
桌子
我在{
CREATE TABLE MENU (
id_menu tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
name varchar(50) NOT NULL,
PRIMARY KEY (id_menu)
);
CREATE TABLE OPERATION (
id_operation smallint(5) unsigned NOT NULL AUTO_INCREMENT,
id_menu tinyint(3) unsigned NOT NULL,
name varchar(50) NOT NULL,
PRIMARY KEY (id_operation),
CONSTRAINT fk_menu_operation FOREIGN KEY (id_menu) REFERENCES MENU (id_menu)
);
CREATE TABLE ROLE (
role char(15) NOT NULL,
PRIMARY KEY (role)
);
CREATE TABLE roles_operation (
id_operation smallint(5) unsigned NOT NULL,
role char(15) NOT NULL,
PRIMARY KEY (id_operation, role),
CONSTRAINT fk_rolop_operation FOREIGN KEY (id_operation) REFERENCES OPERACION (id_operacion),
CONSTRAINT fk_rolop_role FOREIGN KEY (role) REFERENCES ROL (role)
);
ROLE表似乎没用，但我用它来建模与其他表（如USER）的关系，但这不是问题所在
映射的@Entity类
@Entity
@Table(name = "MENU")
public class Menu implements Serializable {
private static final long serialVersionUID = 2884031708620020329L;
@Id
@Column(name = "id_menu")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long idMenu;
@Column(name = "name")
@NotNull
private String name;
@OneToMany(mappedBy = "menu")
private List<Operation> operations;
// gettters/setters, toString and other methods...
}
@Entity
@Table(name = "OPERATION")
public class Operation implements Serializable {
private static final long serialVersionUID = -76328025481684179L;
@Id
@Column(name = "id_operation")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long idOperation;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "id_menu")
@NotNull
private Menu menu;
@Column(name = "name", unique = true)
@NotNull
private String name;
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(name = "roles_operation",
joinColumns = {
@JoinColumn(name = "id_operation", referencedColumnName = "id_operation")},
inverseJoinColumns = {
@JoinColumn(name = "role", referencedColumnName = "role")})
private List<Role> allowedRoles;
// gettters/setters, toString and other methods...
}
@Entity
@Table(name = "ROLE")
public class Role implements Serializable {
private static final long serialVersionUID = -412065042708649504L;
@Id
@Column(name = "role")
@Enumerated(EnumType.STRING)
private RolEnum role; // Fixed values defined in the same class
@ManyToMany(mappedBy = "roles")
private List<User> users; // The roles asigned to the user, doesn't matter here
@ManyToMany(mappedBy = "allowedRoles")
private List<Operation> operations;
// gettters/setters, toString and other methods...
}
我的业务逻辑是，“一个用户有一个或多个角色，一个角色被赋予一个或多个用户”（一种简单的@ManyToMany关系）。现在，给定一个用户，我需要查询，最好使用JPQL，以获得一个List<Menu>，其中的操作通过它的角色分配给给定的User。使用SQL我可以执行以下查询并获得我想要的结果：
select m.name, o.name, r.role
from MENU m inner join
OPERATION o on m.id_menu = o.id_menu inner join
roles_operation ro on o.id_operation = ro.id_operation inner join
ROLE r on ro.role = r.role
where r.rol in ('RoleA', 'RoleB') -- User's roles
order by 1, 2, 3;
问题是我不知道如何将其翻译成JPQL语言。例如，如果我有以下结构：
菜单/操作/角色结构示例
- Menu1
- Operation1.1
- RoleA
- Operation1.2
-RoleA
-RoleB
- Operation1.3
-RoleC
- Menu2
- Operation2.1
- RoleA
- Operation2.2
- RoleD
- Operation2.3
- RoleB
用户有RoleA和RoleB，我必须得到以下结果（我使用类似JSON的格式，但这些是Java对象）
{
Menu{name=Menu1,
operations=[Operation{name=Operation1.1, role=[RoleA]},
Operation{name=Operation1.2, role=[RoleA, RoleB]}
]
},
Menu{name=Menu2,
operations=[Operation{name=Operation2.1, role=[RoleA]},
Operation{name=Operation2.3, role=[RoleB]}
]
}
}
有人能帮我写这个问题吗？我已经用JPQL试过了
public List<Menu> buildUserMenu(User user) {
// Extracts roles from user
List<Role.RolEnum> roles = user.getRoles().stream()
.map(rol -> rol.getRol())
.collect(Collectors.toList());
Query query = entityManager.createQuery(
"SELECT m FROM "
+ "Menu m INNER JOIN "
+ "m.operations o INNER JOIN "
+ "o.allowedRoles r "
+ "WHERE r.role in :roles")
.setParameter("roles", roles);
return query.getResultList();
}
但是我得到了完整的结构（如上面的示例结构）。JPQL中的查询应该是什么来构建我需要的List<Menu>对象
注:
如果有人有其他的解决方案，比如使用CriteriaAPI，请告诉我怎么做。老实说，我不太清楚如何使用那个API
提前感谢您的回答
失败的测试
测试等级
public class MenuOperationRepositoryTest extends BaseTestRepository {
// The class to test
private MenuOperationRepository menuOperationRepository;
@Before
public void initTestCase() {
// Build EntityManager and some other utility classes,
// load some static data in the test DB (trust me, this works fine)
initTestDB(); // this method is part of BaseTestRepository class
// manual dependency injection (also working correctly)
menuOperationRepository = new MenuOperationRepository();
menuOperationRepository.entityManager = em;
menuOperationRepository.logger = LoggerFactory.getLogger(MenuOperationRepository.class);
// Add some data to DB specific for this class
// The parameters are static methods that returns dummy data
addMenuOperations(menu1(), operation11RoleA(), operation12RoleB(), operation13RoleC());
addMenuOperations(menu2(), operation21RoleB(), operation22RoleA(), operation());
}
@After
public void finishTestCase() {
// Closes em and emf (EntityManagerFactory)
closeEntityManager();
}
@Test
public void buildMenuWithOperationsFilteredByRoles() {
// userWithId() generates a dummy User object with Id as if saved in the DB
// userRoleARoleB() generates a dummy object like menu1() and has RoleA and RoleB
List<Menu> result = menuOperationRepository.buildUserMenu(userWithId(userRoleARoleB(), 1L));
// the assertion methods are statically imported from org.junit.Assert and org.hamcrest.CoreMatchers
// I should be getting menu1() and menu2()
assertThat(result.size(), is(equalTo(2)));
// menu1() should contain operation11RoleA() and operation12RoleB()
assertThat(result.get(0).getOperations().size(), is(equalTo(2)));
// menu2() should contain operation21RoleB() and operation22RoleA()
assertThat(result.get(1).getOperations().size(), is(equalTo(2)));
}
/*
* HELPER METHODS
*/
private void addMenuOperations(Menu menu, Operation... operations) {
List<Operacion> operationList = Arrays.asList(operations);
// This is a utility class which manages the connections manually
// I use this in my tests only, in the server this is done automatically
// This works correctly
dbCommandExecutor.executeCommand(() -> {
em.persist(menu);
operationList.forEach((op) -> {
op.setMenu(menu);
em.persist(op);
});
return null;
});
}
}
要测试的类
public class RepositorioMenuOperacion {
@PersistenceContext(unitName = "sigeaPU")
EntityManager entityManager;
@Inject
Logger logger;
public List<Menu> buildUserMenu(User user) {
logger.debug("buildUserMenu({})", user);
// Extracts roles from user object, this works fine according to the log trace
List<Rol.RolEnum> roles = usuario.getRoles().stream()
.map(rol -> rol.getRol())
.collect(Collectors.toList());
logger.debug("Extracted roles: {}", roles);
// The query I'm using and that I posted previously
Query query = entityManager.createQuery(
"SELECT m FROM "
+ "Menu m INNER JOIN "
+ "m.operations o INNER JOIN "
+ "o.allowedRoles r "
+ "WHERE r.role in :roles")
.setParameter("roles", roles);
List<Menu> menuList = query.getResultList();
logger.info("Menu List found");
menuList.forEach(menu -> logger.info(menu.toString()));
return menuList;
}
}
我认为这是问题的全貌。我需要重写查询或过滤查询后的menuList，但我无法解决它。我在查询后尝试过过滤，但是我得到了一个ConcurrentModificationException，无法确切说明原因
如果您需要有关我的应用程序的更多数据，请告诉我
# 1 楼答案
您的查询很好，但它不会返回您想要的内容。它返回至少包含一个允许操作的所有菜单。因为它返回菜单，并且菜单有操作，所以返回的菜单包含所有操作。因为这就是关联：它不包含特定于给定查询的条目。它包含菜单的操作
您需要的不是返回菜单，而是返回允许的操作。然后，您可以使用ManyTone关联获取他们的菜单：
这只会将允许的操作返回给用户