概述
@Insert注解本身很简单，需要注意的是如果需要返回主键的值，情况会稍微复杂一点，这种XML方式配置本质是一样的
不需要返回主键
1.RoleMapper接口增加接口方法
/** * * * @Title: insertSysRole * * @Description: insertSysRole 不需要返回主键的情况 * * @param sysRole * @return * * @return: int */ @Insert({ "insert into sys_role(id, role_name, enabled, create_by, create_time) values(#{id}, #{roleName}, #{enabled}, #{createBy}, #{createTime, jdbcType=TIMESTAMP})" }) int insertSysRole(SysRole sysRole);
2.单元测试
@Test public void insertSysRoleTest() { logger.info("insertSysRoleTest"); // 获取SqlSession SqlSession sqlSession = getSqlSession(); try { // 获取RoleMapper接口 RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); SysRole sysRole = new SysRole(); sysRole.setId((long) 99); sysRole.setRoleName("小工匠"); sysRole.setEnabled(1); sysRole.setCreateBy("test"); sysRole.setCreateTime(new Date()); // 新增用户 ,返回受影响的行数 int result = roleMapper.insertSysRole(sysRole); // 只插入一条数据 ,期望是1 Assert.assertEquals(1, result); // 期望roleName 为小工匠 Assert.assertEquals("小工匠", sysRole.getRoleName()); logger.info("sysRole:" + sysRole); } catch (Exception e) { e.printStackTrace(); } finally { // 为了保持测试数据的干净，这里选择回滚 // 由于默认的sqlSessionFactory.openSession()是不自动提交的 // 除非显式的commit，否则不会提交到数据库 sqlSession.rollback(); logger.info("为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成"); sqlSession.close(); logger.info("sqlSession close successfully "); } }
2018-04-19 01:49:02,496 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-19 01:49:02,501 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-19 01:49:02,506 INFO [main] (RoleMapperTest.java:120) - insertSysRoleTest 2018-04-19 01:49:02,992 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: insert into sys_role(id, role_name, enabled, create_by, create_time) values(?, ?, ?, ?, ?) 2018-04-19 01:49:03,076 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 99(Long), 小工匠(String), 1(Integer), test(String), 2018-04-19 01:49:02.52(Timestamp) 2018-04-19 01:49:03,083 DEBUG [main] (BaseJdbcLogger.java:145) - <== Updates: 1 2018-04-19 01:49:03,088 INFO [main] (RoleMapperTest.java:140) - sysRole:SysRole [id=99, roleName=小工匠, enabled=1, createBy=test, createTime=Thu Apr 19 01:49:02 BOT 2018, user=null, privilegeList=null] 2018-04-19 01:49:03,090 INFO [main] (RoleMapperTest.java:148) - 为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成 2018-04-19 01:49:03,092 INFO [main] (RoleMapperTest.java:151) - sqlSession close successfully
返回自增主键
1.RoleMapper接口增加接口方法
/** * * * @Title: insertSysRole2 * * @Description: insertSysRole2 返回自增主键的情况 * * @param sysRole * @return * * @return: int */ @Insert({ "insert into sys_role(role_name, enabled, create_by, create_time) values(#{roleName}, #{enabled}, #{createBy}, #{createTime, jdbcType=TIMESTAMP})" }) @Options(useGeneratedKeys = true, keyProperty = "id") int insertSysRole2(SysRole sysRole);
和第一个场景相比，SQL语句中少了id这一列，注解多了一个@Options, 我们在这个注解设置了useGeneratedKeys 和 keyProperty ，用法同xml中的配置。
2.单元测试
@Test public void insertSysRoleTest2() { logger.info("insertSysRoleTest2"); // 获取SqlSession SqlSession sqlSession = getSqlSession(); try { // 获取RoleMapper接口 RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); SysRole sysRole = new SysRole(); sysRole.setRoleName("小工匠2"); sysRole.setEnabled(1); sysRole.setCreateBy("test2"); sysRole.setCreateTime(new Date()); // 新增用户 ,返回受影响的行数 int result = roleMapper.insertSysRole2(sysRole); // 只插入一条数据 ,期望是1 Assert.assertEquals(1, result); // 期望roleName 为小工匠 Assert.assertEquals("小工匠2", sysRole.getRoleName()); logger.info("sysRole:" + sysRole); } catch (Exception e) { e.printStackTrace(); } finally { // 为了保持测试数据的干净，这里选择回滚 // 由于默认的sqlSessionFactory.openSession()是不自动提交的 // 除非显式的commit，否则不会提交到数据库 sqlSession.rollback(); logger.info("为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成"); sqlSession.close(); logger.info("sqlSession close successfully "); } }
2018-04-19 01:50:16,054 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-19 01:50:16,057 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-19 01:50:16,060 INFO [main] (RoleMapperTest.java:157) - insertSysRoleTest2 2018-04-19 01:50:16,588 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: insert into sys_role(role_name, enabled, create_by, create_time) values(?, ?, ?, ?) 2018-04-19 01:50:16,684 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 小工匠2(String), 1(Integer), test2(String), 2018-04-19 01:50:16.075(Timestamp) 2018-04-19 01:50:16,690 DEBUG [main] (BaseJdbcLogger.java:145) - <== Updates: 1 2018-04-19 01:50:16,696 INFO [main] (RoleMapperTest.java:176) - sysRole:SysRole [id=105, roleName=小工匠2, enabled=1, createBy=test2, createTime=Thu Apr 19 01:50:16 BOT 2018, user=null, privilegeList=null] 2018-04-19 01:50:16,702 INFO [main] (RoleMapperTest.java:184) - 为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成 2018-04-19 01:50:16,704 INFO [main] (RoleMapperTest.java:187) - sqlSession close successfully
返回非自增主键
1.RoleMapper接口增加接口方法
/** * * * @Title: insertSysRole3 * * @Description: insertSysRole3 返回非自增主键的情况 * * @param sysRole * @return * * @return: int */ @Insert({ "insert into sys_role(role_name, enabled, create_by, create_time) values(#{roleName}, #{enabled}, #{createBy}, #{createTime, jdbcType=TIMESTAMP})" }) @SelectKey(statement = "SELECT LAST_INSERT_ID()", keyProperty = "id", resultType = Long.class, before = false) int insertSysRole3(SysRole sysRole);
配置和xml基本是相同的，其中before=false 等同于order=”AFTER”. before=true 等同于order=”BEFORE”.
不同数据库中，order的配置不同，需要注意下。
2.单元测试
@Test public void insertSysRoleTest3() { logger.info("insertSysRoleTest3"); // 获取SqlSession SqlSession sqlSession = getSqlSession(); try { // 获取RoleMapper接口 RoleMapper roleMapper = sqlSession.getMapper(RoleMapper.class); SysRole sysRole = new SysRole(); sysRole.setRoleName("小工匠3"); sysRole.setEnabled(1); sysRole.setCreateBy("test3"); sysRole.setCreateTime(new Date()); // 新增用户 ,返回受影响的行数 int result = roleMapper.insertSysRole3(sysRole); // 只插入一条数据 ,期望是1 Assert.assertEquals(1, result); // 期望roleName 为小工匠 Assert.assertEquals("小工匠3", sysRole.getRoleName()); logger.info("sysRole:" + sysRole); } catch (Exception e) { e.printStackTrace(); } finally { // 为了保持测试数据的干净，这里选择回滚 // 由于默认的sqlSessionFactory.openSession()是不自动提交的 // 除非显式的commit，否则不会提交到数据库 sqlSession.rollback(); logger.info("为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成"); sqlSession.close(); logger.info("sqlSession close successfully "); } }
2018-04-19 01:50:01,398 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully 2018-04-19 01:50:01,403 INFO [main] (BaseMapperTest.java:29) - reader close successfully 2018-04-19 01:50:01,406 INFO [main] (RoleMapperTest.java:193) - insertSysRoleTest3 2018-04-19 01:50:01,954 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: insert into sys_role(role_name, enabled, create_by, create_time) values(?, ?, ?, ?) 2018-04-19 01:50:02,039 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 小工匠3(String), 1(Integer), test3(String), 2018-04-19 01:50:01.428(Timestamp) 2018-04-19 01:50:02,043 DEBUG [main] (BaseJdbcLogger.java:145) - <== Updates: 1 2018-04-19 01:50:02,046 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Preparing: SELECT LAST_INSERT_ID() 2018-04-19 01:50:02,046 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 2018-04-19 01:50:02,072 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: LAST_INSERT_ID() 2018-04-19 01:50:02,072 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 104 2018-04-19 01:50:02,075 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1 2018-04-19 01:50:02,079 INFO [main] (RoleMapperTest.java:212) - sysRole:SysRole [id=104, roleName=小工匠3, enabled=1, createBy=test3, createTime=Thu Apr 19 01:50:01 BOT 2018, user=null, privilegeList=null] 2018-04-19 01:50:02,082 INFO [main] (RoleMapperTest.java:220) - 为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成 2018-04-19 01:50:02,083 INFO [main] (RoleMapperTest.java:223) - sqlSession close successfully
