I've recently joined a new project and noticed that the team frequently utilizes SqlResultSetMapping to fetch data from native queries, instead of relying on projections directly. They mentioned it's a "better approach," but I'm not entirely clear on what the advantages are. Could someone please explain why SqlResultSetMapping might be favored over using projections?
5 Answers
In some cases, you might be constrained by unusual requirements, such as legal restrictions against using generated SQL. I had a client, a government entity, that mandated this, so we had to adapt our ORM to use pre-approved SQL directly instead of dynamic queries. It's definitely a unique situation, though!
What's a RBAC security layer?
We've had clients wanting on-prem installations with their existing databases. Native queries would require adapting for each SQL variant, while using ORM helps simplify this across different database platforms, even with some hiccups along the way.
Using an ORM like Hibernate can have its pitfalls. The system might not always perform as expected with complex operations, and debugging can be a hassle. That's why I lean towards lower-level tools because they offer more control and clarity. When you need advanced database features, handwritten SQL usually shines better. Just be cautious; switching databases often requires serious testing and adjustment.
I see where you're coming from. Handwriting SQL can save time especially for tricky optimizations. But for large financial systems, stability is key!
I think ORM tools are great, but they also have their limits, especially if you need specific features like multitenancy.
Native queries can become quite messy to maintain, especially if you ever need to switch databases. Using an ORM minimizes that worry and allows for easier adjustments later on. That being said, it might all come down to personal preference. If using native works for you, that's fine, just know you're potentially locking yourself in!
Have you really experienced no performance drop when using generated queries instead?
Are there any downsides to native queries other than lock-in?
You can't use projections with native queries in the same way you would with JPQL, unless you're using Transformers. SqlResultSetMapping is older and a bit clumsy, and I honestly don’t use it. But I heard Hibernate 6.x has made some improvements in that area!
Interesting! Why not go with everything using views and stored procedures?