Quick tip: Using Hibernate with SingleStoreDB

Abstract

Object Relational Mapping (ORM) allows access to Relational DBMSs from Object-Oriented Programming Languages (OOPLs). A wide range of open-source and commercial ORMs are available today and support many different OOPLs. In this short article, we'll use SingleStoreDB with one popular open-source ORM called Hibernate.

The Java code files used in this article are available on GitHub.

Introduction

In this short article, we'll perform Create, Read, Update and Delete (CRUD) operations on some tick data. We'll write several packages in Java. We'll use Hibernate to manage our Java objects with SingleStoreDB.

Create a SingleStoreDB Cloud account

A previous article showed the steps required to create a free SingleStoreDB Cloud account. We'll use Hibernate Demo Group as our Workspace Group Name and hibernate-demo as our Workspace Name. We'll make a note of our password and host name.

Create a Maven project

For quick testing, we'll use maven and build and run our code from the command line.

pom.xml

The pom.xml file is very straightforward with details of the Java version, the two main dependencies and that we want to build a single jar file with all the dependencies:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.s2</groupId>
  <artifactId>s2-app</artifactId>
  <version>1.0-SNAPSHOT</version>
  <properties>
    <maven.compiler.target>1.8</maven.compiler.target>
    <maven.compiler.source>1.8</maven.compiler.source>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>
  <dependencies>
    <!-- https://mvnrepository.com/artifact/com.mysql/mysql-connector-j -->
    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <version>8.0.31</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-core</artifactId>
      <version>5.6.12.Final</version>
    </dependency>
  </dependencies>
  <build>
    <plugins>
      <plugin>
        <artifactId>maven-assembly-plugin</artifactId>
        <configuration>
          <archive>
            <manifest>
              <mainClass>fully.qualified.MainClass</mainClass>
            </manifest>
          </archive>
          <descriptorRefs>
            <descriptorRef>jar-with-dependencies</descriptorRef>
          </descriptorRefs>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

Tick class

Our Tick class consists of four fields:

1. An auto-generated unique identifier.
2. The date in the format YYYY-MM-DD.
3. The stock symbol.
4. The price.

TickDAO class

We'll use the Data Access Object (DAO) pattern to provide the CRUD operations. These will use transactions to ensure that database operations are committed to the SingleStoreDB database.

HibernateUtil class

Our code will provide the connection details. We need to plug in a few values, as follows:

settings.put(AvailableSettings.DRIVER, "com.mysql.cj.jdbc.Driver");

settings.put(AvailableSettings.URL, "jdbc:mysql://<host>:3306/timeseries_db?createDatabaseIfNotExist=true");

settings.put(AvailableSettings.USER, "admin");

settings.put(AvailableSettings.PASS, "<password>");

We'll replace the <host> and <password> with the values from our SingleStoreDB Cloud account.

If the database timeseries_db does not exist, it will be created.

App class

In our application, we'll perform some simple CRUD operations:

• Create a few tick objects and save them to SingleStoreDB.
• Retrieve all the data from SingleStoreDB to verify that the tick objects were correctly saved.
• Update one stock symbol and its price and store it back in SingleStoreDB.
• Retrieve all the data from SingleStoreDB to confirm that one tick object was updated.
• Delete one tick from SingleStoreDB.
• Retrieve all the data from SingleStoreDB to verify that one tick object was deleted.

We'll use some tick data from a previous article.

Build the code

We can build the code from the command line using maven, as follows:

mvn clean compile assembly:single

This will create a jar file with all the dependencies.

Run the code

We'll run our code, as follows:

java -cp target/s2-app-1.0-SNAPSHOT-jar-with-dependencies.jar com.s2.hibernate.util.App

In the command line window, we should see output that shows three tick objects were created:

...
Id = 1 Date = 2013-02-08 Symbol = AAPL Price = 67.8542
Id = 3 Date = 2013-02-08 Symbol = ADBE Price = 39.12
Id = 2 Date = 2013-02-08 Symbol = ABC Price = 46.89
...

one tick object was updated (ADBE was replaced with ABBV):

...
Id = 2 Date = 2013-02-08 Symbol = ABC Price = 46.89
Id = 1 Date = 2013-02-08 Symbol = AAPL Price = 67.8542
Id = 3 Date = 2013-02-08 Symbol = ABBV Price = 36.25
...

and one tick object was deleted (ABC):

...
Id = 1 Date = 2013-02-08 Symbol = AAPL Price = 67.8542
Id = 3 Date = 2013-02-08 Symbol = ABBV Price = 36.25
...

Summary

In this short article, we have built and tested a Java application using Hibernate to perform some simple CRUD operations against SingleStoreDB.

Acknowledgements

I am grateful to Ramesh Fadatare for his video on Hibernate CRUD Example and the GitHub repository for inspiring the code example in this article.