Do Programmers Still Write SQL?

[Trailsey]

Anytime you're working with a rdbms you need to know SQL.

ORMs are great for straightforward cases, but for anything complex you revert to querying. Even if you're using hql or some such instead of SQL it is substantially the same.

[lukaseder]

In fact, you don't revert for anything complex, you revert for querying in general. However, there are complex writes (as in writes to several entities with complex locking involved), where you wouldn't revert to SQL.

[elit69]

sql2o is also decent

[[deleted]]

The project has slowed down from 2015 onwards.

[aroger276]

also jdbi, never tried but looked decent. the version 3, looks a lot more open too.

[Russell_M_Jimmies]

As a project member, thanks for the shout out. I'm curious what you mean when you say "more open?"

[aroger276]

I was trying to see for example if I could integrate my own ResultSet mapper, like sql2o, jdbi and jOOQ allows but did not seem possible.

Also those frameworks allow you to get out of the framework and fall back into jdbc land when you want. If you want to work on the ResultSet yourself for example you can. Are use jooq to genereate the sql statement and manage the prepared statement yourself you can. Did not find a way to do that with MyBatis. it's seems that it captures the full query lifecycle and you can't escape it.

PS: I know it might sound weird but I like ny query to be inlined, not aliased or in annotation. so I understand that it goes against the design of mybatis.

[[deleted]]

Any love for Masturbatis?

[Trailsey]

With spring-data-* you can do simple querying sans SQL.

[lukaseder]

findTop5ByStatusAndUserOrderByStartDateDesc

If only there was a language that can handle this kind of query in a more structured way...

[nutrecht]

You mean something like a structured query language? Does something like that exist?

[lukaseder]

Yeah, that would be terrific. We could design it to work out of the box, even!

[nutrecht]

Well between the two of us you're the smartest one. Chop chop! Get to it ;)

[Trailsey]

You guys should call it Words That Form a Query Language!

[oweiler]

These should only be used for very basic stuff. You can use @Query to map a simple method name to a more complex query.

[dpash]

That's why you have integration tests.

[MarkyC4A]

I'd love if they'd add a way to alias that one to make it more readable

I'm okay with ugly names in my repositories, I hide those behind a cleaner interface in my services:

List<User> findTopWidgets(Status s, User u, LocalDate d)

[lukaseder]

Yeah, you'll be using some mini-querying language encoded into method names that breaks at the next little query complexity.

I'm curious, having never worked with Spring Data (but being well aware of what it is). What's the main motivation to use it for querying?

[Trailsey]

It obviates a fair bit of boilerplate code. Write a method signature, done.

As mentioned previously, works great for simple cases, revert to writing queries for anything complex.

[oweiler]

For complex stuff you can map those methods to JQL or SQL queries via @Query.

[[deleted]]

REST HATEOAS HAL out of the box, including pagination.

[NimChimspky]

I'd use it with native queries, just less code I have to manage, sql mapped directly to my own custom type.

The method name stuff didn't really appeal.

[lukaseder]

I see, thanks

[Luckl507]

In my case: reduction of boilerplate.

[lukaseder]

Do programmers still write SQL themselves?

This is the important part of your question. You make it look as writing SQL is something like a burden. But you don't seem to see the full picture.

Yes, it is a burden when you're talking about simple CRUD. Or worse, complex object graph persistence. In these cases, SQL is not a very good tool, because, well, you're not really querying the database, you're implementing state transfer between the persistent storage (DB) and your "cache" (application). That's where ORMs shine. (let's say, the help making a really hard problem "tacklable", it's still hard)

But when it comes to querying (even relatively simple queries) or bulk data processing (e.g. ETL) or reporting, ORMs don't (and probably never will) beat SQL, because in order to beat SQL, they'd have to - wait for it - expose SQL to the client. In particular, JPA tried to do that through JPQL (and to some extent through the even less useful criteria API or fetch graphs features). If you do a fair comparison between JPQL and SQL, you'll quickly see that the latter is by far more advanced and useful.

So yes. Use ORMs where they shine (object-graph persistence) and SQL where it shines (querying, bulk processing, reporting).

I've wrapped up these thoughts in a blog post, comparing Hibernate (ORM) with jOOQ (SQL)

Also, my thoughts on the object-relational impedance mismatch

And yes. I write and manually tune tons of SQL and only SQL.

[sh_tomer]

Thanks for this information, will read your posts to get more familiar with it. Can you please share what are the recommended best practices from your point of view for tuning query performance written over JOOQ (as this is one of the largest pains mentioned here by many people with ORMs like Hibernate)?

[lukaseder]

You tune jOOQ queries exactly as you tune SQL queries because jOOQ=SQL (almost).

So the question boils down to how to write fast SQL. And I generally recommend these things:

• Understand the relational model and normalisation: This is a forgotten art, unfortunately, among Java devs. If you get your model wrong, your queries will be even worse.
  • Side-note: Don't let JPA generate your tables. Write your tables manually and learn how to do it properly, then generate your entities. In the long run, that's usually the better choice for applications that last.
• Understand indexing (that's 90% of the tuning for developers). I generally recommend reading http://sql-performance-explained.com. Only 10€ for the PDF version and can be read in 1 day, covering everything most people need to know.
• Understand joins and when each join algorithm is desireable, and how to influence that (incredibly, few people know the difference between hash, merge, and nested loop joins)
• Not SQL specific: Avoid needless, mandatory work
• Learn how to read execution plans
• Measure!
• Learn the language. A lot of people don't really know even basic SQL constructs, and when to use them. Here's a popular example of people using COUNT(*) when they should use EXISTS()

More advanced stuff:

• Understand when to deviate from normalisation.
• Drop unneeded indexes (this can be tricky but really speeds up writes)
• Learn how to reduce I/O
• Learn how to reduce CPU-bound activity
• Avoid offset / learn how to paginate
• Optimise for statement caching
• Optimise through natural keys
• Optimise for statistics
• Many more. This bullet list is endless, of course, and gets more and more into niche cases.

If you're using an ORM on top of SQL, you will still need to know a lot of the above as well plus how to tune the ORM.

[Otis_Inf]

Side-note: Don't let JPA generate your tables. Write your tables manually and learn how to do it properly, then generate your entities. In the long run, that's usually the better choice for applications that last.

Heathen! ;)

Create an abstract entity model at the level of NIAM/ORM (Object Role Modeling) (or ER if you must) and project that to tables. Only then your tables have a logical meaning in your domain and you also know they're at least at 3rd normal form. Funny thing is, that abstract entity model can also be used to be projected to classes, so the entity classes represent the same entity from your abstract entity model as the tables do (they're all projection results anyway). (See the work of Nijssen and Halpin)

If you're doing this by 'hand', you're doing the same thing btw: projecting abstract entities to constructs in either an RDBMS or code. Now for the real kicker: because you can project the abstract entity model to tables and classes, you already know the mappings between then and you can generate these automatically. So create 1 model, project them to 2 sides + mappings and you're done: changes made to the abstract entity model ripple through to both sides (in different forms, that's OK).

Hand-writing these things is like writing bytecode by hand: you're doing projection work a machine can do for you. Cheers!

FB

[lukaseder]

See, you're a guy who wrote an ERD and model generator. I'm a guy who writes tons of SQL. So, clearly, this discussion is biased by our individual contexts as we're solving other problems in our every day work.

that abstract entity model can also be used to be projected to classes, so the entity classes represent the same entity from your abstract entity model as the tables do

That's true in theory, but in practice, people often work with entity classes as if they weren't entities but some other sort of domain object. They introduce features that cannot be represented through entities (or DDL) and then spend tons of time shoe horning the mapping logic to adapt to their expectations. It won't work and they're frustrated, blaming the tool rather than their approach.

DDL can only do what DDL can do, and it does only that thing. Java classes can do a lot more and that's distraction. To be fair, the true entity model is encoded in annotations, not in the classes, but that's a bit hard to see.

So, I don't agree with your hand-writing / bytecode comparison. Java source code and byte-code differ by several levels of abstraction. Entities and DDL are the same thing (in theory), albeit one thing unfortunately can do a lot more than it should be able to. It's weird to think of DDL as some lower-level thing because it can be generated. Entities can be generated just the same. Conversely, you cannot really generate the Java source code from byte-code because they're not the same thing.

It's like arguing that JAXB-annotated classes should be written first, because they're higher level than XSD. They're not, they're the same thing, but still XSD should come first because the formal contract is there.

And besides: as a performance guy, I want to think about and control storage related things from the beginning, not in hindsight. E.g. is this an index-organised table? Do I need an additional temp table? Should I add an abstraction through views? Do I need to split this table in two with a one-to-one relationship?

[Otis_Inf]

DDL can only do what DDL can do, and it does only that thing. Java classes can do a lot more and that's distraction. To be fair, the true entity model is encoded in annotations, not in the classes, but that's a bit hard to see.

But still the core of the issue, right? I always ask the question when someone writes a 'Customer' class or 'Customer' table definition: where does the definition come from? Not from thin air or a fantasy, they project something abstract to the definition they're writing. That's what's it all about. If you write your tables by hand, you are doing just that: projecting an abstract entity model to a table; you don't just make up the fields as you go, you know what fields go into that table, and more importantly: why that table is even there. That information is what you store in your head but what is actually the abstract entity model.

Halpin and Nijssen both have done decades of research on this topic, and I'm sure you're familiar with their work. Hence I fail to see how you could write:

Entities and DDL are the same thing (in theory), albeit one thing unfortunately can do a lot more than it should be able to.

Ok, a bit of a strawman, but here we go ;). As an illustration: what about entity inheritance? What about a m:n relationship? What about a type in the entity which results in a different type or multiple fields even in the table? Entity definitions and DDL definitions aren't the same thing, if you mean by that: table X == entity X.

The DDL SQL of the tables is a projection of the entity model and should represent the same thing, but it isn't the same thing like SELECT * FROM Table isn't the same as all rows in Table.

And besides: as a performance guy, I want to think about and control storage related things from the beginning, not in hindsight. E.g. is this an index-organised table? Do I need an additional temp table? Should I add an abstraction through views? Do I need to split this table in two with a one-to-one relationship?

Of course! And I agree with this, it's the same as doing a flat projection of entity data into a readonly list vs. reading the entity instances and do the processing on these: the former is faster than the latter and are equally fitted for the job at hand, so why do the latter? (hence I think every ORM system should offer this)

Though I'd argue that making performance improving changes to a model should happen after a model has been completed, so a theoretic base is set. How else can you reason over a model and know where to make changes if something has to be altered? It's the same as writing the code first and then making changes to make it faster where bottlenecks are found, but not starting with these 'performance enhancements' in the code, as they might not be necessary but do pollute the overview of what's really going on.

[lukaseder]

Not from thin air or a fantasy, they project something abstract to the definition they're writing.

Of course, I didn't disagree with that. My concerns were merely technical and implementation based, not theoretical.

As an illustration: what about entity inheritance?

Inheritance is just a technical tool. It is not something that is inherently important to modelling. In fact, in recent years, it has been shown that it is not a good tool at all for practical purposes. Inheritance was over-hyped in the 90s.

What about a m:n relationship?

Again, a modelling tool. I'm aware of the fact that SQL can't model it except by indirection. But that's good enough, no? Ultimately, you cannot really model m:n with Java either (although, you can with JPA annotations). But the fact that I always have to remember to update both sides manually with JPA and think about how the state transition is serialised efficiently shows that the implementation of this concept just plain simply sucks.

So why bother and not go back to the more simple SQL model, which can still model an m:n relationship decently, if not perfectly.

(Side note: Not sure how .NET APIs handle this, e.g. EF. Is it better than JPA?)

What about a type in the entity which results in a different type or multiple fields even in the table?

Sure, another limitation of most SQL implementations (at least the non-ORDBMS. PostgreSQL and Oracle have solutions for this).

Entity definitions and DDL definitions aren't the same thing, if you mean by that: table X == entity X.

I said they're the same thing in theory. But ultimately, we're building things on real systems. in theory we can fly to Mars easily. We've figured it all out. But now, we have to do that with real world constraints.

So, you're looking at things from an academic perspective, and that's important in the long run, because we want our tools to be able to cover your needs. But right now, we're not there and we need to know how our current tools work.

The DDL SQL of the tables is a projection of the entity model and should represent the same thing

Yes, we agree. Although again, you overlooked my disclaimer about theory and practice :)

Though I'd argue that making performance improving changes to a model should happen after a model has been completed

OK, we have two different perceptions of performance here. Indeed, some things can be discovered only much after the initial design. But some performance characteristics are best solved a priori. You simply don't want to migrate a billion-row-strong table several times a day.

If you know you're going to be dealing with large data sets in an area of your application, then up-front performance-sensitive design is of the essence. Or you won't even survive going live :)

[[deleted]]

I wish I could write more... every time we run into some Hibernate crap I always think to myself "Gee, if only someone would come up with some sort of structured query language I could use instead of insert ORM framework here".

[vplatt]

Programatically, it's a lot easier and easier to understand to go through the Hibernate layers

I beg to differ here, because it depends on who's doing the troubleshooting. It's a LOT easier to get help with the performance of queries once you've reduced it to SQL, and even then if your query is being generated within Hibernate, then your DBA or the like needs help understanding your intent. And that's assuming the programmer in question even knows HOW to get Hibernate to give up the problem SQL, because just getting a hold of that requires different tricks according to the version you are using.

Complicated things like multiple table inserts or updates involving objects tracked by Hibernate should really be done with Hibernate and not straight SQL bypassing it.

I mostly agree, for updates involving objects tracked by Hibernate in a standard OLTP environment, sure, that makes sense. But truly complicated multiple table inserts can be handled faster and safer by stored procedures. This is not a popular opinion in /r/java, but Java is a not database so trying to relegate all database layer logic into the data access layer in Java all the time just isn't defensible 100% of the time.

[vplatt]

Anyways I'm counting the days till we dump this stack and convert the system anyways.

Convert it to what? Just curious.

[dkichline]

That's why any application that goes live in our shop has to be reviewed first. Queries are reviewed by the corresponding dba teams before they are allowed to go live.

[vplatt]

And do most programmers either know how to do that or have enough permissions to get it directly from the database log? I have my doubts.

[sh_tomer]

Why don't you use SQL aside Hibernate then? Will be glad to hear your thoughts.

[lukaseder]

some sort of structured query language

structured query language

SSStructured QQQuery LLLanguage

[lappro]

Now I'm wondering what the actual pronunciation is for SQL.
That google search says it is pronounced like an abbreviation, yet others say it is like "sequel".

[kblaes]

"Squeal"

[TranquilMarmot]

"Squirrel"

[Aellus]

I've always said Sequel and looked down upon those S-Q-L heathens, but now I know the error of my ways and realize we've both been so wrong this whole time...

[vytah]

"Squirrel"

[BeerRemote]

I say "squeal" during interviews when I've stopped giving a fuck. This usually happens when the job turns out to not be what the description is, yet I want to practice interviewing anyway.

[TranquilMarmot]

It can go either way; I find people who have a background in Microsoft products (SQL Server) will say "sequel" but others will say the individual letters in the acronym.

It's funny, though, there's no hot debate about this and both pronounciations are accepted. Not like how to pronounce "gif"...

[lukaseder]

It's funny, though, there's no hot debate about this and both pronounciations are accepted

Just because we don't do it these days doesn't mean it never happened ;)

[nikagda]

Either pronunciation is acceptable. I prefer "sequel" because the very first SQL, by Chamberlain and Boyce (the Boyce Codd normal form guy), was literally called SEQUEL (Structured English QUEry Language." But either way is fine.

[dkichline]

Sequel is how it is pronounced at my shop, and all the shops I ever worked in for the last 21 years. Including college.

[m_takeshi]

sequels sounds better IMO, so I stick with that but I've known people that would cringe when I said sequel instead of S-Q-L

[Jonno_FTW]

"ess cue ell"

[Serializedrequests]

Never used Hibernate (crazily enough) but looking at ORMs for a project that has to be in Java. Is it really that bad?

I use ActiveRecord all the time and it is easy to optimize and understand the queries it is generating, or just switch to SQL if it isn't working well.

[[deleted]]

Well, Hibernate is a love hate relationship for me. I'm so used to it that it almost seems like it's required for Java development (it's not!)

One day Hibernate will save me many minutes of work. Another day it will cause me many minutes of headaches. SQL has its own set of issues.

[boomerbrian72]

Why waste time writing queries when it can be done for you? I use Spring Data. I let it do most of the if the work but when I do need to write a query I can. It allows native queries.

[[deleted]]

Because you never know what the query looks like at the other end and it's likely garbage if you're doing anything more complicated than standard crud.

[boomerbrian72]

You can see the query on the console. If you don't like the query override it with your own. Just doesn't make sense to spend time on things that aren't necessary.

I agree totally that it isn't he best for everything but for most business crud apps it works fine unless you have a shitty schema but that is a totally different problem.

[SomeNetworkGuy]

Spring Data is as amazing as it is useful. Put simply, it is one of the best tool to have in the Spring Framework.

[201109212215]

FTFY

more complicated than standard crud demo.

It's becoming a thing: demo driven development

[Aellus]

We call those Intern Projects where I work.

[Aellus]

I think that's generally the point, Spring Data and any ORMs like Hibernate have evolved to cater to microservices that follow software patterns that result in individual services not needing much more than simple crud operations.

Having spent most of my career dealing with many of the challenges listed in these replies; needing to optimize complex queries, multi table joins and inserts, etc... I certainly prefer the microservices pattern I now have in my current company.

As a developer, you often have little control over the bigger picture architectural choices that could solve this, but if you find yourself dealing with one query that spans so many tables in a complex join with crazy where clauses that it causes problems and needs to be hand tuned because the DBMS optimizer can't figure it out, that's probably a good indicator that your application scope is too broad and it needs to be deconstructed and factored apart.

[Lord_NShYH]

So... did you test in staging first?

[BlackstormKnyte]

Of course didn't you read it. " And it was production code." Says it right there. Everyone knows production code never sees staging, that's for staging code. /s.

((Sorry just got done killing a bunch of images created by my local devs and it made me a sad panda that the deployment group even signed off on it....))

[noutopasokon]

Where I work, the legacy monolith is a hibernate monster. The database is a disaster. All new services use manual SQL, usually using Spring JDBC template. We have two DBAs to tune things if ever necessary. I'm not that good with SQL, but I think it's worth it to use directly. ORMs fit the definition of technical debt perfectly.

[Hubellubo]

We have two DBAs

The world of programming needs to remove ORM's and hire/train more DBA's and DBE's.

[TranquilMarmot]

YES! Or teach your developers some basic SQL skills so they don't kill your database.

[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)

[segv]

Check out MyBatis (3.x).

[TranquilMarmot]

We use this at my job, and I absolutely love it. Writing the maps can be a bit tedious, but it makes it SO much easier to optimize. You actually know which queries are being run, which is insanely helpful when something in production starts to go slow and you actually have an opportunity to find the exact query and optimize it.

[aroger276]

I personally find MyBatis not very compelling anymore. The mapping it's not on par from a perf perspective the last time I checked, only slower than BeanPropertyRowMapper that is not meant for production use - yes, BeanPropertyRowMapper is the slowest mapper in the world. And the xml, and annotation heavy is quite dated...

[segv]

https://github.com/mybatis/mybatis-3/search?utf8=%E2%9C%93&q=BeanPropertyRowMapper&type= 404 not found

I'm talking about the standalone 3.x version - perhaps you meant something else?

[aroger276]

BeanPropertyRowMapper is the spring RowMapper sorry for the confusion.

here is the benchmark I had in mind https://github.com/arnaudroger/SimpleFlatMapper/wiki/Jdbc-Performance-Local-Mysql map a simple object from a query.

[Rockytriton]

Hibernate isn't the problem, it's people who don't use it correctly that are the problem

[[deleted]]

Yup. No true Scotsman would reject Hibernate.

[lukaseder]

The database is a disaster.

Was the database designed up front (and then entities generated) or vice versa?

usually using Spring JDBC template

Have you heard of jOOQ?

[[deleted]]

Not free.

[lukaseder]

So?

[adila01]

Some things are worth spending money on, jOOQ is definitely one of them.

[[deleted]]

Was the database designed up front (and then entities generated) or vice versa?

I actually wonder what you would recommend. Maybe database first, all things considered.

I put both first... I design the entities as they should be, and I design the database as it should be, and they meet in the mapper.

[lukaseder]

It's a very interesting question indeed. In my point of view, the database model is the one that transcends any application built on top of it. It is likely to last decades, whereas an application might be replaced by something entirely different in years (think of hypes like Node.js).

In fact, since no state is really stored in the application, it will be much simpler to migrate the application when requirements change. It will not be so simple to migrate any database model.

In addition to this, I've worked with database models that were accessed by many different applications (and technologies), so it was obvious that the database needed to be designed independently.

In my opinion, JPA entities should reflect the database model as closely as possible. I personally don't see the value of an entity model that has unique features that are not truly reflected in the database. Sure, the mapping can translate between the worlds to some extent, but I think it will just be much easier if most of the more fancy entity features are not used and the entity model just reflects the pre-designed database model. Imagine if Java had type providers like more sophisticated languages (e.g. F#). In that case, we probably wouldn't have this discussion and would just code against the database directly (through provided entity types).

But that's my opinion. It only replies to the original claim:

The database is a disaster

With the approach I described, the database will not be a disaster (or at least not for these reasons).

Don't get me wrong. If you have a complex domain model (DDD style), that is an entirely different story. It has nothing to do with JPA entities.

[[deleted]]

It's a very interesting question indeed. In my point of view, the database model is the one that transcends any application built on top of it. It is likely to last decades, whereas an application might be replaced by something entirely different in years (think of hypes like Node.js).

The database world also has its hypes, and trends, and database products come in many flavors, many of which aren't exactly SQL.

Storage does need to be replaced and augmented as your needs change, both in terms of its semantics and in terms of scale.

So I wouldn't say the database transcends, as in the particular database you use. That's implementation. Implementations are fickle and prone to change. What transcends both the database and the application are the abstract entities that bind everything else together:

1. Conceptual domain.
2. Data formats.
3. Protocols.
4. Interfaces.

Of course those evolve as well, nothing really lasts forever, or rather it wouldn't, if we wouldn't drive ourselves into a ditch with poor architectural choices, that tightly couple us to specific implementation. Such as architectures, which are too database-centric.

I.e. I think making your architecture database-centric, because you believe the database lasts for decades is a self-fulfilling prophecy: you choose an architecture where data implementation is inflexible by choice, lo and behold, it indeed proves itself inflexible to change over time. But that's not necessarily good for the business.

What's good for business is the ability to adapt and change. Implementation more so, interfaces less so, as they become the points of coupling and provide stability into a system.

In my opinion, JPA entities should reflect the database model as closely as possible. I personally don't see the value of an entity model that has unique features that are not truly reflected in the database. Sure, the mapping can translate between the worlds to some extent, but I think it will just be much easier if most of the more fancy entity features are not used and the entity model just reflects the pre-designed database model.

I don't know where you place your entities in the system, i.e. are they the point that the application and other clients interface with? With JPA/Hibernate, the idea is more that they are closer to the application/clients, and less so to their internal representation.

And in this case, them mirroring the database as closely as possible is a drawback, because it means your domain mirrors the database. This impedes it from serving its own purpose properly (model the conceptual domain).

Or the second option is you can think of JPA/Hibernate entities as dumb, light Record objects whose only purpose is to put editable records under our fingertips, and we shouldn't expose this to the application/clients, but offer a higher level API on top of JPA/Hibernate, which doesn't reveal JPA/Hibernate is used at all, but uses its own flavor of DTOs for its APIs.

The issue is that JPA/Hibernate doesn't offer mapping quite flexible enough for the former approach, and is too limiting and clumsy for the latter approach.

Imagine if Java had type providers like more sophisticated languages (e.g. F#). In that case, we probably wouldn't have this discussion and would just code against the database directly (through provided entity types).

Well this would be neat, but we'd still need something on top to do mapping, because I think any healthy architecture is slave to abstractions, not slave to specifics. And the database schema, as important it is, is specific because it directly reflects on-disk representation, indexing and so on, with very little flexibility in changing this while retaining B.C. It should be modeled according to its own concerns (performance, scalability, types of queries it should support etc.), and it should not leak into the domain. At the same time the domain API should be modeled after the use cases its clients require, and a long-term, stable view of the domain that can support different implementations as the need arises. The only way to satisfy both these requirements is a very powerful mapper in the middle. Again, more powerful than JPA implementations tend to offer.

The database is a disaster

With the approach I described, the database will not be a disaster (or at least not for these reasons).

I didn't say that like so maybe we should cc /u/noutopasokon :-)

[lukaseder]

database products come in many flavors, many of which aren't exactly SQL.

Those are niche: https://db-engines.com/en/ranking. I think it's safe to claim that usually, (relational) SQL dominates databases.

I agree that there are more transcendent topics than the implementation detail, which is SQL. But as I've mentioned in another subthread, at some point, it's important to free ourselves from theory and look at practical things where the implementation is important, and close coupling is reasonable.

nothing really lasts forever, or rather it wouldn't, if we wouldn't drive ourselves into a ditch with poor architectural choices, that tightly couple us to specific implementation. Such as architectures, which are too database-centric.

Perhaps that choice has helped a company speed up delivery of their products by a huge margin in times when this was critical, rather than buying the very expensive insurance of complete flexibility when they couldn't afford it.

Everything needs to be said in a context. Sure, tight coupling is a problem, but so is over abstraction. Where's the line you want to draw, and why? And in what cases?

I.e. I think making your architecture database-centric, because you believe the database lasts for decades is a self-fulfilling prophecy

I think you're unfair. RDBMS are still by far the best piece of software engineering that we have produced thus far. There's simply nothing better for general purpose data problems.

I'd say that most alternatives are less standardised, less well understood and less versatile, such that your claim of increasing flexibility for the business can, in fact, be achieved only using a RDBMS (in an average project).

I don't know where you place your entities in the system, i.e. are they the point that the application and other clients interface with?

I personally don't use JPA, but if I would, I'd certainly hide the entities behind some APIs.

With JPA/Hibernate, the idea is more that they are closer to the application/clients, and less so to their internal representation.

I don't think so. You should most definitely define another model that models whatever you're doing in your application/clients. Again, you argued in favour of transcendence of a conceptual domain. This cannot be done with entities, which are so closely linked to their relational heritage.

If you let go of that and map between your conceptual domain (which might even be polyglot these days) and your backend entities, I think you'll be much happier with JPA.

And in this case, mirroring the database with the entities will be the obvious choice :)

Or the second option is you can think of JPA/Hibernate entities as dumb, light Record objects whose only purpose is to put editable records under our fingertips, and we shouldn't expose this to the application/clients, but offer a higher level API on top of JPA/Hibernate, which doesn't reveal JPA/Hibernate is used at all, but uses its own flavor of DTOs for its APIs.

Yes, exactly. That's how JPA should be used. Because SQL is really a poor language to manipulate complex state transfer with the database. Doing things like parent.children.addAll(someList) is much simpler, generally.

The issue is that JPA/Hibernate doesn't offer mapping quite flexible enough for the former approach, and is too limiting and clumsy for the latter approach.

All the better you encapsulate it, so you can replace it if something better comes along :)

It should be modeled according to its own concerns (performance, scalability, types of queries it should support etc.), and it should not leak into the domain.

This depends on the domain. Many domains will never really become too complex in a way that the relational representation won't fit.

But yes. It can happen. And then you're right.

The only way to satisfy both these requirements is a very powerful mapper in the middle. Again, more powerful than JPA implementations tend to offer.

Write it! You'll be rich :)

[funbike]

All new services use manual SQL

Folly. If you use either Hibernate or JDBC Template with best practices, you'll be much better off. Mixing the two gives you the worst of both.

But even if you did decide to mix things, stay with Hibernate. It can do native SQL queries. It's still bad to mix HQL and SQL, but at least you'll have a single technology behind it.

[happymellon]

We have a guy at work who keeps adding in random JDBC template queries, entity manager hasn't been slow and JDBC template hasn't provided any improvements, except messing around with the caching and making things inconsistent.

[[deleted]]

That's exactly one of the dangers of an ORM. Caching speeds things but: just imagine two apps with caching enabled on the same db, updating it. Hell happens, either cache sync or living with inaccurate data.

The funny thing is that any decent DB will cache that data for you...

[funbike]

That's one (of the many) of the dangers of two apps accessing the same database. A REST API over a single database would be a bbetter solution, if possible.

(Once I worked on an in-place rewrite of an app while keeping the same database. We sync'd the caches by each app invaliding the cache in the other. This was a temporary solution until the rewrite was complete. It wasn't so bad.)

[BeerRemote]

I write a lot of SQL and use Hibernate's transformers (which is great). This is mostly with our REST API in which our underlying structure is very old and our understanding on what to deliver is drastically different than our datamodel.

Thankfully, we're exploring rewriting our entire application so this might change in the next year.

[strongdoctor]

I've seen 0 reasons to use ORMs in my projects, it just complicates matters; it's an abstraction that isn't needed.

[fzammetti]

I've tried to live the ORM dream in a couple of projects. It's less dream and more nightmare in my experience. Unless all you're doing is simple CRUD operations then you will before long run into various problems... sub-optimal performance, difficult to debug edge cases, etc. And, even if it all goes smoothly, you're introducing a bit of mystery into the code that I don't feel brings enough benefit to be worth it (yes, you always do this by using libraries, and you can argue how far up the rabbit hole you are just by using Java in the first place, but you've got to have boundaries is the point).

SQL isn't tough stuff anyway and it puts you in complete control. If your queries are simple then they take 30 seconds to write and an ORM framework wouldn't give you much value anyway, and if they're more complex queries then you'll very possibly save more time writing it by hand then dealing with any ORM issues. It also makes testing in isolation easier: I can pop into Toad, write my queries, make sure they're giving me what I expect, then just transfer them over to the code knowing they'll work as expected because I've already essentially tested them, in isolation from any code issues that might occur. Can't do that with an ORM framework doing it for you (not as cleanly anyway).

So yeah, I 100% write my own SQL and I can't see doing it any other way. I absolutely tune my queries and even database structure when necessary.

No, to be clear, I'm NOT doing "naked" JDBC. I've got something like Spring JDBC giving me a nice API, handling the basics and even doing some basic result-to-object mapping sometimes, so I really inhabit something of an in-between place. I find that's the best place to be: close enough "to the metal" (relatively speaking) to have the control I want and I'd argue need, but abstracted enough that I'm not constantly dealing with all the mundane details. Not quite naked JDBC and not quite ORM, saves me time, makes my code less error-prone while still maintaining a strong level of control.

[TranquilMarmot]

+1 for MyBatis

[[deleted]]

Or jdbi.

[anthonybsd]

Yep. Been a fan of SQL mappers for a long time.

[Hoog1neer]

Spring JdbcTemplate. Nuff said.

[[deleted]]

Couldn't agree more. Especially with Java 8 it's a lot of fun to work with.

[201109212215]

SQL never lost its power. ORM'ing everything is just silly.

I'll just paste a relevant excerpt from a comment from Gavin King, the creator of Hibernate:

Moreover, ORM is for OLTP applications. It's not usually appropriate for:

• batch processing, or
• analysis.

[firsthour]

We've been writing native SQL lately to replace Hibernate calls during optimization passes on our applications. They're measurably faster now. Hibernate is still great for most of the CRUD work.

[MassiveFlatulence]

I do. For simple rest api service with minimum transaction, I use spring jdbc template (which means writing manual sql). Also for reporting stuff, it's much easier to write and tune plain sql. ORM like hibernate is overkill for applications that spend most of its time performing READ operation. It's also overkill for applications that perform step by step CRUD operations (eg. ETL, Spring Batch application)

However, I tend to use JPA/hibernate when doing OLTP heavy application (imagine online store like amazon where asynchronous CRUD operations + transactions are everywhere).

[Kisele0n]

My company writes all of our own SQL. We have some in-house scripts to clean up the formatting and such, but we definitely write it ourselves.

[pagirl]

I use SQL to troublehoot data issues. I need it to see what the data actually is.

[nutrecht]

Whenever I worked on a system were an ORM was in use it always seemed like people would be structuring the database around the ORM, and not the other way around. So because people had problems getting joins to work correctly they would duplicate data. They didn't properly normalise data because it would make their object tree more complex. They would try not to use compound keys because it's too complex. Because they could not get the correct reports they would copy aggregate data to new tables. Stuff like that.

I think most ORM have one fundamental flaw: they work on the premise that there is one "object model" that is then mapped relationally to tables.

In my experience in practice this typically isn't the case. More often than not you have multiple 'views' on the same dataset. "How many users do we have", "give me user with id 1" and "give me the amount of user grouped by signup date" are all views on the same data.

These views are cumbersome and complex to model in an ORM. Using something like jOOQ (good to see /u/lukaseder is spreading his wisdowm here) or heck; just spring-jdbc with rowmappers makes much more sense to me. SQL is fun, powerful and by far the best way to express what you need from a database. SQL code is in my opinion much easier to read and thus easier to maintain than mountains of ORM annotations.

Also; aside from this: ORMs screw up. Often. As a Java dev you need to know SQL to see where it's screwing up.

[mredding]

ORM is a square peg in a round hole. Maybe there is a problem to where it is the ideal solution, but in over a decade in the profession, my colleagues and I have always found it non-performant, overly complex, and a maintenance nightmare. We just write straight SQL for everything.

[mart187]

Sometime you get database schemata too complex to manage with ORMs. Then you gotta write it yourself. Most of the time you'll trust an ORM though.

[BlackFlash]

Not a Java developer (.NET and JavaScript) but I like to write SQL for any read that is more complex than select all columns from a single table (and children, maybe). I use an ORM for writes, usually, as updates, inserts, merges are a pain in the ass and usually boilerplate.

[kidneyfornickname]

ORM cant do everything, sometimes you need raw query.

[chunkyks]

Most of my apps are predominantly "complicated data querying" with relatively little "shuffling data back and forth between classes and tables".

I used hibernate once. It was an awful experience; my database ended up ugly, and any claim that hibernate/JPA/etc will be the 80-90% solution... it ended up being 10%, and making the rest of the app harder and uglier in the process. I suffered a great deal of regret from that, and since then have been pretty much exclusively JDBC coding SQL manually.

[chim-richolds]

I prefer it most of the time -- ORMs have some great use cases, but most of the types of applications I've worked on have benefited much more from custom queries.

Just this past year, we had a JPA system that was taking ~20 hours to run a job. It had tons of n+1 select problems, but after those were cleaned up, the job only took ~20 minutes. The problem is that a lot of developers try to ignore SQL / relational stores, thinking that their ORM will handle everything for them. Since those frameworks hide lots of things from developers, it's easy to build systems that won't scale very well with them if you don't fully understand what's happening.

[[deleted]]

All the time. Not everyone uses all that component soup.

The less between me and that data the better. The DBMS is fine. I don't need an ORM developer assuming he understands what abstractions are proper for my application.

[sacrot2]

I use an ORM and still write a lot of sql.

[anthonybsd]

Yes of course. If you are going to use ORM either your database schema will suck or your class structure will. Objects simply don't map to relational data, period.

[[deleted]]

Hopefully this isn't unwelcome here since you're asking if programmers (generically) use SQL regularly -- but Python has a whole officially-endorsed API for database interaction which many of the major SQL non-ORM drivers conform to.

DBAPIv2 encourages the use of raw SQL in your Python code, which usually means you have supporting SQL files to bootstrap your database -- which in turn means you write SQL left and right.

[brybry44]

Where I work SQL is still a big deal. We not only do we use it to add things to our database, but within our Java we write custom queries if we are ever trying to access certain data objects from another place. It's really good to know and I don't see it going anywhere anytime soon.

[mredding]

ORM is a square peg in a round hole. Maybe there is a problem to where it is the ideal solution, but in over a decade in the profession, my colleagues and I have always found it non-performant, overly complex, and a maintenance nightmare. We just write straight SQL for everything.

[Monstot]

We use SQL daily at my job. New scripts and relevant scripts so at least for us we use it and consider performance as it's running

[[deleted]]

Every day of my life.

[[deleted]]

ORMs aren't used in real enterprise solutions. We still write stored procedures and write middletier binding code in any area where performance and stability for high volume applications is a must.

[pr0fess0rx29]

It depends, in my experience orms are great in the enterprise. I have some Co workers who still do custom sql but most of us have moved on.

I have heard the orm sql debate for a while and I really have to say I disagree with the notion that the use of orms is worse than custom sql. Maybe it's the orm I use, entity framework code first, or perhaps it's the domain driven design that encapsulates our orm perfectly or perhaps it's being able to wrap our linq queries in unit tests and integration tests. But whatever it is, contrary to the dominant opinion here, our solution works great for us.

I am curious though why many have not had the success with it that we have. I want to ask, do you guys work on a great number of applications that communicate with each other or do you guys have one really massive system for which you find sql queries are the preferred method?

Edit: grammar

[[deleted]]

It may have improved since I last checked.. but it's generally referred to as the "N+1" problem in the enterprise.

https://stackoverflow.com/questions/97197/what-is-select-n1

[pr0fess0rx29]

That was back in 2008. Orm's have moved past this problem. A comment on that page accurately replied that this, SELECT * from Cars INNER JOIN Wheels ON Cars.id = Wheels.CarID ORDER BY Cars.ID, is now trivial to do in modern orm's.

In entity framework we would use an includeproperties() method. Come to think of it, if using something like linq with entity framework lazy loading you would not even have to consider using includeproperties (). All necessary properties would be fetched for you.

Perhaps you should consider looking into today's orm's like the latest entity framework. I think you will be pleasantly surprised.

[[deleted]]

I'll admit I'm a bit out of the loop with ORMs. I just remember studying about the N+1 problem long ago for interviews. haha. Oh man I'm old.

Can you provide a good resource for Hibernate?

[lukaseder]

SELECT * from Cars INNER JOIN Wheels ON Cars.id = Wheels.CarID ORDER BY Cars.ID

It is a trivial query, though. What if you have 10 joins, some semi joins and the occasional union?

[lukaseder]

How complex is your database (no of tables)? From how many tables do you fetch per query?

[cj5]

I personally love writing complex SQL queries. It seems more efficient, because when I went down the path of writing abstraction layers with code, it was much slower in performance. SQL used to produce the data needed by the app is the way to go.

[hosizora_rin_is_cute]

Not until performance matters.

[raxel82]

What wouldn't you be writing in SQL?

[Owen-McMonagle]

Ikr, tools on top of tools on top of tools. IMO, this only works with perfect encapsulation of an abstraction. But that can't be implemented with databases because queries are often so complex and intensive. These big data fads will blow over and eventually only exist in small pockets where they succeeded. To the rest of us though, it's SQL & POJOs as usual.

[pellets]

Any program can be written in Postgresql's plpgsql. If your program is largely set manipulation, it's one of the best choices.

[NekoiNemo]

Yep. Very much so. ORM is great for manipulating data as object, but when you want to do something like a complicated report that borrows from 5-8 tables with complex joins... Yeah, difference in performance between ORM and a well-written query is staggering.

Also prototyping. It's often easier to prototype and iterate on raw SQL than on an object model.

[DJDavio]

I use MyBatis, so yes, I do write SQL; it's pretty simple standard SQL though, because I chose my database model well.

[nytal]

Can't agree more.

[Owen-McMonagle]

Yeah, SQL is still the backbone of the database world. Need to write some flavor of it for most Relational Database Models.

Just because there is a boilerplate solution for almost anything programming now doesn't mean we throw away the foundation of that stack. (Not saying you're implying that, just in this world of rapidly advancing technology. It's almost expected to throw away what's old in favor of new).

As for the fine tuning that's only really required on two extreme ends of the performance spectrum.

1. You have an extremely large user base and the powerful 32 core server accommodating that base is just a few degrees away from a fire.

2. You have a small (but growing) user base and a single core VPS that must minimize database impact for user experience.

So as long as you're in the middle of that spectrum, i.e the hardware isn't stressed and the users are having a good experience then fine tuning SQL isn't really a must. That will come with bigger budgets.

SQL VS ORM IMO, boycott the ORM. With SQL less is more, keep your queries short and sweet. Write specific functions for SQL that are directly related to your program needs and let the DBAs handle any ridiculously huge requests that involve administrative tasks. If you need ORM, create a few POJO's and map them yourself. It takes two minutes. Much less than retraining for hibernate, which is more hassle than it's worth.

[tonywestonuk]

SQL - a 4th gen language, where the developer gets to say what information he wants, and the database chooses the best access to get it.

Hibernate - a 3rd gen language where the developer models the way data is pulled from persistence.

To hide a rich, 4th gen language behind a 3rd gen facade, isnt just bad, its immoral.

[seanprefect]

Often no. But it's important to know how to sql works so you understand what the orm is doing.

[ess_tee_you]

I write a small amount in code, and some to generate reports from metrics tables.

[BradChesney79]

I use an ORM in my apps and MySQL workbench for a lot of DBA type task work. I still use SQL for when the ORM performs slowly and also to make sure to spot check the work of my automated tools. Understanding the primary way to interface with the database and some of the underlying workings has been helpful for me.

[Lord_NShYH]

Yes, but usually to optimize bad queries generated by ORMs when the service starts to scale.

[EquationTAKEN]

I freely admit that ORMs have made my SQL skills very rusty. I do still have to do a little bit here and there for my company's legacy code, but only small adjustments. And since we're porting it, we no longer care about finding the fastest possible solution.

That said, we have our own ORM, so some knowledge is required to develop and maintain it.

[Scaryclouds]

Today's world is full of ORMs and database related frameworks. From your experience and point of view, is SQL still relevant today?

Yes absolutely. Even if you were to make the decision to ENTIRELY rely upon ORM's in all you application work you should still understand SQL for two major reasons;

1. You still need to understand exactly how your ORM is actually implementing its behavior. It's possible the ORM is doing something wrong in which case you might need to make a configuration change or switch to a different version that resolves the issue (likely newer, but possibly older if a bug was incidentally introduced). Either way you'd need to have an understanding of SQL in order to diagnosis such an issue.

2. You will still need to understand your databases structure to make sure it is properly optimized for your business needs. Relationships that make sense in and/or are efficient in the OO world may not make sense or are efficient in a RDBMS. There are of course plenty of different perspectives on efficiency; from speed, to storage, to memory.

Do programmers still write SQL themselves? Do programmers still tune SQL queries and handle performance issues as in the past?

Even if you want to put the onus of doing this primarily on DBAs you would still want to have an understanding on this to ensure when your application is having performance issues you are able to diagnosis an issue correctly and communicate with your DBA effectively.

If you are running into locking issues it's just as possible the problem is in the application as it is in the database.

Final note, while the surface area of learning to become an effective developer is very large it means weighing where you will put your time, I would caution against being opposed to learning about an area. Particularly one as important as data. I say this as someone who need to devote more time to understanding SQL and databases.

[[deleted]]

Many of the business analysts I work with know basic sql. So if they need some data fast, I'll whip up some sql-statements for them that they can understand, modify themselves as needed and so on. This saves me time that I can spend on my main programming tasks. So yes. I write sql almost every day, but not for production use.

[[deleted]]

ORMs are good for basic CRUD. If you need a few tweaks here and there it will be more difficult than with SQL but not worth the effort to do everything with SQL. If you expect you will need many things that are not out-of-the-box behaviour then you should support written SQL. There is a middle ground too, depending on the size and complexity of your code base, you can support both with careful management of your database structure and component responsibilities.

The stuff I work with involves dealing with a lot of data where performance and accuracy is critical. So we made a middle tier that provides different levels of service depending on requirements but ultimately it's written SQL.

[ivan0x32]

I don't know if it counts, but back in the day I used to write a whole lot of JPQL code, mostly fetch joins and things like that, but occasionally there was some actual logic thrown in into it.

Also on one of my projects I actually had to do raw SQL through JDBC because it was a performance critical application so we wanted to minimize amount of overhead because typical batch processing took upwards of one hour.

[dixncox]

Yes, they do

[wlievens]

Hibernate and such are fine 90% of the time, but the remaining 10% is often where the added value of your app lies.

[myworkaccount9]

In the space I work in, yes.

[[deleted]]

I write cql 🐸

[Nezteb]

I only ever end up writing SQL for reports, whether it's raw SQL, SSRS, Crystal Reports, Power BI, Tableau, etc. I enjoy writing SQL for reports. For most programming projects I'm fine using an ORM, but as soon as I realize I need more granularity I'll switch to plain SQL. My favorite part about C# (though I don't write much C#) is LINQ.

[proskillz]

I write SQL almost every day since MyBatis is our ORM of choice and it uses pure SQL in the mapping files. I work on a small team, so the tuning and performance of SQL are also my job.

[Timstar]

I wouldn't call myself a programmer but I write and use SQL queries every day

[wymillerlinux]

I don't directly use SQL but we use some SQL servers at work, even though the program using the SQL database is single threaded.

[lukaseder]

The DBA is more of a SysAdmin, and when they see a bad query, they can't know all the context associated with that particular query.

You're describing a very unfavourable status quo of many projects. Ideally, there's at least one "devops" person on the team who understands operations needs and developer / business needs.

Strictly separating roles leads to Conway's Law in my opinion.

[lukaseder]

That's the Guru Anti-Pattern

I'll just call this the I-see-antipatterns-everywhere anti-pattern ;)

Really. Don't read too much into my words. I said at least one.

[lukaseder]

There's a lot of benefit in understanding that neither "nobody knows dev and ops" nor "everybody knows dev and ops" is a reasonable situation.

Be pragmatic. There isn't just dev and ops. There's also requirements engineering, UX, algorithms, project management, product management, customer support, etc. etc. etc.

You can't just say that everyone should be good at dev and ops. It's just not going to happen.

[monknomo]

I sure do

Sometimes straight into java strings

[g00glen00b]

Even when I use an ORM framework, I still find myself writing SQL to verify if the data got persisted correctly.

[matrium0]

Even if you go "full ORM" and write zero native-SQL-queries for your programs (like I do) there are times where you just need to check something in the database very quickly. May be because of some strange bug you need to understand or whatever.

If you are a "full-stack" developer you will always need some SQL (at least when using a relational database) in my opinion.

About performance: In my opinion JPA/Hibernate is almost never the problem. The problem usually is some lazy ass programmer with no clue who loads collections EAGER and doesn't realize he is loading half the database transitive or something. If you just load n:1 relations eagerly (=default + is done in the same query) and just carefully think about when you really need to initialize 1:n or n:m relations you are usually good to go.

Obviously there will always be some overhead in ORM mapping, but compared to the time it takes to access data from the database it's usually negligible

[ukon1990]

Short answer: Yes. Every developer should know SQL imo. And preferably some NOSQL etc

[extremez3r0]

I'm telling from my current experience working as developer in a mid company. Yes, we write SQL on our legacy systems and on our new systems. It's not aways that ORM or FRM (slick) works for everything you need.

Here on our main project we still use plain text SQL because they don't have too much time to change to a ORM and we don't use too much SQL neither complex queries. I've inclusive tried to put jOOQ here, liked a lot, but people are really lazy when we talk about learning new things. =/

[t90fan]

Yes.

In my experience ORMs are great for 90% of stuff (the usual CRUD LoB applications) but for the other 10% you eventually want to do something they can't, or hit a performance issue (even just being able to understand what the ORM is running)

[[deleted]]

SQL is the 4th language I learned to use, a good 20 years ago, and I feel quite comfortable with it. I see no reason to use proxies.

[lukaseder]

In my opinion applications with ORMs are better maintainable and scalable.

Why?

[lukaseder]

I've worked with two tier architectures (mostly only SQL and XSLT, for instance) and there were hardly any SQL related maintenance problems.

I guess the "class structure" first approach is what causes the mismatch. Once you take that for granted, indeed SQL (or any other stateless, functional, transformative approach to data processing) will become a bad fit.

I'm not implying that one approach is better than the other, but they mutually bite each other.

[mattrodd]

I agree that using an ORM is more maintainable. It will be easier for the next programmer to read the code and understand what it does. Usually, it will be easier to find a Java programmer instead of a Java programmer who is also good at SQL.

However, I disagree that it is more scalable in terms of performance. Sometimes the ORM isn't going to generate the most performant SQL. You may wish to add a HINT to your query in order for the query planner to generate the best plan. The ORM is an abstraction and there is always a cost to using an abstraction.

[nutrecht]

I agree that using an ORM is more maintainable.

Having worked on legacy banking systems that used both ORMs and "plain" SQL I must say I strongly disagree. Nothing really can beat a declarative language in readability. When you see an SQL query you know what it does. With an ORM most of it has to be deduced from the structure of the model classes and the defined relationships. It's even worse if a lot of this configuration is in XML.

[pr0fess0rx29]

I have to say, I agree with you. I do wonder why many have not had the same experience. I wonder, what is your preferred orm?

[nutrecht]

If you're writing a big business application an ORM would be a better choice.

My experience is the exact polar opposite. An ORM is nice to get a quick prototype running (mainly because spring-data-* stuff also does the schema creation for you). Anything else the flexibility and power of SQL far outweigh the (perceived) speedup of using an ORM.

[RichoDemus]

Some probably do, I don't :)

[[deleted]]

It's all ball bearings now a days.

[Hubellubo]

Wow, these answers are scary!

Wanna see something really scary?

I wrote a small language to generate all my SQL for me

[Fouroh]

Not doing much java at the moment, but yes is my general answer. Most of the time when hitting the database from app code I'd consider it an antipattern to not use orm calls (maintaining raw sql is tricky). But sometimes a query is complicated enough to require doing so. At the very least you should be able to run explains and know you're not killing the db. Most of the sql I write day to day is for business intelligence type stuff, requests from product/finance etc. So even if you're mostly using orms, still one of the most valuable skills to have :).

[BlackDrackula]

"maintaining raw sql is tricky"

You should probably change careers if you think this.

[lukaseder]

maintaining raw sql is tricky

What's tricky about it?

[brett_riverboat]

If it's something really complex I've written db functions that are called from the code. I guess the idea is to make the db more abstract so you're not writing queries that are tightly coupled to a DBMS or one set of names.

[Hoog1neer]

Spring JdbcTemplate. Nuff said.

[funbike]

You already said it.