[tdons]

So much this. ORMs? That way lies madness. For example if you use Hibernate in Java you can end up writing stuff like this:

  interface MyRepository {
    Page<SomeObject> findEntitiesByIdAndCreatedAtAfterOrderByCreatedAtDesc(int id, Instant after, Pageable page);
  }

This abstracts away executing a query along the lines of: select * from entities where id = $1 and created_at >= $2 order by created_at desc

I'll take the latter any day.

[watt]

Pageable also adds window functions (limit, offset). If you are going to compare, please be courteous and do it right.

When app starts up, Java compiles these declarations and will abort if the declared function actually does not match the entities. This is a huge safety net, which you would not have when you write SQL. And also when you write SQL you have to take care and unpack the "select *" into your fields etc etc. I suspect this is much more important downside than you might realize.

[vitro]

How do you get results of your query to SomeObject then?

I think it depends on the use case, as always. I use Hibernate for our platform, which is used at most by 10-20 people at once. There ORM is a huge help because I can just easily view and edit my model and all its relations and I accept performance hit in this case and if something is too slow, I rewrite it in sql.

Then we have API that uses data of the platform, and there I dropdown to plain handcrafted sql with a little help of JDBI and so far it has been working great and responses are fast.

So I think it is just about stepping back a bit to think and not to blindly accept either side of the argument. Each use case is different.

[twic]

That's not Hibernate, that's Spring Data or some such, which is indeed cursed. This bit of Spring was absolutely designed by and for people with no critical thinking ability.

Meanwhile, JPA, the standard which Hibernate implements, has a textual query language [1] which is basically SQL but a little more objecty. For example, you can express joins through properties concisely:

  SELECT c.capital.name FROM Country AS c WHERE c.name = :name

And use constructor expressions [2] to map results into Java objects:

  SELECT NEW example.CountryAndCapital(c.name, c.capital.name)
  FROM Country AS c

[1] https://www.objectdb.com/java/jpa/query/jpql/structure

[2] https://www.objectdb.com/java/jpa/query/jpql/select#Result_C...

[tdons]

Thanks for the corrections, suggestions, and comments everyone!

@twic Thanks for the correction. Didn't know this wasn't a Hibernate but instead a Spring Data feature :-)

@watt You're right, I could've added an offset/limit. Wasn't the main point though (readability was).

@vitro Depends on the approach, but it won't be done /for you/, the mapping is a manual step (grab field X, etc.).

@piokoch, nice suggestion. Of course in the real world we work in teams and some people will prefer this ;-)

[piokoch]

And nothing is going to stop you from doing this, you can pack you query (native or JPA-QL or HQL) into @Query annotation and be done. The good thing is that it will do mapping for you.

The advantage of using findEntitiesByIdAndCreatedAtAfterOrderByCreatedAtDesc is that you will get for free paging support (which is not trivial and has to be done properly for each database engine, simple approach with using limit typically is very inefficient).

Also, most of the queries will be something simple to fetch some entity using some business identifier or primary key, not being forced to write down all those simple queries is a productivity boost.

For more complicated queries you can fallback to writing query manually.

There is also maturing Spring Data JDBC project that kind of has "best of both worlds".

[thesimon]

While the abstraction might be a bit longer, I've found them relatively easy to write using IDE support (e.g. IntelliJ).

Pageable adds of course a bit of functionalities, as does the convenience of passing an Instant instead of a String :)

[register]

That is Java. In .NET using linq with entity framework looks almost like SQL.

[nullsense]

Debatable. Method syntax vs query syntax is available. I mostly see method syntax in my codebase. I also see terribly performing linq queries all over the place.

I think it's neither good nor bad, it's just a tool. Tools are useful for making things, but if wielded improperly they will hurt you.

I'm sure same arguments can be made against raw SQL. It's possible to have good codebases in any paradigm, and bad codebases in any paradigm. It's really all down to the quality of implementation.

As a side note if you want a creative way to backup a database just use try logging an EntityFramework tracked object in Serilog with the destructuring operator. Watch as the logging framework inadvertently causes an entire object graph traversing navigation properties endlessly make infinite SQL queries while your web server slowly runs out of ram. Fun times!

Powerful tools are usually sharp.

[iamsb]

You can provide the query, hibernate/JPA does not stop you from doing that. Autogenerated queries for simple CRUD ops, but queries supplied by developer for more complex logic is ideal.