MyBatis Study (i)

What is 1.Mybatis?

Mybatis is an Apache open source project Ibatis, 2010 This project by the Apache Software Foundation migrated to Google Code, and renamed to Mybatis, In essence mybatis some improvements to ibatis.

MyBatis is an excellent persistence layer framework that encapsulates the process of working with databases in JDBC, allowing developers to focus on the SQL itself without having to expend effort to process such as registering drivers, creating connection, creating statement, setting parameters manually, Result set retrieval and other JDBC complex process code.

MyBatis the various statement (statement, PREPAREDSTATEMNT, CallableStatement) that will be executed by XML or annotations, The resulting SQL statements are generated by mapping Java objects and SQL in statement, and finally the SQL is executed by the MyBatis framework and the results are mapped to Java objects and returned.

2. Analysis of problems in the original JDBC program

2.1 Original Eco JDBC Program code (Java)

1      Public Static voidMain (string[] args) {2Connection Connection =NULL;3PreparedStatement PreparedStatement =NULL;4ResultSet ResultSet =NULL;5         Try {6             //1. Load Database driver7Class.forName ("Com.mysql.jdbc.Driver");8             //2. Get the database link through the Drive management class9Connection = Drivermanager.getconnection ("Jdbc:mysql://localhost:3306/mybatis?characterencoding=utf-8", "Root", " Root);Ten             //3. Define SQL statements? represents placeholders OneString sql = "SELECT * from user where username =?"; A             //4. Get preprocessing statement -PreparedStatement =connection.preparestatement (SQL); -             //5, set the parameter, the first parameter is the ordinal of the parameter in the SQL statement (starting from 1), the second parameter is the set parameter value thePreparedstatement.setstring (1, "AA"); -             //6. Issuing SQL execution query to the database, querying the result set -ResultSet =preparedstatement.executequery (); -             //7. Traverse Query Result set +              while(Resultset.next ()) { -System.out.println (resultset.getstring ("id") + "" +resultset.getstring ("username")); +             } A}Catch(Exception e) { at e.printstacktrace (); -}finally{ -             //8. Releasing Resources -             if(resultset!=NULL){ -                 Try { - resultset.close (); in}Catch(SQLException e) { -                     //TODO auto-generated Catch block to e.printstacktrace (); +                 } -             } the             if(preparedstatement!=NULL){ *                 Try { $ preparedstatement.close ();Panax Notoginseng}Catch(SQLException e) { -                     //TODO auto-generated Catch block the e.printstacktrace (); +                 } A             } the             if(connection!=NULL){ +                 Try { - connection.close (); $}Catch(SQLException e) { $                     //TODO auto-generated Catch block - e.printstacktrace (); -                 } the             } - Wuyi         } the}

2.2 Problems with primitive JDBC

1. Creating a database connection is hard-coded

The configuration file can be used to resolve

2. Hard-coded when executing statement (SQL execution)

The configuration file can be used to resolve

3. Frequent open and close database connections There is a performance waste

The connection pool can be used to resolve

2.3 JDBC evolves to the MyBatis process

Above we see the implementation of JDBC has eight 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.

2.4 Optimization Summary

Let's summarize the optimizations and encapsulation on the face of JDBC

(1) Use a database connection pool to manage data connections.

(2) SQL statements are uniformly placed in 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

2.5 MyBatis needs to be improved

Problem Description:

Mybaits all database operations are based on SQL statements, resulting in any database operations that write SQL statements. There are too many SQL statements to be written by an application system.

Improved method:

Most of our operations on the database is to the table data additions and deletions, a lot of single table data to operate, from this point we can think of a problem: a single table operation may not write SQL statements, through the JavaBean default mapper generated corresponding SQL statements, For example: A class userinfo corresponds to the User_info table, and the UserID property corresponds to the user_id field. So that we can get the corresponding table structure by reflection, it's obviously not a problem to piece together the corresponding SQL statements.

MyBatis Study (i)