Java CRUD Operation MySql (SQL Insert, Select, Update and Delete)

Java CRUD Operation MySql (SQL Insert, Select, Update and Delete)

To perform CRUD (create, read, update, delete) operations on a MySQL database using Java, you can use the MySQL Connector/J driver, which is a MySQL-specific JDBC driver that allows you to connect to a MySQL database and execute SQL statements.

Here is an example of how you can use the MySQL Connector/J driver to perform a simple CRUD operation in Java:

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

public class Main {
  public static void main(String[] args) {
    // Load the MySQL Connector/J driver
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
    } catch (ClassNotFoundException e) {
      System.err.println("Error loading MySQL Connector/J driver: " + e);
      return;
    }

    // Establish a connection to the database
    Connection conn = null;
    try {
      conn = DriverManager.getConnection("jdbc:mysql://localhost/testdb", "username", "password");
    } catch (SQLException e) {
      System.err.println("Error connecting to the database: " + e);
      return;
    }

    // Perform a simple CRUD operation
    try {
      // Create a new record
      PreparedStatement stmt = conn.prepareStatement("INSERT INTO users (name, email) VALUES (?, ?)");
      stmt.setString(1, "John Smith");
      stmt.setString(2, "john@example.com");
      stmt.executeUpdate();

      // Read all records
      stmt = conn.prepareStatement("SELECT * FROM users");
      ResultSet rs = stmt.executeQuery();
      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);
      }

      // Update a record
      stmt = conn.prepareStatement("UPDATE users SET name = ? WHERE id = ?");
      stmt.setString(1, "Jane Smith");
      stmt.setInt(2, 1);
      stmt.executeUpdate();

      // Delete a record
      stmt = conn.prepareStatement("DELETE FROM users WHERE id = ?");
      stmt.setInt(1, 1);
      stmt.executeUpdate();
    } catch (SQLException e) {
      System.err.println("Error performing CRUD operation: " + e);
    }

    // Close the connection
    try {
      conn.close();
    } catch (SQLException e) {
      System.err.println("Error closing connection: " + e);
    }
  }
}

This example creates a new record in the “users” table, reads all records from the table, updates one of the records, and then deletes it.

Note that this is just a basic example, and you will likely need to handle error

To create the “users” table for the Java code above, you can use the following SQL statement:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL
);

This statement creates a table with three columns: “id”, “name”, and “email”. The “id” column is an auto-incrementing primary key, and the “name” and “email” columns are non-nullable varchar columns.

You can execute this SQL statement using the Statement class in Java, like this:

Statement stmt = conn.createStatement();
stmt.executeUpdate("CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL)");

You will need to replace conn with a valid Connection object that is connected to your MySQL database.

The Java code I provided performs the following CRUD (create, read, update, delete) operations on a MySQL database:

1. Create: A new record is inserted into the “users” table using an INSERT statement and the executeUpdate method of the PreparedStatement class. The setString method is used to set the values for the “name” and “email” columns, and the executeUpdate method is used to execute the INSERT statement.
2. Read: All records from the “users” table are selected and read using a SELECT statement and the executeQuery method of the Prepared Statement class. The ResultSet object returned by executeQuery is used to iterate over the rows of the result set, and the getInt, getString, and next methods are used to access the values of the columns in each row.
3. Update: An existing record in the “users” table is updated using an UPDATE statement and the executeUpdate method of the Prepared Statement class. The setString method is used to set the new value for the “name” column, and the setInt method is used to set the value for the “id” column, which is used to identify the record to update.
4. Delete: An existing record in the “users” table is deleted using a DELETE statement and the executeUpdate method of the Prepared Statement class. The setInt method is used to set the value for the “id” column, which is used to identify the record to delete.

After each CRUD operation, the Prepared Statement object is closed using the close method to free up resources. Finally, the connection to the database is closed using the close method of the Connection object.

Here are some additional points to consider when working with CRUD operations in Java and MySQL:

• It is important to properly handle exceptions and errors that may occur during the CRUD operations. For example, you should catch SQLException when working with the JDBC API, and you should handle errors such as database connection failures or constraint violations.
• It is also a good idea to use parameterized queries when executing SQL statements to prevent SQL injection attacks. This can be done using Prepared Statement objects and the set methods.
• You may want to consider using a database connection pool to improve the performance of your application. A connection pool allows you to reuse existing connections to the database rather than creating a new connection for each request.
• If you are working with large amounts of data, you may want to consider using the ResultSet object’s setFetchSize method to control how many rows are retrieved at a time, or using the LIMIT and OFFSET clauses in your SELECT statements to paginate the results.
• Finally, it is a good idea to follow best practices for database design, such as normalization, indexing, and primary key and foreign key constraints, to ensure the scalability and maintainability of your application.

phpMyAdmin SQL Dump

-- phpMyAdmin SQL Dump
-- version 5.1.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Dec 22, 2022 at 11:07 PM
-- Server version: 10.4.20-MariaDB
-- PHP Version: 8.0.8

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `testdb`
--
CREATE DATABASE IF NOT EXISTS `testdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `testdb`;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

You can Clone The Code From This Github Repository Link – https://github.com/mauricemuteti/Java-CRUD-Operation-MySql-SQL-Insert-Select-Update-and-Delete-