目录
1.where标签(它可以去掉开头的and,但不能去掉结尾的and)-实现多条件查询例子
2.Trim标签(prefixOverrides可以去掉开头的and。suffixOverrides标签可以去掉结尾的and)
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>