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

时间:2022-09-27 Elon.Yang 人气:0

用注解实现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);
        }
    }
}

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。

加载全部内容