Configuring a DataSource is a fundamental step in developing Spring-based applications that interact with relational databases. Spring offers several mechanisms to manage database connections, ranging from container-managed resources to embedded databases for development and third-party connection pools for production environments.

1. JNDI-Managed Data Sources

In enterprise environments where applications are deployed to servers like Tomcat, WildFly, or WebSphere, database connections are often managed by the container itself. This allows administrators to manage connection settings outside the application code. The application retrieves the connection via JNDI (Java Naming and Directory Interface).

Container Configuration (e.g., Tomcat server.xml):

<Resource name="jdbc/AppDB"
          auth="Container"
          type="javax.sql.DataSource"
          factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
          maxActive="50"
          minIdle="5"
          initialSize="5"
          username="db_user"
          password="db_password"
          driverClassName="com.mysql.cj.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/production_db"/>

Java-Based Configuration:

@Configuration
@Profile("prod")
public class ProductionDataConfig {

    @Bean
    public DataSource dataSource() throws NamingException {
        JndiObjectFactoryBean jndiFactory = new JndiObjectFactoryBean();
        jndiFactory.setJndiName("jdbc/AppDB");
        jndiFactory.setResourceRef(true); // Prepends "java:comp/env/"
        jndiFactory.afterPropertiesSet();
        return (DataSource) jndiFactory.getObject();
    }
}

XML-Based Configuration:

<jee:jndi-lookup id="dataSource" jndi-name="jdbc/AppDB" resource-ref="true" />

2. Embedded Data Sources for Development

During development or testing, using a full-scale database server can be cumbersome. Spring provides support for embedded databases like H2, HSQL, or Derby, which run in-memory and are destroyed when the application stops.

Java-Based Configuration:

@Configuration
@Profile("dev")
public class StandaloneDataConfig {

    @Bean
    public DataSource dataSource() {
        return new EmbeddedDatabaseBuilder()
                .setType(EmbeddedDatabaseType.H2)
                .addScript("classpath:db/init-schema.sql")
                .addScript("classpath:db/test-data.sql")
                .build();
    }
}

XML-Based Configuration:

<jdbc:embedded-database id="dataSource" type="H2">
    <jdbc:script location="classpath:db/init-schema.sql"/>
    <jdbc:script location="classpath:db/test-data.sql"/>
</jdbc:embedded-database>

3. Third-Party Connection Pools (C3P0)

For standalone applications or environments where container-managed JNDI is not used, developers often rely on robust connection pooling libraries like C3P0 or HikariCP to manage connection lifecycles and performance.

Java-Based Configuration:

@Configuration
public class ConnectionPoolConfig {

    @Bean
    public DataSource dataSource() throws PropertyVetoException {
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        cpds.setDriverClass("com.mysql.cj.jdbc.Driver");
        cpds.setJdbcUrl("jdbc:mysql://localhost:3306/app_store?useSSL=false");
        cpds.setUser("app_user");
        cpds.setPassword("app_pass");
        
        // Pool sizing
        cpds.setInitialPoolSize(5);
        cpds.setMinPoolSize(5);
        cpds.setMaxPoolSize(30);
        
        // Connection testing
        cpds.setIdleConnectionTestPeriod(3000);
        cpds.setTestConnectionOnCheckin(true);
        
        return cpds;
    }
}

XML-Based Configuration:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass" value="com.mysql.cj.jdbc.Driver" />
    <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/app_store" />
    <property name="user" value="app_user" />
    <property name="password" value="app_pass" />
    <property name="maxPoolSize" value="30" />
    <property name="minPoolSize" value="5" />
</bean>

4. Using the DataSource in Service Layers

Once the DataSource bean is defined in the Spring context, it can be injected into services. Its recommended to use modern Java try-with-resources to ensure connections are closed properly.

@Service
public class UserManagementService {

    private final DataSource dataSource;

    @Autowired
    public UserManagementService(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public List<Map<String, Object>> fetchAllUsers() {
        List<Map<String, Object>> results = new ArrayList<>();
        String sql = "SELECT user_id, display_name FROM system_users";

        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                row.put("id", rs.getLong("user_id"));
                row.put("name", rs.getString("display_name"));
                results.add(row);
            }
        } catch (SQLException e) {
            // Handle database exceptions
            e.printStackTrace();
        }
        return results;
    }

    public void registerEvent(String eventName) {
        String sql = "INSERT INTO audit_logs (log_time, event_desc) VALUES (?, ?)";
        String timestamp = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            
            ps.setString(1, timestamp);
            ps.setString(2, eventName);
            ps.executeUpdate();
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}