Spring Data YugabyteDB

Spring Data modules are used for accessing databases and performing various tasks via Java APIs, therefore eliminating the need to learn a database-specific query language.

Spring Data YugabyteDB (SDYB) modules provide support for YSQL APIs and enable you to build cloud-native applications.

Overview

The following are some of the features included in Spring Data YugabyteDB (YSQL):

• Spring Data YsqlTemplate and YsqlRepository for querying YugabyteDB
• @EnableYsqlRepostiory annotation for enabling YsqlRepository
• Yugabyte Distributed SQL transaction manager
• Spring Boot starter support for YugabyteDB
• Cluster awareness achieved via elimination of load balancer from SQL
• Topology awareness for developing geographically-distributed applications
• Partition awareness for achieving row-level geo-partitioning

For more information, demonstrations, and contribution guidelines, see Spring Data YugabyteDB GitHub project.

Project dependencies

The project definition includes the following dependencies:

<dependency>
    <groupId>com.yugabyte</groupId>
    <artifactId>spring-data-yugabytedb-ysql</artifactId>
    <version>2.3.0-RC1.b5</version>
</dependency>
<dependency>
  <groupId>com.yugabyte</groupId>
  <artifactId>jdbc-yugabytedb</artifactId>
  <version>42.3.5-yb-5</version>
</dependency>

Examples

Spring Data YugabyteDB application

The following tutorial illustrates how to build a Java application with Spring Data YugabyteDB and use the YSQL API to connect to and interact with YugabyteDB. The application connects to a YugabyteDB cluster and performs basic SQL operations, including creating an employees table, adding an employee, and querying the database.

Prerequisites

This tutorial assumes that:

• YugabyteDB is up and running. Download and install YugabyteDB by following the steps in Quick start.
• Java Development Kit (JDK) 1.8, or later, is installed. JDK installers for Linux and macOS can be downloaded from OpenJDK, AdoptOpenJDK, or Azul Systems.
• Apache Maven 3.3 or later, is installed.

Create a project using Spring Boot

The Spring Boot project provides the Spring Initializr utility for generating dependencies for Spring Boot applications.

1. Navigate to Spring Initializr. This service pulls in all the dependencies you need for an application and does most of the setup for you.

2. Choose Maven and Java programming language.

3. Click Dependencies and select Spring Web and PostgreSQL Driver. Click Generate.

4. Download the resulting ZIP file, which is an archive of a Spring Boot application that is configured with your choices.

5. Add the following dependencies to pom.xml of the Spring Boot application:

   <dependency>
     <groupId>com.yugabyte</groupId>
     <artifactId>spring-data-yugabytedb-ysql</artifactId>
     <version>2.3.0</version>
   </dependency>
   <dependency>
     <groupId>com.zaxxer</groupId>
     <artifactId>HikariCP</artifactId>
   </dependency>
   

Create the sample Spring Data YugabyteDB application

1. Create new files Employee.java, EmployeeRepository.java, and YsqlConfig.java in the base package.

   package com.yugabyte.sdyb.sample;
   
   import org.springframework.data.annotation.Id;
   import org.springframework.data.annotation.Transient;
   import org.springframework.data.domain.Persistable;
   import org.springframework.data.relational.core.mapping.Table;
   
   @Table(value = "employee")
   public class Employee implements Persistable<String> {
   
   @Id
   private String id;
   private String name;
   private String email;
   
   @Transient
   private Boolean isInsert = true;
   
   // Add Empty Constructor, Constructor, and Getters/Setters
   
   public Employee() {}
   
   public Employee(String id, String name, String email) {
     super();
     this.id = id;
     this.name = name;
     this.email = email;
   }
   
   @Override
   public String getId() {
     return id;
   }
   
   public void setId(String id) {
     this.id = id;
   }
   
   public String getName() {
     return name;
   }
   
   public void setName(String name) {
     this.name = name;
   }
   
   public String getEmail() {
     return email;
   }
   
   public void setEmail(String email) {
     this.email = email;
   }
   
   @Override
   public boolean isNew() {
     return isInsert;
   }
   
   }
   

   package com.yugabyte.sdyb.sample;
   
   import org.springframework.stereotype.Repository;
   
   import com.yugabyte.data.jdbc.repository.YsqlRepository;
   
   @Repository
   public interface EmployeeRepository extends YsqlRepository<Employee, Integer> {
   
   Employee findByEmail(final String email);
   
   }
   

   package com.yugabyte.sdyb.sample;
   
   import javax.sql.DataSource;
   
   import org.springframework.context.annotation.Bean;
   import org.springframework.context.annotation.Configuration;
   import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;
   import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
   import org.springframework.transaction.TransactionManager;
   
   import com.yugabyte.data.jdbc.datasource.YugabyteTransactionManager;
   import com.yugabyte.data.jdbc.repository.config.AbstractYugabyteJdbcConfiguration;
   import com.yugabyte.data.jdbc.repository.config.EnableYsqlRepositories;
   
   @Configuration
   @EnableYsqlRepositories(basePackageClasses = EmployeeRepository.class)
   public class YsqlConfig extends AbstractYugabyteJdbcConfiguration {
   
       @Bean
       NamedParameterJdbcOperations namedParameterJdbcOperations(DataSource dataSource) {
           return new NamedParameterJdbcTemplate(dataSource);
       }
   
       @Bean
       TransactionManager transactionManager(DataSource dataSource) {
           return new YugabyteTransactionManager(dataSource);
       }
   
   }
   

2. A number of options can be customized in the properties file located at src/main/resources/application.properties. Given YSQL's compatibility with the PostgreSQL language, the spring.jpa.database property is set to POSTGRESQL and the spring.datasource.url is set to the YSQL JDBC URL: jdbc:postgresql://localhost:5433/yugabyte.

   # ---------------------
   # JPA/Hibernate config.
   spring.jpa.database=POSTGRESQL
   
   # The SQL dialect makes Hibernate generate better SQL for the chosen database.
   spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
   
   # Hibernate ddl auto (create, create-drop, validate, update).
   spring.jpa.show-sql=true
   spring.jpa.generate-ddl=true
   spring.jpa.hibernate.ddl-auto=create
   
   # -------------------
   # Data-source config.
   spring.sql.init.platform=postgres
   spring.datasource.url=jdbc:postgresql://localhost:5433/yugabyte
   spring.datasource.username=yugabyte
   spring.datasource.password=
   
   # HikariCP config (pool size, default isolation level).
   spring.datasource.type=com.zaxxer.hikari.HikariDataSource
   spring.datasource.hikari.transactionIsolation=TRANSACTION_SERIALIZABLE
   

3. Create a Spring Boot Application runner to perform reads and writes against the YugabyteDB Cluster.

   package com.yugabyte.sdyb.sample;
   
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.boot.CommandLineRunner;
   import org.springframework.boot.SpringApplication;
   import org.springframework.boot.autoconfigure.SpringBootApplication;
   import org.springframework.jdbc.core.JdbcTemplate;
   
   @SpringBootApplication
   public class DemoApplication implements CommandLineRunner {
   
   @Autowired
   JdbcTemplate jdbcTemplate;
   
   @Autowired
   EmployeeRepository customerRepository;
   
   public static void main(String[] args) {
     SpringApplication.run(DemoApplication.class, args);
   }
   
   @Override
   public void run(String... args) throws Exception {
   
     System.out.println("Connected to the YugabyteDB server successfully.");
     jdbcTemplate.execute("DROP TABLE IF EXISTS employee");
     jdbcTemplate.execute("CREATE TABLE IF NOT EXISTS employee" +
                 "  (id text primary key, name varchar, email varchar)");
     System.out.println("Created table employee");
   
   
     Employee customer = new Employee("sl1",
                   "User One",
                   "user@one.com");
   
     customerRepository.save(customer);
   
     Employee customerFromDB = null;
     customerFromDB = customerRepository.findByEmail("user@one.com");
   
     System.out.println(String.format("Query returned: name = %s, email = %s",
       customerFromDB.getName(), customerFromDB.getEmail()));
   
     }
   }
   

Run your program

Run your program using the following command:

$ ./mvnw spring-boot:run

You should see the following output:

2022-04-07 20:25:01.210  INFO 12097 --- [main] com.yugabyte.sdyb.demo.DemoApplication   : Started DemoApplication in 27.09 seconds (JVM running for 27.35)
Connected to the YugabyteDB server successfully.
Created table employee
Query returned: name = User One, email = user@one.com

Shopping cart application

The following example demonstrates how to create a basic shopping cart using Spring data YugabyteDB:

public interface ShoppingCartRepository extends YsqlRepository<ShoppingCart, String> {

  ShoppingCart findById(String id);
  List<ShoppingCart> findByUserId(String userId);

}

@Service
public class CartService {

  private final ShoppingCartRepository repository;

  public CartService(CartService repository) {
    this.repository = repository;
  }

  public void doWork() {
    repository.deleteAll();

    ShoppingCart myShoppingCart = new ShoppingCart();
    myShoppingCart.set("cart1")
    myShoppingCart.setUserId("u1001");
    myShoppingCart.setProductId("asin1001");
    myShoppingCart.setQuantity(1);

    repository.save(myShoppingCart);

    ShoppingCart savedCart = repository.findById("cart1");

    List<ShoppingCart> productsInCart = repository.findByUserId("u1001");
    repository.count();
  }

}

@Configuration
@EnableYsqlRepositories
public class YsqlConfig extends AbstractYugabyteJdbcConfiguration {

  @Bean
  DataSource dataSource() {
    String hostName = "127.0.0.1";
    String port = "5433";

    Properties poolProperties = new Properties();
    poolProperties.setProperty("dataSourceClassName",
    "com.yugabyte.ysql.YBClusterAwareDataSource");
    poolProperties.setProperty("dataSource.serverName", hostName);
    poolProperties.setProperty("dataSource.portNumber", port);
    poolProperties.setProperty("dataSource.user", "yugabyte");
    poolProperties.setProperty("dataSource.password", "");
    poolProperties.setProperty("dataSource.loadBalance", "true");
    poolProperties.setProperty("dataSource.additionalEndpoints",
    "127.0.0.2:5433,127.0.0.3:5433");

    HikariConfig hikariConfig = new HikariConfig(poolProperties);
    DataSource ybClusterAwareDataSource = new HikariDataSource(hikariConfig);
    return ybClusterAwareDataSource;
  }

  @Bean
  JdbcTemplate jdbcTemplate(@Autowired DataSource dataSource) {

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    return jdbcTemplate;
  }

  @Bean
  NamedParameterJdbcOperations namedParameterJdbcOperations(DataSource dataSource) {
    return new NamedParameterJdbcTemplate(dataSource);
  }

  @Bean
  TransactionManager transactionManager(DataSource dataSource) {
    return new YugabyteTransactionManager(dataSource);
  }

}

For additional information and examples, refer to SDYB example.

Compatibility matrix