如何用注解的方式实现Mybatis插入数据时返回自增的主键Id

用注解实现Mybatis插入数据返回自增的主键Id

我们在数据库表设计的时候，一般都会在表中设计一个自增的id作为表的主键。这个id也会关联到其它表的外键。

这就要求往表中插入数据时能返回表的自增id，用这个ID去给关联表的字段赋值。下面讲一下如何通过注解的方式实现插入数据时返回自增Id。

设计数据库表

CREATE TABLE `tbl_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL DEFAULT '',
 `age` int(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

设计Java bean对象

public class User
{
private int userId = -1;
private String name = "";

private int age = -1;

@Override
public String toString()
{
return "name:" + name + "|age:" + age;
}
public int getUserId()
{
return userId;
}
public void setUserId(int userId)
{
this.userId = userId;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public int getAge()
{
return age;
}
public void setAge(int age)
{
this.age = age;
}
}

添加mapper接口

@Mapper
public interface UserMapper
{
@Insert("insert into tbl_user (name, age) values (#{name}, #{age})")
@Options(useGeneratedKeys=true, keyProperty="userId", keyColumn="id")
void insertUser(User user);
}

通过以上几个步骤就可以实现在插入user到数据库时返回自增ID。数据插入成功后，id值被反填到user对象中，调用getUserId()就可以获取。

上面的写法有个地方需要注意一下： 如果insertUser使用了@Param注解，如：void insertUser(@Param(“user”) User user)，keyProperty需要指定为 user.userId。

Mybatis注解增(返回自增id) 删查改以及(一对一，一对多，多对多)

数据库表

目录结构

导入坐标（包）

<dependencies>
       <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <version>5.1.32</version>
       </dependency>
       <dependency>
           <groupId>org.mybatis</groupId>
           <artifactId>mybatis</artifactId>
           <version>3.4.6</version>
       </dependency>
       <dependency>
           <groupId>junit</groupId>
           <artifactId>junit</artifactId>
           <version>4.12</version>
           <scope>test</scope>
       </dependency>
       <dependency>
           <groupId>log4j</groupId>
           <artifactId>log4j</artifactId>
           <version>1.2.12</version>
       </dependency>
   </dependencies>

配置文件

jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/springboot
jdbc.username=root
jdbc.password=111111

sqlMapConfig.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 resource="jdbc.properties"/>
<!--    别名-->
   <typeAliases>
       <package name="com.zyw.domain"/>
   </typeAliases>
   <!--    注册自定义类型处理器-->
   <typeHandlers>
       <typeHandler handler="com.zyw.typeHandler.TypeHandler"/>
   </typeHandlers>
   <environments default="development">
       <environment id="development">
           <transactionManager type="JDBC"></transactionManager>
           <dataSource type="POOLED">
               <property name="driver" value="${jdbc.driver}"/>
               <property name="url" value="${jdbc.url}"/>
               <property name="username" value="${jdbc.username}"/>
               <property name="password" value="${jdbc.password}"/>
           </dataSource>
       </environment>
   </environments>
   <mappers>
       <package name="com.zyw.mapper"/>
   </mappers>
</configuration>

log4j.properties

### direct log messages to stdout ###
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=％d{ABSOLUTE} ％5p ％c{1}:％L - ％m％n
### direct messages to file mylog.log ###
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=c:/mylog.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=％d{ABSOLUTE} ％5p ％c{1}:％L - ％m％n
### set log levels - for more verbose logging change 'info' to 'debug' ###
log4j.rootLogger=debug, stdout

实体类

Order

package com.zyw.domain;
public class Order {
   private Integer id;
   private String ordername;
   private Integer ordernum;
   private User user;
   @Override
   public String toString() {
       return "Order{" +
               "id=" + id +
               ", ordername='" + ordername + '\'' +
               ", ordernum=" + ordernum +
               ", user=" + user +
               '}';
   }
   public Integer getId() {
       return id;
   }
   public void setId(Integer id) {
       this.id = id;
   }
   public String getOrdername() {
       return ordername;
   }
   public void setOrdername(String ordername) {
       this.ordername = ordername;
   }
   public Integer getOrdernum() {
       return ordernum;
   }
   public void setOrdernum(Integer ordernum) {
       this.ordernum = ordernum;
   }
   public User getUser() {
       return user;
   }
   public void setUser(User user) {
       this.user = user;
   }
}

Role

package com.zyw.domain;
import java.util.List;
public class Role {
   private Integer id;
   private String roleName;
   private String roleDesc;
   private List<User> userList;
   public void setUser(List<User> userList) {
       this.userList = userList;
   }
   public List<User> getUser() {
       return userList;
   }
   @Override
   public String toString() {
       return "Role{" +
               "id=" + id +
               ", roleName='" + roleName + '\'' +
               ", roleDesc='" + roleDesc + '\'' +
               ", user=" + userList +
               '}';
   }
   public Integer getId() {
       return id;
   }
   public void setId(Integer id) {
       this.id = id;
   }
   public String getRoleName() {
       return roleName;
   }
   public void setRoleName(String roleName) {
       this.roleName = roleName;
   }
   public String getRoleDesc() {
       return roleDesc;
   }
   public void setRoleDesc(String roleDesc) {
       this.roleDesc = roleDesc;
   }
}

User

package com.zyw.domain;
import java.util.Date;
import java.util.List;
public class User {
   private Integer id;
   private String username;
   private String email;
   private String password;
   private Long phoneNum;
   private Date birthday;
   private List<Order> orderList;
   public List<Order> getOrderList() {
       return orderList;
   }
   public void setOrderList(List<Order> orderList) {
       this.orderList = orderList;
   }
   public Date getBirthday() {
       return birthday;
   }
   public void setBirthday(Date birthday) {
       this.birthday = birthday;
   }
   public Integer getId() {
       return id;
   }
   public void setId(Integer id) {
       this.id = id;
   }
   public String getUsername() {
       return username;
   }
   public void setUsername(String username) {
       this.username = username;
   }
   public String getEmail() {
       return email;
   }
   public void setEmail(String email) {
       this.email = email;
   }
   public String getPassword() {
       return password;
   }
   public void setPassword(String password) {
       this.password = password;
   }
   public void setPhoneNum(Long phoneNum) {
       this.phoneNum = phoneNum;
   }
   public Long getPhoneNum() {
       return phoneNum;
   }
   @Override
   public String toString() {
       return "User{" +
               "id=" + id +
               ", username='" + username + '\'' +
               ", email='" + email + '\'' +
               ", password='" + password + '\'' +
               ", phoneNum=" + phoneNum +
               ", birthday=" + birthday +
               ", orderList=" + orderList +
               '}';
   }
}

mapper接口编写

OrderMapper

public interface OrderMapper {
   @Select("select * from orders ")
   @Results({
           @Result(column = "id",property = "id"),
           @Result(column = "ordername",property = "ordername"),
           @Result(column = "ordernum",property = "ordernum"),
           @Result(
                   property = "user",
                   column = "uid",
                   javaType = User.class,
                   one = @One(select = "com.zyw.mapper.UserMapper.findById")
           )
   })
   public List<Order> findAll();
   @Select("select * from orders where uid=#{uid}")
   public List<Order> findByUid(Integer uid);
}

RoleMapper

public interface RoleMapper {
   @Select("select * from sys_role")
   @Results({
           @Result(column = "id",property = "id"),
           @Result(column = "roleName",property = "roleName"),
           @Result(column = "roleDesc",property = "roleDesc"),
           @Result(
                   property = "userList",
                   column = "id",
                   javaType = List.class,
                   many = @Many(select = "com.zyw.mapper.UserMapper.findUserAndRoleById")
           )
   })
   public List<Role> findRoleAndUser();
}

UserMapper （注意自增id是返回到实体类）

public interface UserMapper {
   @Select("select * from sys_user")
   public List<User> findAll();
   @Select("select * from sys_user where id=#{id}")
   public User findById(Integer id);
   @Insert("insert into sys_user values(#{id},#{username},#{email},#{password},#{phoneNum},#{birthday})")
   @Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")
   public void insertUser(User user);
   @Update("update sys_user set password=#{password} where id=#{id}")
   public void updateUser(User user);
   @Delete("delete from sys_user where id=#{id}")
   public void deleteUser(Integer id);
   @Select("select * from sys_user")
   @Results({
           @Result(column = "id",property = "id"),
           @Result(column = "username",property = "username"),
           @Result(column = "password",property = "password"),
           @Result(column = "email",property = "email"),
           @Result(column = "phoneNum",property = "phoneNum"),
           @Result(column = "birthday",property = "birthday"),
           @Result(
                   property = "orderList", //封装的属性名称
                   column = "id", //根据哪个字段去查询order表中的数据
                   javaType = List.class, //返回类型
                   many = @Many(select = "com.zyw.mapper.OrderMapper.findByUid")
           )
   })
   public List<User> findUserAndOrder();
   @Select("select * from sys_user u,sys_user_role ur where u.id=ur.userId and ur.roleId=#{id}")
   public List<User> findUserAndRoleById(Integer id);
}

测试      

单表增删查改

AnnoTest

public class AnnoTest {
   private UserMapper mapper ;
   @Before
   public void before() throws IOException {
       InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
       SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
       SqlSession sqlSession = build.openSession(true);
        mapper=sqlSession.getMapper(UserMapper.class);
   }
   @Test
   public void testFindAll(){
       List<User> userList = mapper.findAll();
       for (User user : userList) {
           System.out.println(user);
       }
   }
   @Test
   public void testFindById(){
       User byId = mapper.findById(1);
       System.out.println(byId);
   }
   @Test
   public void testInsertUser(){
       User user=new User();
       user.setUsername("小张");
       user.setPassword("951753");
       user.setPhoneNum(13449484984L);
       user.setEmail("951@qq.com");
       user.setBirthday(new Date());
       mapper.insertUser(user);
System.out.println(user.getId());
   }
   @Test
   public void testUpdateUser(){
       User user=new User();
       user.setId(1);
       user.setPassword("123456");
       mapper.updateUser(user);
   }
   @Test
   public void testDeleteUser(){
       mapper.deleteUser(9);
   }

一对一

AnnoTestOneToOne

public class AnnoTestOneToOne {
   private OrderMapper mapper ;
   @Before
   public void before() throws IOException {
       InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
       SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
       SqlSession sqlSession = build.openSession(true);
       mapper=sqlSession.getMapper(OrderMapper.class);
   }
   @Test
   public void testDemo(){
       List<Order> orderList = mapper.findAll();
       for (Order order : orderList) {
           System.out.println(order);
       }
   }
}

一对多

AnnoTestOneToMany

public class AnnoTestOneToMany {
   private UserMapper mapper ;
   @Before
   public void before() throws IOException {
       InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
       SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
       SqlSession sqlSession = build.openSession(true);
       mapper=sqlSession.getMapper(UserMapper.class);
   }
   @Test
   public void testDemo(){
       List<User> userList = mapper.findUserAndOrder();
       for (User user : userList) {
           System.out.println(user);
       }
   }
}

多对多

AnnoTestManyToMany

public class AnnoTestManyToMany {
   private RoleMapper mapper ;
   @Before
   public void before() throws IOException {
       InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
       SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
       SqlSession sqlSession = build.openSession(true);
       mapper=sqlSession.getMapper(RoleMapper.class);
   }
   @Test
   public void testDemo(){
       List<Role> roleList = mapper.findRoleAndUser();
       for (Role role : roleList) {
           System.out.println(role);
       }
   }
}

来源：https://blog.csdn.net/ylforever/article/details/79191182