获取数据库所有表与表字段

获取数据库表和表字段信息

SQL语句：

MySQL

获取所有数据库的所有表信息

select * from information_schema.TABLES where TABLE_SCHEMA=(select database())

获取某个表的所有字段信息

select * from information_schema.COLUMNS where TABLE_SCHEMA = (select database()) and TABLE_NAME='表名'

SQL Server

获取所有数据库的所有表信息

select * from information_schema.TABLES

获取某个表的所有字段信息

select * from information_schema.COLUMNS where  TABLE_NAME='仓库表'

其他数据库

SQL : select * from information_schema.tables

ORACLE: select table_name from user_tables

ACCESS: select name from MSysObjects where type=1 and flags=0

mybatis集成(MySQL数据库)

1.导入依赖

<!--thymeleaf-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<!--web-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--mybatis-->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.3</version>
</dependency>
<!--sql server-->
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <scope>compile</scope>
</dependency>
<!--mysql-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>compile</scope>
</dependency>

2.连接数据库

#sql server
spring.datasource.url=jdbc:sqlserver://localhost:1433;DatabaseName=数据库
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.username=sa
spring.datasource.password=*******

spring:
  datasource:
    username: root
    password: ******
    url: jdbc:mysql://127.0.0.1:3306/数据库名?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver #complie

3.编写mapper接口

@Mapper
public interface TableDao {
	/**
	* 获取所有表信息
	**/
    @Select("select * from information_schema.TABLES where TABLE_SCHEMA=(select database())")
    List<Map> listTable();
	/**
	* 获取表字段信息
	**/
    @Select("select * from information_schema.COLUMNS where TABLE_SCHEMA = (select database()) and TABLE_NAME=#{tableName}")
    List<Map> listTableColumn(String tableName);
    /**
	* 获取表中所有数据
	**/
	@Select("select * from ${tableName}")
	List<Map<String,String>> listTableValue(String tableName);
}

4.开始使用

@Controller
public class IndexController {
    @Autowired
    private TableMapper tableMapper;

    @RequestMapping("/tables")
    @ResponseBody
    public List<String> getList(){
        return tableMapper.getTableList();
    }
    @RequestMapping("/columns")
    @ResponseBody
    public List<String> info(String tableName) {
        List<String> list = tableMapper.listTableColumn(tableName);
        return list.subList(1,list.size()-2);
    }
    @RequestMapping("/values")
    @ResponseBody
    public List<Map<String,String>> values(String tableName){
        return tableMapper.listTableValue(tableName);
    }
}

5.运行结果

参考

https://blog.csdn.net/asd8510678/article/details/80305716
https://blog.csdn.net/weixin_34414650/article/details/85833144