gaurbprajapati

Posted on Oct 22

Spring Boot Database Connection — From JDBC to Production Best Practices

#springboot #tutorial #java #database

🧩 1. Understanding What a “Database Connection” Is

A Database Connection is a communication channel between your Java application and a database (e.g., MySQL, PostgreSQL, Oracle, etc.).

It uses JDBC (Java Database Connectivity) under the hood.
A connection is required to:
- Execute queries
- Fetch / insert / update data
- Commit or rollback transactions

🪜 2. Level 1 — Raw JDBC Connection (Manual Way)

This is the most basic way:
👉 You manually open and close a connection using DriverManager.

🧠 Concept

Each time a request comes, app opens a new connection.
No pooling or optimization.
Good for learning, bad for production.

🧑‍💻 Example Code

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class RawJDBCExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        String username = "root";
        String password = "admin";

        try {
            // 1. Establish connection
            Connection conn = DriverManager.getConnection(url, username, password);
            System.out.println("✅ Connected to DB");

            // 2. Execute a query
            String sql = "SELECT * FROM users WHERE id = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, 1);
            ResultSet rs = stmt.executeQuery();

            // 3. Process result
            while (rs.next()) {
                System.out.println("User: " + rs.getString("name"));
            }

            // 4. Close connection
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

⚠️ Problems

❌ Creates new connection for each call (slow)
❌ No retry or timeout control
❌ Hard to manage in multi-threading
❌ Code is tightly coupled to DB logic

✅ Good for: Testing locally or tiny apps
🚫 Bad for: Real APIs, load testing, production apps

🪜 3. Level 2 — Spring Boot + DataSource (DriverManagerDataSource)

Spring Boot allows us to define a DataSource Bean, so we don’t manually open/close connections every time.

🧠 Concept

DataSource gives you a ready-made connection.
It can still use DriverManager underneath (no pool yet).
Spring injects the DataSource wherever you need it.

🧑‍💻 Example

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

import javax.sql.DataSource;

@Configuration
public class DbConfig {

    @Bean
    public DataSource devDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/mydb");
        dataSource.setUsername("root");
        dataSource.setPassword("admin");
        return dataSource;
    }
}

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class UserRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public String getUserNameById(int id) {
        return jdbcTemplate.queryForObject(
                "SELECT name FROM users WHERE id = ?",
                new Object[]{id},
                String.class
        );
    }
}

⚡ Advantages

✅ Spring manages connection lifecycle
✅ Cleaner code
⚠️ Still no connection pool

✅ Good for: Local apps, internal tools
🚫 Bad for: High-load systems

🪜 4. Level 3 — Connection Pooling (HikariCP) ✅ (Best Practice)

Spring Boot by default uses HikariCP — a fast, lightweight connection pool.

👉 Connection Pooling means:

A fixed number of connections are created (e.g., 10)
Requests reuse existing connections
No time wasted opening/closing every time

🧠 Concept Flow

App starts → Hikari creates a pool (e.g., 10 connections)
Request comes → Borrows connection from pool
Query runs → Returns connection to pool
Idle connections auto-managed (can shrink if needed)
Multiple threads reuse pool efficiently

🧑‍💻 Configuration in `application.properties`

spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=root
spring.datasource.password=admin
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

# HikariCP specific
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.pool-name=MyHikariPool

🧑‍💻 Repository Example

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class UserRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public int getUserCount() {
        return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM users", Integer.class);
    }
}

✅ Best practice for production

⚡ Reuses connections
🧠 Handles idle connections smartly
🧰 Avoids DB overload
🧪 Ideal for high concurrency

🪜 5. Level 4 — JPA / Hibernate (ORM Layer)

When your project grows, you may not want to write SQL queries manually.
👉 That’s where Spring Data JPA / Hibernate comes in.

🧠 Concept

ORM (Object Relational Mapping): maps your Java classes to DB tables
No need to manually manage connections or queries
Uses the same HikariCP pool underneath

🧑‍💻 Example Entity

import jakarta.persistence.Entity;
import jakarta.persistence.Id;

@Entity
public class User {

    @Id
    private int id;
    private String name;

    // getters and setters
}

🧑‍💻 Repository

import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Integer> {
    // No SQL needed, Spring generates queries
}

🧑‍💻 Service Layer

import org.springframework.stereotype.Service;

@Service
public class UserService {
    private final UserRepository userRepo;

    public UserService(UserRepository userRepo) {
        this.userRepo = userRepo;
    }

    public String getUserName(int id) {
        return userRepo.findById(id).map(User::getName).orElse("Not Found");
    }
}

✅ Cleaner code
✅ No manual connection handling
✅ Connection pool under the hood (HikariCP)

🧠 6. Important Advanced Concepts

🧭 a. Connection vs DataSource vs JdbcTemplate vs EntityManager

Layer	Responsibility	Example
`Connection`	Low-level JDBC	`DriverManager.getConnection()`
`DataSource`	Connection provider	`DriverManagerDataSource` or `HikariDataSource`
`JdbcTemplate`	Simplifies JDBC calls	`jdbcTemplate.query(...)`
`EntityManager / JPA`	ORM mapping	`userRepository.findById(1)`

🧭 b. Connection Pool Tuning

maximumPoolSize → How many connections at max
minimumIdle → How many idle connections to keep ready
idleTimeout → How long idle connections can live
connectionTimeout → Max time to wait for a connection

👉 Example: If your app gets 100 concurrent requests but pool size is 10, then 90 have to wait.

🧭 c. Idle Connection Management

Pool may shrink during low traffic (minIdle)
Pool grows back when traffic increases
This avoids keeping unused connections open

E.g.:

Pool size = 10
API idle = no traffic
-> Shrinks to 2 (minimumIdle)
New request comes
-> Expands to 10 again

🧭 d. Transactions

Connections are tightly linked to transactions.

@Transactional ensures:
- One connection per transaction
- Auto commit or rollback
- Returned to pool after transaction ends

@Transactional
public void updateBalance(int userId, double amount) {
    // single connection used here
    userRepo.updateAmount(userId, amount);
}

🧭 e. Common Mistakes to Avoid

❌ Closing DataSource manually
❌ Holding connections too long
❌ Not releasing connections in custom JDBC code
❌ Using too small or too big pool size
❌ Mixing DriverManager and HikariCP

🏁 7. Best Practice Summary

Approach	Level	Suitable For	Pros	Cons
Raw JDBC	1	Learning	Simple, transparent	No pooling, hard to scale
DataSource (no pool)	2	Small apps	Cleaner code	Still no pooling
HikariCP (with JdbcTemplate)	3	Production APIs, Services	Fast, efficient, reliable	Slight config needed
JPA / Hibernate + HikariCP	4	Large apps, microservices	Clean, high-level abstraction	Learning curve, ORM overhead

✅ Recommendation:
For 95% of real Spring Boot projects:
👉 Use HikariCP + Spring Data (JPA or JdbcTemplate).

🧪 8. Real-World Example: REST API with DB

User hits API  ➝ Spring Controller
               ➝ Service Layer
               ➝ Repository Layer (JPA or JdbcTemplate)
               ➝ Borrow Connection from Hikari Pool
               ➝ Execute Query
               ➝ Return Connection to Pool
               ➝ Return Response

✅ Fast, efficient, production ready.

📝 9. Sample Project Structure

src/
 ├─ config/
 │   └─ DbConfig.java
 ├─ entity/
 │   └─ User.java
 ├─ repository/
 │   └─ UserRepository.java
 ├─ service/
 │   └─ UserService.java
 ├─ controller/
 │   └─ UserController.java
 └─ application.properties

🧭 10. Monitoring & Troubleshooting

You can monitor the pool by:

HikariCP metrics (via actuator)
Prometheus / Grafana dashboards
Logs (spring.datasource.hikari.pool-name)

Example Log:

HikariPool-1 - Pool stats (total=10, active=2, idle=8, waiting=0)

🏆 Conclusion

Start with JDBC to understand fundamentals 🧠
Use DataSource to simplify connection creation 🧩
Use HikariCP to handle real-world traffic efficiently ⚡
Use JPA or JdbcTemplate for cleaner code ✨
Monitor and tune pool to match your app’s needs 📊

DEV Community

Spring Boot Database Connection — From JDBC to Production Best Practices

🧩 1. Understanding What a “Database Connection” Is

🪜 2. Level 1 — Raw JDBC Connection (Manual Way)

🧠 Concept

🧑‍💻 Example Code

⚠️ Problems

🪜 3. Level 2 — Spring Boot + DataSource (DriverManagerDataSource)

🧠 Concept

🧑‍💻 Example

⚡ Advantages

🪜 4. Level 3 — Connection Pooling (HikariCP) ✅ (Best Practice)

🧠 Concept Flow

🧑‍💻 Configuration in `application.properties`

🧑‍💻 Repository Example

🪜 5. Level 4 — JPA / Hibernate (ORM Layer)

🧠 Concept

🧑‍💻 Example Entity

🧑‍💻 Repository

🧑‍💻 Service Layer

🧠 6. Important Advanced Concepts

🧭 a. Connection vs DataSource vs JdbcTemplate vs EntityManager

🧭 b. Connection Pool Tuning

🧭 c. Idle Connection Management

🧭 d. Transactions

🧭 e. Common Mistakes to Avoid

🏁 7. Best Practice Summary

🧪 8. Real-World Example: REST API with DB

📝 9. Sample Project Structure

🧭 10. Monitoring & Troubleshooting

🏆 Conclusion

Top comments (0)

🧩 1. Understanding What a “Database Connection” Is

🪜 2. Level 1 — Raw JDBC Connection (Manual Way)

🧠 Concept

🧑‍💻 Example Code

⚠️ Problems

🪜 3. Level 2 — Spring Boot + DataSource (DriverManagerDataSource)

🧠 Concept

🧑‍💻 Example

⚡ Advantages

🪜 4. Level 3 — Connection Pooling (HikariCP) ✅ (Best Practice)

🧠 Concept Flow

🧑‍💻 Configuration in application.properties

🧑‍💻 Repository Example

🪜 5. Level 4 — JPA / Hibernate (ORM Layer)

🧠 Concept

🧑‍💻 Example Entity

🧑‍💻 Repository

🧑‍💻 Service Layer

🧠 6. Important Advanced Concepts

🧭 a. Connection vs DataSource vs JdbcTemplate vs EntityManager

🧭 b. Connection Pool Tuning

🧭 c. Idle Connection Management

🧭 d. Transactions

🧭 e. Common Mistakes to Avoid

🏁 7. Best Practice Summary

🧪 8. Real-World Example: REST API with DB

📝 9. Sample Project Structure

🧭 10. Monitoring & Troubleshooting

🏆 Conclusion

🧑‍💻 Configuration in `application.properties`