I am working on a project (to use as a reference). The project uses mysql, hibernate and spring (REST) and is supposed to be a single microservice.
I have come to find out that limiting user/consumer database access to stored procedures in mysql is considered a better practice security-wise (no unintended operations, and no sql-injection). As a result i created stored procedures for every possible user/consumer interaction with the database.
To use hibernate with stored procedures. I declared
@NamedStoredProcedureQuery for every stored procedure.
The problem (practice wise) is that this approach seem to be very verbose. especially when it comes to stored procedures that return custom (multiple-table) queries (almost all querying stored procedures). As I'll have to define
@SqlResultSetMapping (as shown here) for each custom query returned by a stored procedure.
Should i continue using this practice?
Are there better ways to achieve the same (or better) result?