/**
 * Java. Level 3. Lesson 2. Homework
 *
 * 1. Create a product table (id, title, cost) // -create
 * 2. Clear the table and fill it with 1000 products // -init 
 * 3. Get the price of the product by name // -getprice 
 * 4. Change the price of product // -setprice  
 * 5. List of products in the given price range // -list  
 *
 * @author Sergey Iryupin
 * @version Jul 14, 2018
 * @link https://github.com/biblelamp
 */
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.ArrayList;

public class HW2Lesson {

    static final String DRIVER_NAME = "org.sqlite.JDBC";
    static final String DB_NAME = "jdbc:sqlite:goods.db";
    final String TABLE_NAME = "products";
    final String COL_ID = "id";
    final String COL_TITLE = "title";
    final String COL_PRICE = "price";
    final String SQL_CREATE_TABLE =
        "DROP TABLE IF EXISTS " + TABLE_NAME + ";" +
        "CREATE TABLE " + TABLE_NAME + "(" +
            COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
            COL_TITLE + " TEXT," +
            COL_PRICE + " REAL" +
        ");";
    final String SQL_CLEAR_TABLE =
        "DELETE FROM " + TABLE_NAME + ";" +
        "DELETE FROM sqlite_sequence WHERE name='" + TABLE_NAME + "'"; // reset
    final String SQL_INSERT = "INSERT INTO " + TABLE_NAME +
        " (" + COL_TITLE + ", " + COL_PRICE + ") VALUES (?, ?);";
    final String SQL_SELECT = "SELECT * FROM " + TABLE_NAME + " WHERE title=?;";
    final String SQL_UPDATE =
        "UPDATE " + TABLE_NAME + " SET price=? WHERE title=?;";
    final String SQL_LIST_IN_RANGE =
        "SELECT * FROM " + TABLE_NAME + " WHERE price>=? AND price