在pgsql模式下，查找数据库以及数据表信息

1.获取数据库中所有表

采用的是在代码中编写动态sql语句，再通过mapper层调用对应的数据库。

String sql = "SELECT tablename FROM pg_tables WHERE schemaname = '"+schemaName+"';";

上面是一个简单的通过schemaName（具体数据库名称）获取表信息的sql。

这里可能会有多个数据库，要在每个实现类上打上 @DS("（这里标明是哪个数据库）") 注解

    @Select("${sqlText}")
    void dynamicSql(@Param("sqlText") String sqlText);

    @Select("${sqlText}")
    Object dynamicObject(@Param("sqlText") String sqlText);

    @Select("${sqlText}")
    List<Object> dynamicList(@Param("sqlText") String sqlText);

    @Select("${sqlText}")
    List<Map> dynamicMapList(@Param("sqlText") String sqlText);

将上面的sql放入mapper运行，注意一下，在对应的方法上要打上 @Select("${sqlText}")

2.pgsql获取表名、表描述、表长度、字段名等

String sql = "select a.attname as fieldname,d.description as fielddesc,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\\(.*\\)')) as fieldtype," +
                 "( CASE WHEN a.attlen > 0 THEN a.attlen ELSE a.atttypmod - 4 END ) as fieldlength, " +
                 "(case when (select count(pg_constraint.*)from pg_constraint inner join pg_class on pg_constraint.conrelid = pg_class.oid inner join pg_attribute on pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum = any(pg_constraint.conkey) " +
                 "inner join pg_type on pg_type.oid = pg_attribute.atttypid where pg_class.relname = c.relname and pg_constraint.contype = 'p' and pg_attribute.attname = a.attname) > 0 then true else false end) as fieldkey " +
                 "from pg_class c,pg_attribute a,pg_type t,pg_description d " +
             "where  c.relname ='"+ tableName +"' and a.attnum>0 and a.attrelid = c.oid and a.atttypid = t.oid and  d.objoid=a.attrelid and d.objsubid=a.attnum";
// 以集合嵌套map的格式返回         
List<Map> tableDataMap = dbMapper.dynamicMapList(sql);

最后留言：还有很多操作，比如我们在业务上要经常用到字段等信息，可以将字段信息直接存在元数据库里，这里的元数据库指的是数据源头，方便使用，这里要使用@PostConstruct注解详解，@PostConstruct注解详解_大局观的小老虎的博客-CSDN博客