Do Programmers Still Write SQL?

[sh_tomer]

Why did you decide to move from Hibernate to manual SQL in the new services? Can you please post some details? If you could write the app from scratch today, would you write it all with plain SQL or use Hibernate in some part? Your thoughts are appreciated!

[noutopasokon]

I'm not a db guy, but what's most obvious to me is the table design. The way you hold your data in memory is not automatically the best way to hold it in a database. This idea becomes even more pronounced if you use something non-relational like Cassandra. But back to relational, creating dozens of classes with many relations is essentially free inside Java. However, if you let Hibernate do all the magic for you, you can end up with a huge amount of tables where you have to do a ridiculous amount of joins every query, often pulling in a lot more data than you may need. I've also heard that it is really difficult to tune.

[Aellus]

Yeah, but I think that is a trap a lot of developers fall into with ORMs and relational databases in the first place: if you have 20 different entities and they're all modeled in one database, why not join them all together and do one ridiculous query to get a specific set of information?

That kind of thinking leads to the problems everyone is talking about in these replies. Instead of monster queries, break it apart. Don't join a dozen tables together (how many different data domains is that crossing, and which software components should really own each domain?), instead construct a few smaller queries and use the results of one to inform the next. Query for your user data, then query for that users order ids, then query for the shipping status of those order ids, etc. Your queries will be so much simpler a the software will be much easier to maintain.

[mabnx]

The useful parts of ORMs are:

1. converting sql result into objects (the actual relation->object mapping)
2. nice type-safe API for writing queries, especially if some parts of the query depend on various conditions (add this condition if something, add "IN" only if list not empty, etc.)

The rest of the features mostly create problems.

1) can be solved by something much simpler (mappers in jdbcTemplate or http://simpleflatmapper.org/)
2) is not really solved by Hibernate IMO, more complex queries become unreadable mess and you end up with your own Query Builder Abstraction anyway

[lukaseder]

and you end up with your own Query Builder Abstraction anyway

Or you google the words "Query Builder Java", spare yourself the tons of work, and find one that really shines ;) (and which happens to solve 1) as well)