MyBatis set标签

精华

小牛编辑

159浏览

2023-03-14

在 Mybatis 中，update 语句可以使用 set 标签动态更新列。set 标签可以为 SQL 语句动态的添加 set 关键字，剔除追加到条件末尾多余的逗号。

示例

要求：根据 id 修改网站名称或网址（本节示例基于《第一个MyBatis程序》一节的代码实现）。

WebsiteMapper.xml 代码如下。

<?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="net.biancheng.mapper.WebsiteMapper">
    <select id="selectWebsite" resultType="net.biancheng.po.Website">
        SELECT * FROM website
        <where>
            <if test="id!=null and id!=''">
                id=#{id}
            </if>
        </where>
    </select>

    <!--使用set元素动态修改一个网站记录 -->
    <update id="updateWebsite"
        parameterType="net.biancheng.po.Website">
        UPDATE website
        <set>
            <if test="name!=null">name=#{name}</if>
            <if test="url!=null">url=#{url}</if>
        </set>
        WHERE id=#{id}
    </update>
</mapper>

WebsiteMapper 类中方法如下。

package net.biancheng.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import net.biancheng.po.Website;

public interface WebsiteMapper {
    public List<Website> selectWebsite(Website site);
   
    public int updateWebsite(Website site);
}

测试类代码如下。

package net.biancheng.test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

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 net.biancheng.mapper.WebsiteMapper;
import net.biancheng.po.Website;

public class Test {
    public static void main(String[] args) throws IOException {
        InputStream config = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(config);
        SqlSession ss = ssf.openSession();

        Website site = new Website();
        site.setId(1);
        site.setUrl("www.xnip.cn");

        // 执行update语句前
        List<Website> siteList = ss.getMapper(WebsiteMapper.class).selectWebsite(site);
        for (Website st : siteList) {
            System.out.println(st);
        }

        int num = ss.getMapper(WebsiteMapper.class).updateWebsite(site);
        System.out.println("影响数据库行数" + num);

        // 执行update语句后
        List<Website> siteList2 = ss.getMapper(WebsiteMapper.class).selectWebsite(site);
        for (Website st : siteList2) {
            System.out.println(st);
        }
        ss.commit();
        ss.close();
    }
}

输出结果如下。

DEBUG [main] - ==> Preparing: SELECT * FROM website WHERE id=?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
Website[id=1,name=编程帮,url=http://www.xnip.cn/,age=10,country=CN]
DEBUG [main] - ==> Preparing: UPDATE website SET url=? where id=?
DEBUG [main] - ==> Parameters: www.xnip.cn(String), 1(Integer)
DEBUG [main] - <==    Updates: 1
影响数据库行数1
DEBUG [main] - ==> Preparing: SELECT * FROM website WHERE id=?
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
Website[id=1,name=编程帮,url=www.xnip.cn,age=10,country=CN]