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,
name VARCHAR(100),
age INT,
department VARCHAR(100)
);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 {
connection = DriverManager.getConnection(url, user, password);
System.out.println("Connected to the database.");
} catch (SQLException e) {
e.printStackTrace();
}
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)) {
preparedStatement.setString(1, "John Doe");
preparedStatement.setInt(2, 30);
preparedStatement.setString(3, "IT");
int rowsInserted = preparedStatement.executeUpdate();
if (rowsInserted > 0) {
System.out.println("A new employee was inserted successfully.");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}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) {
e.printStackTrace();
}
}
}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)) {
preparedStatement.setInt(1, 31);
preparedStatement.setString(2, "HR");
preparedStatement.setString(3, "John Doe");
int rowsUpdated = preparedStatement.executeUpdate();
if (rowsUpdated > 0) {
System.out.println("An existing employee's data was updated successfully.");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}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)) {
preparedStatement.setString(1, "John Doe");
int rowsDeleted = preparedStatement.executeUpdate();
if (rowsDeleted > 0) {
System.out.println("An employee was deleted successfully.");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}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)) {
preparedStatement.setString(1, name);
preparedStatement.setInt(2, age);
preparedStatement.setString(3, department);
preparedStatement.executeUpdate();
System.out.println("Employee created successfully: " + name);
} catch (SQLException e) {
e.printStackTrace();
}
}
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) {
e.printStackTrace();
}
}
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)) {
preparedStatement.setInt(1, newAge);
preparedStatement.setString(2, newDepartment);
preparedStatement.setString(3, name);
preparedStatement.executeUpdate();
System.out.println("Employee updated successfully: " + name);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void deleteEmployee(String name) {
String deleteSQL = "DELETE FROM employees WHERE name = ?";
try (Connection connection = JDBCConnection.connect();
PreparedStatement preparedStatement = connection.prepareStatement(deleteSQL)) {
preparedStatement.setString(1, name);
preparedStatement.executeUpdate();
System.out.println("Employee deleted successfully: " + name);
} catch (SQLException e) {
e.printStackTrace();
}
}
}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.
Please subscribe or like our page for newsletters