Prepared statement is executing the batches not in the same order that were given

259
May 14, 2017, at 04:16 AM

I have the following peace of code to read data from XML file which contains the database settings and millions of records to be inserted to a db, in my code I am using StAX to read the XML and set the connection settings, after that I am reading the data to be inserted to the db and store in a LinkedList and send it to the prepared statement, my code works fine but very slow if I make it add the batch and execute it right away but when I add a condition to execute the batch when it contains 1000 batch it execute them but not in the same order here is my code for reading the XML:

public class STAXParser {
    private ConnectionSettings connectionSettings = new ConnectionSettings();
    private QueryBuilder queryBuilder = new QueryBuilder();
    private List<QueryBuilder> queryBuilderList = new LinkedList<>();
    private DBSettings settings = new DBSettings();
    public void parseDocuments() throws Exception {
        XMLInputFactory inputFactory = XMLInputFactory.newFactory();
        try (InputStream stream = Files.newInputStream(Paths.get("./employees.xml"))) {
            XMLStreamReader reader = inputFactory.createXMLStreamReader(stream);
            reader.nextTag();
            if (!reader.getLocalName().equals("import-request"))
                throw new XMLStreamException("Invalid root element: " + reader.getLocalName());
            startElementData(reader);
        }
    }
    private void startElementData(XMLStreamReader reader) throws Exception {
        while (reader.nextTag() == XMLStreamConstants.START_ELEMENT) {
            if (reader.getLocalName().equals("connection-settings")) {
                checkName(reader);
                settings.getSettings(connectionSettings);
            } else if (reader.getLocalName().equals("data")) {
                while (reader.hasNext()) {
                    parseData(reader);
                    if (reader.getLocalName().equals("import-request"))
                        return;
                }
            }
        }
        System.out.println(System.currentTimeMillis());
        settings.closeConnection();
    }
    private void checkName(XMLStreamReader reader) throws XMLStreamException {
        while (reader.nextTag() == XMLStreamConstants.START_ELEMENT) {
            if (reader.getLocalName().equals("url")) {
                StringBuilder a = new StringBuilder();
                a.append(reader.getElementText()).append("?rewriteBatchedStatements=true");
                connectionSettings.setUrl(a.toString());
            } else if (reader.getLocalName().equals("username"))
                connectionSettings.setUsername(reader.getElementText());
            else if (reader.getLocalName().equals("password"))
                connectionSettings.setPassword(reader.getElementText());
            else if (reader.getLocalName().equals("driverClassName"))
                connectionSettings.setDriver(reader.getElementText());
        }
    }
    private void parseData(XMLStreamReader reader) throws Exception {
        while (reader.nextTag() == XMLStreamConstants.START_ELEMENT)
            processData(reader);
    }
    private void processData(XMLStreamReader reader) throws Exception {
        List<String> values = new ArrayList<>();
        List<QName> columns = new ArrayList<>();
        for (int i = 0; i < reader.getAttributeCount(); i++) {
            columns.add(reader.getAttributeName(i));
            values.add(reader.getAttributeValue(i));
        }
        queryBuilder.setTableName(reader.getLocalName());
        queryBuilder.setColumns(columns);
        queryBuilder.setValues(values);
        queryBuilderList.add(queryBuilder);
        if (queryBuilderList.size() <= 1000) {
            settings.invokePreparedStatement(queryBuilderList);
            queryBuilderList.clear();
        } else
            processData(reader);
    }
    public static void main(String[] args) throws Exception {
        System.out.println(System.currentTimeMillis());
        STAXParser s = new STAXParser();
        s.parseDocuments();
        System.out.println(System.currentTimeMillis());
    }
}

And here is where I set the connection and prepared statement:

public class DBSettings {
    private Connection connection;
    private BasicDataSource dataSource;
    private Map<String, PreparedStatement> listOfPs = new HashMap<>();
    private List<String> listOfQueries = new ArrayList<>();
    final int batchSize = 1000;
    int count = 0;
    public void getSettings(ConnectionSettings settings) {
        dataSource = new BasicDataSource();
        dataSource.setDriverClassName(settings.getDriver());
        dataSource.setUrl(settings.getUrl());
        dataSource.setUsername(settings.getUsername());
        dataSource.setPassword(settings.getPassword());
        connectToDB();
    }
    public void commit() {
        try {
            connection.commit();
            System.out.println("Commited");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void invokePreparedStatement(List<QueryBuilder> queryBuilderList) throws SQLException {
        PreparedStatement preparedStatement;
        StringBuilder query = new StringBuilder();
        for (QueryBuilder queryBuilder : queryBuilderList) {
            query.append("INSERT INTO ")
                    .append(queryBuilder.getTableName())
                    .append(" (");
            for (int i = 0; i < queryBuilder.getColumns().size(); i++) {
                if (i != 0)
                    query.append(", ");
                getColumns(queryBuilder, query, i);
            }
            query.append(") VALUES (");
            try {
                for (int i = 0; i < queryBuilder.getValues().size(); i++) {
                    if (i != 0) {
                        query.append(", ");
                    }
                    query.append("?");
                }
                query.append(")");
                preparedStatement = connection.prepareStatement(query.toString());
                Collection<PreparedStatement> values = listOfPs.values();
                if (!listOfPs.containsKey(queryBuilder.getTableName())) {
                    listOfPs.put(queryBuilder.getTableName(), preparedStatement);
                    preparedStatement = connection.prepareStatement(query.toString());
                }
                if (listOfPs.keySet().contains(queryBuilder.getTableName())) {
                    for (int i = 0; i < queryBuilder.getValues().size(); i++) {
                        preparedStatement.setObject(i + 1, queryBuilder.getValues().get(i));
                        if (i == queryBuilder.getValues().size() - 1) {
                            preparedStatement.addBatch();
                        }
                    }
                }

                if (++count % batchSize == 0) {
                    preparedStatement.executeBatch();
                    System.out.println("EXECUTING : " + queryBuilder.getTableName());
                    preparedStatement.clearParameters();
                    preparedStatement.clearBatch();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public void closeConnection() {
        try {
            commit();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void getColumns(QueryBuilder queryBuilder, StringBuilder query, int i) {
        QName columns;
        columns = (queryBuilder.getColumns().get(i));
        query.append(columns);
    }
    private void connectToDB() {
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            System.out.println("Connected");
        } catch (SQLException e) {
            System.out.println("Could not connect to database");
            e.printStackTrace();
        }
    }
}

if I removed this if statement the code work as expected:

if (++count % batchSize == 0) {
    preparedStatement.executeBatch();
    System.out.println("EXECUTING : " + queryBuilder.getTableName());
    preparedStatement.clearParameters();
    preparedStatement.clearBatch();
}

Can someone advice why is that happnning?

READ ALSO
How to send attachment with Amazon SES

How to send attachment with Amazon SES

I'm trying to send an attachment, but I'm getting this error:

406
grouping floats to find most common general number

grouping floats to find most common general number

im working with ebay, and i have a list of 100 prices of items that soldwhat i want to do is separate each floating-point price into groups, and then count up the groups to sortof determine the most common general price for this item, so i can automate...

215
bokeh map plot. lat/long to x and y

bokeh map plot. lat/long to x and y

I am trying to plot some circle markers in bokeh, python on stamer toner mapI have locations from google map api in such format:

486
How to debug django ajax function when using djangorestframework decorator?

How to debug django ajax function when using djangorestframework decorator?

How to debug ajax function when using djangorestframework decorator

387