org.talend.metadata.managment.utils.MetadataConnectionUtils.java Source code

Java tutorial

Introduction

Here is the source code for org.talend.metadata.managment.utils.MetadataConnectionUtils.java

Source

// ============================================================================
//
// Copyright (C) 2006-2015 Talend Inc. - www.talend.com
//
// This source code is available under agreement available at
// %InstallDIR%\features\org.talend.rcp.branding.%PRODUCTNAME%\%PRODUCTNAME%license.txt
//
// You should have received a copy of the agreement
// along with this program; if not, write to Talend SA
// 9 rue Pages 92150 Suresnes, France
//
// ============================================================================
package org.talend.metadata.managment.utils;

import java.io.UnsupportedEncodingException;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.xml.rpc.ServiceException;

import org.apache.commons.codec.binary.Base64;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.eclipse.core.runtime.Platform;
import org.talend.commons.exception.CommonExceptionHandler;
import org.talend.commons.exception.ExceptionHandler;
import org.talend.commons.exception.PersistenceException;
import org.talend.commons.utils.database.SybaseDatabaseMetaData;
import org.talend.core.GlobalServiceRegister;
import org.talend.core.IRepositoryContextService;
import org.talend.core.database.EDatabase4DriverClassName;
import org.talend.core.database.EDatabaseTypeName;
import org.talend.core.model.metadata.IMetadataConnection;
import org.talend.core.model.metadata.builder.ConvertionHelper;
import org.talend.core.model.metadata.builder.MetadataConnection;
import org.talend.core.model.metadata.builder.connection.Connection;
import org.talend.core.model.metadata.builder.connection.DatabaseConnection;
import org.talend.core.model.metadata.builder.connection.MDMConnection;
import org.talend.core.model.metadata.builder.connection.MetadataColumn;
import org.talend.core.model.metadata.builder.database.DriverShim;
import org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils;
import org.talend.core.model.metadata.builder.database.IDriverService;
import org.talend.core.model.properties.ConnectionItem;
import org.talend.core.repository.model.ProxyRepositoryFactory;
import org.talend.core.runtime.CoreRuntimePlugin;
import org.talend.cwm.helper.ConnectionHelper;
import org.talend.cwm.helper.SwitchHelpers;
import org.talend.cwm.relational.RelationalFactory;
import org.talend.cwm.relational.TdColumn;
import org.talend.cwm.relational.TdSqlDataType;
import org.talend.cwm.xml.TdXmlElementType;
import org.talend.mdm.webservice.XtentisBindingStub;
import org.talend.mdm.webservice.XtentisPort_PortType;
import org.talend.mdm.webservice.XtentisServiceLocator;
import org.talend.metadata.managment.connection.manager.HiveConnectionManager;
import org.talend.metadata.managment.model.DBConnectionFillerImpl;
import org.talend.metadata.managment.model.MetadataFillFactory;
import org.talend.utils.exceptions.MissingDriverException;
import org.talend.utils.sql.ConnectionUtils;
import org.talend.utils.string.AsciiUtils;
import org.talend.utils.sugars.ReturnCode;
import org.talend.utils.sugars.TypedReturnCode;

import orgomg.cwm.foundation.softwaredeployment.DataProvider;
import orgomg.cwm.objectmodel.core.ModelElement;
import orgomg.cwm.objectmodel.core.Package;
import orgomg.cwm.resource.relational.Catalog;
import orgomg.cwm.resource.relational.Schema;

/**
 * DOC zshen class global comment. Detailled comment
 */
public class MetadataConnectionUtils {

    private static Logger log = Logger.getLogger(MetadataConnectionUtils.class);

    // MOD mzhao 2009-06-05 Bug 7571
    private static final Map<String, Driver> DRIVER_CACHE = new HashMap<String, Driver>();

    private static final SimpleDateFormat SMPL_DATE_FMT = new SimpleDateFormat("yyyyMMddhhmmss"); //$NON-NLS-1$

    private static final String CHARS_TO_REMOVE = "/"; //$NON-NLS-1$

    private static final String REPLACEMENT_CHARS = "_"; //$NON-NLS-1$

    private static final String DRIVER_EXTENSION_POINT_ID = "org.talend.metadata.managment.DBDriver_extension"; //$NON-NLS-1$

    private static final String TOP_DRIVER_EXTENSION_ID = "org.talend.dataprofiler.core.TOPDriverService"; //$NON-NLS-1$

    private static List<String> sybaseDBProductsNames;

    // MOD by zshen don't needed can replaced by ExtractMetaDataUtils.metadataCon
    // private static IMetadataConnection metadataCon;

    private static Driver derbyDriver;

    public static final String FAKE_SCHEMA_SYNONYMS = "AllSynonyms"; //$NON-NLS-1$

    /**
     * check a Connection and at last will close the connection.
     * 
     * @param metadataBean
     * @return
     */
    public static TypedReturnCode<java.sql.Connection> checkConnection(IMetadataConnection metadataBean) {
        return createConnection(metadataBean, true);
    }

    /**
     * create a Connection and without close it.
     * 
     * @param metadataBean
     * @return
     */
    public static TypedReturnCode<java.sql.Connection> createConnection(IMetadataConnection metadataBean) {
        return createConnection(metadataBean, false);
    }

    /**
     * create a Connection with the parameter whether close it.
     * 
     * @param metadataBean
     * @param boolean close Connection or not
     * @return
     */
    public static TypedReturnCode<java.sql.Connection> createConnection(IMetadataConnection metadataBean,
            boolean closeConnection) {
        TypedReturnCode<java.sql.Connection> rc = new TypedReturnCode<java.sql.Connection>();
        rc.setOk(false);
        if (metadataBean == null) {
            rc.setMessage("connection information can not be null"); //$NON-NLS-1$
            return rc;
        }

        if (EDatabaseTypeName.HIVE.getXmlName().equalsIgnoreCase(metadataBean.getDbType())) {
            try {
                // HiveConnectionManager.getInstance().checkConnection(metadataBean);
                java.sql.Connection createConnection = HiveConnectionManager.getInstance()
                        .createConnection(metadataBean);
                rc.setOk(true);
                rc.setObject(createConnection);
                rc.setMessage("Check hive connection successful!"); //$NON-NLS-1$
            } catch (ClassNotFoundException e) {
                rc.setOk(false);
                rc.setMessage("Check hive connection failed!"); //$NON-NLS-1$
                CommonExceptionHandler.process(e);
            } catch (InstantiationException e) {
                rc.setOk(false);
                rc.setMessage("Check hive connection failed!"); //$NON-NLS-1$
                CommonExceptionHandler.process(e);
            } catch (IllegalAccessException e) {
                rc.setOk(false);
                rc.setMessage("Check hive connection failed!"); //$NON-NLS-1$
                CommonExceptionHandler.process(e);
            } catch (SQLException e) {
                rc.setOk(false);
                rc.setMessage("Check hive connection failed!"); //$NON-NLS-1$
                CommonExceptionHandler.process(e);
            }
        } else if (EDatabaseTypeName.HBASE.getXmlName().equalsIgnoreCase(metadataBean.getDbType())) {
            rc.setOk(true);
        } else {
            String dbUrl = metadataBean.getUrl();
            if (ConnectionUtils.isHsql(dbUrl)) {
                dbUrl = ConnectionUtils.addShutDownForHSQLUrl(dbUrl, metadataBean.getAdditionalParams());
            }
            // TDQ-8893 Don't use ConnectionUtils.createConnection(...) to get a java sql connetcion,replace it with
            // ExtractMetaDataUtils.connect(...).
            java.sql.Connection sqlConn = null;
            List<Object> list = new ArrayList<Object>();
            try {
                list = ExtractMetaDataUtils.getInstance().connect(metadataBean.getDbType(), metadataBean.getUrl(),
                        metadataBean.getUsername(), metadataBean.getPassword(), metadataBean.getDriverClass(),
                        metadataBean.getDriverJarPath(), metadataBean.getDbVersionString(),
                        metadataBean.getAdditionalParams());
            } catch (Exception e) {
                rc.setMessage("fail to connect database!"); //$NON-NLS-1$
                CommonExceptionHandler.process(e);
                return rc;
            }

            Driver driver = null;
            if (!list.isEmpty()) {
                for (int i = 0; i < list.size(); i++) {
                    if (list.get(i) instanceof Driver) {
                        driver = (Driver) list.get(i);
                    }
                    if (list.get(i) instanceof java.sql.Connection) {
                        sqlConn = (java.sql.Connection) list.get(i);
                    }
                }
                ReturnCode varc = ConnectionUtils.isValid(sqlConn);
                if (varc.isOk()) {
                    derbyDriver = null;
                    if (driver != null
                            && isDerbyRelatedDb(metadataBean.getDriverClass(), metadataBean.getDbType())) {
                        DBConnectionFillerImpl.setDriver(driver);
                        derbyDriver = driver;
                    }
                    rc.setObject(sqlConn);
                    rc.setMessage(varc.getMessage());
                    rc.setOk(true);
                }
            }
        }

        if (closeConnection) {
            if (rc.getObject() != null) {
                ConnectionUtils.closeConnection(rc.getObject());
            }
        }

        return rc;
    }

    /**
     * check a Connection and will at last close connection.
     * 
     * @param databaseConnection
     * @return
     */
    public static TypedReturnCode<java.sql.Connection> checkConnection(DatabaseConnection databaseConnection) {
        return createConnection(databaseConnection, true);
    }

    /**
     * create a Connection and will at last not close connection.
     * 
     * @param databaseConnection
     * @return
     */
    public static TypedReturnCode<java.sql.Connection> createConnection(DatabaseConnection databaseConnection) {
        return createConnection(databaseConnection, false);
    }

    /**
     * create a Connection whether close it depends on closeConnection.
     * 
     * @param databaseConnection
     * @param closeConnection
     * @return
     */
    public static TypedReturnCode<java.sql.Connection> createConnection(DatabaseConnection databaseConnection,
            boolean closeConnection) {
        IMetadataConnection metadataConnection;
        if (EDatabaseTypeName.HIVE.getXmlName().equalsIgnoreCase(databaseConnection.getDatabaseType())) {
            metadataConnection = ConvertionHelper.convert(databaseConnection);
        } else {
            metadataConnection = new MetadataConnection();
            String dbUrl = databaseConnection.getURL();
            String password = databaseConnection.getRawPassword();
            String userName = databaseConnection.getUsername();
            String dbType = databaseConnection.getDatabaseType();
            String driverClass = databaseConnection.getDriverClass() == null
                    ? EDatabase4DriverClassName.getDriverClassByDbType(dbType)
                    : databaseConnection.getDriverClass();
            String driverJarPath = databaseConnection.getDriverJarPath();
            String dataBase = databaseConnection.getSID();
            String dbVersionString = databaseConnection.getDbVersionString();
            String additionalParams = databaseConnection.getAdditionalParams();

            // MOD qiongli 2011-9-6,TDQ 3317.handle context mode
            if (databaseConnection.isContextMode()) {
                IRepositoryContextService repositoryContextService = CoreRuntimePlugin.getInstance()
                        .getRepositoryContextService();
                if (repositoryContextService != null) {
                    String contextName = databaseConnection.getContextName();
                    DatabaseConnection origValueConn = null;
                    if (contextName == null) {
                        origValueConn = repositoryContextService.cloneOriginalValueConnection(databaseConnection,
                                true);
                    } else {
                        origValueConn = repositoryContextService.cloneOriginalValueConnection(databaseConnection,
                                false, contextName);
                    }

                    if (origValueConn != null) {
                        dbUrl = origValueConn.getURL();
                        password = origValueConn.getRawPassword();
                        userName = origValueConn.getUsername();
                        driverClass = origValueConn.getDriverClass();
                        driverJarPath = origValueConn.getDriverJarPath();
                        dbType = origValueConn.getDatabaseType();
                        dataBase = origValueConn.getSID();
                        dbVersionString = origValueConn.getDbVersionString();
                        additionalParams = origValueConn.getAdditionalParams();
                    }

                }
            } // ~

            metadataConnection.setAdditionalParams(additionalParams);
            metadataConnection.setDbVersionString(dbVersionString);
            metadataConnection.setDatabase(dataBase);
            metadataConnection.setDbType(dbType);
            metadataConnection.setDriverJarPath(driverJarPath);
            metadataConnection.setDriverClass(driverClass);
            metadataConnection.setUsername(userName);
            metadataConnection.setPassword(password);
            metadataConnection.setUrl(dbUrl);
        }
        return createConnection(metadataConnection, closeConnection);
    }

    /**
     * DOC xqliu Comment method "isOdbcPostgresql".
     * 
     * @param connection
     * @return
     * @throws SQLException
     */
    public static boolean isOdbcPostgresql(java.sql.Connection connection) throws SQLException {
        return isOdbcPostgresql(ExtractMetaDataUtils.getInstance().getConnectionMetadata(connection));
    }

    public static boolean isOdbcPostgresql(DatabaseMetaData connectionMetadata) throws SQLException {
        if (connectionMetadata.getDriverName() != null
                && connectionMetadata.getDriverName().toLowerCase().startsWith(DatabaseConstant.ODBC_DRIVER_NAME)
                && connectionMetadata.getDatabaseProductName() != null
                && connectionMetadata.getDatabaseProductName().toLowerCase()
                        .indexOf(DatabaseConstant.POSTGRESQL_PRODUCT_NAME) > -1) {
            return true;
        }
        return false;
    }

    /**
     * DOC zshen Comment method "isOdbcMssql". feature 10630
     * 
     * @param connection
     * @return
     * @throws SQLException
     */
    public static boolean isOdbcExcel(java.sql.Connection connection) throws SQLException {
        return isOdbcExcel(ExtractMetaDataUtils.getInstance().getConnectionMetadata(connection));
    }

    public static boolean isOdbcExcel(DatabaseMetaData connectionMetadata) throws SQLException {
        if (connectionMetadata.getDriverName() != null
                && connectionMetadata.getDriverName().toLowerCase().startsWith(DatabaseConstant.ODBC_DRIVER_NAME)
                && connectionMetadata.getDatabaseProductName() != null
                && connectionMetadata.getDatabaseProductName().equals(DatabaseConstant.ODBC_EXCEL_PRODUCT_NAME)) {
            return true;
        }
        return false;
    }

    /**
     * 
     * DOC qiongli Comment method "isAccess".
     * 
     * @param connection
     * @return
     * @throws SQLException
     */
    public static boolean isAccess(java.sql.Connection connection) throws SQLException {
        return isAccess(ExtractMetaDataUtils.getInstance().getConnectionMetadata(connection));
    }

    public static boolean isAccess(DatabaseMetaData connectionMetadata) throws SQLException {
        if (connectionMetadata.getDriverName() != null && connectionMetadata.getDatabaseProductName() != null
                && connectionMetadata.getDatabaseProductName().equals(DatabaseConstant.MS_ACCESS_PRODUCT_NAME)) {
            return true;
        }
        return false;
    }

    /**
     * DOC zshen Comment method "isSybase".
     * 
     * @param connection
     * @return decide to whether is sybase connection
     * @throws SQLException
     */
    public static boolean isSybase(java.sql.Connection connection) throws SQLException {
        return isSybase(ExtractMetaDataUtils.getInstance().getConnectionMetadata(connection));
    }

    public static boolean isSybase(DatabaseMetaData connectionMetadata) throws SQLException {
        if (connectionMetadata instanceof SybaseDatabaseMetaData) {
            return true;
        }
        if (connectionMetadata.getDriverName() != null && connectionMetadata.getDatabaseProductName() != null) {
            for (String keyString : getSybaseDBProductsName()) {
                if (keyString.trim().equals(connectionMetadata.getDatabaseProductName().trim())) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * DOC xqliu Comment method "isTeradata".
     * 
     * @param connection
     * @return
     */
    public static boolean isTeradata(DatabaseConnection connection) {
        EDatabaseTypeName dbType = EDatabaseTypeName.getTypeFromDbType(connection.getDatabaseType());
        if (dbType == EDatabaseTypeName.TERADATA) {
            return true;
        }
        return false;
    }

    public static boolean isTeradataSQLMode(IMetadataConnection metadataConnection) {
        if (metadataConnection != null) {
            Object connection = metadataConnection.getCurrentConnection();
            if (connection != null && connection instanceof DatabaseConnection) {
                DatabaseConnection dbConn = (DatabaseConnection) connection;
                if (dbConn != null && isTeradata(dbConn) && dbConn.isSQLMode()) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * zshen Comment method "isOdbcConnection". feature 10630
     * 
     * @param connection
     * @return
     * @throws SQLException
     */
    public static boolean isOdbcConnection(DatabaseMetaData connectionMetadata) throws SQLException {
        if (connectionMetadata.getDriverName() != null
                && connectionMetadata.getDriverName().toLowerCase().startsWith(DatabaseConstant.ODBC_DRIVER_NAME)) {
            return true;
        }
        return false;
    }

    public static boolean isOdbcConnection(java.sql.Connection connection) throws SQLException {
        return isOdbcConnection(ExtractMetaDataUtils.getInstance().getConnectionMetadata(connection));
    }

    /**
     * zshen Comment method "isODBCCatalog".
     * 
     * @param catalogName the name for need to be decided.
     * @param connection
     * @return if connection is a ODBC connection and catalogName isn't which be found then return ture, else return
     * true.
     * @throws SQLException
     */
    public static boolean isODBCCatalog(String catalogName, DatabaseMetaData connectionMetadata)
            throws SQLException {
        if (isOdbcConnection(connectionMetadata)) {
            String userCatalogName = connectionMetadata.getConnection().getCatalog();
            if (catalogName != null && (catalogName.equals(userCatalogName) || userCatalogName == null
                    || "null".equals(userCatalogName))) { //$NON-NLS-1$

                return true;
            } else {
                return false;
            }
        }
        return true;
    }

    public static boolean isOracle8i(Connection connection) {
        if (connection != null && connection instanceof DatabaseConnection) {
            DatabaseConnection dbConn = (DatabaseConnection) connection;
            String version = dbConn.getDbVersionString();
            if (version != null && version.equals("ORACLE_8")) { //$NON-NLS-1$
                return true;
            }
        }
        return false;
    }

    public static boolean isOracle(Connection connection) {
        if (connection != null && connection instanceof DatabaseConnection) {
            DatabaseConnection dbConn = (DatabaseConnection) connection;
            if (EDatabaseTypeName.ORACLEFORSID.getDisplayName().equals(dbConn.getDatabaseType())
                    || EDatabaseTypeName.ORACLESN.getDisplayName().equals(dbConn.getDatabaseType())
                    || EDatabaseTypeName.ORACLE_CUSTOM.getDisplayName().equals(dbConn.getDatabaseType())
                    || EDatabaseTypeName.ORACLE_OCI.getDisplayName().equals(dbConn.getDatabaseType())) {
                return true;
            }
        }
        return false;
    }

    public static boolean isOracleJDBC(Connection connection) {
        if (connection != null && connection instanceof DatabaseConnection) {
            DatabaseConnection dbConn = (DatabaseConnection) connection;
            if (EDatabaseTypeName.GENERAL_JDBC.getDisplayName().equals(dbConn.getDatabaseType())) {
                if (dbConn.getDriverClass() != null
                        && dbConn.getDriverClass().startsWith(DatabaseConstant.ODBC_ORACLE_PRODUCT_NAME)) {
                    return true;
                }
            }
        }
        return false;
    }

    public static boolean isMssql(DatabaseMetaData connectionMetadata) throws SQLException {
        if (connectionMetadata.getDriverName() != null && connectionMetadata.getDatabaseProductName() != null) {
            if (EDataBaseType.Microsoft_SQL_Server.getProductName()
                    .equals(connectionMetadata.getDatabaseProductName().trim())) {
                return true;

            }
        }
        return false;
    }

    public static boolean isMssql(java.sql.Connection connection) throws SQLException {
        return isMssql(ExtractMetaDataUtils.getInstance().getConnectionMetadata(connection));
    }

    public static boolean isMysql(java.sql.Connection connection) throws SQLException {
        return isMysql(ExtractMetaDataUtils.getInstance().getConnectionMetadata(connection));
    }

    public static boolean isMysql(DatabaseMetaData connectionMetadata) throws SQLException {
        if (connectionMetadata.getDriverName() != null && connectionMetadata.getDatabaseProductName() != null) {
            if (EDataBaseType.MySQL.getProductName().equals(connectionMetadata.getDatabaseProductName().trim())) {
                return true;

            }
        }
        return false;
    }

    /**
     * yyi 2010-08-25 for 14851, Sybase DB has several names with different productions and versions. For example the
     * Sybase IQ with version 12.6 is called 'Sybase' getting by JDBC but the version 15+ it is changed to 'Sybase IQ'.
     * it is user by org.talend.cwm.db.connection.ConnectionUtils.isSybase
     * 
     * @return All Sybase DB products name
     * ,"Adaptive Server Enterprise","Sybase Adaptive Server IQ","Sybase IQ","Sybase"
     */
    public static String[] getSybaseDBProductsName() {
        if (null == sybaseDBProductsNames) {
            sybaseDBProductsNames = new ArrayList<String>();
            sybaseDBProductsNames.add("Adaptive Server Enterprise"); //$NON-NLS-1$
            sybaseDBProductsNames.add(" Sybase Adaptive Server IQ"); //$NON-NLS-1$
            sybaseDBProductsNames.add("Sybase"); //$NON-NLS-1$
            sybaseDBProductsNames.add("Sybase IQ"); //$NON-NLS-1$
            sybaseDBProductsNames.add("Adaptive Server Enterprise | Sybase Adaptive Server IQ"); //$NON-NLS-1$
        }
        return sybaseDBProductsNames.toArray(new String[sybaseDBProductsNames.size()]);
    }

    /**
     * DOC qzhang Comment method "getClassDriver".
     * 
     * @param driverClassName
     * @return
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws ClassNotFoundException
     */
    public static Driver getClassDriver(IMetadataConnection metadataBean)
            throws InstantiationException, IllegalAccessException, ClassNotFoundException, RuntimeException {
        String driverClassName = metadataBean.getDriverClass();
        // MOD mzhao 2009-06-05,Bug 7571 Get driver from catch first, if not
        // exist then get a new instance.
        if (driverClassName.equals("org.apache.derby.jdbc.EmbeddedDriver")) { //$NON-NLS-1$
            DRIVER_CACHE.remove(driverClassName);
        } else if (EDatabase4DriverClassName.HIVE.getDriverClass().equals(driverClassName)) {
            // Added by Marvin Wang on Dec. 6, 2012 for bug TDI-24027, for hive it should load all driver jars each time
            // no matter embedded or standalone.
            DRIVER_CACHE.remove(driverClassName);
        }
        Driver driver = DRIVER_CACHE.get(driverClassName);

        if (!Platform.isRunning()) {
            driver = (Driver) Class.forName(driverClassName).newInstance();
        }

        // The case for generalJDBC
        String driverPath = metadataBean.getDriverJarPath();

        if (StringUtils.isEmpty(driverPath)) {
            if (driver != null) {
                return driver;
            }
        }

        driver = getDriver(metadataBean);

        // MOD mzhao 2009-06-05,Bug 7571 Get driver from catch first, if not
        // exist then get a new instance.
        if (driver != null) {
            DRIVER_CACHE.put(driverClassName, driver);
        }
        return driver;
    }

    public static Map<String, Driver> getDriverCache() {
        return DRIVER_CACHE;
    }

    /**
     * get driver.
     * 
     * @param metadataBean
     * @param driver
     * @return
     * @throws Exception
     */
    private static Driver getDriver(IMetadataConnection metadataBean) {
        Driver driver = null;
        List<?> connList = null;
        try {
            connList = getConnection(metadataBean);
            if (connList != null && !connList.isEmpty()) { // FIXME unnecessary check !connList.isEmpty()
                // FIXME scorreia 2011-03-31 why do we loop here? Is it possible to have several drivers. If
                // yes,
                // what do we do?
                for (int i = 0; i < connList.size(); i++) {
                    if (connList.get(i) instanceof Driver) {
                        driver = (DriverShim) connList.get(i); // FIXME scorreia 2011-03-31 strange cast here.
                    }
                }
            }
        } catch (MissingDriverException e) {
            throw e;
        } catch (Exception e) {
            log.error(e, e);
            // Added 20130521 TUP-735 yyin,when connection error should throw the exception to transfer the error
            // message
            throw new RuntimeException(e);
        } finally {
            // ADD msjian TDQ-5952: we should close the unused connection at once.
            if (connList != null && !connList.isEmpty()) {
                for (int i = 0; i < connList.size(); i++) {
                    if (connList.get(i) instanceof java.sql.Connection) {
                        java.sql.Connection con = (java.sql.Connection) connList.get(i);
                        ConnectionUtils.closeConnection(con);
                    }
                }
            }
            // TDQ-5952~
        }
        return driver;
    }

    /**
     * This method to get all database template supported by TDQ.
     * 
     * @return
     */
    public static List<String> getTDQSupportDBTemplate() {

        try {
            if (GlobalServiceRegister.getDefault().isDQDriverServiceRegistered(IDriverService.class)) {
                IDriverService driverService = (IDriverService) GlobalServiceRegister.getDefault()
                        .getDQDriverService(IDriverService.class);

                return driverService.getTDQSupportDBTemplate();
            }
        } catch (Exception e) {
            log.error(e, e);
        }
        return new ArrayList<String>();
    }

    /**
     * This method try to return whether the conn is supported by TDQ.
     * 
     * @return
     */
    public static boolean isTDQSupportDBTemplate(Connection conn) {
        if (conn == null) {
            return false;
        }
        try {
            if (GlobalServiceRegister.getDefault().isDQDriverServiceRegistered(IDriverService.class)) {
                IDriverService driverService = (IDriverService) GlobalServiceRegister.getDefault()
                        .getDQDriverService(IDriverService.class);
                if (conn instanceof DatabaseConnection) {
                    String databaseType = ((DatabaseConnection) conn).getDatabaseType();
                    return driverService.getTDQSupportDBTemplate().contains(databaseType);
                }
            }
        } catch (Exception e) {
            log.error(e, e);
        }

        return false;
    }

    /**
     * 
     * DOC zshen Comment method "getCommonQueryStr".
     * 
     * @param sqlConn
     * @return null only if conn is null.or can not find the
     * @deprecated {@link #getCommentQueryStr(String, String, String)} instead.
     */
    @Deprecated
    public static String getCommonQueryStr(String productName, String tableName) {
        if (productName == null) {
            return null;
        }
        productName = productName.replaceAll(" ", "_"); //$NON-NLS-1$ //$NON-NLS-2$
        EDataBaseType eDataBaseType = null;
        try {
            eDataBaseType = EDataBaseType.valueOf(productName);
        } catch (Exception e) {
            eDataBaseType = EDataBaseType.Microsoft_SQL_Server;
        }
        String sqlStr = ""; //$NON-NLS-1$
        switch (eDataBaseType) {
        case Oracle:
            sqlStr = "SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME='" + tableName + "'"; //$NON-NLS-1$ //$NON-NLS-2$
            break;
        case MySQL:
            sqlStr = "SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_NAME='" + tableName + "'"; //$NON-NLS-1$ //$NON-NLS-2$
            break;
        default:
            sqlStr = null;

        }
        return sqlStr;

    }

    /**
     * get Comment Query Str.
     * 
     * @param productName
     * @param tableName
     * @param catalogName
     * @param schemaPattern
     * @return null only if conn is null.or can not find
     */
    public static String getCommentQueryStr(String productName, String tableName, String catalogName,
            String schemaPattern) {
        if (productName == null) {
            return null;
        }
        productName = productName.replaceAll(" ", "_"); //$NON-NLS-1$ //$NON-NLS-2$
        EDataBaseType eDataBaseType = null;
        try {
            eDataBaseType = EDataBaseType.valueOf(productName);
        } catch (Exception e) {
            eDataBaseType = EDataBaseType.Microsoft_SQL_Server;
        }
        String sqlStr = ""; //$NON-NLS-1$
        switch (eDataBaseType) {
        case Oracle:
            sqlStr = "SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE TABLE_NAME='" + tableName + "' AND OWNER='" //$NON-NLS-1$//$NON-NLS-2$
                    + schemaPattern.toUpperCase() + "'"; //$NON-NLS-1$
            break;
        case MySQL:
            sqlStr = "SELECT TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_NAME='" + tableName //$NON-NLS-1$
                    + "' AND TABLE_SCHEMA='" + catalogName + "'"; //$NON-NLS-1$ //$NON-NLS-2$
            break;
        default:
            sqlStr = null;

        }
        return sqlStr;

    }

    /**
     * DOC xqliu Comment method "createDataType".
     * 
     * @param dataType
     * @param typeName
     * @param decimalDigits
     * @param numPrecRadix
     * @return
     */
    public static TdSqlDataType createDataType(int dataType, String typeName, int decimalDigits, int numPrecRadix) {
        TdSqlDataType sqlDataType = RelationalFactory.eINSTANCE.createTdSqlDataType();
        sqlDataType.setName(typeName);
        sqlDataType.setJavaDataType(dataType);
        sqlDataType.setNumericPrecision(decimalDigits);
        sqlDataType.setNumericPrecisionRadix(numPrecRadix);
        return sqlDataType;
    }

    /**
     * 
     * zshen Comment method "getXtentisBindingStub".
     * 
     * @param metadataBean
     * @return
     * @throws ServiceException
     * @noreference This method is not intended to be referenced by clients.
     */
    public static XtentisBindingStub getXtentisBindingStub(IMetadataConnection metadataBean)
            throws ServiceException {
        return getXtentisBindingStub(metadataBean.getUrl(), metadataBean.getUniverse(), metadataBean.getUsername(),
                metadataBean.getPassword());
    }

    /**
     * 
     * zshen Comment method "getXtentisBindingStub".
     * 
     * @param dataProvider
     * @return
     * @throws ServiceException
     */
    public static XtentisBindingStub getXtentisBindingStub(MDMConnection dataProvider) throws ServiceException {
        return getXtentisBindingStub(dataProvider.getPathname(), ConnectionHelper.getUniverse(dataProvider),
                dataProvider.getUsername(), dataProvider.getValue(dataProvider.getPassword(), false));
    }

    /**
     * 
     * zshen Comment method "getXtentisBindingStub".
     * 
     * @param url
     * @param universe
     * @param userName
     * @param password
     * @return
     * @throws ServiceException
     */
    private static XtentisBindingStub getXtentisBindingStub(String url, String universe, String userName,
            String password) throws ServiceException {

        // initialization Web Service calling
        XtentisServiceLocator xtentisService = new XtentisServiceLocator();
        xtentisService.setXtentisPortEndpointAddress(url);
        XtentisPort_PortType xtentisWS = xtentisService.getXtentisPort();
        XtentisBindingStub stub = (XtentisBindingStub) xtentisWS;

        // authorization
        if (universe == null || universe.trim().length() == 0) {
            stub.setUsername(userName);
        } else {
            stub.setUsername(universe + "/" + userName); //$NON-NLS-1$
        }
        stub.setPassword(password);
        return stub;
    }

    public static void main(String[] args) {
        MetadataConnectionUtils.getCommonQueryStr(EDataBaseType.Microsoft_SQL_Server.getProductName(), "tableName"); //$NON-NLS-1$
    }

    /**
     * Method "createTechnicalName" creates a technical name used for file system storage. MOD mzhao make this method as
     * public access.
     * 
     * @param functionalName the user friendly name
     * @return the technical name created from the user given name.
     */
    public static String createTechnicalName(final String functionalName) {
        String techname = "no_name"; //$NON-NLS-1$
        if (functionalName == null) {
            log.warn("A functional name should not be null"); //$NON-NLS-1$
            return techname;
        }
        // encode in base 64 so that all characters such white spaces, accents, everything that is dangerous when used
        // for file names are removed
        try {
            // encode
            String b64 = new String(Base64.encodeBase64(functionalName.getBytes()), "UTF-8"); //$NON-NLS-1$
            // replace special characters
            String date = SMPL_DATE_FMT.format(new Date(System.currentTimeMillis()));
            techname = AsciiUtils.replaceCharacters(b64, CHARS_TO_REMOVE, REPLACEMENT_CHARS) + date;
        } catch (UnsupportedEncodingException e) {
            log.error(e, e);
        } // .replaceAll(B64ID, PREFIX);
        if (log.isDebugEnabled()) {
            log.debug("Functional name: " + functionalName + " -> techname: " + techname); //$NON-NLS-1$ //$NON-NLS-2$
        }
        return techname;
    }

    public static List<String> getPackageFilter(Connection connection, DatabaseMetaData dbMetaData,
            boolean isCatalog) {
        List<String> packageFilter = new ArrayList<String>();
        try {
            if (isMdmConnection(connection)) {
                // MDMConnection mdmConnection = (MDMConnection) connection;
            } else {
                DatabaseConnection dbConnection = (DatabaseConnection) connection;
                // MOD qiongli 2011-9-23 handle context mod.
                IRepositoryContextService repositoryContextService = null;
                DatabaseConnection origValueConn = null;
                if (dbConnection.isContextMode()) {
                    repositoryContextService = CoreRuntimePlugin.getInstance().getRepositoryContextService();
                    if (repositoryContextService != null) {
                        // get the original value and select the defalut context
                        String contextName = dbConnection.getContextName();
                        origValueConn = repositoryContextService.cloneOriginalValueConnection(dbConnection,
                                contextName == null ? true : false, contextName);
                    }
                }
                if (isCatalog) {
                    boolean isHsql = dbConnection.getDatabaseType()
                            .equals(EDatabaseTypeName.HSQLDB_IN_PROGRESS.getDisplayName());
                    boolean isInformix = dbConnection.getDatabaseType()
                            .equalsIgnoreCase(EDatabaseTypeName.INFORMIX.name());
                    if (dbMetaData.supportsCatalogsInIndexDefinitions() && !isHsql || isInformix) {
                        String sid = dbConnection.getSID();
                        if (origValueConn != null) {
                            sid = origValueConn.getSID();
                        }
                        if (!StringUtils.isEmpty(sid) && !packageFilter.contains(sid)) {
                            packageFilter.add(sid);
                        }
                    }
                } else {
                    if (dbMetaData.supportsSchemasInIndexDefinitions()) {
                        String uiSchema = dbConnection.getUiSchema();
                        if (origValueConn != null) {
                            uiSchema = origValueConn.getUiSchema();
                        }
                        if (!StringUtils.isEmpty(uiSchema) && !packageFilter.contains(uiSchema)) {
                            packageFilter.add(uiSchema);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            log.error(e, e);
        }
        return packageFilter;
    }

    public static boolean isMdmConnection(DataProvider dataprovider) {
        return dataprovider instanceof MDMConnection;
    }

    public static Connection fillConnectionInformation(ConnectionItem connItem) {
        return fillConnectionInformation(connItem, null);
    }

    /**
     * DOC connection created by TOS need to fill the basic information for useing in TOP.<br>
     * 
     * 
     * @param conn
     * @return
     */
    public static Connection fillConnectionInformation(ConnectionItem connItem,
            IMetadataConnection metadataConnection) {
        boolean saveFlag = false;
        Connection conn = connItem.getConnection();
        // // fill metadata of connection
        // if (conn.getName() == null || conn.getLabel() == null) {
        // saveFlag = true;
        // conn = fillConnectionMetadataInformation(conn);
        // }
        // fill structure of connection
        List<Catalog> catalogs = ConnectionHelper.getCatalogs(conn);
        List<Schema> schemas = ConnectionHelper.getSchema(conn);
        // MOD xqliu 2010-10-19 bug 16441: case insensitive
        boolean isNeedToFill = false;
        if (conn instanceof DatabaseConnection) {
            String dbProductID = ((DatabaseConnection) conn).getProductId();
            if (ConnectionHelper.getAllSchemas(conn).isEmpty()
                    && (EDatabaseTypeName.MSSQL05_08.getProduct().equals(dbProductID)
                            || EDatabaseTypeName.MSSQL.getProduct().equals(dbProductID))) {
                isNeedToFill = true;
            } else if (EDatabaseTypeName.AS400.getProduct().equals(dbProductID)) {
                isNeedToFill = true;
            } else if (EDatabaseTypeName.PSQL.getProduct().equals(dbProductID)) {
                isNeedToFill = true;
            }
        }
        if ((catalogs.isEmpty() && schemas.isEmpty()) || isNeedToFill) {

            // ~ 16441
            DatabaseConnection dbConn = SwitchHelpers.DATABASECONNECTION_SWITCH.doSwitch(conn);
            if (dbConn != null) {
                saveFlag = true;

                conn = fillDbConnectionInformation(dbConn, metadataConnection);
            }
            // else {
            // MDMConnection mdmConn = SwitchHelpers.MDMCONNECTION_SWITCH.doSwitch(conn);
            // if (mdmConn != null) {
            // if (mdmConn.getDataPackage().isEmpty()) {
            // saveFlag = true;
            // conn = fillMdmConnectionInformation(mdmConn);
            // }
            // }
            // }
        }
        if (saveFlag && conn != null) {
            try {
                ProxyRepositoryFactory.getInstance().save(connItem);
            } catch (PersistenceException e) {
                log.error(e, e);
            }
        }
        // updateRetrieveAllFlag(conn);
        return conn;
    }

    /**
     * 
     * DOC zshen Comment method "setMDMConnectionParameter".
     * 
     * @param conn
     * @param metadataConnection
     * 
     * set the parameter of MDMConnection from metadataConnection
     */
    public static void setMDMConnectionParameter(MDMConnection conn, IMetadataConnection metadataConnection) {
        if (conn == null || metadataConnection == null) {
            return;
        }
        // host
        conn.setServer(metadataConnection.getServerName());

        // port
        conn.setPort(metadataConnection.getPort());
        // password
        ConnectionHelper.setPassword(conn, metadataConnection.getPassword());
        // user
        conn.setUsername(metadataConnection.getUsername());
        conn.setComment(metadataConnection.getComment());
        conn.setId(metadataConnection.getId());
        conn.setLabel(metadataConnection.getLabel());
        conn.setUniverse(metadataConnection.getUniverse());
        conn.setDatamodel(metadataConnection.getDatamodel());
        conn.setDatacluster(metadataConnection.getDatacluster());

    }

    /**
     * DOC xqliu Comment method "fillDbConnectionInformation".
     * 
     * @param dbConn
     * @return
     */
    public static DatabaseConnection fillDbConnectionInformation(DatabaseConnection dbConn,
            IMetadataConnection metadataConnection) {

        boolean noStructureExists = ConnectionHelper.getAllCatalogs(dbConn).isEmpty()
                && ConnectionHelper.getAllSchemas(dbConn).isEmpty();

        java.sql.Connection sqlConn = null;
        // only for derby related driver
        try {
            if (noStructureExists) { // do no override existing catalogs or
                                     // schemas
                                     // Map<String, String> paramMap =
                                     // ParameterUtil.toMap(ConnectionUtils.createConnectionParam(dbConn));
                IMetadataConnection metaConnection = metadataConnection;
                if (metadataConnection == null) {
                    metaConnection = ConvertionHelper.convert(dbConn);
                }
                EDatabaseTypeName currentEDatabaseType = EDatabaseTypeName
                        .getTypeFromDbType(metaConnection.getDbType());
                if (currentEDatabaseType != null) {
                    MetadataFillFactory dbInstance = MetadataFillFactory.getDBInstance(metaConnection);
                    dbInstance.fillUIConnParams(metaConnection, dbConn);
                    sqlConn = MetadataConnectionUtils.createConnection(metaConnection).getObject();
                    if (sqlConn != null) {
                        DatabaseMetaData databaseMetaData = null;
                        // Added by Marvin Wang on Mar. 21, 2013 for loading hive jars dynamically, refer to TDI-25072.
                        if (EDatabaseTypeName.HIVE.getXmlName().equalsIgnoreCase(metaConnection.getDbType())) {
                            databaseMetaData = HiveConnectionManager.getInstance()
                                    .extractDatabaseMetaData(metaConnection);
                        } else {
                            databaseMetaData = ExtractMetaDataUtils.getInstance().getDatabaseMetaData(sqlConn,
                                    dbConn, false);
                        }

                        if (sqlConn != null) {
                            dbInstance.fillCatalogs(dbConn, databaseMetaData, metaConnection,
                                    MetadataConnectionUtils.getPackageFilter(dbConn, databaseMetaData, true));
                            dbInstance.fillSchemas(dbConn, databaseMetaData, metaConnection,
                                    MetadataConnectionUtils.getPackageFilter(dbConn, databaseMetaData, false));
                        }
                    }
                }
            }
        } catch (Exception e) {
            log.error(e, e);
        } finally {
            if (sqlConn != null) {
                ConnectionUtils.closeConnection(sqlConn);
            }
            closeDerbyDriver();
        }
        return dbConn;
    }

    /**
     * DOC PLV Comment method "closeDerbyDriver". shutdown derby
     */
    public static void closeDerbyDriver() {
        if (derbyDriver != null) {
            try {
                derbyDriver.connect("jdbc:derby:;shutdown=true", null); //$NON-NLS-1$
            } catch (SQLException e) {
                // exception of shutdown success. no need to catch.
            }
        }
    }

    public static boolean isPostgresql(java.sql.Connection connection) throws SQLException {
        return isPostgresql(ExtractMetaDataUtils.getInstance().getConnectionMetadata(connection));
    }

    public static boolean isPostgresql(DatabaseMetaData connectionMetadata) throws SQLException {
        if (connectionMetadata != null) {
            String databaseProductName = connectionMetadata.getDatabaseProductName();
            if (databaseProductName != null) {
                return databaseProductName.toLowerCase().indexOf(DatabaseConstant.POSTGRESQL_PRODUCT_NAME) > -1;
            }
        }
        return false;
    }

    public static boolean isDerbyRelatedDb(String driverClassname, String dbType) {
        return (driverClassname != null
                && driverClassname.equals(EDatabase4DriverClassName.JAVADB_EMBEDED.getDriverClass()))
                || (dbType != null && dbType.equals(EDatabaseTypeName.JAVADB_EMBEDED.getDisplayName())
                        || dbType.equals(EDatabaseTypeName.JAVADB_DERBYCLIENT.getDisplayName())
                        || dbType.equals(EDatabaseTypeName.JAVADB_DERBYCLIENT.getDisplayName())
                        || dbType.equals(EDatabaseTypeName.JAVADB_JCCJDBC.getDisplayName())
                        || dbType.equals(EDatabaseTypeName.HSQLDB_IN_PROGRESS.getDisplayName()));
    }

    public static boolean isHsqlInprocess(IMetadataConnection metadataConnection) {
        if (metadataConnection != null) {
            String dbType = metadataConnection.getDbType();
            if (dbType != null && dbType.equals(EDatabaseTypeName.HSQLDB_IN_PROGRESS.getDisplayName())) {
                return true;
            }
        }
        return false;
    }

    public static boolean isSybase(IMetadataConnection metadataConnection) {
        if (metadataConnection != null) {
            String dbType = metadataConnection.getDbType();
            if (dbType != null && dbType.equals(EDatabaseTypeName.SYBASEASE.getDisplayName())) {
                return true;
            }
        }
        return false;
    }

    public static boolean isGeneralJDBC(IMetadataConnection metadataConnection) {
        if (metadataConnection != null) {
            String dbType = metadataConnection.getDbType();
            if (dbType != null && dbType.equals(EDatabaseTypeName.GENERAL_JDBC.getDisplayName())) {
                return true;
            }
        }
        return false;
    }

    public static boolean isHive(DatabaseMetaData metadata) {
        if (metadata != null) {
            try {
                String name = metadata.getDatabaseProductName();
                if (name != null && name.equals(EDatabaseTypeName.HIVE.getDisplayName())) {
                    return true;
                }
            } catch (SQLException e) {
                ExceptionHandler.process(e);
            }
        }
        return false;
    }

    public static boolean isOracle(DatabaseMetaData metadata) {
        if (metadata != null) {
            try {
                String name = metadata.getDatabaseProductName();
                if (name != null
                        && name.toUpperCase().equals(EDatabaseTypeName.ORACLEFORSID.getProduct().toUpperCase())) {
                    return true;
                }
            } catch (SQLException e) {
                ExceptionHandler.process(e);
            }
        }
        return false;
    }

    public static Driver getDerbyDriver() {
        return derbyDriver;
    }

    /**
     * Check a package is the one of AS400. return false if it's not.
     * 
     * @param packge
     * @return
     */
    public static boolean isAS400(Package packge) {
        Connection connection = ConnectionHelper.getConnection(packge);
        if (connection instanceof DatabaseConnection) {
            DatabaseConnection dbConnection = (DatabaseConnection) connection;
            return StringUtils.equalsIgnoreCase(dbConnection.getDatabaseType(),
                    EDatabaseTypeName.AS400.getDisplayName());
        }

        return false;
    }

    public static List getConnection(IMetadataConnection metadataBean) {
        return ExtractMetaDataUtils.getInstance().getConnection(metadataBean.getDbType(), metadataBean.getUrl(),
                metadataBean.getUsername(), metadataBean.getPassword(), metadataBean.getDatabase(),
                metadataBean.getSchema(), metadataBean.getDriverClass(), metadataBean.getDriverJarPath(),
                metadataBean.getDbVersionString(), metadataBean.getAdditionalParams());
    }

    /**
     * return the MetadataColumn's name, the input object should be MetadataColumn, TdColumn or TdXmlElementType, if
     * not, return null.
     * 
     * @param element MetadataColumn, TdColumn or TdXmlElementType
     * @return
     */
    public static String getMetadataColumnName(ModelElement element) {
        String name = null;

        MetadataColumn mdColumn = SwitchHelpers.METADATA_COLUMN_SWITCH.doSwitch(element);
        TdColumn tdColumn = SwitchHelpers.COLUMN_SWITCH.doSwitch(element);
        TdXmlElementType xmlElementType = SwitchHelpers.XMLELEMENTTYPE_SWITCH.doSwitch(element);

        if (tdColumn != null) {
            name = tdColumn.getName();
        } else if (mdColumn != null) {
            name = mdColumn.getId();
        } else if (xmlElementType != null) {
            name = xmlElementType.getName();
        }

        return name;
    }

}