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

最新推荐文章于 2024-06-20 15:44:27 发布

疆~

最新推荐文章于 2024-06-20 15:44:27 发布

阅读量1.2k

点赞数 2

文章标签： where

本文链接：https://blog.csdn.net/qq_40323256/article/details/89736890

版权

SSM 专栏收录该内容

10 篇文章 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:

<!-- 	public List<User> selectUserListByUser(User u); -->
<select id="selectUserListByUser" parameterType="com.sikiedu.bean.User" resultType="com.sikiedu.bean.User">
		SELECT *
		FROM user3 
		<where>
<!-- 		where标签可以去掉开头的and -->
			<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);
		}
	}

数据库：

运行结果：

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

UserMapper.java:

public List<User> selectUserListByUserTrim(User u);

UserMapper.xml:

<!-- 	public List<User> selectUserListByUserTrim(User u); -->
	<select id="selectUserListByUserTrim" parameterType="com.sikiedu.bean.User" resultType="com.sikiedu.bean.User">
		SELECT *
		FROM user3 
		<trim prefix="where" prefixOverrides="and">
<!-- 		prefixOverrides可以去掉开头的and。suffixOverrides标签可以去掉结尾的and -->
			<if test="sex!=null and sex!=''">
				and sex=#{sex}
			</if>
			<if test="username!=null and username!=''">
				and username like "%"#{username}"%"
			</if>
		</trim>	
	</select>

或者：

<!-- 	public List<User> selectUserListByUserTrim(User u); -->
	<select id="selectUserListByUserTrim" parameterType="com.sikiedu.bean.User" resultType="com.sikiedu.bean.User">
		SELECT *
		FROM user3 
		<trim prefix="where" suffixOverrides="and">
<!-- 		prefixOverrides可以去掉开头的and。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);
		}
	}

运行结果：

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

UserMapper.java:

public void updateSetUser(User u);

UserMapper.xml:

<!-- 	public void updateSetUser(User u); -->
 	<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();
	}

运行结果:

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

4.1遍历数组

UserMapper.java:

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

UserMapper.xml:

<!-- 	public List<User> selectUserListByIds(Integer[] ids); (1,3,4) -->
	<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);
		}
	}

运行结果：

4.2遍历集合

UserMapper.java:

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

UserMapper.xml:

<!-- 	public List<User> selectUserListByList(List<Integer> idList); -->
	<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);
		}
	}

运行结果：

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:

<!-- 	public List<User> selectUserListByUserVo(UserVo vo); -->
	<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);
		}
	}

运行结果：

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

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

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

<!--  public User selectUserById(Integer id); -->
	<select id="selectUserById" parameterType="Integer" resultType="com.sikiedu.bean.User">
<!-- 		SELECT * FROM user3  -->
		<include refid="myselect"></include>
		WHERE id =#{id}
	</select>

疆~

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
Mybatis中的动态SQL-where，trim标签

目录1.where标签（它可以去掉开头的and，但不能去掉结尾的and）-实现多条件查询例子2.Trim标签（prefixOverrides可以去掉开头的and。suffixOverrides标签可以去掉结尾的and）3.set标签（可以解决更新数据表时字符串拼接“，”问题）4.foreach标签遍历数组和，集合和包装类4.1遍历数组4.2遍历集合4.3遍历包装类5...
复制链接

扫一扫