Inserting values from an array into a database
I am using addbatch()
and executebatch()
to insert values from ArrayList
into a database.
But when I run the code, the following error appears:
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 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 piece of 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();
}
source to share
It can be seen from your question that you are getting an exception because you are trying to insert a duplicate key (5) into the table. If I understand correctly, as per your comment, you first get data from the database and then change it. Then you try to save this changed data to the database. So in this case, you need to provide an update request instead of an 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 the column names in the row with the query
actual column names from the database table.
source to share