Introduction: What is MyBatis?
(Formerly iBatis) MyBatis is a persistence 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 the result set. MyBatis can use simple XML or annotations for configuration and original mapping, mapping interfaces and Java's POJOs (Plain Old Java Objects) into records in the database.
1. The Mapper layer parameter is Map, and the Service layer is responsible for overloading.
Due to mechanism problems, the map cannot be overloaded. The parameters are generally set to Map, but this will make the parameters blurry. If you want to make the code clear, you can achieve the purpose of overloading through the service layer. The service layer provided to the outside is overloaded, but these overloaded Service methods are actually to tune the same mapper, but the corresponding parameters are not consistent.
Maybe some people may wonder, why not set it to Map in the Service layer? I personally do not recommend this. Although I have adopted this method in my previous projects for convenience, it will obviously cause trouble for future maintenance work. Because doing this will make your entire MVC rely on the Map model. This model is actually very good and convenient to build a framework, but there is a problem: just looking at the method signature, you don’t know the number, type, and the meaning of each parameter represented by the Map.
Just imagine, if you only change the Service layer or the DAO layer, you need to be clear about the parameters passed by the Map in the entire process. Unless you comment or have good documentation, you must understand the code of each layer clearly before you know which parameters are passed. For simple MVC, that's fine, but if the level is complex, the code will become extremely complex, and if I add a parameter, I need to add the comments for each layer. Compared to comments, it is more feasible to use method signatures to ensure this code controllability, because comments may be outdated, but method signatures are generally unlikely to be stale.
2. Try to use if choice and other statements as little as possible to reduce the difficulty of maintenance.
When configuring SQL in Mybatis, try to use less labels such as if choose. If SQL can be used to determine the judgment, try to use SQL (CASE WHEN, DECODE, etc.) for later maintenance. Otherwise, once SQL bloats, it is super nauseous. If you need to debug SQL 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 will cause the generated SQL to contain a large number of spaces, which will increase the network transmission time, which is also not desirable.
Moreover, a large number of if choose statements, inevitably, the generated SQL will be inconsistent each time, which will lead to a large number of hard parsing of ORACLE, which is also not advisable.
Let's take a look at SQL like this:
<code style="padding:0.5em; margin:0px; display:block; color:rgb(101,123,131); overflow-x:auto; background:rgb(253,246,227)"><span style="padding:0px; margin:0px"><span style="padding:0px; margin:0px; color:rgb(133,153,0)">SELECT</span> * <span style="padding:0px; margin:0px; color:rgb(133,153,0)">FROM</span> T_NEWS_TEXT <span style="padding:0px; margin:0px; color:rgb(133,153,0)">WHERE</span> <span style="padding:0px; margin:0px; color:rgb(42,161,152)">1</span> = <span style="padding:0px; margin:0px; color:rgb(42,161,152)">1</span> < <span style="padding:0px; margin:0px; color:rgb(133,153,0)">1</span> < <span style="padding:0px; margin:0px; color:rgb(133,153,0)">choose</span>> < <span style="padding:0px; margin:0px; color:rgb(133,153,0)">if</span> test =<span style="padding:0px; margin:0px; color:rgb(42,161,152)">"startdate != null and startdate != '' and enddate != null and endate != ''"</span> <span style="padding:0px; margin:0px; color:rgb(133,153,0)">AND</span> PUBLISHTIME >= #{startdate} <span style="padding:0px; margin:0px; color:rgb(133,153,0)">AND</span> PUBLISHTIME <= #{enddate} </ <span style="padding:0px; margin:0px; color:rgb(133,153,0)">if</span> <otherwise> <span style="padding:0px; margin:0px; color:rgb(133,153,0)">AND</span> PUBLISHTIME >= <span style="padding:0px; margin:0px; color:rgb(133,153,0)">SYSDATE</span> - <span style="padding:0px; margin:0px; color:rgb(42,161,152)">7</span> <span style="padding:0px; margin:0px; color:rgb(133,153,0)">AND</span> PUBLISHTIME <= <span style="padding:0px; margin:0px; color:rgb(133,153,0)">SYSDATE</span> </otherwise></ <span style="padding:0px; margin:0px; color:rgb(133,153,0)">SYSDATE</span> </otherwise></ <span style="padding:0px; margin:0px; color:rgb(133,153,0)">choose</span> ></span>Such if judgment is actually completely unnecessary. We can simply use DECODE to solve the default value problem:
<code style="padding:0.5em; margin:0px; display:block; color:rgb(101,123,131); overflow-x:auto; background:rgb(253,246,227)"><span style="padding:0px; margin:0px"><span style="padding:0px; margin:0px; color:rgb(133,153,0)">SELECT</span> * <span style="padding:0px; margin:0px; color:rgb(133,153,0)">FROM</span> T_NEWS_TEXT <span style="padding:0px; margin:0px; color:rgb(133,153,0)">WHERE</span> PUBLISHTIME >= <span style="padding:0px; margin:0px; color:rgb(133,153,0)">DECODE</span>(#{startdate},<span style="padding:0px; margin:0px">NULL</span>,<span style="padding:0px; margin:0px; color:rgb(133,153,0)">SYSDATE</span>-<span style="padding:0px; margin:0px; color:rgb(42,161,152)">7</span>, #{startdate}) <span style="padding:0px; margin:0px; color:rgb(133,153,0)">AND</span> PUBLISHTIME <= <span style="padding:0px; margin:0px; color:rgb(133,153,0)">DECODE</span>(#{enddate},<span style="padding:0px; margin:0px">NULL</span>,<span style="padding:0px; margin:0px">NULL</span>,<span style="padding:0px; margin:0px; margin:0px; color:rgb(133,153,0)">SYSDATE</span>,#{enddate})</span></code>Of course, some people may think that introducing CASE WHEN and DECODE will require ORACLE function parsing, which will slow down SQL execution time. Interested students can go back and do a test to see if there will be a big impact. As far as personal experience is concerned, I did not find any slowdown in SQL due to function parsing. Generally, operations such as JOIN, ORDER BY, DISTINCT, PARTITATION BY, etc., which are generally closely related to table structure design. Compared with the degree of efficiency impact of these, the impact of function parsing on SQL execution speed should be negligible.
Another point is that for some default value assignments, like the SQL above, defaults to the current date, etc., you can actually completely mention the Service layer or Controller layer for processing. These judgments should be used less in Mybatis. Because, in this case, it is difficult to cache processing. If startdate is empty and using dynamic SYSDATE on SQL, it is impossible to determine what the key of the cache startdate date should be. Therefore, it is best to handle the parameters before being passed to Mybatis, so that the Mybatis layer can also reduce some if choose statements, and it is also convenient for cache processing.
Of course, it is not absolute not to use if choose. Sometimes, in order to optimize SQL, if has to be solved, such as LIKE statements. Of course, LIKE is generally not recommended. However, if there are scenarios for use, remove LIKE whenever you don’t need to use it, such as querying the article title to improve query efficiency. The best way is to use search engines such as lucence to solve this problem of full-text indexing.
In general, if and choose to judge the branch is impossible to completely remove, but it is recommended to use SQL native method to solve some dynamic problems, rather than relying entirely on Mybatis to complete the judgment of dynamic branches, because the judgment branch is too complex and difficult to maintain.
3. Replace SQL comments with XML comments.
Try not to retain the comments of Mybatis Central SQL. Comments will cause some problems. If you need to use comments, you can use <!-- --> in XML to annotate to ensure that there will be no SQL annotations in the generated SQL, thereby reducing the possibility of problems. Another advantage of doing this is that it can clearly distinguish annotations from SQL in the IDE.
Now let’s talk about the problems caused by comments. In a project I’m working on, the pagination component is based on Mybatis. It will put another layer of SELECT COUNT(*) ROWNUM_ FROM (......) outside the SQL script you wrote to calculate the total number of records. At the same time, there is another nested SELECT * FROM(...) WHERE ROWNUM > 10 AND RONNUM < 10 * 2 generate paging information. If there is a comment on the last line in your script, the added part will become part of the comment and an error will be reported. In addition, some conditions may also cause some conditions to be ignored, such as the following:
<code style="padding:0.5em; margin:0px; display:block; color:rgb(101,123,131); overflow-x:auto; background:rgb(253,246,227)"><span style="padding:0px; margin:0px; color:rgb(133,153,0)">SELECT</span> * <span style="padding:0px; margin:0px; color:rgb(133,153,0)">FROM</span> TEST <span style="padding:0px; margin:0px; color:rgb(133,153,0)">WHERE</span> COL1 > <span style="padding:0px; margin:0px; color:rgb(42,161,152)">1</span> -- Here is the comment<<span style="padding:0px; margin:0px; color:rgb(133,153,0)">if</span> test=<span style="padding:0px; margin:0px; color:rgb(42,161,152)">"a != null and a != ''"</span>><span style="padding:0px; margin:0px; color:rgb(42,161,152)">"a != null and a != ''"</span>><span style="padding:0px; margin:0px; color:rgb(42,161,152)">"a != null and a != ''"</span>><span style="padding:0px; margin:0px; color:rgb(133,153,0)">AND</span> COL2 = <span style="padding:0px; margin:0px; color:rgb(203,75,22)">#{a}</<span style="padding:0px; margin:0px">if</span>></span></code>Even if there are corresponding parameters in the passed parameters, there will be no effect in fact, because the following content is actually completely commented. This kind of error is difficult to detect without rigorous testing. Generally speaking, XML comments can completely replace SQL comments, so this behavior should be prohibited.
4. Use #{} whenever possible, not ${}.
Try not to use ${} in Mybatis. It is very convenient to do this. However, there is a problem that a large amount of use will cause hard parsing of ORACLE, slowing down the database performance. The longer the database performance will be, the worse the database performance will be. For general processing of multiple string INs, you can refer to the following solution: http://www.myexception.cn/sql/849573.html, which can basically solve most of the ${}.
Regarding ${}, another misuse is LIKE. I have another case here: for example, some tree menus, nodes will be designed as '01', '0101', and two-bit nodes are used to distinguish hierarchies. At this time, if you need to query all nodes under node 01, the simplest SQL is: SELECT * FROM TREE WHERE ID LIKE '01%'. This kind of SQL is actually understandable, because it can also use indexes, so it does not require special processing, just use it directly. But if it is the title of the article, you need to pay extra attention: SELECT * FROM T_NEWS_TEXT WHERE TITLE LIKE '%OSC%', this is no way to use indexing. As mentioned above, it is best to use full text search. But if you cannot do without LIKE, you need to pay attention to the use method: ID LIKE #{ID} || '%' instead of ID LIKE '${ID}%' to reduce the possibility of hard parsing.
Some people think that using || will increase the time for ORACLE processing. I think don't take ORACLE too stupid. Although sometimes it is really stupid, you can summarize the stupid and not garbage when you have time, but after a little test, you will know that this connection method should be very slim for the parsing and execution of the entire SQL.
Of course, there are some special cases that cannot be handled, such as dynamic injection of column names, table names, etc. For these situations, it is difficult and no more convenient means are found. Since this situation is less likely to occur, using ${} will not have any big impact. Of course, if you have a code morbidity, you can use ORACLE's dynamic SQL execution mechanism Execute immediate, so that you can completely avoid the possibility of ${}. This will introduce more complex models, and at this time, you need to make choices.
In response to the problems caused by dynamic SQL above, the most radical way is to use stored procedures and solve them in a database native way to facilitate development and debugging. Of course, it will also bring problems: there will be higher requirements for developers, management of stored procedures, etc. My project has not adopted this method, so I will not expand more here.
5. Simple use of Mybatis.
Mybatis's function is relatively weak, and it lacks many necessary auxiliary libraries, string processing, etc., and it is also difficult to expand, so it is generally possible to process some returns. Therefore, it is best to just use it as a simple SQL configuration file and a simple ORM framework. Don't try to do too much dynamic SQL in Mybatis, otherwise it will cause subsequent maintenance to be very disgusting.
The above is the Mybatis learning summary that the editor introduced to you. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to Wulin.com website!