Performance issues with spring's jdbctemplate batch update

performance issues with spring's jdbctemplate batch update

These two days accidentally found a jdbctemplate batch update performance problem, the problem is such a one-time bulk Delete and insert 3,000 records, incredibly used 400s I was also frightened. I also looked at the code is really using the JdbcTemplate batchupdate method, the usage is not wrong, the normal execution of SQL batch update 3,000 data should also be second level, but it is not clear why the use of JdbcTemplate performance will be so much worse than expected, Later after repeated testing to find that the original parameter type of trouble, specifically, see the code snippet below. jdbctemplate.batchupdate Code snippet before modification

public void Batchupdateacl (list<auth> authbeans) {list<object[]> deleteparams = new arraylist<object[]
    > ();

    list<object[]> insertparams = new arraylist<object[]> ();
        for (Auth Auth:authbeans) {String ResourceID = Auth.getresourceid ();
        String Targetid = Auth.gettargetid (); String TargetType = auth.gettype () = = null?
        AuthTargetType.ROLE.name (): Auth.gettype (). Name ();

        int ACL = Authenum.formateaclcode (Auth.getacls ());
        Deleteparams.add (new object[] {targetid, ResourceID, targetType});
    Insertparams.add (new object[] {targetid, ResourceID, TargetType, ACL}); } String sql = "DELETE from" + table_name + "WHERE target=?" and resource=?
    and targettype=? ";

    Bulk delete changes before the call method Jdbc.batchupdate (SQL, deleteparams);
    sql = "INSERT into" + table_name + "(target, resource, TargetType, ACL) VALUES (?,?,?,?)";
Bulk INSERT modification before the call method Jdbc.batchupdate (SQL, insertparams); }

modified Jdbctemplate.batchupdate Code Snippet

public void Batchupdateacl (list<auth> authbeans) {final list<object[]> deleteparams = new Arraylist<ob
    Ject[]> ();

    Final list<object[]> insertparams = new arraylist<object[]> ();
        for (Auth Auth:authbeans) {String ResourceID = Auth.getresourceid ();
        String Targetid = Auth.gettargetid (); String TargetType = auth.gettype () = = null?
        AuthTargetType.ROLE.name (): Auth.gettype (). Name ();

        int ACL = Authenum.formateaclcode (Auth.getacls ());
        Deleteparams.add (new object[] {targetid, ResourceID, targetType});
    Insertparams.add (new object[] {targetid, ResourceID, TargetType, ACL}); } String sql = "DELETE from" + table_name + "WHERE target=?" and resource=?
    and targettype=? "; Bulk Delete Modified call mode jdbc.batchupdate (SQL, new BatchPreparedStatementSetter () {@Override public void Setva Lues (preparedstatement PS, int i) throws SQLException {object[] args = deleteparams.geT (i);
            Ps.setstring (1, (String) args[0]);
            Ps.setstring (2, (String) args[1]);
        Ps.setstring (3, (String) args[2]);
        } @Override public int getbatchsize () {return 0;

    }
    });
    sql = "INSERT into" + table_name + "(target, resource, TargetType, ACL) VALUES (?,?,?,?)"; Bulk Insert modified call mode jdbc.batchupdate (SQL, new BatchPreparedStatementSetter () {@Override public void Setva
            Lues (preparedstatement PS, int i) throws SQLException {object[] args = Insertparams.get (i);
            Ps.setstring (1, (String) args[0]);
            Ps.setstring (2, (String) args[1]);
            Ps.setstring (3, (String) args[2]);
        Ps.setstring (4, (String) args[3]);
        } @Override public int getbatchsize () {return 0;
}
    }); }

The

method of specific invocation changes somewhat, the original invocation method Jdbc.batchupdate (SQL, deleteparams); The argument is list\