Mybatis中如何使用动态sql代码篇

小瑶同学ct341688

已于 2024-07-05 11:30:40 修改

阅读量180

点赞数

文章标签： mybatis sql java

于 2023-08-12 12:08:07 首次发布

本文链接：https://blog.csdn.net/ct341688/article/details/132245960

版权

什么是mybatis的动态sql

动态sql就是在运行时候根据不同的条件动态生成sql语句的一种技术

动态sql可以实现什么功能

1.语句的动态拼接

2.前后缀格式处理

3.复杂的参数处理

动态sql常用的标签有什么

1.if ---->类似于Java中的if语句

if标签语法
<if  test = "条件判断，返回true或false" >
	SQL语句
</if>

2.where --->简化SQL语句中where子句处理智能处理and、or等关键字

where标签语法
<where>
	<if test="条件判断">
		SQL语句
	</if>
	…
</where>

3.choose ---->是一个组合标签，通常与when、otherwise标签配合使用类似于Java中switch语句

choose标签语法

<choose>
	<when test="条件判断，返回true或false">
	</when>
	<when test="条件判断，返回true或false">
	</when>
	...
	<otherwise>
	</otherwise>
</choose>

4.foreach ---->迭代一个集合，通常用于in条件

foreach 标签语法
解析:1.open表示起始位置的拼接字符
     2.separator 表示元素之间的连接符
     3.close 表示结束位置的拼接字符

<foreach collection = "参数名称"   
               item = "元素别名" 
               open = "("
               separator = "," 
               close = ")" 
               index = "当前元素位置下标" >
	#{元素别名}
</foreach>

5.set ---->简化SQL语句中set子句处理智能忽略更新语句尾部多出来的逗号

set标签语法

<set>
	<if test="条件判断">
		SQL语句
	</if>
	…
</set>

6.trim ----->动态地为SQL语句添加前后缀智能忽略标签前后多余的and、or或逗号等字符

trim标签语法

<trim prefix = "前缀" 
	  suffix = "后缀" 
	  prefixOverrides = "忽略前缀" 
	  suffixOverrides = "忽略后缀" >
	…
</trim>

案例测试中所用到的工具类

工具类
public class SqlSessionUtil {

     private static SqlSessionFactory sqlSessionFactory;

     private SqlSessionUtil(){}

    static {
         String config = "mybatis-config.xml";
        try {
            InputStream is = Resources.getResourceAsStream(config);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSession openSession(){
         return sqlSessionFactory.openSession();
    }
}

动态sql标签的使用案例关键代码

基于if实现动态sql的查询


接口
    /**
     * 基于if的动态sql
     * @param sysUser
     * @return
     */
    List<SysUser>selectIf(SysUser sysUser);



SysuerMapper.xml 映射

parameterType:指所对应的实体类
resultType:返回的类型

<!-- 基于if的动态sql -->
    <select id="selectIf" resultType="pojo.SysUser" parameterType="pojo.SysUser">
        <trim prefixOverrides="and |or"> <!--忽略掉and和or-->
        select* from t_sysuser where 1=1
        <if test="realName!=null">
            and realName=#{realName}
        </if>
        <if test="phone!=null">
            and phone=#{phone}
        </if>
        </trim>
    </select>


测试
 /**
     * 基于if的动态sql
     */

    @Test
    public void selectIf(){
        SqlSession sqlSession=SqlSessionUtil.openSession();
        SysUserMapper mapper=sqlSession.getMapper(SysUserMapper.class);
        SysUser sysUser=new SysUser();
        sysUser.setRealName("张华");//如果都为空就查询所有
        sysUser.setPhone(null);
        List<SysUser>list=mapper.selectIf(sysUser);
        System.out.println(list);
    }

基于where实现动态sql查询

接口

    /**
     * 基于where的动态sql
     * @param sysUser
     * @return
     */
    List<SysUser>selectWhere(SysUser sysUser);


映射文件
 <!--基于where的动态sql(会自动处理and和or)-->

    <select id="selectWhere" parameterType="pojo.SysUser" resultType="pojo.SysUser">
        select*from t_sysuser
        <where>
            <if test="realName!=null">
                and realName=#{realName}
            </if>
            <if test="phone!=null">
                and phone=#{phone}
            </if>
        </where>
    </select>


测试
 /**
     * 基于where的动态sql
     */
    @Test
    public void selectWhere(){
        SqlSession sqlSession=SqlSessionUtil.openSession();
        SysUserMapper mapper=sqlSession.getMapper(SysUserMapper.class);
        SysUser sysUser=new SysUser();
        sysUser.setRealName("张三");//如果都为空就查询所有
        sysUser.setPhone(null);
        List<SysUser>list=mapper.selectWhere(sysUser);
        System.out.println(list);
    }

set+if动态sql的修改

接口
 /**
     * 基于set+if的动态sql的修改
     * @param sysUser
     * @return
     */
    int updateSet(SysUser sysUser);


映射文件
<!--基于set+if的动态sql-->
    <update id="updateSet" parameterType="pojo.SysUser">
        update t_sysuser
        <set>
            <if test="account!=null">account=#{account},</if>  <!--逗号切记不能少-->
            <if test="realName!=null">account=#{realName}</if>
        </set>
        where id=#{id}
    </update>



测试类  set+if的修改
/***
     * 基于set+if的动态sql的修改
     */
    @Test
    public void updateSet(){
        SqlSession sqlSession=SqlSessionUtil.openSession();
        SysUserMapper mapper=sqlSession.getMapper(SysUserMapper.class);
        SysUser sysUser=new SysUser();
        sysUser.setAccount("zhangSan111");
        sysUser.setRealName("张三2");
        sysUser.setId(22);
        int count=mapper.updateSet(sysUser);
        sqlSession.commit();//提交事务
        if(count>0){
            System.out.println("修改成功!");
        }else{
            System.out.println("修改失败!");
        }
    }

trim+if动态sql的修改

接口
/**
     * 基于trim+if的动态sql的修改
     * @param sysUser
     * @return
     */
    int updateTrim(SysUser sysUser);


所对应的映射文件
    <!--基于trim+if的动态sql的修改-->
    <!-- prefix加一个前缀 suffixOverrides指忽略后缀 suffix增加后缀-->
   <update id="updateTrim" parameterType="pojo.SysUser">
       update t_sysuser
       <trim  prefix="set" suffix="where id=#{id}" prefixOverrides=",">
           <if test="account!=null">account=#{account},</if>  <!--逗号切记不能少-->
            <if test="realName!=null">account=#{realName}</if>
        </trim>
    </update>


测试类
/**
     * trim+if的动态sql的修改
     */
    @Test
    public void updateTrim(){
        SqlSession sqlSession=SqlSessionUtil.openSession();
        SysUserMapper mapper=sqlSession.getMapper(SysUserMapper.class);
        SysUser sysUser=new SysUser();
        sysUser.setAccount("zhangSan111");
        sysUser.setRealName("张三1");
        sysUser.setId(22);
        int count=mapper.updateTrim(sysUser);
        sqlSession.commit();//提交事务 更新数据库的数据
        if(count>0){
            System.out.println("修改成功!");
        }else{
            System.out.println("修改失败!");
        }
    }

trim+if动态sql的查询

接口
/**
     * 基于trim+if动态sql的查询
     * @param sysUser
     * @return
     */
    List<SysUser>selectTrim(SysUser sysUser);


映射文件
<!--基于trim+if的查询-->
    <select id="selectTrim" parameterType="pojo.SysUser" resultType="pojo.SysUser">
        select*from t_sysuser
        <trim prefix="where" prefixOverrides="and |or">
            <if test="account!=null">
                and account=#{account}
            </if>
            <if test="realName!=null">
                and realName=#{realName}
            </if>
        </trim>
    </select>

测试类
/**
     * 基于trim+if的查询
     */
    @Test
    public void selectTrim(){
        SqlSession sqlSession=SqlSessionUtil.openSession();
        SysUserMapper mapper=sqlSession.getMapper(SysUserMapper.class);
        SysUser sysUser=new SysUser();
        sysUser.setRealName("张三");//如果都为空就查询所有
        sysUser.setPhone(null);
        List<SysUser>list=mapper.selectTrim(sysUser);
        System.out.println(list);
    }

foreach实现动态sql的查询

接口
 /**
     * 基于foreach动态sql的查询 常用于in关键字
     * @param list
     * @return
     */
    List<SysUser>selectForeach(List list);


映射文件
    <!-- collection 遍历的类型，可以写形参的名字 )-->
    <!-- open 条件的开始 -->
    <!-- close 条件的结束 -->
    <!-- item  遍历集合时候定义的临时变量，存储当前遍历的每一个值 -->
    <!-- separator 多个值之间用逗号拼接-->
    <!-- #{item} 获取遍历的每一个值,与item定义的临时变量一致-->

    <select id="selectForeach" parameterType="java.util.List" resultType="pojo.SysUser">
        select*from t_sysuser
        where realName in
        <foreach item="item" index="index" collection="list"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>


测试类

    /**
     * 基于foreach的动态sql的查询
     */
    @Test
    public void selectForeach(){
        SqlSession sqlSession=SqlSessionUtil.openSession();
        SysUserMapper mapper=sqlSession.getMapper(SysUserMapper.class);
        List list=new ArrayList();
        list.add("张三");
        List<SysUser>list2=mapper.selectForeach(list);
        System.out.println(list2);
    }

基于if 动态sql的删除

接口
/**
     * 根据id删除系统用户
     * @param id
     * @return
     */
    Integer deleteUser1(@Param("id") Integer id);

mapper映射

    <!--动态sql删除-->
    <delete id="deleteUser1" parameterType="java.lang.Integer">
        delete from t_sysuser where
        <if test="id!=null">
         id=#{id}
        </if>
    </delete>

测试类
 @Test
    public void deleteUser1() {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        SysUserMapper mapper = sqlSession.getMapper(SysUserMapper.class);
        int count = mapper.deleteUser1(23);
        sqlSession.commit();//提交事务 用于数据库的数据更新
        if (count > 0) {
            System.out.println("删除成功!");
        } else {
            System.out.println("删除失败!");
        }
    }

基于 trim+if 动态sql的新增

/**
     * 动态sql的新增
     * @param sysUser
     * @return
     */
    Integer insertUser1(SysUser sysUser);



mapper映射类
 <!-- 动态sql的新增-->
解析:suffixOverrides 表示忽略分号
     prefix 表示前缀
     suffix表示后缀


    <insert id="insertUser1" parameterType="pojo.SysUser">
        INSERT INTO `mybatis`.`t_sysuser`
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null"> id, </if>
            <if test="account != null"> account, </if>
            <if test="realName != null"> realName, </if>
            <if test="password != null"> password, </if>
            <if test="sex != null"> sex, </if>
            <if test="birthday != null"> birthday, </if>
            <if test="phone != null"> phone, </if>
            <if test="address != null"> address, </if>
            <if test="roleId != null"> roleId, </if>
            <if test="createdUserId != null"> createdUserId, </if>
            <if test="createdTime != null"> createdTime, </if>
            <if test="updatedUserId != null"> updatedUserId, </if>
            <if test="updatedTime != null"> updatedTime, </if>
            <if test="idPicPath != null"> idPicPath, </if>
            <if test="workPicPath != null"> workPicPath, </if>
            <if test="islock != null"> islock, </if>
        </trim>
        VALUES
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null"> #{id}, </if>
            <if test="account != null"> #{account}, </if>
            <if test="realName != null"> #{realName}, </if>
            <if test="password != null"> #{password}, </if>
            <if test="sex != null"> #{sex}, </if>
            <if test="birthday != null"> #{birthday}, </if>
            <if test="phone != null"> #{phone}, </if>
            <if test="address != null"> #{address}, </if>
            <if test="roleId != null"> #{roleId}, </if>
            <if test="createdUserId != null"> #{createdUserId}, </if>
            <if test="createdTime != null"> #{createdTime}, </if>
            <if test="updatedUserId != null"> #{updatedUserId}, </if>
            <if test="updatedTime != null"> #{updatedTime}, </if>
            <if test="idPicPath != null"> #{idPicPath}, </if>
            <if test="workPicPath != null"> #{workPicPath}, </if>
            <if test="islock != null"> #{islock}, </if>
        </trim>
    </insert>

测试类

    /**
     * 新增系统用户信息
     *
     * @throws ParseException
     */
    @Test
    public void insertUser1() throws ParseException {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        SysUserMapper mapper = sqlSession.getMapper(SysUserMapper.class);
        SysUser sysUser = new SysUser();
        sysUser.setId(25);
        sysUser.setAccount("zhangSan");
        sysUser.setRealName("张三3");
        sysUser.setPassword("0123");
        sysUser.setSex(1);
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        sysUser.setBirthday(new Timestamp(format.parse("1985-08-05").getTime()));
        sysUser.setPhone("15198562232");
        sysUser.setAddress("娄底市新化县");
        sysUser.setRoleId(3);
        sysUser.setCreatedUserId(2);
        SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd-HH:mm:ss");
        sysUser.setCreatedTime(format1.parse("2019-05-06-10:54:02"));
        sysUser.setUpdatedUserId(null);
        sysUser.setUpdatedTime(null);
        sysUser.setIdPicPath(null);
        sysUser.setWorkPicPath(null);
        sysUser.setislock(1);
        int count = mapper.insertUser1(sysUser);
        sqlSession.commit();
        if (count > 0) {
            System.out.println("新增成功!");
        } else {
            System.out.println("新增失败!");
        }
    }

基于trim+if的动态sql的修改

接口
    /**
     * 使用动态sql改造修改
     * @param sysUser
     * @return
     */
    Integer updateUser1(SysUser sysUser);

mapper.xml映射文件类
 <!-- 动态sql改造的修改-->

解析:
prefix表示加前缀
suffixOverrides表示忽略逗号
suffix表示添加后缀

    <update id="updateUser1" parameterType="pojo.SysUser">
        UPDATE `t_sysuser`
        <trim prefix="set" suffixOverrides="," suffix="where id= #{id}">
            <if test="account!=null">account=#{account},</if>
            <if test="realName!=null">realName=#{realName},</if>
            <if test="password!=null">password=#{password},</if>
            <if test="sex!=null">sex=#{sex},</if>
            <if test="birthday!=null">birthday=#{birthday},</if>
            <if test="phone!=null">phone=#{phone},</if>
            <if test="address!=null">address=#{address},</if>
            <if test="roleId!=null">roleId=#{roleId},</if>
            <if test="createdUserId!=null">createdUserId=#{createdUserId},</if>
            <if test="createdTime!=null">createdTime=#{createdTime},</if>
            <if test="updatedUserId!=null">updatedUserId=#{updatedUserId},</if>
            <if test="updatedTime!=null">updatedTime=#{updatedTime},</if>
            <if test="idPicPath!=null">idPicPath=#{idPicPath},</if>
            <if test="workPicPath!=null">workPicPath=#{workPicPath},</if>
            <if test="islock!=null">islock=#{islock},</if>
        </trim>
    </update>

测试类
 /**
     * 修改系统用户
     */
    @Test
    public void updateUser1() throws ParseException {
        SqlSession sqlSession = SqlSessionUtil.openSession();
        SysUserMapper mapper = sqlSession.getMapper(SysUserMapper.class);
        SysUser sysUser = new SysUser();
        sysUser.setId(22);
        sysUser.setAccount("zhangSan");
        sysUser.setRealName("张三和2");
        sysUser.setPassword("0");
        sysUser.setSex(1);
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        sysUser.setBirthday(new Timestamp(format.parse("1985-08-05").getTime()));
        sysUser.setPhone("15198562232");
        sysUser.setAddress("娄底市新化县");
        sysUser.setRoleId(3);
        sysUser.setCreatedUserId(2);
        SimpleDateFormat format1 = new SimpleDateFormat("yyyy-MM-dd-HH:mm:ss");
        sysUser.setCreatedTime(format1.parse("2019-05-06-10:54:02"));
        sysUser.setUpdatedUserId(null);
        sysUser.setUpdatedTime(null);
        sysUser.setIdPicPath(null);
        sysUser.setWorkPicPath(null);
        sysUser.setislock(1);
        int count = mapper.updateUser1(sysUser);
        sqlSession.commit();
        if (count > 0) {
            System.out.println("修改成功!");
        } else {
            System.out.println("修改失败!");
        }
    }

使用注解的方法实现查询

  /**
     * 使用map查询入库的供应商 并满足入库单编号
     * @param map
     * @return
     * 在实体类中没有的字段 可以使用注解的方法 @param("字段") 对应映射文件中#{字段}
     */
    List<TStoragerecord>selectGoodsMap(@Param("map") Map<String,String>map);


映射文件类

<!-- 封装的两表字段(供应商表和入库记录表)-->
    <resultMap id="storageResultMap" type="pojo.TStoragerecord">
        <id column="id" property="id" />
        <result column="goodsName" property="goodsName" />
        <result column="supplierId" property="supplierId" />
        <result column="totalAmount" property="totalAmount" />
        <result column="payStatus" property="payStatus" />
        <result column="createdTime" property="createdTime" />
        <association property="tSupplier" javaType="pojo.TSupplier">
            <result column="supName" property="supName" />
        </association>
    </resultMap>


  <!--使用foreach根据编码模糊匹配查询-->

解析:storageResultMap指的是上面封装的两表字段的id
open表示以括号开始  close表示以括号结束

    <select id="selectGoodsMap" resultMap="storageResultMap">
        SELECT s.*, s.supName
        FROM t_storagerecord stor, t_supplier s
        WHERE stor.supplierId = s.id
        AND srCode like concat
        <foreach item="srCode" collection="map" open="(" separator="," close=")">
            '%',#{srCode},'%'
        </foreach>
    </select>

测试类

 /**
     * 使用map查询商品信息
     */
    @Test
    public void selectGoodsMap(){
        SqlSession sqlSession=SqlSessionUtil.openSession();
        SysUserMapper mapper=sqlSession.getMapper(SysUserMapper.class);
        Map map=new HashMap();
        map.put("map","BILL2016_001");
        List<TStoragerecord>list1= mapper.selectGoodsMap(map);
        System.out.println(list1);
    }