com.sap.dirigible.repository.ext.db.DBUtils.java Source code

Java tutorial

Introduction

Here is the source code for com.sap.dirigible.repository.ext.db.DBUtils.java

Source

/*******************************************************************************
 * Copyright (c) 2014 SAP AG or an SAP affiliate company. All rights reserved.
 * Licensed under the Apache License, Version 2.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://www.apache.org/licenses/LICENSE-2.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.sap.dirigible.repository.ext.db;

import java.io.BufferedInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.StringTokenizer;

import javax.sql.DataSource;

import org.apache.commons.io.IOUtils;

import com.sap.dirigible.repository.ext.db.dialect.DerbyDBSpecifier;
import com.sap.dirigible.repository.ext.db.dialect.HANADBSpecifier;
import com.sap.dirigible.repository.ext.db.dialect.IDialectSpecifier;
import com.sap.dirigible.repository.ext.db.dialect.PostgreSQLDBSpecifier;
import com.sap.dirigible.repository.ext.db.dialect.SAPDBSpecifier;
import com.sap.dirigible.repository.ext.db.dialect.SybaseDBSpecifier;
import com.sap.dirigible.repository.logging.Logger;

public class DBUtils {

    private static final String PRODUCT_DERBY = "Apache Derby"; //$NON-NLS-1$
    private static final String PRODUCT_SYBASE = "Adaptive Server Enterprise"; //$NON-NLS-1$
    private static final String PRODUCT_SAP_DB = "SAP DB"; //$NON-NLS-1$
    private static final String PRODUCT_HDB = "HDB"; //$NON-NLS-1$
    private static final String PRODUCT_POSTGRESQL = "PostgreSQL"; //$NON-NLS-1$

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

    private static Logger logger = Logger.getLogger(DBUtils.class.getCanonicalName());

    //   private IRepository repository;

    private DataSource dataSource;

    public DBUtils(DataSource dataSource) {
        //      this.repository = repository;
        this.dataSource = dataSource;
    }

    //   IRepository getRepository() {
    //      return repository;
    //   }

    /**
     * Read whole SQL script from the class path. It can contain multiple
     * statements separated with ';'
     * 
     * @param path
     * @return the SQL script as a String
     */
    public String readScript(Connection conn, String path, Class<?> clazz) throws IOException {
        logger.debug("entering readScript"); //$NON-NLS-1$
        String sql = null;
        InputStream in = clazz.getResourceAsStream(path);
        if (in == null) {
            throw new IOException("SQL script does not exist: " + path);
        }

        BufferedInputStream bufferedInput = null;
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        PrintWriter writer = new PrintWriter(baos);
        byte[] buffer = new byte[1024];

        try {
            bufferedInput = new BufferedInputStream(in);
            int bytesRead = 0;

            while ((bytesRead = bufferedInput.read(buffer)) != -1) {
                String chunk = new String(buffer, 0, bytesRead, Charset.defaultCharset());
                writer.write(chunk);
            }

            writer.flush();

            sql = new String(baos.toByteArray(), Charset.defaultCharset());
            String productName = conn.getMetaData().getDatabaseProductName();
            IDialectSpecifier dialectSpecifier = getDialectSpecifier(productName);
            sql = dialectSpecifier.specify(sql);

        } catch (FileNotFoundException ex) {
            logger.error(ex.getMessage());
        } catch (IOException ex) {
            logger.error(ex.getMessage());
        } catch (SQLException ex) {
            logger.error(ex.getMessage());
        } finally {
            try {
                if (bufferedInput != null)
                    bufferedInput.close();
            } catch (IOException ex) {
                logger.error(ex.getMessage());
            }
        }

        logger.debug("exiting readScript"); //$NON-NLS-1$

        return sql;
    }

    /**
     * Execute a SQL script containing multiple statements separated with ';'
     * 
     * @param connection
     * @param script
     * @return
     */
    public boolean executeUpdate(Connection connection, String script) {
        logger.debug("entering executeUpdate"); //$NON-NLS-1$
        boolean status = false;
        StringTokenizer tokenizer = new StringTokenizer(script, SCRIPT_DELIMITER);

        while (tokenizer.hasMoreTokens()) {
            String line = tokenizer.nextToken();
            if ("".equals(line.trim())) { //$NON-NLS-1$
                continue;
            }
            PreparedStatement preparedStatement = null;
            try {
                preparedStatement = connection.prepareStatement(line);
                preparedStatement.execute();
            } catch (SQLException e) {
                logger.error(e.getMessage());
            } finally {
                if (preparedStatement != null) {
                    try {
                        preparedStatement.close();
                    } catch (SQLException e) {
                        logger.error(e.getMessage());
                    }
                }
            }
            status = true;
        }
        logger.debug("exiting executeUpdate"); //$NON-NLS-1$
        return status;
    }

    public PreparedStatement getPreparedStatement(Connection connection, String sql) throws SQLException {
        logger.debug("entering getPreparedStatement"); //$NON-NLS-1$
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        logger.debug("exiting getPreparedStatement"); //$NON-NLS-1$
        return preparedStatement;
    }

    public Connection getConnection() throws SQLException {
        logger.debug("entering getConnection"); //$NON-NLS-1$
        Connection connection = this.dataSource.getConnection();
        logger.debug("exiting getConnection"); //$NON-NLS-1$
        return connection;
    }

    public void closeConnection(Connection connection) {
        logger.debug("entering closeConnection"); //$NON-NLS-1$
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }
        }
        logger.debug("exiting closeConnection"); //$NON-NLS-1$
    }

    public void closeStatement(Statement statement) {
        logger.debug("entering closeStatement"); //$NON-NLS-1$
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }
        }
        logger.debug("exiting closeStatement"); //$NON-NLS-1$
    }

    public static IDialectSpecifier getDialectSpecifier(String productName) {
        if (productName != null) {
            if (PRODUCT_HDB.equals(productName)) {
                return new HANADBSpecifier();
            } else if (PRODUCT_SAP_DB.equals(productName)) {
                return new SAPDBSpecifier();
            } else if (PRODUCT_SYBASE.equals(productName)) {
                return new SybaseDBSpecifier();
            } else if (PRODUCT_DERBY.equals(productName)) {
                return new DerbyDBSpecifier();
            } else if (PRODUCT_POSTGRESQL.equals(productName)) {
                return new PostgreSQLDBSpecifier();
            }
        }
        return new HANADBSpecifier();
    }

    public String specifyDataType(Connection connection, String commonType) throws SQLException {
        String productName = connection.getMetaData().getDatabaseProductName();
        IDialectSpecifier dialectSpecifier = getDialectSpecifier(productName);
        return dialectSpecifier.getSpecificType(commonType);
    }

    // public List<String> getListOfSchemes(DatabaseMetaData dmd,
    // String catalogName) throws SQLException {
    //
    // List<String> listOfSchemes = new ArrayList<String>();
    //
    // ResultSet rs = dmd.getSchemas(catalogName, null);
    //
    // while (rs.next()) {
    // String schemeName = rs.getString(1);
    // listOfSchemes.add(schemeName);
    // }
    // rs.close();
    //
    // return listOfSchemes;
    // }
    //
    // public List<String> getListOfTables(DatabaseMetaData dmd,
    // String catalogName, String schemeName) throws SQLException {
    //
    // List<String> listOfTables = new ArrayList<String>();
    //
    // String[] tableTypes = { "TABLE", "VIEW", "ALIAS", "SYNONYM",
    // "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "SYSTEM TABLE" };
    // ResultSet rs = dmd.getTables(catalogName, schemeName, "%", tableTypes);
    //
    // while (rs.next()) {
    // String tableName = rs.getString(3);
    // listOfTables.add(tableName);
    // }
    // rs.close();
    //
    // return listOfTables;
    // }

    /**
     * ResultSet current row to Content transformation
     * 
     * @param resultSet
     * @return
     * @throws SQLException
     */
    public static byte[] dbToData(ResultSet resultSet) throws SQLException {
        String data = resultSet.getString("DOC_CONTENT"); //$NON-NLS-1$
        return data.getBytes(Charset.defaultCharset());
    }

    /**
     * ResultSet current row to Binary Content transformation
     * 
     * @param repository
     * @param resultSet
     * @return
     * @throws SQLException
     * @throws IOException
     */
    public static byte[] dbToDataBinary(Connection connection, ResultSet resultSet, String columnName)
            throws SQLException, IOException {
        String productName = connection.getMetaData().getDatabaseProductName();
        IDialectSpecifier dialectSpecifier = DBUtils.getDialectSpecifier(productName);
        InputStream is = dialectSpecifier.getBinaryStream(resultSet, columnName);
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        IOUtils.copy(is, baos);
        byte[] bytes = baos.toByteArray();
        return bytes;
    }

}