注解实现Mybatis插入数据返回自增的主键Id
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); } } }
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。
加载全部内容