MyBatis 学习（四）动态SQL

最新推荐文章于 2023-08-16 19:34:46 发布

Mr_黑_

最新推荐文章于 2023-08-16 19:34:46 发布

阅读量171

点赞数

分类专栏： MyBatis 文章标签： MyBatis

本文链接：https://blog.csdn.net/qq_36533951/article/details/79172840

版权

MyBatis 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

通过mybatis提供的各种标签方法实现动态拼接sql，这就是动态sql。

IF 标签

接口

public interface UserDao {
   public List<User> selectUserBySexAndUserName (User user) ;
}

Mapper

<?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">
<!-- namespace：命名空间，用于隔离sql -->
<!-- 写SQL语句 -->
<mapper namespace="cn.itcast.dao.UserDao" >
   <select id="selectUserBySexAndUserName" parameterType="User" resultType="User">
      select * from user
      where
      <!-- test里面填判断条件 -->
         <if test="sex != null and sex != ''">
             sex = #{sex}
         </if>
         <if test="username != null and username != ''">
             and username = #{username}
         </if>
   </select>
</mapper>

test

   @Test
   public void testFun2 () throws IOException
   {
       //加载核心配置文件
         String resource = "SqlMapConfig.xml" ;
         InputStream in = Resources.getResourceAsStream(resource) ;

         //创建SqlSessionFactory
         SqlSessionFactory ssf = new SqlSessionFactoryBuilder ().build(in) ;

         //创建SqlSession
         SqlSession sqlSession = ssf.openSession() ;

        UserDao userDao = sqlSession.getMapper(UserDao.class) ;

        User user = new User () ;
        user.setSex("1");
        user.setUsername("张三丰");

        List <User> list = userDao.selectUserBySexAndUserName(user);
         for ( User u  : list )
         {
           System.out.println(u);
         }

   }

若此时将setSex 注释掉那么 SQL语句就无法执行如何解决？
用where 标签

Where标签

可以去掉sql中影响执行的and，前提：and必须在语句的前面

<?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">
<!-- namespace：命名空间，用于隔离sql -->
<!-- 写SQL语句 -->
<mapper namespace="cn.itcast.dao.UserDao" >

   <select id="selectUserBySexAndUserName" parameterType="User" resultType="User">
      select * from user
      <!-- 根据性别和名字查询用户  where 可以去掉语句前的and.
         前提是此语句中的and影响SQL的执行
         比如说此案例中的sex字段，如果此字段为空，
         那么此时的sql语句就变为 select * from user where and username = #{username}
         如果你用了where标签，那么标签就可以帮你把and去掉，让sql语句可以执行，注意只有and在语句的前面，
         where字段才会生效
       -->
      <where>
      <!-- test里面填判断条件 -->
         <if test="sex != null and sex != ''">
             sex = #{sex}
         </if>
         <if test="username != null and username != ''">
             and username = #{username}
         </if>
      </where>
   </select>

</mapper>

SQL片段

即将重复的sql语句部分抽取出来，留作共用
用到两个标签
sql
include

<?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">
<!-- namespace：命名空间，用于隔离sql -->
<!-- 写SQL语句 -->
<mapper namespace="cn.itcast.dao.UserDao" >

   <sql id="select">
      select * from user
   </sql>


   <select id="selectUserBySexAndUserName" parameterType="User" resultType="User">

      <!-- 引用抽取出来的sql片段 -->
      <include refid="select"></include>
      <where>
      <!-- test里面填判断条件 -->
         <if test="sex != null and sex != ''">
             sex = #{sex}
         </if>
         <if test="username != null and username != ''">
             and username = #{username}
         </if>
      </where>

   </select>

</mapper>

foreach标签

从字面上也能看出这是一个循环遍历标签，用于遍历要传递到sql语句的参数

需求：根据多个id查询用户
分为四种情况
1.传递一个数组进去
2.传递一个list集合
3.传递一个包装类，类里面包含一个list
4.传递一个包装类，类里面包含一个数组

先考虑第三种
传递一个包装类，类里面包含一个list
bean

public class Users {
    private User user ;

    List <Integer> list ;

    Integer [] ids  ;

    public List<Integer> getList() {
        return list;
    }

    public void setList(List<Integer> list) {
        this.list = list;
    }

    public Integer[] getIds() {
        return ids;
    }

    public void setIds(Integer[] ids) {
        this.ids = ids;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

public class User implements Serializable {

    private static final long serialVersionUID = 1L;
    private Integer id;
    private String username;// 用户姓名
    private String sex;// 性别
    private Date birthday;// 生日
    private String address;// 地址


    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getSex() {
        return sex;
    }
    public void setSex(String sex) {
        this.sex = sex;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", username=" + username + ", sex=" + sex
                + ", birthday=" + birthday + ", address=" + address + "]";
    }
}

接口

public interface UserDao {

   //根据多个id查询用户信息
// public List<User> selectUserByIds ( Integer [] ids ) ;
// public List<User> selectUserByIds ( List <Integer> ids ) ;
   public List<User> selectUserByIds ( Users users ) ;
}

mapper

<?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">
<!-- namespace：命名空间，用于隔离sql -->
<!-- 写SQL语句 -->
<mapper namespace="cn.itcast.dao.UserDao" >

   <sql id="select">
      select * from user
   </sql>

   <!-- 多个id查询用户 (1,2,3)-->
   <select id="selectUserByIds" parameterType="users" resultType="user">
      <include refid="select"></include>
      <where>
         id in
         <foreach collection="list" item="id" separator="," open="(" close=")" >
            #{id}
         </foreach>
      </where>
   </select>

</mapper>

测试

   @Test
   public void testFun3 () throws IOException
   {
       //加载核心配置文件
         String resource = "SqlMapConfig.xml" ;
         InputStream in = Resources.getResourceAsStream(resource) ;

         //创建SqlSessionFactory
         SqlSessionFactory ssf = new SqlSessionFactoryBuilder ().build(in) ;

         //创建SqlSession
         SqlSession sqlSession = ssf.openSession() ;

        UserDao userDao = sqlSession.getMapper(UserDao.class) ;

        Users user = new Users () ;
        List <Integer> list = new ArrayList <Integer> ();
        list.add(1) ;
        list.add(10) ;
        list.add(12) ;
        user.setList(list) ;

       List<User> list2 =  userDao.selectUserByIds(user);

       for ( User u : list2 )
       {
         System.out.println(u);
       }
   }

传递一个包装类，类里面包含一个数组

bean

public class Users {
    private User user ;

    List <Integer> list ;

    Integer [] ids  ;

    public List<Integer> getList() {
        return list;
    }

    public void setList(List<Integer> list) {
        this.list = list;
    }

    public Integer[] getIds() {
        return ids;
    }

    public void setIds(Integer[] ids) {
        this.ids = ids;
    }

    public User getUser() {
        return user;
    }

    public void setUser(User user) {
        this.user = user;
    }
}

xml

   <!-- 多个id查询用户 (1,2,3)-->
   <select id="selectUserByIds" parameterType="users" resultType="user">
      <include refid="select"></include>
      <where>
         id in
         <foreach collection="ids" item="id" separator="," open="(" close=")" >
            #{id}
         </foreach>
      </where>
   </select>

test

   @Test
   public void testFun4 () throws IOException
   {
       //加载核心配置文件
         String resource = "SqlMapConfig.xml" ;
         InputStream in = Resources.getResourceAsStream(resource) ;

         //创建SqlSessionFactory
         SqlSessionFactory ssf = new SqlSessionFactoryBuilder ().build(in) ;

         //创建SqlSession
         SqlSession sqlSession = ssf.openSession() ;

        UserDao userDao = sqlSession.getMapper(UserDao.class) ;

        Users users = new Users () ;

        Integer [] arr =  new Integer [] { 1 , 12 , 13 };
        users.setIds(arr);

       List<User> list2 =  userDao.selectUserByIds(users);

       for ( User user : list2 )
       {
         System.out.println(user);
       }
   }

下面考虑第一和第二种情况
传递list或数组，那么在collection就要填入关键字 list 或 array 方可遍历，注意遇到这种情况时parameterType 可以瞎写或者不写

array

@Test
   public void testFun4 () throws IOException
   {
       //加载核心配置文件
         String resource = "SqlMapConfig.xml" ;
         InputStream in = Resources.getResourceAsStream(resource) ;

         //创建SqlSessionFactory
         SqlSessionFactory ssf = new SqlSessionFactoryBuilder ().build(in) ;

         //创建SqlSession
         SqlSession sqlSession = ssf.openSession() ;

        UserDao userDao = sqlSession.getMapper(UserDao.class) ;

        Users users = new Users () ;

        Integer [] arr =  new Integer [] { 1 , 15 , 16 };
        users.setIds(arr);

       List<User> list2 =  userDao.selectUserByIds(arr);

       for ( User user : list2 )
       {
         System.out.println(user);
       }
   }

   <!-- 多个id查询用户 (1,2,3)-->
   <select id="selectUserByIds"  resultType="user">
      <include refid="select"></include>
      <where>
         id in
         <foreach collection="array" item="id" separator="," open="(" close=")" >
            #{id}
         </foreach>
      </where>
   </select>

list

   @Test
   public void testFun6 () throws IOException
   {
       //加载核心配置文件
         String resource = "SqlMapConfig.xml" ;
         InputStream in = Resources.getResourceAsStream(resource) ;

         //创建SqlSessionFactory
         SqlSessionFactory ssf = new SqlSessionFactoryBuilder ().build(in) ;

         //创建SqlSession
         SqlSession sqlSession = ssf.openSession() ;

        UserDao userDao = sqlSession.getMapper(UserDao.class) ;

        List <Integer> list = new ArrayList <Integer> ();
        list.add(1) ;
        list.add(10) ;
        list.add(12) ;

       List<User> list2 =  userDao.selectUserByIds(list);

       for ( User u : list2 )
       {
         System.out.println(u);
       }
   }

   <!-- 多个id查询用户 (1,2,3)-->
   <select id="selectUserByIds"  resultType="user">
      <include refid="select"></include>
      <where>
         id in
    <!-- foreach标签，进行遍历 -->
       <!-- collection：遍历的集合，这里是QueryVo的ids属性 -->
       <!-- item：遍历的项目，可以随便写，，但是和后面的#{}里面要一致 -->
       <!-- open：在前面添加的sql片段 -->
       <!-- close：在结尾处添加的sql片段 -->
       <!-- separator：指定遍历的元素之间使用的分隔符 -->
         <foreach collection="list" item="id" separator="," open="(" close=")" >
            #{id}
         </foreach>
      </where>
   </select>

Mr_黑_

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis 学习（四）动态SQL

通过mybatis提供的各种标签方法实现动态拼接sql，这就是动态sql。IF 标签接口public interface UserDao { public List selectUserBySexAndUserName (User user) ;}Mapper<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mappe
复制链接

扫一扫