Spring Boot MyBatis Upgrade-annotations-dynamic SQL (if test)-Scenario two: @Provider (8)

1) Dynamic language annotations
(2) @Provider the use of ideas
(3) @SelectProvider small test sledgehammer
(4) @SelectProvider emerged
(5) @SelectProvider through
(6) @InsertProvider brother deserve
(7) @UpdateProvider you add me to change
(8) Delete @DeleteProvider unhappy

Next look at the specific content:

(1) Dynamic language annotations

For creating dynamic check-in languages. MyBatis provides multiple annotations such as @InsertProvider, @UpdateProvider, @DeleteProvider, and @selectprovider, which are built in dynamic languages and let MyBatis execute those languages.

(2) @Provider the use of ideas

For MyBatis to provide a few @provider, the most important parameter is the type, that is, the SQL Class Calss object, the other is the corresponding method name, we look at Selectprovider source code:

Java code  Collection Code @documented  @Retention (retentionpolicy.runtime)  @Target (Elementtype.method)    Public @interface selectprovider {    Class<?> type ();       String method ();  }  

@Documented  @Retention (retentionpolicy.runtime)  @Target (elementtype.method)    public @interface selectprovider {    Class<?> type ();       String method ();  }  

So to implement the dynamic SQL query, then the general idea is to write a sqlprovider, such as: Demosqlprovider, in this method to return an SQL statement. Then use the @selectprovider annotation in the Mapper class to specify the provider class and the corresponding SQL method.

Next we'll solve the previous blog question:

Problem: There is a table with fields such as Id,name,email, there is a query request: We hope that if the name is not NULL, then as a condition, otherwise do not as a condition, if the email is not NULL, then as a condition, otherwise not as a condition.

Then take a look at how to use the @selectprovider broken.

(3) @SelectProvider small test sledgehammer

Let's start by writing a demosqlprovider with the following code:

Package Com.kfit.demo.mapper;     Import Com.kfit.demo.bean.Demo;  Public classDemosqlprovider {/** * Query statement. * @param demo * @return*/       PublicString Select5 (Demo demo) {StringBuffer SQL=NewStringBuffer ("Select *from Demo where 1=1"); if(Demo.getname ()! =NULL) {sql.append ("and Name=#{name}"); }         if(Demo.getemail ()! =NULL) {sql.append ("and Email=#{email}"); }         returnsql.tostring (); }       }  

Package Com.kfit.demo.mapper;     Import Com.kfit.demo.bean.Demo;  Public classDemosqlprovider {/** * Query statement. * @param demo * @return*/       PublicString Select5 (Demo demo) {StringBuffer SQL=NewStringBuffer ("Select *from Demo where 1=1"); if(Demo.getname ()! =NULL) {sql.append ("and Name=#{name}"); }         if(Demo.getemail ()! =NULL) {sql.append ("and Email=#{email}"); }         returnsql.tostring (); }       }  

Add a query method to the Demomapper:

@SelectProvider (type=demosqlprovider.class,method= "Select5") public  list<demo> Select5 (Demo demo);  

　　

@SelectProvider (Type=demosqlprovider.  Class, method="select5")  public list<demo> select5 ( Demo demo);  

@selectprovider is used here, not @select.

Access 1:HTTP://127.0.0.1:8080/SELECT4 will return all data, dynamic SQL is:

SELECT * from Demo WHERE 1=1

1. SELECT * from Demo WHERE 1=1

Access to 2:http://127.0.0.1:8080/select4?name= Harry returns name= Harry data, dynamic SQL is:

1. SELECT * from Demo WHERE 1=1 and name=?
2. SELECT * from Demo WHERE 1=1 and name=?

Access 3:http://127.0.0.1:8080/select4?name= Harry &[email protected] will return the data for name= Harry and [email protected], dynamic SQL is:

1. SELECT * from Demo WHERE 1=1 and name=?  and email=?
2. SELECT * from Demo WHERE 1=1 and name=?  and email=?

(4) @SelectProvider emerged

The code above is written directly in plain SQL, with a relatively poor readability, and MyBatis provides the SQL class (Org.apache.ibatis.jdbc.SQL) to make the code look more meaningful.

To add a method to Demosqlprovider:

/** * query statement. Use SQL * @param demo * @return*/       PublicString SELECT6 (Final demo demo) {return NewSQL () {{SELECT} ("Id,name,email"); From ("Demo"); if(Demo.getname ()! =NULL) {WHERE ("Name=#{name}"); }             if(Demo.getemail ()! =NULL) {WHERE ("Email=#{email}");      }}}.tostring (); }  

Add code to Dempmapper:

@SelectProvider (Type=demosqlprovider.  Class, method="select6")   public list<demo> select6 ( Demo demo);  

(5) @SelectProvider through

Thought everything was good, happy not, so, the code, in the query code to join:

Pagehelper.startpage (1, 2); The entire code is as follows:

 @RequestMapping  " /SELECT6   " )  public  List<demo> SELECT6 (Demo demo) {pagehelper.startpage ( 1 , 2  );       return   DEMOSERVICE.SELECT6 (demo); }  

 @RequestMapping  " /SELECT6   " )  public  List<demo> SELECT6 (Demo demo) {pagehelper.startpage ( 1 , 2  );       return   DEMOSERVICE.SELECT6 (demo); }  

   Run, Access: http://127.0.0.1:8080/SELECT6 finished, what the hell is this:is-for'  providertakesparameterobject'in'class Org.apache.ibatis.builder.annotation.ProviderSqlSource'       the above problems, Due to the Pagehelper version we are using, the upgrade version is available. 

Run, Access: Http://127.0.0.1:8080/select6 finished, what the hell is this:

Nested exception is Org.apache.ibatis.reflection.ReflectionException:There are no getter for property named ' Providertake Sparameterobject ' in ' Class Org.apache.ibatis.builder.annotation.ProviderSqlSource '

The above problem is due to the Pagehelper version we use, the upgrade version can be.

The original version is:

XML code  Collection code <dependency>      <groupId>com.github.pagehelper</groupId>      < Artifactid>pagehelper</artifactid>  <version>4.1.  0</version>  </dependency>  [XML] View plain copy

Upgrade to:

<dependency>      <groupId>com.github.pagehelper</groupId>      <artifactid>pagehelper </artifactId>  <version>4.2.1</version>  </dependency>  

　　

Looks like: 4.1.5 support @selectprovider paged query (not verified).

(6) @InsertProvider brother deserve

After the most troublesome query has been done, this is easy,

Add the following code to the Demosqlprovider:

/** * query statement. Use SQL * @param demo * @return*/       PublicString Save3 (Final demo demo) {return NewSQL () {{Insert_into ("Demo"); //multiple notation. Into_columns ("name","Email"); Into_values ("#{name}","#{email}"); //conditional notation. //if (demo.getname () = null) {//VALUES ("name", "#{name}"); //         }  //if (demo.getemail () = null) {//VALUES ("email", "#{email}"); //         }  }}.tostring (); }  

Add the following code to the Demomapper:

Ava code  Collection Code @insertprovider (type=demosqlprovider.  Class, method="save3")  @Options (keyproperty="ID  ", keycolumn="ID", usegeneratedkeys=true)    Public void Save3 (demo demo);  

(7) @UpdateProvider you add me to change

The code in Demosqlprovider is as follows:

Java Code Collection code/** * @param demo * @return*/       PublicString Update2 (Final demo demo) {return NewSQL () {{UPDATE] ("Demo"); //conditional notation.            if(Demo.getname ()! =NULL) {SET ("Name=#{name}"); }             if(Demo.getemail ()! =NULL) {SET ("Email=#{email}"); } WHERE ("Id=#{id}");      }}.tostring (); }  

Code in Demomapper:

yards  Collection Code @updateprovider (type=demosqlprovider.  Class, method="update2")  publicint update2 ( Demo demo);  

(8) Delete @DeleteProvider unhappy

Demosqlprovider Code:

Java code  Collection code */* * *       @param demo      * @return      */public       String Delete2 () {         returnnew  SQL () {{             delete_from ("  demo");             WHERE ("id=#{id}");}         }. ToString ();      }  

Code in Demomapper:

@UpdateProvider (Type=demosqlprovider.  Class, method="delete2")  publicint delete2 (  int ID);  

Spring Boot MyBatis Upgrade-annotations-dynamic SQL (if test)-Scenario two: @Provider (8)