数据库WEB客户端工具

王大锤4391

已于 2024-05-13 17:12:19 修改

阅读量159

点赞数 3

文章标签：数据库前端 spring boot

于 2024-04-29 13:39:08 首次发布

本文链接：https://blog.csdn.net/weixin_41037490/article/details/138310175

版权

1.需求，在内网环境下，外部第三方客户端工具直接连接数据库，需要放开3306等端口，在特殊环境下，不一定能开放，在此第三方客户端工具用不了。有没有一种办法在web界面直接操作数据库，

目前支持 mysql, 达梦，neo4j数据库

2.代码

package com.admin.controller;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;

import java.util.List;

import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.admin.common.annotation.Log;
import com.admin.common.core.controller.BaseController;
import com.admin.common.core.domain.AjaxResult;
import com.admin.common.enums.BusinessType;
import com.admin.domain.DbDataResource;
import com.admin.service.IDbDataResourceService;
import com.admin.common.utils.poi.ExcelUtil;
import com.admin.common.core.page.TableDataInfo;

/**
 * 数据源配置管理Controller
 *
 * @author wangweia
 * @date 2024-04-28
 */
@Api(tags = "数据源配置管理")
@RestController
@RequestMapping("/business/dbresource")
public class DbDataResourceController extends BaseController {
    @Autowired
    private IDbDataResourceService dbDataResourceService;

    /**
     * 查询数据源配置管理列表
     */
    //@PreAuthorize("@ss.hasPermi('business:dbresource:list')")
    @ApiOperation(value = "查询数据源配置管理列表")
    @GetMapping("/list")
    public TableDataInfo list(DbDataResource dbDataResource) {
        startPage();
        List<DbDataResource> list = dbDataResourceService.selectDbDataResourceList(dbDataResource);
        return getDataTable(list);
    }

    /**
     * 导出数据源配置管理列表
     */
    @ApiOperation(value = "导出数据源配置管理列表")
    //@PreAuthorize("@ss.hasPermi('business:dbresource:export')")
    @Log(title = "数据源配置管理", businessType = BusinessType.EXPORT)
    @GetMapping("/export")
    public AjaxResult export(DbDataResource dbDataResource) {
        List<DbDataResource> list = dbDataResourceService.selectDbDataResourceList(dbDataResource);
        ExcelUtil<DbDataResource> util = new ExcelUtil<DbDataResource>(DbDataResource.class);
        return util.exportExcel(list, "dbresource");
    }

    /**
     * 获取数据源配置管理详细信息
     */
    @ApiOperation(value = "获取数据源配置管理详细信息")
    //@PreAuthorize("@ss.hasPermi('business:dbresource:query')")
    @GetMapping(value = "/{id}")
    public AjaxResult getInfo(@PathVariable("id") Long id) {
        return AjaxResult.success(dbDataResourceService.selectDbDataResourceById(id));
    }

    /**
     * 新增数据源配置管理
     */
    @ApiOperation(value = "新增数据源配置管理")
    //@PreAuthorize("@ss.hasPermi('business:dbresource:add')")
    @Log(title = "数据源配置管理", businessType = BusinessType.INSERT)
    @PostMapping
    public AjaxResult add(@RequestBody DbDataResource dbDataResource) {
        return toAjax(dbDataResourceService.insertDbDataResource(dbDataResource));
    }

    /**
     * 修改数据源配置管理
     */
    @ApiOperation(value = "修改数据源配置管理")
    //@PreAuthorize("@ss.hasPermi('business:dbresource:edit')")
    @Log(title = "数据源配置管理", businessType = BusinessType.UPDATE)
    @PutMapping
    public AjaxResult edit(@RequestBody DbDataResource dbDataResource) {
        return toAjax(dbDataResourceService.updateDbDataResource(dbDataResource));
    }

    /**
     * 物理删除数据源配置管理
     */
    @ApiOperation(value = "物理删除数据源配置管理")
    //@PreAuthorize("@ss.hasPermi('business:dbresource:remove')")
    @Log(title = "数据源配置管理", businessType = BusinessType.DELETE)
    @DeleteMapping("/{ids}")
    public AjaxResult remove(@PathVariable Long[] ids) {
        return toAjax(dbDataResourceService.deleteDbDataResourceByIds(ids));
    }


    /**
     * 逻辑删除数据源配置管理,单条数据删除
     */
    @ApiOperation(value = "物理删除数据源配置管理， 单条数据删除")
    //@PreAuthorize("@ss.hasPermi('business:dbresource:remove')")
    @Log(title = "数据源配置管理", businessType = BusinessType.DELETE)
    @DeleteMapping("/removeFasleById/{id}")
    public AjaxResult removeFasleById(@PathVariable Long id) {
        return toAjax(dbDataResourceService.deleteDbDataResourceremoveFasleById(id));
    }


    /**
     * 批量逻辑删除数据源配置管理
     */
    @ApiOperation(value = "批量逻辑删除数据源配置管理")
    //@PreAuthorize("@ss.hasPermi('business:dbresource:remove')")
    @Log(title = "数据源配置管理", businessType = BusinessType.DELETE)
    @DeleteMapping("/removeFasleByIds/{id}")
    public AjaxResult removeFasleByIds(@PathVariable Long[] id) {
        return toAjax(dbDataResourceService.deleteDbDataResourceremoveFasleByIds(id));
    }
}

package com.admin.controller;



import com.admin.common.annotation.Log;
import com.admin.common.core.controller.BaseController;
import com.admin.common.core.domain.AjaxResult;
import com.admin.common.core.domain.model.LoginUser;
import com.admin.common.enums.BusinessType;
import com.admin.common.utils.DateUtils;
import com.admin.common.utils.SecurityUtils;
import com.admin.service.SqlTableService;
import com.alibaba.fastjson.JSON;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

/**
 * 功能描述：
 * sql执行后台
 * @author wangwei
 * @date 2024-04-26 11:09
 */
@RestController
@RequestMapping("/sql/client")
public class SqlTableController extends BaseController {

    @Autowired
    private SqlTableService sqlTableService;

    /***
     * 获取数据源配置
     */
    @GetMapping("/getdatasource")
    public AjaxResult getDataSource() {
        List<Map<String, Object>> maps = sqlTableService.getDataSourceList();
        System.out.println("=======>" + JSON.toJSONString(maps));
        return AjaxResult.success(maps);
    }

    /***
     * 获取所有表数据信息(数据库表以及表名称信息)
     * @param datasourceid
     * @param database
     * @return
     */
    @GetMapping("/getTableList")
    public AjaxResult getTableList(String datasourceid, String database){
        List<Map<String, Object>> maps = sqlTableService.getTableList(database);
        System.out.println("=======>"+JSON.toJSONString(maps));
        return AjaxResult.success(maps);
    }


    /***
     * 查询返回数据库  列名，以及备注
     * http://192.168.1.99:7070/sql/client/getTableColnums?database=admin-project&tablename=sys_user
     * @param datasourceid
     * @param database
     * @param tablename
     * @return
     */
    @GetMapping("/getTableColnums")
    public AjaxResult getTableColnums(String datasourceid, String database, String tablename){
        List<Map<String, Object>> maps = sqlTableService.getTableColnums(database,tablename);
        System.out.println("=======查询的数据库表头信息为：>"+ JSON.toJSONString(maps));
        return AjaxResult.success(maps);
    }


    /***
     * 执行查询sql 返回数据集
     * 将客户端输入的所有大写 转换成小写
     * @param datasourceid
     * @param database
     * @param sql
     * @return
     */
    @Log(title = "获取数据源配置", businessType = BusinessType.OTHER)
    @GetMapping("/runWithSql")
    public AjaxResult runWithSql(String datasourceid, String database,String sql){
        SimpleDateFormat sdfTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        LoginUser loginUser = SecurityUtils.getLoginUser();

        AjaxResult ajaxResult = sqlTableService.runWithSql(sql);

        logger.info("用户id为："+loginUser.getUserId().toString() +"====用户账号为："+loginUser.getUsername()+"======在"
                +sdfTime.format(DateUtils.getNowDate())+"执行了一次sql语句，执行sql为："+sql);
        System.out.println(sdfTime.format(DateUtils.getNowDate()) + " 输入执行的SQL语句为：>" +sql.toLowerCase());
        System.out.println("=======查询的结果集数据为：>"+JSON.toJSONString(ajaxResult.get("data")));
        return ajaxResult;
    }


}

package com.admin.domain;

import lombok.Data;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.NoArgsConstructor;
import io.swagger.annotations.ApiModelProperty;
import java.util.Date;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.admin.common.annotation.Excel;

/**
 * 数据源配置管理对象 db_data_resource
 *
 * @author wangwei
 * @date 2024-04-28
 */
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class DbDataResource {
    private static final long serialVersionUID = 1L;

    /**
     * 序号
     */
    private Long id;

    /**
     * 连接名称
     */
    @ApiModelProperty(value = "连接名称")
    @Excel(name = "连接名称")
    private String connname;

    /**
     * 数据库类型
     */
    @ApiModelProperty(value = "数据库类型")
    @Excel(name = "数据库类型")
    private String conntype;

    /**
     * 驱动包名
     */
    @ApiModelProperty(value = "驱动包名")
    @Excel(name = "驱动包名")
    private String conndriverclass;

    /**
     * IP地址
     */
    @ApiModelProperty(value = "IP地址")
    @Excel(name = "IP地址")
    private String connip;

    /**
     * 端口号
     */
    @ApiModelProperty(value = "端口号")
    @Excel(name = "端口号")
    private String connport;

    /**
     * 数据库名
     */
    @ApiModelProperty(value = "数据库名")
    @Excel(name = "数据库名")
    private String conndbname;

    /**
     * 用户名
     */
    @ApiModelProperty(value = "用户名")
    @Excel(name = "用户名")
    private String connusername;

    /**
     * 密码
     */
    @ApiModelProperty(value = "密码")
    @Excel(name = "密码")
    private String connpassword;

    /**
     * 附加参数
     */
    @ApiModelProperty(value = "附加参数")
    @Excel(name = "附加参数")
    private String connproperty;

    /**
     * 创建日期
     */
    @ApiModelProperty(value = "创建日期" + " yyyy-MM-dd")
    @JsonFormat(pattern = "yyyy-MM-dd")
    @Excel(name = "创建日期", width = 30, dateFormat = "yyyy-MM-dd")
    private Date createdate;


}

package com.admin.domain;

import lombok.Data;

/**
 * 功能描述：
 *
 * @author wangwei
 * @date 2024-01-15 22:13
 */
@Data
public class ConnectWeb {

    private String connectWebId;

    private String connectWebName;

    private String connectWebInfo;

    private String personWebIdAlpha;

    private String personWebIdBeta;



    private String personWebIdAlphaName;

    private String personWebIdBetaName;


    //下属的姓名
    private String personWebName;

    //下属的id
    private String personWebId;




}

package com.admin.service;

import com.admin.common.core.domain.AjaxResult;
import com.admin.domain.ConnectWeb;

import java.util.List;
import java.util.Map;

public interface SqlTableService {


    List<Map<String, Object>> getTableColnums(String database, String tablename);

    AjaxResult runWithSql(String sql);

    List<Map<String, Object>> getDataSourceList();

    List<Map<String, Object>> getTableList(String database);
}

package com.admin.service;

import java.util.List;
import com.admin.domain.DbDataResource;

/**
 * 数据源配置管理Service接口
 * @author wangwei
 * @date 2024-04-28
 */
public interface IDbDataResourceService
{
    /**
     * 查询数据源配置管理
     *
     * @param id 数据源配置管理ID
     * @return 数据源配置管理
     */
    public DbDataResource selectDbDataResourceById(Long id);

    /**
     * 查询数据源配置管理列表
     * @param dbDataResource 数据源配置管理
     * @return 数据源配置管理集合
     */
    public List<DbDataResource> selectDbDataResourceList(DbDataResource dbDataResource);

    /**
     * 新增数据源配置管理
     * @param dbDataResource 数据源配置管理
     * @return 结果
     */
    public int insertDbDataResource(DbDataResource dbDataResource);

    /**
     * 修改数据源配置管理
     * @param dbDataResource 数据源配置管理
     * @return 结果
     */
    public int updateDbDataResource(DbDataResource dbDataResource);

    /**
     * 物理删批量删除数据源配置管理
     * @param ids 需要删除的数据源配置管理ID
     * @return 结果
     */
    public int deleteDbDataResourceByIds(Long[] ids);

    /**
     * 物理删删除数据源配置管理信息
     * @param id 数据源配置管理ID
     * @return 结果
     */
    public int deleteDbDataResourceById(Long id);


    /**
     * 逻辑删删除数据源配置管理信息
     * @param id 数据源配置管理ID
     * @return 结果
     */
    public int deleteDbDataResourceremoveFasleById(Long id);

    /**
     * 逻辑删删除数据源配置管理信息-批量删除
     * @param id 数据源配置管理ID
     * @return 结果
     */
    public int deleteDbDataResourceremoveFasleByIds(Long[] id);
}

package com.admin.service.impl;

import com.admin.common.core.domain.model.LoginUser;
import org.springframework.transaction.annotation.Transactional;
import com.admin.common.exception.CustomException;
import com.admin.common.utils.SecurityUtils;


import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.admin.mapper.DbDataResourceMapper;
import com.admin.domain.DbDataResource;
import com.admin.service.IDbDataResourceService;

/**
 * 数据源配置管理Service业务层处理
 *
 * @author wangwei
 * @date 2024-04-28
 */
@Service
public class DbDataResourceServiceImpl implements IDbDataResourceService {
    @Autowired
    private DbDataResourceMapper dbDataResourceMapper;


    /**
     * 查询数据源配置管理
     *
     * @param id 数据源配置管理ID
     * @return 数据源配置管理
     */
    @Override
    public DbDataResource selectDbDataResourceById(Long id) {
        return dbDataResourceMapper.selectDbDataResourceById(id);
    }

    /**
     * 查询数据源配置管理列表
     *
     * @param dbDataResource 数据源配置管理
     * @return 数据源配置管理
     */
    @Override
    public List<DbDataResource> selectDbDataResourceList(DbDataResource dbDataResource) {
        return dbDataResourceMapper.selectDbDataResourceList(dbDataResource);
    }

    /**
     * 新增数据源配置管理
     *
     * @param dbDataResource 数据源配置管理
     * @return 结果
     */
    @Override
    public int insertDbDataResource(DbDataResource dbDataResource) {
        LoginUser loginUser = SecurityUtils.getLoginUser();
       // dbDataResource.setCtCreateUser = loginUser.getUserName();
        int rows = dbDataResourceMapper.insertDbDataResource(dbDataResource);
        if (rows < 1) {
            throw new CustomException("新增失败，请联系管理员！");
        }

        return rows;
    }

    /**
     * 修改数据源配置管理
     *
     * @param dbDataResource 数据源配置管理
     * @return 结果
     */
    @Override
    public int updateDbDataResource(DbDataResource dbDataResource) {
        LoginUser loginUser = SecurityUtils.getLoginUser();
       // dbDataResource.setCtUpdateUser = loginUser.getUserName();
        int rows = dbDataResourceMapper.updateDbDataResource(dbDataResource);
        if (rows < 1) {
            throw new CustomException("修改失败，请联系管理员！");
        }
        return rows;
    }

    /**
     * 物理删批量删除数据源配置管理
     *
     * @param ids 需要删除的数据源配置管理ID
     * @return 结果
     */
    @Override
    public int deleteDbDataResourceByIds(Long[] ids) {
        int rows = dbDataResourceMapper.deleteDbDataResourceByIds(ids);
        if (rows < 1) {
            throw new CustomException("批量物理删除失败，请联系管理员！");
        }
        return rows;
    }

    /**
     * 物理删删除数据源配置管理信息
     *
     * @param id 数据源配置管理ID
     * @return 结果
     */
    @Override
    public int deleteDbDataResourceById(Long id) {
        int rows = dbDataResourceMapper.deleteDbDataResourceById(id);
        if (rows < 1) {
            throw new CustomException("物理删除失败，请联系管理员！");
        }
        return rows;
    }

    /**
     * 逻辑量删除数据源配置管理
     *
     * @param ids 需要删除的数据源配置管理ID
     * @return 结果
     */
    @Override
    public int deleteDbDataResourceremoveFasleByIds(Long[] ids) {
        int rows = dbDataResourceMapper.deleteDbDataResourceremoveFasleByIds(ids);
        if (rows < 1) {
            throw new CustomException("批量逻辑删除失败，请联系管理员！");
        }
        return rows;
    }

    /**
     * 逻辑删除数据源配置管理信息
     *
     * @param id 数据源配置管理ID
     * @return 结果
     */
    @Override
    public int deleteDbDataResourceremoveFasleById(Long id) {
        int rows = dbDataResourceMapper.deleteDbDataResourceremoveFasleById(id);
        if (rows < 1) {
            throw new CustomException("逻辑删除失败，请联系管理员！");
        }
        return rows;
    }


}

package com.admin.service.impl;


import com.admin.common.core.domain.AjaxResult;
import com.admin.common.exception.CustomException;
import com.admin.common.message.CustomSuccess;
import com.admin.mapper.SqlTableMapper;
import com.admin.service.SqlTableService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class SqlTableServiceImpl implements SqlTableService {

    @Autowired
    private SqlTableMapper sqlTableMapper;

    /***
     * 查询数据库表头信息、表头字段备注、长度。。。。
     * @param database
     * @param tablename
     * @return
     */
    @Override
    public List<Map<String, Object>> getTableColnums(String database, String tablename) {
        return sqlTableMapper.getTableColnums(database, tablename);
    }

    /***
     * 执行查询sql 返回数据集
     * 将客户端输入的所有大写 转换成小写
     * @param sql
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    @Override
    public AjaxResult runWithSql(String sql) {
        List<Map<String, Object>> mapList = new ArrayList<>();

        try {
            if (sql.replace(" ", "").substring(0, 10).length() < 10) {
                throw new CustomException("请输入完整的sql语句");
            }
        }catch (StringIndexOutOfBoundsException e){
            throw new CustomException("请输入完整的sql语句");
        }

        String sqlTop = sql.toLowerCase().replace(" ", "").substring(0, 10);
        String sqlLowerCase = sql.toLowerCase();

        if (sqlTop.contains("select")) {
            try {
                mapList.addAll(sqlTableMapper.runWithSql(sqlLowerCase));
            } catch (Exception e) {
                throw new CustomException("sql语句执行失败，请仔细检查sql!!!" + "==详细错误信息为：\n" + e.getMessage());
            }
        }

        //删除执行
        //1.删除数据库、或删除整张表语句限制
        if (sqlTop.contains("drop")) {
            throw new CustomException("小朋友，你想删库跑路，门都没有!!!");
        }

        if (sqlTop.contains("delete")) {
            //2.删除操作必须加条件限制（强制性，不然误操作会删数据库整张表信息的的）
            if (sqlLowerCase.contains("where") == false) {
                throw new CustomException("您在执行删除语句，并且您执行的语句中没有加删除过滤条件，会造成整表删除，请仔细检查您的sql语句!!!");
            }

            //3.删除操作where后面的 条件限制，eg: where 1 = 1 ,（强制性，不然误操作会删数据库整张表信息的的）
            if (sqlLowerCase.contains("where") == true) {
                //去除整句sql中的空格，做判断
                String replaceNewSql = sqlLowerCase.replace(" ", "");
                if (replaceNewSql.contains("1=1")) {
                    throw new CustomException("小朋友，不要调皮，你在试图删除整张表数据啊!!!");
                }
            }
            int i = sqlTableMapper.runWithSqlDelete(sqlLowerCase);
            if (i < 1) {
                throw new CustomException("删除数据失败!!!");
            }
            return AjaxResult.success("删除数据成功!!!");
        }

        //修改执行
        if (sqlTop.contains("update")) {
            if (sqlLowerCase.contains("where") == false) {
                throw new CustomException("您在执行修改语句，并且您执行的语句中没有加修改过滤条件，会造成整表修改，请仔细检查您的sql语句!!!");
            }
            int i = sqlTableMapper.runWithSqlUpdate(sqlLowerCase);
            if (i < 1) {
                throw new CustomException("修改数据失败!!!");
            }
            return AjaxResult.success("修改数据成功!!!");
        }
        return AjaxResult.success(mapList);
    }

    @Override
    public List<Map<String, Object>> getDataSourceList() {
        List<Map<String, Object>> list = sqlTableMapper.getDataSourceList();
        return list;
    }

    @Override
    public List<Map<String, Object>> getTableList(String database) {
        return sqlTableMapper.getTableList(database);
    }
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.admin.mapper.DbDataResourceMapper">

    <resultMap type="DbDataResource" id="DbDataResourceResult">
        <result property="id"    column="id"    />
        <result property="connname"    column="connName"    />
        <result property="conntype"    column="connType"    />
        <result property="conndriverclass"    column="connDriverClass"    />
        <result property="connip"    column="connIp"    />
        <result property="connport"    column="connPort"    />
        <result property="conndbname"    column="connDbName"    />
        <result property="connusername"    column="connUserName"    />
        <result property="connpassword"    column="connPassWord"    />
        <result property="connproperty"    column="connProperty"    />
        <result property="createdate"    column="createDate"    />
    </resultMap>

    <sql id="selectDbDataResourceVo">
        select id, connName, connType, connDriverClass, connIp, connPort, connDbName, connUserName, connPassWord, connProperty, createDate from db_data_resource
    </sql>

    <select id="selectDbDataResourceList" parameterType="DbDataResource" resultMap="DbDataResourceResult">
        <include refid="selectDbDataResourceVo"/>
        <where>
            <if test="connname != null  and connname != ''"> and connName like concat('%', #{connname}, '%')</if>
            <if test="conntype != null  and conntype != ''"> and connType = #{conntype}</if>
            <if test="conndriverclass != null  and conndriverclass != ''"> and connDriverClass = #{conndriverclass}</if>
            <if test="connip != null  and connip != ''"> and connIp = #{connip}</if>
            <if test="connport != null  and connport != ''"> and connPort = #{connport}</if>
            <if test="conndbname != null  and conndbname != ''"> and connDbName like concat('%', #{conndbname}, '%')</if>
            <if test="connusername != null  and connusername != ''"> and connUserName like concat('%', #{connusername}, '%')</if>
            <if test="connpassword != null  and connpassword != ''"> and connPassWord = #{connpassword}</if>
            <if test="connproperty != null  and connproperty != ''"> and connProperty = #{connproperty}</if>
            <if test="createdate != null "> and createDate = #{createdate}</if>
        </where>
    </select>

    <select id="selectDbDataResourceById" parameterType="Long" resultMap="DbDataResourceResult">
        <include refid="selectDbDataResourceVo"/>
        where id = #{id}
    </select>

    <insert id="insertDbDataResource" parameterType="DbDataResource" useGeneratedKeys="true" keyProperty="id">
        insert into db_data_resource
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="connname != null">connName,</if>
            <if test="conntype != null">connType,</if>
            <if test="conndriverclass != null">connDriverClass,</if>
            <if test="connip != null">connIp,</if>
            <if test="connport != null">connPort,</if>
            <if test="conndbname != null">connDbName,</if>
            <if test="connusername != null">connUserName,</if>
            <if test="connpassword != null">connPassWord,</if>
            <if test="connproperty != null">connProperty,</if>
            <if test="createdate != null">createDate,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="connname != null">#{connname},</if>
            <if test="conntype != null">#{conntype},</if>
            <if test="conndriverclass != null">#{conndriverclass},</if>
            <if test="connip != null">#{connip},</if>
            <if test="connport != null">#{connport},</if>
            <if test="conndbname != null">#{conndbname},</if>
            <if test="connusername != null">#{connusername},</if>
            <if test="connpassword != null">#{connpassword},</if>
            <if test="connproperty != null">#{connproperty},</if>
            <if test="createdate != null">#{createdate},</if>
         </trim>
    </insert>

    <update id="updateDbDataResource" parameterType="DbDataResource">
        update db_data_resource
        <trim prefix="SET" suffixOverrides=",">
            <if test="connname != null">connName = #{connname},</if>
            <if test="conntype != null">connType = #{conntype},</if>
            <if test="conndriverclass != null">connDriverClass = #{conndriverclass},</if>
            <if test="connip != null">connIp = #{connip},</if>
            <if test="connport != null">connPort = #{connport},</if>
            <if test="conndbname != null">connDbName = #{conndbname},</if>
            <if test="connusername != null">connUserName = #{connusername},</if>
            <if test="connpassword != null">connPassWord = #{connpassword},</if>
            <if test="connproperty != null">connProperty = #{connproperty},</if>
            <if test="createdate != null">createDate = #{createdate},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteDbDataResourceById" parameterType="Long">
        delete from db_data_resource where id = #{id}
    </delete>

    <delete id="deleteDbDataResourceByIds" parameterType="String">
        delete from db_data_resource where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>

前端

import request from '@/utils/request'


/**
 * 运行sql语句
 */
export function runWithSql(datasourceid,database,sql) {
  const data = {
    datasourceid,
    database,
    sql
  }

  return request({
    url: '/sql/client/runWithSql',
    method: 'get',
    params: data
  })
}

/**
 * 验证SQL
 * @param type
 * @param sql
 */
export function checkWithSql(type,sql){
  const data = {
    type,
    sql
  }

  return request({
    url: '/sql/client/checkWithSql',
    method: 'get',
    params: data
  })
}

/**
 * 获取数据库中的用户表
 */
export function getTableList(datasourceid,database) {
  const data = {
    datasourceid,
    database
  }

  return request({
    url: '/sql/client/getTableList',
    method: 'get',
    params: data
  })
}

/**
 * 获取表的字段信息
 */
export function getTableColnums(datasourceid,database,tablename) {
  const data = {
    datasourceid,
    database,
    tablename
  }

  return request({
    url: '/sql/client/getTableColnums',
    method: 'get',
    params: data
  })
}

/**
 * 获取数据源信息，加载至下拉列表
 */
export function getDataSource(){
  return request({
    url: '/sql/client/getdatasource',
    method: 'get'
  })
}

import request from '@/utils/request'

// 查询数据源配置管理列表
export function listDbresource(query) {
  return request({
    url: '/business/dbresource/list',
    method: 'get',
    params: query
  })
}

// 查询数据源配置管理详细
export function getDbresource(id) {
  return request({
    url: '/business/dbresource/' + id,
    method: 'get'
  })
}

// 新增数据源配置管理
export function addDbresource(data) {
  return request({
    url: '/business/dbresource',
    method: 'post',
    data: data
  })
}

// 修改数据源配置管理
export function updateDbresource(data) {
  return request({
    url: '/business/dbresource',
    method: 'put',
    data: data
  })
}

// 删除数据源配置管理
export function delDbresource(id) {
  return request({
    url: '/business/dbresource/' + id,
    method: 'delete'
  })
}

// 导出数据源配置管理
export function exportDbresource(query) {
  return request({
    url: '/business/dbresource/export',
    method: 'get',
    params: query
  })
}

<template>
  <el-container class="layout-container">
    <el-aside width="320px">
      <el-select v-model="dataSourceValue" placeholder="请选择数据源" style="width: 100%;" clearable
                 @change="selectDataSource">
        <el-option
          v-for="item in dataSourceList"
          :key="item.id"
          :label="item.connName"
          :value="{id:item.id,dbName:item.connDbName,dbType:item.connType}">
          <span style="float: left">{{ item.connName }}({{ item.connType}})</span>
          <span style="float: right; color: #8492a6; font-size: 13px">[{{ item.connDbName}}]</span>
        </el-option>
      </el-select>
      <el-table
        stripe
        :data="tableList"
        @row-dblclick="dbSelected"
        style="width: 100%;font-size:14px;">
        <el-table-column
          prop="name"
          label="表名">
        </el-table-column>
        <el-table-column
          prop="VALUE"
          label="描述">
        </el-table-column>
      </el-table>
    </el-aside>
    <el-main>
      <p>请输入需要执行的SQL语句（1.不选择数据源时，默认数据库为项目主数据库。 2.执行语句中按需添加分页参数）</p>
      <el-input type="textarea" v-model="formData.queryText"></el-input>

      <el-row>
        <el-col :span="24" style="background-color: #11A983;">

          <el-button-group>
            <el-tooltip popper-class="tps" class="item" effect="dark" content="运行" placement="top-end">
              <el-button type="danger" class="toolBtnClass" @click="cmdRun"> 运行
                <svg-icon icon-class="run" class="svgClass"/>
              </el-button>
            </el-tooltip>
            <!--<el-tooltip class="item" effect="dark" content="检查" placement="top-end">
            <el-button type="primary"  class="toolBtnClass" @click="cmdCheck"> 检查
              <svg-icon icon-class="check" class="svgClass" />
          </el-button>
           </el-tooltip>
              <el-tooltip class="item" effect="dark" content="格式化" placement="top-end">
            <el-button type="primary"  class="toolBtnClass" @click="cmdFormat">格式化
              <svg-icon icon-class="format" class="svgClass" />
            </el-button>
              </el-tooltip>
                <el-tooltip class="item" effect="dark" content="清除" placement="top-end">
            <el-button type="primary"  class="toolBtnClass" @click="cmdClear">清除
              <svg-icon icon-class="clear" class="svgClass" />
            </el-button>
                </el-tooltip>-->
          </el-button-group>

        </el-col>
      </el-row>

      <!--      <codemirror

              ref="myCm"
              v-model="formData.queryText"
              :options="cmOptions"
              className = "code-mirror"
              @ready="onCmReady"
              @focus="onCmFocus"
              @input="onCmCodeChange" style="font-size: 14px;">
            </codemirror>-->
      <el-table :data="tableData" style="margin-top:0;" size="mini" border stripe>
        <el-table-column
          v-for="col in cols"
          :prop="col.prop" :label="col.label" :show-overflow-tooltip='true' width='100'>
        </el-table-column>
      </el-table>
    </el-main>
  </el-container>
</template>

<script>
  import sqlFormatter from 'sql-formatter'
  import { getDataSource, getTableList, getTableColnums, runWithSql, checkWithSql } from '@/api/execsql'

  export default {
    name: 'design',
    data() {

      return {
        tableData: [],
        formData: {
          queryText: ''
        },
        cmOptions: {
          tabSize: 2,
          // 高亮选中行
          styleActiveLine: true,
          theme: '3024-night',
          lineNumbers: true,
          line: true,
          //编辑文本模式 ：  text/x-mysql    sql
          mode: 'sql',
          // 自动缩进
          smartIndent: true,
          // 在选择时是否显示光标
          showCursorWhenSelecting: true,
          // scrollbarStyle: 'overlay',
          // 自动括号匹配功能
          matchBrackets: true,
          viewportMargin: 1,
          lineWrapping: true,
          autofocus: true,
          autocorrect: true,
          spellcheck: true
        },
        dataSourceList: [{
          id: 1,
          connType: 'Mysql',
          connName: '测试地址',
          connDbName: ''
        }],
        dataSourceValue: '',
        tableList: [],
        TableColnums: [],
        cols: []
      }
    },
    computed: {
      codemirror() {
      }
    },
    mounted() {
      //获取数据源信息
      this.getSourceList()
    },
    methods: {
      dbSelected(row) {
        getTableColnums(this.dataSourceValue.id, this.dataSourceValue.dbName, row.name).then(response => {
          this.TableColnums = response.data
          //拼接查询语句
          let sqlText = 'select '
          for (let i = 0; i < this.TableColnums.length; i++) {
            sqlText += this.TableColnums[i].ColumnName + ','
          }
          sqlText = sqlText.slice(0, sqlText.length - 1)
          sqlText += ' from ' + row.name + ' limit 100 '
          this.formData.queryText = sqlText
          this.tableData = []
          this.cols = []
          this.loading = false
        })
      },
      getSourceList() {
        this.loading = true
        getDataSource().then(response => {
          console.log(response.data)
          this.dataSourceList = response.data
          this.loading = false
        })

      },
      selectDataSource(value) {
        this.loading = true
        this.dataSourceValue = value
        console.log('value.dbType:' + value.dbType)
        getTableList(value.id, value.dbName).then(response => {
          this.tableList = response.data
          //数据源表为空，清空待执行的SQL语句
          if (this.tableList.length === 0) {
            this.formData.queryText = ''
            this.cols = []
            this.tableData = []
          }
          this.loading = false
        })
      },
      showSQL(val) {
        this.formData.queryText = val ? val : ''
      },
      onCmReady(cm) {
      },
      onCmFocus(cm) {
      },
      onCmCodeChange(newCode) {
        this.formData.queryText = newCode
        this.$emit('codeChange', this.formData.queryText)
      },
      cmdFormat() {
        if (this.formData.queryText.trim().length != 0) {
          this.formData.queryText = (sqlFormatter.format(this.formData.queryText))
        }
      },
      cmdClear() {
        this.formData.queryText = ''
      },
      cmdRun() {
        // 执行SQL语句之前的验证
        if (this.formData.queryText.trim().length === 0) {
          this.$message.error('SQL语句不能为空！')
          return
        }

        /*if(this.tableList.length===0){
          this.$message.error('数据源为空，不能执行！');
          return
        }*/

        this.loading = true
        runWithSql(this.dataSourceValue.id, this.dataSourceValue.dbName, this.formData.queryText).then(response => {
          this.tableData = response.data

          var sqlText = this.formData.queryText.toLowerCase()
          var regDelete = RegExp('delete')
          var isDelete = regDelete.test(sqlText)

          var regUpdate = RegExp('update')
          var isUpdate = regUpdate.test(sqlText)

          //如果是删除、或者修改操作，接口提示直接从后台取
          if (response.code == 200) {
            if (isDelete == true) {
              this.$message.success(response.msg)
              return
            }

            if (isUpdate == true) {
              this.$message.success(response.msg)
              return
            }

            this.$message.success('SQL语句执行成功！')
          }

          //清空表格表头值
          this.cols = []
          if (this.tableData.length > 0) {
            let colofzore = this.tableData[0]
            for (var key in colofzore) {
              let tableCol = { prop: key, label: key }
              this.cols.push(tableCol)
            }
          }
          this.loading = false
        })
      },
      cmdCheck() {
        // 校验SQL语句之前的验证
        if (this.formData.queryText.trim().length === 0) {
          this.$message.error('SQL语句不能为空！')
          return
        }

        if (this.tableList.length === 0) {
          this.$message.error('数据源为空，不能执行！')
          return
        }

        this.loading = true
        checkWithSql(this.dataSourceValue.dbType, this.formData.queryText).then(response => {
          console.log(response.data)
          this.loading = false
        })
      }
    }
  }
</script>

<style scoped>

  .toolBtnClass {
    width: 60px;
    height: 40px;
    margin: 0;
    color: #ff4949;
    background: rgba(97, 79, 255, 0);
    padding-left: 0;
  }

  .el-container {
    height: 800px;
  }

  .atooltip .el-tooltip__popper[x-placement^="top"] .popper__arrow {
    border-top-color: pink;
  }

  .atooltip .el-tooltip__popper[x-placement^="top"] .popper__arrow:after {
    border-top-color: pink;
  }

  .atooltip {
    background: pink !important;
  }

  .layout-container {
    height: 915px;
  }

  .el-main {
    color: #333;
    margin: 0;
    padding: 0;
  }

  .el-aside {
    color: #333;
  }

  .code-mirror {
    font-size: 30px;
    line-height: 150%;
  }


  .svgClass {
    height: 20px;
    width: 20px;
    margin-left: 10px;
  }

  .CodeMirror {
    font-size: 20px;
    border: 1px solid #eee;
    height: auto;
  }


  {
    background: red
  ;
  }
</style>

<template>
  <div class="app-container">
    <el-form :model="queryParams" ref="queryForm" :inline="true" v-show="showSearch" label-width="68px">
      <el-form-item label="连接名称" prop="connname">
        <el-input
          v-model="queryParams.connname"
          placeholder="请输入连接名称"
          clearable
          size="small"
          @keyup.enter.native="handleQuery"
        />
      </el-form-item>
      <el-form-item label="数据库" prop="conntype">
        <el-input
          v-model="queryParams.conntype"
          placeholder="请选择数据库类型"
          clearable
          size="small"
          @keyup.enter.native="handleQuery"
        />
      </el-form-item>
      <el-form-item label="驱动包名" prop="conndriverclass">
        <el-input
          v-model="queryParams.conndriverclass"
          placeholder="请输入驱动包名"
          clearable
          size="small"
          @keyup.enter.native="handleQuery"
        />
      </el-form-item>

      <el-form-item label="端口号" prop="connport">
        <el-input
          v-model="queryParams.connport"
          placeholder="请输入端口号"
          clearable
          size="small"
          @keyup.enter.native="handleQuery"
        />
      </el-form-item>
      <el-form-item label="数据库名" prop="conndbname">
        <el-input
          v-model="queryParams.conndbname"
          placeholder="请输入数据库名"
          clearable
          size="small"
          @keyup.enter.native="handleQuery"
        />
      </el-form-item>

      <el-form-item>
        <el-button type="primary" icon="el-icon-search" size="mini" @click="handleQuery">搜索</el-button>
        <el-button icon="el-icon-refresh" size="mini" @click="resetQuery">重置</el-button>
      </el-form-item>
    </el-form>

    <el-row :gutter="10" class="mb8">
      <el-col :span="1.5">
        <el-button
          type="primary"
          plain
          icon="el-icon-plus"
          size="mini"
          @click="handleAdd"
          v-hasPermi="['business:dbresource:add']"
        >新增
        </el-button>
      </el-col>
      <el-col :span="1.5">
        <el-button
          type="success"
          plain
          icon="el-icon-edit"
          size="mini"
          :disabled="single"
          @click="handleUpdate"
          v-hasPermi="['business:dbresource:edit']"
        >修改
        </el-button>
      </el-col>
      <el-col :span="1.5">
        <el-button
          type="danger"
          plain
          icon="el-icon-delete"
          size="mini"
          :disabled="multiple"
          @click="handleDelete"
          v-hasPermi="['business:dbresource:remove']"
        >删除
        </el-button>
      </el-col>
      <el-col :span="1.5">
        <el-button
          type="warning"
          plain
          icon="el-icon-download"
          size="mini"
          @click="handleExport"
          v-hasPermi="['business:dbresource:export']"
        >导出
        </el-button>
      </el-col>
      <right-toolbar :showSearch.sync="showSearch" @queryTable="getList"></right-toolbar>
    </el-row>

    <el-table v-loading="loading" :data="dbresourceList" @selection-change="handleSelectionChange">
      <el-table-column type="selection" width="55" align="center"/>
      <el-table-column label="序号" align="center" prop="id"/>
      <el-table-column label="连接名称" align="center" prop="connname"/>
      <el-table-column label="数据库类型" align="center" prop="conntype"/>
      <el-table-column label="驱动包名" align="center" prop="conndriverclass"/>
      <el-table-column label="IP地址" align="center" prop="connip"/>
      <el-table-column label="端口号" align="center" prop="connport"/>
      <el-table-column label="数据库名" align="center" prop="conndbname"/>
      <el-table-column label="用户名" align="center" prop="connusername"/>
      <el-table-column label="密码" align="center" prop="connpassword"/>
      <el-table-column label="附加参数" align="center" prop="connproperty"/>
      <el-table-column label="创建日期" align="center" prop="createdate" width="180">
        <template slot-scope="scope">
          <span>{{ parseTime(scope.row.createdate, '{y}-{m}-{d}') }}</span>
        </template>
      </el-table-column>
      <el-table-column label="操作" align="center" class-name="small-padding fixed-width">
        <template slot-scope="scope">
          <el-button
            size="mini"
            type="text"
            icon="el-icon-edit"
            @click="handleUpdate(scope.row)"
            v-hasPermi="['business:dbresource:edit']"
          >修改
          </el-button>
          <el-button
            size="mini"
            type="text"
            icon="el-icon-delete"
            @click="handleDelete(scope.row)"
            v-hasPermi="['business:dbresource:remove']"
          >删除
          </el-button>
        </template>
      </el-table-column>
    </el-table>

    <pagination
      v-show="total>0"
      :total="total"
      :page.sync="queryParams.pageNum"
      :limit.sync="queryParams.pageSize"
      @pagination="getList"
    />

    <!-- 添加或修改数据源配置管理对话框 -->
    <el-dialog :title="title" :visible.sync="open" width="500px" append-to-body>
      <el-form ref="form" :model="form" :rules="rules" label-width="80px">
        <el-form-item label="连接名称" prop="connname">
          <el-input v-model="form.connname" placeholder="请输入连接名称"/>
        </el-form-item>
        <el-form-item label="数据库类型" prop="conntype">
          <el-select v-model="form.conntype" placeholder="请选择数据库类型">
            <el-option label="请选择字典生成" value=""/>
          </el-select>
        </el-form-item>
        <el-form-item label="驱动包名" prop="conndriverclass">
          <el-input v-model="form.conndriverclass" placeholder="请输入驱动包名"/>
        </el-form-item>
        <el-form-item label="IP地址" prop="connip">
          <el-input v-model="form.connip" placeholder="请输入IP地址"/>
        </el-form-item>
        <el-form-item label="端口号" prop="connport">
          <el-input v-model="form.connport" placeholder="请输入端口号"/>
        </el-form-item>
        <el-form-item label="数据库名" prop="conndbname">
          <el-input v-model="form.conndbname" placeholder="请输入数据库名"/>
        </el-form-item>
        <el-form-item label="用户名" prop="connusername">
          <el-input v-model="form.connusername" placeholder="请输入用户名"/>
        </el-form-item>
        <el-form-item label="密码" prop="connpassword">
          <el-input v-model="form.connpassword" placeholder="请输入密码"/>
        </el-form-item>
        <el-form-item label="附加参数" prop="connproperty">
          <el-input v-model="form.connproperty" placeholder="请输入附加参数"/>
        </el-form-item>
        <el-form-item label="创建日期" prop="createdate">
          <el-date-picker clearable size="small"
                          v-model="form.createdate"
                          type="date"
                          value-format="yyyy-MM-dd"
                          placeholder="选择创建日期">
          </el-date-picker>
        </el-form-item>
      </el-form>
      <div slot="footer" class="dialog-footer">
        <el-button type="primary" @click="submitForm">确 定</el-button>
        <el-button @click="cancel">取 消</el-button>
      </div>
    </el-dialog>
  </div>
</template>

<script>
  import {
    listDbresource,
    getDbresource,
    delDbresource,
    addDbresource,
    updateDbresource,
    exportDbresource
  } from '@/api/dbresource'

  export default {
    name: 'Dbresource',
    components: {},
    data() {
      return {
        // 遮罩层
        loading: true,
        // 选中数组
        ids: [],
        // 非单个禁用
        single: true,
        // 非多个禁用
        multiple: true,
        // 显示搜索条件
        showSearch: true,
        // 总条数
        total: 0,
        // 数据源配置管理表格数据
        dbresourceList: [],
        // 弹出层标题
        title: '',
        // 是否显示弹出层
        open: false,
        // 查询参数
        queryParams: {
          pageNum: 1,
          pageSize: 10,
          connname: null,
          conntype: null,
          conndriverclass: null,
          connip: null,
          connport: null,
          conndbname: null,
          connusername: null,
          connpassword: null,
          connproperty: null,
          createdate: null
        },
        // 表单参数
        form: {},
        // 表单校验
        rules: {}
      }
    },
    created() {
      this.getList()
    },
    methods: {
      /** 查询数据源配置管理列表 */
      getList() {
        this.loading = true
        listDbresource(this.queryParams).then(response => {
          this.dbresourceList = response.rows
          this.total = response.total
          this.loading = false
        })
      },
      // 取消按钮
      cancel() {
        this.open = false
        this.reset()
      },
      // 表单重置
      reset() {
        this.form = {
          id: null,
          connname: null,
          conntype: null,
          conndriverclass: null,
          connip: null,
          connport: null,
          conndbname: null,
          connusername: null,
          connpassword: null,
          connproperty: null,
          createdate: null
        }
        this.resetForm('form')
      },
      /** 搜索按钮操作 */
      handleQuery() {
        this.queryParams.pageNum = 1
        this.getList()
      },
      /** 重置按钮操作 */
      resetQuery() {
        this.resetForm('queryForm')
        this.handleQuery()
      },
      // 多选框选中数据
      handleSelectionChange(selection) {
        this.ids = selection.map(item => item.id)
        this.single = selection.length !== 1
        this.multiple = !selection.length
      },
      /** 新增按钮操作 */
      handleAdd() {
        this.reset()
        this.open = true
        this.title = '添加数据源配置管理'
      },
      /** 修改按钮操作 */
      handleUpdate(row) {
        this.reset()
        const id = row.id || this.ids
        getDbresource(id).then(response => {
          this.form = response.data
          this.open = true
          this.title = '修改数据源配置管理'
        })
      },
      /** 提交按钮 */
      submitForm() {
        this.$refs['form'].validate(valid => {
          if (valid) {
            if (this.form.id != null) {
              updateDbresource(this.form).then(response => {
                this.msgSuccess('修改成功')
                this.open = false
                this.getList()
              })
            } else {
              addDbresource(this.form).then(response => {
                this.msgSuccess('新增成功')
                this.open = false
                this.getList()
              })
            }
          }
        })
      },
      /** 删除按钮操作 */
      handleDelete(row) {
        const ids = row.id || this.ids
        this.$confirm('是否确认删除数据源配置管理编号为"' + ids + '"的数据项?', '警告', {
          confirmButtonText: '确定',
          cancelButtonText: '取消',
          type: 'warning'
        }).then(function() {
          return delDbresource(ids)
        }).then(() => {
          this.getList()
          this.msgSuccess('删除成功')
        })
      },
      /** 导出按钮操作 */
      handleExport() {
        const queryParams = this.queryParams
        this.$confirm('是否确认导出所有数据源配置管理数据项?', '警告', {
          confirmButtonText: '确定',
          cancelButtonText: '取消',
          type: 'warning'
        }).then(function() {
          return exportDbresource(queryParams)
        }).then(response => {
          this.download(response.msg)
        })
      }
    }
  }
</script>

效果图