用注解实现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);
}
}
}
以上为个人经验，希望能给大家一个参考，也希望大家多多支持Devmax。