SuperWrapper是我封装的一个继承自AbstractWrapper的工具类
里面有特别多的查询方法,我在类上方写了海量的备注,如下:
最基本的查询用法,如下
SuperWrapper spWrapper = new SuperWrapper()
.setPage( params.getPageNum() , params.getPageSize() , params.getPageAble() )
.ne( "T.active" , 0 );
if ( param != null ) {
spWrapper
//非必选字段 和 选填字段 ,SuperWrapper的默认特性:当这些变量字段为空时,下面判断语句将不会添加执行
//查询: 编号
.likes( "T.serial_code" , param.getSerialCode() )
//查询: 创建用户
.likes( "T.create_by" , param.getCreateBy() )
//查询: 修改用户
.likes( "T.update_by" , param.getUpdateBy() )
//查询: 创建时间
.likes( "T.create_time" , param.getCreateTime() )
//查询: 修改时间
.likes( "T.update_time" , param.getUpdateTime() )
.betweens( "T.create_time" , param.getTimeRange() )
;
final Integer sortMode = param.getSortMode();
final List<String> dayRange = param.dayRange();
if(bookDay!=null){
spWrapper.betweens( "T.day", dayRange );
}
//最近下单
if(sortMode==null||sortMode==0) {
spWrapper.orderBys( "T.create_time");
//最早到期
}else if(sortMode==1 ){
spWrapper.orderByDesc("T.day");
}
}
List< FieldDataVo > DTOList = fieldBookMapper.query( spWrapper );
上面这个用法非常简单。
还有其它用法,比如下面这个例子就非常复杂,在同表进行复杂的多级字典树查询,一句sql嵌套了数十个select,所有需要的数据都只通过仅仅一句sql在同一个表里查,如下:
/**
* 根据条件分页查询字典数据node
*
* @param dictData 字典数据信息
* @return 字典数据集合信息
*/
@Override
public ListResult< SysDictDataVo > selectDictDataListSuper ( SysDictDataVo dictData ) {
ListResult< SysDictDataVo > result = new ListResultImpl<>();
String dictType = dictData.getDictType();
String typeName = dictData.getTypeName();
boolean searchByTypeName = StringUtils.isNotEmpty( typeName );
Integer level = dictData.getLevel();
level = level == null || level < 0 ? 1 : level > 10 ? 10 : level;
SuperWrapper[] unionList = new SuperWrapper[2 + level];
List< Long > idsList = dictData.getIdsList();
String dict_code = DICT_CODE;
String column = null;
SuperWrapper countWrapper = new SuperWrapper( TABLE_NAME , DATA_AS )
.select( COUNT_SELECT_COLUMN + ( searchByTypeName ? ",(SELECT CAST(dict_id AS CHAR) AS dict_id FROM sys_dict_type WHERE dict_type='"+typeName+"' Limit 1)" : ",NULL" ) +" AS type_name" )
;
SuperWrapper firstWrapper = new SuperWrapper( TABLE_NAME , DATA_AS )
.select( FIRST_SELECT_COLUMN + ( searchByTypeName ? ",T1.dict_type" : ",NULL" ) + " AS type_name" );
SuperWrapper pIdsWapper = new SuperWrapper( TABLE_NAME , DATA_AS ).select( dict_code );
if ( StringUtils.isNotEmpty( dictType ) || searchByTypeName ) {
//选择分页列表
column = DICT_TYPE;
countWrapper.eqs( column , dictType ).eqs( DICT_TYPE_NAME , typeName );
if ( searchByTypeName ) {
countWrapper.leftJoin( TABLE_NAME_SYS_DICT_TYPE , SYS_DICT_TYPE_AS , ON_KEY , ON_KEY2 );
firstWrapper.leftJoin( TABLE_NAME_SYS_DICT_TYPE , SYS_DICT_TYPE_AS , ON_KEY , ON_KEY2 );
pIdsWapper.leftJoin( TABLE_NAME_SYS_DICT_TYPE , SYS_DICT_TYPE_AS , ON_KEY , ON_KEY2 );
}
Integer pageNum = dictData.getPageNum();
Integer pageSize = dictData.getPageSize();
Boolean pageAble = dictData.getPageAble();
firstWrapper.eqs( column , dictType ).eqs( DICT_TYPE_NAME , typeName )
.orderBys( DATA_DICT_SORT )
.setPage( pageNum , pageSize , pageAble )
;
pIdsWapper.eqs( column , dictType ).eqs( DICT_TYPE_NAME , typeName )
.setPage( pageNum , pageSize , pageAble )
.orderBys( DATA_DICT_SORT );
pIdsWapper = new SuperWrapper( TABLE_NAME ).select( "*" ).fromS( pIdsWapper , DATA_AS );
} else {
//选择指定行
column = DICT_CODE;
countWrapper.ins( column , idsList );
firstWrapper.ins( column , idsList );
pIdsWapper.ins( column , idsList ).setPageAble( false );
}
unionList[0] = countWrapper;
unionList[1] = firstWrapper;
int index = 2;
while ( 0 != level-- ) {
SuperWrapper nextWrapper = new SuperWrapper( TABLE_NAME );
nextWrapper.select( DEFAULT_SELECT_COLUMN )
.inS( P_ID , pIdsWapper );
pIdsWapper = new SuperWrapper( TABLE_NAME ).select( dict_code )
.inS( P_ID , pIdsWapper );
unionList[index++] = nextWrapper;
}
SuperWrapper MainWrapper = new SuperWrapper().select( "*" ).fromUnionS( "T" , unionList );
List< SysDictDataVo > sysDictDataVos = dictDataMapper.selectS( MainWrapper );
result.setData( sysDictDataVos.get( 0 ) );
result.setRows( sysDictDataVos );
result.set( sysDictDataVos , sysDictDataVos.get( 0 ).getDictCode() );
// log.info( "字典树结果集:\n{}" , JSON.toJSONString( result , true ) );
return result;
}
复杂查询是基于我SuperWrapper里面的一些特殊方法,可以在SuperWrapper里面传参放入进一个新的完整的SuperWrapper,如下:
这几种方法用于实现mysql中参数为完整select语句的相应的语法
一个主wrapper 放数个子wrapper 形成所需的sql语句,通过一些核心方法来进行语句的拼接和变量值的传递
还有一个也比较复杂的用法案例如下:
SuperWrapper superWrapperX = new SuperWrapper("t_cloud_scenes", "T");
Integer isDefault = params.getIsDefault();
String unidTarget = params.getUnitId();
ListResult<TCloudScenesDTO> DTOResult = new ListResult<>();
//主查询
if (isDefault == null || isDefault != 1) {
//选取所有默认规则的情况code
SuperWrapper superWrapper31 = (SuperWrapper) new SuperWrapper("t_cloud_scenes")
.select("code")
.ins("unit_id", "0");
//选取目标对默认情景存在操作记录的所有情况码(包括删除和修改记录)
SuperWrapper superWrapper32 = (SuperWrapper) new SuperWrapper("t_cloud_scenes")
.select("code")
.eqs("unit_id", unidTarget, true)
.inS("code", superWrapper31);
//从默认情况中排除 目标对默认情景不存在操作记录的所有情况(排除删除记录和修改记录)
SuperWrapper superWrapper33 = (SuperWrapper) new SuperWrapper("t_cloud_scenes")
.eqs("unit_id", "0")
.notInS("code", superWrapper32);
//选取目标对默认情况存在修改操作记录的所有情景(只包括修改记录,排除删除记录)
SuperWrapper superWrapper42 = (SuperWrapper) new SuperWrapper("t_cloud_scenes")
.eqs("unit_id", unidTarget, true)
.eqs(" is_default", "1")
.inS("code", superWrapper31);
//选取第四条件的内容(暂无作用)
// SuperWrapper superWrapper21 = (SuperWrapper) new SuperWrapper("t_cloud_scenes")
// .eqs("code", "0")
// .eqs("unit_id", unidTarget,true)
// .eqs("is_default", "1");
//选取目标的所有非默认情景(营盘自己添加的情景)
SuperWrapper superWrapper51 = (SuperWrapper) new SuperWrapper("t_cloud_scenes")
.eqs("code", "0")
.eqs("unit_id", unidTarget, true)
.eqs("is_default", "0");
superWrapperX
.fromUnionS("T", superWrapper33, superWrapper42, superWrapper51)
//必填字段
.ins(true, "T.unit_id", unidTarget, "0")
;
//其它端查询
} else {
superWrapperX
.eqs("T.is_default", 1)
.eqs("T.unit_id", "0")
;
}
//筛选字段
{
superWrapperX
.eqs("T.unid", params.getUnid())
.eqs("T.scene_type", params.getSceneType(), 0)
.likes("T.name", params.getName())
.eqs("T.code", params.getCode())
.orderBys("sort").orderBysDesc("code").orderBysDesc("update_time")
;
}
int pageNum = params.pageNum;
int pageSize = params.pageSize;
List<TCloudScenesDTO> DTOs = new ArrayList<>();
long total;
//最终的查询语句↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
{ //查询详情时,对绑定数据的处理
//单条查询方法
if (StringUtils.isNotEmpty(params.getUnid())) {
SuperWrapper codeWrapper =
(SuperWrapper) new SuperWrapper("t_cloud_scenes_rule")
.select("rule_name,unid")
.lasts("LIMIT 1");
DTOs.add(tCloudScenesMapper.superSelectOne(
superWrapperX
.lasts("LIMIT 1")
.leftJoinS(codeWrapper, "T9", "T9.unid", "T.scenes_rule_id")
));
total = 1;
} else {
//范围查询方法
DTOs = tCloudScenesMapper.superSelect(superWrapperX, superWrapperX.getSuperIpage(pageNum, pageSize));
total = superWrapperX.getSuperIpage().getTotal();
}
}
DTOResult.setRows(DTOs);
DTOResult.setTotal(total);
return DTOResult;
上面这个用例最终的查询语句就只是这句:
tCloudScenesMapper.superSelect(superWrapperX, superWrapperX.getSuperIpage(pageNum, pageSize));
所有其它声明好的wrapper都是直接或间接放到主wrapper(superWrapperX)里面,通过mapper的superSelect方法把主wrapper(superWrapperX)传入,就可以实现最终的查询,其最终传递给Dao层的sql语句是这样的
dao层mapperService里只要写如下方法:(无需在xml里写任何映射方法和sql)
/**
* 联表查询方法
* @param wrapper
* @return 数据库操作结果
*/
@Select("${ew.superSelect}")
List<TCloudScenesDTO> superSelect( @Param(Constants.WRAPPER) SuperWrapper wrapper , @Param("page") IPage<String> page);
最终会生成如下sql自动执行
SELECT * FROM (( SELECT * FROM t_cloud_scenes
WHERE (id = #{ew.paramNameValuePairs.SUPVAL9}
AND code NOT IN (( SELECT code FROM t_cloud_scenes
WHERE (id = #{ew.paramNameValuePairs.SUPVAL8}
AND code IN (( SELECT code FROM t_cloud_scenes
WHERE (id IN (#{ew.paramNameValuePairs.SUPVAL12}
)))))))))
UNION ( SELECT * FROM t_cloud_scenes
WHERE (id = #{ew.paramNameValuePairs.SUPVAL16}
AND is_default = #{ew.paramNameValuePairs.SUPVAL20}
AND code IN (( SELECT code FROM t_cloud_scenes
WHERE (id IN(#{ew.paramNameValuePairs.SUPVAL19}
)))))) UNION ( SELECT * FROM t_cloud_scenes
WHERE (code = #{ew.paramNameValuePairs.SUPVAL24}
AND unit_id = #{ew.paramNameValuePairs.SUPVAL23}
AND is_default = #{ew.paramNameValuePairs.SUPVAL22}
))) AS T
WHERE (T.id IN (#{ew.paramNameValuePairs.MPGENVAL1}
,#{ew.paramNameValuePairs.MPGENVAL2}
) AND T.unid = #{ew.paramNameValuePairs.MPGENVAL3}
AND T.name LIKE #{ew.paramNameValuePairs.MPGENVAL4}
AND T.code = #{ew.paramNameValuePairs.MPGENVAL5})
ORDER BY sort ASC , code DESC , update_time DESC
这样业务逻辑就完成了,所有变量都是防注入的