MyBatis的数据变更(增删改)

1 简单插入

配置文件:

<insert id="insert" parameterType="com.rl.model1.Person">
        insert into person (person_id, name, gender, person_addr, birthday)
         values (#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday})
    </insert>

测试类:

package com.rl.test;

import java.io.InputStream;
import java.util.Date;

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 org.junit.Before;
import org.junit.Test;

import com.rl.model1.Person;

public class MyBatisTest3 {

    SqlSessionFactory sqlSessionFactory;
    
    @Before
    public void setUp() throws Exception {
        //加载配置文件
        InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
        //初始化
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    }

    @Test
    public void test() {
        //创建一个session
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        Person p = new Person();
        p.setName("黄忠");
        p.setBirthday(new Date());
        p.setGender(1);
        p.setPersonAddr("上海");
        try {
            int count = sqlSession.insert("com.rl.mapper.PersonMapper.insert", p);
            System.out.println(count);
            //只要是数据库的变更都要提交事务(MyBatis将数据库的隔离级别设置为可提交读)
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally{
            sqlSession.close();
        }
    }
}

此时数据库中插入一条数据

插入一个知识点: 返回主键id

配置文件:

<insert id="insert" parameterType="com.rl.model1.Person">
    <!-- 
        selectKey标签: 返回主键
        keyProperty: 实体类中的主键名称
        order: 插入的sql和生成主键的顺序(mysql是AFTER, oracle是BEFORE)
        resultType: 返回在数据类型(主键自然是Integer类型)
        select LAST_INSERT_ID(): mysql中生成主键的语句
     -->
        <selectKey keyProperty="personId" order="AFTER" resultType="java.lang.Integer">
            select LAST_INSERT_ID()
        </selectKey>
        insert into person (person_id, name, gender, person_addr, birthday)
         values (#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday})
    </insert>

2 简单修改

配置文件:

<update id="update" parameterType="com.rl.model1.Person">
        update person t set 
        t.name = #{name},
        t.gender = #{gender},
        t.birthday = #{birthday},
        t.person_addr = #{personAddr}
        where t.person_id = #{personId}
    </update>

测试代码(部分):

@Test
    public void test1() {
        //创建一个session
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        Person p = new Person();
        p.setPersonId(2);
        p.setPersonAddr("重庆");
        p.setGender(2);
        p.setName("郑十");
        p.setBirthday(new Date());
        try {
            int count = sqlSession.update("com.rl.mapper.PersonMapper.update", p);
            System.out.println(count);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally{
            sqlSession.close();
        }
    }

这种简单修改有种缺点, 一旦某些字段没有赋值, 则修改之后该字段为null, 需要使用动态sql的方法(后续提供)

3 简单删除:

配置文件:

<delete id="delete" parameterType="java.lang.Integer">
        delete from person where person_id = #{personId}
    </delete>

测试代码(部分):

@Test
    public void test2() {
        //创建一个session
        SqlSession sqlSession = sqlSessionFactory.openSession();
        
        try {
            int count = sqlSession.delete("com.rl.mapper.PersonMapper.delete", 5);
            System.out.println(count);
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally{
            sqlSession.close();
        }
    }