关于QueryWrapper,实现MybatisPlus多表关联查询方式
目录
- QueryWrapper实现MybatisPlus多表关联查询
- 1.dao层接口使用Select注解写SQL
- 2.service层代码示例
- 3.反射工具类
- 4.判空工具类
- MybatisPlus QueryWrapper简单用法
QueryWrapper实现MybatisPlus多表关联查询
1.dao层接口使用Select注解写SQL
重点:@Param("ew") Wrapper参数是必须,因为${ew.customSqlSegment} 底层其实就是where 条件,所以为了保证Wrapper不为空,service层代码中的Wrapper至少需要有一个条件:1 = 1
@Override @Select("select a.code as code , b.name as name , b.barcode as barcode , a.ware_code as wareCode , c.name as wareName , a.qty as qty , a.oprice as oprice , a.total as total , " + " a.id as id , a.create_by as createBy , a.create_date as createDate , a.update_by as updateBy , a.update_date as updateDate , a.status as status , a.remarks as remarks " + "from sku_stock a , goods b , warehouse c " + "${ew.customSqlSegment} and a.code = b.code and a.ware_code = c.code") IPage<SkuStock> selectPage(IPage<SkuStock> page, @Param("ew")Wrapper<SkuStock> queryWrapper);
2.service层代码示例
service父类封装的findPage方法:
/** * 封装findPage * @param entity * @param search Map中的key:";"为保留关键字,拆分数组,仅支持最大长度2的数组, * 下标0:QueryWrapper查询条件中的列名(支持多表关联查询的表别名 + 列名方式,需要dao层接口支持) * 下标1: QueryWrapper中不同的查询条件,eq:等于,ge:大于等..... todo:请自行完善Mybatis eq、ne、gt、lt、ge、le等 * Map中的value:QueryWrapper需要查询的值 * @param args QueryWrapper中order by 排序数组 * @return */ public IPage<T> findPage(T entity , Map<String , Object> search , String... args){ long current = 1L; long size = 10L; if (EmptyUtil.isNoEmpty(ReflexUtil.getFieldValue(entity , "page")) && (long) ReflexUtil.getFieldValue(entity , "page") != 0){ current = (long) ReflexUtil.getFieldValue(entity , "page"); } if (EmptyUtil.isNoEmpty(ReflexUtil.getFieldValue(entity , "limit")) && (long) ReflexUtil.getFieldValue(entity , "limit") != 0){ size = (long) ReflexUtil.getFieldValue(entity , "limit"); } QueryWrapper<T> queryWrapper; if (EmptyUtil.isNoEmpty(search)){ queryWrapper = new QueryWrapper<>(); for (Map.Entry<String , Object> entry:search.entrySet() ) { String[] key = entry.getKey().split(";"); if (key.length > 1){ if (key[1].equals("eq")){ queryWrapper.eq(key[0] , entry.getValue()); }else if (key[1].equals("ge")){ queryWrapper.ge(key[0] , entry.getValue()); }else if (key[1].equals("lt")){ queryWrapper.lt(key[0] , entry.getValue()); } }else { queryWrapper.like(entry.getKey() , entry.getValue()); } } }else { queryWrapper = new QueryWrapper<>(entity); } queryWrapper.orderByAsc(args); return super.page(new Page<T>(current , size) , queryWrapper); }
service实现类方法:
public IPage<SkuStock> findPage(SkuStock entity, String... args) { Map<String , Object> search = null; search = new HashedMap(); search.put("1;eq" , "1"); if (EmptyUtil.isNoEmpty(entity.getCode()) || EmptyUtil.isNoEmpty(entity.getWareCode()) ){ if (EmptyUtil.isNoEmpty(entity.getCode())){ search.put("code" , entity.getCode()); } if (EmptyUtil.isNoEmpty(entity.getWareCode())){ search.put("ware_code" , entity.getWareCode()); } }else { long limit = entity.getLimit(); long page = entity.getPage(); entity = new SkuStock(); entity.setLimit(limit); entity.setPage(page); } return super.findPage(entity , search , args); }
3.反射工具类
package org.bluedream.comm.utils; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.util.ArrayList; import java.util.Arrays; import java.util.List; /** * @ClassName ReflexUtil * @Description TODO * @Author foxsand * @Data 2021-06-09 15:17 * @Version */ public class ReflexUtil { /** * 返回 entity 对象的所有属性,包含父类 * @param obj * @return */ public static List<Field> getObjectFields(Object obj){ Class clazz = obj.getClass(); List<Field> fieldList = new ArrayList<>() ; while (clazz != null) {//当父类为null的时候说明到达了最上层的父类(Object类). fieldList.addAll(Arrays.asList(clazz .getDeclaredFields())); clazz = clazz.getSuperclass(); //得到父类,然后赋给自己 } return fieldList; } public static List<Field> getObjectFields(Class<?> clazz){ List<Field> fieldList = new ArrayList<>() ; while (clazz != null){ fieldList.addAll(Arrays.asList(clazz .getDeclaredFields())); clazz = clazz.getSuperclass(); //得到父类,然后赋给自己 } return fieldList; } /** * 判断 Class entity 是否存在名称为 fieldName 的属性 * @param fieldName * @param entity * @return */ public static Boolean isField(String fieldName , Object entity){ List<Field> fieldList = getObjectFields(entity); for (Field f1:fieldList ) { if (fieldName.equals(f1.getName())) return true; } return false; } /** * 返回 entity 对象中的所有方法,包含父类 * @param entity * @return */ public static List<Method> getObjectMethods(Object entity){ Class<?> clazz = entity.getClass(); List<Method> methods = new ArrayList<>(); while (clazz != null && clazz != Object.class) {//当父类为null的时候说明到达了最上层的父类(Object类). methods.addAll(Arrays.asList(clazz .getDeclaredMethods())); clazz = clazz.getSuperclass(); //得到父类,然后赋给自己 } return methods; } public static List<Method> getObjectMethods(Class<?> clazz){ List<Method> methods = new ArrayList<>(); while (clazz != null && clazz != Object.class) {//当父类为null的时候说明到达了最上层的父类(Object类). methods.addAll(Arrays.asList(clazz .getDeclaredMethods())); clazz = clazz.getSuperclass(); //得到父类,然后赋给自己 } return methods; } /** * 判断 Class entity 是否存在名称为 methodName 的方法 * @param methodName * @param entity * @return */ public static Boolean isMethod(String methodName , Object entity){ List<Method> methods = getObjectMethods(entity); for (Method m1:methods ) { if (methodName.equals(m1.getName())) return true; } return false; } /** * 循环向上转型, 获取对象的 DeclaredMethod * @param obj * @param methodName * @param parameterTypes 方法参数类型 * @return */ public static Method getDeclaredMethod(Object obj , String methodName , Class<?>...parameterTypes) { for (Class<?> clazz = obj.getClass(); clazz != Object.class && clazz != null; clazz = clazz.getSuperclass()) { try { return clazz.getDeclaredMethod(methodName, parameterTypes); } catch (Exception e) { // 这里甚么都不要做!并且这里的异常必须这样写,不能抛出去。 // 如果这里的异常打印或者往外抛,则就不会执行clazz=clazz.getSuperclass(),最后就不会进入到父类中了 } } return null; } public static Object invoke(Object object, String methodName, Class<?>[] parameterTypes, Object[] parameters){ Method method = getDeclaredMethod(object, methodName, parameterTypes); try { if (method != null){ method.setAccessible(true); // 调用object 的 method 所代表的方法,其方法的参数是 parameters return method.invoke(object, parameters); } }catch (Exception e1){ e1.printStackTrace(); } return null; } /** * 循环向上转型, 获取对象的 DeclaredField * * @param object * : 子类对象 * @param fieldName * : 父类中的属性名 * @return 父类中的属性对象 */ public static Field getDeclaredField(Object object, String fieldName) { Field field = null; Class<?> clazz = object.getClass(); for (; clazz != Object.class && clazz != null; clazz = clazz.getSuperclass()) { try { field = clazz.getDeclaredField(fieldName); return field; } catch (Exception e) { // 这里甚么都不要做!并且这里的异常必须这样写,不能抛出去。 // 如果这里的异常打印或者往外抛,则就不会执行clazz = clazz.getSuperclass(),最后就不会进入到父类中了 } } return null; } /** * 直接设置对象属性值, 忽略 private/protected 修饰符, 也不经过 setter * * @param object * : 子类对象 * @param fieldName * : 父类中的属性名 * @param value * : 将要设置的值 */ public static void setFieldValue(Object object, String fieldName, Object value) { // 根据 对象和属性名通过反射 调用上面的方法获取 Field对象 Field field = getDeclaredField(object, fieldName); if (field != null){ // 抑制Java对其的检查 field.setAccessible(true); try { // 将 object 中 field 所代表的值 设置为 value field.set(object, value); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } } } /** * 直接读取对象的属性值, 忽略 private/protected 修饰符, 也不经过 getter * * @param object * : 子类对象 * @param fieldName * : 父类中的属性名 * @return : 父类中的属性值 */ public static Object getFieldValue(Object object, String fieldName) { // 根据 对象和属性名通过反射 调用上面的方法获取 Field对象 Field field = getDeclaredField(object, fieldName); if (field != null){ // 抑制Java对其的检查 field.setAccessible(true); try { // 获取 object 中 field 所代表的属性值 return field.get(object); } catch (Exception e) { e.printStackTrace(); } } return null; } }
4.判空工具类
package org.bluedream.comm.utils; import java.util.Collection; import java.util.Map; public class EmptyUtil { //Suppress default constructor for noninstantiability private EmptyUtil(){ throw new AssertionError(); } public static boolean isEmpty(Object object){ if (object == null){ return true; } if (object instanceof int[]){ return ((int[]) object).length == 0; } if (object instanceof double[]){ return ((double[]) object).length == 0; } if (object instanceof long[]){ return ((long[]) object).length == 0; } if (object instanceof byte[]){ return ((byte[]) object).length == 0; } if (object instanceof short[]){ return ((short[]) object).length == 0; } if (object instanceof float[]){ return ((float[]) object).length == 0; } if (object instanceof char[]){ return ((char[]) object).length == 0; } if (object instanceof Object[]){ return ((Object[]) object).length == 0; } if (object instanceof CharSequence) { return ((CharSequence) object).length() == 0; } if (object instanceof Collection ){ return ((Collection) object).isEmpty(); } if (object instanceof Map){ return ((Map) object).isEmpty(); } return false; } public static boolean isNoEmpty(Object object){ return !isEmpty(object); } }
MybatisPlus QueryWrapper简单用法
查询方式 |
说明 |
setSqlSelect |
设置 SELECT 查询字段 |
where |
WHERE 语句,拼接 +?WHERE 条件 |
and |
AND 语句,拼接 +?AND 字段=值 |
andNew |
AND 语句,拼接 +?AND (字段=值) |
or |
OR 语句,拼接 +?OR 字段=值 |
orNew |
OR 语句,拼接 +?OR (字段=值) |
eq |
等于= |
allEq |
基于 map 内容等于= |
ne |
不等于<> |
gt |
大于> |
ge |
大于等于>= |
lt |
小于< |
le |
小于等于<= |
like |
模糊查询 LIKE |
notLike |
模糊查询 NOT LIKE |
in |
IN 查询 |
notIn |
NOT IN 查询 |
isNull |
NULL 值查询 |
isNotNull |
IS NOT NULL |
groupBy |
分组 GROUP BY |
having |
HAVING 关键词 |
orderBy |
排序 ORDER BY |
orderAsc |
ASC 排序 ORDER BY |
orderDesc |
DESC 排序 ORDER BY |
exists |
EXISTS 条件语句 |
notExists |
NOT EXISTS 条件语句 |
between |
BETWEEN 条件语句 |
notBetween |
NOT BETWEEN 条件语句 |
addFilter |
自由拼接 SQL |
last |
拼接在最后,例如:last(“LIMIT 1”) |
以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。
赞 (0)