技术点
- springboot
- mybatis-plus
- shiro
- swagger
maven依赖
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1</version>
</dependency>
yaml配置
#mybatis plus 设置
mybatis-plus:
mapper-locations: classpath*:org/jeecg/modules/**/xml/*Mapper.xml
global-config:
# 关闭MP3.0自带的banner
banner: false
db-config:
#主键类型 0:"数据库ID自增",1:"该类型为未设置主键类型", 2:"用户输入ID",3:"全局唯一ID (数字类型唯一ID)", 4:"全局唯一ID UUID",5:"字符串全局唯一ID (idWorker 的字符串表示)";
id-type: ASSIGN_ID
# 默认数据库表下划线命名
table-underline: true
configuration:
# 这个配置会将执行的sql打印出来,在开发或测试的时候可以用
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
# 返回类型为Map,显示null对应的字段
call-setters-on-nulls: true
代码
- entity类
/**
- controller层查询条件
*/
public class StandardQueryWrapper {
// 数据库表名称
private String tableName;
// 查询类型: and or 默认查询为and
private String superQueryMatchType;
// 查询参数
private String superQueryParams;
// 分组
private String groupBy;
// 排序字段,只支持单个字段排序,自行扩展
private String orderBy;
// 默认为false,所以是倒序
private boolean desc;
// 默认第0
private int pageNo = 1;
// 默认10
private int pageSize;
public String getSuperQueryMatchType() {
return superQueryMatchType;
}
public void setSuperQueryMatchType(String superQueryMatchType) {
this.superQueryMatchType = superQueryMatchType;
}
public String getTableName() {
return tableName;
}
public void setTableName(String tableName) {
this.tableName = tableName;
}
public String getSuperQueryParams() {
return superQueryParams;
}
public void setSuperQueryParams(String superQueryParams) {
this.superQueryParams = superQueryParams;
}
public String getGroupBy() {
return groupBy;
}
public void setGroupBy(String groupBy) {
this.groupBy = groupBy;
}
public String getOrderBy() {
return orderBy;
}
public void setOrderBy(String orderBy) {
this.orderBy = orderBy;
}
public boolean isDesc() {
return desc;
}
public void setDesc(boolean desc) {
this.desc = desc;
}
public int getPageNo() {
return pageNo;
}
public void setPageNo(int pageNo) {
this.pageNo = pageNo;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
@Override
public String toString() {
return "StandardQueryWrapper{" +
"tableName='" + tableName + '\'' +
", superQueryMatchType='" + superQueryMatchType + '\'' +
", superQueryParams='" + superQueryParams + '\'' +
", groupBy='" + groupBy + '\'' +
", orderBy='" + orderBy + '\'' +
", desc=" + desc +
", pageNo=" + pageNo +
", pageSize=" + pageSize +
'}';
}
}
import lombok.Getter;
import lombok.Setter;
import lombok.ToString;
import java.util.Map;
/**
* 请求参数
*/
@Getter
@Setter
@ToString
public class CommonForm {
/**
* 数据库表名称
*/
private String tableName;
/**
* 数据库表字段名称使用逗号拼接
*/
private String filedNames;
/**
* 表主键id
*/
private String tableId;
/**
* 数据库字段对应的键值对
*/
private Map<String, Object> filedValueMap;
}
import com.alibaba.fastjson.JSON;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.util.List;
/**
* 构建查询条件
*/
public class StandardQueryWrapperBuild {
private static final Logger logger = LoggerFactory.getLogger(StandardQueryWrapperBuild.class);
private StringBuffer stringBuffer = new StringBuffer(" select * from ");
private String tableName;
private StandardQueryWrapper standardQueryWrapper;
public StandardQueryWrapperBuild(String tableName) {
this.tableName = tableName;
}
public StandardQueryWrapperBuild(String tableName, StandardQueryWrapper standardQueryWrapper) {
this.tableName = tableName;
this.standardQueryWrapper = standardQueryWrapper;
}
public String build() {
// 1.拼接表名
appendTable();
// 1.拼接where条件
appendCondition();
// 3.拼接分组
appendGroup();
// 4.拼接排序
appendSort();
// 5.拼接分页
appendPage();
return this.stringBuffer.toString();
}
/**
* 添加表名
*/
private void appendTable() {
this.stringBuffer.append(this.tableName).append(" ");
}
/**
* 拼接where条件
*/
private void appendCondition() {
String superQueryParams = this.standardQueryWrapper.getSuperQueryParams();
if (StringUtils.isBlank(superQueryParams)) {
return;
}
try {
String condition = URLDecoder.decode(this.standardQueryWrapper.getSuperQueryParams(), "utf-8");
logger.debug("condition: {}", condition);
List<QueryItem> queryItems = JSON.parseArray(condition, QueryItem.class);
if (CollectionUtils.isEmpty(queryItems)) {
return;
}
// 拼接 where 关键字
this.stringBuffer.append(" ")
.append("where 1=1 ");
// 解析QueryItem
ParseQueryItem parseQueryItem = new ParseQueryItem(queryItems);
// 解析结果: 只包含where拼接
String superQueryMatchType = this.standardQueryWrapper.getSuperQueryMatchType();
boolean isAnd = false;
if (StringUtils.isBlank(superQueryMatchType) || "and".equals(superQueryMatchType)) {
isAnd = true;
}
logger.debug("isAnd: {}", isAnd);
String parseResult = parseQueryItem.parse(isAnd);
logger.debug("parseResult: {}", parseResult);
this.stringBuffer.append(parseResult);
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
}
/**
* 拼接分组
*/
private void appendGroup() {
String groupBy = this.standardQueryWrapper.getGroupBy();
if (StringUtils.isNotBlank(groupBy)) {
this.stringBuffer.append(" GROUP BY ").append(groupBy).append(" ");
}
}
/**
* 拼接排序
*/
private void appendSort() {
String orderBy = this.standardQueryWrapper.getOrderBy();
if (StringUtils.isNotBlank(orderBy)) {
if (this.standardQueryWrapper.isDesc()) {
this.stringBuffer.append(" ORDER BY ").append(orderBy).append(" ").append("desc").append(" ");
} else {
this.stringBuffer.append(" ORDER BY ").append(orderBy).append(" ").append("asc").append(" ");
}
}
}
/**
* 拼接分页 (pageNo-1)*pageSize,pageSize
*/
private void appendPage() {
int pageNo = this.standardQueryWrapper.getPageNo() < 1 ? 1 : this.standardQueryWrapper.getPageNo();
int pageSize = this.standardQueryWrapper.getPageSize() == 0 ? 10 : this.standardQueryWrapper.getPageSize();
this.stringBuffer.append(" limit ").append((pageNo - 1) * pageSize).append(", ").append(pageSize).append(" ");
}
}
mapper层
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;
import org.jeecg.modules.system.model.CommonForm;
import java.util.List;
import java.util.Map;
public interface CommonFormMapper extends BaseMapper<CommonForm> {
@Insert("${sql}")
int saveCommonForm(@Param("sql") String sql);
@Update("${sql}")
int updateCommonForm(@Param("sql") String sql);
@Delete("${sql}")
int removeCommonForm(@Param("sql") String sql);
@Select("${sql}")
List<Map<String,Object>> queryCommonFormList(@Param("sql") String sql);
@Select("select * from ${tableName} where id = #{tableId}")
Map<String,Object> getCommonForm(@Param("tableId") String tableId, @Param("tableName") String tableName);
}
service层
import com.baomidou.mybatisplus.extension.service.IService;
import org.jeecg.modules.system.model.CommonForm;
import org.jeecg.modules.system.query.StandardQueryWrapper;
import java.util.List;
import java.util.Map;
public interface ICommonFormService extends IService<CommonForm> {
/**
* 根据数据库表名和字段名以及字段的值,通用保存数据
* @param commonForm
* @return
*/
String saveCommonForm(CommonForm commonForm);
/**
* 根据数据库表名和字段名和id值获取数据
* @param commonForm
* @return
*/
Map<String,Object> queryCommonFormByTableAndName(CommonForm commonForm);
/**
* 根据数据库表名和字段名和id值移除数据
* @param commonForm
*/
void removeCommonFormByTableAndName(CommonForm commonForm);
/**
* 根据查询条件获取列表
* 暂时只能查询全部
* @param standardQueryWrapper
* @return
*/
List<Map<String, Object>> queryCommonFormList(StandardQueryWrapper standardQueryWrapper);
}
iml实现类
import cn.hutool.core.map.MapUtil;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.apache.shiro.SecurityUtils;
import org.jeecg.common.system.vo.LoginUser;
import org.jeecg.common.util.DateUtils;
import org.jeecg.modules.business.exception.SelfBusinessException;
import org.jeecg.modules.system.mapper.CommonFormMapper;
import org.jeecg.modules.system.model.CommonForm;
import org.jeecg.modules.system.query.StandardQueryWrapper;
import org.jeecg.modules.system.query.StandardQueryWrapperBuild;
import org.jeecg.modules.system.service.ICommonFormService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import java.util.Date;
import java.util.List;
import java.util.Map;
@Service
public class CommonFormServiceImpl extends ServiceImpl<CommonFormMapper, CommonForm> implements ICommonFormService {
private Logger logger = LoggerFactory.getLogger(CommonFormServiceImpl.class);
/**
* 保存或修改
*
* @param commonForm
* @return
*/
@Override
public String saveCommonForm(CommonForm commonForm) {
String tableName = commonForm.getTableName();
String filedNames = commonForm.getFiledNames();
String tableId = commonForm.getTableId();
Map<String, Object> busiData = this.baseMapper.getCommonForm(tableId, tableName);
try {
String[] fileds = filedNames.split(",");
if (MapUtil.isEmpty(busiData)) { //没有,新增逻辑
saveTableData(tableId, commonForm.getFiledValueMap(), tableName, fileds);
} else { //有,修改
updateTableData(tableId, commonForm.getFiledValueMap(), tableName, fileds);
}
} catch (Exception e) {
logger.error("通用保存在线表单数据异常: {} {} {} ", e.getMessage(), e.getCause(), e);
throw new SelfBusinessException(e);
}
return tableId;
}
@Override
public Map<String, Object> queryCommonFormByTableAndName(CommonForm commonForm) {
return this.baseMapper.getCommonForm(commonForm.getTableId(), commonForm.getTableName());
}
/**
* 根据数据库表名和字段名和id值移除数据
*
* @param commonForm
*/
@Override
public void removeCommonFormByTableAndName(CommonForm commonForm) {
String sql = String.format("delete from %s where id = '%s'", commonForm.getTableName(), commonForm.getTableId());
this.baseMapper.removeCommonForm(sql);
}
/**
* 根据查询条件获取列表
* 暂时只能查询全部
*
* @param standardQueryWrapper
* @return
*/
@Override
public List<Map<String, Object>> queryCommonFormList(StandardQueryWrapper standardQueryWrapper) {
// 建造器解析sql
StandardQueryWrapperBuild queryWrapperBuild = new StandardQueryWrapperBuild(standardQueryWrapper.getTableName(), standardQueryWrapper);
String sqlString = queryWrapperBuild.build();
// String sql = String.format("select * from %s", standardQueryWrapper.getTableName());
return this.baseMapper.queryCommonFormList(sqlString);
}
/**
* 拼接新增sql
*
* @param tableId
* @param filedValueMap
* @param tableName
* @param fileds
*/
private void saveTableData(String tableId, Map<String, Object> filedValueMap, String tableName, String[] fileds) {
StringBuilder filedsB = new StringBuilder("id");
StringBuilder filedsVB = new StringBuilder("'" + tableId + "'");
for (String filed : fileds) {
// 这里不做驼峰转化处理
// String dbFiled = oConvertUtils.camelToUnderline(filed);
if (filed != null && !filed.equals("undefined")) {
if (filedValueMap.get(filed) != null && filedValueMap.get(filed) != "") {
filedsB.append("," + filed);
filedsVB.append(",'" + filedValueMap.get(filed) + "'");
}
}
}
LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
String userName = sysUser.getUsername();
filedsB.append("," + "create_by");
filedsVB.append(",'" + userName + "'");
filedsB.append("," + "create_time");
filedsVB.append(",'" + DateUtils.formatDate(new Date(), "yyyy-MM-dd HH:mm:ss") + "'");
this.baseMapper.saveCommonForm(String.format("INSERT INTO %s (%s) VALUES (%s)", tableName, filedsB.toString(), filedsVB.toString()));
}
/**
* 拼接修改sql
*
* @param tableId
* @param filedValueMap
* @param tableName
* @param fileds
*/
private void updateTableData(String tableId, Map<String, Object> filedValueMap, String tableName, String[] fileds) {
StringBuilder setSql = new StringBuilder();
for (String filed : fileds) {
if (filed != null && !filed.equals("undefined")) {
if (filedValueMap.get(filed) != null && filedValueMap.get(filed) != "") {
// 这里不做驼峰转化处理
// String dbFiled = oConvertUtils.camelToUnderline(filed);
setSql.append(String.format("%s = '%s',", filed, filedValueMap.get(filed)));
}
}
}
String substring = setSql.substring(0, setSql.length() - 1);//去掉最后一个,号
LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
String userName = sysUser.getUsername();
substring += (",update_by = " + "'" + userName + "'");
substring += (",update_time = " + "'" + DateUtils.formatDate(new Date(), "yyyy-MM-dd HH:mm:ss") + "'");
this.baseMapper.updateCommonForm(String.format("update %s set %s where id = '%s'", tableName, substring, tableId));
}
}
controller层
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import me.zhyd.oauth.utils.UuidUtils;
import org.apache.commons.lang3.StringUtils;
import org.jeecg.common.api.vo.Result;
import org.jeecg.common.aspect.annotation.AutoLog;
import org.jeecg.modules.system.model.CommonForm;
import org.jeecg.modules.system.query.StandardQueryWrapper;
import org.jeecg.modules.system.service.ICommonFormService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletRequest;
import java.util.List;
import java.util.Map;
@Api(tags = "基础表单")
@RestController
@RequestMapping("/system/commonform")
@Slf4j
public class CommonFormController {
@Autowired
private ICommonFormService commonFormService;
/**
* 分页列表查询
*
* @param standardQueryWrapper
* @param pageNo
* @param pageSize
* @param req
* @return
*/
@AutoLog(value = "基础表单-分页列表查询")
@ApiOperation(value = "基础表单-分页列表查询", notes = "基础表单-分页列表查询")
@GetMapping(value = "/list")
public Result<?> queryPageList(StandardQueryWrapper standardQueryWrapper,
@RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
@RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
HttpServletRequest req) {
if (StringUtils.isBlank(standardQueryWrapper.getTableName())) {
return Result.error("tableName不能为空");
}
List<Map<String, Object>> list = commonFormService.queryCommonFormList(standardQueryWrapper);
return Result.OK(list);
}
/**
* 添加
*
* @param commonForm
* @return
*/
@AutoLog(value = "基础表单-添加")
@ApiOperation(value = "基础表单-添加", notes = "基础表单-添加")
@PostMapping(value = "/add")
public Result<?> add(@RequestBody CommonForm commonForm) {
if (StringUtils.isBlank(commonForm.getTableName())) {
return Result.error("tableName不能为空");
}
if (StringUtils.isBlank(commonForm.getFiledNames())) {
return Result.error("filedNames不能为空");
}
if (commonForm.getFiledValueMap() == null || commonForm.getFiledValueMap().size() == 0) {
return Result.error("filedValueMap不能为空");
}
if (StringUtils.isBlank(commonForm.getTableId())) {
commonForm.setTableId(UuidUtils.getUUID());
}
commonFormService.saveCommonForm(commonForm);
return Result.OK(commonForm);
}
/**
* 通过id删除
*
* @param commonForm
* @return
*/
@AutoLog(value = "基础表单-通过id删除")
@ApiOperation(value = "基础表单-通过id删除", notes = "基础表单-通过id删除")
@PostMapping(value = "/delete")
public Result<?> delete(CommonForm commonForm) {
if (StringUtils.isBlank(commonForm.getTableName())) {
return Result.error("表名称不能为空");
}
if (StringUtils.isBlank(commonForm.getTableId())) {
return Result.error("id不能为空");
}
commonFormService.removeCommonFormByTableAndName(commonForm);
return Result.OK("删除成功!");
}
/**
* 通过id查询
*
* @param commonForm
* @return
*/
@AutoLog(value = "基础表单-通过id查询")
@ApiOperation(value = "基础表单-通过id查询", notes = "基础表单-通过id查询")
@GetMapping(value = "/queryById")
public Result<?> queryById(CommonForm commonForm) {
if (StringUtils.isBlank(commonForm.getTableName())) {
return Result.error("表名称不能为空");
}
if (StringUtils.isBlank(commonForm.getTableId())) {
return Result.error("id不能为空");
}
Map<String, Object> stringObjectMap = commonFormService.queryCommonFormByTableAndName(commonForm);
if (stringObjectMap == null) {
return Result.error("未找到对应数据");
}
return Result.OK(stringObjectMap);
}
}```