目录
一、引入
在数据库对数据进行增删改查时,难免都希望sql语句的可复用性提高,那么就避免不了使得sql语句中传入的参数动态化,本篇就详述MyBatis中能够实现动态sql语句的标签。
二、MyBatis中的动态Sql标签
(1)if
配置:
<?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="com.jd.vo.UserInfo">
select * from user_info
<where>
<if test="name != null">
and name like #{name}
</if>
</where>
</select>
</mapper>
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.jd.vo.UserInfo;
public interface IUserInfoDao {
List<UserInfo> select(@Param("name") String name);
}
使用:
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.jd.userInfo.dao.IUserInfoDao;
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("%m%")) {
System.out.println(ui);
}
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
运行结果:
(2)where
where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句;而且,若语句的开头为“AND”或“OR”,where 元素也会将它们
配置:
<?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="com.jd.vo.UserInfo">
select * from user_info
<where>
name like #{name}
</where>
</select>
</mapper>
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.jd.vo.UserInfo;
public interface IUserInfoDao {
List<UserInfo> select(@Param("name") String name);
}
使用:
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.jd.userInfo.dao.IUserInfoDao;
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("%m%")) {
System.out.println(ui);
}
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
运行结果:
(3)set
set 元素可以用于动态包含需要更新的列,而删掉无关的逗号
配置:
<?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_info
<set>
<if test="name!=null">
name=#{name},
</if>
<if test="mobile!=null">
mobile=#{mobile},
</if>
</set>
where id=#{id}
</update>
</mapper>
import org.apache.ibatis.annotations.Param;
public interface IUserInfoDao {
boolean update(@Param("id")int id,@Param("name") String name,@Param("mobile")String mobile);
}
使用:
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.jd.userInfo.dao.IUserInfoDao;
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);
boolean flag = userInfoDao.update(5, "admin", "110");
System.out.println(flag);
sqlSession.commit();
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
运行结果:
数据库表修改前:
数据库表修改后:
(4)foreach
foreach元素用于对一个集合进行遍历,构建 IN 条件语句时常用该元素;foreach 元素允许指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量,也允许指定开头与结尾的字符串以及在迭代结果之间放置分隔符。
配置:
<?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_info where id in
<foreach item="id" collection="ids" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
</mapper>
import org.apache.ibatis.annotations.Param;
public interface IUserInfoDao {
boolean delete(@Param("ids") int [] ids);
}
调用:
import java.io.IOException;
import java.io.InputStream;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import com.jd.userInfo.dao.IUserInfoDao;
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);
System.out.println(userInfoDao.delete(new int[] {1,3}));
sqlSession.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}