MyBatis学习（七）

MyBatis映射文件SQL深入

动态SQL语句之if标签

1. 之前做过拼接SQL语句查询条件的查询，需要动态的拼接SQL语句
2. UserDao接口的方法

package com.qcby.dao;

import com.qcby.entity.User;
import java.util.List;

/**
* @author Administrator
*/
public interface UserDao {
// 条件查询
public List<User> findByWhere(User user);
}

UserDao.xml配置文件

<?xml version="1.0" encoding="UTF‐8"?>
<!DOCTYPE mapper
PUBLIC "‐//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis‐3‐mapper.dtd">
<mapper namespace="com.qcby.dao.UserDao">
<!‐‐ 查询所有的数据 ‐‐>
<select id="findByWhere" parameterType="user" resultType="user">
select * from user where 1=1
<if test="username != null and username != ''">
and username like #{username}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
</select>
</mapper>

测试方法

/**
* 测试查询所有
* @throws Exception
*/
@Test
public void testFindAll() throws Exception {
// 加载配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
// 创建工厂对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
// 构建session
SqlSession session = factory.openSession();
// 获取代理对象
UserDao mapper= session.getMapper(UserDao.class);
User user = new User();
user.setUsername("%熊%");
user.setSex("女");
List<User> list = mapper.findByWhere(user);
for (User user1 : list) {
    System.out.println(user1);
}
// 释放资源
session.close();
in.close();
}

动态SQL语句之where标签

1. where标签目的就是为了去掉 where 1=1的拼接的
2. where标签使用在if标签的外面
3. 代码

<?xml version="1.0" encoding="UTF‐8"?>
<!DOCTYPE mapper
PUBLIC "‐//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis‐3‐mapper.dtd">
<mapper namespace="com.qcby.dao.UserDao">
<!‐‐ 查询所有的数据 ‐‐>
<select id="findByWhere" parameterType="user" resultType="user">
select * from user
<where>
<if test="username != null and username != ''">
and username like #{username}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
</where>
</select>
</mapper>

动态SQL语句之foreach标签

1. 需求一
   1. 需求的SQL语句：SELECT * FROM USER WHERE id = 1 OR id = 2 OR id = 3
   2. 在User类中添加属性

public class User implements Serializable{
private static final long serialVersionUID = 525400707336671154L;
// 主键
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
private List<Integer> ids;

在UserDao中添加方法

public List<User> findByIds(User user);

编写测试方法

@Test
public void testfindByIdsOr() throws Exception {
// 加载配置文件
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
// 创建工厂对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
// 构建session
SqlSession session = factory.openSession();
// 获取代理对象
UserDao dao = session.getMapper(UserDao.class);
User user = new User();
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
user.setIds(ids);
// 条件查询
List<User> list = dao.findByIds(user);
for (User user2 : list) {
System.out.println(user2);
}
// 释放资源
session.close();
in.close();
}

编写配置文件

举例1：select * from user where id=1 or id=2 or id=3
<select id="findByIds" parameterType="user" resultType="user">
SELECT * FROM USER
<where>
<foreach collection="ids" open="id=" separator="OR id=" item="i">
#{i}
</foreach>
</where>
</select>

2. 需求二
   1. 需求SQL：SELECT * FROM USER WHERE id IN (1,2,3)
   2. 配置文件编写

<select id="findByIds" parameterType="user" resultType="user">
SELECT * FROM USER
<where>
<foreach collection="ids" open="id IN (" separator="," item="i"
close=")">
#{i}
</foreach>
</where>
</select>