All IT Courses 50% Off
JAVA Tutorials

CRUD Operations on DB

The abbreviation CRUD stands for four basic operations that can be done on persistent storage (create, read, update and delete).  Also, these functions are mapped to standard HTTP methods. We are going to review CRUD operations on the database. All operations will be demonstrated on the MySQL database with the support of JDBC.

JDBC (Java Database Connectivity) is a part of the Java platform. JDBC provides us the possibility to connect from Java application any database (relational or non-relational). To be able to do this, JDBC API needs to utilize specific JDBC drivers. 

As we will work with the MySQL database, we need to download the library with MySQL driver. It is mysql-connector-java-8.0.11.jar. The version of the jar-file can differ.  The MySQL driver class name is com.mysql.jdbc.Driver. 

Before starting with CRUD operations, we have to connect to MySQL, to create a database and create a database table. 

Let’s create Java application that will do this for us:

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

public class InitDatabaseExample {

public static void main(String args[]) {
try {
Connection connection= DriverManager.getConnection
("jdbc:mysql://localhost/?     serverTimezone=UTC&useSSL=false&user=root&password=root"); 
System.out.println("We are successfully connected to  the MySQL    server.");
Statement stmt=connection.createStatement();
stmt.executeUpdate("CREATE DATABASE exampledb");
System.out.println("A new database with the name exampledb is   created.");
stmt.close();

System.out.println("Let's create a new table!");
stmt = connection.createStatement();
String sql = "CREATE TABLE exampledb.USER (ID INT, NAME         VARCHAR(50), "
+ "EMAIL VARCHAR(45), CREATION_TIME DATE, PRIMARY               KEY (ID))";
stmt.execute(sql);
System.out.println("The table is created successfully!");
stmt.close();

} catch (SQLException e) {

            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
      }

    }

}

The output of current applications will be:

We are successfully connected to the MySQL server.

A new database with the name exampledb is created.

Let’s create a new table!

The table is created successfully!

We the result of that execution with MySQL Workbench. If you had it opened before code execution, you need to reconnect to DBMS. After this, your database with one table will appear.

The table doesn’t have data for now. You can see the current state of our database and the table on the screenshot below:

CRUD Operations on DB

Create operation 

The create operation in a relational database is represented by the SQL statement INSERT. We will go ahead and write a code snippet to create a new User in our table. We will use a PreparedStatement object got from our Connection object. To insert values into the right columns, we will use the column’s index. It worth to remind that column indexing starts from 1.

PreparedStatement provides various set methods for each data type. Here are some of them:

setString(int parameterIndex, String x)
setBoolean(int parameterIndex, boolean x)
setDate(int parameterIndex, Date x)

A setXXX() method have to correspond a type of column in the database table.

Let’s look at our example:

import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Date;

public class CreationExample {

public static void main(String args[]) {

try {

Connection connection= DriverManager.getConnection

("jdbc:mysql://localhost:3306/exampledb? serverTimezone=UTC&useSSL=false&user=root&password=root"); 

System.out.println("We are succesfully connected to the MySQL  server.");

String sql = "INSERT INTO User (id, name, email, creation_time)  VALUES (?, ?, ?, ?)";

PreparedStatement statement = connection.prepareStatement(sql);

statement.setInt(1, 1);

statement.setString(2, "TestUser");

statement.setString(3, "testuser@gmail.com");

statement.setDate(4, new Date(new java.util.Date().getTime()));

 

int rowsInserted = statement.executeUpdate();

if (rowsInserted > 0) {

    System.out.println("A new user was inserted successfully!");

}

statement.close();

} catch (SQLException e) {

            System.out.println("SQLException: " + e.getMessage());

            System.out.println("SQLState: " + e.getSQLState());

            System.out.println("VendorError: " + e.getErrorCode());

    }

  }

}

 

You probably found that we have changed the connection URL string in comparison with the previous example. We added the name of the database.

The output of the application will be:

We are successfully connected to the MySQL server.

A new user was inserted successfully!

 

Read operation 

Now, we will look at data in our database table User. We are going to check if the new row was added and learn to work with a reading SQL statement . The read operation in a relational database is represented by the SQL statement SELECT. We can select all the data or just data from some particular columns.

Here are two equal requests:

SELECT * FROM exampledb.user;

SELECT user.ID, userNAME, user.EMAIL, user.CREATION_TIME FROM exampledb.user;

We can skip the database name from the SQL query in case we put the database name into the URL to connect to the MySQL server. 

Let’s look at code example:

package concurrent;

import java.sql.Connection;

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

public class ReadExample {
public static void main(String args[]) {
try {
Connection connection= DriverManager.getConnection
("jdbc:mysql://localhost:3306/exampledb? serverTimezone=UTC&useSSL=false&user=root&password=root"); 
System.out.println("We are successfully connected to the MySQL  server.");
String sql = "SELECT * FROM user";
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery(sql);

while (result.next()){
    int id = result.getInt(1);
   String name = result.getString(2);
    String email = result.getString("email");
    String date = result.getDate(4).toString();
    String output = "User #%d: %s - %s - %s";
    System.out.println(String.format(output, id, name, email,  date));
  }
statement.close();
} catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
     }
   }
 }

 

The output of out code:
We are successfully connected to the MySQL server.
User #1: TestUser – testuser@gmail.com – 2020-04-20

After the execution of our SQL query in MySQL Workbench.

CRUD Operations on DB

Let’s look one more time at the last code example. After the execution of the SQL select query, we are getting data to the ResultSet object. We used different getXXX methods to get a property with a corresponding type. Also, there is a possibility to use columns indexes and names. The last way to get a column value by name showed in our example on the email column. 

Update operation   

To update data in a relational database we use the SQL statement UPDATE. We can update a single row or a group of rows, selected by some condition. We are going to use PreparedStatement object with the setXXX() methods, the same as we did in the case of creation. Let’s update just the user email for our single user TestUser:

package concurrent;

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

public class UpdateExample {

public static void main(String args[]) {

try {

Connection connection= DriverManager.getConnection

("jdbc:mysql://localhost:3306/exampledb? serverTimezone=UTC&useSSL=false&user=root&password=root"); 

System.out.println("We are successfully connected to the MySQL  server.");

String sql = "UPDATE User SET email=? WHERE name=?";

 

PreparedStatement statement = connection.prepareStatement(sql);

statement.setString(1, "newEmail");

statement.setString(2, "TestUser");

 

int rowsUpdated = statement.executeUpdate();

if (rowsUpdated > 0) {

    System.out.println("An existing user was updated  successfully!");

}

statement.close();

} catch (SQLException e) {

            System.out.println("SQLException: " + e.getMessage());

            System.out.println("SQLState: " + e.getSQLState());

            System.out.println("VendorError: " + e.getErrorCode());

    }

  }
  
}

In this case, the index in the setters of the PreparedStatement is the order to insert values into the SQL query. If we had more than one user with the name TestUser, we would have the value of the rowsUpdated variable >1.

The output of our code is:

We are successfully connected to the MySQL server.
An existing user was updated successfully!

Let’s check if the email for our user was really updated in MySQL Workbench:

CRUD Operations on DB

Yes, we did this, the value was really updated.

Delete operation

We can delete data from a relational database with the use of the SQL statement DELETE.

Now we are going to delete our single record from the database and check from code if our User table is empty. With DELETE SQL operation we can delete a group of rows, grouped by some condition, and even all rows from a database table.

package concurrent;

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

import java.sql.Statement;
import java.sql.Date;

public class DeleteExample {

public static void main(String args[]) {

try {

Connection connection= DriverManager.getConnection

("jdbc:mysql://localhost:3306/exampledb?serverTimezone=UTC&useSSL=false&user=root&password=root"); 

System.out.println("We are successfully connected to the MySQL server.");

String sql = "DELETE FROM user WHERE name=?";

 

PreparedStatement statement = connection.prepareStatement(sql);

statement.setString(1, "TestUser");

 

int rowsDeleted = statement.executeUpdate();

if (rowsDeleted > 0) {

    System.out.println("The user was deleted successfully!");

}

statement.close();

sql = "SELECT COUNT(*) AS total FROM user";

Statement st = connection.createStatement();

ResultSet count = st.executeQuery(sql);

if(count.next() && (count.getInt("total") == 0)) {

System.out.println("The User table is empty!");

}

st.close();

} catch (SQLException e) {

            System.out.println("SQLException: " + e.getMessage());

            System.out.println("SQLState: " + e.getSQLState());

            System.out.println("VendorError: " + e.getErrorCode());

}

}

}

 

The output of the application will be:

We are successfully connected to the MySQL server.

The user was deleted successfully!
The User table is empty!

So, we reviewed CRUD operations with the support of the JDBC. Nowadays, there are a lot of frameworks that make our life easier. They can map classes for database schemas, generate all the basic CRUD operations, control transactions. The most popular frameworks for Java are the Java Persistence API, and Spring Data Java Persistence API. 

The main idea is to create simple classes with fields, getters, setters. They are called entities. With the use of annotations, you need to map them to database tables (classes have to correspond tables, fields have to correspond columns). The database relationships between tables are also showing with annotations in entities. All persistence operations exist in the DAO(Data Access Object) layer or Repositories. The basic operations are usually generated by a framework, other you need to add on your own, but it looks simpler than to write just with the support of the JDBC.

Facebook Comments
Tags

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Articles

Back to top button
Close
Close

Get Java Training Online
Course Now at 50%OFF!

Offer valid for 1st 20 seats only, Hurry up!!

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

H2kinfosys will use this information to be in touch with you for providing updates.

Get Python Course
worth 499$ for FREE!

Offer valid for 1st 20 seats only, Hurry up!!

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

H2kinfosys Blog will use the information you provide on this form to be in touch with you and to provide updates and marketing.