Preparations:

public class User {

private int id;

private String lastName;

private int sex;

14.1, if statement description: if statement, can be dynamically based on your value to determine whether to add dynamic query conditions.

A method in the UserMapper class

public interface UserMapper {
	public List<User> queryUsersByNameAndSex(User user);
}
Copy the code

UserMapper configuration file

<! ParameterType ="com.bean.User" resultType="com.bean.User"> select id,last_name lastName,sex from t_user where <! <if test="lastName! = null"> last_name like concat('%',#{ lastName },'%') </if> <if test="sex == 0 or sex == 1"> and sex = #{sex} </if> </select>Copy the code

Test code:

@Test public void testQueryUsersByNameAndSex() { SqlSession session = sqlSessionFactory.openSession(); try { UserMapper userMapper = session.getMapper(UserMapper.class); List<User> users = userMapper.queryUsersByNameAndSex(new User(0, "dmi", 1)); System.out.println(users); } finally { session.close(); }}Copy the code

Results of execution:



14.2. Where statement

Where statement, can help us in a number of dynamic statements, effectively remove the front of the redundant and or or such as redundant keywords

UserMapper configuration file

<! ParameterType =" com.bean.user "-- select id="queryUsersByNameAndSex" parameterType=" com.bean.user" resultType="com.atguigu.bean.User"> select id,last_name lastName,sex from t_user <where> <! <if test="lastName! = null"> last_name like concat('%',#{ lastName },'%') </if> <if test="sex == 0 or sex == 1"> and sex = #{sex} </if> </where> </select>Copy the code

The code for the test is:

@Test public void testQueryUsersByNameAndSex() { SqlSession session = sqlSessionFactory.openSession(); try { UserMapper userMapper = session.getMapper(UserMapper.class); List<User> users = userMapper.queryUsersByNameAndSex(new User(0, null, 1)); System.out.println(users); } finally { session.close(); }}Copy the code

Test results:



14.3. Trim statement

Trim can dynamically add content before and after contained statements. You can also remove the content given before or after

Add the content before prefix

Suffix is added after

suffixOverrides

PrefixOverrides removes the previous content

UserMapper Content in the configuration file

<! ParameterType ="com.bean.User" resultType="com.bean.User"> select  id,last_name lastName,sex from t_user <! Trim can dynamically add content before and after contained statements. Prefix =" suffix "--> <trim prefix="where" prefix=" suffix" suffixOverrides="and" prefixOverrides=""> <! <if test="lastName! = null"> last_name like concat('%',#{ lastName },'%') and </if> <if test="sex == 0 or sex == 1"> sex = #{sex} </if> </trim> </select>Copy the code

Test code:

@Test public void testQueryUsersByNameAndSex() { SqlSession session = sqlSessionFactory.openSession(); try { UserMapper userMapper = session.getMapper(UserMapper.class); List<User> users = userMapper.queryUsersByNameAndSex(new User(0, "dmi", 5)); System.out.println(users); } finally { session.close(); }}Copy the code

Test results:



14.4, Choose (when, otherwise) statement

Note: Choose when otherwise can perform multiple selection judgments, but only one branch will be performed.

Similar to switch case statement

Methods added to the UserMapper interface:

public List<User> queryUsersByNameAndSexForChoose(User user);
Copy the code

Contents of the UserMapper configuration file:

<! The Choose when otherwise statement is somewhat similar to switch Case multiplexing. Only one path is executed. --> <select id="queryUsersByNameAndSexForChoose" parameterType="com.bean.User" resultType="com.bean.User"> select id,last_name lastName,sex from t_user <where> <choose> <when test="id > 0"> id = #{ id } </when> <when test="lastName ! = null"> last_name = #{lastName} </when> <otherwise> 1 = 1 </otherwise> </choose> </where> </select>Copy the code

Test code:

@Test public void testQueryUsersByNameAndSexForChoose() { SqlSession session = sqlSessionFactory.openSession(); try { UserMapper userMapper = session.getMapper(UserMapper.class); List<User> users = userMapper.queryUsersByNameAndSexForChoose(new User(1, "admin", 0)); System.out.println(users); } finally { session.close(); }}Copy the code

Test results:



14.4. The set statement

Set statement can be removed, update time, set update multiple columns, due to the dynamic generation of SQL statements, resulting in additional commas

UserMapper public int updateUserForSet(User User);

Contents of the UserMapper configuration file:

<! Update statement set tag; ParameterType ="com.bean.User"> update t_user <set> <if test="lastName! = null"> last_name = #{lastName}, </if> <if test="sex == 0 or sex == 1"> sex = #{sex} </if> </set> where id = #{id} </update>Copy the code

Test code:

@Test public void testUpdateUserForSet() { SqlSession session = sqlSessionFactory.openSession(); try { UserMapper userMapper = session.getMapper(UserMapper.class); userMapper.updateUserForSet(new User(3, "xxxx", 10)); session.commit(); } finally { session.close(); }}Copy the code

Test results:



14.5. foreach statement

Foreach statement, you can iterate through the output of a collection of data

Methods added in UserMapper

public List<User> queryUserByidsForIn(@Param("ids")List<Integer> ids);
Copy the code

UserMapper Content in the configuration file

<! The separator property sets the space between each output element. The item property. The separator property sets the space between each output element <select ID ="queryUserByidsForIn" resultType="com.bean.User"> select ID,last_name,sex from T_user WHERE id in <foreach collection="ids" open="(" close=")" separator="," item="item_id"> #{item_id} </foreach> </select>Copy the code

Test code:

@Test public void testQueryUserByidsForIn() { SqlSession session = sqlSessionFactory.openSession(); try { UserMapper userMapper = session.getMapper(UserMapper.class); List<Integer> ids = new ArrayList<>(); ids.add(3); ids.add(6); ids.add(7); List<User> users = userMapper.queryUserByidsForIn( ids ); System.out.println(users); } finally { session.close(); }}Copy the code

Test results: