Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

The scene is introduced

For simple CRUD, you might just need a parameterType. This can be either a custom type or a basic data type. However, when the number of parameters passed in is uncertain, or according to whether the parameters passed in the value to determine what kind of SQL statement, you need to use mybatis dynamic SQL function.

XML used in the project

Mybatis mapping table


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.raylee.dao.UserMapper">
    
<! SQL > select * from 'SQL';
    <sql id="selectUser">select * from users</sql>
  
    <! -->
    <select id="findByCondition" resultType="user" parameterType="user">
        <include refid="selectUser"></include>
        <where>
            <if test="id ! = 0">
                id = #{id}
            </if>
            <if test="username ! = null">
                and username = #{username}
            </if>
            <if test="password ! = null">
                and password = #{password}
            </if>
        </where>
    </select>

<! Select * from set;
    <select id="findByIds" parameterType="list" resultType="user">
        <include refid="selectUser"></include>
        <where>
            <foreach collection="list" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>
</mapper>
Copy the code

Mybatis configuration table


      
<! DOCTYPEconfiguration PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <! -- Custom alias -->
    <typeAliases>
        <typeAlias type="com.raylee.domain.User" alias="user"/>
    </typeAliases>

    <! Data source environment -->
    <environments default="developement">
        <environment id="developement">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/LCLDataBase"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

<! Load the core configuration file -->
    <mappers>
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>
Copy the code

Java classes used in the project

interface

public interface UserMapper {
    public List<User> findByCondition(User user);
    public List<User> findByIds(List<Integer> ids);
}
Copy the code

The User class

public class User {
    private int id;
    private String username;
    private String password;

    public int getId(a) {
        return id;
    }

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

    public String getUsername(a) {
        return username;
    }

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

    public String getPassword(a) {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString(a) {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\' ' +
                ", password='" + password + '\' ' +
                '} '; }}Copy the code

Database tables used in the project

Dynamic SQL-if tag

When we use the normal SELECT tag for SQL statement queries, if one of the criteria after where is null, the result may be no result

Configuration of XML in Mybatis

 <select id="findByCondition" resultType="user" parameterType="user">
     select * from users where username = #{username} and password = #{password}
 </select>
Copy the code

Debugging code

 InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
 SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
 SqlSession sqlSession = build.openSession(true);
 UserMapper mapper = sqlSession.getMapper(UserMapper.class);
 ​
 User user = new User();
 user.setId(1);
 user.setUsername("shaosiming");
 //        user.setPassword("123456");
 List<User> userList = mapper.findByCondition2(user);
 System.out.println(userList);
Copy the code

The generated SQL statement and corresponding parameters

 select * from users where id = ? and username = ? and password = ?
 1(Integer), shaosiming(String), null
Copy the code

Result of execution

empty

The expected results

The expectation is that when we pass in a parameter with a value, the condition will be concatenated to the end of the WHERE statement, and if there is no value, no concatenation

Use tags to do this

<! ParameterType ="user"> <include select id="findByCondition" resultType="user" refid="selectUser"></include> <where> <if test="id ! = 0"> and id = #{id} </if> <if test="username ! = null"> and username = #{username} </if> <if test="password ! = null"> and password = #{password} </if> </where> </select>Copy the code

SQL statements and parameters generated after using the tag

If a parameter is empty after the tag is used, the parameter is not generated in the SQL statement

 select * from users WHERE id = ? and username = ?
 1(Integer), shaosiming(String)
Copy the code

Results after using the tag

[User{id=1, username=’shaosiming’, password=’123456′}]

Dynamic SQL-foreach tags

When we use the IN statement to query multiple pieces of data, we may need to pass in a collection as an argument, where we need to use labels

Configuration of XML in Mybatis

<! ParameterType ="list" resultType="user"> <include refid="selectUser"></include> <where> <foreach collection="list" open="id in (" close=")" item="id" separator=","> #{id} </foreach> </where> </select>Copy the code

Debugging code

 InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
         SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
         SqlSession sqlSession = build.openSession(true);
         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
         
 ArrayList<Integer> ids = new ArrayList<>();
 ids.add(1);
 ids.add(2);
 ids.add(3);
 ​
 List<User> byIds = mapper.findByIds(ids);
 System.out.println(byIds);
Copy the code

Generated SQL statements and parameters

select * from users WHERE id in ( ? ,? ,?) 1(Integer), 2(Integer), 3(Integer)Copy the code

The execution result

The current users table does not contain only data whose ID is 3. Therefore, only two entries are displayed

[User{id=1, username=’shaosiming’, password=’123456′}, User{id=2, username=’dasiming’, password=’654321′}]

SQL extraction – SQL tags

 <sql id="selectUser">select * from users</sql>
Copy the code

SQL introduces the -include tag

 <include refid="selectUser"></include>
Copy the code