package Util;

import com.sun.rowset.CachedRowSetImpl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Created by Philipp Coblenz on 28.02.2017.
 */

public class DBConnect {

    private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private static Connection dbConnection = null;
    private static final String conString = "jdbc:oracle:thin:ws1617-team1/X20t7Fkbccx3@132.187.8.167:3306/xe";


    public static void connect() throws SQLException, ClassNotFoundException {
        Class.forName(JDBC_DRIVER);
        System.out.println("JDBC Driver found");
        try {
            dbConnection = DriverManager.getConnection(conString);
        } catch (SQLException e) {
            System.out.println("Could not establish DB connection.\n" + e);
            e.printStackTrace();
            throw e;
        }
    }


    public static void disconnect() throws SQLException {
        try {
            if (dbConnection != null && !dbConnection.isClosed()) {
                dbConnection.close();
            }
        } catch (Exception e){
            throw e;
        }
    }

    public static ResultSet search(String sqlStatement) throws SQLException, ClassNotFoundException {
        Statement statement = null;
        ResultSet resultSet = null;
        CachedRowSetImpl crs = null;
        try {
            connect();
            statement = dbConnection.createStatement();
            resultSet = statement.executeQuery(sqlStatement);

            crs = new CachedRowSetImpl();
            crs.populate(resultSet);
        } catch (SQLException e) {
           throw e;
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            disconnect();
        }
        return crs;
    }


    public static void dbUpdate(String sqlStmt) throws SQLException, ClassNotFoundException {
        Statement statement = null;
        try {
            connect();
            statement = dbConnection.createStatement();
            statement.executeUpdate(sqlStmt);
        } catch (SQLException e) {
            throw e;
        } finally {
            if (statement != null) {
                statement.close();
            }
            disconnect();
        }
    }
}