Java JDBC Oracle Connection CreateTableScript(String dbSourceName, String db2SchemaName, String schemaList, String server, int port, String dbName, String userid, String pwd)

Here you can find the source of CreateTableScript(String dbSourceName, String db2SchemaName, String schemaList, String server, int port, String dbName, String userid, String pwd)

  1. HOME
  2. Java
  3. J
  4. JDBC Oracle Connection
  5. CreateTableScript(String dbSourceName, String db2SchemaName, String schemaList, String server, int port, String dbName, String userid, String pwd)

Description

Create Table Script

License

Apache License

Declaration 

public static void CreateTableScript(String dbSourceName,
            String db2SchemaName, String schemaList, String server,
            int port, String dbName, String userid, String pwd)

Method Source Code

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

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;

import java.io.FileWriter;

import java.io.InputStream;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

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 filesep = System.getProperty("file.separator");
    public static String Message = "";

    public static void CreateTableScript(String dbSourceName,
            String db2SchemaName, String schemaList, String server,
            int port, String dbName, String userid, String pwd) {
        Connection mainConn = null;

        String tableName = "";
        String schemaName = "";
        String OUTPUT_DIR = null;
        String[] tableType = { "TABLE" };

        String driverName = getDriverName(dbSourceName);
        StringBuffer buffer = new StringBuffer();

        Message = "";
        try {/*from w w w.j a va2s .co m*/
            OUTPUT_DIR = System.getProperty("OUTPUT_DIR");
            if ((OUTPUT_DIR == null) || (OUTPUT_DIR.equals(""))) {
                OUTPUT_DIR = ".";
            }
            File tmpfile = new File(OUTPUT_DIR);
            tmpfile.mkdirs();
            BufferedWriter inputFileWriter = new BufferedWriter(
                    new FileWriter(OUTPUT_DIR + filesep + dbName
                            + ".tables", false));
            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);
            }
            ResultSet Reader;
            if ((dbSourceName.equalsIgnoreCase("access"))
                    || (dbSourceName.equalsIgnoreCase("domino"))) {
                DatabaseMetaData dbMetaData = mainConn.getMetaData();
                Reader = dbMetaData.getTables(null, null, null, null);
            } else {
                DatabaseMetaData dbMetaData = mainConn.getMetaData();
                Reader = dbMetaData.getTables(null, "%", "%", tableType);
            }

            buffer.setLength(0);
            if ((schemaList == null) || (schemaList.equals(""))
                    || (schemaList.equalsIgnoreCase("all"))) {
                schemaList = GetSchemaList(dbSourceName, server, port,
                        dbName, userid, pwd);
            }
            while (Reader.next()) {
                tableName = Reader.getString(3);
                schemaName = Reader.getString(2);
                if (((dbSourceName.equalsIgnoreCase("access")) && (tableName
                        .startsWith("MSys")))
                        || ((dbSourceName.equalsIgnoreCase("oracle")) && (tableName
                                .startsWith("BIN$"))))
                    continue;
                if ((schemaName == null) || (schemaName.equals(""))) {
                    if ((db2SchemaName == null)
                            || (db2SchemaName.equals("")))
                        db2SchemaName = "ADMIN";
                    if (dbSourceName.equalsIgnoreCase("mysql")) {
                        buffer.append("\"" + db2SchemaName + "\".\""
                                + tableName + "\":SELECT * FROM "
                                + tableName + linesep);
                        continue;
                    }
                    buffer.append("\"" + db2SchemaName + "\".\""
                            + tableName + "\":SELECT * FROM \"" + tableName
                            + "\"" + linesep);
                    continue;
                }

                String[] strArray = schemaList.split(":");
                for (int i = 0; i < strArray.length; i++) {
                    if (!strArray[i].equalsIgnoreCase(schemaName))
                        continue;
                    db2SchemaName = schemaName.replaceAll("\\\\",
                            "\\\\\\\\");
                    if (dbSourceName.equalsIgnoreCase("oracle")) {
                        boolean notOverFlow = isOracleIOTOverFlow(mainConn,
                                schemaName, tableName);
                        if (notOverFlow)
                            buffer.append("\"" + db2SchemaName + "\".\""
                                    + tableName + "\":SELECT * FROM \""
                                    + schemaName + "\".\"" + tableName
                                    + "\"" + linesep);
                    } else {
                        buffer.append("\"" + db2SchemaName + "\".\""
                                + tableName + "\":SELECT * FROM \""
                                + schemaName + "\".\"" + tableName + "\""
                                + linesep);
                    }
                }
            }

            if ((buffer.length() == 0)
                    && (dbSourceName.equalsIgnoreCase("oracle")))
                buffer.append("\"" + userid.toUpperCase()
                        + "\".\"DUMMY_TABLE\":SELECT * FROM " + "\""
                        + userid.toUpperCase() + "\".\"DUMMY_TABLE\""
                        + linesep);
            log(buffer.toString());
            inputFileWriter.write(buffer.toString());
            inputFileWriter.close();
            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();
            }
        }
    }

    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;
    }

    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 {
            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;
    }

    private static boolean isOracleIOTOverFlow(Connection conn,
            String schemaName, String tableName) {
        boolean notOverFlow = true;
        String sql = "SELECT 1 FROM DBA_TABLES WHERE OWNER = '"
                + schemaName + "' " + "AND TABLE_NAME = '" + tableName
                + "' AND IOT_TYPE = 'IOT_OVERFLOW'";
        try {
            PreparedStatement statement = conn.prepareStatement(sql);
            ResultSet rs = statement.executeQuery();
            if (rs.next()) {
                notOverFlow = false;
            }
            if (rs != null)
                rs.close();
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return notOverFlow;
    }

    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. createConnection()
  2. createDataBaseConnection(String hostName, String portNumber, String userName, String password)
  3. getConnection()
  4. getConnection()
  5. getConnection()
  6. getConnection()