JDBC (Java Database Connectivity) is the standard API in Java for connecting to and interacting with relational databases. It allows Java programs to perform CRUD (Create, Read, Update, Delete) operations on a database.
In this section, we’ll look at how to perform CRUD operations using JDBC.
To connect to a database using JDBC, you need to:
Statement
, PreparedStatement
, etc.) and
retrieve results.Before starting, make sure to import the required JDBC classes:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
We will assume that you have a database named company
with a table employees
defined as follows:
CREATE TABLE employees (
id INT PRIMARY KEY AUTO_INCREMENT,
VARCHAR(100),
name INT,
age VARCHAR(100)
department );
Before performing any CRUD operations, we need to establish a connection to the database using the JDBC driver.
public class JDBCConnection {
public static Connection connect() {
String url = "jdbc:mysql://localhost:3306/company";
String user = "root";
String password = "password"; // replace with your actual password
Connection connection = null;
try {
= DriverManager.getConnection(url, user, password);
connection System.out.println("Connected to the database.");
} catch (SQLException e) {
.printStackTrace();
e}
return connection;
}
}
To insert data into the employees
table:
public class CreateEmployee {
public static void main(String[] args) {
Connection connection = JDBCConnection.connect();
String insertSQL = "INSERT INTO employees (name, age, department) VALUES (?, ?, ?)";
try (PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {
.setString(1, "John Doe");
preparedStatement.setInt(2, 30);
preparedStatement.setString(3, "IT");
preparedStatement
int rowsInserted = preparedStatement.executeUpdate();
if (rowsInserted > 0) {
System.out.println("A new employee was inserted successfully.");
}
} catch (SQLException e) {
.printStackTrace();
e}
}
}
Explanation:
PreparedStatement
to safely insert data into the
database. This prevents SQL injection attacks.name
, age
, and
department
are set using setString
and
setInt
.To fetch data from the employees
table:
public class ReadEmployee {
public static void main(String[] args) {
Connection connection = JDBCConnection.connect();
String selectSQL = "SELECT * FROM employees";
try (PreparedStatement preparedStatement = connection.prepareStatement(selectSQL);
ResultSet resultSet = preparedStatement.executeQuery()) {
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String department = resultSet.getString("department");
System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age + ", Department: " + department);
}
} catch (SQLException e) {
.printStackTrace();
e}
}
}
Explanation:
PreparedStatement
is used to prepare and execute the
SQL query.ResultSet
is used to iterate over the result set and
retrieve column values.To update an existing employee’s details:
public class UpdateEmployee {
public static void main(String[] args) {
Connection connection = JDBCConnection.connect();
String updateSQL = "UPDATE employees SET age = ?, department = ? WHERE name = ?";
try (PreparedStatement preparedStatement = connection.prepareStatement(updateSQL)) {
.setInt(1, 31);
preparedStatement.setString(2, "HR");
preparedStatement.setString(3, "John Doe");
preparedStatement
int rowsUpdated = preparedStatement.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("An existing employee's data was updated successfully.");
}
} catch (SQLException e) {
.printStackTrace();
e}
}
}
Explanation:
UPDATE
SQL query updates the employee’s
age
and department
where the employee’s
name
matches “John Doe”.PreparedStatement
is used to set the new values for
age
and department
.To delete an employee from the employees
table:
public class DeleteEmployee {
public static void main(String[] args) {
Connection connection = JDBCConnection.connect();
String deleteSQL = "DELETE FROM employees WHERE name = ?";
try (PreparedStatement preparedStatement = connection.prepareStatement(deleteSQL)) {
.setString(1, "John Doe");
preparedStatement
int rowsDeleted = preparedStatement.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("An employee was deleted successfully.");
}
} catch (SQLException e) {
.printStackTrace();
e}
}
}
Explanation:
DELETE
SQL query deletes an employee with the name
“John Doe”.PreparedStatement
is used to safely pass the name of
the employee to be deleted.Below is a full example that integrates all CRUD operations into a single Java program:
import java.sql.*;
public class CRUDExample {
public static void main(String[] args) {
// Create operation
createEmployee("Alice", 25, "IT");
// Read operation
readEmployees();
// Update operation
updateEmployee("Alice", 26, "HR");
// Read operation after update
readEmployees();
// Delete operation
deleteEmployee("Alice");
// Final read operation
readEmployees();
}
public static void createEmployee(String name, int age, String department) {
String insertSQL = "INSERT INTO employees (name, age, department) VALUES (?, ?, ?)";
try (Connection connection = JDBCConnection.connect();
PreparedStatement preparedStatement = connection.prepareStatement(insertSQL)) {
.setString(1, name);
preparedStatement.setInt(2, age);
preparedStatement.setString(3, department);
preparedStatement.executeUpdate();
preparedStatementSystem.out.println("Employee created successfully: " + name);
} catch (SQLException e) {
.printStackTrace();
e}
}
public static void readEmployees() {
String selectSQL = "SELECT * FROM employees";
try (Connection connection = JDBCConnection.connect();
PreparedStatement preparedStatement = connection.prepareStatement(selectSQL);
ResultSet resultSet = preparedStatement.executeQuery()) {
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
int age = resultSet.getInt("age");
String department = resultSet.getString("department");
System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age + ", Department: " + department);
}
} catch (SQLException e) {
.printStackTrace();
e}
}
public static void updateEmployee(String name, int newAge, String newDepartment) {
String updateSQL = "UPDATE employees SET age = ?, department = ? WHERE name = ?";
try (Connection connection = JDBCConnection.connect();
PreparedStatement preparedStatement = connection.prepareStatement(updateSQL)) {
.setInt(1, newAge);
preparedStatement.setString(2, newDepartment);
preparedStatement.setString(3, name);
preparedStatement.executeUpdate();
preparedStatementSystem.out.println("Employee updated successfully: " + name);
} catch (SQLException e) {
.printStackTrace();
e}
}
public static void deleteEmployee(String name) {
String deleteSQL = "DELETE FROM employees WHERE name = ?";
try (Connection connection = JDBCConnection.connect();
PreparedStatement preparedStatement = connection.prepareStatement(deleteSQL)) {
.setString(1, name);
preparedStatement.executeUpdate();
preparedStatementSystem.out.println("Employee deleted successfully: " + name);
} catch (SQLException e) {
.printStackTrace();
e}
}
}
JDBC makes it easy to perform CRUD operations on a relational
database from Java. By using PreparedStatement
for all
operations, we ensure that the code is safe from SQL injection attacks
and that it works efficiently with different types of databases.
In the above example, we created a database connection, performed all four CRUD operations (Create, Read, Update, Delete), and handled the results using JDBC. You can expand this example further to include error handling, transaction management, and other advanced features.