20220629_MyBatis_数据库增删改查练习(三)_mybatis 增删改查练习题-优快云博客

本文链接：https://blog.youkuaiyun.com/ClarkGable/article/details/125530645

本文详细介绍了MyBatis中添加与修改操作的实现步骤，包括如何在映射文件中编写SQL语句，以及如何在Java代码中执行这些操作。重点讲解了动态SQL的使用，通过`<if>`标签实现条件判断，从而进行部分字段的更新。同时，展示了如何获取自增主键的值，并强调了事务管理的重要性，如需手动提交事务。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

文章目录

添加操作
第一步，接口加方法，不用返回值

void add(Brand brand);

映射文件里生成sql语句，如果要写sql语句时有自动提示，在一切设置完毕之后，必须用数据库名点表名，才能正确自动提示，可能是新版新特性

    <insert id="add">
        insert into mybatis.brand (brand_name, company_name, ordered, description, status)
        values (#{brandName},#{companyName},#{ordered},#{description},#{status})
    </insert>

执行代码，注意修改表项内容的sql，在mybatis里要手动提交，不然会rolling back，即使执行成功，数据库里查看也没有

    @Test
    public void testAdd() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqs = sqlSessionFactory.openSession();

        BrandMapper mapper = sqs.getMapper(BrandMapper.class);

        int status = 1;
        String companyName = "nokia";
        String brandName = "aikon";
        Integer ordered = 16;
        String desc = "you never forget nokia";

        Brand br = new Brand();
        br.setStatus(status);
        br.setCompanyName(companyName);
        br.setBrandName(brandName);
        br.setDescription(desc);
        br.setOrdered(ordered);

        mapper.add(br);
        /**注意必须本方法执行后，必须手动提交*/
        sqs.commit();

        sqs.close();

    }

因为被回滚了两次添加，所以不回滚这次的id自动变成了6
请添加图片描述
如果要在添加表项后获得自动生成的id，把映射语句的标签加两个字段

    <insert id="add" useGeneratedKeys="true" keyProperty="id">
        insert into mybatis.brand (brand_name, company_name, ordered, description, status)
        values (#{brandName},#{companyName},#{ordered},#{description},#{status})
    </insert>

在方法执行之后用对象Brand的get方法获得id即可

        mapper.add(br);
        /**注意必须本方法执行后，必须手动提交*/
        sqs.commit();
        System.out.println(br.getId());

请添加图片描述

文章目录

修改操作
接口新建方法，获取返回值int，影响的行数

int update(Brand brand);

生成sql语句映射标签

    <update id="update">
        update mybatis.brand
        set brand_name = #{brandName},
            company_name = #{companyName},
            ordered = #{ordered},
            description = #{description},
            status = #{status}
        where id = #{id}
    </update>

写执行代码，这里我梦在会话的openSession(true)方法里加参数true，打开自动提交，就不用手动commit了

    @Test
    public void testUpdate() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqs = sqlSessionFactory.openSession(true);

        BrandMapper mapper = sqs.getMapper(BrandMapper.class);

        int status = 1;
        String companyName = "Simon";
        String brandName = "siemens";
        Integer ordered = 17;
        String desc = "you still remember siemens";
        int id = 6;

        Brand br = new Brand();
        br.setStatus(status);
        br.setCompanyName(companyName);
        br.setBrandName(brandName);
        br.setDescription(desc);
        br.setOrdered(ordered);
        br.setId(id);

        int lines = mapper.update(br);
        System.out.println(lines);

        sqs.close();

    }

结果图
请添加图片描述

文章目录

动态修改，先把sql映射语句改成如下，关键字set换成标签

    <update id="update">
        update mybatis.brand
        <set>
            <if test="brandName != null and brandName != ''">
                brand_name = #{brandName},
            </if>
            <if test="companyName != null and companyName != ''">
                company_name = #{companyName},
            </if>
            <if test="ordered != null">
                ordered = #{ordered},
            </if>
            <if test="description != null and description != ''">
                description = #{description},
            </if>
            <if test="status != null">
                status = #{status}
            </if>
        </set>
        where id = #{id}
    </update>

执行代码里，就修改一个status

        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqs = sqlSessionFactory.openSession(true);

        BrandMapper mapper = sqs.getMapper(BrandMapper.class);

        int status = 0;
        String companyName = "Simon";
        String brandName = "siemens";
        Integer ordered = 17;
        String desc = "you still remember siemens";
        int id = 6;

        Brand br = new Brand();
        br.setStatus(status);
//        br.setCompanyName(companyName);
//        br.setBrandName(brandName);
//        br.setDescription(desc);
//        br.setOrdered(ordered);
        br.setId(id);

        int lines = mapper.update(br);
        System.out.println(lines);

        sqs.close();