当前位置：首页 > news >正文

MyBatisPlus查询数据库中所有表的数据（AI）

news 2025/8/7 12:17:54

简介

提供一个完整的解决方案，使用MyBatisPlus查询数据库中所有表的数据，并通过Vue前端展示。

代码

DatabaseExplorerApplication.java


@SpringBootApplication
public class DatabaseExplorerApplication {public static void main(String[] args) {SpringApplication.run(DatabaseExplorerApplication.class, args);}
}

TableInfoController.java

@RestController
@RequestMapping("/api/tables")
public class TableInfoController {@Autowiredprivate DataSource dataSource;@GetMapping("/list")public List<String> listTables() throws SQLException {List<String> tables = new ArrayList<>();try (Connection connection = dataSource.getConnection()) {DatabaseMetaData metaData = connection.getMetaData();ResultSet rs = metaData.getTables(null, null, "%", new String[]{"TABLE"});while (rs.next()) {tables.add(rs.getString("TABLE_NAME"));}}return tables;}@GetMapping("/data/{tableName}")public Map<String, Object> getTableData(@PathVariable String tableName,@RequestParam(defaultValue = "1") int page,@RequestParam(defaultValue = "10") int size) {Page<Map<String, Object>> pageInfo = new Page<>(page, size);SqlRunner sqlRunner = SqlRunner.db();String countSql = "SELECT COUNT(*) FROM " + tableName;String dataSql = "SELECT * FROM " + tableName + " LIMIT " + pageInfo.getSize() + " OFFSET " + (pageInfo.getCurrent() - 1) * pageInfo.getSize();Map<String, Object> result = new HashMap<>();result.put("total", sqlRunner.selectOne(countSql));result.put("records", sqlRunner.selectList(dataSql));return result;}
}

DatabaseExplorer.vue

<template><div class="container mx-auto p-4"><h1 class="text-2xl font-bold mb-4">数据库浏览器</h1><div class="flex gap-4"><div class="w-1/4 bg-gray-100 p-4 rounded"><h2 class="text-lg font-semibold mb-2">表列表</h2><ul class="space-y-1"><li v-for="table in tables" :key="table" @click="selectTable(table)"class="p-2 hover:bg-blue-100 cursor-pointer rounded":class="{ 'bg-blue-200': selectedTable === table }">{{ table }}</li></ul></div><div class="w-3/4"><div v-if="selectedTable" class="bg-white p-4 rounded shadow"><h2 class="text-lg font-semibold mb-4">{{ selectedTable }} 数据</h2><div class="overflow-x-auto"><table class="min-w-full border"><thead><tr class="bg-gray-50"><th v-for="column in columns" :key="column" class="px-4 py-2 text-left border-b">{{ column }}</th></tr></thead><tbody><tr v-for="(row, index) in tableData" :key="index"><td v-for="(value, key) in row" :key="key" class="px-4 py-2 border-b">{{ value }}</td></tr></tbody></table></div><div class="mt-4 flex justify-between items-center"><div>共 {{ total }} 条记录</div><div class="flex gap-2"><button @click="prevPage" :disabled="currentPage === 1"class="px-3 py-1 bg-gray-200 rounded disabled:opacity-50">上一页</button><span>第 {{ currentPage }} 页</span><button @click="nextPage":disabled="currentPage * pageSize >= total"class="px-3 py-1 bg-gray-200 rounded disabled:opacity-50">下一页</button></div></div></div></div></div></div>
</template><script>
import axios from 'axios';export default {data() {return {tables: [],selectedTable: null,tableData: [],columns: [],currentPage: 1,pageSize: 10,total: 0};},async created() {await this.fetchTables();},methods: {async fetchTables() {const response = await axios.get('/api/tables/list');this.tables = response.data;},async selectTable(table) {this.selectedTable = table;this.currentPage = 1;await this.fetchTableData();},async fetchTableData() {const response = await axios.get(`/api/tables/data/${this.selectedTable}`, {params: {page: this.currentPage,size: this.pageSize}});this.tableData = response.data.records;this.total = response.data.total;if (this.tableData.length > 0) {this.columns = Object.keys(this.tableData[0]);}},prevPage() {if (this.currentPage > 1) {this.currentPage--;this.fetchTableData();}},nextPage() {if (this.currentPage * this.pageSize < this.total) {this.currentPage++;this.fetchTableData();}}}
};
</script>