CODEWITHSUNDEEP
mysqljdbcinsert
Nickolay Komar
Nickolay Komar

Reputation: 340

Getting duplicate count when executing INSERT IGNORE via JDBC

Is it possible to get the duplicate count when executing MySQL "INSERT IGNORE" statement via JDBC?

For example, when I execute an INSERT IGNORE statement on the mysql command line, and there are duplicates I get something like

Query OK, 0 rows affected (0.02 sec) Records: 1 Duplicates: 1 Warnings: 0

Note where it says "Duplicates: 1", indicating that there were duplicates that were ignored.

Is it possible to get the same information when executing the query via JDBC?

Thanks.

Upvotes: 4

Views: 1870

Answers (3)

Mark T.
Mark T.

Reputation: 19

You can use the ROW_COUNT() and FOUND_ROWS() functions to determine the number of inserted rows and duplicates when doing INSERT IGNORE.

For Example :

SELECT ROW_COUNT(), FOUND_ROWS()
    INTO myRowCount, myFoundRows;
myInsertedRows = myRowCount;
myDuplicateRows =  myFoundRows - myInsertedRows;
COMMIT;

Upvotes: 1

Dojo
Dojo

Reputation: 5684

You can get the rows affected value via JDBC. Just subtract it from the number of rows you inserted.

Bear in mind that rows affected also includes rows that are indirectly affected by the query. So, if there are any triggers that affect other rows, rows affected will include those rows as well.

See: http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#executeUpdate()

Upvotes: 0

Joshua Martell
Joshua Martell

Reputation: 7212

I believe you can retrieve this by issuing SHOW WARNINGS after your insert.

http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html

Upvotes: 1

Related Questions