18/11/2024
CRUD operation in JDBC
Java

1. How to perform CRUD operation in JDBC ( Best solutions )

Ram Chadarby Ram Chadar

Way 1 : CRUD operation in JDBC ( Core Java )

To perform CRUD operation in JDBC in simple Core Java, you can follow these steps

How to write JDBC code in Java

  • Import the necessary Java JDBC classes:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
  • Establish a database connection:
String jdbcUrl = "jdbc:mysql://localhost:3306/your_database";
String username = "your_username";
String password = "your_password";

try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
    // Perform CRUD operations here
} catch (SQLException e) {
    e.printStackTrace();
}

Replace your_database, your_username, and your_password with your actual database details.

  • Perform CRUD operation in JDBC:

Create (Insert) Operation:

String insertSql = "INSERT INTO your_table_name (column1, column2, ...) VALUES (?, ?, ...)";
try (PreparedStatement pstmt = connection.prepareStatement(insertSql)) {
    pstmt.setString(1, value1); // Set the values for the placeholders
    pstmt.setString(2, value2);
    // Set values for other placeholders if needed
    
    int rowsInserted = pstmt.executeUpdate();
    System.out.println(rowsInserted + " row(s) inserted.");
} catch (SQLException e) {
    e.printStackTrace();
}

The create operation is used to insert new records into a database table. In JDBC, you can use the INSERT statement to specify the values to be inserted. The operation typically involves the following steps:

  • Create an SQL INSERT statement with placeholders for the values.
  • Set the values for the placeholders using a PreparedStatement object.
  • Execute the INSERT statement using the executeUpdate() method of the PreparedStatement object.
  • The executeUpdate() method returns the number of rows affected by the insert operation.

Read (Select) Operation:

String selectSql = "SELECT * FROM your_table_name";
try (Statement stmt = connection.createStatement();
     ResultSet rs = stmt.executeQuery(selectSql)) {
    while (rs.next()) {
        // Retrieve values from the result set
        String value1 = rs.getString("column1");
        String value2 = rs.getString("column2");
        // Retrieve values for other columns if needed
        
        // Process the retrieved values
        System.out.println("Value 1: " + value1);
        System.out.println("Value 2: " + value2);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

The read operation is used to retrieve records from a database table. In JDBC, you can use the SELECT statement to specify the columns to be retrieved and any conditions to filter the data. The operation typically involves the following steps:

  • Create an SQL SELECT statement to retrieve the desired columns from the table.
  • Execute the SELECT statement using a Statement object or a PreparedStatement object.
  • Obtain a ResultSet object containing the retrieved rows.
  • Iterate over the ResultSet to access the retrieved data for each row.

Update Operation:

String updateSql = "UPDATE your_table_name SET column1 = ?, column2 = ? WHERE condition";
try (PreparedStatement pstmt = connection.prepareStatement(updateSql)) {
    pstmt.setString(1, newValue1); // Set the new values for the placeholders
    pstmt.setString(2, newValue2);
    // Set new values for other placeholders if needed
    
    int rowsUpdated = pstmt.executeUpdate();
    System.out.println(rowsUpdated + " row(s) updated.");
} catch (SQLException e) {
    e.printStackTrace();
}

The update operation is used to modify existing records in a database table. In JDBC, you can use the UPDATE statement to specify the columns to be updated and any conditions to identify the rows to be updated. The operation typically involves the following steps:

  • Create an SQL UPDATE statement with placeholders for the new values and conditions.
  • Set the new values for the placeholders using a PreparedStatement object.
  • Execute the UPDATE statement using the executeUpdate() method of the PreparedStatement object.
  • The executeUpdate() method returns the number of rows affected by the update operation.

Delete Operation:

String deleteSql = "DELETE FROM your_table_name WHERE condition";
try (Statement stmt = connection.createStatement()) {
    int rowsDeleted = stmt.executeUpdate(deleteSql);
    System.out.println(rowsDeleted + " row(s) deleted.");
} catch (SQLException e) {
    e.printStackTrace();
}

The delete operation is used to remove records from a database table. In JDBC, you can use the DELETE statement to specify the conditions to identify the rows to be deleted. The operation typically involves the following steps:

  • Create an SQL DELETE statement with conditions to identify the rows to be deleted.
  • Execute the DELETE statement using a Statement object or a PreparedStatement object.
  • The executeUpdate() method returns the number of rows affected by the delete operation.

Remember to replace your_table_name with the actual name of your table, and adjust the SQL statements and placeholders according to your specific requirements.

  • Close the database connection:
connection.close();

Make sure to handle exceptions appropriately and close the connection after performing any CRUD operation in JDBC to release resources.

These steps outline a basic implementation of JDBC CRUD operations in simple Core Java. However, it’s worth noting that using a framework like Spring JDBC or Hibernate can provide more advanced features, improved code organization, and additional benefits.

Here single Java class that demonstrates all CRUD operation in JDBC (Create, Read, Update, Delete):

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCCrudOperations {

    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USERNAME = "your_username";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD)) {
            System.out.println("Connected to the database.");

            // Create (Insert) Operation
            insertRecord(connection, "John Doe", "john@example.com");

            // Read (Select) Operation
            selectRecords(connection);

            // Update Operation
            updateRecord(connection, 1, "Jane Smith");

            // Read (Select) Operation
            selectRecords(connection);

            // Delete Operation
            deleteRecord(connection, 1);

            // Read (Select) Operation
            selectRecords(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void insertRecord(Connection connection, String name, String email) throws SQLException {
        String insertSql = "INSERT INTO your_table_name (name, email) VALUES (?, ?)";
        try (PreparedStatement pstmt = connection.prepareStatement(insertSql)) {
            pstmt.setString(1, name);
            pstmt.setString(2, email);
            int rowsInserted = pstmt.executeUpdate();
            System.out.println(rowsInserted + " row(s) inserted.");
        }
    }

    private static void selectRecords(Connection connection) throws SQLException {
        String selectSql = "SELECT * FROM your_table_name";
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(selectSql)) {
            System.out.println("Records:");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
            }
            System.out.println();
        }
    }

    private static void updateRecord(Connection connection, int id, String name) throws SQLException {
        String updateSql = "UPDATE your_table_name SET name = ? WHERE id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(updateSql)) {
            pstmt.setString(1, name);
            pstmt.setInt(2, id);
            int rowsUpdated = pstmt.executeUpdate();
            System.out.println(rowsUpdated + " row(s) updated.");
        }
    }

    private static void deleteRecord(Connection connection, int id) throws SQLException {
        String deleteSql = "DELETE FROM your_table_name WHERE id = ?";
        try (PreparedStatement pstmt = connection.prepareStatement(deleteSql)) {
            pstmt.setInt(1, id);
            int rowsDeleted = pstmt.executeUpdate();
            System.out.println(rowsDeleted + " row(s) deleted.");
        }
    }
}

Make sure to replace your_table_name, your_database, your_username, and your_password with the actual table name, database details, username, and password for your database configuration. This class performs the following operations in sequence:

  1. Inserts a record into the table.
  2. Reads and displays all records from the table.
  3. Updates a record in the table.
  4. Reads and displays all records from the table again.
  5. Deletes a record from the table.
  6. Reads and displays all records from the table one final time.

NOTE : Ensure that you have the appropriate MySQL JDBC driver included in your classpath.

Way 2 : CRUD operation in JDBC ( In Maven Project )

To configure JDBC and MySQL in a Maven project, you need to include the necessary dependencies in your project’s pom.xml file and set up the required configuration. Here’s an example of how you can configure JDBC and MySQL in a Maven project:

  1. Add the MySQL Connector/J dependency to your pom.xml file:
<dependencies>
    <!-- Other dependencies -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.27</version>
    </dependency>
</dependencies>

Replace the version with the desired version of the MySQL Connector/J library.

  1. Create a configuration file to store the database connection details. You can use a properties file (database.properties) in the src/main/resources directory. The file should contain the following properties:
jdbc.url=jdbc:mysql://localhost:3306/your_database
jdbc.username=your_username
jdbc.password=your_password

Replace your_database, your_username, and your_password with your actual database details.

  1. Set up a utility class to manage the database connection. Create a new Java class (DatabaseUtil.java, for example) with the following code:
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DatabaseUtil {

    private static final String DATABASE_PROPERTIES_FILE = "database.properties";

    public static Connection getConnection() throws SQLException, IOException {
        try (InputStream inputStream = DatabaseUtil.class.getClassLoader().getResourceAsStream(DATABASE_PROPERTIES_FILE)) {
            Properties properties = new Properties();
            properties.load(inputStream);

            String jdbcUrl = properties.getProperty("jdbc.url");
            String username = properties.getProperty("jdbc.username");
            String password = properties.getProperty("jdbc.password");

            return DriverManager.getConnection(jdbcUrl, username, password);
        }
    }
}

Now you can use the DatabaseUtil class to obtain a database connection wherever you need it in your project. For example, you can modify the main method of your application’s entry point class (App.java, for instance) as follows:

import java.sql.Connection;
import java.sql.SQLException;

public class App {

    public static void main(String[] args) {
        try (Connection connection = DatabaseUtil.getConnection()) {
            // Perform database operations using the connection
        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }
}

In the main method, you obtain a database connection by calling DatabaseUtil.getConnection(). Make sure to handle any exceptions that may occur.

This setup allows you to centralize the database configuration in the database.properties file, and you can retrieve the connection using the DatabaseUtil class whenever you need it. Remember to replace your_database, your_username, and your_password with your actual database details in the database.properties file.

With these steps, you should have a Maven project configured with JDBC and MySQL. You can now start performing database operations using the obtained connection.

Other Related Assignments:

Post Views: 249

Ram Chadar

Hello! I'm Ram Chadar, a passionate software developer and freelancer based in Pune. Welcome to my blog, where I share my experiences, insights, and knowledge in the world of software development, different technologies, freelancing, and more.

View all posts by Ram Chadar →

Leave a Reply

Your email address will not be published. Required fields are marked *