In order to master Mybatis dynamic mapping, I really made great efforts

Dynamic SQL is one of the powerful features of MyBatis. If you’ve ever used JDBC or a similar framework, you can understand how painful it can be to concatenate SQL statements based on different conditions, such as making sure you don’t forget to add the necessary whitespace and removing the comma from the last column name of the list. With dynamic SQL, you can get rid of this pain completely.

Using dynamic SQL is not an easy task, but with the powerful dynamic SQL language that can be used in any SQL mapping statement, MyBatis significantly improves the ease of use of this feature.

If you’ve ever worked with JSTL or any xmL-like language based text processor before, dynamic SQL elements may feel familiar. In previous versions of MyBatis, it took time to understand a large number of elements. With the help of powerful OGNL based expressions, MyBatis 3 replaces most of the previous elements, greatly reducing the number of elements to be learned by less than half.

If: Use if to implement simple conditional selection.

Choose (when,otherwise) : Equivalent to the Switch statement in Java, usually paired with when and otherwise.

Set: Resolves dynamic update statements.

Trim: Removes redundant keywords flexibly.

Foreach: Iterates over a collection, usually for in conditions.

In practice, these labels are often used in combination.

Today’s demo uses spring-boot +Mybatis to demonstrate, for spring-boot integration Mybatis recommended:

If + WHERE implements multi-condition query

Create a database table:

CREATE TABLE `m_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL COMMENT '0: girl 1: boy '.PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
Copy the code

Initialize several pieces of data:

Let’s start with the usermapper.xml file:

<mapper namespace="com.tian.mybatis.mapper.UserMapper">
    <resultMap id="User" type="com.tian.mybatis.entity.User">
        <id column="id" property="id"/>
        <result column="name" property="userName"/>
    </resultMap>
    <select id="selectUserById"  resultMap="User">
        select * from m_user
        <where>
            <if test="id ! = null">
                id = #{id}
            </if>
            <if test="name ! = null and name ! = "">
                and `name` = #{name}
            </if>
        </where>
    </select>
</mapper>
Copy the code

UserMapper. Java content:

import com.tian.mybatis.entity.User;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface UserMapper {
    User selectUserById(@Param("name") String userName, @Param("id") Integer id);
}
Copy the code

UserService. Java content:

public interface UserService {
    User selectUserById(String userName, Integer id);
}
Copy the code

UserServiceImpl. Java content:

import com.tian.mybatis.entity.User;
import com.tian.mybatis.mapper.UserMapper;
import com.tian.mybatis.service.UserService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

@Service
public class UserServiceImpl implements UserService {

    @Resource
    private UserMapper userMapper;

    @Override
    public User selectUserById(String userName, Integer id) {
        returnuserMapper.selectUserById(userName, id); }}Copy the code

UserController. Java content:

import com.tian.mybatis.entity.User;
import com.tian.mybatis.service.UserService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

@RestController
public class UserController {

    @Resource
    private UserService userService;

    @GetMapping("/test")
    public User selectUserById(a) {
        return userService.selectUserById("tian".1); }}Copy the code

Application. Java content:

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.tian.mybatis.mapper")
public class Application {
    public static void main(String[] args) { SpringApplication.run(Application.class, args); }}Copy the code

Get the project started and access /test.

http://localhost:9002/test

Returns:

The above example is also an example of code in our work, and we work partially in this way.

All of the demonstrations below are based on the above code.

Back to business.

The above example uses where+if. There seems to be a problem in the case:

Id =null; id=null;

We modify the code in the Controller

    @GetMapping("/test")
    public User selectUserById(a) {
        return userService.selectUserById("tian".null);
    }
Copy the code

To enable SQL output, we add a configuration item to the configuration file:

logging:
  level:
    com:
      tian:
        mybatis:
          mapper: debug
Copy the code

Again, the output is the same as before. The SQL output from the console does not contain and. This is one of the great features of so-called dynamic mapping.

If we do not use dynamic mapping labels, we are likely to have problems handling OR or AND.

The WHERE element can handle redundant and and or problems intelligently without worrying about syntax errors caused by redundant keywords.

The test element of the if element is used to determine whether the expression matches, which continues to concatenate the SQL statement.

advice

It is recommended to use this kind of dynamic label rather than the original one, because sometimes unexpected judgment leads to an AND or OR, which leads to bugs, which may lead to an online function being unavailable.

If +trim+foreach

Make adjustments to the previous code

<select id="selectUsersByIds" resultMap="User">
        select * from m_user
        <trim prefix="where" prefixOverrides="and | or">
            <if test="idList ! = null">
                id in
                <foreach collection="idList" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
            <if test="gender ! = null and gender ! = 0">
                AND gender = #{gender}
            </if>
        </trim>
</select>
Copy the code

UserMapper. Java is increased

List<User> selectUsersByIds(@Param("idList") List<Integer> idList, @Param("gender") Integer gender);
Copy the code

Controller new method:

    @GetMapping("/users")
    public List<User> selectUsersByIds(a) {
        List<Integer> idList = new ArrayList<>();
        idList.add(1);
        idList.add(2);
        idList.add(3);
        idList.add(4);
        idList.add(5);
        return userService.selectUsersByIds(idList, null);
    }
Copy the code

Projects run and visit

http://localhost:9002/users

Output:

SQL output:

Describe the above properties

The attribute of the trim

Prefix: Prefix is used to prefix the trim content by automatically identifying whether there is a return value, such as where in the preceding example.
Suffix: suffix is used to add suffixes to the contents of trim.
PrefixOverrides: : to trim contains first specified content, such as the above examples and | or ignore more than (to);
SuffixOverrides: : Specifies that the first part of trim is ignored.

The foreach attribute

Item: Alias for iterating each element in the collection.
Index: : Specifies a name that represents the position reached during each iteration.
Open: indicates what the statement starts with (if it is an in condition, it must start with ‘(‘))
Separator: : Indicates what character is used to separate each iteration. (Separator: : indicates the “, “separator for each iteration.)
Close: : indicates where the statement ends.
Collection: The most critical and error-prone attributes. Note that this attribute must be specified. The value of this attribute is different in different cases.

There are three main cases:

@param (“name”) @param (“name”) is the name used in mapper.xml.

I’ve seen a lot of people do this in my projects when I’m not sure a condition will appear after a WHERE statement

slect ... from... where1=1
Copy the code

See if your code has it too?

set

The set element can be used to dynamically contain columns that need to be updated, ignoring other columns that are not updated.

New UserMapper. The XML

<update id="updateAuthorIfNecessary">
        update m_user
        <set>
            <if test="userName ! = null and userName ! = "">
               `name` = #{userName},
            </if>
            <if test="gender ! = null and gender ! = 0">
                gender = #{gender},
            </if>
            <if test="age ! = null and age ! = 0">
                age = #{age},
            </if>
        </set>
        where id=#{id}
</update>
Copy the code

UserMapper. New in Java

int updateAuthorIfNecessary(User user);
Copy the code

The controller of new

    @PostMapping("/updateUser")
    public String update(a) {
        User user = new User();
        user.setAge(18);
        user.setUserName("Tian Ge");
        user.setId(1);
        return userService.updateAuthorIfNecessary(user) == 1 ? "success" : "fail";
    }
Copy the code

Restart the project, access

http://localhost:9002/updateUser

Output: success

The data in the database table has been modified successfully:

SQL output

In this example, the set element inserts the set keyword dynamically at the beginning of the line and removes additional commas (which are introduced when a conditional statement is used to assign values to a column).

Let’s do it another way

<trim prefix="SET" suffixOverrides=",">.</trim>
Copy the code

Let’s adjust the DIAM in the XML above:

    <update id="updateAuthorIfNecessary">
        update m_user
        <trim prefix="SET" suffixOverrides=",">
            <if test="userName ! = null and userName ! = "">
                `name` = #{userName},
            </if>
            <if test="gender ! = null and gender ! = 0">
                gender = #{gender},
            </if>
            <if test="age ! = null and age ! = 0">
                age = #{age},
            </if>
        </trim>
        where id=#{id}
    </update>
Copy the code

The controller changes:

    @PostMapping("/updateUser")
    public String update(a) {
        User user = new User();
        user.setAge(19);
        user.setUserName("tian");
        user.setId(1);
        return userService.updateAuthorIfNecessary(user) == 1 ? "success" : "fail";
    }
Copy the code

Finally, look at the SQL output:

We were automatically assigned the SET keyword. The database is successfully modified.

choose

Equivalent to the Switch statement in Java, usually paired with when and otherwise.

Sometimes, we don’t want to use all of the conditions, but just choose one of several conditions to use. For this, MyBatis provides the Choose element, which is a bit like the Switch statement in Java.

Let’s continue with the example code above.

Add method to usermapper.xml:

<select id="selectUsersByName" resultMap="User">
        select * from m_user where age = 19
        <choose>
            <when test="userName ! = null and userName ! = "">
                and `name` = #{userName}
            </when>
            <otherwise>
                AND gender = 1
            </otherwise>
        </choose>
</select>
Copy the code

Controller new method:

@GetMapping("/user/name")
    public  List<User>  selectUsersByName(a) {
        return userService.selectUsersByName("tian");
}
Copy the code

Returns:

SQL output:

Correct output. What if our userName is not null?

The output and above normal, before looking at the SQL output:

Because the condition of userName is not met, gender is implemented directly.

The above

is similar to the default module in switch in our Java syntax, which is executed when the front conditions are not met.

Bind

It’s not used very much, but it’s useful. The bind element allows you to create a variable outside of the OGNL expression and bind it to the current context. Such as:

<select id="selectUserByName" resultType="User">
  <bind name="pattern" value="'%' + userName + '%'" />
  select * from m_user
  WHERE `name` LIKE #{pattern}
</select>
Copy the code

And then there’s script, which you don’t have to do in a demo, you don’t have to do in a job. It unloads SQL into Java code. Such as

    @Update({""})
    void updateUserValues(User user);
Copy the code

conclusion

In order to demonstrate some knowledge in the article, some code may not be very standard, especially the SQL part, we are in the development, for the use of Mybatis development, I have summed up a few points:

Whether the table already has an index, if there is an index in our SQL is useful.
Do not write asterisks (*) in return fields. You are advised to use required fields.
It is recommended to write all keywords in uppercase to better distinguish non-keywords.
Remember single quotes when you encounter table fields that are identical to database keys.
Use @param annotations. Be sure to use annotations in Mybatis.
When using either a single parameter or multiple parameters, specify the name using the @param annotation so that fields can be added later.
Dynamic labels are strongly recommended to avoid SQL errors with extra AND or keywords, and to avoid where 1=1

In order to master Mybatis dynamic mapping, I really made great efforts

If + WHERE implements multi-condition query

If +trim+foreach

set

choose

Bind

conclusion

Related Posts

Data index system construction method

Distributed Transaction learning Notes “a” | weekend learning

Cobra + client-GO implementation of K8s custom plug-in development