MyBatis Study Summary of MyBatis use recommendations _java

Description: What is MyBatis?

(formerly Ibatis) MyBatis is a persistent layer framework that can customize SQL, stored procedures, and advanced mappings. MyBatis eliminates the manual setting of almost all JDBC code and parameters and the retrieval of result sets. MyBatis can use simple XML or annotations for configuration and raw mappings, mapping interfaces and Java Pojo (Plain old Java Objects, normal Java objects) to records in the database.

The 1.Mapper layer parameter is map, and the service layer is responsible for overloading.

Mapper due to the mechanism of the problem, can not overload, parameters are generally set to map, but this will make the parameters blurred, if you want to make the code clear, you can use the service layer to achieve the purpose of overloading, the external service layer is overloaded, However, these overloaded service methods are actually tuned to the same mapper, except that the corresponding parameters are inconsistent.

Some people might think, why not in the service layer also set to map it? I personally do not recommend this, although for the sake of convenience, I have in the previous project has a large number of this approach, but it is obvious that the future maintenance work will bring trouble. Because doing so will make your entire MVC dependent on the map model, which is really nice and easy to frame, but there's a problem: just look at the method signature, you don't know the number of parameters, type, and the meaning of each parameter represented in the map.

Just imagine that you only change the service layer, or the DAO layer changes, you need to understand the whole process of the map pass through the parameters, unless you note or the document is good, otherwise each layer of code must be clear, you know what parameters passed. That's fine for simple MVC, but if the hierarchy is complex, the code becomes incredibly complex, and if I add an argument, I need to add a comment for each layer. In contrast to annotations, it is more feasible to use method signatures to ensure that this code is controllable, since annotations may be obsolete, but method signatures are generally less likely to be stale.

2. Use if choose and so on to minimize the difficulty of maintenance.

MyBatis configuration of SQL, as far as possible with the use of the Choose, such as tags, can use SQL to determine the use of SQL to judge (case, decode, etc.), so as to maintain later. Otherwise, once the SQL expands, super disgusting, if you need to debug the SQL in MyBatis, need to remove a lot of judgment statements, very troublesome. On the other hand, a large number of if judgments, will make the generated SQL contains a large number of spaces, increase the network transmission time, nor desirable.

And a lot of if choose statements, inevitably, each generation of SQL will be inconsistent, will lead to Oracle a large number of hard parsing, nor desirable.

Let's take a look at this sql:

<code class= "Hljs sql" style= "PADDING:0.5EM; margin:0px; Display:block; Color:rgb (101,123,131); Overflow-x:auto; Background:rgb (253,246,227) "><span class=" Hljs-operator "style=" padding:0px; margin:0px "><span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">SELECT</span> * <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">FROM</span> t_news_text <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">WHERE</span> <span class=" Hljs-number "style=" padding:0px; margin:0px; Color:rgb (42,161,152) ">1</span> = <span class=" Hljs-number "style=" padding:0px; margin:0px; Color:rgb (42,161,152) ">1</span> < <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">choose</span>> < <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">if</span> test =<span class="Hljs-string" style= "padding:0px; margin:0px; Color:rgb (42,161,152) ">" StartDate!= null and StartDate!= ' ' and EndDate '!= null and Endate!= ' "' </span>> & Lt;span class= "Hljs-keyword" style= "padding:0px; margin:0px; Color:rgb (133,153,0) ">AND</span> publishtime >= #{startdate} <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">AND</span> publishtime <= #{enddate} </<span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">if</span>> <otherwise> <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">AND</span> publishtime >= <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">SYSDATE</span>-<span class=" Hljs-number "style=" padding:0px; margin:0px; Color:rgb (42,161,152) ">7</span> <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">and</span> publishtime <= <span class= "Hljs-keyword" style=; margin:0px; Color:rgb (133,153,0) ">SYSDATE</span> </otherwise></<span class=" Hljs-keyword "style=" padding : 0px; margin:0px; Color:rgb (133,153,0) ">choose</span> ></span>

Such an if judgment, in fact, is completely unnecessary, we can easily use decode to solve the default value problem:

<code class= "Hljs sql" style= "PADDING:0.5EM; margin:0px; Display:block; Color:rgb (101,123,131); Overflow-x:auto; Background:rgb (253,246,227) "><span class=" Hljs-operator "style=" padding:0px; margin:0px "><span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">SELECT</span> * <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">FROM</span> t_news_text <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">WHERE</span> publishtime >= <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">DECODE</span> (#{startdate},<span class=" hljs-literal "style=" padding:0px; margin:0px ">null</span>,<span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">sysdate</span>-<span class=" Hljs-number "style=" padding:0px; margin:0px; Color:rgb (42,161,152) "&GT;7&LT;/SPAN&GT;, #{startdate}" <span class= "Hljs-keyword" style= "padding:0px"; margin:0px; Color:rgb (133,153,0) ">AND</span> publishtime <= <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">DECODE</span> (#{enddate},<span class=" hljs-literal "style=" padding:0px; margin : 0px ">null</span>,<span class=" Hljs-keyword "style=" padding:0px; margin:0px;  Color:rgb (133,153,0) ">sysdate</span>,#{enddate}" </span></code>

Of course, some people think that the introduction of case When,decode will result in the need for Oracle function resolution, will slow down the SQL execution time, interested students can go back to do a test to see if there will be a big impact. As far as personal experience is concerned, in my development process, there is no case where the SQL slows down due to function resolution. Typically, the operations that affect SQL execution efficiency are join, order BY, DISTINCT, partitation by, and these operations are generally associated with table structure design. The effect of function parsing on SQL execution speed should be negligible relative to the degree of efficiency impact.

Another point, for some of the default values of the assignment, like the above SQL, default to the current date and so on, in fact, you can completely mention the service layer or controller layer to do the processing, in the MyBatis should be less use these judgments. Because, in this case, it is difficult to do cache processing. If StartDate is empty and dynamic sysdate is used on the SQL, it is not possible to determine what the key should be for the cached StartDate date. So the parameters are best handled before passing to MyBatis, so that the mybatis layer can also reduce some if choose statements, and also facilitate cache processing.

Of course, not using the IF choose is not absolute, sometimes in order to optimize the SQL, you have to use if to solve, such as like statements, of course, it is generally not recommended to use like, but if there is a use of the scene, as far as possible, do not need to use when to remove like, To improve query efficiency. The best way to do this is to use search engines such as lucence to solve this full-text indexing problem.

In general, it is not possible to completely remove the branch of if and choose judgments, but it is recommended that the SQL native approach be used to solve some dynamic problems, rather than relying entirely on mybatis to complete the judgment of the dynamic branch, because the decision branches are too complex and difficult to maintain.

3. Replace SQL annotations with XML annotations.

MyBatis Zhongyuan SQL comments as far as possible do not retain, comments will raise some problems, if you need to use comments, you can use the XML <!----> to annotate, to ensure that the generated SQL does not exist SQL comments, thereby reducing the likelihood of problems. Another benefit of doing this is that you can distinguish annotations and SQL very clearly in the IDE.

Now to talk about the issues raised by annotations, in one of my projects, the paging component is based on MyBatis, and it's going to have a layer of select COUNT (*) rownum_ from (...) to calculate the total number of records from the SQL script you write, with another nested SELECT * From (...) where RowNum > Ronnum < 10 * 2 this way raw page information, if the last line in your script has a comment, the added part becomes part of the comment and the execution will be an error. In addition, some situations may also cause partial conditions to be ignored, such as the following:

<code class= "Hljs vbnet" style= "padding:0.5em"; margin:0px; Display:block; Color:rgb (101,123,131); Overflow-x:auto; Background:rgb (253,246,227) "><span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">SELECT</span> * <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">FROM</span> TEST <span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">WHERE</span> COL1 > <span class=" hljs-number "style=" padding:0px; margin:0px; Color:rgb (42,161,152) ">1</span>--here is the annotation <<span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">if</span> test=<span class=" hljs-string "style=" padding:0px; margin:0px; Color:rgb (42,161,152) ">" a!= null and a!= ' "</span>><span class=" Hljs-keyword "style=" padding:0px; margin:0px; Color:rgb (133,153,0) ">AND</span> COL2 = <span class=" hljs-preprocessor "style=" padding:0px; margin:0px; Color:rgb (203,75,22) ">#{a}</<span class=" Hljs-keyword "style=" padding:0px;  margin:0px ">if</span>></span></code>

Even if there is a corresponding parameter in the passed parameter, the effect will not be effective, because the following content is actually fully commented. This kind of mistake, if not through rigorous testing, is difficult to find. In general, XML annotations can be completely substituted for SQL annotations, so this behavior should be prohibited.

4. Use #{} as much as possible instead of ${}.

MyBatis try not to use ${}, as far as possible to do so is very convenient to develop, but there is a problem is that a lot of use will lead to Oracle hard resolution, slow down the database performance, the longer the database performance will be worse. For general multiple string in processing, you can refer to the following solution: http://www.myexception.cn/sql/849573.html, basically can solve most ${}.

About ${}, another misuse of the place is like, I also have a case on this side: for example, some tree menu, the node will be designed as ' 01 ', ' 0101 ', with two-bit nodes to distinguish the hierarchy, this time, if you need to query 01 nodes under all the nodes, the simplest SQL is: SELECT * from The tree WHERE ID is like ' 1% ', this SQL is understandable, because it can also use the index, so do not need special processing, direct use of the line. However, if the title of the article, you need extra attention: SELECT * from T_news_text WHERE the title like '%osc% ', this is how not to use the index, said above, it is best to use Full-text search. But if you can't do without like, you need to pay attention to the way it's used: ID-type #{id} | | '% ' rather than ID like ' ${id}% ', reducing the likelihood of hard parsing.

Some people feel the use of | | Will increase the time of Oracle processing, I do not think that Oracle is too silly, although sometimes it is really silly, there is no time to summarize Oracle silly garbage place, but a little test will know: This series, for the entire SQL parsing implementation, should be negligible.

Of course, there are some special circumstances can not handle, such as dynamic injection of column names, table names and so on. For these cases, it is more difficult to find a more convenient means. Since this situation is less likely to occur, the use of ${} will not have much effect. Of course, if you have code cleanliness, you can use the dynamic execution SQL mechanism of Oracle execute immediate, so that you can completely avoid the possibility of ${}. This will introduce a more complex model, at which point you will need to make a choice.

The most radical way to solve the problems caused by the above dynamic SQL is to use all of the stored procedures with the database native way to solve, facilitate development and debugging, of course, will also bring problems: the developer will have higher requirements, storage process management, etc., my side of the project has not been used this way, here do not do more expansion.

5. Simple use of mybatis.

MyBatis's function is relatively weak, the lack of a lot of necessary auxiliary library, string processing and so on, expansion is also difficult, and generally may be a return value for some processing. So it's best to just use it as a simple SQL configuration file, and simply an ORM framework. Do not try to do too much dynamic SQL in the MyBatis, otherwise it will cause subsequent maintenance very disgusting.

The above is a small set to introduce the MyBatis learning summary of the use of MyBatis suggestions, hope to help everyone, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!