My CMS development Note-2 that ORM is ORM, write SQL or write SQL

Ah, ORM or SQL, this is a problem.

First of all, my original company's products are Java and. NET coexistence, the Java part is outsourced, so you can imagine, two programmers encounter a piece of what happened-____-; Fortunately, we are civilized people, after the war of words, I also get from their mvc,hibernate and so on the concept of framework, so that the world also has such a programming idea. Of course, they were also surprised by the ability of the SQL query of Heaven and earth (first blown boast), countless seemingly complex report problems in the magic of SQL statements under the Ashes, both have their own advantages.

So I had this problem when I was developing, ORM can greatly liberate the productivity, in the background maintenance, the code is extremely concise, and no longer need to worry about what the field misspelled Ah, leakage field ah what a mess, as long as the need to implement the function can be. Let's take the article release module for example, use ORM after the development process is like this, I first create the entity class, anyway, what is the title Ah, content Ah, subtitle Ah, xxoo on those things, and then, even the table is not built, directly with the entity class can generate tables, additions and deletions to check. In the show, using an ORM query, even SQL injection is automatically to you, it looks really good.

Yes, it's good. But here is a very serious problem, the article input is of course no problem, but in the article List page, the problem came. First of all, the first big question is the query question. Article List page, only need to display the title of the article, a large section of the article content is not need to find out. To find out these things is undoubtedly a great effect on efficiency. But at this point you'll find that using ORM is all about being controlled, he's either simply unable to customize the field, or it's very troublesome. As an example of the Castle ActiveRecord (internal call nhibernate) I use, it's true that he can use local SQL statements, but you have to write all the fields in your use, and you'll often experience strange and strange questions. In fact, my request is very simple, I just want to take the first 10 headings, why do I have to go around a big circle? Well, anyway, not 10 articles, waste is not wasted, I endure.

However, if I add the material now, I have to take a few from a certain category, or a few categories, but also consider the top, recommend and other factors, this is a big problem. ActiveRecord that poor little bit of inquiry is not going to satisfy such a complicated (though actually uncomplicated) query, OK then I use HQL, after some research, HQL is written out, but his automatic translation into the SQL statement is more than handwritten SQL is much worse, and the use of the child When the query is written in obscure and difficult to understand, it is ... I forgot it when I wrote it.

Then after a painful decision, I decided, hey, despised is despised, while using SQL and ORM. ORM for site background maintenance, front show, or the use of my hard work for many years of SQL query, hey, can not forget the person is not, of course, I can not do the full page of SQL this too outdated things, appropriate encapsulation, the display of the page and data directly separated, the code to write a little clearer, is not necessarily bad where to go, but the efficiency of implementation is coming up.

Because the ORM framework is used, SQL Server and access can be used at the same time, and other databases have not yet been tested, then pages that are directly SQL-queried also need to prepare a database access layer, while supporting SQL and Access or others, which everyone will have nothing to say, According to my previous experience in the implementation of financial software maintenance, in fact, the standard SQL statements of the major databases are not too much, subqueries, connections and other grammars are generic. Or the list of articles, it's just

Select top title from article where .... This type of writing is simple and straightforward.

In terms of the SELECT statement, the vast majority of requirements can be written in a variety of database, even if it is not found common writing, then the use of reflection and other techniques to support multiple databases, anyway, the efficiency of SQL statements must be higher than ORM.

Finally, the conclusion is that, do not hang in a tree, the most important thing is that the structure of the software to be clear, implementation efficiency to be satisfied