Architecture blog post by Erez

Connect Your Spring Boot Application to Regatta

Eyal Lotan

Head of Regatta Connect

In this tutorial, we’ll show you just how simple it is to deploy a Spring Boot application that interacts with Regatta using JDBC. By following a few steps, you’ll have a working backend capable of handling customers and purchases with a powerful relational database.

At Regatta, we believe in making database integration seamless for developers. Whether you’re building a new application or migrating an existing one, connecting your Spring Boot backend to Regatta Database is quick and effortless. With support for JDBC and Hibernate (JPA), you can easily integrate Regatta into your application and start managing data efficiently.

Set Up Your Regatta Database

Deploying a Regatta Database is easy and can be done with just a click of a button. In this tutorial, we will use Regatta Cloud, our fully managed database solution. Regatta Cloud provides an elastic, scalable, and hassle-free way to deploy Regatta clusters. You can also opt for an on-premises deployment to match your specific needs.

  • Choose a descriptive name and select the cluster type:
  • Click Confirm and wait for the cluster to start:
  • Once your cluster is deployed, click on its name in the Clusters menu to view its details, including connectivity information:

Install JDK

Download and install a Java Development Kit. In this tutorial, we use Oracle JDK 17.0.10 LTS.

Install Maven

The example application used in this tutorial uses Maven to manage application dependencies.

Get The Application Code

git clone https://github.com/Regatta-Dev/spring-boot-app.git

Add Dependencies

The Regatta Spring Boot app will be available under the spring-boot-app root directory.

Your Spring Boot project requires the Regatta JDBC Driver to communicate with the database. Navigate to the jdbc_app directory, and add the following dependency to your pom.xml file:

<dependencies>
    <!-- Regatta JDBC Driver -->
    <dependency>
        <groupId>dev.regatta</groupId>
        <artifactId>regatta-jdbc</artifactId>
        <version>1.4.0</version>
    </dependency>
</dependencies>

Note: If you’re using Hibernate (JPA), also include the regatta-hibernate dependency.

Configure Your Application

Navigate to jdbc_app/src/main/resources and add the following configurations in your application.properties file to connect to your Regatta database:

# Database Configuration
spring.datasource.url=jdbc:regatta:<CLUSTER_IP>:<CLUSTER_PORT>
spring.datasource.username=<YOUR_USERNAME>
spring.datasource.password=<YOUR_PASSWORD>
spring.datasource.driver-class-name=dev.regatta.jdbc1.Driver

Build and Run the Application

Navigate to your project directory and build it using Maven:

mvn clean install

Once the build is successful, start the application:

mvn spring-boot:run

You should see the following output:

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::                (v3.1.0)
 
2025-03-01T14:36:51.741+02:00  INFO 27868 --- [           main] dev.regatta.jdbc_springboot.App          : Starting App using Java 17.0.10 with PID 27868 (/home/eyallotan/spring-boot-app/jdbc_app/target/classes started by eyallotan in /home/eyallotan/spring-boot-app/jdbc_app)
2025-03-01T14:36:51.743+02:00  INFO 27868 --- [           main] dev.regatta.jdbc_springboot.App          : No active profile set, falling back to 1 default profile: "default"
2025-03-01T14:36:52.272+02:00  INFO 27868 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2025-03-01T14:36:52.278+02:00  INFO 27868 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2025-03-01T14:36:52.278+02:00  INFO 27868 --- [           main] o.apache.catalina.core.StandardEngine    : Starting Servlet engine: [Apache Tomcat/10.1.8]
2025-03-01T14:36:52.350+02:00  INFO 27868 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2025-03-01T14:36:52.351+02:00  INFO 27868 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 578 ms
2025-03-01T14:36:52.592+02:00  INFO 27868 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ""
2025-03-01T14:36:52.596+02:00  INFO 27868 --- [           main] d.r.j.service.DatabaseSetupService       : Starting table creation...
2025-03-01T14:36:52.599+02:00  INFO 27868 --- [           main] dev.regatta.jdbc1.RegConnection          : Creating connection to the database with user admin and url 34.73.76.233:8850
2025-03-01T14:36:53.616+02:00  INFO 27868 --- [           main] dev.regatta.jdbc1.RegConnection          : Database connection created
2025-03-01T14:36:53.766+02:00  INFO 27868 --- [           main] dev.regatta.jdbc1.RegResultSet           : ResultSet closed
2025-03-01T14:36:53.766+02:00  INFO 27868 --- [           main] dev.regatta.jdbc1.RegStatement           : Statement closed
2025-03-01T14:36:54.153+02:00  INFO 27868 --- [           main] dev.regatta.jdbc1.RegStatement           : Statement closed
2025-03-01T14:36:54.988+02:00  INFO 27868 --- [           main] dev.regatta.jdbc1.RegConnection          : Connection closed
2025-03-01T14:36:54.993+02:00  INFO 27868 --- [           main] dev.regatta.jdbc_springboot.App          : Started App in 3.443 seconds (process running for 3.591)

Your Spring Boot app is now up and running, connected to Regatta!

Application Details

Controllers

jdbc_app/src/main/java/dev/regatta/jdbc_springboot/controller/
CustomerController.java
defines the controller interface to the customers table:

package dev.regatta.jdbc_springboot.controller;
 
import dev.regatta.jdbc_springboot.entity.Customer;
import dev.regatta.jdbc_springboot.entity.Purchase;
import dev.regatta.jdbc_springboot.service.CustomerService;
import dev.regatta.jdbc_springboot.service.PurchaseService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
 
import com.github.javafaker.Faker;
 
import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
 
@RestController
@RequestMapping("/customers")
public class CustomerController {
 
    @Autowired
    private CustomerService customerService;
 
    @Autowired
    private PurchaseService purchaseService;
 
    @GetMapping
    public List<Customer> getAllCustomers() {
        return customerService.findAll();
    }
 
    @GetMapping("/{id}")
    public Customer getCustomerById(@PathVariable Long id) {
        return customerService.findOne(id);
    }
 
    @PostMapping
    public Customer createCustomer(@RequestBody Customer customer) {
        return customerService.create(customer);
    }
 
    @PutMapping("/{id}")
    public Customer updateCustomer(@PathVariable Long id, @RequestBody Customer customer) {
        return customerService.update(id, customer);
    }
 
    @DeleteMapping("/{id}")
    public ResponseEntity<String> deleteCustomer(@PathVariable Long id) {
        boolean isDeleted = customerService.deleteCustomerById(id);
        if (isDeleted) {
            String message = "Customer with ID " + id + " deleted successfully.";
            return ResponseEntity.ok(message);
        } else {
            String errorMessage = "Customer with ID " + id + " not found.";
            return ResponseEntity.status(404).body(errorMessage);
        }
    }
 
    /**
     * Endpoint to populate the database with 1000 customers for testing.
     * Each customer will have between 1 to 5 purchases.
     * URL: POST /customers/populate
     */
    @PostMapping("/populate")
    public String populateCustomersAndPurchases() {
        try {
            int numberOfCustomers = 1000;
            int bulkSize = 100;
            int batches = numberOfCustomers / bulkSize;
            int remaining = numberOfCustomers % bulkSize;
 
            long customerIdCounter = 1L;
            long purchaseIdCounter = 1L;
 
            Faker faker = new Faker();
            Random random = new Random();
 
            List<Purchase> allPurchases = new ArrayList<>();
 
            // Generate and collect customers and their purchases in batches
            for (int b = 0; b < batches; b++) {
                List<Customer> customers = generateCustomersWithPurchases(
                        bulkSize, faker, random, customerIdCounter, purchaseIdCounter);
                // Perform bulk insertion for customers
                customerService.createAll(customers);
 
                for (Customer customer: customers) {
                    allPurchases.addAll(customer.getPurchases());
                }
 
                // Perform bulk insertion for purchases
                purchaseService.createAll(allPurchases);
                allPurchases.clear();
                customerIdCounter += bulkSize;
                purchaseIdCounter += customers.stream().mapToInt(c -> c.getPurchases().size()).sum();
            }
 
            // Handle remaining customers
            if (remaining > 0) {
                List<Customer> customers = generateCustomersWithPurchases(
                        remaining, faker, random, customerIdCounter, purchaseIdCounter);
 
                for (Customer customer : customers) {
                    allPurchases.addAll(customer.getPurchases());
                }
 
                // Perform bulk insertion for customers
                customerService.createAll(customers);
 
                // Perform bulk insertion for purchases
                purchaseService.createAll(allPurchases);
            }
 
            return numberOfCustomers +
                    " customers with purchases have been successfully added to the database.";
        } catch (Exception e) {
            return "An error occurred during the population process: " + e.getMessage();
        }
    }
 
    /**
     * Generates a list of customers with random data and purchases.
     *
     * @param count the number of customers to generate
     * @param faker the Faker instance for generating data
     * @param random the Random instance for generating random numbers
     * @param customerIdStart the starting customer ID
     * @param purchaseIdStart the starting purchase ID
     * @return a list of generated customers
     */
    private List<Customer> generateCustomersWithPurchases(int count, Faker faker, Random random,
                                             long customerIdStart, long purchaseIdStart) {
        List<Customer> customers = new ArrayList<>(count);
 
        for (int i = 0; i < count; i++) {
            Customer customer = new Customer();
            customer.setCustomerId(customerIdStart++);
            String rawName = faker.name().fullName();
            String sanitizedName = rawName.replace("'", "");
            customer.setName(sanitizedName);
            customer.setEmail(faker.internet().emailAddress());
            String phone = faker.phoneNumber().phoneNumber();
            customer.setPhone(phone.length() > 12 ? phone.substring(0, 12) : phone);
 
            // Generate between 1 to 5 purchases for this customer
            int numberOfPurchases = random.nextInt(5) + 1;
            for (int p = 0; p < numberOfPurchases; p++) {
                Purchase purchase = new Purchase();
                purchase.setPurchaseId(purchaseIdStart++);
                // Random purchase date within the last 2 years
                LocalDate purchaseDate = LocalDate.now().minusDays(random.nextInt(730));
                purchase.setPurchaseDate(purchaseDate);
                // Random price between $10 and $1000 with 2 decimal places
                BigDecimal price = BigDecimal.valueOf(faker.number().randomDouble(2, 10, 1000));
                purchase.setPrice(price);
                purchase.setItem(faker.commerce().productName());
                purchase.setCustomer(customer);
 
                customer.getPurchases().add(purchase);
            }
 
            customers.add(customer);
        }
 
        return customers;
    }
}

Note: The populate endpoint can be used to create 1,000 random customers. Each customer will be randomly assigned 1 to 5 purchases.

Similarly, the PurchaseController.java defines the controller interface to the purchases table.

Services

jdbc_app/src/main/java/dev/regatta/jdbc_springboot/service/DatabaseSetupService.java defines the main service that creates the database tables:

package dev.regatta.jdbc_springboot.service;
 
import java.sql.*;
import java.util.HashSet;
import java.util.Set;
 
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.event.ContextRefreshedEvent;
import org.springframework.context.event.EventListener;
import org.springframework.stereotype.Service;
 
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
@Service
public class DatabaseSetupService {
 
    private static final Logger logger = LoggerFactory.getLogger(DatabaseSetupService.class);
 
    @Value("${spring.datasource.url}")
    private String jdbcURL;
 
    @Value("${spring.datasource.username}")
    private String jdbcUsername;
 
    @Value("${spring.datasource.password}")
    private String jdbcPassword;
 
    @EventListener(ContextRefreshedEvent.class)
    public void createTables() {
        logger.info("Starting table creation...");
        try (Connection connection = DriverManager.getConnection(jdbcURL, jdbcUsername, jdbcPassword);
             Statement statement = connection.createStatement()) {
 
            Set<String> existingTables = getExistingTables(connection);
 
            if (!existingTables.contains("customers")) {
                createCustomerTable(statement);
            } else {
                logger.info("Customer table already exists.");
            }
 
            if (!existingTables.contains("purchases")) {
                createPurchaseTable(statement);
            } else {
                logger.info("Purchase table already exists.");
            }
 
        } catch (SQLException e) {
            logger.error("Failed to check or create tables in the database.", e);
            throw new RuntimeException("Failed to check or create tables in the database.", e);
        }
    }
 
    private Set<String> getExistingTables(Connection connection) throws SQLException {
        Set<String> tables = new HashSet<>();
        String showTablesSQL = "SHOW TABLES";
 
        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(showTablesSQL)) {
            while (resultSet.next()) {
                tables.add(resultSet.getString(1).toLowerCase());
            }
        }
        logger.debug("Existing tables: {}", tables);
        return tables;
    }
 
    private void createCustomerTable(Statement statement) throws SQLException {
        String createCustomerTableSQL = """
                CREATE TABLE customers (
                    customerid BIGINT PRIMARY KEY INDEX,
                    name VARCHAR(100) NOT NULL,
                    email VARCHAR(100) NOT NULL,
                    phone VARCHAR(15)
                );
                """;
        statement.execute(createCustomerTableSQL);
        logger.debug("Customer table created or already exists.");
    }
 
    private void createPurchaseTable(Statement statement) throws SQLException {
        String createPurchaseTableSQL = """
                CREATE TABLE purchases (
                    purchaseid BIGINT PRIMARY KEY INDEX,
                    customerid BIGINT NOT NULL REFERENCES customers INDEX,
                    item VARCHAR(255),
                    price DECIMAL(10, 2),
                    purchasedate DATE
                );
                """;
        statement.execute(createPurchaseTableSQL);
        logger.debug("Purchase table created or already exists.");
    }
}

CustomerService.java implements the CustomerService, which allows you to perform CRUD operations on customers. This service uses the Customer entity that is defined in entity/Customer.java:

package dev.regatta.jdbc_springboot.entity;
 
import java.util.ArrayList;
import java.util.List;
 
public class Customer {
 
    private Long customerId;
    private String name;
    private String email;
    private String phone;
    private List<Purchase> purchases = new ArrayList<>();
 
    public Customer() {
    }
 
    public Customer(Long customerId, String name, String email, String phone) {
        this.customerId = customerId;
        this.name = name;
        this.email = email;
        this.phone = phone;
    }
 
    // Getters and Setters
 
    public Long getCustomerId() {
        return customerId;
    }
 
    public void setCustomerId(Long customerId) {
        this.customerId = customerId;
    }
 
    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;
    }
 
        public String getPhone() {
        return phone;
    }
 
    public void setPhone(String phone) {
        this.phone = phone;
    }
 
    public List<Purchase> getPurchases() {
        return purchases;
    }
 
    public void setPurchases(List<Purchase> purchases) {
        this.purchases = purchases;
    }
 
    @Override
    public String toString() {
        return "Customer{" +
                "customerId=" + customerId +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }
}
Instances of the Purchase class, defined in jdbc_app/src/main/java/dev/regatta/jdbc_springboot/entity/Purchase.java, represent rows in the purchases table:
package dev.regatta.jdbc_springboot.entity;
 
import java.math.BigDecimal;
import java.time.LocalDate;
 
public class Purchase {
 
    private Long purchaseId;
    private Long customerId;
    private String item;
    private BigDecimal price;
    private LocalDate purchaseDate;
 
    // Getters and Setters
 
    public Long getPurchaseId() {
        return purchaseId;
    }
 
    public void setPurchaseId(Long purchaseId) {
        this.purchaseId = purchaseId;
    }
 
    public Long getCustomerId() {
        return customerId;
    }
 
    public void setCustomerId(Long customerId) {
        this.customerId = customerId;
    }
 
    public String getItem() {
        return item;
    }
 
    public void setItem(String item) {
        this.item = item;
    }
 
    public BigDecimal getPrice() {
        return price;
    }
 
    public void setPrice(BigDecimal price) {
        this.price = price;
    }
 
    public LocalDate getPurchaseDate() {
        return purchaseDate;
    }
 
    public void setPurchaseDate(LocalDate purchaseDate) {
        this.purchaseDate = purchaseDate;
    }
 
    public void setCustomer(Customer customer) {
        if (customer != null) {
            this.customerId = customer.getCustomerId();
        } else {
            this.customerId = null;
        }
    }
}

Similarly, the PurchasesService.java implements the PurchasesService, which allows you to perform CRUD operations on purchases. This service uses the Purchase entity that is defined in entity/Purchase.java.

Entities

Instances of the Customer class, defined in jdbc_app/src/main/java/dev/regatta/jdbc_springboot/entity/Customer.java, represent rows in the customers table:

package dev.regatta.jdbc_springboot.entity;
 
import java.util.ArrayList;
import java.util.List;
 
public class Customer {
 
    private Long customerId;
    private String name;
    private String email;
    private String phone;
    private List<Purchase> purchases = new ArrayList<>();
 
    public Customer() {
    }
 
    public Customer(Long customerId, String name, String email, String phone) {
        this.customerId = customerId;
        this.name = name;
        this.email = email;
        this.phone = phone;
    }
 
    // Getters and Setters
 
    public Long getCustomerId() {
        return customerId;
    }
 
    public void setCustomerId(Long customerId) {
        this.customerId = customerId;
    }
 
    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;
    }
 
        public String getPhone() {
        return phone;
    }
 
    public void setPhone(String phone) {
        this.phone = phone;
    }
 
    public List<Purchase> getPurchases() {
        return purchases;
    }
 
    public void setPurchases(List<Purchase> purchases) {
        this.purchases = purchases;
    }
 
    @Override
    public String toString() {
        return "Customer{" +
                "customerId=" + customerId +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }
}

Instances of the Purchase class, defined in jdbc_app/src/main/java/dev/regatta/jdbc_springboot/entity/Purchase.java, represent rows in the purchases table:

package dev.regatta.jdbc_springboot.entity;
 
import java.util.ArrayList;
import java.util.List;
 
public class Customer {
 
    private Long customerId;
    private String name;
    private String email;
    private String phone;
    private List<Purchase> purchases = new ArrayList<>();
 
    public Customer() {
    }
 
    public Customer(Long customerId, String name, String email, String phone) {
        this.customerId = customerId;
        this.name = name;
        this.email = email;
        this.phone = phone;
    }
 
    // Getters and Setters
 
    public Long getCustomerId() {
        return customerId;
    }
 
    public void setCustomerId(Long customerId) {
        this.customerId = customerId;
    }
 
    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;
    }
 
        public String getPhone() {
        return phone;
    }
 
    public void setPhone(String phone) {
        this.phone = phone;
    }
 
    public List<Purchase> getPurchases() {
        return purchases;
    }
 
    public void setPurchases(List<Purchase> purchases) {
        this.purchases = purchases;
    }
 
    @Override
    public String toString() {
        return "Customer{" +
                "customerId=" + customerId +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }
}

Testing API Endpoints

Populate Customers and Purchases

[16:06:24] eyallotan | (main) | ~/spring-boot-app/jdbc_app$ curl -X POST http://localhost:8080/customers/populate
1000 customers with purchases have been successfully added to the database.

Retrieve a Customer by ID

[16:49:12] eyallotan | (main) | ~/spring-boot-app/jdbc_app$ curl -s -X GET http://localhost:8080/customers/17 | jq
{
  "customerId": 17,
  "name": "Sean Fadel",
  "email": "nathanial.kerluke@hotmail.com",
  "phone": "1-727-196-44",
  "purchases": [
    {
      "purchaseId": 55,
      "customerId": 17,
      "item": "Heavy Duty Leather Computer",
      "price": 605.14,
      "purchaseDate": "2025-02-04"
    },
    {
      "purchaseId": 56,
      "customerId": 17,
      "item": "Awesome Leather Keyboard",
      "price": 794.91,
      "purchaseDate": "2025-01-13"
    },
    {
      "purchaseId": 57,
      "customerId": 17,
      "item": "Sleek Bronze Watch",
      "price": 808.29,
      "purchaseDate": "2024-09-17"
    },
    {
      "purchaseId": 58,
      "customerId": 17,
      "item": "Intelligent Rubber Gloves",
      "price": 647.99,
      "purchaseDate": "2024-09-16"
    },
    {
      "purchaseId": 59,
      "customerId": 17,
      "item": "Awesome Marble Table",
      "price": 421.06,
      "purchaseDate": "2024-09-07"
    }
  ]
}

Create a New Customer

[16:57:22] eyallotan | (main) | ~/spring-boot-app/jdbc_app$ curl -s -X POST http://localhost:8080/customers \
>      -H "Content-Type: application/json" \
>      -d '{
>            "customerId": 2100000,
>            "name": "John Doe",
>            "email": "john.doe@example.com",
>            "phone": "123-456-7890"
>          }' | jq
{
  "customerId": 2100000,
  "name": "John Doe",
  "email": "john.doe@example.com",
  "phone": "123-456-7890",
  "purchases": []
}

Update a Customer

[16:58:55] eyallotan | (main) | ~/spring-boot-app/jdbc_app$ curl -s -X PUT http://localhost:8080/customers/2100000 \
>      -H "Content-Type: application/json" \
>      -d '{
>            "name": "Martin Doe",
>            "email": "martin.doe@example.com",
>            "phone": "098-765-4321"
>          }' | jq
{
  "customerId": 2100000,
  "name": "Martin Doe",
  "email": "martin.doe@example.com",
  "phone": "098-765-4321",
  "purchases": []
}

Delete a Customer

[16:59:42] eyallotan | (main) | ~/spring-boot-app/jdbc_app$ curl -s -X DELETE http://localhost:8080/customers/2100000
Customer with ID 2100000 deleted successfully.
[17:01:28] eyallotan | (main) | ~/spring-boot-app/jdbc_app$ curl -X GET http://localhost:8080/customers/2100000
[17:01:36] eyallotan | (main) | ~/spring-boot-app/jdbc_app$

For more details and full source code, check out the GitHub repository: Regatta Spring Boot Example.

Happy coding!

< Back to blog

Sign up for the newsletter

    Thanks for subscribing!

    You’re already a subscriber – thank you!

    Latest Blogs

    Can developers and databases finally escape their endless Waltz?

    Commonly one of the first things one does when learning a new language and using a new framework is to build a basic…

    Connect Your Spring Boot Application to Regatta

    In this tutorial, we’ll show you just how simple it is to deploy a Spring Boot application that interacts with Regatta…

    Regatta’s Architecture: A Bird’s Eye View

    In this blog I will provide a high level overview of Regatta’s design and architectural principles. While I keep this…

    Sharding – some dirty little secrets

    A summary of the more comprehensive whitepaper examining the dirty little secrets behind sharded databases and the…
    Showing 1-3 of 10 articles
    Skip to content