我们知道MySql中主键可以设成auto_increment,也就是自增主键,每当新增一条记录,主键值自动加1。
在MyBatis中,执行insert,返回值为受影响的语句行数,此操作跟JDBC一致。
但是如果应用中需要将插入的值的主键返回时,这时有两种方式:
第一种方式:在mapper的insert节点中添加useGeneratedKey和keyProperty属性
第二种方式:在mapper的insert节点下添加selectKey子节点的方式
下面是具体的使用方式举例:
一、MySql中mybatis数据库的users表结构
- +
- | Field | Type | Null | Key | Default | Extra |
- +
- | id | int(11) | NO | PRI | NULL | auto_increment |
- | name | varchar(50) | YES | | NULL | |
- | password | varchar(255) | YES | | NULL | |
- +
二、MyBatis配置文件 mybatis-config.xml
- <?xml version="1.0" encoding="UTF-8" ?>
- <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
-
- <configuration>
-
-
- <properties>
- <property name="driver" value="com.mysql.jdbc.Driver"/>
- <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
- <property name="username" value="root"/>
- <property name="password" value="zzlibdmt"/>
- </properties>
-
-
-
-
-
- <typeAliases>
- <package name="cn.kolbe.mybatis.domain"/>
- </typeAliases>
-
-
-
-
- <environments default="development">
- <environment id="development">
-
- <transactionManager type="JDBC"></transactionManager>
-
-
-
-
- <dataSource type="POOLED">
- <property name="driver" value="${driver}"/>
- <property name="url" value="${url}"/>
- <property name="username" value="${username}"/>
- <property name="password" value="${password}"/>
- </dataSource>
- </environment>
- </environments>
-
- <mappers>
- <mapper resource="cn/kolbe/mybatis/domain/UserMapper.xml"/>
- </mappers>
- </configuration>
三、MyBatis的mapper映射文件 UserMapper.xml
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/mybatis-3-mapper.dtd">
- <mapper namespace="cn.kolbe.mybatis.domain.UserMapper">
-
- <insert id="getGeneratedKey1" parameterType="cn.kolbe.mybatis.domain.User">
- insert into users(name, password) values(#{name}, #{password})
- </insert>
-
-
- <insert id="getGeneratedKey2" parameterType="cn.kolbe.mybatis.domain.User" useGeneratedKeys="true" keyProperty="id">
- insert into users(name, password) values(#{name}, #{password})
- </insert>
-
- <insert id="getGeneratedKey3" parameterType="cn.kolbe.mybatis.domain.User">
-
-
- <selectKey keyProperty="id" resultType="int">
- select LAST_INSERT_ID()
- </selectKey>
- insert into users(name, password) values(#{name}, #{password})
- </insert>
-
- </mapper>
四、普通的Java实体类User
- package cn.kolbe.mybatis.domain;
-
- public class User {
-
- private Integer id;
- private String name;
- private String password;
-
- public User(String name, String password) {
- this.name = name;
- this.password = password;
- }
-
- public Integer getId() {
- return id;
- }
- public void setId(Integer id) {
- this.id = id;
- }
- public String getName() {
- return name;
- }
- public void setName(String name) {
- this.name = name;
- }
- public String getPassword() {
- return password;
- }
- public void setPassword(String password) {
- this.password = password;
- }
- @Override
- public String toString() {
- return "User [id=" + id + ", name=" + name + ", password=" + password + "]";
- }
-
- }
五、测试代码
- package cn.kolbe.mybatis;
-
- import java.io.FileInputStream;
- import java.io.FileNotFoundException;
- import java.io.InputStream;
-
- 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 cn.kolbe.mybatis.domain.User;
-
- public class MyBatisTest {
-
-
-
-
-
-
- @Test
- public void testGetGeneratedKey1() throws FileNotFoundException {
- SqlSession session = factory.openSession();
- String statement = "cn.kolbe.mybatis.domain.UserMapper.getGeneratedKey1";
- User user = new User("User_01","123456");
- int result = session.update(statement, user);
- session.commit();
- System.out.println(user);
- System.out.println("result:" + result);
-
- }
-
-
-
-
-
-
- @Test
- public void testGetGeneratedKey2() throws FileNotFoundException {
- SqlSession session = factory.openSession();
- String statement = "cn.kolbe.mybatis.domain.UserMapper.getGeneratedKey2";
- User user = new User("User_02","123456");
-
-
-
- int result = session.update(statement, user);
- session.commit();
- System.out.println(user);
- System.out.println("result:" + result);
- }
-
-
-
-
-
-
-
-
-
- @Test
- public void testGetGeneratedKey3() throws FileNotFoundException {
- SqlSession session = factory.openSession();
- String statement = "cn.kolbe.mybatis.domain.UserMapper.getGeneratedKey3";
- User user = new User("User_03", "123456");
-
-
-
- int result = session.update(statement, user);
- session.commit();
- System.out.println(user);
- System.out.println("result:" + result);
- }
-
-
-
-
-
- @Before
- public void init() throws Exception {
- InputStream in = new FileInputStream("src/main/java/mybatis-config.xml");
- factory = new SqlSessionFactoryBuilder().build(in);
- }
-
- private SqlSessionFactory factory;
- }
注:
1)使用selectKey的方式获取主键,相当于调用了MySql的LAST_INSERT_ID()函数,意思是查询最后一次插入的ID,相当于执行了两条语句
2)两种方式的主键获取方式都是通过MyBatis为我们自动将id值赋值给传入的javaBean,而不是通过update语句返回,也就是update返回的同jdbc一致(影响的行数),获取id的方式是执行完update后调用user.getId()方法,具体可参看上面的例子。