import java.io.*;
import java.text.*;
import java.util.*;
import java.sql.*;

public class erotima3 {
    public static void main(String args[]){
        String url = "jdbc:sqlserver://altebaran.dmst.aueb.gr:1433;" +
                           "databaseName=DB32;user=G532;password=;";
  //  Database credentials
        Connection dbcon ;
        Statement stmt;
        ResultSet rs;
        int cr_number;
        int card_for_delete;
        int cust_code=0;
        System.out.println("Δώσε τον αριθμό της πιστωτικής κάρτας που θέλεις να διαγράψεις:");
        Scanner input = new Scanner(System.in);
        card_for_delete = input.nextInt();

                                  /* declare ODBC conectivity */
// STEP 2: REGISTER JDBC DRIVER
        try {Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  } //dynamically load the driver's class file into memory, which automatically registers it
        catch(java.lang.ClassNotFoundException e)
        {System.out.print("ClassNotFoundException: ");
        System.out.println(e.getMessage());}
            /* execute SQL statements */
        try {
//STEP 3: OPEN A CONNECTION
                dbcon = DriverManager.getConnection(url); //establish a connection using the DriverManager.getConnection() method
            stmt = dbcon.createStatement(); /*Creating Statement Objectdefine the methods and properties that enable
            you to send SQL or PL/SQL commands and receive data from your database.*/
            rs = stmt.executeQuery("SELECT card_number, customer_code FROM credit_card");/*The SQL statements that read data from a database query,
            return the data in a result set. --When you perform a query against the database you get back a ResultSet.
            You can then traverse this ResultSet to read the result of the query.*/

//STEP 5: Extract data from result set
            while (rs.next()) {  //the //apo to select from where pigene apo katw (diatreksei ton pinaka)
                cust_code = rs.getInt("customer_code");
            }
            rs.close();
//Retrieve by column name
            String sql1 = "DELETE FROM payment WHERE customer_code="+cust_code;
            stmt.executeUpdate(sql1);
            String sql2 = "DELETE FROM purchase WHERE card_number="+card_for_delete;
            stmt.executeUpdate(sql2);
            String sql3 = "DELETE FROM credit_card WHERE card_number="+card_for_delete;
            stmt.executeUpdate(sql3);
            stmt.close(); //Closing Statement Object
            dbcon.close();
            }
        catch(SQLException e)
        {
        System.out.print("SQLException: ");
        System.out.println(e.getMessage());}
    }
}
/*
*Import the packages: Requires that you include the packages containing the JDBC classes needed for database programming. Most often, using import java.sql.* will suffice.

*Register the JDBC driver: Requires that you initialize a driver so you can open a communications channel with the database.

*Open a connection: Requires using the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with a database server.

*Execute a query: Requires using an object of type Statement for building and submitting an SQL statement to delete records from a table. This Query makes use of the WHERE clause to delete conditional records.

*Clean up the environment: Requires explicitly closing all database resources versus relying on the JVM's garbage collection./*