CODEWITHSUNDEEP
javamysqljdbc
Tanveer Jafri
Tanveer Jafri

Reputation: 25

Getting duplicate key exception while doing JDBC transaction

I'm trying to add designation and its salary using a JDBC transaction. The problem is this throws an exception about duplicate key.

This is the first time I put some invalid data in salary columns and after that everything is correct. It shows duplicate key exception for designation id but the designation id is not stored already and not even for first attempt. The first transaction that is invalid is rolled back, but storing on next time it shows duplicate key exception.

Below is my code:- 

  public boolean addDesignation(ObservableList nodeList) throws SQLException {
        Connection demo = getConnection();
        demo.setAutoCommit(false);
        Savepoint savePoint = demo.setSavepoint("savePoint");
        try {
        PreparedStatement addDesig =  demo.prepareStatement(
                "INSERT INTO `designation`(`desig_id`,`dept_id`,`desig_name`,`desig_desc`) VALUES (?,?,?,?)");
        PreparedStatement addSal =  demo.prepareStatement("INSERT INTO `salary` "
                + "(`desig_id`, `basic`, `house_rent`, `conveyance`, `medical`, `dearness`,`others_allowances`,"
                + " `income_tax`, `pro_tax`, `emp_state_insu`, `absence_fine`, `others_deductions`, `month`)"
                + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");

        addDesig.setString(1 , nodeList.get(0).toString());
        addDesig.setString(2,  nodeList.get(1).toString());
        addDesig.setString(3, nodeList.get(2).toString());
        addDesig.setString(4,  nodeList.get(3).toString());
        addDesig.executeUpdate();

        addSal.setString(1, nodeList.get(0).toString());
        addSal.setInt(2, Integer.parseInt(nodeList.get(4).toString()));
        addSal.setInt(3, Integer.parseInt(nodeList.get(5).toString()));
        addSal.setInt(4, Integer.parseInt(nodeList.get(6).toString()));
        addSal.setInt(5, Integer.parseInt(nodeList.get(7).toString()));
        addSal.setInt(6,Integer.parseInt(nodeList.get(8).toString()));
        addSal.setInt(7,Integer.parseInt(nodeList.get(9).toString()));
        addSal.setInt(8, Integer.parseInt(nodeList.get(10).toString()));
        addSal.setInt(9, Integer.parseInt(nodeList.get(11).toString()));
        addSal.setInt(10, Integer.parseInt(nodeList.get(12).toString()));
        addSal.setInt(11, Integer.parseInt(nodeList.get(13).toString()));
        addSal.setInt(12, Integer.parseInt(nodeList.get(14).toString()));
        addSal.setString(13, nodeList.get(15).toString());
        addSal.executeUpdate();
         demo.commit();

         return true;

    } catch (SQLException ex) {
        demo.rollback(savePoint);

        Logger.getLogger(DatabaseHandler.class.getName()).log(Level.SEVERE, null, ex);
    }

    return false;
}

these are two tables and im trying to add that data in my first attempt failed but not store due roll back

Upvotes: 0

Views: 1133

Answers (1)

forpas
forpas

Reputation: 164099

There are 2 INSERT statements in your code.
The 1st for designation table:

"INSERT INTO `designation`(`desig_id`,`dept_id`,`desig_name`,`desig_desc`) VALUES (?,?,?,?)"

here it looks like desig_id is the primary key (maybe autoincrement in which case you must not supply a value at all).
Are you sure the value that you supply for this column does not already exist in the table?

The 2nd for the salary table: 

"INSERT INTO `salary` " + "(`desig_id`, `basic`, `house_rent`, `conveyance`, `medical`, `dearness`,`others_allowances`," + " `income_tax`, `pro_tax`, `emp_state_insu`, `absence_fine`, `others_deductions`, `month`)" + " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)"

in this case it is not clear since you did not post the CREATE statement of the table, which is the primary key.
So you have to check, if the value (or values if it's a multi column key), violate the uniqueness of the key.

Upvotes: 1

Related Questions