package lab2;

import java.sql.*;
import java.util.Random;

public class Main {


    private static final String jdbcClassName   = "com.ibm.db2.jcc.DB2Driver";
    private static final String databaseName    = "Lab1";
    private static final String baseUrl         = "jdbc:db2://localhost:50000/" + databaseName;
    private static final String user            = "db2inst1";
    private static final String password        = "db2";

    private static final int LOOP_CYCLES = 100;

    private static final String SELECT_AVG_RATING = "SELECT movie_id,"
                    + "CAST(ROUND(AVG(rating), 3) AS DECIMAL(10,4)),"
                    + "COUNT(movie_id)"
                    + "FROM rating "
                    + "WHERE movie_id = ? GROUP BY movie_id";


    private static void executeAndPrintResults(ResultSet result, int i) throws SQLException {
        while(result.next()) {
            System.out.println((i) + ". " + result.getInt(1) + " \t\t" + result.getDouble(2) + " \t\t" + result.getInt(3));
            i++;
        }
    }

    private static Statement getStatement(Connection con) throws SQLException {
        return con.createStatement();
    }


    private static PreparedStatement getPreparedStatement(Connection con, String query) throws SQLException {
        PreparedStatement statement =  con.prepareStatement(query);
        statement.setInt(1, getRandomInt());
        return statement;
    }

    private static int getRandomInt() {
        return new Random().nextInt(LOOP_CYCLES) + 1;
    }



    public static void main(String[] args) {

        long startTime = System.currentTimeMillis();
        Connection connection = null;
        try {
            //Load class into memory
            Class.forName(jdbcClassName);
            //Establish connection
            connection = DriverManager.getConnection(baseUrl, user, password);

            boolean execPreparedStatement = true;
            boolean execStatement = false;

            Statement statement = null;

            if(execPreparedStatement) {
                for(int i = 0; i < LOOP_CYCLES;i++) {
                    statement = getPreparedStatement(connection, SELECT_AVG_RATING);
                    executeAndPrintResults(((PreparedStatement)statement).executeQuery(), i + 1);
                }
            }

            if(execStatement) {
                for(int i = 0; i < LOOP_CYCLES;i++) {
                    statement = getStatement(connection);
                    String generatedQuery = SELECT_AVG_RATING.replace("?", "" + getRandomInt());
                    executeAndPrintResults(statement.executeQuery(generatedQuery), i + 1);
                }
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if(connection != null) {
                try {
                    connection.close();
                    System.out.println("Connection closed. Execution time " + (System.currentTimeMillis() - startTime) + " ms.");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }

}