20220629_MyBatis_数据库增删改查练习(三)

void add(Brand brand);
1

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

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

执行代码，注意修改表项内容的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();

    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

因为被回滚了两次添加，所以不回滚这次的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>
1
2
3
4

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

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

int update(Brand brand);
1

生成sql语句映射标签

    <update id="update">
        update mybatis.brand
        set brand_name = #{brandName},
            company_name = #{companyName},
            ordered = #{ordered},
            description = #{description},
            status = #{status}
        where id = #{id}
    </update>
1
2
3
4
5
6
7
8
9

写执行代码，这里我梦在会话的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();

    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

结果图

    <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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

执行代码里，就修改一个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();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

看日志预编译的语句，智能

结果图