In this tutorial, we will create a simple JDBC application to perform CRUD (Create, Read, Update, Delete) operations on a MySQL database. We will use the latest JDBC features and best practices, including using try-with-resources for resource management.
Table of Contents
- Introduction
- Technologies Used
- Database Setup
- Project Setup
- Creating the JDBC Utility Class
- CRUD Operations
- Create
- Read
- Update
- Delete
- Testing CRUD Operations
- Conclusion
Check out the complete JDBC tutorial: JDBC Tutorial.
1. Introduction
Java Database Connectivity (JDBC) is an API that provides industry-standard and database-independent connectivity between Java applications and relational databases. JDBC allows Java applications to perform database operations like querying, updating, and retrieving data from relational databases, spreadsheets, and flat files.
To keep it simple, JDBC allows a Java application to connect to a relational database. Major databases such as Oracle, Microsoft SQL Server, DB2, and many others are supported.
In this tutorial, we will demonstrate how to use JDBC to perform CRUD operations on a MySQL database.
2. Technologies Used
- JDK 21
- MySQL 8.0
- JDBC API 4.2
- IDE (Eclipse, IntelliJ IDEA, etc.)
3. Database Setup
First, ensure you have MySQL installed and running. Create a database and a table for this tutorial.
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL,
country VARCHAR(50) NOT NULL,
salary DOUBLE NOT NULL
);
4. Project Setup
Set up your Java project and add the MySQL JDBC driver to your project's build path. You can download the JDBC driver from MySQL Connector/J.
5. Creating the JDBC Utility Class
Create a utility class to handle the database connection.
package com.example.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCUtils {
private static final String URL = "jdbc:mysql://localhost:3306/test_db?useSSL=false";
private static final String USER = "root";
private static final String PASSWORD = "root";
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
public static void printSQLException(SQLException ex) {
for (Throwable e : ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
}
6. CRUD Operations
Create
This operation inserts a new record into the database.
package com.example.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class InsertEmployee {
private static final String INSERT_EMPLOYEES_SQL = "INSERT INTO employees (name, email, country, salary) VALUES (?, ?, ?, ?);";
public void insertRecord(String name, String email, String country, double salary) throws SQLException {
// Establishing a Connection
try (Connection connection = JDBCUtils.getConnection();
// Creating a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(INSERT_EMPLOYEES_SQL)) {
// Setting parameters
preparedStatement.setString(1, name);
preparedStatement.setString(2, email);
preparedStatement.setString(3, country);
preparedStatement.setDouble(4, salary);
// Executing the query
preparedStatement.executeUpdate();
} catch (SQLException e) {
JDBCUtils.printSQLException(e);
}
}
}
Read
This operation reads and retrieves records from the database.
package com.example.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SelectEmployees {
private static final String SELECT_ALL_EMPLOYEES = "SELECT * FROM employees;";
public void selectAllRecords() throws SQLException {
// Establishing a Connection
try (Connection connection = JDBCUtils.getConnection();
// Creating a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_EMPLOYEES)) {
// Executing the query and getting the result set
ResultSet rs = preparedStatement.executeQuery();
// Processing the result set
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
String country = rs.getString("country");
double salary = rs.getDouble("salary");
System.out.println(id + ", " + name + ", " + email + ", " + country + ", " + salary);
}
} catch (SQLException e) {
JDBCUtils.printSQLException(e);
}
}
}
Update
This operation updates existing records in the database.
package com.example.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class UpdateEmployee {
private static final String UPDATE_EMPLOYEES_SQL = "UPDATE employees SET name = ?, email = ?, country = ?, salary = ? WHERE id = ?;";
public void updateRecord(int id, String name, String email, String country, double salary) throws SQLException {
// Establishing a Connection
try (Connection connection = JDBCUtils.getConnection();
// Creating a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_EMPLOYEES_SQL)) {
// Setting parameters
preparedStatement.setString(1, name);
preparedStatement.setString(2, email);
preparedStatement.setString(3, country);
preparedStatement.setDouble(4, salary);
preparedStatement.setInt(5, id); // Updating record with the provided ID
// Executing the query
preparedStatement.executeUpdate();
} catch (SQLException e) {
JDBCUtils.printSQLException(e);
}
}
}
Delete
This operation deletes a record from the database.
package com.example.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class DeleteEmployee {
private static final String DELETE_EMPLOYEES_SQL = "DELETE FROM employees WHERE id = ?;";
public void deleteRecord(int id) throws SQLException {
// Establishing a Connection
try (Connection connection = JDBCUtils.getConnection();
// Creating a statement using connection object
PreparedStatement preparedStatement = connection.prepareStatement(DELETE_EMPLOYEES_SQL)) {
// Setting parameter
preparedStatement.setInt(1, id); // Deleting record with the provided ID
// Executing the query
preparedStatement.executeUpdate();
} catch (SQLException e) {
JDBCUtils.printSQLException(e);
}
}
}
7. Testing CRUD Operations
Now, let's create a main class to test all the CRUD operations.
package com.example.jdbc;
import java.sql.SQLException;
public class TestCRUDOperations {
public static void main(String[] args) {
InsertEmployee insertEmployee = new InsertEmployee();
SelectEmployees selectEmployees = new SelectEmployees();
UpdateEmployee updateEmployee = new UpdateEmployee();
DeleteEmployee deleteEmployee = new DeleteEmployee();
try {
// Insert records
insertEmployee.insertRecord("Ravi Kumar", "ravi.kumar@example.com", "India", 50000);
insertEmployee.insertRecord("Sita Sharma", "sita.sharma@example.com", "India", 60000);
insertEmployee.insertRecord("Rahul Jain", "rahul.jain@example.com", "India", 55000);
// Select records
System.out.println("After inserting records:");
selectEmployees.selectAllRecords();
// Update record
updateEmployee.updateRecord(1, "Ravi Kumar Singh", "ravi.kumar@example.com", "India", 55000);
// Select records
System.out.println("After updating record with ID 1:");
selectEmployees.selectAllRecords();
// Delete record
deleteEmployee.deleteRecord(3);
// Select records
System.out.println("After deleting record with ID 3:");
selectEmployees.selectAllRecords();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
8. Conclusion
In this tutorial, we demonstrated how to use JDBC to perform CRUD operations on a MySQL database. JDBC allows you to interact with any relational database from your Java applications. This guide covered establishing a connection, creating a table, and performing insert, read, update, and delete operations using the JDBC API. Proper exception handling and resource management using try-with-resources were also emphasized to ensure the application's robustness.
Comments
Post a Comment
Leave Comment