package training;

import java.sql.*;

public class SqliteExample {
    public final static String DATABASE = "jdbc:sqlite:myExample.db";

    public static void main(String[] args) {
        if (!tableExists()) {
            createTable();
        }

        //addRow("Ford", "1999");
        //delRow("Ford");

        StringBuffer results = getRows();

        System.out.println(results);
    }

    private static void createTable() {
        Connection conn = null;
        Statement st = null;

        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection(DATABASE);
            st = conn.createStatement();
            st.executeUpdate("CREATE TABLE `Vehicle` (" + "`id` INTEGER PRIMARY KEY,"
                    + "`name` varchar(32) NOT NULL DEFAULT 'Name',"
                    + "`year` varchar(4) NOT NULL DEFAULT '0000');");
        } catch (SQLException e) {
            System.out.println("Error: " + e);
        } catch (ClassNotFoundException e) {
            System.out.println("Error loading org.sqlite.JDBC: " + e);
        } finally {
            try {
                if (conn != null)
                    conn.close();
                if (st != null)
                    st.close();
            } catch (SQLException e) {
                System.out.println("Could not create the table (on close): " + e);
            }
        }
    }

    public static StringBuffer getRows() {
        StringBuffer sb = new StringBuffer();
        Connection conn = null;
        Statement statement = null;
        ResultSet set = null;
        int iSize = 0;

        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection(DATABASE);
            statement = conn.createStatement();
            set = statement.executeQuery("SELECT * FROM Vehicle");

            while (set.next()) {
                iSize++;
                int id = set.getInt("id");
                String name = set.getString("name");
                String year = set.getString("year");

                sb.append("[id: " + id + ", name: " + name + ", year: " + year + "]\n");
            }

            System.out.println(iSize + " rows retrieved from the " + DATABASE + " database");
        } catch (SQLException e) {
            System.out.println("Error: " + e);
        } catch (ClassNotFoundException e) {
            System.out.println("Error: " + e);
        } finally {
            try {
                if (statement != null)
                    statement.close();
                if (set != null)
                    set.close();
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                System.out.println("Error: " + e);
            }
        }

        return sb;
    }

    public static boolean addRow(String sName, String sYear) {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean bSuccess = false;

        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection(DATABASE);

            ps = conn.prepareStatement("INSERT INTO Vehicle (id, name, year) VALUES (null,?,?)");
            ps.setString(1, sName);
            ps.setString(2, sYear);
            ps.executeUpdate();
            bSuccess = true;
        } catch (SQLException e) {
            System.out.println("Error: " + e);
        } catch (ClassNotFoundException e) {
            System.out.println("Error: " + e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                System.out.println("Error: " + e);
                bSuccess = false;
            }
        }

        return bSuccess;
    }

    public static boolean delRow(String sName) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet set = null;
        boolean bSuccess = false;

        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection(DATABASE);
            ps = conn.prepareStatement("DELETE FROM Vehicle WHERE name = ?");
            ps.setString(1, sName);
            ps.executeUpdate();
            bSuccess = true;
        } catch (SQLException e) {
            System.out.println("Error: " + e);
        } catch (ClassNotFoundException e) {
            System.out.println("Error: " + e);
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (set != null) {
                    set.close();
                }
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                System.out.println("Error: " + e);
                bSuccess = false;
            }
        }

        return bSuccess;
    }

    private static boolean tableExists() {
        Connection conn = null;
        ResultSet rs = null;

        try {
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection(DATABASE);
            DatabaseMetaData dbm = conn.getMetaData();
            rs = dbm.getTables(null, null, "Vehicle", null);

            if (!rs.next())
                return false;

            return true;
        } catch (SQLException e) {
            System.out.println("Error: " + e);
            return false;
        } catch (ClassNotFoundException e) {
            System.out.println("Error: " + e);
            return false;
        } finally {
            try {
                if (rs != null)
                    rs.close();

                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                System.out.println("Error: " + e);
            }
        }
    }
}