MyBatis know how much (3)

One way to address the inherent limitations of stored procedures is to embed SQL in a more general language. In contrast to the stored procedure moving business logic into the database, inline SQL moves SQL from the database into the application code. This allows the SQL statement to interact directly with the language. In a sense, SQL becomes a feature of the language. There are many languages with this "feature", including COBOL, C, and even Java. Here is an example of SQL in Java:

= 28959};

Inline SQL is really elegant, because it does a close integration with the language. Local language variables can be passed directly to SQL as parameters, and SQL execution results can be directly assigned to similar variables. In a sense, SQL does become a feature of the language.

Unfortunately, inline SQL has not been widely accepted, and the existence of some major problems has finally made it impossible to bear fruit. First, SQL is not standard, there are many extended versions, and each version applies only to a particular database.

The splitting of the SQL language makes it difficult to implement an inline SQL parser that is both complete and portable across database platforms. The second problem with inline SQL is that it often does not really implement a feature of the language, but instead uses a pre-compiler to "translate" inline SQL into the corresponding code in the current language. This poses a problem for tools such as the IDE (integrated development environment, integrated development environment), because these tools may need to be pre-interpreted to enable advanced features such as syntax highlighting and code completion. Code that contains inline SQL, if not precompiled, may not even compile, and this dependency causes people to worry about the maintainability of their code in the future.

One of the scenarios for resolving inline SQL is to remove SQL from the language level and replace it with some kind of data structure (such as a string) in your application. This approach is what we typically call dynamic SQL.

Dynamic SQL

Dynamic SQL solves some of the problems with inline SQL by avoiding using a precompiled compiler. Instead, SQL is represented as a string type of data that can be manipulated like all other character data in a modern language. Because SQL is represented as a string type, it can no longer interact directly with the language like inline SQL. Therefore, the implementation of dynamic SQL requires a strong set of APIs to set up SQL parameters, get results data, and so on.

The advantage of dynamic SQL is its flexibility. SQL can be built at run time based on different parameters or dynamic application capabilities. For example, a Web form with a sample query (Query-by-example) might allow the user to dynamically select the fields that need to be searched, as well as the data that they want to search. This requires that the WHERE clause of the SQL statement be dynamically changed, which is very easy to do with dynamic SQL.

Dynamic SQL is the most popular method of accessing a relational database from a modern programming language today. Most of these modern programming languages include standard APIs for database access. It is believed that Java developers and. NET developers are familiar with the standard APIs in their respective languages (JDBC and ADO, respectively), and these standard SQL APIs are often very strong and provide great flexibility for developers. Here is a simple example of dynamic SQL in the Java language:

= "Select Emp_name, hire_date" + "from employee WHERE emp_num =?"  == conn.preparestatement (sql) ';


= Ps.executequery ();

While

HireDate = Rs.getdate ("hire_date");}

Rs. close (); Should be wrapped in a try-catch, Conn.close (); code block

There is no doubt that dynamic SQL is not as elegant as inline SQL, or even as a stored procedure (exception handling has been omitted in the code above, but still very complex). These APIs are usually very complex and lengthy, as we saw in the previous example. Using such a framework often leads to a lot of code, and the code tends to be repetitive. In addition, the SQL statement itself is often too long to be written in one line of code. At this point, you have to break the SQL string into multiple substrings and then combine them by stitching (CONCATENAT) operations. The concatenation of strings in the code makes the readability of SQL much less, which brings more difficulties to maintenance and use.

So, since SQL is placed in a database as a stored procedure, or in a language as inline SQL, or as a data structure in an application, what should be "disposed" of it? We dodged the question. In modern object-oriented applications, one of the most compelling solutions to interacting with relational databases is the use of object/relational mapping tools.

0/rm

0/rm (object/relational mapping) is designed to simplify object persistence by excluding SQL entirely from the responsibilities of the developer. In 0/rm, SQL is generated. --some tools generate SQL statically at application build or compile time, others are generated dynamically at run time. SQL is generated based on the mapping between the classes in the application and the relational database tables. In addition to not writing SQL statements, APIs that use the 0/RM tool are often much simpler than typical SQL APIs. 0/RM is not really a new concept, its history is almost as long as the history of object-oriented programming languages. Just a lot of progress in recent years has made 0/rm a compelling way to persist.

The modern 0/RM tool does not simply produce several SQL statements. They provide a complete set of persistence architectures that will benefit your entire application. Any good 0/RM tool provides transaction management capabilities, including very simple o/rm tools that can be used to handle local transactions and distributed transactions, often providing many cache policies to handle various types of data to avoid unnecessary database access. Another way that the 0/RM tool can reduce database access is the data lazy loading technique. Lazy loading allows the acquisition of data to be deferred until it is absolutely necessary, that is, until the moment they really need to be used.

While there are so many good features, the 0/RM tool is still not a "silver bullet" and it does not apply to all scenarios. The 0/rm tool is based on some assumptions and rules. One of the most common assumptions is that the database is properly normalized. As we discussed, the largest and most valuable databases are often not well-normalized. This can lead to a lot of trouble with mapping, or even a detour, or a compromise of efficiency at design time. No object/Relationship solution can support every feature, every capability, and design inherent flaw in each database. As we have said before, SQL is not a reliable standard. For these reasons, each 0/RM tool is just a subset of all the functionality that a particular database has.

Series Articles:

MyBatis know how much (1)

MyBatis know how much (2)

MyBatis know how much (3)