前面绍了mybatis-plus的大部分功能,大家有没有发现那些功能都是基本依赖具体的实体类来处理数据,这个在实际的业务开发没有问题,但是我们有没有考虑过在某些特殊场景下,我们只需要查询,而且需要关联多个table,而且在系统业务中这样查询很多,所以我们需要开发一个这样组件,自已根据业务来写sql,绑定变量,然后返回结果。
我们先看一下来mybatis如何动态生成sql:
1.mybatis官方文档中有一个可以动态生成sql的方式:
@Update({ "<script>", "update Author", " <set>", " <if test='username != null'>username=#{username},</if>", " <if test='password != null'>password=#{password},</if>", " <if test='email != null'>email=#{email},</if>", " <if test='bio != null'>bio=#{bio}</if>", " </set>", "where id=#{id}", "</script>"})
void updateAuthorValues(Author author);
2.根据mybatis原理,其实sql里#{email}中是传参的成员值,这个成员可以某个类型的成员或者map中存在的key-value
根据以上内容,我们可以定个简易版本的查询组件:
1.准备maaper
public interface DynamicQueryMapper extends BaseMapper {
@Select(
{"<script>",
" ${sql}",
"</script>"}
)
List<Map<String,Object>> selectCondition(Map map);
}
2.准备测试类
@SpringBootTest
class DynamicTests {
@Autowired
private DynamicQueryMapper dynamicQueryMapper;
@Test
public void testCallProcedure(){
HashMap<String,Object> map=new HashMap<String,Object>();
map.put("sql","select * from user where name like #{name1} or name like #{name2} ");
map.put("name1","%o%");
map.put("name2","%x%");
List<Map<String,Object>> maps= dynamicQueryMapper.selectCondition(map);
maps.forEach(System.out::println);
}
}
3.测试log
2022-10-16 20:51:21.503 DEBUG 18172 --- [ main] c.m.s.m.d.D.selectCondition : ==> Preparing: SELECT * FROM user WHERE (name LIKE ? OR name LIKE ?) AND user.FAB = 'FAB1'
2022-10-16 20:51:21.533 DEBUG 18172 --- [ main] c.m.s.m.d.D.selectCondition : ==> Parameters: %o%(String), %x%(String)
Consume Time:16 ms 2022-10-16 20:51:21
Execute SQL:SELECT * FROM user WHERE (name LIKE '%o%' OR name LIKE '%x%') AND user.FAB = 'FAB1'
2022-10-16 20:51:21.603 DEBUG 18172 --- [ main] c.m.s.m.d.D.selectCondition : <== Total: 220