Java Database Connectivity (JDBC)
Java Database Connectivity (JDBC) is an API that enables Java applications to interact with a wide range of databases. It provides methods for querying and updating data in a database. JDBC is part of the Java Standard Edition platform and is included in the java.sql package.
Key Concepts in JDBC
- DriverManager: Manages a list of database drivers and establishes a connection to the database.
- Connection: Represents a connection to a specific database.
- Statement: Used to execute SQL queries.
- PreparedStatement: Used for executing precompiled SQL queries with parameters.
- ResultSet: Represents the result of a query and provides methods to access the data.
- SQLException: Handles SQL errors.
Setting Up JDBC
1. Add the Database Driver
To connect to a database, you need the appropriate JDBC driver. For example, to connect to a MySQL database, you need the MySQL JDBC driver. Add the driver dependency to your project.
- Maven Example:
Java
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>2. Load the Driver Class
Although this step is often not needed with modern JDBC drivers that use the service provider mechanism, it’s a good practice to ensure the driver class is loaded.
Java
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}Establishing a Connection
Use DriverManager to establish a connection to the database.
Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCExample {
public static void main(String[] args) {
String jdbcURL = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "password";
try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
System.out.println("Connected to the database");
} catch (SQLException e) {
e.printStackTrace();
}
}
}Executing SQL Queries
Using Statement
Statement is used for executing simple SQL queries without parameters.
- Example: Executing a Query:
Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCStatementExample {
public static void main(String[] args) {
String jdbcURL = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "password";
try (Connection connection = DriverManager.getConnection(jdbcURL, username, password);
Statement statement = connection.createStatement()) {
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
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);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}Using PreparedStatement
PreparedStatement is used for executing precompiled SQL queries with parameters. It helps prevent SQL injection attacks.
- Example: Executing a Query with Parameters:
Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCPreparedStatementExample {
public static void main(String[] args) {
String jdbcURL = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "password";
try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
String sql = "SELECT * FROM users WHERE id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, 1);
ResultSet resultSet = preparedStatement.executeQuery();
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);
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}Updating Data
Using Statement
- Example: Inserting Data:
Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCInsertExample {
public static void main(String[] args) {
String jdbcURL = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "password";
try (Connection connection = DriverManager.getConnection(jdbcURL, username, password);
Statement statement = connection.createStatement()) {
String sql = "INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com')";
int rowsInserted = statement.executeUpdate(sql);
if (rowsInserted > 0) {
System.out.println("A new user was inserted successfully!");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}Using PreparedStatement
- Example: Updating Data:
Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JDBCUpdateExample {
public static void main(String[] args) {
String jdbcURL = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "password";
try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
String sql = "UPDATE users SET email = ? WHERE id = ?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "new.email@example.com");
preparedStatement.setInt(2, 1);
int rowsUpdated = preparedStatement.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("An existing user was updated successfully!");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}Handling Transactions
Transactions ensure that a series of operations are executed as a single unit of work. JDBC provides methods to manage transactions.
- Example: Managing Transactions:
Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class JDBCTransactionExample {
public static void main(String[] args) {
String jdbcURL = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "password";
try (Connection connection = DriverManager.getConnection(jdbcURL, username, password)) {
connection.setAutoCommit(false);
String sqlInsert = "INSERT INTO users (name, email) VALUES (?, ?)";
String sqlUpdate = "UPDATE accounts SET balance = balance - ? WHERE user_id = ?";
try (PreparedStatement insertStmt = connection.prepareStatement(sqlInsert);
PreparedStatement updateStmt = connection.prepareStatement(sqlUpdate)) {
insertStmt.setString(1, "Alice");
insertStmt.setString(2, "alice@example.com");
insertStmt.executeUpdate();
updateStmt.setDouble(1, 100.00);
updateStmt.setInt(2, 1);
updateStmt.executeUpdate();
connection.commit();
System.out.println("Transaction committed successfully!");
} catch (SQLException e) {
connection.rollback();
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}Best Practices for Using JDBC
- Close Resources: Always close
Connection,Statement, andResultSetobjects to prevent resource leaks. - Use Prepared Statements: Use
PreparedStatementto prevent SQL injection and improve performance. - Manage Transactions: Use transactions to ensure data consistency and handle rollback scenarios.
- Connection Pooling: Use connection pooling to manage database connections efficiently and improve performance.
- Exception Handling: Properly handle SQL exceptions to ensure your application can recover from errors gracefully.
- Batch Processing: Use batch processing to execute multiple SQL statements efficiently.
Summary
JDBC provides a powerful and flexible way to interact with databases from Java applications. By understanding the core concepts, setting up connections, executing queries, and managing transactions, you can build robust database-driven applications. Following best practices ensures that your application is efficient, secure, and maintainable.