JDBC Operations with Statement

JDBC allows us to perform Data Manipulation Language (DML) operations and Data retrieval Language (DRL) operations such as Insert, Update, Delete, Select, and Batch Update on a database. Below are examples of these operations and explanations of key JDBC statement methods.

1. Insert Data

To insert data into a table, use the INSERT INTO SQL command. Here's an example JDBC program to insert data:

import java.sql.*;

public class InsertDataExample {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/your_database", "root", "password");
            Statement statement = connection.createStatement();
            
            String insertQuery = "INSERT INTO Employees (ID, Name, Age, Department) "
                + "VALUES (1, 'John Doe', 30, 'Engineering')";
            int rowsAffected = statement.executeUpdate(insertQuery);
            
            System.out.println(rowsAffected + " row(s) inserted.");
            
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
      

2. Update Data

To update data in a table, use the UPDATE SQL command. Here's an example JDBC program to update data:

import java.sql.*;

public class UpdateDataExample {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/your_database", "root", "password");
            Statement statement = connection.createStatement();
            
            String updateQuery = "UPDATE Employees SET Age = 31 WHERE ID = 1";
            int rowsAffected = statement.executeUpdate(updateQuery);
            
            System.out.println(rowsAffected + " row(s) updated.");
            
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
      

3. Delete Data

To delete data from a table, use the DELETE FROM SQL command. Here's an example JDBC program to delete data:

import java.sql.*;

public class DeleteDataExample {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/your_database", "root", "password");
            Statement statement = connection.createStatement();
            
            String deleteQuery = "DELETE FROM Employees WHERE ID = 1";
            int rowsAffected = statement.executeUpdate(deleteQuery);
            
            System.out.println(rowsAffected + " row(s) deleted.");
            
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
      

4. Select Data

To retrieve data from a table, use the SELECT SQL command. Here's an example JDBC program to select data:

import java.sql.*;

public class SelectDataExample {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/your_database", "root", "password");
            Statement statement = connection.createStatement();
            
            String selectQuery = "SELECT * FROM Employees";
            ResultSet resultSet = statement.executeQuery(selectQuery);
            
            while (resultSet.next()) {
                System.out.println("ID: " + resultSet.getInt("ID"));
                System.out.println("Name: " + resultSet.getString("Name"));
                System.out.println("Age: " + resultSet.getInt("Age"));
                System.out.println("Department: " + resultSet.getString("Department"));
            }
            
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
      

5. Batch Update

Batch updates allow you to execute multiple SQL statements in a single batch. This is efficient when you need to perform multiple insert, update, or delete operations. Here's an example JDBC program for batch updates:

import java.sql.*;

public class BatchUpdateExample {
    public static void main(String[] args) {
        try {
            Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/your_database", "root", "password");
            Statement statement = connection.createStatement();
            
            // Add SQL statements to the batch
            statement.addBatch("INSERT INTO Employees (ID, Name, Age, Department) VALUES (2, 'Jane Doe', 25, 'HR')");
            statement.addBatch("UPDATE Employees SET Age = 32 WHERE ID = 2");
            statement.addBatch("DELETE FROM Employees WHERE ID = 2");
            
            // Execute the batch
            int[] results = statement.executeBatch();
            System.out.println("Batch update completed. " + results.length + " statement(s) executed.");
            
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
      

Summary of JDBC Statement Methods

JDBC provides several statement methods to execute SQL queries and manage database operations. Here's a summary of the key methods: