java 获取各种数据库的视图、函数、存储过程

最新推荐文章于 2023-01-12 14:43:33 发布

麻花2013

最新推荐文章于 2023-01-12 14:43:33 发布

阅读量2.3k

点赞数

分类专栏：数据库 java技术

本文链接：https://blog.csdn.net/u012269637/article/details/102977813

版权

java技术同时被 2 个专栏收录

59 篇文章 0 订阅

订阅专栏

数据库

25 篇文章 1 订阅

订阅专栏

//postgresql ||HWMPP
if (dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.PostgreSQL.getName()) 
      || dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.GreenPlum.getName())
      || dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.HWMPPDB.getName())) {
    sql = "select * from pg_views where schemaname=?";
    sqlValue=dataSource.getSchemaName();
} else if (dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.Oracle.getName())||dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.GaussDB100.getName())) {//oracle
    sql = "select * from all_views where owner=?";
    sqlValue=dataSource.getUserName();
} else if (dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.MySQL.getName())) {
    sql = "select * from views WHERE TABLE_SCHEMA=?";
    sqlValue=dataSource.getDbServiceName();
} else if(dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.SQLServer.getName())||dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.SQLServer2000.getName()))
{
    sql="select sysobjects.*,OBJECT_DEFINITION (id) AS ObjectDefinition  from sysobjects where xtype='V'";
}else if(dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.GBase.getName())) {
   sql = "select * from information_schema.views WHERE TABLE_SCHEMA=?";
    sqlValue=dataSource.getDbServiceName();
}

if (dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.PostgreSQL.getName()) 
      || dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.GreenPlum.getName())
      || dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.GaussDB200.getName())
        || dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.GaussDB100.getName())
      || dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.HWMPPDB.getName())) {//postgresql
    //sql = "select * from pg_proc where proowner=(select usesysid from pg_user where usename ='" + dataSource.getSchemaName() + "')";
   sqlValue=dataSource.getSchemaName();
   sql = "select oid::regprocedure as proname,prosrc from pg_proc where pronamespace=(SELECT oid  FROM pg_namespace where nspname =?)";
    schema = new Schema(null, dataSource.getSchemaName());
} else if (dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.Oracle.getName())||dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.GaussDB100.getName())) {//oracle
    sql = "select all_procedures.*,user_source.TEXT from all_procedures  " +
            "inner join user_source " +
            "on all_procedures.OBJECT_NAME=user_source.name " +
            "where owner=? and object_type='FUNCTION'";
    sqlValue=dataSource.getUserName();
}else if (dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.MySQL.getName())) {//必须具有读取msyql系统数据库的权限
    sql = "select * from mysql.proc where db=? and type='FUNCTION' ";
    sqlValue=dataSource.getDbServiceName();
} else if(dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.SQLServer.getName())||dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.SQLServer2000.getName())){
    sql="select sysobjects.*,OBJECT_DEFINITION (id) AS ObjectDefinition  from sysobjects where xtype in('FN','IF','TF')";
    //sqlValue=dataSource.getDbServiceName();
}else if(dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.GBase.getName())) {
    sql = "select * from gbase.proc where db=? and type='FUNCTION' ";
    sqlValue=dataSource.getDbServiceName();
}

if (dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.Oracle.getName())||dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.GaussDB100.getName())) {//oracle
    sql = "select all_procedures.*,user_source.TEXT from all_procedures  " +
            "inner join user_source " +
            "on all_procedures.OBJECT_NAME=user_source.name " +
            "where owner=? and object_type='PROCEDURE'";
    sqlValue=dataSource.getUserName();
} else if (dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.MySQL.getName())) {//必须具有读取msyql系统数据库的权限
    sql = "select * from mysql.proc where db=?  and type='PROCEDURE'";
    sqlValue=dataSource.getDbServiceName();
}else if(dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.SQLServer.getName())||dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.SQLServer2000.getName())){
    sql="select sysobjects.*,OBJECT_DEFINITION (id) AS ObjectDefinition  from sysobjects where xtype='P'";
}else if(dataSource.getDatabaseType().equalsIgnoreCase(DatabaseType.GBase.getName())) {
   sql = "select * from gbase.proc where db=?  and type='PROCEDURE'";
     sqlValue=dataSource.getDbServiceName();
}