单表增删改查通用方法（也涉及多表关联），使用反射动态返回Obje
2018-10-30 本文已影响94人
墨色尘埃
有一个需求：对一个页面上的操作进行增删改查，每一个页面的增、删、改、查都涉及到两张表，即原表和备份表。因为涉及到的增删改查太多，所以增/删/改/查分别用一个通用方法来实现
页面结构.png
image.png
1、如果只用一个通用的方法会遇到一个问题，即权限问题。通过分配url权限控制某人只能对人口库进行操作而不能对经济库操作，所以将通用方法写在一个父类BaseController中，子类xxControl继承BaseController，即可对url进行权限控制了。
BaseController
package com.cicdi.servertemplate.modules.baselibrary.control;
import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.cicdi.servertemplate.common.model.ResponseObj;
import com.cicdi.servertemplate.common.model.RetCode;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.util.List;
import javax.validation.Valid;
/**
* 增删改差基础Controller
*
* @param <S> Service类型
* @param <M> Mapper类型
* @param <T> 增删改差的对象类型
* @param <I> id的类型，主键的数据类型
*/
public class BaseController<S extends ServiceImpl<M, T>, M extends BaseMapper<T>, T, I extends Serializable> {
@Autowired
protected S service;
@Autowired
protected M mapper;
@Autowired
private BaseExecute<T> baseExecute;
/**
* 查询过滤属性使用，直接将要过滤的属性设置为Null即可
*
* @param object
* @return
*/
protected void queryFilter(T object) {
}
/**
* 插入需要变换的值，或者校验的值
*
* @param object
* @return
*/
protected boolean commitFilter(T object) {
return true;
}
/**
* @param model 传入id查询
* @return 返回Object而不是泛型T
* selectOne
* selectPage
*/
@RequestMapping(value = "/selectById", method = RequestMethod.GET)
public ResponseObj<Object> selectById(@ModelAttribute T model) {
return baseExecute.selectById(model,service);
}
// /**
// * 返回列表
// *
// * @return
// */
// @RequestMapping(value = "/list", method = RequestMethod.GET)
// public ResponseObj<List<T>> list(@ModelAttribute T model) {
// return baseExecute.list(model, service);
// }
/**
* @param page 查询一般传入参数为current和size, 例如/listPage?current=1&size=5,
* @return 返回分页数据
*/
@RequestMapping(value = "/page", method = RequestMethod.GET)
public ResponseObj<Page<T>> listPage(@ModelAttribute Page<T> page, @ModelAttribute T model) {
return baseExecute.listPage(page, model, service);
}
/**
* insert主要是在insert和update要分开权限的时候使用
*
* @param object
* @return
*/
@RequestMapping(value = "/insert", method = RequestMethod.POST)
@Transactional
public ResponseObj<Boolean> insert(@RequestBody @Valid T object) throws NoSuchMethodException,
IllegalAccessException, InvocationTargetException {
return baseExecute.insert(object);
}
/**
* update主要是在insert和update要分开权限的时候使用
*
* @param object
* @return
*/
@RequestMapping(value = "/upd", method = RequestMethod.POST)
@Transactional
public ResponseObj<Boolean> update(@RequestBody @Valid T object) {
return baseExecute.update(object);
}
/**
* 根据ID删除数据
*
* @return
*/
@RequestMapping(value = "/del", method = RequestMethod.POST)
@Transactional
public ResponseObj<Boolean> del(@RequestBody List<T> list) {
return baseExecute.deletebylogic(list);
}
/**
* 批量删除
*/
@RequestMapping(value = "/deleteBatch", method = RequestMethod.POST)
@Transactional
public ResponseObj<Boolean> delList(@RequestBody List<I> ids) {
boolean result = service.deleteBatchIds(ids);
RetCode retCode = result ? RetCode.SUCCESS : RetCode.FAIL;
return new ResponseObj<>(result, retCode);
}
}
BaseExecute
①对于selectById方法，参数传入ServiceImpl service，而这个service是BaseController中定义的，传入service之后可以使用mybatis-plus的一些方法
②对于listPage方法，使用了分页查询，传入的参数object即是条件，如果同时需要模糊查询，对model中的对应属性上加@TableField(condition = SqlCondition.LIKE)
package com.cicdi.servertemplate.modules.baselibrary.control;
import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.cicdi.servertemplate.common.model.ResponseObj;
import com.cicdi.servertemplate.common.model.RetCode;
import com.cicdi.servertemplate.common.util.MapBeanUtil;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestParam;
import java.util.List;
import java.util.Map;
@Component
public class BaseExecute<T> {
@Autowired
public SqlSession sqlSession;
@Value("${pName}")
private String pName;
@Value("${modelName}")
private String modelName;
/**
* 通用查询接口
* 根据主键id和条件返回查询结果
*
* @param object 传入的查询条件
* @param service 对应的service，这个参数很重要！！
* @return
*/
public ResponseObj<Object> selectById(@ModelAttribute Object object, ServiceImpl service) {
Object o1 = service.selectOne(new EntityWrapper(object).eq("IS_DELETE", "0"));
return new ResponseObj<>(o1, RetCode.SUCCESS);
}
/**
* 通用接口
* 获取列表,没有分页
*
* @return
*/
public ResponseObj<List<T>> list(Object object, ServiceImpl service) {
List<T> pageList = service.selectList(new EntityWrapper<>(object));
return new ResponseObj<>(pageList, RetCode.SUCCESS);
}
/**
* 通用接口
* 获取列表,可以分页。详情接口
*
* @param page 分页
* @param object 传入的查询条件
* @param service 对应的service，这个参数很重要！！
* @return
*/
public ResponseObj<Page<T>> listPage(Page page, Object object, ServiceImpl service) {
Class<?> clazz = object.getClass(); //获取obj的Class
String tableName = clazz.getSimpleName();
Page<T> pageList = null;
Object obj = null;
Map<String, Object> map = MapBeanUtil.beanToMap(object);
if (tableName.endsWith("WithBLOBs")) {
String[] withBLOBses = tableName.split("WithBLOBs");
tableName = withBLOBses[0].toString();
obj = MapBeanUtil.returnObj(tableName, map); //备份表结构
pageList = service.selectPage(page, new EntityWrapper<>(obj).eq("IS_DELETE", "0"));
} else {
pageList = service.selectPage(page, new EntityWrapper<>(object).eq("IS_DELETE", "0"));
}
return new ResponseObj<>(pageList, RetCode.SUCCESS);
}
/**
* 通用接口：新增
* mybatis-plus框架主键自动生成
* Object obj接收前端数据，其实里面是LinkHashMap,索性这里直接用Map map
* 备份表也插入一条记录：BAK_ID后台生成、IS_NEW=1、OPERATION=0、BATCH_TIME=服务器时间
* OPERATION：操作标志0：插入，1：更新，2：删除
*
* @return
*/
public ResponseObj<Boolean> insert(@RequestBody Object object) {
Class<?> c = object.getClass(); //获取obj的Class
String tableName = c.getSimpleName();
String tableNameBak;
Object objBak = null;
Object objBakBak = null;
Object logicObject = null;
Map map2;
Map<String, Object> map = MapBeanUtil.beanToMap(object);
int i;
int j;
tableNameBak = c.getSimpleName() + "Bak";
objBak = MapBeanUtil.returnObj(tableNameBak, map); //备份表结构
// String id = String.valueOf(IdWorker.getId()); //雪花算法生成id,mybatis不能自动生成id，使用mybatis-plus框架可以
// int pos = 0;
// for (Iterator<String> iterator = map.keySet().iterator(); iterator.hasNext(); pos++) {
// String keyId = iterator.next();
// if (pos == 0) { //备份表中的外键,取map中的第二个元素
// map.put(keyId, id);
// }
// }
// logicObject = MapBeanUtil.mapToBean(map, c);
// i = sqlSession.insert(pName + tableName + "Mapper.insertSelective", logicObject); //新增成功之后id会自动赋值
i = sqlSession.insert(pName + tableName + "Mapper.insert", object); //新增成功之后id会自动赋值
map2 = MapBeanUtil.transMap(object, objBak, "insert");
objBakBak = MapBeanUtil.returnObj(tableNameBak, map2);
j = sqlSession.insert(pName + tableNameBak + "Mapper.insert", objBakBak);
if (i == 1 && j == 1)
return new ResponseObj<Boolean>(true, RetCode.SUCCESS);
return new ResponseObj<Boolean>(false, RetCode.FAIL);
}
/**
* 通用接口：修改
* 修改备份表"IS_NEW=1 and 原表主键字段=当前传过来的主键"的记录的 IS_NEW=0；
* 备份表插入一条记录：BAK_ID后台生成、IS_NEW=1、OPERATION=1、BATCH_TIME=服务器时间；
* OPERATION：操作标志0：插入，1：更新，2：删除
*
* @return
*/
public ResponseObj<Boolean> update(@RequestBody Object object) {
Class<?> c = object.getClass(); //获取obj的Class, object原表结构
String tableName = c.getSimpleName();
String tableNameBak;
Object objBak = null;
Object objBakBak = null;
Map map2;
Map<String, Object> map = MapBeanUtil.beanToMap(object);
int i;
int j;
int k;
tableNameBak = c.getSimpleName() + "Bak";
objBak = MapBeanUtil.returnObj(tableNameBak, map); //备份表结构
i = sqlSession.update(pName + tableName + "Mapper.updateByPrimaryKeySelective", object);
j = sqlSession.update(pName + tableNameBak + "Mapper.updateByPrimaryKeySelective", objBak);
map2 = MapBeanUtil.transMap(object, objBak, "update");
objBakBak = MapBeanUtil.returnObj(tableNameBak, map2);
k = sqlSession.insert(pName + tableNameBak + "Mapper.insert", objBakBak);
if (i == 1 && j == 1 && k == 1)
return new ResponseObj<Boolean>(true, RetCode.SUCCESS);
return new ResponseObj<Boolean>(false, RetCode.FAIL);
}
/**
* 通用接口：逻辑删除，逻辑删除原表
* 修改备份表"IS_NEW=1 and 原表主键字段=当前传过来的主键"的记录的 IS_NEW=0；
* 备份表插入一条记录：BAK_ID后台生成、IS_NEW=1、OPERATION=2、BATCH_TIME=服务器时间；
* OPERATION：操作标志0：插入，1：更新，2：删除
*
* @return
*/
public ResponseObj<Boolean> deletebylogic(@RequestBody List<T> list) {
int i1 = 0;
int j1 = 0;
int k1 = 0;
if (list != null && list.size() > 0) {
for (T object : list) {
Class<?> c = object.getClass(); //获取obj的Class
String tableName = c.getSimpleName();
String tableNameBak;
Object objBak = null; //原表
Object objBakBak = null; //备份表结构
Object logicObject = null; //逻辑删除原表
Map map2;
int i;
int j;
int k;
Map<String, Object> map = MapBeanUtil.beanToMap(object);
map.put("isDelete", "1"); //0没有删除 1逻辑删除
try {
logicObject = MapBeanUtil.mapToBean(map, c);
} catch (Exception e) {
}
tableNameBak = c.getSimpleName() + "Bak";
map.put("isDelete", null);
objBak = MapBeanUtil.returnObj(tableNameBak, map); //备份表结构
//修改原表isDelete字段
i = sqlSession.update(pName + tableName + "Mapper.updateByPrimaryKeySelective", logicObject);
j = sqlSession.update(pName + tableNameBak + "Mapper.updateByPrimaryKeySelective", objBak);
map2 = MapBeanUtil.transMap(object, objBak, "delete");
objBakBak = MapBeanUtil.returnObj(tableNameBak, map2);
k = sqlSession.insert(pName + tableNameBak + "Mapper.insert", objBakBak);
if (i == 1)
i1++;
if (j == 1)
j1++;
if (k == 1)
k1++;
}
}
if (i1 == list.size() && j1 == list.size() && k1 == list.size())
return new ResponseObj<Boolean>(true, RetCode.SUCCESS);
return new ResponseObj<Boolean>(false, RetCode.FAIL);
}
/**
* 物理删除
*
* @return
*/
public ResponseObj<Boolean> delete(@RequestParam Object object) {
Class<?> c = object.getClass(); //获取obj的Class
String tableName = c.getSimpleName();
String tableNameBak = c.getSimpleName() + "Bak";
Map<String, Object> map = MapBeanUtil.beanToMap(object);
// String tableName = (String) map.get("_tbName");
// String tableNameBak = (String) map.get("_tbNameBak"); //备份表
// Object obj = MapBeanUtil.returnObj(tableName, map); //原表结构
Object objBak = MapBeanUtil.returnObj(tableNameBak, map); //备份表结构
int i = sqlSession.delete(pName + tableName + "Mapper.deleteByPrimaryKey", object);
int j = sqlSession.delete(pName + tableNameBak + "Mapper.deleteByPrimaryKey", objBak);
if (i == 1 & j == 1)
return new ResponseObj<Boolean>(true, RetCode.SUCCESS);
return new ResponseObj<Boolean>(false, RetCode.FAIL);
}
}
PbHouseController
子类xxControl继承BaseController，其中@TargetDataSource("ds3")指定使用哪个数据源
/**
* 子类继承的时候必须指定类型：<PbzHouseDwell>
*/
@TargetDataSource("ds3")
@RestController
@RequestMapping("/api/pbHouse")
public class PbHouseController extends /*SingleRecordControl<PbzHouseDwell>*/
BaseController<PbHouseService,
PbHouseMapper,
PbHouse, Long> {
}
对于不适用这些方法的需求，在各自xxControl中自定义接口方法。比如多表关联查询同时需要将查出来的结果分页
PbHujiController
@TargetDataSource("ds3")
@RestController
@RequestMapping("/api/pbHuji")
public class PbHujiController extends BaseController<PbHujiService,
PbHujiMapper,
PbHuji, Long> {
/**
* 多表关联分页查询
*
* @param page 分页参数
* @param model 搜索条件
*/
@RequestMapping(value = "/list", method = RequestMethod.GET)
public ResponseObj<Page<PbHuji>> list(@ModelAttribute Page<PbHuji> page,
@ModelAttribute PbHuji model) {
Page<PbHuji> pageList = service.list(page, new EntityWrapper<>(model));
return new ResponseObj<>(pageList, RetCode.SUCCESS);
}
}
对应的PbHujiService
@Service
public class PbHujiService extends ServiceImpl<PbHujiMapper, PbHuji> {
public Page<PbHuji> list(Page<PbHuji> page, Wrapper<PbHuji> wrapper) {
return page.setRecords(baseMapper.list(page, wrapper));
}
}
对应的baseMapper
/**
* 不能使用selectList这个方法名，与BaseMapper中的方法名重复了
* 根据 entity 条件，查询全部记录并分页
* @param rowBounds 分页条件
* @param wrapper 查询条件
* @return
*/
List<PbHuji> list(RowBounds rowBounds, @Param("ew") Wrapper<PbHuji> wrapper);
对应的baseMapper.xml
<select id="list" resultType="com.cicdi.servertemplate.modules.baselibrary.model.PbHuji">
SELECT
ph.*, pc.COMM_NAME,
px.XIAOQU_NAME,
pbl.BUILD_NAME
FROM
pb_huji ph
LEFT JOIN pb_community pc ON ph.COMMUNITY_ID = pc.COMMUNITY_ID
AND pc.IS_DELETE = 0
LEFT JOIN pb_xiaoqu px ON ph.XIAOQU_ID = px.XIAOQU_ID
AND px.IS_DELETE = 0
LEFT JOIN pb_building pbl ON ph.BUILD_ID = pbl.BUILD_ID
AND pbl.IS_DELETE = 0
WHERE (ph.IS_DELETE = '0')
<if test="ew!=null">
<if test="ew.entity!=null">
<if test="ew.entity.headName!=null">
AND ph.HEAD_NAME LIKE CONCAT('%',#{ew.entity.headName},'%')
</if>
<if test="ew.entity.headCardNo!=null">
AND ph.HEAD_CARD_NO LIKE CONCAT('%',#{ew.entity.headCardNo},'%')
</if>
<if test="ew.entity.hujiAddr!=null">
AND ph.HUJI_ADDR LIKE CONCAT('%',#{ew.entity.hujiAddr},'%')
</if>
<if test="ew.entity.liveAddr!=null">
AND ph.LIVE_ADDR LIKE CONCAT('%',#{ew.entity.liveAddr},'%')
</if>
</if>
<if test="ew.sqlSegment!=null">
${ew.sqlSegment}
</if>
</if>
</select>
