# Hibernate SingleStore Dialect

Hibernate is an object-relational mapping (ORM) framework for Java, which simplifies database interactions by mapping Java objects to database tables. The Hibernate SingleStore dialect generates SQL, DDL, and DML statements using standard Hibernate and Java Persistence annotations.

> **📝 Note**: The Hibernate SingleStore dialect is only available in Hibernate version 7.0.0.Beta5+.

## Configure Hibernate to Use the SingleStore Dialect

To configure Hibernate using Maven,

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

   1. Hibernate Community Dialects
      ```xml
      <dependency>
          <groupId>org.hibernate.orm</groupId>
          <artifactId>hibernate-community-dialects</artifactId>
          <version>7.0.0.Beta5</version>
      </dependency>
      ```

   2. 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](https://docs.singlestore.com/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-java-jdbc/the-singlestore-jdbc-driver.md) for more information.

2. 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/select/#select-for-update.md)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.

1. Create a Maven project with the following directory structure:
   ```
   HibernateExample
   ├── pom.xml
   └── src
       └── main
           └── java
               └── org
                   └── example
           └── resources
               └── hibernate.cfg.xml
   ```

2. 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>
   ```

3. 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;
   	}
   }
   ```

4. 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>
   ```

5. 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();
           }
       }
   }
   ```

6. Run the application.
   ```shell
   mvn clean compile
   mvn exec:java -Dexec.mainClass="org.example.App"

   ```
   ```output

   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!
   ```

7. 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;

   ```
   ```output

   +------------+--------------+------+------+---------+-------+
   | 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;

   ```
   ```output

   +--------+-------------------------------------------+--------+--------------+
   | 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](https://docs.singlestore.com/db/v9.1/connect-to-singlestore/connect-with-mysql/connect-with-mysql-client/unsupported-mysql-features.md):

* 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

***

Modified at: September 26, 2025

Source: [/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-java-jdbc/hibernate-singlestore-dialect/](https://docs.singlestore.com/db/v9.1/developer-resources/connect-with-application-development-tools/connect-with-java-jdbc/hibernate-singlestore-dialect/)

(An index of the documentation is available at /llms.txt)