Share knowledge, not yourself.

preface

In our actual development. Data extraction is more frequent than data storage. It becomes more complex as the volume of data grows and business requirements change. In many cases, simple query operations can no longer meet business requirements. So we need SQL to get smarter. The dynamic SQL-related tags in mapper.xml, which we’ll learn in this chapter, address this need.

A, preparation,

Before we start studying. We need to modify the user table in the mybatis_test library of the test database we created earlier. Add two fields gender and age. The gender field is an int, and we agree that if gender is 1 we treat her as a boy, and if gender is 2 we treat her as a girl. The DDL code is as follows:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `gender` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
Copy the code

After the modification, we insert two pieces of data into user as test data

We also need to add gender and age attributes and get/set methods to the mapping entity class user.clss of the User table. The code is as follows:

public class User {

    private Integer id;
    private String username;
    private Integer gender;
    private Integer age;

    public Integer getId(a) {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername(a) {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public Integer getGender(a) {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    public Integer getAge(a) {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age; }}Copy the code

Once we’re ready, we can start coding.

Dynamic Sql if tag

The if tag in dynamic SQL is often used in what we call multi-conditional queries. In real development, there may be an indefinite number of query conditions in an SQL query. Then we can use the if tag. The same goes for the if code in our Java code. It can determine whether to concatenate the condition into the query SQL by giving an expression that results in a Boolean type. Next we will improve the getListByCondition method. The code is as follows:

    <! -->
    <select id="getListByCondition" resultType="User" parameterType="User">
        select * from user
            <where>
                <if test="id ! = null"> id = #{id} </if>
                <if test="username ! = null and username ! = ""> and username = #{username} </if>
                <if test="gender ! = null"> and gender = #{gender} </if>
                <if test="age ! = null"> and age < #{age} </if>
            </where>
    </select>
Copy the code

If there is one or more of the attributes in the if tag that meet the criteria, the SQL statement will be appended only if the attribute in the if tag has a value. After you’ve written the code, take your time testing. Let’s start with a question. Select * from user where username = #{username}. Select * from user where username = #{username}. Obviously, this syntax is incorrect.

How do we avoid such mistakes? It’s really quite simple. We just need to make a few changes. The code is as follows:

<! -->
    <select id="getListByCondition" resultType="User" parameterType="User">
        select * from user
            <where>
                1 = 1 
                <if test="id ! = null"> and id = #{id} </if>
                <if test="username ! = null and username ! = ""> and username = #{username} </if>
                <if test="gender ! = null"> and gender = #{gender} </if>
                <if test="age ! = null"> and age < #{age} </if>
            </where>
    </select>
Copy the code

We add 1 = 1 to the where tag. This way, even if one of the if tags does not meet the condition, there will be no syntax errors.

Note: If a range query is required in a query, it is not recommended to use symbols like >, >=, <, <= directly. It can cause parsing errors in XMl files. So we should write the corresponding escape character and end with a semicolon

The original symbol > < > = < = & ‘ “
Escape character &gt; &lt; &gt; = &lt; = &amp; &apos; &quot;

Next, let’s write the test class. Suppose we want to find a male and younger than 18.

@Test
    public void testGetListByCondition(a) throws IOException {
        // Get the configuration file and convert it to the input stream
        InputStream resourceAsStream =
                Resources.getResourceAsStream("mybatis-config.xml");
        // Get SqlSessionFactory
        SqlSessionFactory sqlSessionFactory =
                new SqlSessionFactoryBuilder().build(resourceAsStream);
        / / get sqlSessin
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // Get the interface proxy object using the JDK dynamic proxy
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        // Build the input parameter object
        User user = new User();
        user.setGender(1);
        user.setAge(18);
        // Execute method
        List<User> users = userDao.getListByCondition(user);
        for (User user1 : users) {
            System.out.println("id = " + user1.getId() +
                    ", username = " + user1.getUsername() +
                    ", gender = " + user1.getGender() +
                    ", age = " +user1.getAge());
        }
        / / close the sqlSession
        sqlSession.close();
    }
Copy the code

Output result:

Other queries as an exercise, you can simulate yourself.

Foreach tags for dynamic query

A foreach tag can be used as a child of a WHERE tag and can be used as a separate tag. Attribute meanings are as follows:

  • Collection: The input object type of the method. The default List type is List, and the default array type is array.
  • Index: In lists and arrays,index is the sequence number of an element. In maps,index is the key of an element.
  • Open: the start symbol of SQL concatenation, used with close.
  • Close: the end symbol of SQL concatenation. Used in combination with open.
  • Separator: The separator between elements. Separator =”,”.

With the above concepts in mind, we started writing code. First declare two methods in the UserDao interface:

    // Batch query
    List<User> selectByIds(int[] ids);

    // Batch add
    int batchInsert(List<User> users);
Copy the code

Then write SQL in usermapper. XML as follows:

    <! -- Batch query -->
    <select id="selectByIds" resultType="user" parameterType="_int[]">
        select * from user
        <where>
            <foreach collection="array" open="id in (" close=")" separator="," item="id">
                #{id}
            </foreach>
        </where>
    </select>

    <! -- Batch add -->
    <insert id="batchInsert" parameterType="list">
        insert into user(id,username,gender,age) values
            <foreach collection="list" separator="," item="user">
                (
                 #{user.id},
                 #{user.username},
                 #{user.gender},
                 #{user.age}
                )
            </foreach>
    </insert>
Copy the code

Finally, start writing the test class.

    @Test
    public void TestSelectByIds(a) throws IOException {
        // Get the configuration file and convert it to the input stream
        InputStream resourceAsStream =
                Resources.getResourceAsStream("mybatis-config.xml");
        // Get SqlSessionFactory
        SqlSessionFactory sqlSessionFactory =
                new SqlSessionFactoryBuilder().build(resourceAsStream);
        / / get sqlSessin
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // Get the interface proxy object using the JDK dynamic proxy
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        // Build the input parameter object
        int[] ids = new int[] {1.2};
        List<User> users = userDao.selectByIds(ids);
        for (User user : users) {
            System.out.println("id = " + user.getId() +
                    ", username = " + user.getUsername() +
                    ", gender = " + user.getGender() +
                    ", age = " +user.getAge());
        }
        / / close the sqlSession
        sqlSession.close();
    }
Copy the code

Test Method batchInsert:

    @Test
    public void TestBatchInsert(a) throws IOException {
        // Get the configuration file and convert it to the input stream
        InputStream resourceAsStream =
                Resources.getResourceAsStream("mybatis-config.xml");
        // Get SqlSessionFactory
        SqlSessionFactory sqlSessionFactory =
                new SqlSessionFactoryBuilder().build(resourceAsStream);
        / / get sqlSessin
        SqlSession sqlSession = sqlSessionFactory.openSession();
        // Get the interface proxy object using the JDK dynamic proxy
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        // Build the input parameter object
        List<User> users = new ArrayList<>();
        User user1 = new User();
        user1.setId(3);
        user1.setUsername("tom");
        user1.setGender(1);
        user1.setAge(20);
        User user2 = new User();
        user2.setId(4);
        user2.setUsername("lisa");
        user2.setGender(2);
        user2.setAge(22);
        users.add(user1);
        users.add(user2);
        int row = userDao.batchInsert(users);
        System.out.println(row);
        // Commit the transaction
        sqlSession.commit();
        / / close the sqlSession
        sqlSession.close();
    }
Copy the code

The log shows that the number of affected rows is 2 and the database was successfully inserted.

Affected by the length of batch modification and batch delete only given code, you can test yourself

    int batchUpdate(List<User> users);

    int deleteByIds(int[] ids);
Copy the code
    <! -- Batch change -->
    <update id="batchUpdate" parameterType="list">
        <foreach collection="list" item="user" separator=";">
            update user
            <set>
                <if test="user.username ! = null and user.username ! = "">
                    username = #{user.username},
                </if>
                <if test="user.gender ! = null">
                    gender = #{user.gender},
                </if>
                <if test="user.age ! = null">
                    age = #{user.age}
                </if>
            </set>
            where id = #{user.id}
        </foreach>
    </update>

    <!-- 批量删除 -->
    <delete id="deleteByIds" parameterType="_int[]">
        delete from user
        <where>
            <foreach collection="array" open="id in (" close=")" separator="," item="id">
                #{id}
            </foreach>
        </where>
    </delete>
Copy the code

Four,

Good design always gives you clear boundaries and enough flexibility, like the three tags in today’s lesson where, if and foreach. They can be used individually or in combination, which ensures that our code is as flexible as possible to meet our development needs. Students learning MyBatis for the first time may be confused by some too flexible design. We should study from a higher perspective. Look at these features in the big picture, rather than getting stuck in a rut. Increase the dimension of thinking and practice diligently to navigate complexity and make better use of tools.

5. Historical links

  • Chapter 1: Introduction and progress of MyBatis – introduction and introduction
  • Chapter 2: MyBatis introduction and advanced – proxy mode and core configuration details

If there are ambiguities or errors. Welcome to point out. Thank you.