andersswanson.dev

package com.example;

import java.time.Duration;
import java.util.List;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

@ConfigurationProperties(prefix = DatabaseProperties.PREFIX)
@Component
public class DatabaseProperties {
    public static final String PREFIX = "database";

    // Used to refresh properties on an interval
    private Duration propertyRefreshInterval;

    // We may have 0 or more property sources
    private List<PropertySource> propertySources;

    // A property source is a database table
    public static class PropertySource {
        private String table;

        public String getTable() {
            return table;
        }

        public void setTable(String table) {
            this.table = table;
        }
    }

    public Duration getPropertyRefreshInterval() {
        return propertyRefreshInterval;
    }

    public void setPropertyRefreshInterval(Duration propertyRefreshInterval) {
        this.propertyRefreshInterval = propertyRefreshInterval;
    }

    public List<PropertySource> getPropertySources() {
        return propertySources;
    }

    public void setPropertySources(List<PropertySource> propertySources) {
        this.propertySources = propertySources;
    }
}

spring:
  datasource:
    username: ${USERNAME}
    password: ${PASSWORD}
    url: ${JDBC_URL}

    # Set these to use UCP over Hikari.
    driver-class-name: oracle.jdbc.OracleDriver
    type: oracle.ucp.jdbc.PoolDataSourceImpl
    oracleucp:
      initial-pool-size: 1
      min-pool-size: 1
      max-pool-size: 30
      connection-pool-name: UCPSampleApplication
      connection-factory-class-name: oracle.jdbc.pool.OracleDataSource


# Load properties from the spring_property database table every 1000ms
database:
  property-sources:
    - table: spring_property
  property-refresh-interval: 1000ms

package com.example;

import java.time.Duration;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Timer;
import java.util.TimerTask;

import org.springframework.jdbc.core.JdbcTemplate;

/**
 * A utility class responsible for loading and periodically refreshing database properties from a specified table.
 * It uses the Spring JDBC Template to execute queries against the database and stores the loaded properties in an internal map.
 *
 * @author Anders Swanson
 */
public class DatabasePropertyLoader implements AutoCloseable {
    private static Timer timer;

    private final String table;
    private final JdbcTemplate jdbcTemplate;
    private Map<String, String> properties = new LinkedHashMap<>();

    /**
     * Constructs a new instance of DatabasePropertyLoader that loads and periodically refreshes
     * database properties from the specified table using the provided JdbcTemplate.
     *
     * @param table   the name of the database table containing the properties
     * @param jdbcTemplate the Spring JDBC Template used to execute queries against the database
     * @param refresh the duration between automatic refreshes of the properties, or 0 ms to disable property refresh.
     *                The default refresh rate is 10 minutes if not specified.
     */
    public DatabasePropertyLoader(String table, JdbcTemplate jdbcTemplate, Duration refresh) {
        this.table = table;
        this.jdbcTemplate = jdbcTemplate;
        reload();
        long refreshMillis = Optional.ofNullable(refresh)
                .orElse(Duration.ofMinutes(10))
                .toMillis();
        if (refreshMillis > 0) {
            synchronized (DatabasePropertyLoader.class) {
                if (timer == null) {
                    timer = new Timer(true);
                    timer.scheduleAtFixedRate(new TimerTask() {
                        @Override
                        public void run() {
                            reload();
                        }
                    }, refreshMillis, refreshMillis);
                }
            }
        }
    }

    boolean containsProperty(String key) {
        return properties.containsKey(key);
    }

    Object getProperty(String key) {
        return properties.get(key);
    }

    String[] getPropertyNames() {
        return properties.keySet().toArray(String[]::new);
    }

    /**
     * Reloads the database properties from the specified table into memory.
     * This method executes a SQL query to retrieve all key-value pairs from the table,
     * then updates the internal map of properties with the retrieved values.
     */
    private void reload() {
        String query = "select * from %s".formatted(table);
        List<Property> result = jdbcTemplate.query(query, (rs, rowNum)
                -> new Property(rs.getString("key"), rs.getString("value")));
        properties = new HashMap<>(properties.size());
        for (Property property : result) {
            properties.put(property.key(), property.value());
        }
    }

    @Override
    public void close() throws Exception {
        synchronized (DatabasePropertyLoader.class) {
            if (timer != null) {
                timer.cancel();
                timer = null;
            }
        }
    }
}

package com.example;

import org.springframework.core.env.EnumerablePropertySource;

/**
 * A custom {@link org.springframework.core.env.PropertySource} implementation that retrieves properties from a database.
 * This class extends {@link EnumerablePropertySource} and delegates property access to an underlying
 * {@link DatabasePropertyLoader}.
 *
 * @see DatabasePropertyLoader
 */
public class DatabasePropertySource extends EnumerablePropertySource<DatabasePropertyLoader> {
    public DatabasePropertySource(String name, DatabasePropertyLoader source) {
        super(name, source);
    }

    @Override
    public String[] getPropertyNames() {
        return source.getPropertyNames();
    }

    @Override
    public Object getProperty(String name) {
        return source.getProperty(name);
    }

    @Override
    public boolean containsProperty(String name) {
        return source.containsProperty(name);
    }
}

package com.example;

import javax.sql.DataSource;
import java.time.Duration;
import java.util.List;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.config.ConfigDataEnvironmentPostProcessor;
import org.springframework.boot.context.properties.bind.Bindable;
import org.springframework.boot.context.properties.bind.Binder;
import org.springframework.boot.env.EnvironmentPostProcessor;
import org.springframework.core.Ordered;
import org.springframework.core.env.ConfigurableEnvironment;
import org.springframework.core.env.MutablePropertySources;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.util.ClassUtils;

import static org.springframework.core.env.StandardEnvironment.SYSTEM_ENVIRONMENT_PROPERTY_SOURCE_NAME;

/**
 * A Spring Boot {@link EnvironmentPostProcessor} implementation that loads database properties
 * after the application has been initialized but before it starts up.
 */
public class DatabaseEnvironmentPostProcessor implements EnvironmentPostProcessor, Ordered {
    /**
     * Post-processes the Spring environment by loading database properties and adding them as property sources.
     * This method is called after the application has been initialized but before it starts up.
     * It creates a JdbcTemplate from the Spring environment, loads the property source properties,
     * and adds the database property sources to the collection of Spring property sources.
     *
     * @param environment the Spring environment to be processed
     * @param application the Spring application instance
     */
    @Override
    public void postProcessEnvironment(ConfigurableEnvironment environment, SpringApplication application) {
        // Create a JdbcTemplate from the Spring environment
        Binder binder = Binder.get(environment);
        DataSourceProperties dataSourceProperties = binder.bind("spring.datasource", Bindable.of(DataSourceProperties.class))
                .orElse(new DataSourceProperties());
        DataSource dataSource = dataSourceProperties.initializeDataSourceBuilder()
                .build();
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        // Load the property source properties from the Spring environment
        DatabaseProperties databaseProperties = binder.bind(
                DatabaseProperties.PREFIX,
                Bindable.of(DatabaseProperties.class)
        ).orElse(new DatabaseProperties());
        List<DatabaseProperties.PropertySource> databasePropertySources = databaseProperties.getPropertySources();
        Duration refreshInterval = databaseProperties.getPropertyRefreshInterval();

        MutablePropertySources propertySources = environment.getPropertySources();
        for (DatabaseProperties.PropertySource source : databasePropertySources) {
            DatabasePropertyLoader propertyLoader = new DatabasePropertyLoader(source.getTable(), jdbcTemplate, refreshInterval);
            DatabasePropertySource propertySource = new DatabasePropertySource(source.getTable(), propertyLoader);

            // Add the database property source to the collection of Spring property sources
            if (propertySources.contains(SYSTEM_ENVIRONMENT_PROPERTY_SOURCE_NAME)) {
                propertySources.addAfter(SYSTEM_ENVIRONMENT_PROPERTY_SOURCE_NAME, propertySource);
            } else {
                propertySources.addFirst(propertySource);
            }
        }
    }

    @Override
    public int getOrder() {
        return ConfigDataEnvironmentPostProcessor.ORDER + 1;
    }
}

package com.example;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class PropertyService {
    private static final String PROPERTY_TABLE = "spring_property";
    private static final String UPDATE_PROPERTY = """
            update %s set value = ? where key = ?
            """.formatted(PROPERTY_TABLE);
    private final JdbcTemplate jdbcTemplate;

    /**
     * The value of 'property1' is dynamically loaded during
     * startup, using our database property source implementation.
     */
    @Value("${property1}")
    private String property1;

    public PropertyService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * Used to demonstrate dynamic property loading, using the value of
     * 'property1' loaded from the database property source.
     * @return the current value of property1.
     */
    public String getProperty1() {
        return property1;
    }

    /**
     * Updates an existing property in the database.
     *
     * @param property the updated property object containing the new value and key
     */
    public void updateProperty(Property property) {
        jdbcTemplate.update(UPDATE_PROPERTY, property.value(), property.key());
    }
}

package com.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SampleApp {
    public static void main(String[] args) {
        SpringApplication.run(SampleApp.class, args);
    }
}

package com.example;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Duration;

import oracle.jdbc.pool.OracleDataSource;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.support.DefaultSingletonBeanRegistry;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ConfigurableApplicationContext;
import org.testcontainers.junit.jupiter.Testcontainers;
import org.testcontainers.oracle.OracleContainer;

import static org.assertj.core.api.Assertions.assertThat;

@Testcontainers
@SpringBootTest
public class DatabasePropertySourceTest {
    /**
     * Use a containerized Oracle Database instance to test the Database property source.
     */
    static OracleContainer oracleContainer = new OracleContainer("gvenzl/oracle-free:23.26.0-slim-faststart")
            .withStartupTimeout(Duration.ofMinutes(2))
            .withUsername("testuser")
            .withPassword(("testpwd"));

    /**
     * Dynamically configure Spring Boot properties to use the Testcontainers database.
     */
    @BeforeAll
    static void setUp() throws SQLException {
        oracleContainer.start();
        System.setProperty("JDBC_URL", oracleContainer.getJdbcUrl());
        System.setProperty("USERNAME", oracleContainer.getUsername());
        System.setProperty("PASSWORD", oracleContainer.getPassword());

        // Configure a datasource for the Oracle Database container.
        OracleDataSource dataSource = new OracleDataSource();
        dataSource.setUser(oracleContainer.getUsername());
        dataSource.setPassword(oracleContainer.getPassword());
        dataSource.setURL(oracleContainer.getJdbcUrl());
        // Create the property source table, and populate it with some
        // initial data
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate("""
                create table spring_property (
                key varchar2(255) primary key not null ,
                value varchar2(255) not null
            )""");
            stmt.executeUpdate(" insert into spring_property (key, value) values ('property1', 'initial value')");
        }
    }

    @Autowired
    PropertyService propertyService;

    @Autowired
    DatabaseProperties databaseProperties;

    @Autowired
    ApplicationContext applicationContext;

    @Test
    void propertySourceTest() throws InterruptedException {
        System.out.println("Starting Property Source Test");

        String property1 = propertyService.getProperty1();
        assertThat(property1).isNotNull();
        assertThat(property1).isEqualTo("initial value");
        System.out.println("Value of 'property1': " + property1);


        System.out.println("Updating Property 'property1'");
        propertyService.updateProperty(new Property("property1", "updated"));

        // Wait for property to be refreshed
        Thread.sleep(databaseProperties.getPropertyRefreshInterval().plusMillis(200));

        System.out.println("Reloading PropertyService Bean");
        ConfigurableApplicationContext configContext = (ConfigurableApplicationContext) applicationContext;
        DefaultSingletonBeanRegistry registry = (DefaultSingletonBeanRegistry) configContext.getBeanFactory();

        // Destroy the existing bean instance
        registry.destroySingleton("propertyService");

        // Re-create the bean
        propertyService = (PropertyService) applicationContext.getBean("propertyService");

        // Verify bean has reloaded the new property value
        property1 = propertyService.getProperty1();
        assertThat(property1).isNotNull();
        assertThat(property1).isEqualTo("updated");
        System.out.println("New value of 'property1': " + property1);
    }
}

Starting Property Source Test
Value of 'property1': initial value
Updating Property 'property1'
Reloading PropertyService Bean
New value of 'property1': updated