Java + MySQL - 更新时覆盖行

Question

我开始为管理工程师创建一个数据库程序，为他们分配调用等等，并将所有这些链接在一起。但是，我遇到了一个问题，当我尝试通过使用我的程序来更新/更改数据库中的一些信息时，它反而将表中的所有其他项目更改为刚刚更改的内容。例如，如果我将工程师 1 的名称更改为“a”，它将覆盖表中的其他实体，因此工程师 2 现在的名称将是“a”以及工程师 1 的属性。

我附上了为更新表而编写的代码以及数据库的 SQL 代码。

如果有人可以帮助我了解这里的问题，我将不胜感激，我可以在需要时提供其他信息。

谢谢

    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

-- 2017 年 9 月 22 日星期五 12:56:05-- 模型:新模型版本:1.0-- MySQL Workbench 正向工程

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

首先使用PreparedStatement。

如果您想更新特定工程师，您应该使用 sql where 部分标识数据。

在你的情况下可能是:

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();