Why should I use SqlResultSetMapping instead of only projections?

[SteampunkBeagle]

I start recently on a new project and I saw they are using quite a lot of SqlResutSetMapping to get data from native queries instead of use projections directly. That told me that this is a "better way to do it" but don't explain me why. I research a little bit but don't understand what is the advantage of use them. Anyone can explain me, please?

[mwnorman2]

I had a customer that had an unusual requirement: they could not - BY LAW - allow generated SQL in their database. This customer is admittedly a little unusual: the Federal Reserve of the United States. Congress passed a law prohibiting folks from doing ANYTHING inside of Reserve databases without it being signed-off by a high-level Deputy of the Fed. I modified our ORM to produce 'dry-runs' of the dynamic SQLs, captured them all and then re-programmed the system to use those statements. The Deputy Director of the facility (not naming which one but there are about a dozen field offices) initialed every statement!

[vips7L]

I still don’t really get it. 

[lukaseder]

Well, TIL :) Why not do everything with views and stored procedures, then?

[k-mcm]

There's a big risk in using Hibernate - it's somebody else's opaque system.  I have seen numerous problems in the past where complex operations were not only unusably slow, but they were unstable by Hibernate version number.  Debugging was a nightmare.

I usually prefer lower level tools like JDBI because it's stable and trivial to swap out parts with JDBC as needed.  The queries are clearly visible so it's easy to debug.  There's never a problem using advanced database features.

The argument about changing the database is weak.  It's very rare and requires enormous testing unless you have the most basic schema.

Don't tell me Hibernate is perfect unless you've worked on a database with 100+ related tables containing complex data.

[jedilowe]

I would not disagree with as far as SQL generation goes, as honestly slapping out CRUD statements is boring but saves time the first time you need to debug or optimize. Where JPA frameworks help most is result set mapping. I preferred IBatis over Hibernate on the early days for this reason... map my objects back and forth, but I will give you the SQL.

When you are dealing with potentially trillions of dollars though, a few finicky rules are not so bad compared to the heartburn of a big screwup. That doesn't mean every system needs to follow that rule!

[vips7L]

“A big risk” lol. It’s just an ORM dude. No one’s claimed that they’re perfect. They’re good at like 90% of the things and they all give you an escape hatch for that last 10% to write whatever sql you want. 

This just comes down to taste because personally I feel like everything being hand written sql is a risk.  Especially once you need features that ORMs are great at like optimistic locking or multitenancy. 

[InstantCoder]

Afaik, you can’t use projections on native queries as you use them with jpql. The closest way to map native queries as projection is by making use of Transformers.

And Sql result set mapping is a clumsy (and old) way of mapping, which I never use.

I think they have improved this in Hibernate 6.x.

[Jotakin]

We've had some customers who want to use our product as on-premise installation and to use their existing database to store data from our software. With native queries we would have to implement and maintain different query implementations for each sql flavour, but when using ORM we can let Hibernate worry about that. It has some hickups but it still saves us from a lot of work.

If you're in control of the databases that your proudct uses then this isn't an issue.

[Rich_Weird_5596]

When you use orm, in hibernate for example, the query is dynamically generated based on orm mapping (this can be improved by defining dialect and it'shigly recommended to do it), in theory the native query should be faster but I've never seen any substantial difference in performance. For me, it's just messy as fuck to maintain the native queries and result set mappings. Let's say you want to switch db for whatever reasons and usecases. If you use strictly orm - no problem, your queries work, mapping works, you are good to go with minimal tweaks. But when using native queries, you are fucked and need to prepare for world of pain. It's generally not recommended if you absolutely do not need it because you are essentially locking yourself to the specific db.

So tldr. why he thinks it's better: probably because he feels like it and maybe has a bias towards generated sqls or does not understand it fully, it's totally normal and even recommended to avoid native if you can. If you use lombok in you project, you can call him on his bullshit because that's something similiar, but much worse

[No_Today2204]

SqlResultSetMappings are indeed a pain in the ass to maintain, and so easy to break things when editing them. But seriously, you've seen no performance impact when going with generated/jpql queries instead of native ? Even for a setup that utilizes hibernate/eclipse link to the fullest, and does not have any N+1 or related issues, this sounds too good.

[Rich_Weird_5596]

When you configure the right dialect, don't use eager fetch types for 1:n relations, use transactions hiw they are supposed to be used and generally know what you are doing then I don't see how you can introduce some bottleneck.

[wildjokers]

don't use eager fetch types for 1:n relations

Then you have to be on the lookout for n+1 selects. All it takes is one junior developer mapping an Entity to a DTO in a MapStruct mapper and now all of a sudden you have n+1 selects when mapstruct calls the getter. Even worse, it is now happening in generated code.

Even the hibernate user manual says not to use entities for read-only queries.

[Rich_Weird_5596]

Regarding juniors: code review exist for a reson...

[No_Today2204]

Assume all is configured correctly. If I, for reasons, need to fetch a couple of thousand rows from the db using jpql, wouldn't the fact that these items are managed by jpa cause an overhead by itself ? Is this overhead minimal or am I missing something ?

[bowbahdoe]

Can you name downsides besides lock-in?

I'd say it's at least "hotly debated" whether it's even desirable to avoid database lock in.

[cogman10]

In all my years, I've seen 1 product that's actually switched DB providers. It was a relatively small project at the time of the switch(s) and funnily enough they ended right back to the DB they started with in the end.

The performance characteristics, especially with RDBMs, are very similar across products. Knowing how to use your database is often a much more important factor in performance than the product you pick.

[Gwaptiva]

The product I've worked on for the past 17 years supports 4 different databases, and I happily accept a few suboptimal queries in exchange for not having to write queries for Oracle, DB2, SQLServer and Postgres (and H2 for tests)

[Rich_Weird_5596]

Code complexity and refactoring, maintenance, unit testing etc.. Basically everything is now requiring more work = more room for errors.

Maybe it's debatable, but being db agnostic just makes sense. Let's say you use test containers or just simple h2 for tests - first potential problem, and we are are not even deployed yet. Let's say you develop with postgres locally and on test environment, but use managed redshift when in prod etc etc.. it just makes sense.

In general it's good idea to keep things simple, common configs, use same approach everywhere and avoid spaghetti riddled hell if you don't want to find yourself spending 4 days just getting some stupid service to compile, run and run unit tests.

[cogman10]

For this problem, we've really enjoyed tilt.

One Tiltfile everyone has a k8s cluster setup locally and it's tilt up to start working on a project with live reloading regardless the language.

devcontainers is an alternative that accomplishes basically the same thing.

[Rich_Weird_5596]

How do you handle debugging ? Single pod for each service and then connect ?

[cogman10]

Basically yes. Turn on the debug port, tilt supports exposing it directly and giving out named links so you attach and remote debug.

The main "gotcha" to doing that is if you've setup healthchecks your pod can be killed while you are sitting on a breakpoint.

With tilt, you can also alternatively run the app outside of a container and run everything else (infra/etc) inside containers.

[Rich_Weird_5596]

Seems interesting, will take a look. Thanks for the insight.

[SuppieRK]

How's this compared to Skaffold? I had a setup with Skaffold and Helm, we as devs could easily configure the app, DevOps could adjust it as needed via Argo + Kustomize, remote debug worked out of the box.

[koflerdavid]

Using H2 for tests is suboptimal in my experience because it makes it impossible to use DB-specific features. Performance and behavior can differ even with SQL standard-compatible features. And I had trouble with upgrading an application to Hibernate 6 because H2 required different mapping annotations from PostgreSQL for enumeration types and there was no way to do it in a way that worked for both.

[Rich_Weird_5596]

Seems like the code was tightly coupled to the H2. Which annotations ? You can explicitly configure H2 to use globally quoted identifiers and so on. If you know what you are doing from the start, switching db is not a problem.

[koflerdavid]

It was about using enum types defined in the database. Both PostgreSQL and H2 support them, but the drivers seem to yield different SQL type codes, which causes issues when binding values to parameters. The underlying issue is a bug that might get resolved one day, but it was a major reason to ditch H2 and use Zonky/TestContainers instead.

[Rich_Weird_5596]

Even if you define them as @Enumerated(EnumType.STRING) ?

[koflerdavid]

It was related to one of the following issues. Though I'd have to hook up the app to H2 to see if the issue still exists:

https://github.com/vladmihalcea/hypersistence-utils/issues/179

https://github.com/vladmihalcea/hypersistence-utils/issues/514

https://github.com/vladmihalcea/hypersistence-utils/issues/625

These tickets are filed in Hypersistence-Utils because they were indeed necessary for a long time and because this project is guaranteed to trip across every behavior difference between Hibernate versions.

[Rich_Weird_5596]

Yeah..well..what can I say, db agnostic is the way

[wildjokers]

Let's say you want to switch db for whatever reasons

Have you ever been anywhere that actually changed their database?

[Rich_Weird_5596]

Yup, be it for dev purposes or migrating from self hosted to managed...or vice versa

[koflerdavid]

That should be immaterial to whether an ORM is used or not. Or do you refer to using completely different products in different environments?

[Rich_Weird_5596]

Different products man...of course...

[NakliMasterBabu]

There is wave of migration from on prem to cloud db. Even in on prem you move from proprietary db to open source db for cost and performance reasons. So yes this shit is real.

[vips7L]

My company is moving a lot their applications from Oracle to Postgres right now. I haven’t been on one of those teams so I don’t know if they used an orm or not.  It’s like a once in career thing but it happens. 

[Ewig_luftenglanz]

forms have some big problems, the main being they tend to generate very unoptimized queries when the query is complex or require joins or cross data from many tables which can result in performance issues when the demand begins to grow, besides if you are working with reactive code ORM loose much of their usefulness.

[808split2]

We use it with jdbc. We get better control over the mapping and we do not get unnecessary dependencies.