JDBC Testing Support

/**
 * @Sql is used to annotate a test class or test method to configure SQL scripts 
 * to be executed during integration tests.
 */
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
@Repeatable(SqlGroup.class)
public @interface Sql {
    
    /**
     * Alias for scripts().
     * @return an array of SQL script resource paths
     */
    @AliasFor("scripts")
    String[] value() default {};
    
    /**
     * The paths to the SQL scripts to execute.
     * @return an array of SQL script resource paths
     */
    @AliasFor("value")
    String[] scripts() default {};
    
    /**
     * Inline SQL statements to execute.
     * @return an array of SQL statements
     */
    String[] statements() default {};
    
    /**
     * When the SQL scripts and statements should be executed.
     * @return the execution phase
     */
    ExecutionPhase executionPhase() default ExecutionPhase.BEFORE_TEST_METHOD;
    
    /**
     * Configuration for the SQL scripts.
     * @return the SqlConfig annotation
     */
    SqlConfig config() default @SqlConfig;
    
    /**
     * Enumeration of phases that dictate when SQL scripts are executed.
     */
    enum ExecutionPhase {
        
        /**
         * The configured SQL scripts and statements will be executed before the corresponding test method.
         */
        BEFORE_TEST_METHOD,
        
        /**
         * The configured SQL scripts and statements will be executed after the corresponding test method.
         */
        AFTER_TEST_METHOD,
        
        /**
         * The configured SQL scripts and statements will be executed before the corresponding test class.
         */
        BEFORE_TEST_CLASS,
        
        /**
         * The configured SQL scripts and statements will be executed after the corresponding test class.
         */
        AFTER_TEST_CLASS
    }
}

/**
 * @SqlConfig defines metadata that is used to determine how to parse and execute 
 * SQL scripts configured via @Sql.
 */
@Target({})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface SqlConfig {
    
    /**
     * The bean name of the DataSource against which the scripts should be executed.
     * @return the DataSource bean name
     */
    String dataSource() default "";
    
    /**
     * The bean name of the PlatformTransactionManager that should be used to drive transactions.
     * @return the transaction manager bean name
     */
    String transactionManager() default "";
    
    /**
     * The mode to use when determining whether SQL scripts should be executed within a transaction.
     * @return the transaction mode
     */
    TransactionMode transactionMode() default TransactionMode.DEFAULT;
    
    /**
     * The encoding for the supplied SQL scripts, if different from the platform encoding.
     * @return the script encoding
     */
    String encoding() default "";
    
    /**
     * The character string used to separate individual statements within each script.
     * @return the statement separator
     */
    String separator() default ScriptUtils.DEFAULT_STATEMENT_SEPARATOR;
    
    /**
     * The prefix that identifies single-line comments within the SQL scripts.
     * @return the comment prefix
     */
    String commentPrefix() default ScriptUtils.DEFAULT_COMMENT_PREFIX;
    
    /**
     * The start delimiter that identifies block comments within the SQL scripts.
     * @return the block comment start delimiter
     */
    String blockCommentStartDelimiter() default ScriptUtils.DEFAULT_BLOCK_COMMENT_START_DELIMITER;
    
    /**
     * The end delimiter that identifies block comments within the SQL scripts.
     * @return the block comment end delimiter
     */
    String blockCommentEndDelimiter() default ScriptUtils.DEFAULT_BLOCK_COMMENT_END_DELIMITER;
    
    /**
     * The error mode to use when an error is encountered executing a SQL script.
     * @return the error mode
     */
    ErrorMode errorMode() default ErrorMode.DEFAULT;
    
    /**
     * Enumeration of transaction modes for executing SQL scripts.
     */
    enum TransactionMode {
        
        /**
         * Use the default transaction mode: execute scripts in a transaction if a transaction manager is available.
         */
        DEFAULT,
        
        /**
         * Execute scripts in isolated transactions that will be immediately committed.
         */
        ISOLATED,
        
        /**
         * Execute scripts without a transaction.
         */
        INFERRED
    }
    
    /**
     * Enumeration of error modes for SQL script execution.
     */
    enum ErrorMode {
        
        /**
         * Use the default error mode: fail fast and abort further processing on error.
         */
        DEFAULT,
        
        /**
         * Ignore failed SQL statements and continue processing.
         */
        IGNORE_FAILED_DROPS,
        
        /**
         * Continue processing and log failed SQL statements.
         */
        CONTINUE_ON_ERROR,
        
        /**
         * Fail fast and abort further processing on error.
         */
        FAIL_ON_ERROR
    }
}

/**
 * @SqlGroup is a container annotation that aggregates several @Sql annotations.
 */
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface SqlGroup {
    
    /**
     * An array of @Sql annotations.
     * @return the array of @Sql annotations
     */
    Sql[] value();
}

/**
 * @SqlMergeMode is used to annotate a test class to configure whether method-level 
 * @Sql declarations are merged with class-level @Sql declarations.
 */
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@Inherited
public @interface SqlMergeMode {
    
    /**
     * The merge mode to use.
     * @return the merge mode
     */
    MergeMode value();
    
    /**
     * Enumeration of modes that dictate whether method-level @Sql annotations should be merged with class-level @Sql annotations.
     */
    enum MergeMode {
        
        /**
         * Method-level @Sql annotations will be merged with class-level @Sql annotations.
         */
        MERGE,
        
        /**
         * Method-level @Sql annotations will override class-level @Sql annotations.
         */
        OVERRIDE
    }
}

/**
 * JdbcTestUtils is a collection of JDBC related utility functions 
 * intended to simplify standard database testing scenarios.
 */
public abstract class JdbcTestUtils {
    
    /**
     * Count the rows in the given table.
     * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations
     * @param tableName name of the table to count rows in
     * @return the number of rows in the table
     */
    public static int countRowsInTable(JdbcTemplate jdbcTemplate, String tableName);
    
    /**
     * Count the rows in the given table, using the provided WHERE clause.
     * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations
     * @param tableName name of the table to count rows in
     * @param whereClause the WHERE clause to append to the query
     * @return the number of rows in the table that match the provided WHERE clause
     */
    public static int countRowsInTableWhere(JdbcTemplate jdbcTemplate, String tableName, String whereClause);
    
    /**
     * Delete all rows from the specified tables.
     * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations
     * @param tableNames the names of the tables from which to delete
     * @return the total number of rows deleted from all specified tables
     */
    public static int deleteFromTables(JdbcTemplate jdbcTemplate, String... tableNames);
    
    /**
     * Delete rows from the given table, using the provided WHERE clause.
     * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations
     * @param tableName the name of the table from which to delete
     * @param whereClause the WHERE clause to append to the query
     * @return the number of rows deleted from the table
     */
    public static int deleteFromTableWhere(JdbcTemplate jdbcTemplate, String tableName, String whereClause);
    
    /**
     * Drop the specified tables.
     * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations
     * @param tableNames the names of the tables to drop
     */
    public static void dropTables(JdbcTemplate jdbcTemplate, String... tableNames);
    
    /**
     * Execute the given SQL script.
     * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations
     * @param resource the resource (potentially associated with a specific encoding) to load the SQL script from
     * @param continueOnError whether or not to continue without throwing an exception in the event of an error
     * @throws DataAccessException if there is an error executing a statement and continueOnError was false
     */
    public static void executeSqlScript(JdbcTemplate jdbcTemplate, Resource resource, boolean continueOnError)
            throws DataAccessException;
    
    /**
     * Execute the given SQL script using default settings for statement separators, 
     * comment delimiters, and exception handling flags.
     * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations
     * @param resource the resource (potentially associated with a specific encoding) to load the SQL script from
     * @throws DataAccessException if there is an error executing a statement
     */
    public static void executeSqlScript(JdbcTemplate jdbcTemplate, Resource resource) throws DataAccessException;
    
    /**
     * Read the given SQL script and return its contents as a String.
     * @param resource the resource to load the SQL script from
     * @return the contents of the SQL script
     * @throws IOException if there is an error reading from the resource
     */
    public static String readScript(Resource resource) throws IOException;
    
    /**
     * Read the given SQL script using the specified character encoding and return its contents as a String.
     * @param resource the resource to load the SQL script from
     * @param encoding the encoding to use when reading the script
     * @return the contents of the SQL script
     * @throws IOException if there is an error reading from the resource
     */
    public static String readScript(Resource resource, @Nullable String encoding) throws IOException;
    
    /**
     * Determine if the given SQL script contains the specified delimiter.
     * @param script the SQL script to search
     * @param delim the delimiter to search for
     * @return true if the script contains the delimiter
     */
    public static boolean containsSqlScriptDelimiters(String script, String delim);
    
    /**
     * Split the given SQL script into separate statements delimited by the provided delimiter character.
     * @param script the SQL script to split
     * @param delim the delimiter character
     * @return an array of individual SQL statements
     */
    public static String[] splitSqlScript(String script, char delim);
    
    /**
     * Split the given SQL script into separate statements delimited by the provided delimiter string.
     * @param script the SQL script to split
     * @param delim the delimiter string
     * @return an array of individual SQL statements
     */
    public static String[] splitSqlScript(String script, String delim);
}

/**
 * Configuration class for embedded database testing support.
 */
public class EmbeddedDatabaseBuilder {
    
    /**
     * Specify the type of embedded database.
     * @param databaseType the database type
     * @return this EmbeddedDatabaseBuilder
     */
    public EmbeddedDatabaseBuilder setType(EmbeddedDatabaseType databaseType);
    
    /**
     * Set the name of the embedded database.
     * @param databaseName the database name
     * @return this EmbeddedDatabaseBuilder
     */
    public EmbeddedDatabaseBuilder setName(String databaseName);
    
    /**
     * Add a SQL script to execute to populate the database.
     * @param sqlResource the SQL script resource
     * @return this EmbeddedDatabaseBuilder
     */
    public EmbeddedDatabaseBuilder addScript(String sqlResource);
    
    /**
     * Add multiple SQL scripts to execute to populate the database.
     * @param sqlResources the SQL script resources
     * @return this EmbeddedDatabaseBuilder
     */
    public EmbeddedDatabaseBuilder addScripts(String... sqlResources);
    
    /**
     * Specify a custom script configuration.
     * @param config the script configuration
     * @return this EmbeddedDatabaseBuilder
     */
    public EmbeddedDatabaseBuilder setScriptConfiguration(DatabasePopulator config);
    
    /**
     * Build the embedded database.
     * @return the EmbeddedDatabase instance
     */
    public EmbeddedDatabase build();
}

/**
 * Factory for creating embedded database instances for testing.
 */
public class EmbeddedDatabaseFactory {
    
    /**
     * Set the type of embedded database.
     * @param databaseType the database type
     */
    public void setDatabaseType(EmbeddedDatabaseType databaseType);
    
    /**
     * Set the configuration for populating the database.
     * @param databasePopulator the database populator
     */
    public void setDatabasePopulator(DatabasePopulator databasePopulator);
    
    /**
     * Set the configuration for cleaning up the database.
     * @param databaseCleaner the database cleaner
     */
    public void setDatabaseCleaner(DatabasePopulator databaseCleaner);
    
    /**
     * Factory method to create the embedded database instance.
     * @return the EmbeddedDatabase
     */
    public EmbeddedDatabase getDatabase();
}

/**
 * Interface representing an embedded database that can be shut down.
 */
public interface EmbeddedDatabase extends DataSource {
    
    /**
     * Shut down this embedded database.
     */
    void shutdown();
}

/**
 * Enumeration for the types of embedded databases supported.
 */
public enum EmbeddedDatabaseType {
    
    /** The Hypersonic SQL Database Engine */
    HSQL,
    
    /** The H2 Database Engine */
    H2,
    
    /** The Apache Derby Database */
    DERBY
}

import org.springframework.test.context.jdbc.*;
import org.springframework.test.jdbc.JdbcTestUtils;
import org.springframework.jdbc.core.JdbcTemplate;

// Basic SQL script execution
@SpringJUnitConfig(DatabaseTestConfig.class)
@Transactional
class UserRepositoryTest {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Autowired 
    private UserRepository userRepository;
    
    @Test
    @Sql("/test-data/users.sql")
    void shouldFindUserById() {
        User user = userRepository.findById(1L);
        assertThat(user.getName()).isEqualTo("John Doe");
    }
    
    @Test
    @Sql(scripts = "/test-data/users.sql", 
         executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
    @Sql(scripts = "/test-data/cleanup.sql", 
         executionPhase = Sql.ExecutionPhase.AFTER_TEST_METHOD)
    void shouldCleanupAfterTest() {
        // Test with setup and cleanup scripts
        int initialCount = JdbcTestUtils.countRowsInTable(jdbcTemplate, "users");
        assertThat(initialCount).isGreaterThan(0);
        
        userRepository.deleteAll();
        // Cleanup script will run after test
    }
}

// Inline SQL statements
@SpringJUnitConfig(DatabaseTestConfig.class)
@Transactional
class InlineSqlTest {
    
    @Test
    @Sql(statements = {
        "INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')",
        "INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com')"
    })
    void shouldExecuteInlineStatements() {
        int userCount = JdbcTestUtils.countRowsInTable(jdbcTemplate, "users");
        assertThat(userCount).isEqualTo(2);
    }
}

// Advanced SQL configuration
@SpringJUnitConfig(DatabaseTestConfig.class)
@Transactional
class AdvancedSqlConfigTest {
    
    @Test
    @Sql(
        scripts = "/complex-schema.sql",
        config = @SqlConfig(
            dataSource = "testDataSource",
            transactionManager = "testTransactionManager",
            transactionMode = SqlConfig.TransactionMode.ISOLATED,
            encoding = "UTF-8",
            separator = "@@",
            commentPrefix = "#",
            errorMode = SqlConfig.ErrorMode.CONTINUE_ON_ERROR
        )
    )
    void shouldUseCustomSqlConfiguration() {
        // Test with custom SQL script configuration
    }
}

// Multiple SQL annotations
@SpringJUnitConfig(DatabaseTestConfig.class)
@SqlGroup({
    @Sql(scripts = "/schema.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_CLASS),
    @Sql(scripts = "/lookup-data.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_CLASS)
})
class MultipleScriptsTest {
    
    @Test
    @Sql("/test-specific-data.sql")
    void shouldRunWithMultipleScripts() {
        // Class-level scripts + method-level script
    }
}

// SQL merge modes
@SpringJUnitConfig(DatabaseTestConfig.class)
@Sql("/base-data.sql")
@SqlMergeMode(SqlMergeMode.MergeMode.MERGE)
class MergedSqlTest {
    
    @Test
    @Sql("/additional-data.sql")
    void shouldMergeWithClassLevelSql() {
        // Both base-data.sql and additional-data.sql will be executed
    }
}

@SpringJUnitConfig(DatabaseTestConfig.class)
@Sql("/base-data.sql")
@SqlMergeMode(SqlMergeMode.MergeMode.OVERRIDE)
class OverriddenSqlTest {
    
    @Test
    @Sql("/replacement-data.sql")
    void shouldOverrideClassLevelSql() {
        // Only replacement-data.sql will be executed
    }
}

// JDBC test utilities usage
@SpringJUnitConfig(DatabaseTestConfig.class)
@Transactional
class JdbcUtilitiesTest {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Test
    @Sql("/test-data/sample-data.sql")
    void shouldUseJdbcTestUtils() {
        // Count rows in tables
        int totalUsers = JdbcTestUtils.countRowsInTable(jdbcTemplate, "users");
        int activeUsers = JdbcTestUtils.countRowsInTableWhere(
            jdbcTemplate, "users", "status = 'ACTIVE'");
        
        assertThat(totalUsers).isEqualTo(10);
        assertThat(activeUsers).isEqualTo(7);
        
        // Delete test data
        int deletedRows = JdbcTestUtils.deleteFromTableWhere(
            jdbcTemplate, "users", "created_date < '2023-01-01'");
        assertThat(deletedRows).isGreaterThan(0);
        
        // Clean up multiple tables
        JdbcTestUtils.deleteFromTables(jdbcTemplate, "user_roles", "users", "roles");
        
        // Verify cleanup
        assertThat(JdbcTestUtils.countRowsInTable(jdbcTemplate, "users")).isEqualTo(0);
    }
    
    @Test
    void shouldExecuteScriptDirectly() throws Exception {
        Resource script = new ClassPathResource("/test-data/setup.sql");
        JdbcTestUtils.executeSqlScript(jdbcTemplate, script, false);
        
        int rowCount = JdbcTestUtils.countRowsInTable(jdbcTemplate, "products");
        assertThat(rowCount).isGreaterThan(0);
    }
}

// Embedded database testing
@TestConfiguration
class EmbeddedDatabaseTestConfig {
    
    @Bean
    @Primary
    public DataSource dataSource() {
        return new EmbeddedDatabaseBuilder()
            .setType(EmbeddedDatabaseType.H2)
            .setName("testdb")
            .addScript("classpath:schema.sql")
            .addScript("classpath:test-data.sql")
            .build();
    }
    
    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new JdbcTemplate(dataSource);
    }
}

@SpringJUnitConfig(EmbeddedDatabaseTestConfig.class)
class EmbeddedDatabaseTest {
    
    @Autowired
    private DataSource dataSource;
    
    @Test
    void shouldUseEmbeddedDatabase() {
        assertThat(dataSource).isInstanceOf(EmbeddedDatabase.class);
        
        JdbcTemplate template = new JdbcTemplate(dataSource);
        int count = JdbcTestUtils.countRowsInTable(template, "users");
        assertThat(count).isGreaterThan(0);
    }
    
    @AfterEach
    void cleanup() {
        if (dataSource instanceof EmbeddedDatabase embeddedDb) {
            embeddedDb.shutdown();
        }
    }
}

/**
 * Strategy interface for populating, initializing, or cleaning up a database.
 */
public interface DatabasePopulator {
    
    /**
     * Populate, initialize, or clean up the database using the provided JDBC connection.
     * @param connection the JDBC connection to use to access the database
     * @throws SQLException if an unrecoverable data access exception occurs during database population
     * @throws ScriptException in all other error cases
     */
    void populate(Connection connection) throws SQLException, ScriptException;
}

/**
 * Populates, initializes, or cleans up a database using SQL scripts.
 */
public class ResourceDatabasePopulator implements DatabasePopulator {
    
    /**
     * Construct a new ResourceDatabasePopulator with default settings.
     */
    public ResourceDatabasePopulator();
    
    /**
     * Construct a new ResourceDatabasePopulator with the supplied scripts.
     * @param scripts the scripts to execute to populate, initialize, or clean up the database
     */
    public ResourceDatabasePopulator(Resource... scripts);
    
    /**
     * Add a script to execute to populate, initialize, or clean up the database.
     * @param script the script to execute
     */
    public void addScript(Resource script);
    
    /**
     * Add multiple scripts to execute to populate, initialize, or clean up the database.
     * @param scripts the scripts to execute
     */
    public void addScripts(Resource... scripts);
    
    /**
     * Specify the character encoding used in the SQL scripts, if different from the platform encoding.
     * @param sqlScriptEncoding the encoding used in the scripts
     */
    public void setSqlScriptEncoding(@Nullable String sqlScriptEncoding);
    
    /**
     * Specify the statement separator in the SQL scripts, typically ";" (semicolon) or "\n" (newline).
     * @param separator the script statement separator
     */
    public void setSeparator(String separator);
    
    /**
     * Set the prefix that identifies single-line comments in the SQL scripts.
     * @param commentPrefix the prefix for single-line comments
     */
    public void setCommentPrefix(String commentPrefix);
    
    /**
     * Set the start delimiter that identifies block comments in the SQL scripts.
     * @param blockCommentStartDelimiter the start delimiter for block comments
     */
    public void setBlockCommentStartDelimiter(String blockCommentStartDelimiter);
    
    /**
     * Set the end delimiter that identifies block comments in the SQL scripts.
     * @param blockCommentEndDelimiter the end delimiter for block comments
     */
    public void setBlockCommentEndDelimiter(String blockCommentEndDelimiter);
    
    /**
     * Flag to indicate that all failures in SQL should be logged but not cause a failure.
     * @param continueOnError true if script execution should continue on error
     */
    public void setContinueOnError(boolean continueOnError);
    
    /**
     * Flag to indicate that failed SQL DROP statements should be ignored.
     * @param ignoreFailedDrops true if failed DROP statements should be ignored
     */
    public void setIgnoreFailedDrops(boolean ignoreFailedDrops);
}

/**
 * Thrown when we cannot determine anything more specific than "something went wrong 
 * while processing an SQL script": for example, an SQLException from JDBC that we cannot pinpoint more precisely.
 */
public class ScriptException extends RuntimeException {
    
    /**
     * Constructor for ScriptException.
     * @param message the detail message
     */
    public ScriptException(String message);
    
    /**
     * Constructor for ScriptException.
     * @param message the detail message
     * @param cause the root cause
     */
    public ScriptException(String message, @Nullable Throwable cause);
}

/**
 * Utility class for common operations on SQL scripts.
 */
public abstract class ScriptUtils {
    
    /** Default statement separator within SQL scripts: ";" */
    public static final String DEFAULT_STATEMENT_SEPARATOR = ";";
    
    /** Default prefix for single-line comments: "--" */
    public static final String DEFAULT_COMMENT_PREFIX = "--";
    
    /** Default start delimiter for block comments: "/*" */
    public static final String DEFAULT_BLOCK_COMMENT_START_DELIMITER = "/*";
    
    /** Default end delimiter for block comments: "*/" */
    public static final String DEFAULT_BLOCK_COMMENT_END_DELIMITER = "*/";
    
    /**
     * Execute the given SQL script using default settings.
     * @param connection the JDBC connection to use to execute the script
     * @param resource the resource (potentially associated with a specific encoding) to load the SQL script from
     * @throws ScriptException if an error occurred while executing the SQL script
     */
    public static void executeSqlScript(Connection connection, Resource resource) throws ScriptException;
    
    /**
     * Execute the given SQL script.
     * @param connection the JDBC connection to use to execute the script
     * @param resource the resource (potentially associated with a specific encoding) to load the SQL script from
     * @param continueOnError whether or not to continue without throwing an exception in the event of an error
     * @param ignoreFailedDrops whether or not to continue in the event of failed DROP statements
     * @param commentPrefix the prefix that identifies comments in the SQL script (typically "--")
     * @param separator the script statement separator; falls back to "\n" if not specified and not found in the script
     * @param blockCommentStartDelimiter the start delimiter for block comments (typically "/*")
     * @param blockCommentEndDelimiter the end delimiter for block comments (typically "*/")
     * @throws ScriptException if an error occurred while executing the SQL script
     */
    public static void executeSqlScript(Connection connection, Resource resource, boolean continueOnError,
            boolean ignoreFailedDrops, String commentPrefix, String separator, String blockCommentStartDelimiter,
            String blockCommentEndDelimiter) throws ScriptException;
}