com.data2semantics.yasgui.server.db.ConnectionFactory.java Source code

Java tutorial

Introduction

Here is the source code for com.data2semantics.yasgui.server.db.ConnectionFactory.java

Source

package com.data2semantics.yasgui.server.db;

/*
 * #%L
 * YASGUI
 * %%
 * Copyright (C) 2013 Laurens Rietveld
 * %%
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 * 
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 * 
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 * #L%
 */

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Map.Entry;
import java.util.TreeMap;

import org.apache.commons.io.FileUtils;
import org.apache.commons.io.filefilter.FileFilterUtils;
import org.json.JSONException;
import org.json.JSONObject;

import com.data2semantics.yasgui.server.fetchers.ConfigFetcher;
import com.data2semantics.yasgui.shared.SettingKeys;

public class ConnectionFactory {

    /**
     * Get database connection
     * 
     * @param configDir
     * @return
     * @throws JSONException
     * @throws ClassNotFoundException
     * @throws SQLException
     * @throws FileNotFoundException
     * @throws IOException
     * @throws ParseException
     */
    public static Connection getConnection(File configDir) throws JSONException, ClassNotFoundException,
            SQLException, FileNotFoundException, IOException, ParseException {
        JSONObject config = ConfigFetcher.getJsonObjectFromPath(configDir);
        Connection connect = null;
        try {
            connect = connect(
                    config.getString(SettingKeys.MYSQL_HOST) + "/" + config.getString(SettingKeys.MYSQL_DB),
                    config.getString(SettingKeys.MYSQL_USERNAME), config.getString(SettingKeys.MYSQL_PASSWORD));
            applyDeltas(connect, configDir);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("either db doesnt exist, or there is an error in our deltas");
            //connect without db selector, create db, and create new connector
            connect = connect(config.getString(SettingKeys.MYSQL_HOST),
                    config.getString(SettingKeys.MYSQL_USERNAME), config.getString(SettingKeys.MYSQL_PASSWORD));
            connect = updateDatabase(connect, config, configDir);
        }
        return connect;
    }

    /**
     * Connect to a DB
     * @param host
     * @param username
     * @param password
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    private static Connection connect(String host, String username, String password)
            throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://" + host;
        return DriverManager.getConnection(url, username, password);

    }

    /**
     * Update a database. Called on an empty database. This method executes an SQL script generating the needed db tables.
     * Also checks our delta's, to see whether we need to change our database.
     * 
     * @param connect
     * @param config
     * @param configDir
     * @return
     * @throws FileNotFoundException
     * @throws IOException
     * @throws SQLException
     * @throws JSONException
     * @throws ClassNotFoundException
     */
    private static Connection updateDatabase(Connection connect, JSONObject config, File configDir)
            throws FileNotFoundException, IOException, SQLException, JSONException, ClassNotFoundException {
        String dbName = config.getString(SettingKeys.MYSQL_DB);
        if (!databaseExists(connect, dbName)) {
            createDatabase(connect, dbName);
            connect.close();
            connect = connect(
                    config.getString(SettingKeys.MYSQL_HOST) + "/" + config.getString(SettingKeys.MYSQL_DB),
                    config.getString(SettingKeys.MYSQL_USERNAME), config.getString(SettingKeys.MYSQL_PASSWORD));

            System.out.println("connected to " + config.getString(SettingKeys.MYSQL_HOST) + "/"
                    + config.getString(SettingKeys.MYSQL_DB));

            ScriptRunner runner = new ScriptRunner(connect, false, true);
            String filename = "create.sql";
            FileInputStream fileStream = new FileInputStream(
                    configDir.getAbsolutePath() + "/" + ConfigFetcher.CONFIG_DIR + filename);
            runner.runScript(new BufferedReader(new InputStreamReader(fileStream, "UTF-8")));
            fileStream.close();
        }
        applyDeltas(connect, configDir);
        return connect;
    }

    private static void applyDeltas(Connection connect, File configDir)
            throws UnsupportedEncodingException, IOException, SQLException {
        @SuppressWarnings("unchecked")
        ArrayList<File> listFiles = new ArrayList<File>(
                FileUtils.listFiles(new File(configDir.getAbsolutePath() + "/config"),
                        FileFilterUtils.prefixFileFilter("delta_"), null));

        TreeMap<Integer, File> files = new TreeMap<Integer, File>();
        for (File file : listFiles) {
            String basename = file.getName();
            basename = basename.substring("delta_".length());
            basename = basename.substring(0, basename.length() - ".sql".length());
            int index = Integer.parseInt(basename);
            files.put(index, file);
        }
        ArrayList<Integer> currentDeltas = getDeltas(connect);
        //treemap is naturally sorted, so just iterate through them
        for (Entry<Integer, File> entry : files.entrySet()) {
            if (!currentDeltas.contains(entry.getKey())) {
                ScriptRunner runner = new ScriptRunner(connect, false, true);
                FileInputStream fileStream = new FileInputStream(entry.getValue());
                runner.runScript(new BufferedReader(new InputStreamReader(fileStream, "UTF-8")));
                fileStream.close();
                setDeltaApplied(connect, entry.getKey());
            }

        }
    }

    /**
     * Create database
     * @param connect
     * @param dbName
     * @throws SQLException
     */
    private static void createDatabase(Connection connect, String dbName) throws SQLException {
        Statement statement = connect.createStatement();
        statement.executeUpdate(
                "CREATE DATABASE `" + dbName + "` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
        statement.close();
    }

    /**
     * Check if database exists
     * @param connect
     * @param dbName
     * @return
     * @throws SQLException
     */
    private static boolean databaseExists(Connection connect, String dbName) throws SQLException {
        Statement statement = connect.createStatement();
        ResultSet resultSet = statement.executeQuery(
                "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '" + dbName + "'");
        boolean exists = resultSet.next();
        statement.close();
        resultSet.close();
        return exists;
    }

    private static ArrayList<Integer> getDeltas(Connection connect) throws SQLException {
        ArrayList<Integer> deltaIds = new ArrayList<Integer>();
        String sql = "SELECT Id FROM Deltas WHERE 1 ORDER BY Id ASC";
        Statement statement = connect.createStatement();
        ResultSet result = statement.executeQuery(sql);
        while (result.next()) {
            deltaIds.add(result.getInt("Id"));
        }
        return deltaIds;
    }

    private static void setDeltaApplied(Connection connect, int deltaId) throws SQLException {
        String sql = "INSERT INTO Deltas (Id) VALUES(?)";
        PreparedStatement statement = connect.prepareStatement(sql);
        statement.setInt(1, deltaId);
        statement.executeUpdate();
    }

    public static void main(String[] args) {
        String basename = "delta_10.sql";
        basename = basename.substring("delta_".length());
        basename = basename.substring(0, basename.length() - ".sql".length());
    }

}