Requirement: query users by gender and name

SQL statement:

Select id, username, birthday, sex, address from 'user ` where sex = 1 and username like'% 3% '

Problems:

There may be only one A.sex and username attribute, and the other is null or empty [use if tag]

B. when the sex/username property is null or empty, the sql statement becomes:

Syntax error in select id, username, birthday, sex, address from 'user ` where and username like'% 3% '= = > >

Solution:

• Add 1 = 1: SELECT id, username, birthday, sex, address FROM `user` WHERE 1=1 AND username LIKE '% 3%'

 

<select id="findBynamesex" parameterType="User" resultType="User">
		select * from user 
		where 1=1
			<if test="sex != null and sex!=''">
				sex = #{sex}
			</if>
			<if test="username != null and username != ''">
				and username like "%"#{username}"%"
			</if>
		</where>
	</select>

• where tag

<select id="findBynamesex" parameterType="User" resultType="User">
		select * from user
		<!-- where Labels can be added automatically where，Simultaneous processing sql Statement         Front and   Keyword -->
		<where>
			<if test="sex != null and sex!=''">
				and sex = #{sex}
			</if>
			<if test="username != null and username != ''">
				and username like "%"#{username}"%"
			</if>
		</where>
	</select>

C. solve the problem of repeated sql [sql segments - sql tags and include tags]

 

Requirements: query user information according to multiple IDS [pass array or List to sql, and use foreach to parse for mybatis]

SQL statement: SELECT * FROM user WHERE id IN (1,10,24)

Implementation 1: create a wrapper class and add a List type attribute to the wrapper class

 

Implementation 2: use an array like Integer[] ids

Implementation 3: use list < integer > IDS

mapper.java

package com.mapper;

import java.util.List;

import com.entity.QueryVo;
import com.entity.User;

/**
 *
 * mapper Dynamic agent development write only interfaces do not require implementation classes (generated by programs) to replace Dao
 */
public interface UserMapper {
	// Follow four principles
	// 1 interface method name = = id in User.xml
	// 2. The return value type is consistent with the return value in User.xml
	// 3. The input of method is consistent in User.xml
	// 4 namespace binding this interface (namespace in User.xml writes this class)
	

	// Query the user according to the id list [add a list in the encapsulation class]
	public List<User> findByIds1(QueryVo vo);

	// Query the user [using an array such as Integer[] ids] according to the id list]
	public List<User> findByIds2(Integer[] ids);

	// Query the user according to the id list [use a list such as list < integer > IDS]
	public List<User> findByIds3(List<Integer> ids);

}

mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
   "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mapper.UserMapper">
<!-- according to id List query user [add one in package class] list] There collection You can directly take the ids attribute
		SELECT * FROM user WHERE id IN (1,10,24)
	-->
	<select id="findByIds1" parameterType="QueryVo" resultType="User">
	<include refid="selector"/>
	<where>
		<foreach collection="ids" item="id" open="id IN (" close=")" separator=",">
		#{id}
		</foreach>
	
	</where>
	</select>
	
	
	<!-- according to id List query user [use] Integer[] ids Such an array] here collection Can't write directly ids，Must write array
		SELECT * FROM user WHERE id IN (1,10,24)
	-->
	<select id="findByIds2" parameterType="Integer" resultType="User">
	<include refid="selector"/>
	<where>
		<foreach collection="array" item="id" open="id IN (" close=")" separator=",">
		#{id}
		</foreach>
	
	</where>
	</select>
	
	
	<!-- according to id List query user [use] List<Integer> ids In this way list] There collection It can't be written directly. ids，Must write list
		SELECT * FROM user WHERE id IN (1,10,24)
	-->
	<select id="findByIds3" parameterType="List" resultType="User">
	<include refid="selector"/>
	<where>
		<foreach collection="list" item="id" open="id IN (" close=")" separator=",">
		#{id}
		</foreach>
	
	</where>
	</select>
	
	<!-- Definition sql fragment -->
	<sql id="selector">
	select * from user
	</sql>
</mapper>

Test class: