MyBatis动态sql

为什么使用动态SQL？

使用动态sql可以方便的根据不同条件拼接sql语句。

（1）多条件查询

<?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.jd.userinfo.dao.IUserInfoDao">
	<select id="select" resultType="UserInfo">
		<!-- 注意：如果数据库表属性名与自定义类中属性名不一样，需要使用别名法(如下)或者在mybatis-config.xml配置驼峰命名转换
			select id,user_name userName,mobile,address from user_inf
		 -->
		select id,user_name,mobile,address
		from user_inf
		<!-- where标签起到where 1=1 的作用；但其原理为消除第一个and -->
		<where>
			<if test="userName!=null and userName!=''">
				and user_name like #{userName}
			</if>
			<if test="mobile!=null and mobile!=''">
				and mobile like #{mobile}
			</if>
		</where>
	</select>
</mapper>

（2）删除多条指定数据

<?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.jd.userinfo.dao.IUserInfoDao">
	<delete id="delete">
		delete from user_inf where id in
                <!-- 实现一次删除多条记录需要通过数组以及foreach标签实现动态构建sql语句 -->
		<foreach item="id" collection="ids" open="(" separator="," close=")">
			#{id}
		</foreach>
	</delete>
</mapper>

（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.jd.userinfo.dao.IUserInfoDao">
	<update id="update">
		update user_inf
                <!-- 与多条件查询类似需要使用if子标签，但要使用set标签实现自动拼接自动消除最后一句sql的“，” -->
		<set>
			<if test="userName!= null and userName!=''">
				user_name = #{userName},
			</if>
			<if test="mobile!= null and mobile!=''">
				mobile = #{mobile},
			</if>
		</set>
		<where>
			where id = #{id}
		</where>
	</update>
</mapper>

mapper接口展示

IUserInfoDao.java

package com.jd.userinfo.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.jd.vo.UserInfo;

public interface IUserInfoDao {

	List<UserInfo> select(@Param("userName")String userName,@Param("mobile")String mobile);
	
	boolean delete(@Param("ids")int [] ids);
	
	boolean update(@Param("id")int id,@Param("userName")String userName,@Param("mobile")String mobile);
}

测试

Test.java

public class Test {

	public static void main(String[] args) {
		try {
			InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
			SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
			SqlSession sqlSession = sqlSessionFactory.openSession();
			IUserInfoDao userInfoDao = sqlSession.getMapper(IUserInfoDao.class);
                        //查询
			for (UserInfo ui : userInfoDao.select("w","")) {
				System.out.println(ui);
			}
                        //删除
                        System.out.println(userInfoDao.delete(new int [] {1,3,8}));
                        //更改
			System.out.println(userInfoDao.update(1,"walylz", "18866993333"));
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}