Mybatis saveOrUpdate SelectKey非主

传送：

Spring大观园，我有过的困惑或许你也有！
看透ClassLoader
一起掌握并发

为什么有这篇文章:

• 有些情况下,我们在db插入操作时,会有这样的逻辑,不存在就插入,存在就更新.
• 有些情况下,更新db某个记录中字段值+N或者-N,比如库存数 如何得到本次操作后的结果呢?

demo环境事项:

• mybatis + mysql ,利用mysql 的 insert on duplicate 机制
• 数据库中id作为主键

1. insertOrUpdate :到底是插入还是更新呢:

• 方法1:

插入的情况:主键不存在就插入,方法返回结果为1
更新的情况:重复的情况下,就更新,方法返回结果为2

• 方法2:

方法执行后,参数student中的age属性值来判断.
借助mybatis的selectkey机制, 可以将db中age字段的值赋予student参数的age属性.
比如:首次插入时,age值是0;若是更新,sql内部有+1,age的值就>0(1....N),根据实际并发插入的情况.

留意这段代码:

<selectKey resultType="int" keyProperty="age" order="AFTER">
      SELECT age FROM student where id =#{id}
</selectKey>

2. updateAdd :更新某个字段值+N或者-N 如何得到本次操作的结果呢?

还是这段代码

<selectKey resultType="int" keyProperty="age" order="AFTER">
      SELECT age FROM student where id =#{id}
</selectKey>

selectKey并非只能生成主键的时候来使用.我的理解中insert和update没有像select那样的结果集,selectkey就是对一些写操作之后又想获取一些值的补充,从其文档来看,也可以操作多个列.有待进一步研究学习.

整个demo

<?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="com.***.demo.mapper.StudentMapper">
  <resultMap id="BaseResultMap" type="com.***.demo.entity.Student">
    <!--
      WARNING - @mbg.generated
    -->
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="age" jdbcType="INTEGER" property="age" />
  </resultMap>
  <insert id="insertOrUpdate" >
    insert into student(id,name,age) values (#{id},#{name},#{age})
    on duplicate key update age = age + 1;
    <selectKey resultType="int" keyProperty="age" order="AFTER">
      SELECT age FROM student where id =#{id}
    </selectKey>
  </insert>

  <update id="updateAdd" keyColumn="age" keyProperty="age" >
    update student set age = age + 1 where id =#{id};
    <selectKey resultType="int" keyProperty="age" order="AFTER">
      SELECT age FROM student where id =#{id}
    </selectKey>
  </update>
</mapper>

public class Student implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    private Integer age;

    private static final long serialVersionUID = 1L;

    /**
     * @return id
     */
    public Long getId() {
        return id;
    }

    /**
     * @param id
     */
    public void setId(Long id) {
        this.id = id;
    }

    /**
     * @return name
     */
    public String getName() {
        return name;
    }

    /**
     * @param name
     */
    public void setName(String name) {
        this.name = name;
    }

    /**
     * @return age
     */
    public Integer getAge() {
        return age;
    }

    /**
     * @param age
     */
    public void setAge(Integer age) {
        this.age = age;
    }
}

import com.***.demo.entity.Student;
import tk.mybatis.mapper.common.Mapper;

public interface StudentMapper extends Mapper<Student> {
    public int insertOrUpdate(Student student);
    public int updateAdd(Student student);
}

public class TestStudentMapper {

    @Autowired
    private StudentMapper studentMapper;


    @Test
    public void TestInsert(){
        try {
            Student student = new Student();
            student.setId(1L);
            student.setName("lisi2");
            student.setAge(1);
            int result = studentMapper.insertOrUpdate(student);
            System.out.println(student);

            Student student1 = new Student();
            student1.setId(8L);
            student1.setName("lisi2");
            student1.setAge(1);
            int result1= studentMapper.updateAdd(student1);
            System.out.println(student1);

        }catch (Exception exp){
            exp.printStackTrace();
        }
    }
}