package test;

import java.sql.*;

import static java.lang.System.out;

public class App {
    private Connection connection;

    public static void main(String[] args) {
        new App().start();
    }

    private void start() {
        try {
            executeInsert("insert into user(host, user, password, ssl_cipher, x509_issuer, x509_subject) values('localhost', 'hello', 'md5@!#$', '', '', '')");
            executeSelect("select * from user order by rand()");
            executeSelect("show tables");
            executeSelect("select * from help_keyword");
            executeSelect("select * from time_zone order by rand()");
        } finally {
            closeConnection();
        }
    }

    private void executeSelect(String selectQuery) {
        Statement statement = null;
        try {
            statement = getConnection().createStatement();
            ResultSet res = statement.executeQuery(selectQuery);
            out.println(String.format("\nQuery: \"%s\"", selectQuery));
            ResultSetMetaData metaData = res.getMetaData();
            int columns = metaData.getColumnCount();
            out.println("Columns:");

            StringBuffer format = new StringBuffer();

            for (int i = 1; i <= columns; i++) {
                out.println(i + ". " + metaData.getColumnName(i));
                int size = metaData.getColumnDisplaySize(i);
                size = (i == 1 ? 1 : (size > 20 ? 20 : Math.abs(size)));
                format.append("%").append(size).append("s");
            }
            out.println("Data:");
            while (res.next()) {
                Object[] args = new String[columns];
                for (int i = 1; i <= columns; i++) {
                    args[i - 1] = res.getString(i);
                }
                out.println(String.format(format.toString(), args));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
                if (statement != null) {
                    try {
                        statement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }

        }
    }

    private int executeInsert(String insertQuery) {
        Statement statement = null;
        try {
            statement = getConnection().createStatement();
            return statement.executeUpdate(insertQuery);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
                if (statement != null) {
                    try {
                        statement.close();
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }
        }
        return 0;
    }

    private Connection getConnection() {
        if (connection == null) {
            try {
                connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "developer");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return connection;
    }

    private void closeConnection() {
        try {
            connection.close();
        } catch (NullPointerException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}