Comparing Three Methods for Inserting Data into SQL Server with Spring Boot

The author tests three insertion strategies for a SQL Server database running on Java 1.8 with Spring Boot: repeatedly executing a single INSERT statement, constructing SQL via XML, and using batch processing. The conclusion is to use single inserts for small volumes (around 20 rows) and batch processing for larger volumes, while avoiding XML‑based SQL concatenation.

Methods compared

Repeated single‑row INSERT statements

XML‑based SQL concatenation

Batch processing using MyBatis and ExecutorType.BATCH

Code snippets

<insert id="insertByBatch" parameterType="java.util.List">
    INSERT INTO tb_item VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (newId(),#{item.uniqueCode},#{item.projectId},#{item.name},#{item.type},#{item.packageUnique},
        #{item.isPackage},#{item.factoryId},#{item.projectName},#{item.spec},#{item.length},#{item.weight},
        #{item.material},#{item.setupPosition},#{item.areaPosition},#{item.bottomHeight},#{item.topHeight},
        #{item.serialNumber},#{item.createTime}</foreach>
</insert>

public interface ItemMapper extends Mapper<Item> {
    int insertByBatch(List<Item> itemList);
}

@Service
public class ItemService {
    @Autowired
    private ItemMapper itemMapper;
    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    // Batch processing
    @Transactional
    public void add(List<Item> itemList) {
        SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        ItemMapper mapper = session.getMapper(ItemMapper.class);
        for (int i = 0; i < itemList.size(); i++) {
            mapper.insertSelective(itemList.get(i));
            if (i % 1000 == 999) {
                session.commit();
                session.clearCache();
            }
        }
        session.commit();
        session.clearCache();
    }

    // XML‑based concatenation (not recommended)
    @Transactional
    public void add1(List<Item> itemList) {
        itemList.insertByBatch(itemMapper::insertSelective);
    }

    // Simple loop insertion
    @Transactional
    public void add2(List<Item> itemList) {
        itemList.forEach(itemMapper::insertSelective);
    }
}

The accompanying test class generates 1,000 dummy Item objects and measures insertion time for each method. Results show that single inserts and batch inserts both complete within a few hundred milliseconds for 10–25 rows, while XML concatenation fails for 500–1,000 rows due to the SQL Server limit of 2,100 parameters per RPC call.

Conclusions

For small data sets, repeated single inserts are simplest and sufficiently fast.

XML‑based SQL concatenation should be avoided because it is error‑prone and can hit parameter limits.

Batch processing offers the best performance for large data volumes with low overhead.

Therefore, choose the insertion method based on the expected number of rows: single inserts for a few dozen rows, batch processing for hundreds or thousands, and never use XML concatenation.