MyBatis learning Summary (16) several suggestions for Mybatis

1. The Mapper layer parameter is Map, and the Service layer is responsible for reload. Mapper cannot be overloaded due to Mechanism issues. Generally, the parameter is set to Map, but this will blur the parameter. To make the code clearer, you can use the service layer to achieve the purpose of overloading, the Service layer provided externally is overloaded, but these overloaded Service methods are actually the same

1. The Mapper layer parameter is Map, and the Service layer is responsible for reload. Mapper cannot be overloaded due to Mechanism issues. Generally, the parameter is set to Map, but this will blur the parameter. To make the code clearer, you can use the service layer to achieve the purpose of overloading, the Service layer provided externally is overloaded, but these overloaded Service methods are actually the same

1. The Mapper layer parameter is Map, and the Service layer is responsible for reload.

Mapper cannot be overloaded due to Mechanism issues. Generally, the parameter is set to Map, but this will blur the parameter. To make the code clearer, you can use the service layer to achieve the purpose of overloading, the Service layer provided externally is overloaded, but these overloaded Service methods call the same Mapper, but the corresponding parameters are inconsistent.

Some may wonder why Map is not configured on the Service layer? I personally do not recommend this method. Although this method has been widely used in my previous projects for convenience, it will obviously cause trouble for future maintenance work. Because doing so will make the entire MVC dependent on the Map model, this model is actually very good and convenient to build the framework, but there is a problem: just look at the method signature, you do not know the number, type, and meaning of each parameter in Map.

If you only change the Service layer or DAO layer, you need to understand the parameters passed by Map in the whole process, unless you note or the document is good, otherwise, you must understand the code at each layer before you can know which parameters are passed. For simple MVC, it would be fine, but if the hierarchy is complex, the code will become abnormal and complex. If I add a parameter, I need to add comments for each layer. Compared with annotations, it is more feasible to use method signatures to ensure code controllability, because annotations may be outdated, but method signatures are generally not outdated.

2. Use if choose and other statements as little as possible to reduce the maintenance difficulty.

WHEN configuring SQL for Mybatis, use as few labels as possible, such as if choose, and use SQL to judge (CASE WHEN, DECODE, etc.) for later maintenance. Otherwise, once the SQL statement expands, it is super disgusting. If you need to debug the SQL statement in Mybatis, you need to remove a large number of judgment statements, which is very troublesome. On the other hand, a large number of if judgments may cause the generated SQL statement to contain a large number of spaces and increase the network transmission time.

Moreover, a large number of if choose statements are inevitably inconsistent with the SQL statements generated each time, resulting in a large amount of hard parsing and unavailability of ORACLE.
Let's take a look at this SQL:

SELECT * FROM T_NEWS_TEXT WHERE 1 = 1< choose>< if test ="startdate != null and startdate != '' and enddate != null and endate != ''">AND PUBLISHTIME >= #{startdate} AND PUBLISHTIME <= #{enddate}
  
  
   AND PUBLISHTIME >= SYSDATE - 7 AND PUBLISHTIME <= SYSDATE
  
  

Such an if judgment is completely unnecessary. We can simply use DECODE to solve the default value problem:

SELECT * FROM T_NEWS_TEXT WHERE PUBLISHTIME >= DECODE(#{startdate},NULL,SYSDATE-7, #{startdate}) AND PUBLISHTIME <= DECODE(#{enddate},NULL,SYSDATE,#{enddate})

Of course, some people may think that the introduction of case when and DECODE will lead to ORACLE function parsing, which will slow the SQL Execution time. If you are interested, you can go back and perform a test, check whether there is a major impact. In my personal experience, I have not found any slow SQL statement due to function parsing during my development. Generally, JOIN, order by, DISTINCT, and partitation by operations affect SQL Execution efficiency. These operations are generally associated with table structure design. The effect of function parsing on SQL Execution speed is negligible.

In addition, for some default values, such as the preceding SQL statement, the default value is the current date or something. In fact, the Service layer or Controller layer can be fully mentioned for processing, in Mybatis, these judgments should be used less. Because, in this case, it is difficult to cache. If startdate is empty and dynamic SYSDATE is used in SQL, you cannot determine what the key should be cached for startdate. Therefore, it is best to process the parameters before they are passed to Mybatis, so that the Mybatis layer can also reduce some if choose statements and facilitate cache processing.

Of course, it is not absolute to not use if choose. Sometimes we have to use if to optimize SQL statements. For example, LIKE statements are not recommended, remove LIKE whenever possible, such as querying the article title to improve query efficiency. The best way is to use a search engine such as lucence to solve this full-text index problem.

In general, it is impossible to completely remove the if and choose judgment branches. However, we recommend that you use the native SQL method to solve some dynamic problems, instead of relying entirely on Mybatis to complete the judgment of dynamic branches, it is difficult to maintain the branch because it is too complicated.
3. Replace SQL comments with XML comments.

In Mybatis, do not retain original SQL comments as much as possible. Comments may cause some problems. If you need to use annotations, you can use them in XML. To ensure that there is no SQL comment in the generated SQL, thus reducing the possibility of problems. Another benefit of doing so is that annotations and SQL can be clearly distinguished in IDE.

Now let's talk about the issues caused by annotations. In a project I am working on, the paging component is based on Mybatis, and it will set another layer of select count (*) outside the SQL script you write (*) ROWNUM _ FROM (....) calculates the total number of records and has another nested SELECT * FROM (...) where rownum> 10 and ronnum <10*2 generate paging information in this way. If the comment appears in the last line of your script, the added part will become part of the comment, an error is reported when the command is executed. In addition, some conditions may be ignored in some cases, as shown below:

SELECT * from test where COL1> 1 -- comment here
  
   
AND COL2 = # {}
  

Even if there is a corresponding parameter in the input parameter, it will not actually produce results, because the subsequent content is actually completely commented out. This kind of error is hard to find if it is not strictly tested. Generally, XML comments can replace SQL comments. Therefore, this behavior should be disabled.
4. Try to use # {} instead of $ {}.

In Mybatis, do not use $ {} as much as possible. This is very convenient for development, but there is a problem that a large amount of use will lead to hard parsing in ORACLE, slowing down database performance and running longer, the worse the database performance. For the processing of multiple strings IN, you can refer to the following solution: http://www.myexception.cn/ SQL /849573.html. basically, you can solve the problem {}.

Another misuse of $ {} is LIKE. I have another case: for example, in some tree menus, the node is designed to be '01', '123 ', two nodes are used to differentiate the layers. In this case, if you need to query all the nodes under Node 01, the simplest SQL statement is: SELECT * FROM TREE WHERE ID LIKE '123 ', this kind of SQL is actually understandable, because it can also use indexes, so it does not need special processing, just use it directly. However, if it is the TITLE of the article, you need to pay extra attention: SELECT * FROM T_NEWS_TEXT where title like '% OSC %'. How can this problem be solved without using indexes? As mentioned above, full-text search is recommended. However, if you do not need LIKE, pay attention to the method used: id like # {ID} | '%' instead of id like '$ {ID} %' to reduce the possibility of hard parsing.

Some people think that using | will increase the ORACLE processing time. I don't think it should be too silly to read ORACLE. Although sometimes it is really silly, you can summarize ORACLE's silly places when you are free, however, we can know with a little test: This method of concatenation should be minimal for the parsing and execution of the entire SQL statement.

Of course, there are some special cases that cannot be handled, such as dynamic injection of column names and table names. In these cases, it is difficult to find a convenient method. Because this situation is less likely to occur, the use of $ {} does not have much impact. Of course, if you have code cleansing, You can Execute immediate using the dynamic SQL Execution mechanism of ORACLE, which can completely avoid the possibility of $. This will introduce a complicated model. At this time, you need to choose between them.

The most radical way to solve the problems caused by the above dynamic SQL statements is to use all the stored procedures and use the native method of the database to facilitate development and debugging. Of course, this will also cause problems: there are higher requirements for developers and Management of stored procedures. I have never used this method for projects here. I will not do more here.
5. Simply use Mybatis.

The function of Mybatis is relatively weak. It lacks many necessary auxiliary libraries, string processing, and so on. expansion is also difficult. In general, the return value may be processed. Therefore, it is best to use it as a simple SQL configuration file and a simple ORM framework. Do not try to do too many dynamic SQL statements in Mybatis. Otherwise, the subsequent maintenance will be very disgusting.