How do you use JDBC to perform CRUD operations on a database?

JDBC (Java Database Connectivity) is a Java API that allows us to interact with relational databases. CRUD stands for Create, Read, Update and Delete operations, which are the basic database operations performed on data.

Here are the steps involved in using JDBC to perform CRUD operations on a database:

1. Import the required JDBC libraries:

To use JDBC, we need to import the JDBC driver library for the specific database we want to interact with. We can download the JDBC driver from the database vendors website, and then add it to the classpath of our project. For example, if we are using MySQL, we would add the MySQL JDBC driver jar file to the classpath.

2. Establish a database connection:

To interact with a database using JDBC, we need to establish a connection to the database. We can do this using the DriverManager class that is part of the JDBC API.

Here is an example of creating a database connection in Java:

    import java.sql.*;
    
    public class DatabaseConnection {
        
        public static void main(String[] args) {
            try {
                //load the MySQL JDBC driver
                Class.forName("com.mysql.cj.jdbc.Driver");
                
                //establish a database connection
                String url = "jdbc:mysql://localhost:3306/mydatabase";
                String user = "username";
                String password = "password";
                Connection connection = DriverManager.getConnection(url, user, password);
                
                //perform CRUD operations on the database
                //...
                
                //close the database connection
                connection.close();
            } catch (ClassNotFoundException ex) {
                System.out.println("MySQL JDBC driver not found");
            } catch (SQLException ex) {
                System.out.println("Failed to connect to the database");
            }
        }
    }

3. Perform CRUD operations:

Once we have established a connection to the database, we can perform CRUD operations on it using the various methods available in the JDBC API.

Here are examples of how to perform each CRUD operation:

Create: To create a new record in a database table, we need to execute an SQL INSERT statement. Here is an example:

    //create a PreparedStatement object with the SQL INSERT statement
    String sql = "INSERT INTO mytable (name, age) VALUES (?, ?)";
    PreparedStatement statement = connection.prepareStatement(sql);
    
    //set the values of the ? placeholders in the SQL statement
    statement.setString(1, "John");
    statement.setInt(2, 25);
    
    //execute the SQL INSERT statement
    int rowsInserted = statement.executeUpdate();
    
    //check the number of rows inserted
    if (rowsInserted > 0) {
        System.out.println("A new record was inserted.");
    }

Read: To read data from a database table, we need to execute an SQL SELECT statement. Here is an example:

    //create a Statement object with the SQL SELECT statement
    Statement statement = connection.createStatement();
    
    //execute the SQL SELECT statement and get the result set
    String sql = "SELECT * FROM mytable";
    ResultSet resultSet = statement.executeQuery(sql);
    
    //loop through the result set and print the values of each record
    while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        int age = resultSet.getInt("age");
        System.out.println(id + ", " + name + ", " + age);
    }

Update: To update an existing record in a database table, we need to execute an SQL UPDATE statement. Here is an example:

    //create a PreparedStatement object with the SQL UPDATE statement
    String sql = "UPDATE mytable SET age = ? WHERE name = ?";
    PreparedStatement statement = connection.prepareStatement(sql);
    
    //set the values of the ? placeholders in the SQL statement
    statement.setInt(1, 30);
    statement.setString(2, "John");
    
    //execute the SQL UPDATE statement
    int rowsUpdated = statement.executeUpdate();
    
    //check the number of rows updated
    if (rowsUpdated > 0) {
        System.out.println("An existing record was updated.");
    }

Delete: To delete a record from a database table, we need to execute an SQL DELETE statement. Here is an example:

    //create a PreparedStatement object with the SQL DELETE statement
    String sql = "DELETE FROM mytable WHERE id = ?";
    PreparedStatement statement = connection.prepareStatement(sql);
    
    //set the value of the ? placeholder in the SQL statement
    statement.setInt(1, 1);
    
    //execute the SQL DELETE statement
    int rowsDeleted = statement.executeUpdate();
    
    //check the number of rows deleted
    if (rowsDeleted > 0) {
        System.out.println("A record was deleted.");
    }

4. Close the database connection:

Once we have finished performing CRUD operations on the database, its important to close the connection to the database using the ‘close()‘ method to free up any resources used by the connection.

Thats it! These are the basic steps involved in using JDBC to perform CRUD operations on a database. Of course, there are many more advanced features available in JDBC that we can use to interact with databases, but these are the essential steps needed to get started with JDBC.