一、简介
根据配置的数据源，可以查询该数据源下所有的数据库、表、以及字段说明。
二、前端代码
<!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>css代码
table, td, th {
border: 1px solid black;
}
td {
padding: 15px;
}三、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>四、nginx 配置
此项目采取前后端分离
#user nobody;
worker_processes 1;
#pid logs/nginx.pid;
events {
worker_connections 1024;
}
http {
include mime.types;
default_type application/octet-stream;
sendfile on;
keepalive_timeout 65;
server {
listen 80;
server_name localhost;
location / {
proxy_pass http://localhost:8080;
}
#静态文件交给nginx处理
location ~ .*.(htm|html|gif|jpg|jpeg|png|bmp|swf|ioc|rar|zip|txt|flv|mid|doc|ppt|pdf|xls|mp3|wma)$
{
root D:/workspace_idea/spring-mvc/src/main/webapp/;
expires 30d;
}
#静态文件交给nginx处理
location ~ .*.(js|css)?$
{
root D:/workspace_idea/spring-mvc/src/main/webapp/;
expires 1h;
}
error_page 500 502 503 504 /50x.html;
location = /50x.html {
root html;
}
}
}