In my spring boot project I had to create a db view to get some data from several tables by joining them. And the query is working perfectly. Here is the part of my query.
CREATE VIEW "admin-service".view_system_user_access AS
SELECT
systemUser.id AS userId,
systemUser.user_name AS userName,
role.id AS roleId,
role.name AS roleName,
userRole.read_only AS userRoleReadOnly,
form.id AS formId,
form.name AS formName,
form.url_path AS urlPath,
formAccess.read_only AS formAccessReadOnly,
formTab.id AS formTabId,
formTab.name AS formTabName,
FROM system_user systemUser
INNER JOIN ....
Please focus on the column that I selected (I haven't put the full query here). In there I have many selections. To map those into a DTO in java I used interface projection as follows.
interface UserAccess {
Optional<Long> getUserId();
Optional<String> getUserName();
Optional<Long> getRoleId();
Optional<String> getRoleName();
Optional<Boolean> getUserRoleReadOnly();
Optional<Long> getFormId();
Optional<String> getFormName();
Optional<String> getUrlPath();
Optional<Boolean> getFormAccessReadOnly();
Optional<Long> getFormTabId();
Optional<String> getFormTabName();
Optional<Boolean> getFormTabAccessReadOnly();
Optional<Long> getBranchId();
Optional<String> getBranchName();
Optional<Boolean> getBranchAccessReadOnly();
Optional<Long> getEntityId();
Optional<String> getEntityName();
Optional<Boolean> getEntityAccessReadOnly();
Optional<Long> getOrgId();
Optional<String> getOrgName();
}
As you can see in there I had to write get methods for all the selections in the query. So my question is to know about two things. (Keep in mind that my requirement is to take data from a view in db. There is no entity class also)
If it works, it's secure and it's quick, then it's okay. So you need to analyze your solution and detect whether anything prevents it from being okay and fix it.
The use of a view suggests that you have a joined query. This may work for smaller and less busy systems, but could cause huge headaches on larger and busier systems. So, you need to take it with a grain of salt and use it carefully. You can leave it as is if it's working well now, but, if it starts to perform in less than adequate speed for example, then you might want to create a table instead of the view and using a cron job, you might want to periodically refresh that, so the heavy-lifting will only be done once hourly, for example, rather than at each and every request.
As about the fields, you can generate your DTO if it's too much, by writing code that writes code. Looping the columns and seeing what values are in there you can implement a code that more-or-less reliably automatically detects what their getter's name and type should be. It takes a lot of time to implement such a helper, but if you have many thousands of columns whose types you are to infer on long term, then making this automatic makes sense. Of course, such a system may make mistakes, but it's better to fix a code's 10-15 errors for 1000 columns than to manually map 1000 columns with mappers and actually type in all the code yourself.