com.testmax.util.DbUtil.java Source code

Java tutorial

Introduction

Here is the source code for com.testmax.util.DbUtil.java

Source

/*
 * Copyright (C) 2014 Artitelly Solutions Inc, www.CloudTestSoftware.com
 *
 * Licensed under the Common Development and Distribution License (CDDL-1.0) (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://opensource.org/licenses/CDDL-1.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.testmax.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.dom4j.Element;
import com.testmax.framework.ConfigLoader;
import com.testmax.framework.WmLog;

public class DbUtil {

    private ItemUtility ut = new ItemUtility();
    static HashMap<String, Connection> dbManager = null;

    public DbUtil() {
        dbManager = new HashMap<String, Connection>();
    }

    public Connection makeOracleDbConnection(String url, String user, String password) {
        Connection con = null;
        try {
            try {
                Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

            } catch (InstantiationException e) {
                String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                        + ">>>" + e.getMessage();
                printMessage(msg);
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                        + ">>>" + e.getMessage();
                printMessage(msg);
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                        + ">>>" + e.getMessage();
                printMessage(msg);
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            con = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                    + ">>>" + e.getMessage();
            printMessage(msg);
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return con;
    }

    public Connection makeCassandraDbConnection(String url, String user, String password) {
        Connection con = null;
        try {
            try {
                Class.forName("org.apache.cassandra.cql.jdbc.CassandraDriver");
            } catch (ClassNotFoundException e) {
                String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                        + ">>>" + e.getMessage();
                printMessage(msg);
                // TODO Auto-generated catch block
                e.printStackTrace();
            }

            if (user == null || user.isEmpty()) {
                con = DriverManager.getConnection(url);
            } else {
                con = DriverManager.getConnection(url, user, password);
            }

        } catch (SQLException e) {
            String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                    + ">>>" + e.getMessage();
            printMessage(msg);
            e.printStackTrace();
        }
        return con;
    }

    public Connection makePostgreSqlDbConnection(String url, String user, String password) {
        Connection con = null;
        try {
            try {
                Class.forName("org.postgresql.Driver").newInstance();
            } catch (InstantiationException e) {
                String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                        + ">>>" + e.getMessage();
                printMessage(msg);
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                        + ">>>" + e.getMessage();
                printMessage(msg);
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                        + ">>>" + e.getMessage();
                printMessage(msg);
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            con = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                    + ">>>" + e.getMessage();
            printMessage(msg);
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return con;
    }

    public Connection makeMysqlDbConnection(String url, String user, String password) {
        Connection con = null;
        try {
            try {
                Class.forName("com.mysql.jdbc.Driver").newInstance();
            } catch (InstantiationException e) {
                String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                        + ">>>" + e.getMessage();
                printMessage(msg);
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                        + ">>>" + e.getMessage();
                printMessage(msg);
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                        + ">>>" + e.getMessage();
                printMessage(msg);
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            con = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                    + ">>>" + e.getMessage();
            printMessage(msg);
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return (con);
    }

    private void printMessage(String msg) {
        WmLog.getCoreLogger().error(msg);
        System.out.println(msg);
    }

    public Connection makeOtherDbConnection(String dbDriver, String url, String user, String password) {
        Connection con = null;
        try {
            try {
                Class.forName(dbDriver).newInstance();
            } catch (InstantiationException e) {
                String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                        + ">>>" + e.getMessage();
                printMessage(msg);
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                        + ">>>" + e.getMessage();
                printMessage(msg);
                // TODO Auto-generated catch block
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                        + ">>>" + e.getMessage();
                printMessage(msg);
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            con = DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            String msg = ">>ERROR: Can not create database connection using url=" + url + " and dbUser=" + user
                    + ">>>" + e.getMessage();
            printMessage(msg);
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return (con);
    }

    public Connection getDbConnection(String dbName) {
        Connection con = null;
        String dbHost = null;
        String dbService = null;
        String dbUser = null;
        String dbPass = null;
        String dbPort = null;
        String provider = null;
        String driver = null;
        Element dbEnv = null;
        String url = null;
        if (dbManager.get(dbName) != null) {
            return dbManager.get(dbName);
        }
        if (dbName != null && !dbName.isEmpty()) {
            dbEnv = ConfigLoader.getDatabaseEnv(dbName);
            if (dbEnv != null) {
                List<Element> elmlist = dbEnv.elements();
                for (Element elm : elmlist) {
                    if (elm.getName().equalsIgnoreCase("user")) {
                        dbUser = elm.getText();
                    } else if (elm.getName().equalsIgnoreCase("pwd")) {
                        dbPass = elm.getText();
                    } else if (elm.getName().equalsIgnoreCase("host") && !elm.getText().isEmpty()) {
                        dbHost = elm.getText();
                    } else if (elm.getName().equalsIgnoreCase("service")) {
                        dbService = elm.getText();
                    } else if (elm.getName().equalsIgnoreCase("rac") && !elm.getText().isEmpty()) {
                        dbHost = elm.getText();
                    } else if (elm.getName().equalsIgnoreCase("url") && !elm.getText().isEmpty()) {
                        url = elm.getText();
                    } else if (elm.getName().equalsIgnoreCase("port") && !elm.getText().isEmpty()) {
                        dbPort = elm.getText();
                    } else if (elm.getName().equalsIgnoreCase("db") && !elm.getText().isEmpty()) {
                        provider = elm.getText();
                    } else if (elm.getName().equalsIgnoreCase("driver") && !elm.getText().isEmpty()) {
                        driver = elm.getText();
                    }
                }
            }
        }

        if (url == null) {
            url = getConnectionStr(provider, dbHost, dbService, dbPort);
        }

        con = makeDbConnection(provider, driver, url, dbUser, dbPass);
        dbManager.put(dbName, con);
        return con;
    }

    public Connection getDbConnection(Element dbEnv, String dbName) {
        Connection con = null;
        String dbHost = null;
        String dbService = null;
        String dbUser = null;
        String dbPass = null;
        String dbPort = null;
        String provider = null;
        String driver = null;
        String url = null;
        if (dbManager.get(dbName) != null) {
            return dbManager.get(dbName);
        }
        if (dbEnv != null) {
            List<Element> elmlist = dbEnv.elements();
            for (Element elm : elmlist) {
                if (elm.getName().equalsIgnoreCase("user")) {
                    dbUser = elm.getText();
                } else if (elm.getName().equalsIgnoreCase("pwd")) {
                    dbPass = elm.getText();
                } else if (elm.getName().equalsIgnoreCase("host") && !elm.getText().isEmpty()) {
                    dbHost = elm.getText();
                } else if (elm.getName().equalsIgnoreCase("service")) {
                    dbService = elm.getText();
                } else if (elm.getName().equalsIgnoreCase("rac") && !elm.getText().isEmpty()) {
                    dbHost = elm.getText();
                } else if (elm.getName().equalsIgnoreCase("url") && !elm.getText().isEmpty()) {
                    url = elm.getText();
                } else if (elm.getName().equalsIgnoreCase("port") && !elm.getText().isEmpty()) {
                    dbPort = elm.getText();
                } else if (elm.getName().equalsIgnoreCase("db") && !elm.getText().isEmpty()) {
                    provider = elm.getText();
                } else if (elm.getName().equalsIgnoreCase("driver") && !elm.getText().isEmpty()) {
                    driver = elm.getText();
                }
            }
        }

        if (url == null) {
            url = getConnectionStr(provider, dbHost, dbService, dbPort);
        }

        con = makeDbConnection(provider, driver, url, dbUser, dbPass);
        dbManager.put(dbName, con);

        return con;
    }

    private Connection makeDbConnection(String provider, String driver, String url, String user, String password) {
        Connection con = null;
        DbUtil du = new DbUtil();
        if (provider.equalsIgnoreCase("oracle")) {
            con = du.makeOracleDbConnection(url, user, password);
        } else if (provider.equalsIgnoreCase("cassandra")) {
            con = du.makeCassandraDbConnection(url, user, password);
        } else if (provider.equalsIgnoreCase("postgresql")) {
            con = du.makePostgreSqlDbConnection(url, user, password);
        } else if (provider.equalsIgnoreCase("mysql")) {
            con = du.makeMysqlDbConnection(url, user, password);
        } else {
            if (driver != null && !driver.isEmpty()) {
                con = du.makeOtherDbConnection(driver, url, user, password);
            } else {
                printMessage(">>>>ERROR To Connect Database. Please provide driver class name");
            }
        }
        return con;
    }

    public String getColumnValue(ArrayList<ArrayList<Object>> rsData, int rowId, String column) {

        return (ut.getColumnValue(rsData, rowId, column));

    }

    public ArrayList<ArrayList<Object>> getQueryResult(String sql, String dbName) {

        Statement st = null;
        ArrayList<ArrayList<Object>> rsData = null;

        Connection con = this.getDbConnection(dbName);

        if (con != null) {
            try {
                st = con.createStatement();
                ResultSet rs = st.executeQuery(sql);
                rsData = ut.setResults2Array(rs, null);
                if (st != null) {
                    try {
                        st.close();
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                printMessage("Problem occured while querying into database \n" + e.getMessage() + "\n" + sql);

                e.printStackTrace();
                if (!e.getMessage().contains("Duplicate")) {
                    dbManager.remove(dbName);
                }

            } finally {
                if (st != null) {
                    try {
                        st.close();
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }

            }
        }

        return (rsData);

    }

    public boolean executeQuery(String sql, String dbName) {
        Connection con = null;
        if (dbManager.get(dbName) != null) {
            con = dbManager.get(dbName);
        }
        if (con == null) {
            con = this.getDbConnection(dbName);
            dbManager.put(dbName, con);
        }
        Statement st = null;
        try {
            st = con.createStatement();
            st.executeUpdate(sql);
        } catch (SQLException e) {
            printMessage(
                    "##### Problem occured while executing query into database \n" + e.getMessage() + "\n" + sql);
            if (!e.getMessage().contains("Duplicate")) {
                dbManager.remove(dbName);
            }
            //e.printStackTrace();
            return false;
        }
        return true;
    }

    /*
     *  Prepare default connection strings for Oracle Rac
     */
    private String getConnectionStr(String provider, String rac, String service, String port) {
        String url = "jdbc:oracle:thin:@(DESCRIPTION =" + "(ADDRESS_LIST =" + "(ADDRESS = (PROTOCOL = TCP)(HOST = "
                + rac + ")(PORT =" + port + "))" + ")" + "(CONNECT_DATA =" + "(SERVICE_NAME = " + service + ")"
                + ")" + ")";
        if (provider.equalsIgnoreCase("cassandra")) {
            url = "jdbc:cassandra://" + rac + ":" + port + "/" + service + "?version=3.0.0";
        } else if (provider.equalsIgnoreCase("mysql")) {
            url = "jdbc:mysql://" + rac + ":" + port + "/" + service;
        } else if (provider.equalsIgnoreCase("postgresql")) {
            url = "jdbc:postgresql://" + rac + ":" + port + "/" + service;
        }

        return url;
    }

}