IGTechTeam

Connect your Java console with a database so that you can perform several operations like creating, deleting, inserting, and updating records.

What will you learn?

Download the MySQL connector jar file.
Test if the jar file is loaded or not.
Set up a local server environment.
JDBC (Java Database Connectivity)
Perform CRUD operations in Java

Download the MySQL connector jar file:

The most important part includes making connections. Mysql connector can be used to make a connection to the database. Download the latest version of the Mysql connector so that you won't face any problems during the connection. You can easily navigate to this URL and download mysql connector.

Select the operating system as platform independent.
It lists MySQL connectors. Download any one of them.
Extract the file.
Inside that file, there must be a file with a name like mysql-connector-java-x.x.x.jar
Open your editor: eclipse or IntelliJ Idea. Right now, I am doing for the eclipse. But for other editors also, the steps are nearly the same.
Create a Java project.
To add a jar file, right-click on the project > Build Path > Configure Build Path.
Add your jar file in the Java Build Path > Libraries > ModulePath.
There must be a module-info.java file in your src folder. If not, create one.
Add your dependency there as below.

module javaConnection {
  requires java.sql;
}

Now, your jar file should be loaded properly.

Test if the jar file is loaded or not:

package connection;

public class Main {

  public static void main(String[] args) {
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");

      System.out.println("Driver loaded properly.");
    } catch (ClassNotFoundException e) {
      System.out.println("Class not found exception");
      System.out.println(e);
    }
  }
}

Set up a local server environment:

You can use XAMPP to set up a local server environment to manage the database locally. To install Xampp, you can click here.

Then you can run your server through Xampp. Go to localhost/phpmyadmin from your browser, it should redirect to the dashboard from where you can create a database and a table.

You can create a database with a name say, Student_DB, and a table with a name say, Stu_Table.

JDBC (Java Database Connectivity)

Make sure your Java is successfully connected to the database.

package connection;

import java.sql.*;

public class Main {

  public static Connection setconnection() {
    String databaseURL = "jdbc:mysql://localhost:3306/Student_DB";

    String user = "root";
    String password = "";
    Connection conn = null;

    try {
      Class.forName("com.mysql.cj.jdbc.Driver");

      try {
        conn = DriverManager.getConnection(databaseURL, user, password);
      } catch (SQLException e) {
        System.out.println(e);
      }
    } catch (ClassNotFoundException e) {
      System.out.println("Class not found exception");
    }
    return conn;
  }

  public static void main(String[] args) {
    Main main = new Main();

    Connection conn = setconnection();
    if (conn == null) {
      System.out.println("Connection not established.");
    } else {
      System.out.println("Successfully connected to the database.");
    }

  }
}

I have made a complete video and post on JDBC (Java Database Connectivity). As its name suggests, it is a process of connection between Java and a database using MySQL connector.

After connecting to the database, you can easily perform CRUD operations.

CRUD (Create, Read/Retrieve, Update, Delete) operation in Java

In Java, the CRUD (Create, Read/Retrieve, Update, and Delete) operation is most useful to perform any task related to the database.

Create statement = Construct a database and a table

Read statement = Read database information

Update = Change the value in a database

Delete statement = Delete a row in a database

CRUD is data-driven and makes use of HTTP methods in a standardized manner. HTTP contains a few methods that act as CRUD operations and it is important to note that they are quite important from a developmental point of view in programming that also helps us relate better web development and also aids us when dealing with databases. As a result, the following are the standard CRUD Operations:

POST: This method produces a new resource.
GET: Reads and returns a resource.
PUT: This method updates an existing resource.
DELETE: Removes a resource.

package connection;

import java.sql.*;

public class Main {

  public static Connection setconnection() {
    String databaseURL = "jdbc:mysql://localhost:3306/Student_DB";
    String user = "root";
    String password = "";

    Connection conn = null;

    try {
      Class.forName("com.mysql.cj.jdbc.Driver");

      try {
        conn = DriverManager.getConnection(databaseURL, user, password);
        System.out.println("Connected to the database");
      } catch (SQLException e) {
        System.out.println(e);
      }
    } catch (ClassNotFoundException e) {
      System.out.println("Class not found exception");
    }
    return conn;
  }

  // Insert/Create data into database
  public void insert() {
    Connection conn = setconnection();
    try {
      Statement st = conn.createStatement();
      st.executeUpdate("insert into Stu_Table(id,name,address,age) " + "values(2,'Ishwar','Butwal', 27)");
      System.out.println("Data successfully inserted.");
    } catch (SQLException e) {
      System.out.println(e);
    }
  }

  // Retrieve data from database
  public void read() {
    Connection conn = setconnection();
    try {
      Statement st = conn.createStatement();
      ResultSet rs = st.executeQuery("select * from Stu_Table");
      while (rs.next()) {
        System.out.println(rs.getString("name") + "\t" + rs.getString("address") + "\t" + rs.getInt("age"));

      }
    } catch (SQLException e) {
      System.out.println(e);
    }

  }

  // Update data into database
  public void update() {
    Connection conn = setconnection();
    try {
      Statement st = conn.createStatement();
      st.executeUpdate("update Stu_Table set age=26 where id=2");
      System.out.println("Data successfully updated.");
    } catch (SQLException e) {
      System.out.println(e);
    }
  }

  // Delete data from the database
  public void delete() {
    Connection conn = setconnection();
    try {
      Statement st = conn.createStatement();
      st.executeUpdate("delete from Stu_Table where id=2");
      System.out.println("Data successfully deleted.");
    } catch (SQLException e) {
      System.out.println(e);
    }
  }

  public static void main(String[] args) {
    Main main = new Main();
    // Run one statement at a time (insert, read, update, delete)
    main.insert();
    //		main.read();
    //        main.update();
    //        main.delete();

  }
}

Conclusion:

In this way, you can perform CRUD operations in Java. First of all, you need to install the MySQL connector jar file and then locate the file properly in your editor. After that, you can set up a local server with Xampp and test the connection to the database. Finally, you can perform insert/create, read/retrieve, update, and delete operations in Java.

I hope this post is very helpful to you. If you have any questions, you can ask me in the comment section. I will reply as soon as possible. Thanks.

Java MySQL Tutorial: CRUD Operations