package Datatypes1;
 import java.io.IOException;
 import java.sql.*;
 import java.sql.SQLException;
 import java.util.Scanner;
   public class Database2 {
    Scanner input = new Scanner(System.in);
     public static void main(String args[]) throws IOException {
      char ans;
      System.out.println("Welcome tp the coffee shop management system");
      Database2 database2 = new Database2();
      database2.connect();
      database2.createTable();
      do{
        database2.obtaindata();
        System.out.println("Do you want to enter more data:y/n?");
        ans =(char) System.in.read();
      }while( ans == 'y');

      database2.disconnect();
   }

//create the connection
String url="jdbc:postgresql://localhost:5432/dreamhome1";
String user = "postgres";
String pass = "postgres";


//database variables
Connection con = null;
ResultSet rs = null;
Statement stmt = null;



//connect with the database
public void connect(){
    try{
        //open connection
        con=DriverManager.getConnection(url,user,pass);

        //show that is it connected
        System.out.println("Connection successful");

    }catch(SQLException e){
        e.printStackTrace();
    }
}

//create table in the database
public void createTable(){
    try{
        stmt=con.createStatement();
        String sql="CREATE TABLE IF NOT EXISTS product (product_id SERIAL PRIMARY KEY NOT NULL,"+
                "product_name VARCHAR(100) NOT NULL,"+
                "product_price REAL)";
        stmt.executeUpdate(sql);
        System.out.println("table created successfully!");
        stmt.close();
    }catch(SQLException e){
        e.printStackTrace();

    }
}

public void obtaindata(){
    System.out.println("enter the data you want to insert");
    System.out.print("Product Name");
    String productname=input.nextLine();
    System.out.println("Product Price");
    Double productprice=input.nextDouble();
    Database2 database2=new Database2();
    database2.insertRecord(productname,productprice);

}


public void insertRecord(String prodname,Double prodprice){
    try{

        PreparedStatement st=con.prepareStatement("INSERT INTO public.product(product_name,product_price) VALUES (nextval('productid_sequence'),?,?)");
        st.setString(1,prodname);
        st.setDouble(2,prodprice);
        int i =st.executeUpdate();
        st.close();
    }catch(SQLException e){
        e.printStackTrace();
    }
}

//disconnect with the database
public void disconnect(){
    try{
        if(con != null){
            con.close();
        }

        if(stmt != null){
            stmt.close();
        }

        if(rs != null){
            rs.close();
        }

    }catch(SQLException e){
        e.printStackTrace();
    }

}