通用表单增删改查接口

本文主要是介绍通用表单增删改查接口，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

技术点

• 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);
    }

}```

这篇关于通用表单增删改查接口的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！