Java + MySQL - Overwriting rows when updating

Question

I'm starting to create a database program for managing engineers, assigning calls to them and such and having this all linked together. However, I have ran into a problem where when I try to update/change a bit of information in the database through the use of my program it instead changed all the other items in the table to what it has just been changed to. For example, if I changed engineer 1's name to 'a', it would overwrite the other entities in the table so engineer 2 would have its name as 'a' now along with the properties of engineer 1.

I have attached the code I have written to update the table along with the SQL code for my database.

I'd appreaciate if someone could help me understand what is wrong here and I can provide other information when requested.

Thanks

    private void btnUpdateActionPerformed(java.awt.event.ActionEvent evt) {                                          
    try{
        con = Connect.ConnectDB();
        String sql = "update engineers set first_name ='" + textFirstName.getText()+ "',last_name='"+ textLastName.getText()+ "',middle_name='" + textMiddleName.getText()+ "',postcode='" + textPostcode.getText() + "',engineer_address='" + textAddress.getText() + "',engineer_dob='" + textDOB.getText() + "',comments='" + textComments.getText()+ "'";
        pst = con.prepareStatement(sql);
        pst.execute();
        JOptionPane.showMessageDialog(this, "Updated","Engineer",JOptionPane.INFORMATION_MESSAGE);
        btnUpdate.setEnabled(false);

    }catch(HeadlessException | SQLException ex){
        JOptionPane.showMessageDialog(this,ex);
    }

-- MySQL Script generated by MySQL Workbench

-- Fri Sep 22 12:56:05 2017 -- Model: New Model Version: 1.0 -- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`customers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`customers` (
  `customer_id` INT NOT NULL AUTO_INCREMENT,
  `customer_name` VARCHAR(45) NOT NULL,
  `telephone` VARCHAR(45) NOT NULL,
  `postcode` VARCHAR(45) NOT NULL,
  `address` VARCHAR(45) NOT NULL,
  `city` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`customer_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`engineers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`engineers` (
  `engineer_id` INT NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NOT NULL,
  `postcode` VARCHAR(45) NOT NULL,
  `active_job` VARCHAR(45) NOT NULL,
  `on_holiday` VARCHAR(45) NOT NULL,
  `engineer_address` VARCHAR(45) NOT NULL,
  `engineer_postcode` VARCHAR(45) NOT NULL,
  `comments` VARCHAR(45) NOT NULL,
  `middle_name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`engineer_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`machines`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`machines` (
  `serial_number` VARCHAR(45) NOT NULL,
  `customer_id` INT NOT NULL,
  `meter_reading` VARCHAR(45) NOT NULL,
  `install_date` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`serial_number`),
  INDEX `fk_customer_id_idx` (`customer_id` ASC),
  CONSTRAINT `fk_customer_id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `mydb`.`customers` (`customer_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`new_call`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`new_call` (
  `call_id` INT NOT NULL AUTO_INCREMENT,
  `serial_number` VARCHAR(45) NOT NULL,
  `customer_id` INT NOT NULL,
  `engineer_id` INT NOT NULL,
  `call_fault` VARCHAR(45) NOT NULL,
  `call_type` VARCHAR(45) NOT NULL,
  `date_recieved` VARCHAR(45) NOT NULL,
  `start_date` VARCHAR(45) NOT NULL,
  `engineer_dob` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`call_id`),
  INDEX `fk_serial_number_idx` (`serial_number` ASC),
  INDEX `fk_engineer_id_idx` (`engineer_id` ASC),
  INDEX `fk1_customer_id_idx` (`customer_id` ASC),
  CONSTRAINT `fk_serial_number`
    FOREIGN KEY (`serial_number`)
    REFERENCES `mydb`.`machines` (`serial_number`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk_engineer_id`
    FOREIGN KEY (`engineer_id`)
    REFERENCES `mydb`.`engineers` (`engineer_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `fk1_customer_id`
    FOREIGN KEY (`customer_id`)
    REFERENCES `mydb`.`customers` (`customer_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`users` (
  `username` VARCHAR(45) NOT NULL,
  `password` VARCHAR(45) NOT NULL)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Answer 1

first of all use PreparedStatement.

if you want to update a specific engineer you should identify the data with the sql where section.

in your case that could be:

PreparedStatement statement = con.prepareStatement("update engineers set first_name =? " +
            ",last_name=?, middle_name=?" +
            ",postcode=?,engineer_address=?" +
            ",engineer_dob=?" +
            ",comments=? " +
            "where engineer_id=?"); // <--- WHERE SECTION
statement.setString(1, "Chuck");
statement.setString(2, "Norris");
// and so on and so on...
statement.setInteger(8, idOfEngineer);

statement.executeUpdate();