Currently I have a SQL statement that inserts rows in multiple tables such as “state table”, “city table” and “zipcode table”, etc using JDBCTemplate.queryForObject using one query using templates.

jdbcTemplate.queryForObject(
                            myQuery,
                            (result, rowNum) -> {
                                return (UUID) result.getObject("id");
                            }, stateName, stateAb, county);

I’m trying to convert it into a batch insert using JDBCTemplate.batchUpdate to make it faster and more efficient for the database.

The query uses templates, I have simplified the query so it inserts into two tables for the purposes of this question.

Example query:

WITH tmpstate AS (
INSERT INTO state (state_name, state_code)
        VALUES (initcap('Florida'), upper('FL'))
    ON CONFLICT (state_code)
        DO UPDATE SET
            state_name = excluded.state_name
        RETURNING
            id
), tmpcounty AS (
INSERT INTO county (county, state_id)
        VALUES (initcap('Broward County'),
            (
                SELECT
                    id
                FROM
                    tmpstate
                LIMIT 1))
    ON CONFLICT (county,
        state_id)
        DO UPDATE SET
            county = excluded.county
        RETURNING
            id,
            county
)
SELECT
    id FROM tmpcounty;

Below is the method I currently have after converting it into batchUpdate

public int[] insertPropertyBatch(List<Address> addressList) {

    return template.batchUpdate(
            "with tmpstate as (insert into state (state_name, state_code) " +
                    "values (initcap(?), upper(?)) on conflict (state_code) do update set state_name = excluded.state_name returning id)," +
                    " tmpcounty as ( insert into real_estate.county (county, state_id) values ( initcap(?), ( select id from tmpstate limit 1) ) on conflict (county, state_id) do update set county = excluded.county returning id, county )" +
                    "select id from tmpcounty;",
            new BatchPreparedStatementSetter() {
                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    Address address = addressList.get(i);
                    ps.setString(1, address.getStateName());
                    ps.setString(2, address.getStateAb());
                    ps.setString(3, address.getCounty());
                }

                @Override
                public int getBatchSize() {
                    return addressList.size();
                }
            });
}

From what I have noticed, when there is a conflict, the application throws the following error

Caused by: org.postgresql.util.PSQLException: ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
  Hint: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

How can I achieve my goal without getting the PSQLException exception?