Mybatis中的动态SQL-where，trim标签

约定不等于承诺〃 2021-09-19 04:38 593阅读 0赞

**目录**

1.where标签（它可以去掉开头的and，但不能去掉结尾的and）-实现多条件查询例子

2.Trim标签（prefixOverrides可以去掉开头的and。suffixOverrides标签可以去掉结尾的and）

3.set标签（可以解决更新数据表时字符串拼接“，”问题）

4.foreach标签遍历数组和，集合和包装类

4.1遍历数组

4.2遍历集合

4.3遍历包装类

5.sql标签（他可以将重复的SQL代码提取出来）

# 1.where标签（它可以去掉开头的and，但不能去掉结尾的and）-实现多条件查询例子 #

User.java:

package com.sikiedu.bean;
    
    public class User {
    
    	private Integer id;
    	private String username;
    	private String password;
    	private String sex;
    	
    	public Integer getId() {
    		return id;
    	}
    	public void setId(Integer id) {
    		this.id = id;
    	}
    	public String getUsername() {
    		return username;
    	}
    	public void setUsername(String username) {
    		this.username = username;
    	}
    	public String getPassword() {
    		return password;
    	}
    	public void setPassword(String password) {
    		this.password = password;
    	}
    	public String getSex() {
    		return sex;
    	}
    	public void setSex(String sex) {
    		this.sex = sex;
    	}
    	@Override
    	public String toString() {
    		return "User [id=" + id + ", username=" + username + ", password=" + password + ", sex=" + sex + "]";
    	}
    	
    	
    }

UserMapper.java:

package com.sikiedu.mapper;
    
    import java.util.List;
    
    import com.sikiedu.bean.User;
    
    public interface UserMapper {
    
    	public User selectUserById(Integer id);
    	
    	public List<User> selectUserByName(String name);
    	
    	public List<User> selectUserListByUser(User u);
    }

UserMapper.xml:

<select id="selectUserListByUser" parameterType="com.sikiedu.bean.User" resultType="com.sikiedu.bean.User">
    		SELECT *
    		FROM user3 
    		<where>
    
    			<if test="sex!=null and sex!=''">
    				and sex=#{sex}
    			</if>
    			<if test="username!=null and username!=''">
    				and username like "%"#{username}"%"
    			</if>
    		</where>	
    	</select>

MapperTest.java:

@Test
    	public void Test3() throws Exception{
    		String resource="sqlMapConfig.xml";
    		InputStream in = Resources.getResourceAsStream(resource);
    		SqlSessionFactoryBuilder ssfb=new SqlSessionFactoryBuilder();
    		SqlSessionFactory ssf=ssfb.build(in);
    		SqlSession session  = ssf.openSession();
    		
    		UserMapper mapper = session.getMapper(UserMapper.class);
    		
    		User u=new User();
    		u.setSex("男");
    		u.setUsername("李");
    		
    		List<User> list = mapper.selectUserListByUser(u);
    		
    		for(User user:list){
    			System.out.println(user);
    		}
    	}

数据库：

![20190501122712414.png][]

运行结果：

![20190501122643518.png][]

# 2.Trim标签（prefixOverrides可以去掉开头的and。suffixOverrides标签可以去掉结尾的and） #

UserMapper.java:

public List<User> selectUserListByUserTrim(User u);

UserMapper.xml:

<select id="selectUserListByUserTrim" parameterType="com.sikiedu.bean.User" resultType="com.sikiedu.bean.User">
    		SELECT *
    		FROM user3 
    		<trim prefix="where" prefixOverrides="and">
    
    			<if test="sex!=null and sex!=''">
    				and sex=#{sex}
    			</if>
    			<if test="username!=null and username!=''">
    				and username like "%"#{username}"%"
    			</if>
    		</trim>	
    	</select>

或者：

<select id="selectUserListByUserTrim" parameterType="com.sikiedu.bean.User" resultType="com.sikiedu.bean.User">
    		SELECT *
    		FROM user3 
    		<trim prefix="where" suffixOverrides="and">
    
    			<if test="sex!=null and sex!=''">
    				sex=#{sex} and 
    			</if>
    			<if test="username!=null and username!=''">
    				username like "%"#{username}"%" and 
    			</if>
    		</trim>	
    	</select>

MapperTest.java:

@Test
    	public void Test4() throws Exception{
    		String resource="sqlMapConfig.xml";
    		InputStream in = Resources.getResourceAsStream(resource);
    		SqlSessionFactoryBuilder ssfb=new SqlSessionFactoryBuilder();
    		SqlSessionFactory ssf=ssfb.build(in);
    		SqlSession session  = ssf.openSession();
    		
    		UserMapper mapper = session.getMapper(UserMapper.class);
    		
    		User u=new User();
    		u.setSex("男");
    		u.setUsername("李");
    		
    		List<User> list = mapper.selectUserListByUserTrim(u);
    		
    		for(User user:list){
    			System.out.println(user);
    		}
    	}

运行结果：

![20190501124204236.png][]

# 3.set标签（可以解决更新数据表时字符串拼接“，”问题） #

UserMapper.java:

public void updateSetUser(User u);

UserMapper.xml:

<update id="updateSetUser" parameterType="com.sikiedu.bean.User">
    		update user3
    		<set>
    			<if test="username!=null and username!=''">
    				username=#{username},
    			</if>
    			<if test="password!=null and password!=''">
    				password=#{password},
    			</if>
    			<if test="sex!=null and sex!=''">
    				sex=#{sex}
    			</if>
    		 </set>
    	    where id=#{id}
    	</update>

MapperTest.java:

@Test
    	public void Test5() throws Exception{
    		String resource="sqlMapConfig.xml";
    		InputStream in = Resources.getResourceAsStream(resource);
    		SqlSessionFactoryBuilder ssfb=new SqlSessionFactoryBuilder();
    		SqlSessionFactory ssf=ssfb.build(in);
    		SqlSession session  = ssf.openSession();
    		
    		UserMapper mapper = session.getMapper(UserMapper.class);
    		
    		User u=new User();
    		u.setId(3);
    		u.setUsername("张三三");
    		u.setPassword("3333");
    		u.setSex("女");
    		
    		mapper.updateSetUser(u);
    		session.commit();
    	}

运行结果:

![20190501131632690.png][]

# 4.foreach标签遍历数组和，集合和包装类 #

## 4.1遍历数组 ##

UserMapper.java:

public List<User> selectUserListByIds(Integer[] ids);

UserMapper.xml:

<select id="selectUserListByIds" resultType="com.sikiedu.bean.User">
    		select * 
    		from user3 
    		where id 
    		in
    		<foreach collection="array" item="id" open="(" close=")" separator=",">
    			#{id}
    		</foreach>
    	</select>

MapperTest.java:

@Test
    	public void Test6() throws Exception{
    		String resource="sqlMapConfig.xml";
    		InputStream in = Resources.getResourceAsStream(resource);
    		SqlSessionFactoryBuilder ssfb=new SqlSessionFactoryBuilder();
    		SqlSessionFactory ssf=ssfb.build(in);
    		SqlSession session  = ssf.openSession();
    		
    		UserMapper mapper = session.getMapper(UserMapper.class);
    		
    		Integer[] ids={1,3,4};
    		List<User> list = mapper.selectUserListByIds(ids);
    		for (User user : list) {
    			System.out.println(user);
    		}
    	}

运行结果：

![20190501133917430.png][]

## 4.2遍历集合 ##

UserMapper.java:

public List<User> selectUserListByList(List<Integer> idList);

UserMapper.xml:

<select id="selectUserListByList" resultType="com.sikiedu.bean.User">
    		select * 
    		from user3 
    		where id 
    		in
    		<foreach collection="list" item="id" open="(" close=")" separator=",">
    			#{id}
    		</foreach>
    	</select>

MapperTest.java:

@Test
    	public void Test7() throws Exception{
    		String resource="sqlMapConfig.xml";
    		InputStream in = Resources.getResourceAsStream(resource);
    		SqlSessionFactoryBuilder ssfb=new SqlSessionFactoryBuilder();
    		SqlSessionFactory ssf=ssfb.build(in);
    		SqlSession session  = ssf.openSession();
    		
    		UserMapper mapper = session.getMapper(UserMapper.class);
    		
    		
    		List<Integer> idList=new ArrayList<Integer>();
    		idList.add(1);
    		idList.add(3);
    		idList.add(4);
    		
    		List<User> list = mapper.selectUserListByList(idList);
    		for (User user : list) {
    			System.out.println(user);
    		}
    	}

运行结果：

![20190501133917430.png][]

## 4.3遍历包装类 ##

UserVo.java:

package com.sikiedu.bean;
    
    import java.util.List;
    
    public class UserVo extends User {
    
    	private List<Integer> idList;
    
    	public List<Integer> getIdList() {
    		return idList;
    	}
    
    	public void setIdList(List<Integer> idList) {
    		this.idList = idList;
    	}
    }

UserMapper.java:

public List<User> selectUserListByUserVo(UserVo vo);

UserMapper.xml:

<select id="selectUserListByUserVo"  parameterType="com.sikiedu.bean.UserVo" resultType="com.sikiedu.bean.User">
    		select * 
    		from user3 
    		where id 
    		in
    		<foreach collection="idList" item="id" open="(" close=")" separator=",">
    			#{id}
    		</foreach>
    	</select>

MapperTest.java:

@Test
    	public void Test8() throws Exception{
    		String resource="sqlMapConfig.xml";
    		InputStream in = Resources.getResourceAsStream(resource);
    		SqlSessionFactoryBuilder ssfb=new SqlSessionFactoryBuilder();
    		SqlSessionFactory ssf=ssfb.build(in);
    		SqlSession session  = ssf.openSession();
    		
    		UserMapper mapper = session.getMapper(UserMapper.class);
    		
    		
    		List<Integer> idList=new ArrayList<Integer>();
    		idList.add(1);
    		idList.add(3);
    		idList.add(4);
    		
    		UserVo vo=new UserVo();
    		vo.setIdList(idList);
    		
    		List<User> list = mapper.selectUserListByUserVo(vo);
    		for (User user : list) {
    			System.out.println(user);
    		}
    	}

运行结果：

![20190501133917430.png][]

# 5.sql标签（他可以将重复的SQL代码提取出来） #

<sql id="myselect">
    		select *
    		from user3
    	</sql>

使用时，只需要将select \* from user3替换成<include refid="myselect"></include>即可，如下：

<select id="selectUserById" parameterType="Integer" resultType="com.sikiedu.bean.User">
    
    		<include refid="myselect"></include>
    		WHERE id =#{id}
    	</select>

[20190501122712414.png]: /images/20210725/2d97dfdecb844a4690b9abbd74f459e7.png
[20190501122643518.png]: /images/20210725/5f6b73b1b0494ec0b9983d675ae26f9e.png
[20190501124204236.png]: /images/20210725/0b0766fcb2eb4d9c9fa3284e7c56fba5.png
[20190501131632690.png]: /images/20210725/0b44d1c8a78c4d2eb6473baa8438e3a3.png
[20190501133917430.png]: /images/20210725/63fa6f643fb044feb7a6cdb60f514bec.png