获取代码获取数据库表,字段信息-两种方式

最新推荐文章于 2023-05-26 10:07:27 发布

不学会Ⅳ

最新推荐文章于 2023-05-26 10:07:27 发布

阅读量695

点赞数

分类专栏： mybatis mysql 文章标签：数据库 java 开发语言

本文链接：https://blog.csdn.net/qq_35040959/article/details/126729196

版权

mybatis 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

mysql

4 篇文章 0 订阅

订阅专栏

业务需求找了一下代码里获取数据库表和表字段信息的方式

一、sql查询

查询information_schema表可以获取到整个数据库的信息

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/sql-test")
    public void t() {
        String tableName = "combo_card";//表名
        //一、获取表名
        SqlRowSet sqlRowSet = jdbcTemplate.queryForRowSet("select table_name tableName, engine, table_comment tableComment, create_time createTime " +
                "from information_schema.tables where table_schema = "+tableName);
        while(sqlRowSet.next()){
            System.out.println(sqlRowSet.getString("tableName"));
        }
        //一、获取列数据
        SqlRowSet sqlRowSet1 = jdbcTemplate.queryForRowSet("select * from information_schema.columns " +
                "where table_schema = '库名' AND table_name = '" + tableName + "'");
        while (sqlRowSet1.next()) {
            System.out.println(sqlRowSet);
        }
	}

二、JDBC元数据

从连接的元数据中去获取

 @Autowired
    private JdbcTemplate jdbcTemplate;

    @GetMapping("/sql-test")
    public void t() {
        String tableName = "combo_card";//表名
        List<IntrospectedColumn> columns=new LinkedList<>();
        Object execute = jdbcTemplate.execute(new ConnectionCallback<Object>() {
            @Override
            public Object doInConnection(Connection con) throws SQLException, DataAccessException {
                DatabaseMetaData metaData = con.getMetaData();
                ResultSet schemas = metaData.getSchemas();
                String[] types = {"TABLE"};
                //二、获取表名
                ResultSet tables = metaData.getTables(null, null, null, types);
                while(tables.next()){
                    System.out.println(tables.getString("TABLE_NAME"));
                }
                //二、获取列数据
                ResultSet rs = metaData.getColumns(null, null, tableName, "%");
                while (rs.next()) {
                    IntrospectedColumn introspectedColumn = new IntrospectedColumn();
                    introspectedColumn.setTableAlias(tableName);
                    introspectedColumn.setJdbcType(rs.getInt("DATA_TYPE"));
                    introspectedColumn.setLength(rs.getInt("COLUMN_SIZE"));
                    introspectedColumn.setActualColumnName(rs.getString("COLUMN_NAME"));
                    introspectedColumn.setNullable(rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable);
                    introspectedColumn.setScale(rs.getInt("DECIMAL_DIGITS"));
                    introspectedColumn.setRemarks(rs.getString("REMARKS"));
                    introspectedColumn.setDefaultValue(rs.getString("COLUMN_DEF"));
                    introspectedColumn.setAutoIncrement("YES".equals(rs.getString("IS_AUTOINCREMENT")));
                    columns.add(introspectedColumn);
                }
                return "resultSet";
            }
        });
        System.out.println(columns);
    }