Java JDBC Oracle Connection GetSchemaList(String dbSourceName, String server, int port, String dbName, String userid, String pwd)

Description

Get Schema List

License

Apache License

Declaration

public static String GetSchemaList(String dbSourceName, String server,
            int port, String dbName, String userid, String pwd) 

Method Source Code

//package com.java2s;
//License from project: Apache License 

import java.io.FileInputStream;

import java.io.InputStream;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;

import java.sql.ResultSet;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.Properties;

public class Main {
    private static final SimpleDateFormat timestampFormat = new SimpleDateFormat(
            "yyyy-MM-dd HH.mm.ss.SSS");
    private static String URL_PROP_FILE = "url.properties";
    private static String DRIVER_PROP_FILE = "driver.properties";
    private static String SCHEMA_EXCLUDE_FILE = "SchemaExcludeList.properties";
    private static Properties mapExcludeList = null;
    private static Properties propURL = null;
    private static Properties propDrivers = null;
    public static String linesep = System.getProperty("line.separator");
    public static String osType = System.getProperty("os.name")
            .toUpperCase().startsWith("Z/OS") ? "z/OS" : System
            .getProperty("os.name").toUpperCase().startsWith("WIN") ? "WIN"
            : "OTHER";
    public static String Message = "";

    public static String GetSchemaList(String dbSourceName, String server,
            int port, String dbName, String userid, String pwd) {
        Connection mainConn = null;

        String tableSchema = "";
        String schemaNames = "";
        String driverName = getDriverName(dbSourceName);

        Message = "";
        try {//from w  w w .  j  a v  a  2s .c o m
            Class.forName(driverName).newInstance();
            log("Driver " + driverName + " loaded");
            String url = getURL(dbSourceName, server, port, dbName);
            if (dbSourceName.equalsIgnoreCase("domino")) {
                mainConn = DriverManager.getConnection(url);
            } else {
                mainConn = DriverManager.getConnection(url, userid, pwd);
                mainConn.setAutoCommit(false);
            }

            DatabaseMetaData dbMetaData = mainConn.getMetaData();
            ResultSet Reader = dbMetaData.getSchemas();
            int i = 0;
            while (Reader.next()) {
                tableSchema = Reader.getString(1);
                if (!Excluded(dbSourceName, tableSchema))
                    continue;
                if (i > 0)
                    schemaNames = schemaNames + ":";
                schemaNames = schemaNames + tableSchema;
                i++;
            }

            if (dbSourceName.equalsIgnoreCase("mysql"))
                schemaNames = "admin";
            mainConn.commit();
            Reader.close();
            mainConn.close();
        } catch (Exception e) {
            if ((dbSourceName.equals("access"))
                    || (dbSourceName.equals("domino"))) {
                log("Schema names are not supported in " + dbSourceName
                        + " database");
            } else {
                log("Error in loading the driver : " + driverName + " for "
                        + dbSourceName + " Error Message :"
                        + e.getMessage());
                Message = dbSourceName
                        + " Error encountered. Please see console's output.";
                e.printStackTrace();
            }
        }
        return schemaNames;
    }

    public static String getDriverName(String dbSourceName) {
        try {
            if (propDrivers == null) {
                propDrivers = new Properties();
                InputStream istream = ClassLoader
                        .getSystemResourceAsStream(DRIVER_PROP_FILE);
                if (istream == null) {
                    FileInputStream finStream = new FileInputStream(
                            DRIVER_PROP_FILE);
                    propDrivers.load(finStream);
                    finStream.close();
                } else {
                    propDrivers.load(istream);
                    istream.close();
                }
                log("Configuration file loaded: '" + DRIVER_PROP_FILE + "'");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        String driverName = propDrivers.getProperty(dbSourceName
                .toLowerCase());
        return driverName;
    }

    public static void log(String msg) {
        if (osType.equals("z/OS")) {
            System.out.println(timestampFormat.format(new Date()) + ":"
                    + msg);
        } else
            System.out.println("[" + timestampFormat.format(new Date())
                    + "] " + msg);
    }

    public static String getURL(String dbSourceName, String server,
            int port, String dbName) {
        String url = "";
        try {
            if (propURL == null) {
                propURL = new Properties();
                InputStream istream = ClassLoader
                        .getSystemResourceAsStream(URL_PROP_FILE);
                if (istream == null) {
                    FileInputStream finStream = new FileInputStream(
                            URL_PROP_FILE);
                    propURL.load(finStream);
                    finStream.close();
                } else {
                    propURL.load(istream);
                    istream.close();
                }
                log("Configuration file loaded: '" + URL_PROP_FILE + "'"
                        + linesep);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        if (dbSourceName.equalsIgnoreCase("oracle")) {
            url = (String) propURL.get(dbSourceName) + server + ":" + port
                    + ":" + dbName;
        } else if (dbSourceName.equalsIgnoreCase("mssql")) {
            url = (String) propURL.get(dbSourceName) + server + ":" + port
                    + ";database=" + dbName;
        } else if ((dbSourceName.equalsIgnoreCase("access"))
                || (dbSourceName.equalsIgnoreCase("hxtt"))
                || (dbSourceName.equalsIgnoreCase("domino"))) {
            url = (String) propURL.get(dbSourceName) + server;
        } else if (dbSourceName.equalsIgnoreCase("mysql")) {
            url = (String) propURL.get(dbSourceName) + server + ":" + port
                    + "/" + dbName + "?zeroDateTimeBehavior=round";
        } else if (dbSourceName.equalsIgnoreCase("zdb2")) {
            url = (String) propURL.get(dbSourceName)
                    + server
                    + ":"
                    + port
                    + "/"
                    + dbName
                    + ":retrieveMessagesFromServerOnGetMessage=true;emulateParameterMetaDataForZCalls=1;";
        } else if (dbSourceName.equalsIgnoreCase("idb2")) {
            url = (String) propURL.get(dbSourceName) + server + ":" + port
                    + "/" + dbName + ";date format=iso";
        } else if (dbSourceName.equalsIgnoreCase("sybase")) {
            url = (String) propURL.get(dbSourceName) + server + ":" + port
                    + "/" + dbName;
        } else {
            url = (String) propURL.get(dbSourceName) + server + ":" + port
                    + "/" + dbName;
        }
        return url;
    }

    private static boolean Excluded(String dbSourceName, String schemaName) {
        String[] strArray = null;
        String schemaList = null;
        if ((schemaName == null) || (schemaName.equals(""))) {
            return false;
        }
        LoadExcludeList();

        schemaList = mapExcludeList.getProperty(dbSourceName.toLowerCase());
        if (schemaList != null)
            strArray = schemaList.split("~");
        for (int i = 0; i < strArray.length; i++) {
            if (strArray[i].equalsIgnoreCase(schemaName))
                return false;
        }
        return true;
    }

    private static void LoadExcludeList() {
        try {
            if (mapExcludeList == null) {
                mapExcludeList = new Properties();
                InputStream istream = ClassLoader
                        .getSystemResourceAsStream(SCHEMA_EXCLUDE_FILE);
                if (istream == null) {
                    FileInputStream finStream = new FileInputStream(
                            SCHEMA_EXCLUDE_FILE);
                    mapExcludeList.load(finStream);
                    finStream.close();
                } else {
                    mapExcludeList.load(istream);
                    istream.close();
                }
                log("Configuration file loaded: '" + SCHEMA_EXCLUDE_FILE
                        + "'");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Related

1. getJDBCConnection()
2. getOracle10GConnection(String connIP, String port, String dbName, String username, String password)
3. getOracleConn()
4. getOracleConnection()
5. getOracleDBConnection(String server, String database, String username, String password)
6. OpenConnection(String dbSourceName, String server, int port, String dbName, String userid, String pwd)