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.
JDBC allows Java applications to establish connections to a wide range of relational databases, including MySQL, PostgreSQL, Oracle, SQL Server, and more.
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.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.
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();
}
}
}
JDBC provides mechanisms for handling database-related errors, such as SQLException
, which may occur during database operations.
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.
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:
queryForObject
, query
, queryForList
, queryForRowSet
, queryForMap
update
, batchUpdate
, execute
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.
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);
}
}
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);
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.