JDBC & JDBC Template

JDBC (Java Database Connectivity)

JDBC (Java Database Connectivity) is a Java API for connecting and interacting with relational databases. It provides a set of classes and interfaces for performing database operations, such as executing SQL queries, processing query results, and managing database connections.

Database Connectivity

JDBC allows Java applications to establish connections to a wide range of relational databases, including MySQL, PostgreSQL, Oracle, SQL Server, and more.

Driver-based Architecture

• Database-specific Drivers

  • JDBC uses a driver-based architecture, where each database vendor provides a JDBC driver that implements the JDBC API to facilitate communication between the Java application and the database.

• JDBC API

  • Each type of database has its own specific protocol and communication mechanism for interacting with it. Thus, there needs to be an interpreter that changes high-level, abstract language that we humans write into a specific language/protocol that the database understands. JDBC drivers act as this interpreter.
  • Since there is a driver for each type of database, JDBC allows users to communicate with different types of databases without changing code for different databases. In other words, JDBC provides a standardized set of interfaces and classes that define how Java applications interact with databases. This API remains consistent across different database vendors, allowing developers to write portable database code regardless of databases' underlying differences.

• Driver Manager

  • The DriverManager class in JDBC serves as a central registry for managing JDBC drivers. It loads and registers drivers dynamically based on the JDBC URL provided by the application.
  • It's basically a helper that helps Java programs find the right "translator" (JDBC driver) to talk to different databases, making it possible for Java applications to connect to and work with various types of databases.

Core Components

Some of the core components of JDBC include DriverManager for managing drivers, Connection for representing a database connection, Statement for executing SQL statements, and ResultSet for processing query results.

Basic Steps

The basic steps involved in using JDBC are
1. loading the appropriate driver class
2. establishing a connection to the database
3. creating and executing SQL statements
4. processing the results and
5. finally closing the resources (connections, statements, result sets) properly.

import java.sql.*;

public class JdbcExample {

    public static void main(String[] args) {
        try {
        	// 1. load the appropriate driver class
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 2. establish connection to the database
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "username", "password");
            //3. create and execute SQL statements
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM users");
			
            // 4. process results
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
            }
			
            // 5. close the resources
            resultSet.close();
            statement.close();
            connection.close();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

Error Handling

JDBC provides mechanisms for handling database-related errors, such as SQLException, which may occur during database operations.

JdbcTemplate

JdbcTemplate is a part of the Spring JDBC module, which simplifies JDBC usage by providing higher-level abstraction and reducing boilerplate code. It builds on top of the core JDBC API and provides several features to streamline database operations in Spring applications.

Simplified Database Access

JdbcTemplate simplifies database access by encapsulating common JDBC operations, such as executing SQL queries, updating data, and processing query results. Commonly used functions are as follows:

• select: queryForObject, query, queryForList, queryForRowSet, queryForMap
• update: update, batchUpdate, execute

Exception Handling

It handles JDBC exceptions internally and converts them into more user-friendly Spring exceptions, reducing the amount of exception handling code required in application code.

Resource Management

JdbcTemplate automatically manages database resources such as connections, statements, and result sets, ensuring that they are properly opened and closed, even in the presence of exceptions.

• JDBC requires developers to manually manage database resources, like the code example provided above in 'basic steps' of JDBC.

• However, like the following code, just by declaring a JdbcTemplate instance, Spring automatically handles many aspects of resource management for you.

  import org.springframework.jdbc.core.JdbcTemplate;
  
   public class MyDao {
  	
       private final JdbcTemplate jdbcTemplate;
  
       public MyDao(JdbcTemplate jdbcTemplate) {
           this.jdbcTemplate = jdbcTemplate;
       }
  
       public int getUserCount() {
           return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM users", Integer.class);
       }
   }

Parameterized Queries

It supports parameterized queries, allowing you to safely pass parameters to SQL statements, which helps prevent SQL injection attacks and improves performance by reusing query execution plans.

String lastName = jdbcTemplate.queryForObject("select last_name from customers where id = ?", String.class, id);

Result Set Mapping

JdbcTemplate provides support for mapping query results to Java objects using RowMapper and ResultSetExtractor interfaces, simplifying the process of converting database rows into domain objects.

// JDBC
private final RowMapper<Customer> rowMapper = (resultSet, rowNum) -> {
    Customer customer = new Customer(
            resultSet.getLong("id"),
            resultSet.getString("first_name"),
            resultSet.getString("last_name"));
    return customer;
};

// JdbcTempkate
List<Customer> customers = jdbcTemplate.query("select id, first_name, last_name from customers where first_name = ?", rowMapper, firstName);

Overall, JDBC provides the basic functionality for interacting with databases in Java, while JdbcTemplate adds convenience and abstraction on top of JDBC, making database access easier and more efficient, especially in Spring applications.