import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.io.IOException;
import java.util.Scanner;
import java.io.FileReader;
import java.io.FileWriter;


public class COFFEE1
{
  public static void main(String[] args) throws IOException,ClassNotFoundException
  {  
	FileReader fin = new FileReader("coffee.txt");
    Scanner src = new Scanner(fin);
    String name;
    int code;
    double price=0;
    String pr;
    int amount;
    double total=0;
    String s1="";
    // load the sqlite-JDBC driver using the current class loader
    Class.forName("org.sqlite.JDBC");   
    Connection connection = null;
    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:COFFEE.db");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.
      
      statement.executeUpdate("drop table if exists COFFEE");
      statement.executeUpdate("create table COFFEE(COFFEE_NAME String, CODE int, PRICE double, AMOUNT int,TOTAL double)");
      while (src.hasNext()) 
      {name=src.next();
       code=Integer.parseInt(src.next());
       price=Double.parseDouble(src.next());
       amount=Integer.parseInt(src.next());
       total=Double.parseDouble(src.next());
       s1=name+","+code+","+price+","+amount+","+total;
        statement.executeUpdate("insert into COFFEE values("+s1+");");
       }
      statement.executeUpdate("update COFFEE set TOTAL=PRICE*AMOUNT;");     
      ResultSet rs = statement.executeQuery("select * from COFFEE");
      while(rs.next())
      {
        // read the result set
        System.out.print("COFFEE_NAME = " + rs.getString("COFFEE_NAME"));
        System.out.print(" CODE = " + rs.getInt("CODE"));
        System.out.print(" PRICE = " + rs.getDouble("PRICE"));
        System.out.print(" AMOUNT = " + rs.getInt("AMOUNT"));
        System.out.println(" TOTAL = " + rs.getDouble("TOTAL"));
      }
    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
  }
}
