注解形式@Insert("insert into anchor_admin (aid,uid) values (#{aid},#{uid})")
int insert(AnchorAdmin anchorAdmin);
主要是需要添加对象,然后获取直接通过获取对象的 id 即可获取到新增的主键了@Service
public class AnchorAdminService {
@Autowired
AnchorAdminDao anchorAdminDao;
public int add(int aid,int uid){
AnchorAdmin anchorAdmin = new AnchorAdmin();
anchorAdmin.setAid(aid);
anchorAdmin.setUid(uid);
int row = anchorAdminDao.insert(anchorAdmin);
System.out.println(anchorAdmin.getId());
return row;
}
}
XML形式
useGeneratedKeys="true" keyColumn="id" keyProperty="id"
parameterType="userPo">
INSERT INTO `user` (`name`,sex,register_ts) VALUES (#{name},#{sex},#{registerTs})
然后代码中以对象的形式插入,插入完毕之后,获取对象的 id 就是新增的主键了User user = new User(username,sex,TsUtil.now());
if(userDao.add(user) > 0){
System.out.println(user.getId());
}
错误笔记
第一次使用 Mybatis 注解的形式来写入,想返回自增的主键@Insert("insert into user_auth (uid,auth) values (#{uid}, #{auth})")
int insert(@Param("uid")int uid, @Param("auth")String auth);
执行的时候出现如下错误org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.binding.BindingException: Parameter 'id' not found. Available parameters are [uid, auth, param1, param2]
原来需要配合使用@SelectKey@Insert("insert into user_auth (uid,auth) values (#{uid}, #{auth})")
@SelectKey(statement="SELECT LAST_INSERT_ID()",keyProperty="id",before=false, resultType=int.class)
int insert(@Param("uid")int uid, @Param("auth")String auth);
但是问题还是没解决,实际发现不管怎么样都是返回1
下面是 debug 信息16:44:45.516 [nioEventLoopGroup-3-1] DEBUG org.mybatis.spring.SqlSessionUtils - Creating a new SqlSession
16:44:45.520 [nioEventLoopGroup-3-1] DEBUG org.mybatis.spring.SqlSessionUtils - SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@405d4d1c] was not registered for synchronization because synchronization is not active
16:44:45.530 [nioEventLoopGroup-3-1] DEBUG o.s.jdbc.datasource.DataSourceUtils - Fetching JDBC Connection from DataSource
16:44:45.535 [nioEventLoopGroup-3-1] DEBUG o.m.s.t.SpringManagedTransaction - JDBC Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@5a24d003 [wrapping: com.mysql.jdbc.JDBC4Connection@689f7624]] will not be managed by Spring
16:44:45.539 [nioEventLoopGroup-3-1] DEBUG m.t.awesome.t16.dao.UserDao.insert - ooo Using Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@5a24d003 [wrapping: com.mysql.jdbc.JDBC4Connection@689f7624]]
16:44:45.546 [nioEventLoopGroup-3-1] DEBUG m.t.awesome.t16.dao.UserDao.insert - ==> Preparing: insert into user(`name`,`sex`,`birthday`,`location`,`register_ts`) values (?, ?, ?, ?, ?)
16:44:45.590 [nioEventLoopGroup-3-1] DEBUG m.t.awesome.t16.dao.UserDao.insert - ==> Parameters: mengkang(String), 1(Integer), 0(Integer), null, 1453452147(Integer)
16:44:45.622 [nioEventLoopGroup-3-1] DEBUG m.t.awesome.t16.dao.UserDao.insert - <== Updates: 1
16:44:45.647 [nioEventLoopGroup-3-1] DEBUG m.t.a.t.dao.UserDao.insert!selectKey - ooo Using Connection [com.mchange.v2.c3p0.impl.NewProxyConnection@5a24d003 [wrapping: com.mysql.jdbc.JDBC4Connection@689f7624]]
16:44:45.652 [nioEventLoopGroup-3-1] DEBUG m.t.a.t.dao.UserDao.insert!selectKey - ==> Preparing: SELECT LAST_INSERT_ID()
16:44:45.659 [nioEventLoopGroup-3-1] DEBUG m.t.a.t.dao.UserDao.insert!selectKey - ==> Parameters:
16:44:45.706 [nioEventLoopGroup-3-1] DEBUG m.t.a.t.dao.UserDao.insert!selectKey - <== Total: 1
16:44:45.713 [nioEventLoopGroup-3-1] DEBUG org.mybatis.spring.SqlSessionUtils - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@405d4d1c]
16:44:45.716 [nioEventLoopGroup-3-1] DEBUG o.s.jdbc.datasource.DataSourceUtils - Returning JDBC Connection to DataSource
16:44:45.725 [nioEventLoopGroup-3-1] DEBUG c.m.v2.c3p0.impl.NewProxyConnection - com.mchange.v2.c3p0.impl.NewProxyConnection@5a24d003 [wrapping: com.mysql.jdbc.JDBC4Connection@689f7624]: close() called more than once.我的主键是自增的,在数据库里已经增长了。
从上面的 debug 信息也可以看出是在同一个Sqlsession里执行的,不是两个。
实在找不到原因,无奈,我换成 xml 的形式,问题同样存在。
useGeneratedKeys="true" keyColumn="id" keyProperty="id"
parameterType="userPo">
INSERT INTO `user` (`name`,sex,register_ts) VALUES (#{name},#{sex},#{registerTs})
SELECT LAST_INSERT_ID()
把selectKey的resultType属性改为int,_int,或者添加order="AFTER",同样还是返回1