package SomePackage;

/**
 * Created by A.V.Tsaplin on 03.03.2016.
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;



public class SqlInit {
    // JDBC URL, username and password of MySQL server
    private static final String url = "jdbc:mysql://localhost:3306/mydb";
    private static final String user = "root";
    private static final String password = "mercedesg55amg";

    // JDBC variables for opening and managing connection
    private static Connection con;
    private static Statement stmt;

    public void Init() {

        final String queryDropTable = "Drop table mapnumberone";

        final String queryCreateTable = "create table if not exists mapnumberone (id int(11) not null," +
                " x int(11) not null, y int(11) not null, value int(11) not null, primary key(id))" +
                " engine = INNODB default charset = latin1";



        final String queryInsertData01 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (1, 0, 0, 1);";
        final String queryInsertData02 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (2, 1, 0, 1);";
        final String queryInsertData03 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (3, 2, 0, 0);";
        final String queryInsertData04 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (4, 3, 0, 1);";
        final String queryInsertData05 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (5, 4, 0, 1);";
        final String queryInsertData06 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (6, 5, 0, 1);";
        final String queryInsertData07 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (7, 0, 1, 1);";
        final String queryInsertData08 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (8, 1, 1, 0);";
        final String queryInsertData09 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (9, 2, 1, 0);";
        final String queryInsertData10 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (10, 3, 1, 0);";
        final String queryInsertData11 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (11, 4, 1, 0);";
        final String queryInsertData12 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (12, 5, 1, 1);";
        final String queryInsertData13 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (13, 0, 2, 1);";
        final String queryInsertData14 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (14, 1, 2, 0);";
        final String queryInsertData15 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (15, 2, 2, 1);";
        final String queryInsertData16 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (16, 3, 2, 1);";
        final String queryInsertData17 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (17, 4, 2, 2);";
        final String queryInsertData18 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (18, 5, 2, 1);";
        final String queryInsertData19 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (19, 0, 3, 1);";
        final String queryInsertData20 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (20, 1, 3, 1);";
        final String queryInsertData21 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (21, 2, 3, 1);";
        final String queryInsertData22 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (22, 3, 3, 1);";
        final String queryInsertData23 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (23, 4, 3, 1);";
        final String queryInsertData24 = "INSERT INTO mydb.mapnumberone (id, x, y, value) VALUES (24, 5, 3, 1);";

        ArrayList<String> queryArray = new ArrayList<String>() {{
            add(queryInsertData01);
            add(queryInsertData02);
            add(queryInsertData03);
            add(queryInsertData04);
            add(queryInsertData05);
            add(queryInsertData06);
            add(queryInsertData07);
            add(queryInsertData08);
            add(queryInsertData09);
            add(queryInsertData10);
            add(queryInsertData11);
            add(queryInsertData12);
            add(queryInsertData13);
            add(queryInsertData14);
            add(queryInsertData15);
            add(queryInsertData16);
            add(queryInsertData17);
            add(queryInsertData18);
            add(queryInsertData19);
            add(queryInsertData20);
            add(queryInsertData21);
            add(queryInsertData22);
            add(queryInsertData23);
            add(queryInsertData24);
        }};


        try {
            // opening database connection to MySQL server
            con = DriverManager.getConnection(url, user, password);
            // getting Statement object to execute query
            stmt = con.createStatement();
            // executing drop query
            stmt.executeUpdate(queryDropTable);
            // executing create query
            stmt.executeUpdate(queryCreateTable);
            // executing insert queries
            for (String query: queryArray) {
                try {
                    stmt.executeUpdate(query);
                } catch (Exception e) {

                }
            }
        } catch (SQLException sqlEx) {
            sqlEx.printStackTrace();
        } finally {
            //close connection ,stmt and resultset here
            try { con.close(); } catch(SQLException se) { /*can't do anything */ }
            try { stmt.close(); } catch(SQLException se) { /*can't do anything */ }
        }
    }
}