目录
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 使用时,只需要将select * from user3替换成
WHERE id =#{id}










