Is it bad software design to have JDBC/raw SQL and also use an ORM? I don’t mind using an ORM for boilerplate CRUD, but some queries are better in SQL (for me). I’m not arguing for or against ORMs. I’ve read about them until I’m blue in the face.
I used JDBC in my question, but it could have been DBI, or some other database interface. ORM can be any of them.
How about maintainability? Would mixing the strategies complicate it? To me the maintainability of an application efficiently becomes even more blurrier, if it is a compiled language vs. a scripting language. I have to recompile Java. I don’t have to recompile Python or PHP.
If my SQL is in a model layer, does that change anything?
EDIT: If I use find a point where I need to use SQL in my ORM, in my model, what kind of data is sent back? I won’t be able to use something like person.save(‘somevalue’), for example, will I?
3
It depends on your application needs. For example, I’ve been working for banking, and when you have to generate thousands of invoice payments orders, it’s better to have an stored procedure to do the task (it’ll be by far faster) and to update the model tables directly in the database.
Then you can show the results with ORM, even update a payment order (but not thousands).
If your application is not performance critical, it’s better for programming mantainability to deal with ORM, so that you don’t need to know about the underlaying DB technology.
So that one more time, it depends on your needs and if you keep the code organized and clean (apply a DAO pattern for it, for example), it’s not a bad architecture nor unmaintainable code.
It’s usually bad because the ORM will cache data, so by going around it you won’t be able to take advantage of that caching. You also can’t share things like prepared statments. If you intermix things too much, you could possibly also wind up with a deadlock if your JDBC code and the ORM don’t use the same locking strategy, or you have lock promotion issues.
Some ORMs provide the ability to execute statements within the same context as the ORM, which can eliminate many of the problems.
3
Object-Relational Mappers are not an all-or-nothing proposition. You use ORM’s so that you don’t have to go through the tedium of writing CRUD queries, which can comprise about 80% of your application.
But every ORM still has a mechanism for using raw SQL or stored procedures, or allows you to write such procedures outside of the ORM. This allows you to optimize that small part of your program (perhaps the remaining 20%) that doesn’t satisfactorily lend itself to orthodox object-relational mapping.
2
It depends. Most of the ORM frameworks provide an “escape hatch” that lets you execute raw SQL, but what should be in the back of your mind is: “Why am I doing this when my ORM layer is capable of recreating an entire object hierarchy on demand?”
I would say you’re really stepping onto thin ice if you’re doing updates outside of your ORM layer. Queries would probably be okay if you’re doing something reporting related (aggregation or joins), where the cost of building up object hierarchies is high and provides little value. However, even then you have to think about what you’re trading off. The reason we use ORMs is so that we can think in objects, and by writing raw SQL against the underlying tables, even when it’s just queries, we’re writing code that will break when we change our object attributes or relationships. Whether that’s a big deal or not depends entirely on the application.
So, unfortunately the answer to your question is: “It depends.”
I’d say yes, you should probably avoid it if at all possible.
Code is read drastically more regularly than it is written. When you’re maintaining an application, having code that works and reads the same way throughout is much more maintainable than having bits that work one way, and bits that work another.