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

   声明:本站部分内容来自互联网,如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。

23

目录

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;}@Overridepublic 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 selectUserByName(String name);public List selectUserListByUser(User u);
}

UserMapper.xml:


SELECT *FROM user3 
and sex=#{sex}and username like "%"#{username}"%"	

MapperTest.java:

	@Testpublic 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 list = mapper.selectUserListByUser(u);for(User user:list){System.out.println(user);}}

数据库:

运行结果:

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

UserMapper.java:

public List selectUserListByUserTrim(User u);

UserMapper.xml:

SELECT *FROM user3 
and sex=#{sex}and username like "%"#{username}"%"	

或者:

SELECT *FROM user3 
sex=#{sex} and username like "%"#{username}"%" and 	

MapperTest.java:

	@Testpublic 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 list = mapper.selectUserListByUserTrim(u);for(User user:list){System.out.println(user);}}

运行结果:

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

UserMapper.java:

public void updateSetUser(User u);

UserMapper.xml:

update user3username=#{username},password=#{password},sex=#{sex}where id=#{id}

MapperTest.java:

	@Testpublic 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 selectUserListByIds(Integer[] ids);

UserMapper.xml:

select * from user3 where id in#{id}

 MapperTest.java:

	@Testpublic 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 list = mapper.selectUserListByIds(ids);for (User user : list) {System.out.println(user);}}

运行结果:

4.2遍历集合

UserMapper.java:

public List selectUserListByList(List idList);

UserMapper.xml:

select * from user3 where id in#{id}

MapperTest.java:

	@Testpublic 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 idList=new ArrayList();idList.add(1);idList.add(3);idList.add(4);List 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 idList;public List getIdList() {return idList;}public void setIdList(List idList) {this.idList = idList;}
}

UserMapper.java:

public List selectUserListByUserVo(UserVo vo);

UserMapper.xml:

select * from user3 where id in#{id}

MapperTest.java:

@Testpublic 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 idList=new ArrayList();idList.add(1);idList.add(3);idList.add(4);UserVo vo=new UserVo();vo.setIdList(idList);List list = mapper.selectUserListByUserVo(vo);for (User user : list) {System.out.println(user);}}

运行结果:

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

	select *from user3

使用时,只需要将select * from user3替换成即可,如下:


WHERE id =#{id}