• MyBatis之动态sql


    如果使用JDBC或者Hibernate等ORM框架,很多时候你要根据需要去拼接sql,这是一个很麻烦的事情。而MyBatis提供对动态sql的组装能力,而且它只有为数不多的几个基本元素,简单明了,大量的判断可以在MyBatis的映射文件xml进行配置,以达到我们需要编写大量代码才能实现的需求。动态sql减少了很多编写代码量的工作。

    这从中又体现了MyBatis的优点,可配置性,灵活性和可维护性。

    注意该例子在MyBatis+Hibernate+JDBC分析对比的文章上基础进行的

    下面进行对上述表中的例子进行演示:

    package cn.mybatis;
    
    import org.apache.ibatis.annotations.Param;
    
    public interface UserMapper {
    
        
        User getUserById(Integer Id);
        //注意@Param 适用场景针对于参数n<=5的情况下进行,如果参数n大于5以上建议将参数改为JavaBean
        User getUserByName(@Param("userName") String userName);
    }

    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="cn.mybatis.UserMapper">
        <!--如果一部分字段查询比较频繁,建议使用sql标签,将查询过频繁的字段放进去,这样方面管理和修改-->    
        <sql id="user">
        id,user_name
        </sql>
        
        <select id="getUserById" parameterType="Integer" resultMap="users">
            select <include refid="user"/> from `user` where id=#{Id}
        </select>    
        
        <!--
        if标签主要适用于多条件查询,例如分页查询,比如博客,比如时间日期,标签类型,时间类型等等
       单个条件可以使用下面的例子
    如果条件多的话,建议在<if></if>外嵌套<where>标签
        --> <select id="getUserByName" parameterType="String" resultMap="users"> select id,user_name from `user` where <if test="userName!=null and userName !=''"> user_name like concat ('%',#{userName},'%') </if> </select>

    <resultMap type="User" id="users"> <id column="id" property="Id"/> <result column="user_name" property="userName"/> </resultMap> </mapper>

    choosey演示示例:

    <?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="cn.mybatis.UserMapper"> 
          
         <!-- 
         Choose相当于Java中的
         if(..){
         }else if(..){
         }else{
         
         }
         otherwise这里就不做演示了,otherwise相当于否则的意思,可以说就是else
          
          注意 在外层必须嵌套<where>标签,否则会一直显示sql错误
          -->
          
               <select id="getUserByName" parameterType="Map" resultMap="users">
               select * from `user`
               <where>
               <choose>
               <when test="Id!=null and Id!=''">
                   and id = #{Id}
               </when>
                <when test="userName != null and userName != ''">
                   and  user_name = #{userName}
                </when>     
               </choose>
               </where>
              
         </select>
         
        <resultMap type="User" id="users">
        <id column="id" property="Id"/>
        <result column="user_name" property="userName"/>
        </resultMap>
    
    </mapper>

    接口类

    package cn.mybatis;
    
    import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.annotations.Param;
    
    public interface UserMapper {
    
        
        User getUserByName(Map<String,Object> map);
    }

    测试类

    package cn.mybatis;
    
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.session.SqlSession;
    
    public class TestMyBatis {
    
        public static void main(String[] args) {
            SqlSession sqlSession = null;
            sqlSession = MyBatisExample.getSqlSessionFactory().openSession();
            
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User users = new User();
            users.setId(1);
            users.setUserName(null);
            Map<String,Object> map = new HashMap<String,Object>();
            map.put("user", users);
            User user = userMapper.getUserByName(map);
            System.out.println(user.getUserName());
            
            
            
    
        }
    }

    trim演示示例:

    <?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="cn.mybatis.UserMapper">
         
         <!-- 
         trim意味着去掉一些特殊字符
         trim prefix表示前缀 通常加上where的话,表示已经将where写进前缀,这时就不用写where了
         prefixOverrides 表示将第一個and或者or去掉 
          -->
          <select id="getUserByName" parameterType="String" resultMap="users">
             select id,user_name from `user`
             <trim prefix="where" prefixOverrides="and|or">
                <if test="userName!=null">
                 and user_name = #{userName}    
                 </if>
             </trim>
         </select>
      
         
        <resultMap type="User" id="users">
        <id column="id" property="Id"/>
        <result column="user_name" property="userName"/>
        </resultMap>
    
    </mapper>

    接口类:

    package cn.mybatis;
    
    
    
    import org.apache.ibatis.annotations.Param;
    
    public interface UserMapper {
    
        
        
        User getUserByName(@Param ("userName") String userName);
    }

    测试类:

    package cn.mybatis;
    
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.session.SqlSession;
    
    public class TestMyBatis {
    
        public static void main(String[] args) {
            SqlSession sqlSession = null;
            sqlSession = MyBatisExample.getSqlSessionFactory().openSession();
            
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = userMapper.getUserByName("张飞");
            System.out.println(user.getUserName());
            
            
            
    
        }
    }

    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="cn.mybatis.UserMapper">
    
    
         
          
    
         <!--
    set 当满足条件时继续下一个,否则报错,这里也相当于做一个判断验证
    --> <!-- <update id="updateUserInfo" parameterType="User"> update `user` <set> <if test="userName != null and userName !='' "> user_name = #{userName} </if> </set> where id = #{Id} </update> -->
    <!--
    trim在这里也可以应用
    --> <update id="updateUserInfo" parameterType="User"> update `user` <trim prefix="set" prefixOverrides=","> <if test="userName !=null and userName !=''"> user_name = #{userName} </if> </trim> where id= #{Id} </update> <resultMap type="User" id="users"> <id column="id" property="Id"/> <result column="user_name" property="userName"/> </resultMap> </mapper>

    接口类:

    package cn.mybatis;
    
    import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.annotations.Param;
    
    public interface UserMapper {
    
        
        
        int updateUserInfo(User user);
        
    }

    测试类:

    package cn.mybatis;
    
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    import org.apache.ibatis.session.SqlSession;
    
    public class TestMyBatis {
    
        public static void main(String[] args) {
            SqlSession sqlSession = null;
            sqlSession = MyBatisExample.getSqlSessionFactory().openSession(true);
            
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = new User();
            user.setId(1);
            user.setUserName("张好s");
            int lines =userMapper.updateUserInfo(user);
            if(lines==1) {
                System.out.println("Yes");
                
            }else {
                System.out.println("No");
            }
            
            
    
        }
    }

    foreach这里就不演示了,参照我的博文批量更新,那里写的比较详细

    当然接下来还有bind,不过这个不常用,以上说的包括没说的foreach是开发过程中常用的动态sql。

  • 相关阅读:
    [luogu3393]逃离僵尸岛
    [BZOJ2818]GCD
    [SCOI2015]情报传递
    [NOIP2010]引水入城
    [luogu4315]月下“毛景树”
    「LibreOJ NOI Round #2」不等关系
    [HNOI2013]游走
    Yet Another Minimization Problem
    ZJOI2015 地震后的幻想乡
    [九省联考2018]一双木棋chess
  • 原文地址:https://www.cnblogs.com/youcong/p/8722761.html
Copyright © 2020-2023  润新知