Results 1 to 12 of 12
  1. #1
    Wasp is offline Member
    Join Date
    Nov 2016
    Posts
    32
    Rep Power
    0

    Default Duplicate entry for key primary Eror Code: 1062 [on hold]

    I can insert row with mysql workbench query. But i cant do that with java mysql connector. Mysql jdbc connector version: 5.1.38. I have 2 primary and foreign key Table;

    Java Code:
    CREATE TABLE teachers_lessons
    (
    teacher_id              INT,
    lesson_id               INT,
    CONSTRAINT pk_teachers_lessons_user_id_lesson_id
        PRIMARY KEY (teacher_id, lesson_id),
    CONSTRAINT fk_teachers_lessons__teacher_id
        FOREIGN KEY (teacher_id)
            REFERENCES teachers (teacher_id),
    CONSTRAINT fk_teachers_lessons_lesson_id
        FOREIGN KEY (lesson_id)
            REFERENCES lessons (lesson_id)
    );
    can insert this row with Mysql Workbench. Tehere is no problem.

    Java Code:
    INSERT INTO teachers_lessons VALUES (40454237,5);
    INSERT INTO teachers_lessons VALUES (40454237,6);
    But in Java i got this eror;

    Java Code:
    Error Message: Duplicate entry '40401556-6' for key 'PRIMARY'
    Error Code: 1062
    SQL State: 23000
    There is my Java code;

    Java Code:
    private boolean insertTeacherLessons(Teacher entity) throws SQLException {
        Set<Lesson> lessons = entity.getLessons();
        boolean result = false;
            final String sql = "INSERT INTO teachers_lessons VALUES (?,?)";
            try (PreparedStatement statement = connection.prepareStatement(sql)) {
                for (Lesson lesson : lessons) {
                    statement.setInt(1, entity.getId());
                    statement.setInt(2, lesson.getId());
                }
                result = statement.executeUpdate() == lessons.size();
            }
        return result;
    }

  2. #2
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,965
    Rep Power
    7

    Default Re: Duplicate entry for key primary Eror Code: 1062 [on hold]

    You need to either call addBatch() and after the loop call executeBatch(), or execute the update INSIDE the loop. Now, all you're doing is setting parameters the entire loop and in the end, the last set of parameters will be executed.

    Second, if the database says "Duplicate entry", then it's a duplicate entry. Use the workbench to see what record matches the PK.
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2™ © 2013

  3. #3
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    25

    Default Re: Duplicate entry for key primary Eror Code: 1062 [on hold]

    Yes, as SurfMan points out, at the moment you are actually only adding a single row to the database, the last one. So you need to use the batching methods.

    That aside, debug your code by printing out the values for the two ids for each INSERT, then compare with the data in the database.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  4. #4
    Wasp is offline Member
    Join Date
    Nov 2016
    Posts
    32
    Rep Power
    0

    Default Re: Duplicate entry for key primary Eror Code: 1062 [on hold]

    In workbench its working. No error. Only with jdbc i got this error.
    Thıs 2 query will record with workbench no duplicate error.
    1
    2
    INSERT INTO teachers_lessons VALUES (40454237,5);
    INSERT INTO teachers_lessons VALUES (40454237,6);
    Now i change my method like this but still same error;

    private boolean insertTeacherLessons(Teacher entity) throws SQLException {
    Set<Lesson> lessons = entity.getLessons();
    boolean result = false;
    final String sql = "INSERT INTO teachers_lessons(teacher_id, lesson_id) VALUES (?,?)";
    try (PreparedStatement statement = connection.prepareStatement(sql)) {
    int[] count;
    for (Lesson lesson : lessons) {
    statement.setInt(1, entity.getId());
    statement.setInt(2, lesson.getId());
    statement.addBatch();
    }

    count = statement.executeBatch();
    result = statement.executeUpdate() == count.length;
    }
    return result;
    }
    Last edited by Wasp; 02-16-2017 at 04:27 PM.

  5. #5
    Wasp is offline Member
    Join Date
    Nov 2016
    Posts
    32
    Rep Power
    0

    Default Re: Duplicate entry for key primary Eror Code: 1062 [on hold]

    Quote Originally Posted by Tolls View Post
    Yes, as SurfMan points out, at the moment you are actually only adding a single row to the database, the last one. So you need to use the batching methods.

    That aside, debug your code by printing out the values for the two ids for each INSERT, then compare with the data in the database.
    Composite primary key. No duplicate error in workbench. Only with JDBC.

  6. #6
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,965
    Rep Power
    7

    Default Re: Duplicate entry for key primary Eror Code: 1062 [on hold]

    Now you are executing the last statement twice: executeBatch() AND executeUpdate() resulting, again, in a duplicate key. Use one or the other.
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2™ © 2013

  7. #7
    Wasp is offline Member
    Join Date
    Nov 2016
    Posts
    32
    Rep Power
    0

    Default Re: Duplicate entry for key primary Eror Code: 1062 [on hold]

    Its work with this method;

    private boolean insertTeacherLessons(Teacher entity) throws SQLException {
    Set<Lesson> lessons = entity.getLessons();

    boolean result = false;
    final String sql = "INSERT INTO teachers_lessons(teacher_id, lesson_id) VALUES (?,?)";
    try (PreparedStatement statement = connection.prepareStatement(sql)) {
    int[] count;
    for (Lesson lesson : lessons) {
    System.out.println("dongo");
    statement.setInt(1, entity.getId());
    statement.setInt(2, lesson.getId());
    statement.addBatch();
    }
    count = statement.executeBatch();
    result = count.length == lessons.size();
    }
    return result;
    }
    Thanks friends.

  8. #8
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    13,541
    Rep Power
    25

    Default Re: Duplicate entry for key primary Eror Code: 1062 [on hold]

    Java Code:
    System.out.println("dongo");
    That's a rather useless debug statement.
    Surely you should be printing the ids?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  9. #9
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,965
    Rep Power
    7

    Default Re: Duplicate entry for key primary Eror Code: 1062 [on hold]

    Quote Originally Posted by Tolls View Post
    Java Code:
    System.out.println("dongo");
    That's a rather useless debug statement.
    Surely you should be printing the ids?
    My code is littered with "dongo", "bongo", and even "congo". You don't do that?
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2™ © 2013

  10. #10
    DarrylBurke's Avatar
    DarrylBurke is offline Forum Police
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    12,059
    Rep Power
    24

    Default Re: Duplicate entry for key primary Eror Code: 1062 [on hold]

    Quote Originally Posted by SurfMan View Post
    My code is littered with "dongo", "bongo", and even "congo". You don't do that?
    That's just rongo
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

  11. #11
    Wasp is offline Member
    Join Date
    Nov 2016
    Posts
    32
    Rep Power
    0

    Default Re: Duplicate entry for key primary Eror Code: 1062 [on hold]

    Quote Originally Posted by SurfMan View Post
    My code is littered with "dongo", "bongo", and even "congo". You don't do that?
    Thats a "döngü", turkish word. To english its loop :) I just want to see how much turn my loop :)

  12. #12
    SurfMan's Avatar
    SurfMan is offline Godlike
    Join Date
    Nov 2012
    Location
    The Netherlands
    Posts
    1,965
    Rep Power
    7

    Default Re: Duplicate entry for key primary Eror Code: 1062 [on hold]

    Quote Originally Posted by Wasp View Post
    Thats a "döngü", turkish word. To english its loop :) I just want to see how much turn my loop :)
    Yeah, we get your drift. I was just joking. Considering the nature of your problem, we were expecting you to log something like this:
    Java Code:
    for (Lesson lesson : lessons) {
       System.out.println("entity id = " + entity.getId());
       System.out.println("lesson id = " + lesson.getId());
       statement.setInt(1, entity.getId());
       statement.setInt(2, lesson.getId());
       statement.addBatch();
    }
    "It's not fixed until you stop calling the problem weird and you understand what was wrong." - gimbal2™ © 2013

Similar Threads

  1. Replies: 0
    Last Post: 11-25-2008, 09:24 AM
  2. Hibernate Duplicate Entry problem
    By mc1392 in forum Advanced Java
    Replies: 0
    Last Post: 09-02-2008, 10:03 PM
  3. ZipOutputStream "duplicate entry"
    By new_2_java in forum New To Java
    Replies: 0
    Last Post: 02-14-2008, 04:28 PM
  4. How to prevent duplicate username entry in database?
    By anki1234 in forum JavaServer Pages (JSP) and JSTL
    Replies: 4
    Last Post: 01-09-2008, 08:02 AM
  5. Duplicate entry in registration form!!!
    By anki1234 in forum Advanced Java
    Replies: 1
    Last Post: 01-04-2008, 08:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •