We have an existing old java application using a native sql queries with JDBC. I need to replace the dao layer using JPA + eclipselink. I have generated the entities using eclipse, but I don't know if the generated entities are optimized for one particular case. In the follow image, I represented the tables I'm struggling with
Notice : The content of the tables App and Func is not editable with this application.
A admin in the application can create a role in the ihm. the role is saved in the Role table. The admin can associate one or several apps to the created role by choosing from a list loaded from the App table. On save, the relation between the role and the selected apps will be inserted in the table RoleApp.
For each selected app, the admin can select one or several functions loaded from Func table using a join on AppFunc. On save, the relation between the role, the selected app and the functions attributed to the app will be inserted in the table RoleFuncApp.
Finally, for each function the admin can create one or sevaral permissions. On save, the permissions will be inserted in the table Perm and a relation between the role, the app, the function and the created permissions will be inserted in the table RoleAppFuncPerm.
Notice : a role have a one to one relationship with a User. Each user in the application has an attributed role by the admin.
The generated Entities will be used in two cases. The first case is when a admin create or manage roles as described above. In this case, by saving a role, the following tables will be updated Role, RoleApp, RoleAppFunc, RoleAppFuncPerm and Perm.
The second case, is when a user log in the application. Basing on its role, the user can access to only chosen apps attached to its role. In each, app he will have access to only chosen functions and for each function he will have access only to the permissions of this function.
For the second case, I'll be happy if I can have in the Role.java entity a List apps containing only the apps having relation with the user's role in RoleApp. And App.java with a List functions containing only the functions having relation with the role and app in RoleAppFunc. And Finally, Func.java with a List permissions containing only the permissions having relation with the role, app and function in RoleAppFunPerm. The goal is to use these lists only as a readonly to manage the access, functions and permissions of the roles in the application.
For the first case, I need a way to have relation in Role.java with the tables Role, RoleApp, RoleAppFunc, RoleAppFuncPerm and Perm. Which will allow me to insert/update/delete in cascade the relation between the role and (apps, functions and permissions).
Have any one an idea of the relations to create in each java entity to do the good insertions/updates when I do entityManager.persist(role) For the first case ?
And what is the best JPQL query to do for the second case (loading a role with only the apps, functions and permissions having relation with it) ?
