Cart

package application;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mysql.jdbc.PreparedStatement;

import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.geometry.Insets;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.layout.GridPane;
import javafx.scene.text.Text;
import javafx.stage.Stage;

public class Cart extends Stage {
    public Cart(Stage prev , String userName) {
        Stage current = this;
        java.util.List<Button> list = new ArrayList<Button>();
        GridPane gridPane = new GridPane();
        int counter =0;
        try {
            ResultSet rs;
            Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/Online_Book_Store?useSSL=false", "root", "ahmed666" );
            PreparedStatement p = (PreparedStatement) con.prepareStatement("select * from CART where USER_NAME='"+userName+"'");
            rs = p.executeQuery();
            while(rs.next()) {
                   list.add(new Button("Purchase"));
                   list.get(counter).setStyle("-fx-background-color: darkslateblue; -fx-text-fill: white;");
                   Text ISBN = new Text(Integer.toString(rs.getInt("ISBN")));
                   Text copies = new Text(Integer.toString(rs.getInt("COPIES")));
                   ISBN.setStyle("-fx-font: normal italic 15px 'serif' ");
                   copies.setStyle("-fx-font: normal italic 15px 'serif' ");
                   gridPane.add(ISBN,0,counter+1);
                   gridPane.add(copies,1,counter+1);
                   gridPane.add(list.get(counter),(400/30) ,counter+1);


                   // Purchasing handling
                   Button b;
                   b =  list.get(counter);

                   list.get(counter).setOnAction(new EventHandler<ActionEvent>() {
                        @Override
                        public void handle(ActionEvent event) {
                            // connect to db
                              try {
                                  String c = copies.getText();
                                  String i = ISBN.getText();
                                  Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/Online_Book_Store?useSSL=false", "root", "ahmed666" );
                                  PreparedStatement p = (PreparedStatement) con.prepareStatement("update BOOK_STORE set BOOK_COPIES = BOOK_COPIES - "+c+
                                        " where ISBN = "+i);
                                  p.executeUpdate();
                                  p = (PreparedStatement) con.prepareStatement("delete from CART where ISBN = "+i+" and USER_NAME = '"+userName+"'");
                                  p.executeUpdate();
                                  b.setDisable(true);

                              }
                              catch (SQLException e) {
                                  // TODO Auto-generated catch block
                                  e.printStackTrace();
                              }



                        }
                    });

                   counter++;
            }

        }
        catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


        //back button
        Button back = new Button("Back");
        back.setStyle("-fx-background-color: darkslateblue; -fx-text-fill: white;");
        gridPane.add(back, 0, 400/30);
        back.setOnAction(new EventHandler<ActionEvent>() {
            @Override
            public void handle(ActionEvent event) {
                current.close();
                prev.show();
            }
        });

        //Setting size for the pane
        gridPane.setMinSize(600, 400);

        //Setting the padding
        gridPane.setPadding(new Insets(10, 10, 10, 10));

        //Setting the vertical and horizontal gaps between the columns
        gridPane.setVgap(30);
        gridPane.setHgap(30);

        if(counter == 0 ) {
            Text noRes = new Text("Cart is Empty");
            gridPane.add(noRes,600/120 , 400/60 );
            noRes.setStyle("-fx-font: normal bold 20px 'serif' ");
        }
        else {
            Text ISBN = new Text("ISBN");
            Text copies = new Text("COPIES");
            Button purchaseAll = new Button("Purchase All");

            // Add Nodes to GridPane
            gridPane.add(ISBN,0,0);
            gridPane.add(copies,1,0);
            gridPane.add(purchaseAll, 4 , 400/30);

            // set styling
            ISBN.setStyle("-fx-font: normal bold 15px 'serif' ");
            copies.setStyle("-fx-font: normal bold 15px 'serif' ");
            purchaseAll.setStyle("-fx-background-color: darkslateblue; -fx-text-fill: white;");



            purchaseAll.setOnAction(new EventHandler<ActionEvent>() {
                @Override
                public void handle(ActionEvent event) {
                    try {
                          Connection con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/Online_Book_Store?useSSL=false", "root", "ahmed666" );
                          PreparedStatement p = (PreparedStatement) con.prepareStatement("update BOOK_STORE set BOOK_COPIES = BOOK_COPIES - ( select COPIES"
                                + " from CART where BOOK_STORE.ISBN = ISBN and USER_NAME ='"+userName+"') where ISBN IN ( select ISBN from CART );");
                          System.out.println("update BOOK_STORE set BOOK_COPIES = BOOK_COPIES - ( select COPIES"
                                    + " from CART where BOOK_STORE.ISBN = ISBN and USER_NAME ='"+userName+"')  where ISBN IN ( select ISBN from CART ); ");
                          p.executeUpdate();
                          p = (PreparedStatement) con.prepareStatement("delete from CART where USER_NAME = '"+userName+"'");
                          p.executeUpdate();
                          purchaseAll.setDisable(true);
                          for(int i =0 ; i< list.size() ;i++) {
                              list.get(i).setDisable(true);
                          }

                      }
                      catch (SQLException e) {
                          // TODO Auto-generated catch block
                          e.printStackTrace();
                      }
                }
            });


        }
        Scene scene = new Scene(gridPane);
        setScene(scene);


    }

}