package net.sqlitetutorial;

import java.sql.*;

/**
 *
 * @author sqlitetutorial.net
 */
public class DBConnect {
    private static String url;
    /**
     * Connect to a sample database
     */
    public static void connect() {
        url = "jdbc:sqlite:my_super_database.db";
        Connection conn = null;
        try {
            // db parameters

            // create a connection to the database
            conn = DriverManager.getConnection(url);

            System.out.println("Connection to SQLite has been established.");

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
        }
    }

    public static void createNewTable() {
        String sql = "CREATE TABLE IF NOT EXISTS Mark"
                + "(idMark INTEGER PRIMARY KEY AUTOINCREMENT, "
                + "value INTEGER CHECK(value<6))";

        executeSQL(sql);
    }

    public static void insertNote(String note) {
        String sql = "INSERT INTO Note(text) VALUES('" + note + "')" ;
        executeSQL(sql);
    }

    public static void executeSQL(String sql) {
        try (Connection conn = DriverManager.getConnection(url);
             Statement stmt = conn.createStatement()) {
            // create a new table
            stmt.execute(sql);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public static void readNotes() throws SQLException {
        String sql = "SELECT idNote, text FROM Note";
        Connection conn = DriverManager.getConnection(url);
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(sql);


        while (rs.next()) {
            String id = rs.getString("idNote");
            String note = rs.getString("text");

            System.out.println("Id je " + id + " a poznámka zní " + note);
        }


    }

    public static void main(String[] args) throws SQLException {
        connect();
        createNewTable();
        insertNote("David Loukota nedělá, co má a neustále se směje.");
        readNotes();
    }
}