Spring Boot - JDBC

Spring Boot provides support for JDBC (Java Database Connectivity) to facilitate database interactions in a more imperative style compared to the declarative style of JPA. JDBC is a standard Java API for connecting to relational databases.

Here's a brief guide to using JDBC with Spring Boot:

1. Dependencies:

To get started, you'll need to add the required dependencies to your pom.xml:

<!-- Spring Boot Starter JDBC for JDBC support -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>

<!-- Your Database Driver, e.g., H2 for demonstration purposes -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

2. Configuration:

In the application.properties or application.yml file, you can configure the data source:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled=true

For production databases like MySQL or PostgreSQL, you'd replace the connection string and driver class name accordingly.

3. Using `JdbcTemplate`:

Spring Boot provides JdbcTemplate to simplify database interactions using JDBC.

Here's an example of a service class that uses JdbcTemplate:

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

@Service
public class UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

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

    public List<Map<String, Object>> findAllUsers() {
        String sql = "SELECT * FROM users";
        return jdbcTemplate.queryForList(sql);
    }

    // ... Other CRUD methods
}

4. Database Initialization:

Spring Boot allows you to execute SQL scripts during application startup which can be useful for schema creation or data initialization. By default, it will execute schema.sql and data.sql files found in the classpath.

For example:

schema.sql:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100)
);

data.sql:

INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
INSERT INTO users (name, email) VALUES ('Jane Doe', 'jane@example.com');

5. Handling Transactions:

You can use the @Transactional annotation to handle transactions:

import org.springframework.transaction.annotation.Transactional;

@Service
public class UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Transactional
    public void someTransactionalMethod() {
        // ... Multiple database operations
    }
}

6. Connection Pooling:

Spring Boot configures a connection pool for you. By default, it uses HikariCP. You can configure properties like maximum pool size, connection timeout, etc., in the application.properties file.

Conclusion:

While JPA and other ORM tools abstract a lot of database operations, JDBC with JdbcTemplate provides more control over queries and database interactions. It's especially useful when dealing with complex queries or legacy databases where ORM tools might not be the best fit. Spring Boot makes it easy to integrate and use JDBC, simplifying many of the common tasks associated with database operations.