Java Database Connectivity (JDBC)

Java Database Connectivity (JDBC) is a standard Java API that enables Java applications to interact with databases. JDBC provides a set of interfaces and classes that allow developers to write database-independent code to perform CRUD operations (Create, Read, Update, Delete) and execute SQL queries. It is part of the Java SE (Standard Edition) platform and is commonly used to connect Java applications to relational databases like MySQL, Oracle, PostgreSQL, and others.

Here’s an overview of JDBC and its key components:

1. What is JDBC?

JDBC stands for Java Database Connectivity. It is a Java-based API that allows Java applications to connect to and interact with databases using SQL (Structured Query Language). JDBC provides a set of methods to:

• Establish a connection to a database.
• Send SQL queries to the database.
• Process the results of SQL queries.
• Handle exceptions and errors that may occur during database interactions.

JDBC abstracts away the underlying details of different database management systems (DBMS), making it easier for developers to write database-independent code.

2. Key Components of JDBC

JDBC consists of several core components and classes, which work together to facilitate database connectivity and interaction:

a) JDBC Drivers

JDBC drivers are specific implementations for different databases that enable Java applications to connect to those databases. There are four types of JDBC drivers:

• Type 1 (JDBC-ODBC Bridge Driver): Uses ODBC to connect to a database. This type is deprecated.
• Type 2 (Native-API Driver): Uses database-specific native libraries to interact with the DBMS.
• Type 3 (Network Protocol Driver): Uses a middleware server to communicate with the database.
• Type 4 (Thin Driver): A pure Java driver that directly communicates with the database using the database’s native protocol (most commonly used in modern Java applications).

b) Connection Interface

The Connection interface represents a connection to a specific database. It allows you to:

• Establish a connection to the database.
• Create Statement, PreparedStatement, and CallableStatement objects for executing SQL queries.
• Manage transactions (commit, rollback, set auto-commit).
• Close the connection once the database operations are completed.

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "username", "password");

c) Statement Interface

The Statement interface is used to execute SQL queries against the database. It is responsible for sending SQL commands to the DBMS and receiving the results. There are three main types of statements:

• Statement: Used for executing simple SQL queries without parameters.
• PreparedStatement: Used for executing precompiled SQL queries with parameters, improving performance and preventing SQL injection attacks.
• CallableStatement: Used to execute stored procedures in the database.

Example of using Statement:

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

d) ResultSet Interface

The ResultSet interface represents the result of a query. It provides methods to retrieve data from the result set, move through the rows, and access individual columns.

Example:

while (rs.next()) {
    String name = rs.getString("name");
    int age = rs.getInt("age");
    System.out.println(name + ": " + age);
}

e) Exception Handling

JDBC provides several exceptions, the most common being SQLException, which is thrown when there is an issue with database connectivity or an SQL operation.

Example:

try {
    // Code to connect and query the database
} catch (SQLException e) {
    e.printStackTrace();
}

3. Basic Steps for Using JDBC

Here is a simple example demonstrating the basic steps involved in using JDBC to interact with a database:

a) Load JDBC Driver

You need to load the JDBC driver class specific to your database. This is typically done using Class.forName() (though modern versions of JDBC do this automatically).

Example:

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

b) Establish a Database Connection

Use DriverManager.getConnection() to establish a connection to the database.

Example:

Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "username", "password");

c) Create a Statement and Execute Queries

Once the connection is established, create a Statement object to execute SQL queries.

Example:

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM users");

d) Process the ResultSet

You can loop through the ResultSet to extract the data.

Example:

while (resultSet.next()) {
    String name = resultSet.getString("name");
    int age = resultSet.getInt("age");
    System.out.println(name + " - " + age);
}

e) Close the Connection

Finally, close the ResultSet, Statement, and Connection objects to release resources.

Example:

resultSet.close();
statement.close();
connection.close();

4. Example of Using PreparedStatement

A PreparedStatement is preferred when executing queries that require parameters, as it helps prevent SQL injection and can improve performance by reusing the precompiled query.

Example:

String query = "SELECT * FROM users WHERE age > ?";
PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setInt(1, 30);  // Set the parameter value
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
    String name = resultSet.getString("name");
    System.out.println(name);
}

5. Managing Transactions

JDBC allows you to manage transactions explicitly. By default, JDBC operates in auto-commit mode, meaning each statement is committed immediately after it’s executed. To manage transactions manually:

• Disable auto-commit with connection.setAutoCommit(false).
• Commit the transaction using connection.commit().
• Rollback if there’s an error using connection.rollback().

Example:

connection.setAutoCommit(false); // Disable auto-commit
try {
    Statement stmt = connection.createStatement();
    stmt.executeUpdate("INSERT INTO users (name, age) VALUES ('John', 25)");
    connection.commit();  // Commit the transaction
} catch (SQLException e) {
    connection.rollback();  // Rollback if there's an error
} finally {
    connection.setAutoCommit(true); // Enable auto-commit again
}