Hibernate SingleStore Dialect

Configure Hibernate to Use the SingleStore Dialect

To configure Hibernate using Maven,

Add the following dependencies to the pom.xml file of your project:

Hibernate Community Dialects

XML

<dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-community-dialects</artifactId>
    <version>7.0.0.Beta5</version>
</dependency>

SingleStore JDBC driver (update x.x.x with the latest driver version)

XML

<dependency>
    <groupId>com.singlestore</groupId>
    <artifactId>singlestore-jdbc-client</artifactId>
    <version>x.x.x</version>
</dependency>

Refer to The SingleStore JDBC Driver for more information.

Update the Hibernate configuration file hibernate.cfg.xml.

XML

<?xml version = "1.0" encoding = "utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.url">jdbc:singlestore://<hostname>:<port>/<database>?connectionAttributes=_connector_name:hibernate</property>
        <property name="hibernate.connection.username"><username></property>
        <property name="hibernate.connection.password"><password></property>
        <property name="hibernate.show_sql">true</property>
        <!-- Optional: Auto-generate schema -->
        <property name="hibernate.hbm2ddl.auto">update</property>
    </session-factory>
</hibernate-configuration>

Update the following values in the Hibernate configuration:

<hostname>: Hostname or IP address of the SingleStore deployment.
<port>: Port of the SingleStore deployment.
<username>: Name of the SingleStore database user with which to access the database.
<password>: Password for the SingleStore database user.

Additional Configuration Properties

Property	Default Value	Description
`hibernate.dialect.singlestore.for_update_lock_enabled`	`false`	When enabled, uses the `FOR UPDATE` clause to acquire row locks. Refer to SELECT … FOR UPDATE for more information.

Example

The following example connects to a SingleStore deployment using the Hibernate SingleStore dialect from a Hibernate application and performs insert and update operations.

Create a Maven project with the following directory structure:

HibernateExample
├── pom.xml
└── src
    └── main
        └── java
            └── org
                └── example
        └── resources
            └── hibernate.cfg.xml

Add the following dependencies in the pom.xml file of your project:

XML

<?xml version="1.0" encoding="UTF-8"?>
<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>TestHibernateSingleStore</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>11</maven.compiler.source>
        <maven.compiler.target>11</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <hibernate.version>6.6.0.Final</hibernate.version>
        <hibernate.community.dialect.version>7.0.0.Beta5</hibernate.community.dialect.version>
        <singlestore.jdbc.version>1.2.7</singlestore.jdbc.version>
    </properties>

    <dependencies>
        <!-- Hibernate core -->
        <dependency>
            <groupId>org.hibernate.orm</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate.version}</version>
        </dependency>

        <!-- SingleStore JDBC driver -->
        <dependency>
            <groupId>com.singlestore</groupId>
            <artifactId>singlestore-jdbc-client</artifactId>
            <version>${singlestore.jdbc.version}</version>
        </dependency>

        <!-- Hibernate Community Dialects snapshot -->
        <!-- https://mvnrepository.com/artifact/org.hibernate.orm/hibernate-community-dialects -->
        <dependency>
            <groupId>org.hibernate.orm</groupId>
            <artifactId>hibernate-community-dialects</artifactId>
            <version>${hibernate.community.dialect.version}</version>
        </dependency>
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.16.1</version> 
        </dependency>
    </dependencies>
</project>

Create a Plain Old Java Object (POJO) class named Song (Song.java) in the example directory.

Java

package org.example;

import org.hibernate.annotations.JdbcTypeCode;
import org.hibernate.type.SqlTypes;
import java.util.Map;
import java.util.HashMap;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "song")
public class Song
{
    @Id @Column(name = "songId") 
    private int id;

    @Column(name = "songName") 
    private String songName;

    @Column(name = "singer") 
    private String artist;

    //Hibernate 6.0 and later versions provide an annotation named @JdbcTypeCode
    @Column(name = "Album_JSON", columnDefinition = "JSON")
    @JdbcTypeCode(SqlTypes.JSON)
    private Map<String, Object> jsonAttributes = new HashMap<>();

    public int getId() {return id;}

    public void setId(int id) {this.id = id;}

    public String getSongName() {return songName;}

    public void setSongName(String songName)
    {
        this.songName = songName;
    }

    public String getArtist() {return artist;}

    public void setArtist(String artist)
    {
        this.artist = artist;
    }
    
    public Map<String, Object> getJsonAttributes() {
		return jsonAttributes;
	}

	public void setJsonAttributes(Map<String, Object> jsonAttributes) {
		this.jsonAttributes = jsonAttributes;
	}
}

Update the Hibernate configuration file hibernate.cfg.xml:

XML

<?xml version = "1.0" encoding = "utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <!-- Set URL -->
        <property name="hibernate.connection.url">jdbc:singlestore://svchost:3306/dbEx?_connector_name=hibernate</property>
        <!-- Set User Name -->
        <property name="hibernate.connection.username">s2user</property>
        <!-- Set Password -->
        <property name="hibernate.connection.password">passkey</property>

        <property name="hibernate.show_sql">true</property>

        <!-- Use 'FOR UPDATE' for table lock -->
        <property name="hibernate.dialect.singlestore.for_update_lock_enabled">true</property>
        <!-- Optional: Auto-generate schema -->
        <property name="hibernate.hbm2ddl.auto">create</property>
    </session-factory>
</hibernate-configuration>

Create a class named App (App.java) in the example directory to start the Hibernate application.

Java

package org.example;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.LockMode;
import org.hibernate.LockOptions;
import com.fasterxml.jackson.databind.ObjectMapper;
import java.util.HashMap;
import java.util.Map;

public class App {
    public static void main(String[] args) {
        Configuration configuration = new Configuration();
        configuration.configure("hibernate.cfg.xml");
        configuration.addAnnotatedClass(Song.class);

        try (SessionFactory sessionFactory = configuration.buildSessionFactory();
             Session session = sessionFactory.openSession()) {

            // Creating a new Song
            Song song1 = new Song();
            song1.setId(1);
            song1.setSongName("Broken Angel");
            song1.setArtist("No Name");
	    
	    String json = "{\"album\":\"Konvicted\",\"release_year\":2006}";

	    ObjectMapper mapper = new ObjectMapper();
            Map<String, Object> jsonMap = mapper.readValue(json, Map.class);
	    song1.setJsonAttributes(jsonMap);

            // Inserting the song into the database
            Transaction transaction = session.beginTransaction();
            session.persist(song1);
            transaction.commit();
            System.out.println("Song saved successfully!");

            // Update with pessimistic lock
            transaction = session.beginTransaction();
            session.lock(song1, new LockOptions(LockMode.PESSIMISTIC_READ));
            song1.setArtist("Akon");
            session.update(song1);
            transaction.commit();
            System.out.println("Song updated successfully!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Run the application.

Shell

mvn clean compile
mvn exec:java -Dexec.mainClass="org.example.App"

Hibernate: drop table if exists song
Hibernate: create table song (songId integer not null, Album_JSON JSON, singer varchar(255), songName varchar(255), primary key (songId))
Hibernate: insert into song (singer,Album_JSON,songName,songId) values (?,?,?,?)
Song saved successfully!
Hibernate: select songId from song where songId=? for update
Hibernate: update song set singer=?,Album_JSON=?,songName=? where songId=?
Song updated successfully!

To verify that the song table is created correctly, a new row is added, and then successfully updated, log in to your SingleStore deployment and run the following commands:

SQL

USE dbEx;
DESC song;

+------------+--------------+------+------+---------+-------+
| Field      | Type         | Null | Key  | Default | Extra |
+------------+--------------+------+------+---------+-------+
| songId     | int(11)      | NO   | PRI  | NULL    |       |
| Album_JSON | JSON         | YES  |      | NULL    |       |
| singer     | varchar(255) | YES  |      | NULL    |       |
| songName   | varchar(255) | YES  |      | NULL    |       |
+------------+--------------+------+------+---------+-------+

SQL

SELECT * FROM song;

+--------+-------------------------------------------+--------+--------------+
| songId | json_attributes                           | singer | songName     |
+--------+-------------------------------------------+--------+--------------+
|      1 | {"album":"Konvicted","release_year":2006} | Akon   | Broken Angel |
+--------+-------------------------------------------+--------+--------------+

The table is created with the specified structure, a row is inserted in the table, and the value in the singer column is updated from No Name to Akon.

Limitations

SingleStore does not support the following in addition to Unsupported MySQL Features:

Unique keys: Hibernate requires every entity table to have a primary key, hence unique keys are ignored. Therefore, annotations such as @UniqueConstraint and @Column(unique = true) are disregarded.
Columnstore tables do not allow ENUM types as unique keys. Either set the table type to ROWSTORE or change the column type.
SingleStore returns SELECT query results in a random order, which may affect the predictability of query outcomes.
Primary key updates: Attempts to update a primary key fail because every primary key in SingleStore is also a unique key and shard key.
FOR UPDATE clause for distributed joins
Zoned timestamps
ANY or ALL clauses
Sub-selects referencing outer table fields
ON DUPLICATE KEY references to different tables
Case-insensitive LIKE clause

Hibernate SingleStore Dialect

On this page

Configure Hibernate to Use the SingleStore Dialect

Additional Configuration Properties

Example

Limitations

Was this article helpful?

On this page

Was this article helpful?