Detailed introduction to the extension and application of sqlmapper configuration in mybatis (1)

I struggled for several nights to debug the program, wrote several blogs, and finally established the extension mechanism of mybatis configuration. Although the extension mechanism is important, if there is no really practical extension function, it is at least not so encouraging. This blog will give some examples of extension.

The reason for studying the source code this time is the compatibility between Oracle and MySQL databases. For example, double vertical bars are used as connectors in Oracle and concat function is used in MySQL; For example, Oracle can use the decode function, while MySQL can only use the standard case when; For another example, delete form table where field1 in (select field1 form table where field2 =?) can be executed in Oracle, But exceptions will be thrown in mysql, and so on.

Let's start by solving these compatibility problems. First, you need to add the configuration related to database identification in the configuration:

1、 Connector problem

1. Write SQL configuration function implementation class

2, register in the static block of SchemaHandlers class, or register the method of calling SchemaHandlers in the initialization class.

In addition to registering concatsqlconfigfunction, the above code also has some other registration codes, which are given here and omitted below.

3. Modify sqlmapper configuration

4. Writing Dao interface classes

5. Writing test classes

6. Run the following in MySQL and H2 respectively (adjust the mybatis log level to trace)

(1)MysqL

(2)H2

As you can see, the compatibility problem of connectors has been solved.

In addition, we also found that it is troublesome to write when using the like keyword. Let's give it a new set of SQL configuration functions:

Here, a set of SQL configuration functions are defined, including left similarity, right similarity and middle similarity matching, and SQL configuration functions can also be nested. Therefore, the configuration file of sqlmapper is simplified to:

The results are exactly the same.

If you still feel troublesome, because param_ Name and paramname are hump type correspondences. You can even add a fieldlike function and modify the configuration to

If combined with the data dictionary, the configuration of JDBC type can also be generated automatically:

In this case, if there are multiple parameters, there will be no ambiguity (or a new configuration function $likes {} is defined to eliminate ambiguity), so multiple conditions can be simplified to:

Of course, there are more mining simplifications, which are not only the scope of compatibility, so there will be no further development here.

2、 Decode function / Case WHEN

The code function in Oracle is very convenient. The syntax is as follows:

Code (condition, value 1, return value 1, value 2, return value 2,... Value n, return value n [, default])

Equivalent standard writing:

Now let's implement a $decode configuration function:

Then register with schemahandlers and modify the configuration in sqlmapper:

The tests are as follows:

(1) H2 (replace Oracle with H2)

(2) In MySQL

The above is a detailed introduction to the expansion and application of sqlmapper configuration in mybatis introduced by Xiaobian (1). I hope it will be helpful to you. If you have any questions, please leave me a message, and Xiaobian will reply to you in time. Thank you very much for your support for the programming tips website@ H_ 403_ 157@