MyBatis之动态sql

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);
}

<?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 ="getUserById" parameterType="Integer" resultMap="users"
        select include refid/> from `user` where id=#{Id}
    select>    
    
　　　　<!-- 
　　　　if标签主要适用于多条件查询，例如分页查询，比如博客，比如时间日期，标签类型，时间类型等等
　　   单个条件可以使用下面的例子
      如果条件多的话，建议在<if></if>外嵌套<where>标签
　　　　-->
     ="getUserByName"="String"
         select     id,user_name from `user` where
         if test="userName!=null and userName !=''"
              user_name like concat ('%',#{userName},'%')             
         if>
     >


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

mapper>

> 
      
     <!-- 
     Choose相当于Java中的
     if(..){
     }else if(..){
     }else{
     
     }
     otherwise这里就不做演示了，otherwise相当于否则的意思，可以说就是else
      
      注意 在外层必须嵌套<where>标签,否则会一直显示sql错误
      -->
      
           ="Map"
           select * from `user`
           where>
           choosewhen ="Id!=null and Id!=''"
               and id = #{Id}
           when>
            ="userName != null and userName != ''"
               and  user_name = #{userName}
            >     
           >
          
     >
     
    >

 java.util.List;
 java.util.Map;

 UserMapper {

    
    User getUserByName(Map<String,Object> map);
}

 java.util.HashMap;
 org.apache.ibatis.session.SqlSession;

class TestMyBatis {

    static void main(String[] args) {
        SqlSession sqlSession = null;
        sqlSession = MyBatisExample.getSqlSessionFactory().openSession();
        
        UserMapper userMapper = sqlSession.getMapper(UserMapper.);
        User users = new User();
        users.setId(1);
        users.setUserName();
        Map<String,Object> map = new HashMap<String,1)">();
        map.put("user",users);
        User user = userMapper.getUserByName(map);
        System.out.println(user.getUserName());
        
        
        

    }
}

>
     
      
     trim意味着去掉一些特殊字符
     trim prefix表示前缀 通常加上where的话，表示已经将where写进前缀，这时就不用写where了
     prefixOverrides 表示将第一個and或者or去掉 
      -->
      
         select id,user_name from `user`
         trim prefix="where" prefixOverrides="and|or"="userName!=null"
             and user_name = #{userName}    
             >
         trim>
  
     
    >

 cn.mybatis;



 UserMapper {

    
    
    User getUserByName(@Param ("userName") String userName);
}

);
        User user = userMapper.getUserByName("张飞");
        System.out.println(user.getUserName());
        
        
        

    }
}

>


     
      

     <!--
  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 ="updateUserInfo"="User"
     update `user`
     ="set"=","="userName !=null and userName !=''"
     user_name = #{userName}
     
    where id= #{Id}
    update>
         
     UserMapper {

    
    
    int updateUserInfo(User user);
    
}

;
        sqlSession = MyBatisExample.getSqlSessionFactory().openSession(true);
        
        UserMapper userMapper = sqlSession.getMapper(UserMapper.);
        User user =  User();
        user.setId(1);
        user.setUserName("张好s");
        int lines =userMapper.updateUserInfo(user);
        if(lines==1) {
            System.out.println("Yes");
            
        }else {
            System.out.println("No");
        }
        
        

    }
}