I need to build a Menu/Operation data structure based on the roles assigned to a given user and I'm not finding the right query in my app. Let's see the details:
I'm working in MySQL, but that's not the problem, could be any DBMS. These are the involved tables in my query
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)
);
The ROLE table seems to be useless but I use it to model the relationship with other tables like USER, but that's not the problem.
@Entity classes@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...
}
My business logic is that "A User has one or more roles, and a role is given to one or more users" (a simple @ManyToMany relationship). Now, given a user, I need to query, preferably using JPQL, to get a List<Menu> with the operations assigned to the given User via it's roles. With SQL I can perform the following query and get the result that I want:
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;
The problem is that I don't know how to translate this to the JPQL language. E.g., if I have the following structure:
- Menu1
- Operation1.1
- RoleA
- Operation1.2
-RoleA
-RoleB
- Operation1.3
-RoleC
- Menu2
- Operation2.1
- RoleA
- Operation2.2
- RoleD
- Operation2.3
- RoleB
And the user has RoleA and RoleB, I must get the following result (I use a JSON-like format, but these are Java objects)
{
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]}
]
}
}
Can anyone help me writing this query? I've tried this using 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();
}
But I'm getting the full structure (like the example structure above). What should be the query in JPQL to build the List<Menu> object that I need?
If someone has other solution, like using the Criteria API, please tell me how to do it. To be honest, I don't know quite well how to use that API.
Thanks in advance for your answers.
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;
}
}
I think this gives a full picture of the problem. I need to rewrite the query or to filter the menuList after the query but I can't solve it. I give it a try with filtering after querying but I'm getting a ConcurrentModificationException, can't say exactly why.
If you need more data about my application, just let me know.
Your query is fine, but it doesn't return what you want. It returns all the menus containing at least one allowed operation. And since it returns Menus, and menus have operations, the returned menus contain all their operations. Because that's what a association is: it doesn't contain entries specific to a given query. It contains the operations of the menu.
What you need, instead of returning the menus, is to return the allowed operations. You can then get their menu using the ManyToOne association:
select o from Operation o
join o.allowedRoles role
where role role in :roles
That will only return the operations allowed to the user.