1. ホーム
  2. java

[解決済み] java.sql.SQLIntegrityConstraintViolationException

2022-02-12 21:40:13

質問

保存挿入/更新コマンドを実行すると、このエラーが発生します。

SEVERE: null
java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL110207185137350' defined on 'EMPLOYEE'.
null, Boris Wilkins

もし、このエラーの原因がわかる方がいらっしゃいましたら、教えていただければ幸いです。

以下はそのコードです。

 /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing pkg in the database */
    public void save(Employee pkg) throws DataException {
        Connection conn = null;
        try {
            conn = ConnectionPool.getInstance().get();
            save(pkg, conn);
            conn.commit();

        } catch (Exception e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                throw new DataException("We're currently upgrading our site to serve you better.", e);
            }
            throw new DataException("Problem saving the Employee", e);

        } finally {
            ConnectionPool.getInstance().release(conn);

        }//update
    }

    /** Internal method to update a pkg in the database */
    void save(Employee emp, Connection conn) {
        // update the cache
        Cache.getInstance().put(emp.getId(), emp);
        // if not dirty, return
        if (!emp.isDirty()) {
            return;
        }
        // call either update() or insert()
        if (emp.isObjectAlreadyInDB()) {
            update(emp, conn);
        } else {
            insert(emp, conn);
        }

    }//save

    /** Saves an existing pkg to the database */



    //NEED HELP WITH EXECUTE STATEMENT!!!


    private void update(Employee pkg, Connection conn) {
        try {
            // run the update SQL
            PreparedStatement pstmt = conn.prepareStatement("UPDATE Employee SET id=?, name1=?, username1=?, password=?, type=? WHERE id=?");
            pstmt.setString(1, pkg.getId());
            pstmt.setString(2, pkg.getName1());
            pstmt.setString(3, pkg.getUserName1());
            pstmt.setString(4, pkg.getPassword());
            pstmt.setString(5, pkg.getType());
            pstmt.setString(6, pkg.getId());

            pstmt.executeUpdate();
            pstmt.close();
            // update the dirty variable
            pkg.setDirty(false);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDAO.class.getName()).log(Level.SEVERE, null, ex);
        }


    }

    /** Inserts a new pkg into the database */
    private void insert(Employee pkg, Connection conn) {
        try {
            // run the insert SQL
            PreparedStatement pstmt = conn.prepareStatement("INSERT into Employee (id, name1, username1, password, type) values(?, ?, ?, ?, ?)");
            pstmt.setString(1, pkg.getId());
            pstmt.setString(2, pkg.getName1());
            pstmt.setString(3, pkg.getUserName1());
            pstmt.setString(4, pkg.getPassword());
            pstmt.setString(5, pkg.getType());
            // update the dirty variable

            pstmt.execute();
            pstmt.close();
            pkg.setDirty(false);
        } catch (SQLException ex) {
            Logger.getLogger(EmployeeDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

alreadyInDB のコード。

/** Returns whether the object is in the DB or not */
  boolean isObjectAlreadyInDB() {
    return objectAlreadyInDB;
  }//isObjectAlreadyInDB

  /** 
   * Sets whether the object is already in the DB.  This method
   * is called ONLY from the DAO responsible for this object.
   */
  void setObjectAlreadyInDB(boolean objectAlreadyInDB) {
    this.objectAlreadyInDB = objectAlreadyInDB;
  }//setObjectAlreadyInDB

解決方法は?

このステートメント emp.isObjectAlreadyInDB() は明らかに失敗して false そのため、このコードを実行するたびに同じ従業員が挿入され、データベース・テーブルに設定した主キーまたは一意制約に違反することになります。