JDBC PreparedStatement Batch continue insert on error

Question

Hello guy I create a Batch with a PreparedStatement in java like this

for(Item  item: list){
    ps.setString(1, item.getSome());
    ps.setString(2, item.getFoo());
    ps.setString(3, item.getBatman());
    statement.addBatch();

    if (++count % batchSize == 0) {
        results = ps.executeBatch(); //execute parcial batch

        if (results != null)
           System.out.println(results.length);
    }

}
results= ps.executeBatch(); //execute rest of batch

The datebase server is a MySQL, in table to insert I have several restrictions

By these restrictions when I insert generates errors

I want run the batch and omit errors, at this moment throw a Exception a ends batch

Before I create the batch I have a Big for the save one by one like

//seudocode level
For item
Try{
   insert item
}catch(E){nothing happens}

But it is very slow, in some cases, the batch procces 4000 item, insert 1500 and omit the rest

How do I do with the batch?

EDIT

I use weblogic to make conections with this driver mysql-connector-java-commercial-5.0.3-bin

I test this properties

1.

continueBatchOnError=true

2.

rewriteBatchedStatements=true

3.

continueBatchOnError=true
rewriteBatchedStatements=true

And add connection.setAutoCommit(false); but continues throw the exception in duplicates

EDIT

forgot to mention, I use for connection Hibernate + Spring

The only For-Save example is made in Hibernate, but for performance i tried use a JDBC Batch, in other procces in the webapp also use JDBC with the connection from Hibernate and works well

This is the full code

@Transactional
public void saveMany(final List<Item> items) {
    getMySqlSession().doWork(new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {

            StringBuilder sb = new StringBuilder();
            sb.append("INSERT INTO `FRT_DB`.`ITEM` ");
            sb.append("( ");
            sb.append("`masterID`, ");
            sb.append("`agent`, ");
            sb.append("`rangeID`) ");
            sb.append("VALUES ");
            sb.append("( ");
            sb.append("?, ");
            sb.append("?, ");
            sb.append("?) ");

            int[] results = null;

            PreparedStatement ps = null;

            try {
                connection.setAutoCommit(false);
                ps = connection.prepareStatement(sb.toString());


                final int batchSize = 250;
                int count = 0;

                for (Item item : items) {

                    if (item.getMasterId() != null) {
                        ps.setInt(1, item.getMasterId());
                    } else
                        ps.setNull(1, java.sql.Types.INTEGER);

                    if (item.getAgent() != null) {
                        ps.setString(2, item.getAgent());
                    } else
                        ps.setNull(2, Types.VARCHAR);

                    if (item.getRangeId() != null)
                        ps.setInt(3, item.getRangeId());
                    else
                        ps.setNull(3, Types.INTEGER);

                    ps.addBatch();

                    if (++count % batchSize == 0) {
                        results = ps.executeBatch();

                        if (results != null)
                            System.out.println(results.length);

                    }

                }

                results= ps.executeBatch();
            } catch (Exception e) {
                e.printStackTrace();
            }

        }
    });
}

This produce next Exception

java.sql.BatchUpdateException: Duplicate entry '1-000002725' for key 'masterID'

But I need to continue

spring + hibernate settings interfere with the properties of jdbc? I dont know

Answer 1

I think it can be summed up in one word IGNORE

When you run the batch with this

sb.append("INSERT IGNORE INTO `FRT_DB`.`ITEM` ");

This NOT throw a Exception realted with constrains, this pass over, and still old data in your rows

If you need save the 'new data' you change for INSERT ... ON DUPLICATE KEY Statement, but , right now think dont need it.

Commit or rollback is not necesary in your code @Transactional work for you.

Your big try { only crash in SqlException not in BatchUpdateException

You only need add allowMultiQueries beacuse other and default true

http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html