动态SQL
IF
根据性别和名字查询
接口
public List<User> findif(User user);
映射文件
<select id="findif" parameterType="User" resultType="user">
select * from User where
<if test="sex != null and sex != '' ">
sex = #{sex}
</if>
<if test="username != null and username != '' ">
and username = #{username}
</if>
</select>
测试类
String resource = "SqlMapConfig.xml";
InputStream in = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
usermapper mapper = sqlSession.getMapper(usermapper.class);
User user = new User();
user.setUsername("张小明");
user.setSex("1");
List<User> users = mapper.findif(user);
System.out.println(users);
如果username的属性不对,对代码毫无影响,但是sex如果不对,sql语句会发生拼接错误
所以再where后面加上1=1,是sql成立
where
去掉IF标签中的前and,是sql语句拼接成功
映射文件
<select id="findif" parameterType="User" resultType="user">
select * from User
<where>
<if test="sex != null and sex != '' ">
sex = #{sex}
</if>
<if test="username != null and username != '' ">
and username = #{username}
</if>
</where>
</select>
SQL片段
<sql id="tet" >
select * from USER
</sql>
<select id="findif" parameterType="User" resultType="user">
<include refid="tet"/>
<where>
<if test="sex != null and sex != '' ">
sex = #{sex}
</if>
<if test="username != null and username != '' ">
and username = #{username}
</if>
</where>
</select>
Foreach
是将id的参数,接收过来进行遍历
select * from user where id in (1,2,3)
mapper接口:三种方式
public List<User> findlist1(Integer[] args);
public List<User> findlist2(List<Integer> idslist);
public List<User> findlist3(QueryVo vo);
mapper映射文件:collection的值,参数是数组则是array,参数是list集合则是list,参数是包装类则是注入包装类的属性名
item="值是什么无所谓",但是要与#{值}要一致
<select id="findlist1" parameterType="QueryVo" resultType="User">
SELECT * FROM USER WHERE id IN
<foreach collection="array" item="id" open="(" close=")" separator="," >
#{id}
</foreach>
测试类
包装类
List<Integer> idslist = new ArrayList<Integer>();
idslist.add(1);
idslist.add(22);
idslist.add(10);
QueryVo vo = new QueryVo(idslist);
List<User> users = mapper.findlist3(vo);
System.out.println(users);
数组
Integer[] args = new Integer[3];
args[0] = 1;
args[1] = 10;
args[2] = 16;
List<User> users = mapper.findlist1(args);
System.out.println(users);
集合
List<Integer> idslist = new ArrayList<Integer>();
idslist.add(1);
idslist.add(10);
idslist.add(16);
List<User> users = mapper.findlist2(idslist);
System.out.println(users);