package ua.inf.smart.database;

import java.sql.*;

public class JDBCExample_2 {

    private static final String URL = "jdbc:mysql://localhost:3306/bookkeeping";
    private static final String NAME = "avangard";
    private static final String PASS = "7654321";

    public static void main(String[] args) {

        try (Connection con = DriverManager.getConnection(URL, NAME, PASS);
             Statement stmt = con.createStatement()) {

            stmt.execute("CREATE TABLE donations (\n" +
                    "  id INT NOT NULL AUTO_INCREMENT,\n" +
                    "  type VARCHAR(45) NULL,\n" +
                    "  amount INT NULL,\n" +
                    "  PRIMARY KEY (id))\n" +
                    "DEFAULT CHARACTER SET = utf8;");

            stmt.execute("INSERT INTO donations VALUES (777, 'name', 777)");
            stmt.execute("DELETE FROM donations WHERE id = 777");

            stmt.execute("INSERT INTO donations (type, amount) VALUES ('Dollars', 2570)");
            stmt.execute("INSERT INTO donations (type, amount) VALUES ('Euros', 5032)");
            stmt.execute("INSERT INTO donations (type, amount) VALUES ('Pounds', 1259)");
            stmt.execute("INSERT INTO donations (type, amount) VALUES ('Real-Estate', 55300)");
            stmt.execute("INSERT INTO donations (type, amount) VALUES ('Food_supply', 550)");

            stmt.executeUpdate("UPDATE donations SET type='Goods' WHERE type='Food_supply'");
            int changedRows =
            stmt.executeUpdate("UPDATE donations SET amount=2930 WHERE type='Goods'");
            System.out.println("Changed rows: " + changedRows);

            stmt.addBatch("INSERT INTO donations (type, amount) VALUES ('Type_1', 1)");
            stmt.addBatch("INSERT INTO donations (type, amount) VALUES ('Type_1', 2)");
            stmt.addBatch("INSERT INTO donations (type, amount) VALUES ('Type_1', 3)");
            stmt.executeBatch();

            stmt.clearBatch();

            stmt.addBatch("INSERT INTO donations (type, amount) VALUES ('Type_4', 4)");

            ResultSet resSet = stmt.executeQuery("SELECT * FROM donations");
            while (resSet.next()) {
                int id = resSet.getInt(1);
                String type = resSet.getString(2);
                int amount = resSet.getInt(3);
                System.out.println(id + ") " + type + ", " + amount);
            }
            resSet.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}