skip to content

What does @SqlConfig configure, and when would you set errorMode, separator, or dataSource?

level: seniorimportance: should knowfreq 45%

answer

  1. errorMode: FAIL/CONTINUE/IGNORE_FAILED_DROPS
  2. separator ; else EOF for procedures
  3. dataSource/transactionManager = bean names
  4. transactionMode INFERRED vs ISOLATED
  5. backed by ScriptUtils/ResourceDatabasePopulator

basics

~10 s

@SqlConfig customizes how @Sql parses and runs scripts: errorMode (fail vs ignore failed statements), separator (statement delimiter, e.g. ;), commentPrefixes, blockCommentStart/End, encoding, dataSource and transactionManager bean names, and transactionMode.

solid answer

~40 s

`@SqlConfig` tunes script parsing and execution for `@Sql`. Common attributes: `errorMode` — `FAIL_ON_ERROR` (default via DEFAULT), `CONTINUE_ON_ERROR` (log and keep going), or `IGNORE_FAILED_DROPS` (ignore only failing DROP/DELETE, useful for idempotent teardown). `separator` — the statement delimiter, default `;`; set `"@@"` or `ScriptUtils.EOF_STATEMENT_SEPARATOR` for scripts with no separators or stored-proc bodies. `dataSource` / `transactionManager` — bean names to disambiguate when the context has more than one. `transactionMode` — `INFERRED` (reuse the test transaction) vs `ISOLATED` (own committed transaction). Plus `encoding`, `commentPrefixes`, `blockCommentStartDelimiter`/`EndDelimiter`. You can attach it locally as `@Sql(config = @SqlConfig(...))` or globally as a class-level `@SqlConfig` that all `@Sql`s in the class inherit and can override.

code

java · 12 lines
java
@Sql(
    scripts = "/procedures.sql",
    config = @SqlConfig(
        dataSource = "reportingDataSource",   // pick among multiple DataSources
        transactionManager = "reportingTxManager",
        separator = ScriptUtils.EOF_STATEMENT_SEPARATOR, // whole file = 1 statement
        errorMode = SqlConfig.ErrorMode.IGNORE_FAILED_DROPS,
        encoding = "UTF-8"
    )
)
@Test
void loadsStoredProcedures() { /* ... */ }

go deeper

for a junior

Aware @SqlConfig exists to tweak script behavior.

for a middle

Knows errorMode and separator and can set them per @Sql.

for a senior

Explains ErrorMode variants precisely, multi-datasource selection, transactionMode, and class-level default merging.

for a principal

Ties config to ScriptUtils/ResourceDatabasePopulator internals and standardizes conventions (encoding, teardown idempotency) across a suite.

## Purpose `@org.springframework.test.context.jdbc.SqlConfig` controls **how** `@Sql` scripts are parsed and executed. It's supplied either inside a specific `@Sql` via its `config` attribute, or **class-level** as a standalone `@SqlConfig` that provides defaults inherited by every `@Sql` in that class (local values override the class-level ones; unset attributes fall back to framework defaults). ## Key attributes ### errorMode (`SqlConfig.ErrorMode`) - `DEFAULT` — inherit; effectively `FAIL_ON_ERROR` when nothing overrides. - `FAIL_ON_ERROR` — any failing statement fails the test. Safest, catches typos. - `CONTINUE_ON_ERROR` — failures are logged and execution continues. Handy for best-effort seeds. - `IGNORE_FAILED_DROPS` — only failures from **DROP** (and drop-like) statements are ignored; everything else still fails. Classic for teardown scripts that drop objects that may not exist. ### separator The statement delimiter used to split the script into statements. Default is `;`. Special values via `org.springframework.jdbc.datasource.init.ScriptUtils`: `EOF_STATEMENT_SEPARATOR` (`"^^^ END OF SCRIPT ^^^"`) treats the whole file as one statement — needed for stored procedures / PL-SQL blocks that themselves contain `;`. You can also use custom separators like `"@@"`. ### Comments - `commentPrefixes` (default `--`) — single-line comment markers. - `blockCommentStartDelimiter` / `blockCommentEndDelimiter` (default `/*` and `*/`). ### encoding Character encoding of the script file (e.g. `"UTF-8"`); default is the platform default, so set it for non-ASCII data. ### dataSource / transactionManager Bean **names**. When the ApplicationContext has multiple `DataSource` or `PlatformTransactionManager` beans, `@Sql` can't guess; you name the right one. Otherwise the single bean is auto-detected. ### transactionMode (`SqlConfig.TransactionMode`) - `DEFAULT`/`INFERRED` — if there's a transaction manager, run the script within the **existing test-managed transaction** if present, else create one. This makes before-scripts get rolled back with the test. - `ISOLATED` — always run in its **own transaction that commits immediately**, independent of the test's rollback. Use when you need seeded data to survive a rolled-back test, or to commit teardown. ## Precedence / merging A class-level `@SqlConfig` sets defaults; an `@SqlConfig` inside a specific `@Sql` overrides per attribute. Unset attributes use the enum `DEFAULT`/empty-string sentinels, which resolve to framework defaults. ## Underlying engine `@Sql` delegates to Spring JDBC's `ResourceDatabasePopulator` / `ScriptUtils`, which is why the separator/comment/error semantics mirror those classes exactly. ## Gotchas - `IGNORE_FAILED_DROPS` ignores *only* drop failures, not all errors — people wrongly expect it to swallow everything. - Forgetting `separator = ScriptUtils.EOF_STATEMENT_SEPARATOR` on a stored-procedure script splits it at internal `;` and breaks. - Multiple DataSources without naming one throws at runtime. - `encoding` defaults to the platform charset — a source of CI-vs-local flakiness with special characters. ## When to use Reach for `@SqlConfig` when default parsing doesn't fit: procedural scripts (separator), multi-datasource contexts (dataSource), idempotent teardown (`IGNORE_FAILED_DROPS`), or data that must survive rollback (`ISOLATED`).

  • What exactly does errorMode = IGNORE_FAILED_DROPS ignore?
    Only failures caused by DROP statements (e.g. dropping a table/object that doesn't exist). Any other failing statement still fails the test — it is not a blanket CONTINUE_ON_ERROR.
  • Your context has two DataSource beans and @Sql fails to resolve one. Fix?
    Add @SqlConfig(dataSource = "<beanName>") (and transactionManager if ambiguous) so the script targets the intended bean.
  • How do you run a script whose statements are separated only by end-of-file, like a PL/SQL block?
    Set separator = ScriptUtils.EOF_STATEMENT_SEPARATOR so the whole file is treated as one statement and internal semicolons aren't split points.

saying these in an interview costs you the question

  • Saying IGNORE_FAILED_DROPS ignores all errors
  • Thinking dataSource takes a DataSource instance rather than a bean name string
  • Believing separator can't be changed from ;

context

resolving links…