JdbcTemplate (Spring JDBC)

JdbcTemplate is a core Spring JDBC utility that makes working with relational databases simple and clean by handling low-level JDBC operations automatically. It lets developers focus on writing SQL and business logic instead of boilerplate code.

---

What is JdbcTemplate (Spring JDBC)?

JdbcTemplate is a Spring-provided helper class that:

• Executes SQL queries and updates
• Manages database connections, statements, and result sets
• Converts SQLException into Spring’s unchecked DataAccessException
• Integrates smoothly with Spring transactions (@Transactional)

In simple words:

JdbcTemplate = Write SQL directly in Spring without worrying about JDBC complexity

It is lightweight, fast, and SQL-centric, making it ideal when you need full control over queries without using an ORM like JPA or Hibernate.

---

Why Use JdbcTemplate?

Without JdbcTemplate (Plain JDBC):

• Too much boilerplate code
• Manual connection and resource handling
• Error-prone exception management

With JdbcTemplate:

• Automatic connection & resource management
• Clean and readable code
• Consistent exception handling
• Full control over SQL queries

---

How JdbcTemplate Works

Application Code
      ↓
JdbcTemplate
      ↓
DataSource (Connection Pool)
      ↓
Database (MySQL / PostgreSQL icon)

---

Key Features of JdbcTemplate

Feature	Description
SQL-based	Uses raw SQL queries
Lightweight	No ORM, no entity management
Fast	Minimal abstraction
Exception Translation	Converts SQLException → DataAccessException
Thread-safe	Can be reused across beans

---

Basic Setup

1. Dependency (Spring Boot)

Adds Spring JDBC support to the project and enables auto-configuration for DataSource and JdbcTemplate.

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

---

2. Database Configuration (application.properties)

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

---

Auto-Configuration

With the dependency and properties in place, Spring Boot automatically creates DataSource and JdbcTemplate beans—no manual setup required.

Once auto-configuration is done, you can directly inject and use JdbcTemplate:

@Service
publicclassUserService {

@Autowired
private JdbcTemplate jdbcTemplate;

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

---

Common Operations

1. Insert / Update / Delete

update() executes INSERT, UPDATE, or DELETE queries and returns the number of affected rows.

@Autowired
private JdbcTemplate jdbcTemplate;

public int saveUser(String name, String email) {
    String sql = "INSERT INTO users(name, email) VALUES (?, ?)";
    return jdbcTemplate.update(sql, name, email);
}

---

2. Query for Single Object

Fetches exactly one record from the database and maps it to a Java object or simple value.

public User getUserById(Long id) {
    String sql = "SELECT * FROM users WHERE id = ?";
    return jdbcTemplate.queryForObject(
        sql,
        new BeanPropertyRowMapper<>(User.class),
        id
    );
}

---

3. Query for Multiple Rows

Executes a SELECT query and returns a list of mapped objects, one for each row in the result set.

public List<User> getAllUsers() {
    String sql = "SELECT * FROM users";
    return jdbcTemplate.query(
        sql,
        new BeanPropertyRowMapper<>(User.class)
    );
}

---

4. Custom RowMapper (Optional)

Gives full control over how each database row is converted into a Java object, useful for complex mappings.

public List<User> getUsers() {
    return jdbcTemplate.query(
        "SELECT * FROM users",
        (rs, rowNum) -> new User(
            rs.getLong("id"),
            rs.getString("name"),
            rs.getString("email")
        )
    );
}

---

JdbcTemplate vs Spring Data JDBC vs JPA

Feature	JdbcTemplate	Spring Data JDBC	JPA
SQL Control	Full control – you write every SQL query yourself	Good control – SQL is visible but CRUD is auto-generated	Limited control – SQL is mostly hidden behind ORM
ORM Support	Not an ORM	Not an ORM	Full ORM support
Lazy Loading	Not supported	Not supported	Fully supported
Boilerplate Code	Moderate – manual queries and mapping required	Low – repositories reduce code	Very low – ORM handles most tasks
Performance	High and predictable	High and predictable	Moderate, may vary due to ORM behavior
Learning Curve	Easy – straightforward JDBC concepts	Easy – simple and clean abstraction	Steep – requires understanding ORM concepts

---

When to Use JdbcTemplate?

Use JdbcTemplate when:

• You want full control over SQL
• Performance is critical
• Queries are complex
• No need for ORM features

Avoid when:

• You need lazy loading or entity relationships
• Large domain models with many entities
• Prefer minimal SQL writing

---

Real-World Use Cases

• Reporting & Analytics Queries: Ideal for complex SQL queries and reports where performance and full SQL control are required.
• Legacy Database Integration: Works well with existing databases that do not fit clean ORM models.
• High-Performance Read/Write Services: Suitable for performance-critical applications where predictable execution is important.
• Microservices with Simple DB Logic: Perfect for small, focused services that need straightforward database operations without ORM overhead.

---

Conclusion

JdbcTemplate is the simplest and most reliable way to work with JDBC in Spring.

It provides full SQL control, reduces boilerplate, and ensures clean, maintainable code without ORM complexity.

Use it when you need explicit SQL management, high performance, and predictable behavior in database operations.