CRUD Operations In Java with mysql Using JDBC

In this post, we will become familiar with a few things simultaneously. It is being suggested to use the java Eclipse platform to work on these projects. In this instructional exercise, you will know how to use JDBC and its methods and how to implement it with any databases.
If you don’t know how to do CRUD operations in java with MySQL using JDBC then you are at the right place to know your problem’s solution.

CRUD Operations In Java Using JDBC

What is JDBC?

JDBC represents Java Database Connectivity. JDBC is a Java API to interface and execute the question with the database. It is a piece of JavaSE (Java Standard Edition). JDBC API utilizes JDBC drivers to connect with the database.
In this program, we will interface MySQL Server with Java. So we need a connector for MySQL that is otherwise called MySQL-connector-java.

Let’s start with creating a table in a database

creation of the database

+ - + - + - + - + - + - + 

| Field | Type | Null | Key | Default 

+ - + - + - + - + - + - + 

| USERID | int(10) | NO | PRI | NULL | 

| FULLNAME | varchar(20) | NO | NULL | 

| DOB | date | NO | NULL | 

| EMAILID | varchar(50) | NO | NULL | 

+ - + - + - + - + - + - +

You can create this database by writing the below code:-

/*import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
*/
import java.sql.*;
class CreateTableExample {
  
private static final String CREATE_TABLE="CREATE TABLE Employee
("+"USER_ID int(10) NOT NULL,"+"FULLNAME VARCHAR(20) NOT NULL,"+"DOB DATE NOT NULL,"+"EMAIL VARCHAR(50) NOT NULL,"+"PRIMARY KEY (EMP_ID))";
  
public static void main(String[] args) 
{
    String url = "jdbc:mysql://localhost:3306/shubham/crudopn";
    String username = "root";
    String passwd = "admin";

    Connection connect = null;
    PreparedStatement statement = null;

    try 
{
connect = DriverManager.getConnection(url, username, passwd);
statement = connect.prepareStatement(CREATE_TABLE);
statement.executeUpdate();

System.out.println("Table created");

}catch(SQLException e) 
{
e.printStackTrace();
}finally
{
      try {
        if (statement != null) {
          statement.close();
        }
        if (connect!= null) {
          connect.close();
        }
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  }
}

String url = "jdbc:mysql://localhost:3306/shubham/crudopn";

It’s a Driver Class which is used to build the connection with the MySql server.

Insertion of the records in the database

Use of PreparedStatement to insert the values into the corresponding attributes of a table.

PreparedStatement- It is used to execute a parameterized query entered by the user.
In the place of ‘?’ sign it will take the value entered by the user.

import java.sql.*;
import java.util.Scanner;
class InsertionIntoTable{
    public static void main(String[] args){
        Scanner sc=new Scanner(System.in);
         try {
      System.out.println("Enter user Id:");
      String userid =sc.next();
      System.out.println("Enter FULL NAME");
      String name =sc.next();
      System.out.println("Enter Date of Birth:");
      String dob = sc.next();
      System.out.println("Enter Emailid:");
      String email = sc.next();
      Class.forName("mysql.jdbc.Driver");
   Connection connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/shubham/crudopn", "username", "passwd");
   PreparedStatement pst = connect.prepareStatement("insert into Employee(USER_ID,FULLNAME,DOB,EMAIL) values(?,?,?,?)");
 
  pst.setString(1,userid);
  pst.setString(2, name);
  pst.setString(3, dob);
  pst.setString(4, email);
        
  int i = pst.executeUpdate();
  if(i!=0){
        System.out.println("added");
      }
      else{
        System.out.println("failed");
      }
    }
    catch (Exception e){
     System.out.println(e);
    }
  }
}

The output will be-

Enter user Id:
101010
Enter FULL NAME:
Shubham Raj
Enter Date of Birth:
4 september
Enter Emailid:
[email protected]

Read data from a table

you can do it by:-

import java.sql.*;
class FetchingData{
public static void main(String[] args){
    try {
   Class.forName("mysql.jdbc.Driver");
   Connection connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/shubham/crudopn", "username", "passd");
   Statement mystatement = connect.createStatement();
   ResultSet output=mystatement.executeQuery("select * from data where username=\"Shubham Raj\"");
   while(output.next())
      {
          System.out.println(output.getString("userid")+"  "+output.getString("name")+"  "+output.getString("dob")+" "+output.getString("email"));  
          
      }
      
         }   
         catch (Exception e){
     System.out.println(e);
    }
  }
}

ExecuteQuery() method is used to execute the query.

The Output will be-

+ - + - + - + - + - + - + | USERID | FULLNAME  | DOB |  EMAILID 
                          |101010   |Shubham Raj| 4 September|[email protected]|
+ - + - + - + - + - + - +

Deletion of the data from the table

You can delete any data from the created table

Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/shubham/crudopn",username","passd");
String query = "delete from users where id = 101010";
PreparedStatement preparedStmt = conn.prepareStatement(query);

row deleted.