MoviesDB_Client/src/main/java/com/moviesdb/DataBaseConnector.java

package com.moviesdb;

import com.google.protobuf.Internal;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.chart.PieChart;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

public class DataBaseConnector {

    private Connection conn = null;
    private List<String> connRights;
    private Boolean initializeErr;

    public void initConnection(ConnectionParams connectionParams) {

        System.out.println("Connecting with params:\n\thost:\t\t" + connectionParams.host()
                + "\n\tuser:\t\t" + connectionParams.user()
                + "\n\tpassword:\t" + connectionParams.password());

        try {
            String url = "jdbc:mysql://" + connectionParams.host();
            DriverManager.setLoginTimeout(2);
            conn = DriverManager.getConnection(url, connectionParams.user(), connectionParams.password());
            if (conn != null) {
                initializeErr = false;
            }
            System.out.println("Successful");

            Statement statement = conn.createStatement();
            String req = "SELECT * FROM information_schema.user_privileges WHERE grantee LIKE \"'" + connectionParams.user() + "'%\";";
            ResultSet rs = statement.executeQuery(req);
            connRights= new ArrayList<String>();
            while (rs.next()) {
                connRights.add(rs.getString("PRIVILEGE_TYPE"));
            }
            System.out.println("Connection grants:\n\t" + connRights);

        } catch (SQLException ex) {
            //exception
            initializeErr = true;
            if (ex.getMessage().contains("Communications link failure")) {
                System.out.println(ex.getMessage());
            }
            else if (ex.getMessage().contains("Access denied")){
                System.out.println(ex.getMessage());
            } else {
                ex.printStackTrace();
            }

        }
    }

    public Boolean tryConnection(ConnectionParams connectionParams) {
        System.out.println("Trying to connect with params:\n\thost:\t\t" + connectionParams.host()
                + "\n\tuser:\t\t" + connectionParams.user()
                + "\n\tpassword:\t" + connectionParams.password());

        try {
            String url = "jdbc:mysql://" + connectionParams.host();
            DriverManager.setLoginTimeout(2);
            DriverManager.getConnection(url, connectionParams.user(), connectionParams.password());
            System.out.println("Successful");
            return true;
        } catch (SQLException ex) {
            //exception
            if (ex.getMessage().contains("Communications link failure")) {
                System.out.println(ex.getMessage());
            } else if (ex.getMessage().contains("Access denied")) {
                System.out.println(ex.getMessage());
            } else {
                ex.printStackTrace();
            }
            return false;
        }
    }

    public boolean initialized(){
        return !initializeErr;
    }



    public Movie getMovieInfo(int movieId)
    {
        Movie resultedMovie = new Movie();

        try
        {
            Statement statement = conn.createStatement();
            String req = "SELECT * FROM Movies WHERE id = " + movieId + ";";
            ResultSet rs = statement.executeQuery(req);

            while(rs.next()){
                resultedMovie.setId(movieId);
                resultedMovie.setName(rs.getString("name"));
                resultedMovie.setGenre(rs.getString("genre"));
                resultedMovie.setDescription(rs.getString("description"));
                resultedMovie.setDuration(rs.getString("duration"));
                resultedMovie.setRating(rs.getString("user_rating"));
                resultedMovie.setYear(rs.getString("year"));
                resultedMovie.setStudio(rs.getString("studio"));
                resultedMovie.setDirector(rs.getString("director"));
                resultedMovie.setCover(rs.getString("image_link"));
            }
        }
        catch (SQLException ex) {
            //exception
            ex.printStackTrace();
        }
        return  resultedMovie;
    }

    public Movie[] getMovies()
    {
        Movie[] resultedMovies;

        try
        {
            Statement statement = conn.prepareStatement("SELECT * FROM Movies;", ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            String req = "SELECT * FROM Movies;";
            ResultSet rs = statement.executeQuery(req);
            rs.last();
            int Count = rs.getRow();
            resultedMovies = new Movie[Count];
            rs.first();
            for (int i = 0; i < Count; i++)
            {
                resultedMovies[i] = new Movie();
                resultedMovies[i].setId(rs.getInt("id"));
                resultedMovies[i].setName(rs.getString("name"));
                resultedMovies[i].setGenre(rs.getString("genre"));
                resultedMovies[i].setDescription(rs.getString("description"));
                resultedMovies[i].setDuration(rs.getString("duration"));
                resultedMovies[i].setRating(rs.getString("user_rating"));
                resultedMovies[i].setYear(rs.getString("year"));
                resultedMovies[i].setStudio(rs.getString("studio"));
                resultedMovies[i].setDirector(rs.getString("director"));
                resultedMovies[i].setCover(rs.getString("image_link"));
                rs.next();
            }

        }
        catch (SQLException ex) {
            //exception
            ex.printStackTrace();
            resultedMovies = null;
        }
        return  resultedMovies;
    }

    public int addMovie(Movie movie) {
        try {
            Statement statement = conn.createStatement();
            String req;

            String intoPart = "";
            String valPart = "";

            if (!movie.name().equals("Movie_name_undefined")) {
                intoPart += " name,";
                valPart += " '" + movie.name() + "',";
            }

            if (!movie.genre().equals("Movie_genre_undefined")) {
                intoPart += " genre,";
                valPart += " '" + movie.genre() + "',";
            }

            if (!movie.description().equals("Movie_description_undefined")) {
                intoPart += " description,";
                valPart += " '" + movie.description() + "',";
            }

            if (!movie.duration().equals("Movie_duration_undefined")) {
                intoPart += " duration,";
                valPart += " '" + movie.duration() + "',";
            }

            if (!movie.rating().equals("Movie_rating_undefined")) {
                intoPart += " user_rating,";
                valPart += " '" + movie.rating() + "',";
            }

            if (!movie.year().equals("Movie_year_undefined")) {
                intoPart += " year,";
                valPart += " '" + movie.year() + "',";
            }

            if (!movie.studio().equals("Movie_studio_undefined")) {
                intoPart += " studio,";
                valPart += " '" + movie.studio() + "',";
            }

            if (!movie.director().equals("Movie_director_undefined")) {
                intoPart += " director,";
                valPart += " '" + movie.director() + "',";
            }

            if (!movie.getImageLink().equals("Movie_ImageLink_undefined")) {
                intoPart += " image_link,";
                valPart += " '" + movie.getImageLink() + "',";
            }

            if (intoPart.endsWith(","))
                intoPart = intoPart.substring(0, intoPart.length() - 1);
            if (valPart.endsWith(","))
                valPart = valPart.substring(0, valPart.length() - 1);

            //сборка текста запроса
            req = "INSERT INTO Movies (" + intoPart + ") ";
            req += "values (" + valPart + ");";
            System.out.println(req);
            //выполнение запроса
            statement.execute(req);

            //For returning id of added movie
            statement = conn.prepareStatement("SELECT * FROM Movies;", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            req = "SELECT * FROM Movies;";
            ResultSet rs = statement.executeQuery(req);
            rs.last();
            return rs.getInt("id");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int addRole(Role role) {
        try {
            Statement statement = conn.createStatement();
            String req;

            String intoPart = "";
            String valPart = "";

            if (role.movieId() > 0) {
                intoPart += " movie_id,";
                valPart += " '" + role.movieId() + "',";
            }

            if (!role.actor().equals("Role_actor_undefined")) {
                intoPart += " actor,";
                valPart += " '" + role.actor() + "',";
            }

            if (!role.role().equals("Role_role_undefined")) {
                intoPart += " role,";
                valPart += " '" + role.role() + "',";
            }
            if (intoPart.endsWith(",") && valPart.endsWith(",")) {
                intoPart = intoPart.substring(1, intoPart.length() - 1);
                valPart = valPart.substring(1, valPart.length() - 1);

                //сборка текста запроса
                req = "INSERT INTO Roles (" + intoPart + ") ";
                req += "values (" + valPart + ");";
                System.out.println(req);
                //выполнение запроса
                if (true) {
                    statement.execute(req);

                    //For returning id of added movie
                    statement = conn.prepareStatement("SELECT * FROM Roles;", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                    req = "SELECT * FROM Roles;";
                    ResultSet rs = statement.executeQuery(req);
                    rs.last();
                    return rs.getInt("id");
                }
            } else {
                System.out.println("[ERROR] Error in creation of addRole's query!");
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public void updateMovie(Movie oldMovie, Movie newMovie) {
        try {
            Statement statement = conn.createStatement();
            String req;

            String intoPart = "";

            if (!oldMovie.name().equals(newMovie.name())) {
                intoPart += " `name` = '" + newMovie.name() + "',";
            }

            if (!oldMovie.genre().equals(newMovie.genre())) {
                intoPart += " `genre` = '" + newMovie.genre() + "',";
            }

            if (!oldMovie.description().equals(newMovie.description())) {
                intoPart += " `description` = '" + newMovie.description() + "',";
            }

            if (!oldMovie.duration().equals(newMovie.duration())) {
                intoPart += " `duration` = '" + newMovie.duration() + "',";
            }

            if (!oldMovie.rating().equals(newMovie.rating())) {
                intoPart += " `user_rating` = '" + newMovie.rating() + "',";
            }

            if (!oldMovie.year().equals(newMovie.year())) {
                intoPart += " `year` = '" + newMovie.year() + "',";
            }

            if (!oldMovie.studio().equals(newMovie.studio())) {
                intoPart += " `studio` = '" + newMovie.studio() + "',";
            }

            if (!oldMovie.director().equals(newMovie.director())) {
                intoPart += " `director` = '" + newMovie.director() + "',";
            }

            if (!oldMovie.getImageLink().equals(newMovie.getImageLink())) {
                intoPart += " `image_link` = '" + newMovie.getImageLink() + "',";
            }

            if (intoPart.charAt(intoPart.length() - 1) == ',')
                intoPart = intoPart.substring(0, intoPart.length() - 1);

            //сборка текста запроса

            req = "UPDATE Movies SET" + intoPart;
            req += " WHERE (`id` = '" + oldMovie.id() + "');";


            //выполнение запроса
            statement.execute(req);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public void deleteMovie(Movie movie) {
        try {
            Statement statement = conn.createStatement();
            int targetId = movie.id();
            String req = "DELETE FROM Movies WHERE (`id` = '" + targetId + "');";
            statement.execute(req);
            req = "DELETE FROM Roles WHERE (`movie_id` = '" + targetId + "');";
            statement.execute(req);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public boolean granted(String grantName){
        if (connRights == null){
            return false;
        }
        return connRights.contains(grantName);
    }

    public Movie[] filterMovie(Movie filterMovie, String actor, String role) {
        ResultSet rs = null;
        try {
            //Statement statement = conn.createStatement();
            String req;

            String intoPart = "";

            if (!filterMovie.name().equals("Movie_name_undefined")) {
                intoPart += "AND `name` LIKE '" + filterMovie.name() + "%' ";
            }

            if (!filterMovie.genre().equals("Movie_genre_undefined")) {
                intoPart += "AND `genre` LIKE '" + filterMovie.genre() + "%' ";
            }

            if (!filterMovie.description().equals("Movie_description_undefined")) {
                intoPart += "AND `description` LIKE '" + filterMovie.description() + "%' ";
            }

            if (!filterMovie.duration().equals("Movie_duration_undefined")) {
                intoPart += "AND `duration` LIKE '" + filterMovie.duration() + "%' ";
            }

            if (!filterMovie.rating().equals("Movie_rating_undefined")) {
                intoPart += "AND `user_rating` LIKE '" + filterMovie.rating() + "%' ";
            }

            if (!filterMovie.year().equals("Movie_year_undefined")) {
                intoPart += "AND `year` LIKE '" + filterMovie.year() + "%' ";
            }

            if (!filterMovie.studio().equals("Movie_studio_undefined")) {
                intoPart += "AND `studio` LIKE '" + filterMovie.studio() + "%' ";
            }

            if (!filterMovie.director().equals("Movie_director_undefined")) {
                intoPart += "AND `director` LIKE '" + filterMovie.director() + "%' ";
            }

            if ((actor != "")) {
                intoPart += "AND `actor` LIKE '" + actor + "%' ";
            }

            if ((role != "")) {
                intoPart += "AND `role` LIKE '" + role + "%' ";
            }

            //if (intoPart.charAt(intoPart.length() - 1) == ',')
            //    intoPart = intoPart.substring(0, intoPart.length() - 1);

            //сборка текста запроса

            req = "SELECT DISTINCT Movies.id FROM moviesdb.Roles, moviesdb.Movies";
            if (actor != "" || role != "")
                req += " WHERE (moviesdb.Movies.id = moviesdb.Roles.movie_id " + intoPart +  ")";
            else

                if(intoPart.length() > 3) {
                    intoPart = intoPart.substring(3, intoPart.length());
                    req += " WHERE (" + intoPart + ")";
                }

            req+=";";
            System.out.println(req);
            //выполнение запроса
            Statement statement = conn.prepareStatement(req, ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            rs = statement.executeQuery(req);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        List<Integer> movies_id = new ArrayList<Integer>();
        if (rs != null) {
            try {
                rs.first();
                movies_id.add(rs.getInt("id"));
                while (rs.next())
                {
                    movies_id.add(rs.getInt("id"));
                }
            }
            catch (SQLException ex)
            {
                System.out.println(ex.getMessage());
            }
        }

        Movie[] filteredMovies = new Movie[movies_id.size()];

        for (int i = 0; i < movies_id.size(); i++) {
            filteredMovies[i] = getMovieInfo(movies_id.get(i));
        }
        System.out.println(filteredMovies.length);
        return filteredMovies;
    }

    public ObservableList<PieChart.Data> getGenreStats()
    {
        ObservableList<PieChart.Data> list = FXCollections.observableArrayList();

        try
        {
            Statement statement = conn.prepareStatement("SELECT count(id) AS number, genre FROM moviesdb.Movies GROUP BY genre;", ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            String req = "SELECT count(id) AS number, genre FROM moviesdb.Movies GROUP BY genre;";
            ResultSet rs = statement.executeQuery(req);
            rs.last();
            int Count = rs.getRow();

            rs.first();


            for (int i = 0; i < Count; i++)
            {
                String name = rs.getString("genre");
                if (name == null)
                    name = "Empty";
                list.add(new PieChart.Data(name, rs.getInt("number")));
                rs.next();
            }


        }
        catch (SQLException ex) {
            //exception
            ex.printStackTrace();
        }
        return  list;
    }

}