So is ORM needed in a large and complex Enterprise project?
Recently, a question flashed on Habré - is ORM really necessary in a large and complex project? After all, it is often slow, cumbersome, supports only a certain subset of SQL, does not support a specific and very convenient syntax, for example, Oracle (the same connect_by for hierarchical queries) and so on and so forth.
The opinion was expressed that ORM is really only needed in primitive projects to reduce the size of the code, and in a really large and complex project it is better to do without it. I will say for big projects - let the others say for small ones :) I’ll make a reservation that the reasoning and examples are based on Java / Oracle, a classic bundle.
By itself, ORM, just like a mapping, in large projects, is needed very strongly.
Imagine - I have a very large system, and there is an orders table in it, say, 50 columns (in fact, we have 150, but oh well. Normalizers, be silent! I also know about normal forms). And here you have to imagine that you choose one order and show it on the screen. Suppose you write select, the question is - what next to do with its results, in the intermediate layer, in server-side Java? You do not call the stored procedure or request directly from, say, the JSP page (I hope), you still need to get the data and pass it somehow, in the form of some kind of data structure.
')
So, pass them as an array, ArrayList, of an associative array as [column name: value]? It is cumbersome, inconvenient, and very easy to make a mistake. And if you need several orders, then what, create nested collections to convert the results? By its harmful influence, this will already resemble the well-known anti-pattern “String typing”. Use a collection of vectors or associative arrays where you can use a collection of objects of type Order.
We found an example of a case, we need an Order object that has all the necessary properties, and, consequently, we need code that can convert the results of an SQL query into objects (a collection of such objects).
Further, it is obvious that it is difficult and tedious to write all the requests with your hands, it is easy to make a mistake, because in Java, they will be presented either as strings in the code (which means no static typing and compile-time checks), and they should be kept either in Java code (if they are small, and you still get trash Java code), or more to make in separate XML files.
Here is a small lyrical digression. The argument is whether to keep the SQL code for short queries right in the Java code, or to take them into separate files in Enterprise-e has been going on for a long time.
The argument for keeping from inside Java code is that the used code is side by side, it is easier to find and see the SQL query code, than if it lies somewhere separate, in case of changes in the VCS, you will need to remember to change only one file and two.
Argument for separation into different classes - multiline SQL queries heavily litter Java code (including because Java does not fully support multiline string literals, unlike, say, Groovy) + if they are defined as class constants, then when the content changes this constant make java hotswap, for example, will not work. I myself usually look at the circumstances :)
In general, ORM in large projects is needed to simplify the routine part. Without him - nowhere :)
Of course, it’s not possible to do only ORM. We have a lot of places where complex logic is written in queries stored procedures in 500-1000 lines in PL / SQL, for written through ORM / Java, it would take 10 times more space and work 2 times slower (at the same time, it was it would be even less understandable, because there is such logic, which is described in terms of relational algebra easier than in terms of OOP :), and such logic falls on the ORM with a creak). How many complicated queries with subqueries, unions, and cunning joins are also cumbersome to write through a pure ORM. Optimizing queries that work with tables where at least a few hundred million records, without access to SQL optimizer plans and statistics / DBMS level monitoring tools (for Oracle this is Explain Plan, tkprof, Grid Control) is also extremely difficult. So without SQL, too - nowhere :)
You can talk about how to still sweeten the work with pure SQL queries in a large project.
One option is to use macros. I write my own framework for writing SQL macros (in essence, the engine for running moderately primitive parsers, allowing people to write their own macros and parsers for them). Macros can help solve the following tasks:
and much more.
Yes, and more, in the end. I did not say a word here about compatibility with different DBMSs, since This is often an unnecessary requirement. Large and complex systems actively use DBMS-specific features (extensions to SQL syntax, syntax for procedural extensions such as PL-SQL, T-SQL, pgplsql are very different in general, partitioning schemes, etc., etc.), and requirements to write SQL code , at the same time compatible with Oracle / MSSQL / DB2 is often not installed (I listed here the classic recognized Enterprise-level DBMS, please do not be offended by the rest of the fans). Everyone understands that this is a huge complication and the rise in price of the system, which is far from always justified. Genuine independence from a serious vendor, such as Oracle or IBM, is expensive, very expensive. And how often does she really need?
The opinion was expressed that ORM is really only needed in primitive projects to reduce the size of the code, and in a really large and complex project it is better to do without it. I will say for big projects - let the others say for small ones :) I’ll make a reservation that the reasoning and examples are based on Java / Oracle, a classic bundle.
Why ORM is needed
By itself, ORM, just like a mapping, in large projects, is needed very strongly.
Imagine - I have a very large system, and there is an orders table in it, say, 50 columns (in fact, we have 150, but oh well. Normalizers, be silent! I also know about normal forms). And here you have to imagine that you choose one order and show it on the screen. Suppose you write select, the question is - what next to do with its results, in the intermediate layer, in server-side Java? You do not call the stored procedure or request directly from, say, the JSP page (I hope), you still need to get the data and pass it somehow, in the form of some kind of data structure.
')
So, pass them as an array, ArrayList, of an associative array as [column name: value]? It is cumbersome, inconvenient, and very easy to make a mistake. And if you need several orders, then what, create nested collections to convert the results? By its harmful influence, this will already resemble the well-known anti-pattern “String typing”. Use a collection of vectors or associative arrays where you can use a collection of objects of type Order.
We found an example of a case, we need an Order object that has all the necessary properties, and, consequently, we need code that can convert the results of an SQL query into objects (a collection of such objects).
Further, it is obvious that it is difficult and tedious to write all the requests with your hands, it is easy to make a mistake, because in Java, they will be presented either as strings in the code (which means no static typing and compile-time checks), and they should be kept either in Java code (if they are small, and you still get trash Java code), or more to make in separate XML files.
Here is a small lyrical digression. The argument is whether to keep the SQL code for short queries right in the Java code, or to take them into separate files in Enterprise-e has been going on for a long time.
The argument for keeping from inside Java code is that the used code is side by side, it is easier to find and see the SQL query code, than if it lies somewhere separate, in case of changes in the VCS, you will need to remember to change only one file and two.
Argument for separation into different classes - multiline SQL queries heavily litter Java code (including because Java does not fully support multiline string literals, unlike, say, Groovy) + if they are defined as class constants, then when the content changes this constant make java hotswap, for example, will not work. I myself usually look at the circumstances :)
In general, ORM in large projects is needed to simplify the routine part. Without him - nowhere :)
Why need pure SQL
Of course, it’s not possible to do only ORM. We have a lot of places where complex logic is written in queries stored procedures in 500-1000 lines in PL / SQL, for written through ORM / Java, it would take 10 times more space and work 2 times slower (at the same time, it was it would be even less understandable, because there is such logic, which is described in terms of relational algebra easier than in terms of OOP :), and such logic falls on the ORM with a creak). How many complicated queries with subqueries, unions, and cunning joins are also cumbersome to write through a pure ORM. Optimizing queries that work with tables where at least a few hundred million records, without access to SQL optimizer plans and statistics / DBMS level monitoring tools (for Oracle this is Explain Plan, tkprof, Grid Control) is also extremely difficult. So without SQL, too - nowhere :)
You can talk about how to still sweeten the work with pure SQL queries in a large project.
One option is to use macros. I write my own framework for writing SQL macros (in essence, the engine for running moderately primitive parsers, allowing people to write their own macros and parsers for them). Macros can help solve the following tasks:
- Delivering reusable parts of queries (usually subqueries) into separate, let's say, named SQL components, and then including them in queries using a macro
- Unification of some annoying differences in SQL syntax, for example, when a query parameter can be a scalar number, or maybe an array, then you write a macro that abstracts you from the difference between t.property = x and t.property in (x1, x2)
- Adding predicates to where clause for security
- Wrapping the entire query in SELECT * FROM (query body) t order by ... for unified sorting and paging support
and much more.
About portability between different DBMS
Yes, and more, in the end. I did not say a word here about compatibility with different DBMSs, since This is often an unnecessary requirement. Large and complex systems actively use DBMS-specific features (extensions to SQL syntax, syntax for procedural extensions such as PL-SQL, T-SQL, pgplsql are very different in general, partitioning schemes, etc., etc.), and requirements to write SQL code , at the same time compatible with Oracle / MSSQL / DB2 is often not installed (I listed here the classic recognized Enterprise-level DBMS, please do not be offended by the rest of the fans). Everyone understands that this is a huge complication and the rise in price of the system, which is far from always justified. Genuine independence from a serious vendor, such as Oracle or IBM, is expensive, very expensive. And how often does she really need?
Source: https://habr.com/ru/post/123293/
All Articles