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:

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, "[email protected]");

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:

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 – [email protected] – 2020-04-20

After the execution of our SQL query in MySQL Workbench.

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:

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.