Complete Guide to Java Database Connectivity

Learn JDBC fundamentals, including database connectivity, statements, CRUD operations, transactions, and batch processing in Java for effective database interaction.

JDBC (Java Database Connectivity) is a Java API that enables Java applications to interact with databases. It provides a standard interface for connecting to various relational databases and executing SQL queries.

1. JDBC Architecture

The JDBC architecture allows Java applications to connect and execute SQL queries with databases. It follows a two-tier architecture:

Key Components:

  1. JDBC API: Provides Java interfaces for database interaction (e.g., Connection, Statement, PreparedStatement, etc.).
  2. JDBC Drivers: Specific implementations that enable communication between Java and the database.
  3. Database: The relational database (e.g., MySQL, PostgreSQL, Oracle) that stores the data.

JDBC Architecture Diagram:


Java Application → JDBC API → JDBC Drivers → Database

2. Database Drivers

JDBC requires specific drivers for connecting to different databases. There are four types of JDBC drivers:

  1. Type 1: JDBC-ODBC Bridge Driver – Uses ODBC (Open Database Connectivity) to connect to the database.
  2. Type 2: Native-API Driver – Uses database-specific libraries to connect.
  3. Type 3: Network Protocol Driver – Uses middleware for database communication.
  4. Type 4: Thin Driver – Database-specific driver that communicates directly with the database via TCP/IP (most common).

Example of Type 4 Driver for MySQL:

  1. MySQL provides a JDBC Type 4 driver called mysql-connector-java.

3. Establishing a Connection

To connect a Java application to a database, you need to use the DriverManager class or DataSource to establish a database connection.

Example – Establishing Connection to MySQL Database


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Main {
public static void main(String[] args) {
// Database URL, username, and password
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "password";
try {
// Establish connection
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println("Connected to the database successfully!");
// Close the connection
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Key Points:

  1. Use DriverManager.getConnection() to establish the connection.
  2. URL format: jdbc:<dbType>://<host>:<port>/<dbName>.

4. Statements and PreparedStatement

  1. Statement: Used for executing simple SQL queries.
  2. PreparedStatement: Used for executing precompiled SQL statements. It is more efficient, as it allows SQL query parameters to be set dynamically.

Example – Using Statement:


import java.sql.*;

public class Main {
public static void main(String[] args) {
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "root", "password");
Statement statement = connection.createStatement()) {
String query = "SELECT * FROM employees";
ResultSet resultSet = statement.executeQuery(query);
while (resultSet.next()) {
System.out.println("Employee ID: " + resultSet.getInt("id"));
System.out.println("Employee Name: " + resultSet.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Example – Using PreparedStatement:


import java.sql.*;

public class Main {
public static void main(String[] args) {
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "root", "password");
PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO employees (name, age) VALUES (?, ?)")) {
preparedStatement.setString(1, "John Doe");
preparedStatement.setInt(2, 30);
int rowsAffected = preparedStatement.executeUpdate();
System.out.println("Rows affected: " + rowsAffected);
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Key Points:

  1. PreparedStatement prevents SQL injection.
  2. PreparedStatement is more efficient for repeated queries.

5. CRUD Operations

JDBC enables you to perform the basic CRUD operations: Create, Read, Update, and Delete data in a database.

Create – Insert Data


String query = "INSERT INTO employees (name, age) VALUES (?, ?)";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, "Alice");
pstmt.setInt(2, 25);
pstmt.executeUpdate();

Read – Retrieve Data


String query = "SELECT * FROM employees";
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
System.out.println(rs.getInt("id") + ": " + rs.getString("name"));
}

Update – Modify Data


String query = "UPDATE employees SET age = ? WHERE name = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setInt(1, 28);
pstmt.setString(2, "Alice");
pstmt.executeUpdate();

Delete – Remove Data


String query = "DELETE FROM employees WHERE name = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, "Alice");
pstmt.executeUpdate();

6. Transactions

Transactions in JDBC allow you to group multiple SQL statements together as a single unit of work. If one statement fails, all changes made by the transaction are rolled back.

Transaction Example:


try {
connection.setAutoCommit(false); // Disable auto-commit
String query1 = "INSERT INTO employees (name, age) VALUES (?, ?)";
PreparedStatement pstmt1 = connection.prepareStatement(query1);
pstmt1.setString(1, "Bob");
pstmt1.setInt(2, 35);
pstmt1.executeUpdate();
String query2 = "UPDATE employees SET age = ? WHERE name = ?";
PreparedStatement pstmt2 = connection.prepareStatement(query2);
pstmt2.setInt(1, 36);
pstmt2.setString(2, "Alice");
pstmt2.executeUpdate();
connection.commit(); // Commit transaction
} catch (SQLException e) {
connection.rollback(); // Rollback if error occurs
e.printStackTrace();
} finally {
connection.setAutoCommit(true); // Re-enable auto-commit
}

Key Points:

  1. Use connection.setAutoCommit(false) to begin a transaction.
  2. Use connection.commit() to commit and connection.rollback() to rollback.

7. Batch Processing

Batch processing allows executing multiple SQL statements as a batch, which can improve performance when inserting, updating, or deleting large volumes of data.

Batch Processing Example:


String query = "INSERT INTO employees (name, age) VALUES (?, ?)";
PreparedStatement pstmt = connection.prepareStatement(query);

for (int i = 1; i <= 1000; i++) {
pstmt.setString(1, "Employee " + i);
pstmt.setInt(2, 25 + i);
pstmt.addBatch(); // Add to batch
}

int[] result = pstmt.executeBatch(); // Execute the batch
System.out.println("Batch executed successfully.");

Key Points:

  1. Use addBatch() to add SQL statements to a batch.
  2. Execute the batch using executeBatch().