MyBatis Study Summary (--mybatis) Several suggestions for use

The 1.Mapper layer parameter is map and is overloaded by the service layer.

Mapper because of the mechanism of the problem, can not be overloaded, 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 service layer provided externally is overloaded, However, these overloaded service methods are actually tuned to the same mapper, except that the corresponding parameters are inconsistent.

     Maybe someone would think, why not set up a map on the service layer? I personally do not recommend this, although for the sake of convenience, I also used a lot of this way in the previous project, but it will obviously cause trouble for future maintenance work. Because this will make your whole MVC dependent on the map model, this model is actually very good, easy to frame, but there is a problem: just look at the method signature, you do not know the map has the number of parameters, type, each parameter represents the meaning.  

Imagine that you only change the service layer, or the DAO layer changes, you need to understand the whole process of the map passed through the parameters, unless you comment or document is good, otherwise you have to understand each layer of code, you know what parameters are passed. That's fine for simple MVC, but if the hierarchy is complicated, the code becomes very complex, and if I add a parameter, I need to add the comments for each layer. With respect to annotations, it is more feasible to use a method signature to ensure that the code is controllable, since annotations may be outdated, but method signatures are generally less likely to be stale.

2. Minimize the difficulty of maintenance by using statements such as if choose.

MyBatis configuration of SQL, as far as possible with the if choose and other tags, can use SQL to determine the best use of SQL to judge (case when, decode, etc.), in order to maintain later. Otherwise, once SQL expands, super disgusting, if need to debug MyBatis in SQL, need to remove a lot of judgment statement, very troublesome. On the other hand, a large number of if judgments will make the generated SQL contain a large number of spaces, increase the time of network transmission, is not advisable.

and a lot of if choose statements, inevitably, each generation of SQL will be inconsistent, resulting in a large number of Oracle hard parsing, nor desirable.
Let's take a look at this sql:

SELECT* fromT_news_textWHERE 1=1<Choose><ifTest ="StartDate! = null and StartDate! =" and enddate! = null and Endate! = ""> andPublishtime >= #{startdate} andPublishtime <= #{enddate}</if><otherwise> andPublishtime >=sysdate-7  andPublishtime <=sysdate</otherwise></Choose>

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

SELECT* fromT_news_textWHEREPublishtime >=DECODE(#{startdate},NULL,sysdate-7, #{startdate}) andPublishtime <=DECODE(#{enddate},NULL,sysdate, #{enddate})

Of course, some people will think that the introduction of case When,decode will lead to the need for Oracle function parsing, 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 of SQL slowing due to function parsing. Typically, the operations that affect SQL execution efficiency are joins, ORDER by, DISTINCT, and partitation by, which are generally associated with the table structure design. The effect of function resolution on the speed of SQL execution should be negligible relative to the degree of efficiency of these.

    Another point, for some default value assignment, like the above SQL, default to the current date or something, can actually refer to the service layer or controller layer to do processing, These judgments should be used less in mybatis. Because, in this case, it is difficult to do cache processing. If startdate is empty, using dynamic sysdate on SQL, you cannot determine what the key of the cache StartDate date should be. So the parameters are best handled before being passed to MyBatis, so that the mybatis layer can also reduce some if choose statements, and also facilitate cache processing.

Of course not to use if choose is not absolute, sometimes in order to optimize SQL, 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 when not needed to remove like, such as query article title, To improve query efficiency. The best way is to use search engines such as lucence to solve this full-text indexing problem.

In general, if and choose the branch is not completely removed, but it is recommended to use SQL Native way to solve some dynamic problems, rather than relying entirely on mybatis to complete the dynamic branch of judgment, because the branch is too complex and difficult to maintain.
3. Replace the SQL comment with an XML comment.

MyBatis Zhongyuan SQL comments try not to keep, comments can cause some problems, if you need to use comments, you can use <!----> in the XML to comment, to ensure that SQL comments are not present in the generated SQL, thereby reducing the likelihood of problems. One of the benefits of doing this is that you can clearly distinguish between annotations and SQL in the IDE.

Now let's talk about the problem with annotations, and in a project that I do, the paging component is based on MyBatis, and it sets a layer of select count (*) Rownum_ from (...) to calculate the total number of records outside of the SQL script you write. There is also another nested SELECT * FROM (...) where ROWNUM > Ronnum < 10 * 2 this way raw page information, if the last line in your script appears comments, then the added part will be part of the comment, execution will be error. In addition, in some cases, some conditions may be ignored, as in the following cases:

SELECT* fromTESTWHERECOL1 >1--Here is the comment <iftest="A! = null and a! =" "> andCOL2 =#{a}</if>

even if there is a corresponding parameter in the passed in parameter, it will not actually produce an effect, because the subsequent content is actually fully annotated. Such errors, if not rigorously tested, are difficult to find. In general, XML annotations can be completely substituted for SQL comments, so this behavior should be prohibited.
4. Use #{} instead of ${} whenever possible .

    mybatis try not to use ${}, as far as possible to do so is easy to develop, but there is a problem, is that a large number of uses will lead to the hard parsing of Oracle, slow database performance, The longer you run, the worse the database performance will be. For general multiple string in processing, you can refer to the following solution: Http://www.myexception.cn/sql/849573.html, the basic can solve most ${}.

    about ${}, another misuse of the place is like, I also have a case: 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 all nodes under the 01 node, the simplest SQL is: SELECT * from the TREE WHERE ID 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 article title, you need to pay extra attention: SELECT * from T_news_text WHERE the title like '%osc% ', which is not used in the index, it said, it is best to use full-text search. But if you can not leave like, you need to pay attention to How to use: ID as #{id} | | '% ' rather than ID like ' ${id}% ', reduces the possibility of hard parsing.

     people feel the use of | | Will increase the time of Oracle processing, I think not to see the oracle too silly, although sometimes very silly, there is no time to summarize Oracle silly place, but a little test will know: this concatenation, for the entire SQL parsing execution, should be minimal.

Of course, there are some special cases are not handled, such as dynamic injection column name, table name 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 ${} does not have much effect. Of course, if you have code cleanliness, you can use Oracle's dynamic Execute SQL mechanism, execute immediate, so you can completely avoid the possibility of ${}. This will introduce a more complex model, and this time, you will need to choose.

in response to the above problems caused by dynamic SQL, the most radical way is to take all the stored procedures, the database in the original way to solve, easy to develop debugging, of course, will also bring problems: the developers will have higher requirements, the management of stored procedures and so on, my side of the project has not been used this way, There's no more unfolding here.
5. Simply use the MyBatis.

MyBatis function is relatively weak, the lack of a lot of necessary auxiliary library, string processing and so on, the extension is also more difficult, it is generally possible to do some processing of the return value. It is therefore better to simply use it as a simple SQL configuration file, as well as an ORM framework. Do not try to do too much dynamic SQL in MyBatis, otherwise it will cause the subsequent maintenance to be very disgusting.

MyBatis Study Summary (--mybatis) Several suggestions for use