Connect with Spring Boot

Configure Spring Boot to Use the SingleStore Dialect

Add Dependencies

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

Hibernate Community Dialects
XML
```
<dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-community-dialects</artifactId>
    <version>7.0.0.Beta5</version>
</dependency>
```
Because the SingleStore dialect is currently unavailable in the Spring-compatible Hibernate version, the hibernate-community-dialects version must be 7.0.0.Beta5 or higher.

SingleStore JDBC driver (replace 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>

Specify Spring Application Properties

Add the following properties to the application.properties file of your project:

spring.datasource.driver-class-name (Required): Specify com.singlestore.jdbc.Driver. Because Spring does not have SingleStore as a predefined source, you must specify this property. The SingleStore dialect is automatically resolved from the hibernate-community-dialects dependency.
spring.datasource.url (Required): Specify the connection string for your SingleStore database in the following format:

jdbc:singlestore://<hostname_or_IP_address>:3306/<database_name>?_connector_name=SingleStore Spring Connector
spring.datasource.username: Specify the username of the SingleStore database user.
spring.datasource.password: Specify the password for the SingleStore database user.
Optional property:
- spring.jpa.properties.hibernate.dialect.singlestore.for_update_lock_enabled: 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 Spring Boot application. This example uses a table named song with the following structure and rows:

SQL

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 | Album_JSON                                      | singer        | songName           |
+--------+-------------------------------------------------+---------------+--------------------+
|      1 | {"album":"Endless Skies","release_year":2025}   | The Wanderers | Golden Horizon     |
|      2 | {"album":"Bright Future","release_year":2025}   | John Doe      | Sunny Days         |
+--------+-------------------------------------------------+---------------+--------------------+

Create a Spring project. This example creates a Spring project using Spring Initializr with the following options:

Project: Maven
Language: Java
Spring Boot: 3.4.6
Group: com.singlestore
Artifact: SpringExample
Packaging: Jar
Java: 21

This project has the following directory structure:

pom.xml
src/main/
└── java/com/singlestore/SpringExample
    ├── SpringExampleApplication.java
    ├── model
    │   └── Song.java
    ├── repository
    │   └── SongRepository.java
    └── controller
        └── SongController.java
└── resources/
    ├── application.properties

Add the SingleStore JDBC driver and Hibernate Community dialects dependencies to 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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>3.4.6</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.singlestore</groupId>
	<artifactId>SpringExample</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>SpringExample</name>
	<description>Demo project for connecting to SingleStore using Spring Boot</description>
	<url/>
	<licenses>
		<license/>
	</licenses>
	<developers>
		<developer/>
	</developers>
	<scm>
		<connection/>
		<developerConnection/>
		<tag/>
		<url/>
	</scm>
	<properties>
		<java.version>21</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
            		<groupId>org.springframework.boot</groupId>
            		<artifactId>spring-boot-starter-data-jpa</artifactId>
        	</dependency>
        	<dependency>
            		<groupId>org.springframework.boot</groupId>
            		<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!-- SingleStore JDBC driver -->
        	<dependency>
            		<groupId>com.singlestore</groupId>
            		<artifactId>singlestore-jdbc-client</artifactId>
            		<version>1.2.8</version>
            		<scope>runtime</scope>
		</dependency>
         	<!-- Hibernate community dialects -->
       	        <dependency>
          		<groupId>org.hibernate.orm</groupId>
          		<artifactId>hibernate-community-dialects</artifactId>
           		<version>7.0.0.Beta5</version>
        	</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

Update/add the required properties to the application.properties file of your project. This example uses the following properties:

spring.application.name=SpringExample
spring.datasource.url=jdbc:singlestore://svchost:3306/dbTest?_connector_name=SingleStore Spring Connector
spring.datasource.driver-class-name=com.singlestore.jdbc.Driver

spring.datasource.username=s2user
spring.datasource.password=pa55w0rd
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Add the following code to SpringExampleApplication.java:

Java

package com.singlestore.SpringExample;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringExampleApplication {

	public static void main(String[] args) {
		SpringApplication.run(SpringExampleApplication.class, args);
	}
}

Create a Plain Old Java Object (POJO) class named Song (model/Song.java).

Java

package com.singlestore.SpringExample.model;

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

Add a Spring data repository (repository/SongRepository.java):

Java

package com.singlestore.SpringExample.repository;

import java.util.List;

import com.singlestore.SpringExample.model.Song;
import org.springframework.data.repository.CrudRepository;

public interface SongRepository extends CrudRepository<Song, Integer> {
	List<Song> findBySongNameContains(String name);
}

Add a REST controller (controller/SongController.java):

Java

package com.singlestore.SpringExample.controller;

import java.util.List;

import com.singlestore.SpringExample.model.Song;
import com.singlestore.SpringExample.repository.SongRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequestMapping("/songs")
public class SongController {

	@Autowired
	private SongRepository songRepository;

	@PostMapping("/add")
	public String createSong(@RequestBody Song song) {
		songRepository.save( song );
		return "Saved";
	}

	@RequestMapping("/all")
	public @ResponseBody Iterable<Song> getSongs() {
		return songRepository.findAll();
	}

	@RequestMapping("/byid")
	public @ResponseBody Iterable<Song> getSongsById(
			@RequestParam(name = "id") Integer id) {
		if ( id != null ) {
			return songRepository.findAllById( List.of( id ) );
		}
		else {
			return songRepository.findAll();
		}
	}

	@RequestMapping("/byname")
	public @ResponseBody Iterable<Song> getSongsByName(@RequestParam(name = "name") String name) {
		if ( StringUtils.hasText( name ) ) {
			return songRepository.findBySongNameContains( name );
		}
		else {
			return songRepository.findAll();
		}
	}

	@RequestMapping("/count")
	public long count() {
		return songRepository.count();
	}

}

This controller creates the following endpoints:

Endpoint	Method	Description
`/add`	`POST`	Adds a new row to the `song` table.
`/all`	`GET`	Returns all the rows in the `song` table.
`/byid`	`GET`	Retrieves a row by `songID`.
`/byname`	`GET`	Retrieves a row by `songName`.
`/count`	`GET`	Returns the total number of rows in the `song` table.

Build the application. Run the following command from the root of your project:
Shell
```
mvn clean install
```
Run the application. Run the following command from the root of your project:
Shell
```
mvn spring-boot:run
```

Once the API is running, run the following command to get a list of all the rows in the song table:

Shell

curl http://svchost:8080/songs/all

-- Output formatted for clarity. --
[{
    "id": 1,
    "songName": "Golden Horizon",
    "artist": "The Wanderers",
    "jsonAttributes": {
      "album": "Endless Skies",
      "release_year": 2025
    }
  },
  {
    "id": 2,
    "songName": "Sunny Days",
    "artist": "John Doe",
    "jsonAttributes": {
      "album": "Bright Future",
      "release_year": 2025
    }
}]

To return the total number of rows in the song table, run the following command:

Shell

curl http://svchost:8080/songs/count

You can use the other endpoints to perform the respective operations.

Connect with Spring Boot

On this page

Configure Spring Boot to Use the SingleStore Dialect

Add Dependencies

Specify Spring Application Properties

Example

Was this article helpful?

On this page

Was this article helpful?