基于SSM的数据库表字段查询应用

2023-09-28 09:16:46

一、简介

根据配置的数据源，可以查询该数据源下所有的数据库、表、以及字段说明。

二、前端代码

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
        "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
    <meta charset="UTF-8">
    <title>表结构</title>
    <link href="css/table.css" rel="stylesheet" type="text/css"/>
</head>
<body>
<div align="center">

    <div>
        请选择数据库：
        <select id="database">
            <option value="volvo">Volvo</option>
            <option value="saab">Saab</option>
            <option value="opel">Opel</option>
            <option value="audi">Audi</option>
        </select>

        请选择表：
        <select id="table">
            <option value="volvo">Volvo</option>
            <option value="saab">Saab</option>
            <option value="opel">Opel</option>
            <option value="audi">Audi</option>
        </select>
        <button type="button" id="qryBtn">查询</button>

    </div>
</div>
<div>
    <table align="center" id="tblId">
        <thead>
        <tr id="tblHeadId">
            <th>字段编码</th>
            <th>字段名称</th>
            <th>字段类型</th>
            <th>允许为空</th>
            <th>索引类型</th>
            <th>编码</th>
            <th>注释</th>
        </tr>
        </thead>
        <tbody id="tblBodyId">

        </tbody>


    </table>
</div>
</body>
<!-- 引入本地文件 -->
<script src="jquery/jquery-3.5.1.js" type="text/javascript"></script>
<script type="text/javascript">
    /**
     * jquery的就绪事件
     */
    $(document).ready(function () {
        init();
    });

    $("#qryBtn").click(function () {
        var databaseName=$("#database").val();
        var tableName=$("#table").val();
        var param = {
            "databaseName": databaseName,
            "tableName":tableName
        };
        $.ajax({
            type: "post",
            url: "springmvc/columns",
            dataType: "json",
            contentType: "application/json;charset=UTF-8",
            data: JSON.stringify(param),
            success: function (data) {
                var tableObj = $('#tblBodyId');
                tableObj.html("");
                for (var i = 0; i < data.length; i++) {
                    var item = data[i];
                    var columnStr = '<tr> ' +
                        '<td >' + item.fieldNo + '</td>' +
                        '<td >' + item.fieldName + '</td>' +
                        '<td >' + item.fieldType + '</td>' +
                        '<td >' + item.isEmpty + '</td>' +
                        '<td >' + item.indexType + '</td>' +
                        '<td >' + item.encode + '</td>' +
                        '<td >' + item.comments + '</td>' +
                        '</tr>';
                    tableObj.append(columnStr)
                }
            },
            error: function (errordata) {
                console.log("Error: " + errordata);
            }
        });
    });

    $("#database").change(function (event) {
        var databaseName = $("#database").val();
        queryTables(databaseName);
    });

    /**
     * 初始化所有的库
     */
    function init() {
        var databaseSelectObj = $("#database");
        databaseSelectObj.html("");
        $.ajax({
            type: "GET",
            url: "springmvc/databases",
            dataType: "json",
            success: function (data) {
                if (data.length == 0) {
                    return;
                }
                var databaseObj = $('#database');
                databaseObj.html("");
                var defaultDatabase = data[0];
                for (var i = 0; i < data.length; i++) {
                    var optionObj = ' <option value=' + data[i] + '>' + data[i] + '</option>';
                    databaseObj.append(optionObj)
                }
                queryTables(defaultDatabase);

            },
            error: function (errordata) {
                console.log("Error: " + errordata);
            }
        });
    }

    /**
     * 数据库名称查询所有的表
     * @param databaseName
     */
    function queryTables(databaseName) {
        var tableSelectObj = $("#table");
        tableSelectObj.html("");
        var param = {"databaseName": databaseName};
        $.ajax({
            type: "post",
            url: "springmvc/tables",
            dataType: "json",
            contentType: "application/json;charset=UTF-8",
            data: JSON.stringify(param),
            success: function (data) {
                if (data.length == 0) {
                    return;
                }
                var tableObj = $('#table');
                tableObj.html("");
                for (var i = 0; i < data.length; i++) {
                    var optionObj = ' <option value=' + data[i] + '>' + data[i] + '</option>';
                    tableObj.append(optionObj)
                }

            },
            error: function (errordata) {
                console.log("Error: " + errordata);
            }
        });
    }

</script>
</html>

三、java代码

3.1、controller层代码

package com.haojie.controller;

import com.haojie.beans.Column;
import com.haojie.mapper.TPermssionMapper;
import com.haojie.service.inf.TableService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

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

@RestController
public class TableController {

    @Autowired
    private TableService tableService;

    @Autowired
    private TPermssionMapper tPermssionMapper;

    @RequestMapping("/columns")
    public List<Column> qryColumns(@RequestBody Map<String,String> param){
        String databaseName=param.get("databaseName");
        String tableName=param.get("tableName");
        return tableService.queryColumns(databaseName,tableName);
    }

    @RequestMapping("/databases")
    public List<String> databases(){
        return tableService.queryDatabases();
    }

    @RequestMapping("/tables")
    public List<String> tables(@RequestBody Map<String,String> param){
        System.out.println(param);
        List<String> databaseName = tableService.queryTables(param.get("databaseName"));

        return databaseName;
    }

}

3.2、Service层代码

package com.haojie.service.impl;

import com.haojie.beans.Column;
import com.haojie.mapper.TableMapper;
import com.haojie.service.inf.TableService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class TableServiceImpl implements TableService {

    @Autowired
    private TableMapper tableMapper;
    @Override
    public List<Column> queryColumns(String databaseName,String tableName) {
        return tableMapper.queryColumns(databaseName,tableName);
    }

    @Override
    public List<String> queryDatabases() {
        return tableMapper.queryDatabases();
    }

    @Override
    public List<String> queryTables(String databaseName) {
        return tableMapper.queryTables(databaseName);
    }
}

package com.haojie.service.inf;

import com.haojie.beans.Column;

import java.util.List;

public interface TableService {

    List<Column> queryColumns(String databaseName,String tableName);

    List<String> queryDatabases();

    List<String> queryTables(String databaseName);

}

3.3、Dao层代码

package com.haojie.mapper;

import com.haojie.beans.Column;
import com.haojie.beans.TUserRole;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface TableMapper {
    List<Column> queryColumns(@Param("databaseName") String databaseName,@Param("tableName") String tableName);

    List<String> queryDatabases();

    List<String> queryTables(String databaseName);

}

<?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.haojie.mapper.TableMapper">
    <select id="queryColumns" resultType="com.haojie.beans.Column">
        SELECT
                C.ORDINAL_POSITION AS 'fieldNo',
                C.COLUMN_NAME AS 'fieldName',
                C.COLUMN_TYPE AS 'fieldType',
                C.IS_NULLABLE AS 'isEmpty',
                C.COLUMN_KEY AS 'indexType',
                C.CHARACTER_SET_NAME AS 'encode',
                C.COLUMN_COMMENT AS 'comments'
        FROM
                information_schema.COLUMNS C
                INNER JOIN information_schema.TABLES T ON C.TABLE_SCHEMA = T.TABLE_SCHEMA
                AND C.TABLE_NAME = T.TABLE_NAME
        WHERE
            T.TABLE_SCHEMA = #{databaseName}
          and C.TABLE_NAME=#{tableName}
    </select>

    <select id="queryDatabases" resultType="java.lang.String">
        show databases
    </select>

    <select id="queryTables" resultType="java.lang.String">
        select table_name from information_schema.TABLES where table_schema=#{0}
    </select>


</mapper>

码农公寓

一、简介

二、前端代码

三、java代码

3.1、controller层代码

3.2、Service层代码

3.3、Dao层代码

相关文章