package try_1;
import java.sql.*;

/**
 *
 * @author Anton
 */

public class Main {

    /**
     * @param args the command line arguments
     */
   public static void main(String[] args)
        {
            try {
                try {
                    // Establishing connection
                    Class.forName("com.mysql.jdbc.Driver");
                    Connection conn = DriverManager.getConnection(
                        "jdbc:mysql://localhost:3306/test_db", "root", "");

                    helper.pIt("Connection_successful.");
                    Statement stmt = conn.createStatement();

                    //CREATION

                    stmt.executeUpdate("CREATE TABLE test_db.Cookoo ("
                                        + "id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,"
                                        + "title VARCHAR(100),"
                                        + "info VARCHAR(1000),"
                                        + "PRIMARY KEY (id)"
                                        + ");");

                    helper.pIt("Database created successfully...");

                    try {

                    //INSERTION
                    String name = "заголовок";
                    String inf = "опейсание";

                    stmt.executeUpdate("INSERT INTO Cookoo VALUES (1, '" + name + "', '" + inf + "');");
                    stmt.executeUpdate("INSERT INTO Cookoo VALUES (3, '" + name + "', '" + inf + "');");
                    stmt.executeUpdate("INSERT INTO Cookoo VALUES (2, '" + name + "', '" + inf + "');");

                    //SELECTING + OUTPUT
                    ResultSet rs = null;
                    rs = stmt.executeQuery("SELECT * FROM Cookoo WHERE title LIKE '%гол%'");

                    helper.pIt("*");
                    int id;
                    String title;
                    String info;

                    while (rs.next()) {
                        title = rs.getString("title");
                        id = rs.getInt("id");
                        info = rs.getString("info");
                        helper.pIt(" | " + id + " | " + title + " | " + info + " | ");
                        //helper.pIt("*");
                    }

                    rs.close();
                    } catch(Exception a) {}

                    //DEATH = drop table

                    stmt.executeUpdate("DROP TABLE Cookoo");
                    helper.pIt("Table killed...");

                    stmt.close(); conn.close();

                 } catch(ClassNotFoundException e) {
                    helper.pIt("class not found, too bad");
                    return;
                    }
            } catch(SQLException x) {
                helper.pIt("fail with sql");
             //   x.printStackTrace();
                return;
            }
            helper.pIt("Done.");
            return;
        }
}

/**
 * helper is a cool class with my static utils
 * for example pIt is output
 */