How to perform batch update in Spring with a list of maps?

Question

New to Spring, I am trying to insert a List<Map<String, Object>> into a table. Until now I have been using the SqlParameterSource for batch update, which works fine when a java bean is supplied to them. Something like this:

    @Autowired
    private NamedParameterJDBCTemplate v2_template;

    public int[] bulkInsertIntoSiteTable(List<SiteBean> list){
            SqlParameterSource[] batch = SqlParameterSourceUtils
                    .createBatch(list.toArray());
            int[] updateCounts = v2_template
                    .batchUpdate(
                            "insert into sitestatus (website, status, createdby) values (:website, :status, :username)",
                            batch);

            return updateCounts;

        }

However, I tried the same technique with a list of maps in place of a bean, it failed (rightly so).

public int[] bulkInsertIntoSiteTable(List<Map<String, Object>> list){
        SqlParameterSource[] batch = SqlParameterSourceUtils
                .createBatch(list.toArray());
        int[] updateCounts = v2_template
                .batchUpdate(
                        "insert into sitestatus (website, status, createdby) values (:website, :status, :username)",
                        batch);

        return updateCounts;

    }

The above code failed with the following exception:

Exception in thread "main" org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'website': Invalid property 'website' of bean class [org.springframework.util.LinkedCaseInsensitiveMap]: Bean property 'website' is not readable or has an invalid getter method: Does the return type of the getter match the parameter type of the setter?
    at org.springframework.jdbc.core.namedparam.NamedParameterUtils.buildValueArray(NamedParameterUtils.java:322)
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils$1.setValues(NamedParameterBatchUpdateUtils.java:45)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:893)
    at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:1)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:587)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615)
    at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:884)
    at org.springframework.jdbc.core.namedparam.NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(NamedParameterBatchUpdateUtils.java:40)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.batchUpdate(NamedParameterJdbcTemplate.java:303)
    at tester.utitlies.dao.VersionTwoDao.bulkInsertIntoSites(VersionTwoDao.java:21)
    at tester.utitlies.runner.Main.main(Main.java:28)

It fails as it considers the list to be a batch of beans, I guess. I cannot find a way to perform a batch update in Spring with a list of maps and using NamedParameterJDBCTemplate. Please advice.

Exanimate

Answer 1

As per Spring NamedParameterJDBCTemplate docs, found here, this method can be used for batch updating with maps.

int[] batchUpdate(String sql, Map<String,?>[] batchValues)

The real challange was to a get an array of Map<String, Object> from a corresponding List<Map<String, Object>>. I used the following code to get the array and perform the batch update.

public static Map<String, Object>[] getArrayData(List<Map<String, Object>> list){
        @SuppressWarnings("unchecked")
        Map<String, Object>[] maps = new HashMap[list.size()];

        Iterator<Map<String, Object>> iterator = list.iterator();
        int i = 0;
        while (iterator.hasNext()) {
            Map<java.lang.String, java.lang.Object> map = (Map<java.lang.String, java.lang.Object>) iterator
                    .next();
            maps[i++] = map;
        }

        return maps;
    }

Exanimate

Why do they have Map<String,?>[] batchValues and not Map<String, Object>[] batchValues? – Cephalization

Had success doing it this way: Map<String, Object>[] batchValues = list.toArray(new HashMap[0]); namedParameterJdbcTemplate.batchUpdate("...", batchValues); – About

@Corin Fletcher - Could you please paste your code ? – Personage

Answer 2

You can not directly use your bean in NamedParameterJdbcTemplate's batchUpdate, NamedParameterJdbcTemplate's batchUpdate accepts params in form of array only. Either an array of SqlParameterSource or an array of Map.

Here I will demonstrate how you can use array of Map to achieve your goal.

Considering the above problem, convert your List of Bean into array of map, Each map corresponds to one row to be inserted or One Bean object, field and its value are stored as key-value pair inside the map where key is the field name and value is the value of the field under consideration.

@Autowired
private NamedParameterJDBCTemplate v2_template;

public int[] bulkInsertIntoSiteTable(List<SiteBean> list){
        String yourQuery = "insert into sitestatus (website, status, createdby) 
               values (:website, :status, :username)"
        
        Map<String,Object>[] batchOfInputs = new HashMap[list.size()];
        int count = 0;
        for(SiteBean sb : list.size()){
           Map<String,Object> map = new HashMap();
           map.put("website",sb.getWebsite());
           map.put("status",sb.getStatus());
           map.put("username",sb.getUsername());
           batchOfInputs[count++]= map;
        }
        int[] updateCounts = v2_template.batchUpdate(yourQuery,batchOfInputs);

        return updateCounts;

    }

Pastille

Answer 3

There is another way to avoid @SuppressWarnings("unchecked").

public static final String INSERT_INTO = "INSERT INTO {0} ({1}) VALUES ({2})";

private NamedParameterJdbcTemplate template;


template.batchUpdate(insertQuery(rowMaps.get(0)), batchArgs(mapRows));

/**
 * Create SQL instruction INSERT from Map record
 *
 * @return literal INSERT INTO [schema].[prefix][table_name] (column1, column2, column3, ...)
 * VALUES (value1, value2, value3, ...);
 */
public String insertQuery(Map<String, String> rowMap) {
    String schemaTable = Objects.isNull(getSchema()) ? table : getSchema() + "." + table;
    String splittedColumns = String.join(",", rowMap.keySet());
    String splittedValues = rowMap.keySet().stream()
            .map(s -> ":" + s).collect(Collectors.joining(","));
    return MessageFormat.format(INSERT_INTO, schemaTable, splittedColumns, splittedValues);
}

private MapSqlParameterSource[] batchArgs(List<Map<String, String>> mapRows) {
    int size = mapRows.size();
    MapSqlParameterSource[] batchArgs = new MapSqlParameterSource[size];
    IntStream.range(0, size).forEach(i -> {
        MapSqlParameterSource args = new MapSqlParameterSource(mapRows.get(i));
        batchArgs[i] = args;
    });

    return batchArgs;
}

Best regards

Morava

Answer 4

in your statement SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(list.toArray()) will not work in case if list or list of map object is singleton. In such case below option will be of use.

public int[] bulkInsertIntoSiteTable(List<Map<String, Object>> list){           
    Map<String, Object>[] maps = new HashMap[list.size()];
    IntStream.range(0, list.size()).forEach(x -> maps[x] = new HashMap<>(list.get(x)));     
    int[] updateCounts = v2_template
            .batchUpdate(
                    "insert into sitestatus (website, status, createdby) values (:website, :status, :username)",
                    maps);

    return updateCounts;

}

Footstone

Answer 5

One working snippet :

 public List builkInsert(String insert,List details) {

        Map<String, Object>[] maps = new HashMap[details.size()];
        
        Map<String, Object>[] batchValues = (Map<String, Object>[]) details.toArray(maps);
        
        int[] response= namedParameterJdbcTemplate.batchUpdate(insert, batchValues);
    
        return Arrays.asList(response);
    }

Glucoside

Answer 6

I tested with code.

Map<String, Object>[] rs = new Map<String, Object>[1];

Map<String, Object> item1 = new HashMap<>();
item1.put("name", "Tien Nguyen");
item1.put("age", 35);
rs[0] = item1;

NamedParameterJdbcTemplate jdbc = new NamedParameterJdbcTemplate(datasource); 
// datasource from JDBC.
jdbc.batchUpdate("call sp(:name, :age)", rs);

Hope it is easy to know. Thanks

Pillar