? Understanding JDBC: Java Database Connectivity ?

Java Database Connectivity (JDBC) is an API that enables Java applications to interact with relational databases. It provides a standard interface for connecting to a database, executing queries, and retrieving results.

?? Key Components of JDBC

1. ?? JDBC Driver: A software component that establishes a connection between Java applications and databases.
2. ? Connection: Represents a session with the database.
3. ?? Statement: Used to execute SQL queries.
4. ?? ResultSet: Stores the results retrieved from a query execution.
5. ?? PreparedStatement: A precompiled SQL statement that improves performance.
6. ?? CallableStatement: Used for calling stored procedures.

? Steps to Use JDBC

1. ?? Load the JDBC Driver

Class.forName("com.mysql.cj.jdbc.Driver");        

2. ? Establish a Connection

Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "root", "password");        

3. ?? Create and Execute a Statement

Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users");        

4. ?? Process the Results

while (rs.next()) {
    System.out.println(rs.getString("name"));
}        

5. ?? Close the Connection

rs.close();
stmt.close();
con.close();        

?? Types of JDBC Drivers

1. ?? JDBC-ODBC Bridge Driver (Type 1) - Uses ODBC driver; not commonly used now.
2. ?? Native-API Driver (Type 2) - Uses database vendor-specific APIs.
3. ?? Network Protocol Driver (Type 3) - Uses a middleware server for database communication.
4. ?? Thin Driver (Type 4) - Pure Java driver, directly communicates with the database.

? When to Use JDBC in Spring Boot

? High Performance Requirements

JDBC allows direct interaction with the database without the overhead of an ORM like Hibernate.

?? Complex Queries and Stored Procedures

JDBC provides fine-grained control over query execution and result handling.

?? Lightweight Applications

Using JDBC can reduce dependency on heavier frameworks when basic CRUD operations are needed.

?? Legacy Systems

JDBC offers better compatibility when integrating with legacy databases.

?? Batch Processing

JDBC allows batch updates, improving performance for multiple queries.

?? Using JDBC in a Spring Boot Application

?? Step 1: Add Dependencies

Add these dependencies in pom.xml:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>        

?? Step 2: Configure DataSource

Configure database properties in application.properties:

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

?? Step 3: Use JdbcTemplate

Spring Boot provides JdbcTemplate, simplifying JDBC operations:

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

@Repository
public class UserRepository {
    private final JdbcTemplate jdbcTemplate;

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

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

class UserRowMapper implements RowMapper<User> {
    @Override
    public User mapRow(ResultSet rs, int rowNum) throws SQLException {
        return new User(rs.getInt("id"), rs.getString("name"), rs.getString("email"));
    }
}        

?? Step 4: Perform Database Operations

You can now call UserRepository.findAll() to retrieve data.

? Advantages of JDBC

• ?? Platform-independent database connectivity.
• ?? Supports various relational databases like MySQL, PostgreSQL, and Oracle.
• ?? Efficient data retrieval and manipulation.
• ? Works with Java EE frameworks like Hibernate and Spring Data.
• ?? Spring Boot simplifies JDBC with built-in configurations.

?? Best Practices

• ?? Use connection pooling for better performance.
• ?? Always close database resources to prevent memory leaks.
• ?? Prefer PreparedStatements to prevent SQL injection.
• ?? Handle exceptions properly using try-with-resources.

JDBC remains an essential part of Java-based database applications, providing a flexible and reliable way to interact with relational databases. Spring Boot enhances JDBC usage by reducing configuration overhead and simplifying database interactions. ?