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

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

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

Description

Open Connection

License

Apache License

Declaration 

public static Connection OpenConnection(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.File;
import java.io.FileInputStream;

import java.io.InputStream;

import java.sql.Connection;

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 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 DB2Path = "";
    public static String InstanceName = "";
    public static String Varchar2_Compat = "";
    public static String Date_Compat = "";
    public static String Number_Compat = "";
    public static String Decflt_rounding = "";

    public static Connection OpenConnection(String dbSourceName,
            String server, int port, String dbName, String userid,
            String pwd) {//from   w w  w . jav  a2 s  . c  o  m
        Connection mainConn = null;
        Message = "";
        String driverName = getDriverName(dbSourceName);
        try {
            try {
                Class.forName(driverName).newInstance();
            } catch (Exception e) {
                e.printStackTrace();
                Message = "Driver could not be loaded. See console's output.";
                return null;
            }
            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);
                if (dbSourceName.equalsIgnoreCase("db2")) {
                    DB2Path = getDB2PathName(mainConn);
                    InstanceName = getInstanceName(mainConn);
                    getCompatibilityParams(mainConn);
                }
            }
            Message = "Connection to " + dbSourceName + " succeeded.";
            log(Message);
        } catch (SQLException e) {
            if (e.getErrorCode() == -4499) {
                if (e.getMessage().contains(
                        "An attempt was made to access a database"))
                    Message = "Connection succeeded but database not found";
                else if (e.getMessage().contains("Error opening socket"))
                    Message = "Error opening socket to DB2 Server. Check if DB2 instance is up. If yes, check check port number.";
                else
                    Message = "Error connecting to DB2 server";
                log("Error Message : " + e.getMessage());
            } else if (e.getErrorCode() == -4214) {
                Message = "Connection succeeded but userid/password is incorrect.";
            } else {
                if (e.getMessage().contains("User ID or Password invalid"))
                    Message = "Error connecting. User ID or Password invalid";
                else if (e
                        .getMessage()
                        .contains(
                                "The Network Adapter could not establish the connection"))
                    Message = "The Network Adapter could not establish the connection";
                else
                    Message = dbSourceName
                            + " JDBC connection problem. Please see console's output.";
                log("Error connecting : " + driverName + " for "
                        + dbSourceName + " Error Message :"
                        + e.getMessage());
            }
            log(Message);
            e.printStackTrace();
            return null;
        }
        return mainConn;
    }

    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 String getDB2PathName(Connection conn) {
        String result = "";
        try {
            String sql = "select reg_var_value from sysibmadm.reg_variables where reg_var_name = 'DB2PATH'";
            PreparedStatement partStatement = conn.prepareStatement(sql);
            ResultSet rs = partStatement.executeQuery();
            while (rs.next()) {
                result = rs.getString(1);
            }
            if (rs != null)
                rs.close();
            if (partStatement != null)
                partStatement.close();
            conn.commit();
        } catch (Exception e) {
            e.printStackTrace();
            String userHome = System.getProperty("user.home");
            if (FileExists(userHome + "/sqllib")) {
                result = userHome + "/sqllib/java";
            } else
                result = "";
        }
        log("DB2 PATH is " + result);
        return result;
    }

    private static String getInstanceName(Connection conn) {
        String result = "";
        try {
            String sql = "select inst_name from sysibmadm.env_inst_info fetch first row only";
            PreparedStatement partStatement = conn.prepareStatement(sql);
            ResultSet rs = partStatement.executeQuery();
            while (rs.next()) {
                result = rs.getString(1);
            }
            if (rs != null)
                rs.close();
            if (partStatement != null)
                partStatement.close();
            conn.commit();
        } catch (Exception e) {
            result = "";
            e.printStackTrace();
        }
        log("DB2 instance name is " + result);
        return result;
    }

    private static void getCompatibilityParams(Connection conn) {
        Varchar2_Compat = "";
        Date_Compat = "";
        Number_Compat = "";
        try {
            String sql = "select a.value, b.value, c.value, d.value from sysibmadm.dbcfg a, sysibmadm.dbcfg b, sysibmadm.dbcfg c, sysibmadm.dbcfg d where a.name = 'varchar2_compat' and b.name = 'date_compat' and c.name = 'number_compat'and d.name = 'decflt_rounding'";

            PreparedStatement partStatement = conn.prepareStatement(sql);
            ResultSet rs = partStatement.executeQuery();
            while (rs.next()) {
                Varchar2_Compat = rs.getString(1);
                Date_Compat = rs.getString(2);
                Number_Compat = rs.getString(3);
                Decflt_rounding = rs.getString(4);
            }
            if (rs != null)
                rs.close();
            if (partStatement != null)
                partStatement.close();
            conn.commit();
        } catch (Exception e) {
            Varchar2_Compat = "";
            Date_Compat = "";
            Number_Compat = "";
            Decflt_rounding = "";
            e.printStackTrace();
        }
        log("DB2 Compatibility params varchar2_compat=" + Varchar2_Compat
                + " date_compat=" + Date_Compat + " number_compat="
                + Number_Compat + " Decflt_rounding=" + Decflt_rounding);
    }

    public static boolean FileExists(String fileName) {
        File f = new File(fileName);
        return f.exists();
    }
}

Related

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