Mybatis通用Join的实现
像写普通curd一样，实现了多表关联的查询、更新、删除、自定义sql的mybatis轻量级扩展库，不再需要写繁琐的xml或者注解，也不需要每个表实体都重复创建各自的mapper接口文件。
使用说明（springboot示例)
1. pom.xml中添加maven依赖包
<!-- https://mvnrepository.com/artifact/com.github.rexsheng/mybatis-extension --> <dependency> <groupId>com.github.rexsheng</groupId> <artifactId>mybatis-extension</artifactId> <version>1.7.1</version> </dependency>
2. 在启动类或者配置类上加入注解@EnableMybatisExtension进行配置
@EnableMybatisExtension
3. 配置mybatis拦截器（使用@EnableMybatisExtension无需此步，若进行自定义SqlSessionFactory,则需要手动配置此拦截器）
import com.github.rexsheng.mybatis.config.BuilderConfiguration; import com.github.rexsheng.mybatis.interceptor.ResultTypeInterceptor;
import com.github.rexsheng.mybatis.config.BuilderConfigurationFactory; @Configuration public class InterceptorConfig { @Bean public ResultTypeInterceptor resultTypeInterceptor() { ResultTypeInterceptor resultTypeInterceptor=new ResultTypeInterceptor(); resultTypeInterceptor.setConfig(BuilderConfigurationFactory.builder().dialect(new MySqlDialect()).build()); return resultTypeInterceptor; } }
4. 注入接口DynamicMapper使用
import com.github.rexsheng.mybatis.extension.TableQueryBuilder; import com.github.rexsheng.mybatis.mapper.DynamicMapper; import com.github.rexsheng.mybatis.test.dto.UserRoleQueryDto; import com.github.rexsheng.mybatis.test.entity.TUser; import com.github.rexsheng.mybatis.test.entity.UserRole; @SpringBootTest public class MapperTest { @Autowired private DynamicMapper dao; /** * 单表简单查询 */ @Test public void simpleSelect() { //定义要查询的表的构建器 TableQueryBuilder<TUser> userQuery=TableQueryBuilder.from(TUser.class); //定义要查询的字段 userQuery.select(TUser::getUserId,TUser::getUserName).where().like(TUser::getUserName, "%王二小%"); //执行查询 List<TUser> userList=dao.selectByBuilder(userQuery.build()); log.info("用户列表:{}",userList); } /** * 多表简单关联查询 */ @Test public void simpleJoin() { //定义要查询的主表的构建器 TableQueryBuilder<TUser> userQuery=TableQueryBuilder.from(TUser.class); //定义要查询的从表的构建器 TableQueryBuilder<UserRole> userRoleQuery=TableQueryBuilder.from(UserRole.class); //定义要查询主表的所有字段，并且使用主表左关联从表，指定关联条件 userQuery.selectAll().leftJoin(userRoleQuery).on(TUser::getUserId, UserRole::getUserId); //定义where中的条件 userQuery.where().like(TUser::getFirstName, "%管理员%").like(TUser::getLastName, "%管理员%"); //执行查询，定义新的返回类 List<UserRoleQueryDto> userList=dao.selectByBuilder(userQuery.build(UserRoleQueryDto.class)); log.info("用户角色列表:{}",userList); } }
5. 手写sql查询
@Test public void testSelectSql() { List<TUser> userList = dao.selectBySql("Select user_id as userId,user_name as userName,create_time as createTime from t_user where user_id>10 limit 3", TUser.class); logger.info("userList:{}", userList); TestCase.assertNotNull(userList); TestCase.assertNotNull(userList.get(0).getCreateTime()); logger.info("userList.0:{}", userList.get(0)); }
2020-10-09 23:49:27.860 DEBUG 15856 --- [ main] c.g.r.m.m.D.selectBySql[TUser] : ==> Preparing: Select user_id as userId,user_name as userName,create_time as createTime from t_user where user_id>10 limit 3 2020-10-09 23:49:27.884 DEBUG 15856 --- [ main] c.g.r.m.m.D.selectBySql[TUser] : ==> Parameters: 2020-10-09 23:49:27.906 DEBUG 15856 --- [ main] c.g.r.m.m.D.selectBySql[TUser] : <== Total: 3 2020-10-09 23:49:27.913 INFO 15856 --- [ main] com.mybatis.test.SqlTest : userList:[TUser [userId=11, userName=用户11, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=null, updateTime=null, updateUser=null], TUser [userId=12, userName=用户12, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=null, updateTime=null, updateUser=null], TUser [userId=13, userName=用户13, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=null, updateTime=null, updateUser=null]] 2020-10-09 23:49:27.916 INFO 15856 --- [ main] com.mybatis.test.SqlTest : userList.0:TUser [userId=11, userName=用户11, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=null, updateTime=null, updateUser=null]
@Test public void testSelectSqlWithParams() { Map<String,Object> paramMap=new HashMap<>(); paramMap.put("limit", 3); paramMap.put("userId", 9999); paramMap.put("userName", "%用户%"); paramMap.put("orderByClause", "create_time desc "); paramMap.put("userIdClause", " is not null "); String sql="Select user_id as userId,user_name as userName,create_time as createTime from t_user "+ " where user_id>10 and user_id<#{userId} and user_name like #{userName} "+ "and user_id${userIdClause} order by ${orderByClause}limit #{limit} offset #{limit}"; List<TUser> userList = dao.selectBySqlWithParams(sql,paramMap, TUser.class); logger.info("userList:{}", userList); TestCase.assertNotNull(userList); TestCase.assertNotNull(userList.get(0).getCreateTime()); logger.info("userList.0:{}", userList.get(0)); }
2020-10-09 23:50:48.626 DEBUG 12184 --- [ main] c.g.r.m.m.D.selectBySqlWithParams[TUser] : ==> Preparing: Select user_id as userId,user_name as userName,create_time as createTime from t_user where user_id>10 and user_id< ? and user_name like ? and user_id is not null order by create_time desc limit ? offset ? 2020-10-09 23:50:48.646 DEBUG 12184 --- [ main] c.g.r.m.m.D.selectBySqlWithParams[TUser] : ==> Parameters: 9999(Integer), %用户%(String), 3(Integer), 3(Integer) 2020-10-09 23:50:48.673 DEBUG 12184 --- [ main] c.g.r.m.m.D.selectBySqlWithParams[TUser] : <== Total: 3 2020-10-09 23:50:48.678 INFO 12184 --- [ main] com.mybatis.test.SqlTest : userList:[TUser [userId=9986, userName=用户9986, createTime=Mon Aug 31 15:37:59 CST 2020, createUser=null, updateTime=null, updateUser=null], TUser [userId=9985, userName=用户9985, createTime=Mon Aug 31 15:37:59 CST 2020, createUser=null, updateTime=null, updateUser=null], TUser [userId=9989, userName=用户9989, createTime=Mon Aug 31 15:37:59 CST 2020, createUser=null, updateTime=null, updateUser=null]] 2020-10-09 23:50:48.680 INFO 12184 --- [ main] com.mybatis.test.SqlTest : userList.0:TUser [userId=9986, userName=用户9986, createTime=Mon Aug 31 15:37:59 CST 2020, createUser=null, updateTime=null, updateUser=null]
6.分页及自动查询总条数
@Test @Tag("两表关联") public void testTwoTable() { long startTime=System.currentTimeMillis(); TableQueryBuilder<TUser> userQuery=TableQueryBuilder.from(TUser.class); userQuery.totalCountEnabled().page(3, 10).selectAll().leftJoin(TableQueryBuilder.from(UserRole.class).select(UserRole::getRoleId)) .on(TUser::getUserId, UserRole::getUserId); userQuery.where().gt(TUser::getUserId, 3); List<TUser> userPagedList=dao.selectByBuilder(userQuery.build()); long endTime=System.currentTimeMillis(); logger.info("user关联表:{}ms,data:{}",endTime-startTime,userPagedList); TestCase.assertNotNull(userPagedList); TestCase.assertTrue(userQuery.getTotalItemCount()>0); }
2020-10-09 23:04:39.195 DEBUG 19132 --- [ main] c.g.r.mybatis.mapper.DynamicMapper : ==> TotalCount SQL: SELECT COUNT(*) FROM t_user AS a LEFT OUTER JOIN t_user_role AS b on a.user_id=b.user_id WHERE (a.user_id > ?) 2020-10-09 23:04:39.212 DEBUG 19132 --- [ main] c.g.r.mybatis.mapper.DynamicMapper : ==> TotalCount Parameters: 3(Integer) 2020-10-09 23:04:39.260 DEBUG 19132 --- [ main] c.g.r.mybatis.mapper.DynamicMapper : <== TotalCount Result: 10030 2020-10-09 23:04:39.270 DEBUG 19132 --- [ main] c.g.r.m.m.DynamicMapper.selectByBuilder : ==> Preparing: SELECT a.user_id, a.user_name, a.create_time, a.create_user, a.update_time, a.update_user, b.role_id FROM t_user AS a LEFT OUTER JOIN t_user_role AS b on a.user_id=b.user_id WHERE (a.user_id > ?) LIMIT ? OFFSET ? 2020-10-09 23:04:39.278 DEBUG 19132 --- [ main] c.g.r.m.m.DynamicMapper.selectByBuilder : ==> Parameters: 3(Integer), 10(Integer), 20(Integer) 2020-10-09 23:04:39.324 DEBUG 19132 --- [ main] c.g.r.m.m.DynamicMapper.selectByBuilder : <== Total: 10 2020-10-09 23:04:39.330 INFO 19132 --- [ main] com.mybatis.test.PagedTest : user关联表:320ms,data:[TUser [userId=7, userName=用户7, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=8, updateTime=null, updateUser=null], TUser [userId=7, userName=用户7, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=8, updateTime=null, updateUser=null], TUser [userId=8, userName=用户8, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=9, updateTime=null, updateUser=null], TUser [userId=8, userName=用户8, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=9, updateTime=null, updateUser=null], TUser [userId=8, userName=用户8, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=9, updateTime=null, updateUser=null], TUser [userId=8, userName=用户8, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=9, updateTime=null, updateUser=null], TUser [userId=8, userName=用户8, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=9, updateTime=null, updateUser=null], TUser [userId=8, userName=用户8, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=9, updateTime=null, updateUser=null], TUser [userId=8, userName=用户8, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=9, updateTime=null, updateUser=null], TUser [userId=8, userName=用户8, createTime=Mon Aug 31 15:35:47 CST 2020, createUser=9, updateTime=null, updateUser=null]]
7.批量插入
@Test public void insertBatch() { List<TUser> userList=new ArrayList<>(); TUser user1=new TUser(); user1.setUserName("测测1"); user1.setCreateUser(12); user1.setCreateTime(new Date()); userList.add(user1); TUser user2=new TUser(); user2.setUserName("测测2"); userList.add(user2); int result=dao.insertBatch(userList); logger.info("insertBatch:{}", result); TestCase.assertNotNull(result); TestCase.assertTrue(result>0); }
2020-10-09 23:54:38.919 DEBUG 18008 --- [ main] c.g.r.m.m.DynamicMapper.insertBatch : ==> Preparing: INSERT INTO t_user (user_id, user_name, create_time, create_user, update_time, update_user) VALUES (?, ?, ?, ?, ?, ?) , (?, ?, ?, ?, ?, ?) 2020-10-09 23:54:38.952 DEBUG 18008 --- [ main] c.g.r.m.m.DynamicMapper.insertBatch : ==> Parameters: null, 测测1(String), 2020-10-09 23:54:38.616(Timestamp), 12(Integer), null, null, null, 测测2(String), null, null, null, null 2020-10-09 23:54:39.916 DEBUG 18008 --- [ main] c.g.r.m.m.DynamicMapper.insertBatch : <== Updates: 2 2020-10-09 23:54:39.918 INFO 18008 --- [ main] com.mybatis.test.InsertTest : insertBatch:2
8.关联更新
@Test @Tag("批量更新") public void updateTable() { long startTime=System.currentTimeMillis(); TableUpdateBuilder<TUser> userQuery=TableUpdateBuilder.from(TUser.class); userQuery.setValue(TUser::getUpdateTime, new Date()).setValue(TUser::getUpdateUser, null) .leftJoin(TableQueryBuilder.from(UserRole.class)) .on(TUser::getUserId, UserRole::getUserId) .setColumnValue(TUser::getUpdateUser, UserRole::getUserId); userQuery.where().gt(TUser::getUserId, 10000).notIn(TUser::getCreateUser, Arrays.asList(1,2,3,4,5,6,7)); int rows=dao.updateByBuilder(userQuery.build()); long endTime=System.currentTimeMillis(); logger.info("user更新表:{}ms,data:{}",endTime-startTime,rows); TestCase.assertTrue(rows>0); }
2020-10-23 04:25:54.661 DEBUG 9424 --- [ main] c.g.r.m.m.DynamicMapper.updateByBuilder : ==> Preparing: UPDATE t_user AS a LEFT OUTER JOIN t_user_role AS b on a.user_id=b.user_id SET a.update_time = ?, a.update_user = ?, a.update_user=b.user_id WHERE (a.user_id > ? AND (a.create_user not in (?,?,?) OR a.create_user not in (?,?,?) OR a.create_user not in (?))) 2020-10-23 04:25:54.694 DEBUG 9424 --- [ main] c.g.r.m.m.DynamicMapper.updateByBuilder : ==> Parameters: 2020-10-23 00:25:54.394(Timestamp), null, 10000(Integer), 1(Integer), 2(Integer), 3(Integer), 4(Integer), 5(Integer), 6(Integer), 7(Integer) 2020-10-23 04:25:54.706 DEBUG 9424 --- [ main] c.g.r.m.m.DynamicMapper.updateByBuilder : <== Updates: 0 2020-10-23 04:25:54.707 INFO 9424 --- [ main] com.mybatis.test.UpdateDeleteTest : user更新表:318ms,data:0
9.关联删除
@Test @Tag("批量删除") public void deleteTable() { long startTime=System.currentTimeMillis(); TableDeleteBuilder<TUser> userQuery=TableDeleteBuilder.from(TUser.class); userQuery.leftJoin(TableQueryBuilder.from(UserRole.class)) .on(TUser::getUserId, UserRole::getUserId); userQuery.where().gt(TUser::getUserId, 10000); int rows=dao.deleteByBuilder(userQuery.build()); long endTime=System.currentTimeMillis(); logger.info("user删除表:{}ms,data:{}",endTime-startTime,rows); TestCase.assertTrue(rows>0); }
2020-10-23 03:27:29.845 DEBUG 5956 --- [ main] c.g.r.m.m.DynamicMapper.deleteByBuilder : ==> Preparing: DELETE a FROM t_user AS a LEFT OUTER JOIN t_user_role AS b on a.user_id=b.user_id WHERE (a.user_id > ?) 2020-10-23 03:27:29.877 DEBUG 5956 --- [ main] c.g.r.m.m.DynamicMapper.deleteByBuilder : ==> Parameters: 10000(Integer) 2020-10-23 03:27:29.883 DEBUG 5956 --- [ main] c.g.r.m.m.DynamicMapper.deleteByBuilder : <== Updates: 0 2020-10-23 03:27:29.885 INFO 5956 --- [ main] com.mybatis.test.UpdateDeleteTest : user删除表:278ms,data:0
10. 支持使用mybatis-plus的实体类列名注解，只需在启动配置上加入参数mybatisPlus=true
@EnableMybatisExtension(mybatisPlus=true)
