JDBC

A Java API that manages connecting to a database, issuing queries and commands, and handling result sets. It acts as an abstraction layer, allowing Java applications to interact with any relational database (MySQL, PostgreSQL, Oracle) using a standard set of interfaces.

Core Components of JDBC

To work with JDBC, you primarily interact with these four interfaces from the java.sql package:

1. DriverManager: This class manages the list of database drivers. It matches connection requests from the Java application with the proper database driver using a communication sub-protocol.
2. Connection: This interface represents a physical connection with the database. It is used to manage transactions and generate statements.
3. Statement / PreparedStatement: Used to submit SQL queries to the database. PreparedStatement is preferred for security (preventing SQL injection) and performance.
4. ResultSet: This holds the data retrieved from a database after you execute a SQL SELECT query. It acts as an iterator to let you move through the data rows.

The JDBC Architecture

JDBC follows a two-layer architecture:

• JDBC API: Provides the application-to-JDBC Manager connection.
• JDBC Driver API: Supports the JDBC Manager-to-Driver connection.

Standard Workflow (The 5 Steps)

To execute a database operation, follow this logical sequence:

1. Register the Driver: (Optional in modern JDBC) Historically required Class.forName("com.mysql.cj.jdbc.Driver").
2. Establish Connection: Use the DriverManager.getConnection() method with a Database URL, username, and password.
3. Create a Statement: Create a Statement or PreparedStatement object.
4. Execute the Query: Use executeQuery() for SELECT statements or executeUpdate() for INSERT, UPDATE, or DELETE.
5. Close Connections: Always close the ResultSet, Statement, and Connection to free up resources (ideally using a try-with-resources block).

Common JDBC Classes and Methods

Class/Interface	Sets SQL query parameters safely.	Purpose
DriverManager	getConnection(url, user, pass)	Establishes a connection to the DB.
Connection	prepareStatement(sql)	Creates a pre-compiled SQL statement.
PreparedStatement	setInt(index, value), setString(...)	Sets parameters for the SQL query safely.
PreparedStatement	executeQuery()	Returns a ResultSet object.
ResultSet	next()	Moves the cursor to the next row.
ResultSet	getString(columnName)	Retrieves data from the current row.

Best Practices

• Use PreparedStatements: Never concatenate strings to build SQL queries. Using ? placeholders prevents SQL Injection attacks.
• Try-with-Resources: Use this Java feature to ensure that database connections are closed automatically, even if an exception occurs.
• Connection Pooling: For production applications, use a connection pool (like HikariCP) instead of opening/closing a new connection for every request, which is computationally expensive.
• Handle SQLExceptions: Always wrap JDBC code in a try-catch block to handle database-specific errors gracefully.

Basic Java JDBC Code Example

String url = "jdbc:mysql://localhost:3306/my_database";
String user = "root";
String password = "password";

String sql = "SELECT id, name FROM users WHERE id = ?";

try (Connection conn = DriverManager.getConnection(url, user, password);
     PreparedStatement pstmt = conn.prepareStatement(sql)) {
    
    pstmt.setInt(1, 101); // Setting the first '?' to 101
    ResultSet rs = pstmt.executeQuery();

    while (rs.next()) {
        System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name"));
    }
} catch (SQLException e) {
    e.printStackTrace();
}