IF Tags

Problems arising from the non-use of if tags
Dynamic splicing sql is realized by various tag methods provided by mybatis.
Demand: user enquiry based on gender and name
Query sql:
SELECT id, username, birthday, sex, address FROM user WHERE sex = 1 AND username LIKE'% Liu%'

<!-- Query users based on gender and name -->
	<select id="queryUserByWhere" parameterType="user" resultType="user">
		select id, username, birthday, sex, address FROM `user` 
		WHERE sex = #{sex} AND username LIKE '%${username}%'
	</select>

Write the Mapper interface, as shown below

Test:

@Test
	public void testQueryUserByWhere() {
		// Get sqlSession, and spring is managed by spring after integration
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		//Get the mapper object
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		User user = new User();
		user.setSex("1");
		user.setUsername("Liu");
		List<User> userList = userMapper.queryUserByWhere(user);
		for (User user2 : userList) {
			System.out.println(user2);
		}
		sqlSession.close();
	}

Results:

Assume that //user.setSex("1") is commented out;
sql will become like this:
SELECT id, username, birthday, sex, address FROM user WHERE sex = null AND username LIKE'% Liu%'
So you can't query the data.

If you don't use the if tag, you will need to write multiple SQLs to solve this problem.

Use the if tag

Modify UserMapper.xml as follows:

<!-- Query users according to conditions -->
<select id="queryUserByWhere" parameterType="user" resultType="user">
	SELECT id, username, birthday, sex, address FROM `user`
	WHERE 1=1
	<if test="sex != null and sex != ''">
		AND sex = #{sex}
	</if>
	<if test="username != null and username != ''">
		AND username LIKE
		'%${username}%'
	</if>
</select>

where 1=1 facilitates back stitching and
Result:

Where Tags

The sql above also has a statement like where 1=1, which is troublesome.
where tag can be used for modification
Modify UserMapper.xml as follows

where 1=1 is equivalent to <where> </where>

Result:

Sql fragment (extracting columns from sql, reusable)

Sql can extract duplicate SQL and use include reference to achieve SQL reuse.

The id, username, birthday, sex, addres in the above example are extracted as sql fragments, as follows:

<!-- Query users based on gender and name -->
	<select id="queryUserByWhere" parameterType="user" resultType="user">
		select <include refid="userField"></include> FROM `user` 
		<where>
			<if test="sex!=null and sex!=''">
			 AND sex = #{sex}
			</if>
			<if test="username!=null and username!=''">
			 AND username LIKE '%${username}%'
		</if>
		</where>
	</select>
	<sql id="userField">
		id, username, birthday, sex, address
	</sql>

Result:

foreach tag (to solve the list or array problem with parameters)

Passing an array or List to sql, mybatis uses foreach parsing, as follows:

Query user information based on multiple IDS
Query sql:
SELECT * FROM user WHERE id IN (1,10,24)
You can't pass three values (1, 10, 24) at this time, so it's too cumbersome to pass an array to solve the problem.

Create entity classes

import java.util.List;

public class QueryVo {
	public User user;
	public List<Integer> ids;
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
	public List<Integer> getIds() {
		return ids;
	}
	public void setIds(List<Integer> ids) {
		this.ids = ids;
	}
	
}

Write UserMapper.java:

Write UserMapper.xml:

	<select id="queryUserByIds" parameterType="queryVo" resultType="user">
		select * from user
		<!-- foreach Label, traversal -->
		<!-- collection: The traversal set, here is QueryVo Of ids attribute -->
		<!-- item: Items traversed can be written casually, but with the following#Consistency in {} - > ____________
		<!-- open: Added earlier sql fragment -->
		<!-- close: Added at the end sql fragment -->
		<!-- separator: Specifies the separator to be used between elements traversed -->
		<where>
			<foreach collection="ids" item="i" open="id in(" close=")" separator=",">
				#{i}
			</foreach>
		</where>
	</select>

Write test methods:

	@Test
	public void testQueryUserByIds() {
		// Get sqlSession, and spring is managed by spring after integration
		SqlSession sqlSession = this.sqlSessionFactory.openSession();
		//Get the mapper object
		UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
		QueryVo queryVo = new QueryVo();
		List<Integer> ids = new ArrayList<Integer>();
		ids.add(10);
		ids.add(16);
		ids.add(25);
		ids.add(32);
		queryVo.setIds(ids);
		List<User> userList = userMapper.queryUserByIds(queryVo );
		for (User user2 : userList) {
			System.out.println(user2);
		}
		sqlSession.close();
	}

Result: