Spring Boot-mybatis Three kinds of dynamic SQL (5)

Script SQLXML Configuration Dynamic SQL I will not talk about, I am interested to understand, the following is a <script> way to copy it, with annotations to achieve. Applies to XML configuration transitions to annotation configuration

@Select ("<script>select * from user <if test=\" id!=null \ ">where id = #{id} </if></scr ipt>")    public

Obviously, writing XML in Java is poor in readability and maintainability, especially when SQL is long, which is painful to write.

It is not possible to write implementations in the Sqldao interface constructed in the method, so the internal classes are borrowed to generate dynamic SQL. There are also corresponding @insertprovider, @UpdateProvider, @DeleteProvider

@Mapper Public InterfaceMybatisdao {//Use the Finduserbyid method of the Userdaoprovider class to generate SQL@SelectProvider (type = Userdaoprovider.class, method ="Finduserbyid")       PublicList<user>Finduserbyid (user user); classUserdaoprovider { Publicstring Finduserbyid (user user) {string sql="SELECT * from user"; if(User.getid ()! =NULL) {SQL+="WHERE id = #{id}"; }              returnSQL; }      }  

This is clearer than <script>, and is suitable for scenarios where query statements are not very long and conditions are not many, and SQL is straightforward. But when it comes to writing long SQL, it's also painful to splice SQL.

Structured SQL

 PublicString Finduserbyid (user user) {return NewSQL () {{SELECT} ("Id,name"); SELECT (" Other"); From ("User"); if(User.getid ()! =NULL) {WHERE ("id = #{id}"); }                      if(User.getname ()! =NULL) {WHERE ("name = #{name}"); }                  //from this ToString, it can be seen that the internal use of efficient StringBuilder implementation of SQL splicing}}.tostring (); }      

This is to transform the previous inner class. Select: Indicates the field to query, if one line is not finished, you can write a select in the second row, and the two select will be intelligently merged without repeating from and where: As with SELECT, you can write multiple parameters, It can also be reused in multiple lines, and will eventually be intelligently merged without an error. This makes the SQL structure clear when the statement is suitable for writing long SQL. Easy to maintain, high readability. But this automatically generated SQL, like Hibernate, does nothing to implement SQL for some complex statements. So it is necessary to consider what kind of dynamic SQL to use according to the real situation. The above example is only the most basic usage: More detailed usage, you can refer to the MyBatis Chinese network of special introduction http://www.mybatis.org/mybatis-3/zh/ Statement-builders.htmllist error in dynamic SQL, sometimes the batch data to be processed, will inevitably use the list as a parameter

@SelectProvider (type = Userdaoprovider.  Class"find")      public list<map> Find (list list);                  class Userdaoprovider {          public String find (List list) {  

This is the simplest list parameter, but will report a parameter error at run time. This is caused by mybatis internal mechanism, its parameters need to be key/value structure, when encountered here is not key/value structure of the list, MyBatis will itself convert it to key/value structure, key is his name "list", Value is the list of his values, and the correct reference to the map using the Key/value structure is as follows

@SelectProvider (type = Userdaoprovider.  Class"find")      public list<map> Find (list list);                  class Userdaoprovider {          public  String find (Map map) {              = (List) map.  Get("list");  

Spring Boot-mybatis Three kinds of dynamic SQL (5)