3 ms·

Yeah, to me SQL Mappers like MyBatis are usually the way to go. I don't want the power of SQL abstracted away from me in most cases. If I had a nickel for ever

Yeah, to me SQL Mappers like MyBatis are usually the way to go. I don't want the power of SQL abstracted away from me in most cases. If I had a nickel for every time I've seen code using an ORM framework fetching a set of ids and then iterating over that set to fetch a single record at a time. And then if I had another nickle for each blank stare I got when I explained that only one SQL query was needed to return the flattened result set of data and that the hierarchical object graph could be built in the app much faster than the N SQL queries they were making.

xamuel 8y ago
>fetching a set of ids and then iterating The ironic part? The folks who do that tend to be the same folks who swear the ORM will pay for itself by query-optimizing under-the-hood. 9_9
geophile 8y ago
Many years ago, I wrote a Java ORM that solved that exact problem. But I still have come around 180 degrees to the view that ORMs are terrible. Even aside from this N+1 problem, to use an ORM well, you have to understand the SQL that you want to be generated, and then instead of writing that SQL, you have the additional problem of having to know exactly what to say to the ORM to get that SQL generated. Also: you don't always want objects returned; sometimes partially instantiated objects are what you want; bulk updates are hard to get right; stored procedures break everything; and on and on. The thing that really started me on the path to my current view was a conversation with an app developer who was evaluating our project. Just from a career point of view, having Oracle on her resume was a lot more valuable than having OurProprietaryORM on her resume. That, combined with the realization that solving the N+1 problem was really a pretty minor thing, and that all the other problems were even harder to solve. The approach I have been using for a while is to use a very simple mapping layer that hides some of the ugliness of JDBC, but still lets me write SQL directly. I understand that MyBatis does something like this, although I haven't used it.
twic 8y ago
I really feel like i'm owed a considerable number of nickels for all the times i've seen posts and comments about ORMs which miss the fact that they have powerful query languages built in. JPQL is a genuinely pretty nice way of querying a database - it's basically SQL, plus object property syntax [1]. Here's a little example which concisely examines a many-to-many relationship: SELECT DISTINCT p FROM Player p WHERE p.teams IS NOT EMPTY Or navigation through a path (i made this example up, might not be quite right): SELECT t.name, t.league.sport FROM Team t WHERE t.city = 'Ul Qoma' If you're doing reporting where it doesn't make sense to pull out the entities, you can use a constructor expression [2] to materialise results as anything else you like (again, made up, might not be spot on): // package hn; public class CountSummary { public CountSummary(String name, int count) {} } SELECT NEW hn.CountSummary(l.name, COUNT(l.teams)) FROM League l Certainly, there are more sophisticated features of SQL that are useful for analytical queries that aren't in this query language. That's why every ORM has an escape hatch where you can write raw SQL, but still use its machinery for turning results into objects. [1] https://docs.oracle.com/javaee/6/tutorial/doc/bnbtl.html https://docs.oracle.com/javaee/6/tutorial/doc/bnbtl.html [2] https://docs.oracle.com/javaee/6/tutorial/doc/bnbuf.html#bnbwc https://docs.oracle.com/javaee/6/tutorial/doc/bnbuf.html#bnb...

xamuel 8y ago

>fetching a set of ids and then iterating The ironic part? The folks who do that tend to be the same folks who swear the ORM will pay for itself by query-optimizing under-the-hood. 9_9

geophile 8y ago

Many years ago, I wrote a Java ORM that solved that exact problem. But I still have come around 180 degrees to the view that ORMs are terrible. Even aside from this N+1 problem, to use an ORM well, you have to understand the SQL that you want to be generated, and then instead of writing that SQL, you have the additional problem of having to know exactly what to say to the ORM to get that SQL generated. Also: you don't always want objects returned; sometimes partially instantiated objects are what you want; bulk updates are hard to get right; stored procedures break everything; and on and on. The thing that really started me on the path to my current view was a conversation with an app developer who was evaluating our project. Just from a career point of view, having Oracle on her resume was a lot more valuable than having OurProprietaryORM on her resume. That, combined with the realization that solving the N+1 problem was really a pretty minor thing, and that all the other problems were even harder to solve. The approach I have been using for a while is to use a very simple mapping layer that hides some of the ugliness of JDBC, but still lets me write SQL directly. I understand that MyBatis does something like this, although I haven't used it.

twic 8y ago

I really feel like i'm owed a considerable number of nickels for all the times i've seen posts and comments about ORMs which miss the fact that they have powerful query languages built in. JPQL is a genuinely pretty nice way of querying a database - it's basically SQL, plus object property syntax [1]. Here's a little example which concisely examines a many-to-many relationship: SELECT DISTINCT p FROM Player p WHERE p.teams IS NOT EMPTY Or navigation through a path (i made this example up, might not be quite right): SELECT t.name, t.league.sport FROM Team t WHERE t.city = 'Ul Qoma' If you're doing reporting where it doesn't make sense to pull out the entities, you can use a constructor expression [2] to materialise results as anything else you like (again, made up, might not be spot on): // package hn; public class CountSummary { public CountSummary(String name, int count) {} } SELECT NEW hn.CountSummary(l.name, COUNT(l.teams)) FROM League l Certainly, there are more sophisticated features of SQL that are useful for analytical queries that aren't in this query language. That's why every ORM has an escape hatch where you can write raw SQL, but still use its machinery for turning results into objects. [1] https://docs.oracle.com/javaee/6/tutorial/doc/bnbtl.html https://docs.oracle.com/javaee/6/tutorial/doc/bnbtl.html [2] https://docs.oracle.com/javaee/6/tutorial/doc/bnbuf.html#bnbwc https://docs.oracle.com/javaee/6/tutorial/doc/bnbuf.html#bnb...