CODEWITHSUNDEEP
javajdbcarraylist
Nesma Zaki
Nesma Zaki

Reputation: 57

Insert values from arraylist into database

I use addbatch() and executebatch() to insert values from ArrayList into database.

But when I run the code this error message appeared:

Exception in thread "main" java.sql.BatchUpdateException: Duplicate entry '5' for key 'PRIMARY'  
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2020)  
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1451)

This is the data:
1 A R1
7 A R1
2 B R1
3 C R1
4 B R1
8 A R1
5 A R1
9 C R1
6 C R1

This part of the code: 

    public void insertDB(ArrayList<Eventlog> d) throws SQLException {  
        String insert = "insert into eventlog values(?,?,?)";  
        PreparedStatement ps = conn.prepareStatement(insert);  
        for (int k = 0; k < d.size()-1; k++) {  
            ps.setInt(1, d.get(k).getEvent_id()); // event id  
            ps.setString(3,d.get(k).getResources()); // resource  
            ps.setString(2,d.get(k).getActivity()); // activity  
            ps.addBatch();  
        }  
        ps.executeBatch();  
    }

Upvotes: 3

Views: 8842

Answers (2)

Pradeep S
Pradeep S

Reputation: 185

From your question it is evident that you are getting exception because you are trying to insert a duplicate key(5) in the table. If I understand correctly, as per your comment you are first getting the data from database and then changing it. Then you are trying to save this modified data to the database. So in this case you need to supply an update query instead of insert. Please try the following code:

   public void insertDB(ArrayList<Eventlog> d) throws SQLException {  
    String query = "update eventlog set resources = '?', activity = '?' where event_id = ?";  
    PreparedStatement ps = conn.prepareStatement(query);  
    for (int k = 0; k < d.size()-1; k++) {              
        ps.setString(1,d.get(k).getResources()); // resource  
        ps.setString(2,d.get(k).getActivity()); // activity  
        ps.setInt(3, d.get(k).getEvent_id()); // event id  
        ps.addBatch();  
    }  
    ps.executeBatch();  
}

Please replace column names in query string with your actual column names from the database table.

Upvotes: 0

Philip Devine
Philip Devine

Reputation: 1169

The code looks fine, but make sure your table is truncated before you run this since it looks like this is more or less a static set of fields.

To clarify further, primary key 5 already exists in your database and you need to delete it before running this code.

Upvotes: 1

Related Questions