1、MyBatis中的动态SQL,实现不确定条件的CRUD的实现
将查询条件封装到map里面,通过 if 和 where 标签的使用,实现sql的动态性
2、动态SQL,实现不确定条件的查询
2.1 不确定条件查询的案列
2.2 不确定条件查询的实现
2.2.1 mapper接口
public interface UserMapper {
// 动态查询用户
public List<User> findUserByIdDongTaiSql(Map<String, Object> map);
}
2.2.2 mapper.xml <where> 和 <if> 标签的用法
<!-- 利用动态sql的查询 -->
<select id="findUserByIdDongTaiSql" parameterType="map"
resultType="user">
select * from user
<where>
<if test="id!=null">
and id = #{id}
</if>
<if test="username!=null">
and username like '%${username}%'
</if>
</where>
</select>
2.2.3 测试代码
public class Mybatis_Four_Test {
// 会话工厂
private SqlSessionFactory sqlSessionFactory;
@Before
public void createSqlSessionFactory() throws IOException {
// 配置文件
String resource = "SqlMapConfig.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
// 使用SqlSessionFactoryBuilder从xml配置文件中创建SqlSessionFactory
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
// one list foreach
@Test
public void testfindAll() {
// 获取session
SqlSession session = sqlSessionFactory.openSession();
// 获取mapper接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("id", 16);
map.put("username", "小明");
List<User> users = userMapper.findUserByIdDongTaiSql(map);
for (int i = 0; i < users.size(); i++) {
System.out.println(users.get(i));
}
// 关闭session
session.close();
}
}
3、动态SQL,实现不确定条件的更新
3.1 不确定条件更新的案列
3.2 不确定条件更新的实现
3.2.1 mapper接口
// 动态更新用户
public void updateUserDongTaiSql(Map<String, Object> map);
3.2.2 mapper.xml
<!-- 利用动态sql的更新 -->
<update id="updateUserDongTaiSql" parameterType="map">
update user
<!--set标签,会自动判断哪个是最后一个字段,并去除逗号 -->
<set>
<if test="id!=null">
username = #{username},
</if>
<if test="username!=null">
address = #{address},
</if>
</set>
where id = #{id}
</update>
3.2.3 测试代码
public void testupdateUserDongTaiSql() {
// 获取session
SqlSession session = sqlSessionFactory.openSession();
// 获取mapper接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("id", 29);
map.put("username", "张小三");
map.put("address", "浙江省");
userMapper.updateUserDongTaiSql(map);
session.commit();
// 关闭session
session.close();
4、动态SQL,实现不确定条件的删除
4.1 不确定条件删除的案列
4.2 不确定条件删除的实现
4.2.1 Array形式的实现
4.2.1.1 mapper接口
// 动态删除,Array的形式
public void deleteUserDongTaiSqlArray(int[] ids);
4.2.1.2 mapper.xml
<!--利用动态sql的删除 -->
<!--根据User的id,删除用户 delete from user where id in(1,2,3) -->
<!--Array的形式 -->
<delete id="deleteUserDongTaiSqlArray" parameterType="map">
delete from user where id in
<foreach collection="array" open="(" close=")" separator=","
item="id">
#{id}
</foreach>
</delete>
4.2.1.3 测试代码
// 数组的形式
@Test
public void testdeleteUserDongTaiSqlArray() {
// 获取session
SqlSession session = sqlSessionFactory.openSession();
// 获取mapper接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
int ids[] = { 26, 29 };
session.commit();
userMapper.deleteUserDongTaiSqlArray(ids);
// 提交
session.commit();
// 关闭session
session.close();
}
4.2.2 AList形式 的实现
4.2.2.1 mapper接口
// 动态删除用户,List的形式
public void deleteUserDongTaiSqlList(List<Integer> ids);
4.2.2.2 mapper.xml
<!--list的形式 -->
<delete id="deleteUserDongTaiSqlList" parameterType="java.util.List">
delete from user where id in
<foreach collection="list" open="(" close=")" separator=","
item="id">
#{id}
</foreach>
</delete>
4.2.2.3 测试代码
// list的形式
@Test
public void testdeleteUserDongTaiSqlList() {
// 获取session
SqlSession session = sqlSessionFactory.openSession();
// 获取mapper接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
List<Integer> list = Arrays.asList(34, 35, 36);
userMapper.deleteUserDongTaiSqlList(list);
session.commit();
// 关闭session
session.close();
}
5、动态SQL,实现不确定条件的插入
5.1 不确定条件查询的案列
5.2 不确定条件查询的实现
5.2.1 mapper接口
// 动态插入用户
public void insertUserDongTaiSql(User user);
5.2.2 mapper.xml
<!-- sql片段,对应字段名称,id属性值任意 -->
<sql id="key">
<trim suffixOverrides=",">
<if test="username!=null">
username,
</if>
<if test="sex!=null">
sex,
</if>
</trim>
</sql>
<!-- sql片段,对用?,id属性值任意 -->
<sql id="value">
<trim suffixOverrides=",">
<if test="username!=null">
#{username},
</if>
<if test="sex!=null">
#{sex},
</if>
</trim>
</sql>
<!-- 动态sql的插入 -->
<insert id="insertUserDongTaiSql" parameterType="user">
insert into user(
<include refid="key" />
) values(
<include refid="value" />
)
</insert>
5.2.3 测试代码
public void testinsertUserDongTaiSqlList() {
// 获取session
SqlSession session = sqlSessionFactory.openSession();
// 获取mapper接口的代理对象
UserMapper userMapper = session.getMapper(UserMapper.class);
User user = new User();
user.setAddress("广东釜山");
user.setSex("1");
user.setUsername("李小三");
userMapper.insertUserDongTaiSql(user);
session.commit();
// 关闭session
session.close();
}