package ua.inf.smart.prestmt;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.*;
import java.util.Calendar;

public class PreparingStatements {

    private static String HOST = "jdbc:mysql://localhost:3306/dreambase";
    private static String NAME = "favorite";
    private static String PASS = "abracadabra";

    private static Connection conn;
    private static PreparedStatement preStmt;

    private static final String INSERT_NEW =
            "INSERT INTO applications VALUES (?, ?, ?, ?, ?, ?, ?)";
    private static final String GET_ALL = "SELECT * FROM applications";
    private static final String DELETE = "DELETE FROM applications WHERE id = ?";

    public static void main(String[] args) {
        try {
            conn = DriverManager.getConnection(HOST, NAME, PASS);
            preStmt = conn.prepareStatement(INSERT_NEW);
            preStmt.setInt(1, 2);
            preStmt.setString(2, "\"Some title...\"");
            preStmt.setString(3, "\"Some description...\"");
            preStmt.setFloat(4, 0.57f);
            preStmt.setBoolean(5, true);
            preStmt.setDate(6, new Date(Calendar.getInstance().getTimeInMillis()));
            preStmt.setBlob(7, new FileInputStream("barashka.jpg"));
            preStmt.execute();
            preStmt = conn.prepareStatement(GET_ALL);
            ResultSet resultSet = preStmt.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String title = resultSet.getString("title");
                String desc = resultSet.getString("description");
                float rating = resultSet.getFloat("rating");
                boolean published = resultSet.getBoolean("published");
                Date date = resultSet.getDate("created");
                byte[] icon = resultSet.getBytes("icon");

                System.out.println(
                        "id: " + id +
                        ", Title: " + title +
                        ", Desc: " + desc +
                        ", Rating: " + rating +
                        ", Published: " + published +
                        ", Date: " + date +
                        ", Icon.length: " + icon.length
                );
            }

            preStmt = conn.prepareStatement(DELETE);
            preStmt.setInt(1, 2);
            preStmt.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        finally {
            shutdown();
        }
    }

    private static void shutdown() {
        try {
            if (preStmt != null) {
                preStmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException sqlExcept) {}
    }
}