Analysis of MyBatis from the evolutionary process of JDBC

We know that MyBatis is the encapsulation of JDBC, so how did he evolve?

Excerpt from the autobiography Intelligence Media Java Training materials

On the evolution principle of mybatis, let's take a look at our most familiar and basic database data through JDBC, which typically requires the following seven steps:

(1) Loading the JDBC driver

(2) Establishing and acquiring database connections

(3) Creating a JDBC statements Object

(4) Setting incoming parameters for SQL statements

(5) Execute SQL statements and get query results

(6) Conversion processing of query results and return of processing results

(7) Releasing related resources (close connection, close statement, close resultset)

The following is the specific implementation code:

1  Public StaticList<map<string,object>>queryForList () {2 3Connection Connection =NULL; 4 5ResultSet rs =NULL; 6 7PreparedStatement stmt =NULL; 8 9List<map<string,object>> resultlist =NewArraylist<map<string,object>>(); Ten  One         Try {  A  -             //loading the JDBC driver -  theClass.forName ("Oracle.jdbc.driver.OracleDriver"). newinstance ();  -  -String url = "Jdbc:oracle:thin: @localhost: 1521:oracledb";  -  +String user = "Trainer";  -  +String Password = "Trainer";  A  at             //Get database connection -  -Connection =drivermanager.getconnection (Url,user,password);  -  -String sql = "SELECT * from userinfo where user_id =?";  -  in             //Create statement objects (each statement for a database execution request) -  tostmt =connection.preparestatement (SQL);  +  -             //Set Incoming parameters the  *Stmt.setstring (1, "Zhangsan");  $ Panax Notoginseng             //Execute SQL statement -  thers =Stmt.executequery ();  +  A             //Process Query Results (convert query results to list<map> format) the  +ResultSetMetaData RSMD =Rs.getmetadata (); -  $             intnum =Rsmd.getcolumncount ();  $  -              while(Rs.next ()) { -  theMap map =NewHashMap (); - Wuyi                  for(inti = 0;i < num;i++){  the  -String columnName = Rsmd.getcolumnname (i+1);  Wu  - Map.put (columnname,rs.getstring (ColumnName)); About  $                 }  -  - resultlist.add (map); -  A             }   +  the}Catch(Exception e) { -  $ e.printstacktrace (); the  the}finally {  the  the             Try {  -  in                    //Close Result set the  the                 if(rs! =NULL) {  About  the rs.close (); the  thers =NULL;  +  -                 }  the Bayi                    //Close Execution the  the                 if(stmt! =NULL) {  -  - stmt.close (); the  thestmt =NULL;  the  the                 }  -  the                 if(Connection! =NULL) {  the  the connection.close ();94  theConnection =NULL;  the  the                 } 98  About}Catch(SQLException e) { - 101 e.printstacktrace ();102 103             } 104  the         } 106   returnresultlist; the 111}

JDBC begins to evolve to MyBatis

Above we see the implementation of JDBC has seven steps, which steps can be further encapsulated, reduce the amount of code we develop

First Step optimization: Connection acquisition and release

Problem Description:

The frequent opening and closing of a database connection creates a waste of resources and affects the performance of the system.

Solve the problem:

Access and shutdown of database connections we can use database connection pooling to solve the problem of resource wasting. Connection pooling allows you to reuse an already established connection to access the database. Reduces the time to open and close the connection.

Problem Description:

However, there are many different connection pools, there may be changes, it is possible to use DBCP connection pool, or the container itself can use the Jndi database connection pool.

Solve the problem:

We can isolate the decoupling through the datasource, we unify from the datasource inside obtains the database connection, DataSource concretely by the DBCP implementation or by the container's Jndi implementation all can, So we're going to datasource the implementation by letting the user configure it to cope with the change.

Second Step optimization: SQL Unified Access

Problem Description:

When we use JDBC to manipulate the database, the SQL statements are basically scattered across Java classes, with three deficiencies:

First, readability is poor, not conducive to maintenance and performance tuning.

Second, the changes to Java code need to be recompiled and packaged for deployment.

Third, not conducive to the removal of SQL in the database client execution (after removing the intermediate Java code, write a good SQL statement after writing, and by the + number in Java to patchwork).

Solve the problem:

We can consider not writing SQL statements into Java code, so where do we put the SQL statements? First we need to have a unified storage place, we can put these SQL statements in a unified centralized configuration file or database (in key-value format). The SQL statement is then passed the key value to obtain the corresponding SQL statement.

Now that we have all the SQL statements in the configuration file or database, this involves the loading of an SQL statement.

Third Step optimization: Incoming parameter mapping and dynamic SQL

Problem Description:

In many cases, we can achieve the purpose of using incoming parameters by setting placeholders in the SQL statement, which in itself has some limitations, which are passed in a certain order to match the placeholder one by one. However, if the parameters we passed are indeterminate (for example, a list query, depending on the query criteria that the user fills in, the parameters of the incoming query are different, sometimes a parameter, sometimes three parameters), then we have to put together the corresponding SQL statements in the background code according to the incoming parameters of the request. This will avoid the fate of writing SQL statements in Java code. Now that we have consolidated the SQL statements in the configuration file or database, how can we dynamically generate the corresponding SQL statements according to the different foreground parameters?

Solve the problem:

First, we first solve this dynamic problem, according to our normal programmer thinking is, through the IF and else this kind of judgment is the most intuitive, this time we think of Jstl in the <if test= "" ></if> such a label, then, Can you introduce such tags into SQL statements? Assuming it is possible, then we need a dedicated SQL parser to parse such SQL statements, but where does the IF-judged variable come from? The value passed in itself is mutable, so we have to define a constant variable name for this value, and the variable name must correspond to the corresponding value, you can find the corresponding value by this variable name, we think of Key-value map. When parsing, it is judged by the specific value of the variable name.

If the front can be judged no problem, then if the result of the judgment is true, then you need to output the SQL fragment inside the tag, but how to solve the problem of using the variable name in the tag? Here we need to use a syntax different from SQL to embed variables (e.g. using # variable name #). In this way, the SQL statement can be parsed to dynamically generate a context-compliant SQL statement.

Also, how do you divide placeholder variables and non-placeholder variables? Sometimes we can't just use placeholders, placeholders can only be placeholders for query criteria, and SQL statements can't be used anywhere else. Here we can use the # variable name # to represent a placeholder variable, using the $ variable name $ to represent a non-placeholder variable.

Fourth Step optimization: result mapping and result caching

Problem Description:

Executing SQL statements, getting execution results, converting execution results, and releasing related resources is a complete set. If the query is executed, then after the execution of the SQL statement, the return is a resultset result set, this time we need to get the data of the ResultSet object, or wait until the release of resources will not get the results information. We see from the previous optimizations, and will get the connection, set incoming parameters, execute SQL statements, release resources These are encapsulated, only the result processing this block has not been encapsulated, if it can be encapsulated, each database operation does not have to write a lot of Java code, Call an encapsulated method directly to get it done.

Solve the problem:

We analyze the general processing of the results of the implementation, it is possible to return the results without any processing, it is possible to convert the results to a JavaBean object return, a map return, a list return and so on, the result processing may be a variety of. From here, we have to tell SQL processor two points: first, what type of object needs to be returned, and secondly, how the data structure of the object that needs to be returned is mapped to the result of the execution in order to copy the specific value to the corresponding data structure.

Next, we can further consider caching the results of SQL execution to improve performance. The cached data is in key-value format, so how does this key come from? How can we guarantee the only one? Even though the same SQL statement is accessed several times during the process, the resulting SQL statement is different because of the different arguments that are passed in. It's a lot more time to cache. However, the SQL statement and the incoming parameter can be combined as the key value of the data cache.

Fifth Step optimization: Resolving duplicate SQL statement issues

Problem Description:

Because we put all the SQL statements in the configuration file, this time will encounter a SQL duplication problem, a few functions of the SQL statements are actually similar, some may be the select after the paragraph is different, some may be the where statement is different. Sometimes the table structure changes, then we need to change a number of places, not conducive to maintenance.

Solve the problem:

What happens when our code program has duplicate code? Pull the duplicated code out to become a standalone class, and then reference it where it needs to be used. For the problem of SQL duplication, we can also use this approach, by modularization the SQL fragment, separate the duplicated SQL fragment into a single SQL block, and then reference the duplicate SQL block in each SQL statement, so that you need to modify only one place.

Optimization Summary:

Let's summarize the optimizations and packages that face JDBC:

(1) Using a database connection pool to manage connections

(2) SQL statements are stored uniformly to the configuration file

(3) Mapping of SQL statement variables and incoming parameters and dynamic SQL

(4) Handling of dynamic SQL statements

(5) Mapping and result caching of database operation results

(6) Duplication of SQL statements

Architecture Design for 2.3.mybatis

Functional Architecture explained:

We divide MyBatis's functional architecture into three tiers:

(1) API Interface Layer: provides interface APIs for external use, where developers manipulate databases through these local APIs. Once the interface layer receives the call request, it invokes the data processing layer to complete the specific data processing.

(2) Data processing layer: Responsible for the specific SQL lookup, SQL parsing, SQL execution and execution result mapping processing and so on. Its primary purpose is to complete a database operation at the request of the call.

(3) Base support layer: Responsible for the most basic functional support, including connection management, transaction management, configuration loading and caching processing, these are common things, they are extracted as the most basic components. Provides the most basic support for the data processing layer of the upper layer.

Analysis of MyBatis from the evolutionary process of JDBC