//mycotroller...
package fxapplication;

import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.Button;
import javafx.scene.control.ComboBox;
import javafx.scene.control.TextField;
import javax.swing.JOptionPane;

/**
 * FXML Controller class
 *
 * @author PHILSERVER
 */
public class SearchController implements Initializable {

     @FXML
    private Connection con;

     @FXML
    private Statement stmt;

      @FXML
    private ResultSet rs;

    @FXML
    private ComboBox cbGender;

    @FXML
    private TextField tfLocation;

    @FXML
    private TextField tfFirstname;

    @FXML
    private TextField tfSearch;

    @FXML
    private Button update;

    @FXML
    private Button insert;

    @FXML
    private Button delete;

    @FXML
    private TextField tfSecondname;

    @FXML
    void search(ActionEvent event) {

 if (tfSearch.getText().equals("")){
     JOptionPane.showMessageDialog(null," Enter firstname please !!!");
    }else{
    if(checkName()){
   search();

}else{
   JOptionPane.showMessageDialog(null,"firstname does not exist");
}

    }

    }


    @FXML
    void deleteRecord(ActionEvent event) {
 try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch(ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null,"Unable to register class "+e.getMessage());
        }

        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost/level2","root","addison");
            stmt = con.createStatement();
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null,"Unable to connect to database "+e.getMessage());
        }

        try{
            String sql;
            sql="delete from sstudent where firstname='"+tfSearch.getText()+"'";
            int result=stmt.executeUpdate(sql);
            JOptionPane.showMessageDialog(null," Record deleted sucessfully");
            tfSearch.setText(null);
            tfFirstname.setText(null);
            tfSecondname.setText(null);
            cbGender.setValue(null);
            tfLocation.setText(null);

        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null,"Unable to delete record "+e.getMessage());
        }
    }

    @FXML
    void updateRecord(ActionEvent event) {
    try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch(ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null,"Unable to register class "+e.getMessage());
        }

        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost/level2","root","addison");
            stmt = con.createStatement();
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null,"Unable to connect to database "+e.getMessage());
        }


        try{
            String sql;
            sql="update sstudent set secondname='"+tfSecondname.getText()+"',"
            +"location='"+tfLocation.getText()+"',"
            +"gender='"+cbGender.getValue().toString()+"'"
            +" where firstname='"+tfSearch.getText()+"'";


            int result=stmt.executeUpdate(sql);
            JOptionPane.showMessageDialog(null," Record updated sucessfully");
       } catch (SQLException f) {
           JOptionPane.showMessageDialog(null,"Unable to update record "+f.getMessage());
       }


    }

    @FXML
    void insertRecord(ActionEvent event) {
try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch(ClassNotFoundException e) {
                JOptionPane.showMessageDialog(null,"Unable to register class "+e.getMessage());
            }

            try {
                con = DriverManager.getConnection("jdbc:mysql://localhost/level2","root","addison");
                  stmt=con.createStatement();
                }catch (SQLException e){
                    JOptionPane.showMessageDialog(null,"Unable to connect to database "+e.getMessage());
                }


    try {
    String sql;
    sql="insert into sstudent(firstname,secondname,gender,location) values ("
      +"'"+tfFirstname.getText()+"',"
      +"'"+tfSecondname.getText()+"',"
      +"'"+cbGender.getValue().toString()+"',"
      +"'"+tfLocation.getText()+"')";

    int result=stmt.executeUpdate(sql);
    JOptionPane.showMessageDialog(null,"record saved sucessfully ");
    } catch (Exception e) {
    JOptionPane.showMessageDialog(null,"Unable to insert record "+e.getMessage());
    }


    }


      private boolean checkName(){
    try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch(ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null,"Unable to register class "+e.getMessage());
        }

        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost/level2","root","addison");
            stmt = con.createStatement();
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null,"Unable to connect to database "+e.getMessage());
        }
         try{
             String sql;
             sql="select * from sstudent where firstname='"+tfSearch.getText()+"'";
             rs=stmt.executeQuery(sql);
             if(rs.next()){
             return true;
             }
         }catch (SQLException e){
         JOptionPane.showMessageDialog(null,"unable to retrieve record"+ e.getMessage());
         }
         return false;
         }

      private void search(){
       try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch(ClassNotFoundException e) {
            JOptionPane.showMessageDialog(null,"Unable to register class "+e.getMessage());
        }

        try {
            con = DriverManager.getConnection("jdbc:mysql://localhost/level2","root","addison");
            stmt = con.createStatement();
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null,"Unable to connect to database "+e.getMessage());
        }

        try {
            String sql;
            sql = "select * from sstudent where firstname='"+tfSearch.getText()+"'";
            rs = stmt.executeQuery(sql);
            while(rs.next()){
             tfFirstname.setText(rs.getString("firstname"));
             tfSecondname.setText(rs.getString("secondname"));
             cbGender.setValue(rs.getString("gender"));
             tfLocation.setText(rs.getString("location"));
            }
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null,"Unable to retrieve record "+e.getMessage());
        }
      }
    @Override
    public void initialize(URL url, ResourceBundle rb) {



      cbGender.getItems().addAll("male","female");
    }

}

//main class....
package fxapplication;

import javafx.application.Application;
import javafx.fxml.FXMLLoader;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.scene.media.Media;
import javafx.scene.media.MediaPlayer;
import javafx.stage.Stage;

/**
 *
 * @author PHILSERVER
 */
public class FXApplication extends Application {

    @Override
    public void start(Stage stage) throws Exception {
        Parent root = FXMLLoader.load(getClass().getResource("search.fxml"));

        Scene scene = new Scene(root);

        stage.setScene(scene);

        stage.show();

// my scene builder forms....