Mybatis Three implementation methods using global variables

Mybatis uses global variables

1. Overview

When we encounter a statistical requirement during development, this requirement needs to be implemented based on two databases, and it can also be implemented directly through the interaction between the two modules. However, in this way, if search conditions are added and the search conditions are scattered in different libraries, it is very complicated to rely on data to process, and the data has not been effectively paging. Considering that the databases are currently placed in another server, there is no need to place different databases on different servers, the library name is introduced in SQL.

Although the library name was introduced, the corresponding business could run, but I also encountered a rather strange problem, that is, there was a library with the same name in the database. Because the library name was introduced in the previous SQL, directly modifying the configuration file and the database library name without changing the code will cause the SQL with the library name to be unusable. For this reason, the function of dynamically modifying the database library name in the .xml file was introduced. For details, it was implemented through the global variables of mybatis.

2. Implementation plan

After exploration, we have roughly obtained three implementation solutions

2.1 mybaits comes with global variable definition

Add the following global variable configuration to the yml file in springboot

mybatis:
  configuration:
    variables:
      myConfigName: "`base1`"

Then in SQL

  <select  resultType="">
      select * from ${myConfigName}.table1
  </select>

It should be noted that the backtick mark "` " is ". If the backtick mark is no longer added to the library name, then SQL will not be able to run smoothly. In addition, the placeholder must use $ because $ can get the global variable.

In this way, the library name can be configured dynamically, but in this case, there is a disadvantage that the default value cannot be set. If myConfigName is not configured, then this SQL cannot be executed

2.2 Use @value and mybatis to define global variables

Use @value to copy a member variable in java, and actually the default value of hi, so we configure the corresponding library name through the yml file

    /**
      * Database name
      */
    public static String dataBaseName;
	//Note the quotes    @Value("${:`test`}")
    private void setUserDataBaseName(String name){
        dataBaseName=name;
    }

Mapper definition and corresponding sql call

List<Object> getInfoFromtaba(@Param("dataBaseName") String dataBaseName);
List<Object> result=()

.xml writing method

<!-- Notice，Can be used here#placeholder --->  <select  resultType="">
      select * from #{dataBaseName}.table1
  </select>

This method solves the problem of default database name, but there is also a problem, that is, the mapper interface has a database name as a parameter, and the setting of this formal parameter looks awkward.

2.3 Use @value and mybatis to define global variables

This method is similar to the above 2.2 solution, but it can reduce unnecessary database name parameters of the mapper interface. As long as you don't look at SQL carefully, developers will not feel the dynamic configuration of the library name in general.

    /**
      * Database name
      */
    public static String dataBaseName;
	//Note the quotes    @Value("${:`test`}")
    private void setUserDataBaseName(String name){
        dataBaseName=name;
    }

<!-- Notice，Can be used here#placeholder --->  <select  resultType="">
      select * from 
      <choose>
           <when test="null !=  @@dataBaseName">
               ${@@dataBaseName}.table1 AS tu
           </when>
           <otherwise>
               `pre-base`.table1 AS tu
           </otherwise>
       </choose>
  </select>

Note: Here, the first @ of @@dataBaseName is the class path, and the following @dataBaseName is the library name.

Note that the $ placeholder is not needed in the boolean expression sub-test, and the subsequent specific data location requires the placement symbol $. The reason for using <chose> is to be safe, so that the default value of the corresponding variable is lost, and the default library can be used. The second is to avoid the error of the ${} reporting variable too long.

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.