Quick tip: Using Hibernate with SingleStoreDB
Tags: singlestoredb, hibernate, java, orm
Published: 2022-10-23 23:30:06

## 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](https://github.com/VeryFatBoy/s2-hibernate).

## 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](https://dev.to/veryfatboy/quick-tip-using-dbt-with-singlestoredb-161g) 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:

```xml
<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:

```java
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](https://medium.com/@VeryFatBoy/using-singlestore-as-a-time-series-database-6517a1a36a4e).

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

```shell
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:

```shell
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](https://www.linkedin.com/in/ramesh-fadatare) for his video on [Hibernate CRUD Example](https://www.javaguides.net/2019/02/hibernate-crud-example.html) and the [GitHub](https://github.com/RameshMF/Hibernate-ORM-Tutorials/tree/4df199ddc5b167f72f3a52b0eaea689c546135c7/hibernate-tutorial/hibernate-crud-example) repository for inspiring the code example in this article.