Prerequisites

This tutorial assumes that you have:

  • YugabyteDB 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.

Clone the "orm-examples" repository

$ git clone https://github.com/YugabyteDB-Samples/orm-examples.git

The Using ORMs with YugabyteDB orm-examples repository has a Spring Boot example that implements a basic REST API server. The scenario is that of an e-commerce application. Database access in this application is managed through Spring Data JPA, which internally uses Hibernate as the JPA provider. It consists of the following:

  • The users of the e-commerce site are stored in the users table.
  • The products table contains a list of products the e-commerce site sells.
  • The orders placed by the users are populated in the orders table. An order can consist of multiple line items, each of these are inserted in the orderline table.

The source for this example application can be found in the repository.

There are a number of options that 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.

Build the application

$ cd orm-examples/java/spring
$ mvn -DskipTests package

Run the application

Start the Sprint Boot REST API server at http://localhost:8080.

$ mvn spring-boot:run

Send requests to the application

Create 2 users.

$ curl --data '{ "firstName" : "John", "lastName" : "Smith", "email" : "[email protected]" }' \
   -v -X POST -H 'Content-Type:application/json' http://localhost:8080/users
$ curl --data '{ "firstName" : "Tom", "lastName" : "Stewart", "email" : "[email protected]" }' \
   -v -X POST -H 'Content-Type:application/json' http://localhost:8080/users

Create 2 products.

$ curl \
  --data '{ "productName": "Notebook", "description": "200 page notebook", "price": 7.50 }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/products
$ curl \
  --data '{ "productName": "Pencil", "description": "Mechanical pencil", "price": 2.50 }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/products

Create 2 orders.

$ curl \
  --data '{ "userId": "2", "products": [ { "productId": 1, "units": 2 } ] }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/orders
$ curl \
  --data '{ "userId": "2", "products": [ { "productId": 1, "units": 2 }, { "productId": 2, "units": 4 } ] }' \
  -v -X POST -H 'Content-Type:application/json' http://localhost:8080/orders

Query results

Using the YSQL shell

$ ./bin/ysqlsh
ysqlsh (11.2)
Type "help" for help.

yugabyte=#

List the tables created by the app.

yugabyte=# \d
List of relations
 Schema |          Name           |   Type   |  Owner
--------+-------------------------+----------+----------
 public | orderline               | table    | yugabyte
 public | orders                  | table    | yugabyte
 public | orders_user_id_seq      | sequence | yugabyte
 public | products                | table    | yugabyte
 public | products_product_id_seq | sequence | yugabyte
 public | users                   | table    | yugabyte
 public | users_user_id_seq       | sequence | yugabyte
(7 rows)

Note the 4 tables and 3 sequences in the list above.

yugabyte=# SELECT count(*) FROM users;
 count
-------
     2
(1 row)
yugabyte=# SELECT count(*) FROM products;
 count
-------
     2
(1 row)
yugabyte=# SELECT count(*) FROM orders;
 count
-------
     2
(1 row)
yugabyte=# SELECT * FROM orderline;
 order_id                             | product_id | units
--------------------------------------+------------+-------
 45659918-bbfd-4a75-a202-6feff13e186b |          1 |     2
 f19b64ec-359a-47c2-9014-3c324510c52c |          1 |     2
 f19b64ec-359a-47c2-9014-3c324510c52c |          2 |     4
(3 rows)

Note that orderline is a child table of the parent orders table connected using a foreign key constraint.

Using the REST API

$ curl http://localhost:8080/users
{
  "content": [
    {
      "userId": 2,
      "firstName": "Tom",
      "lastName": "Stewart",
      "email": "[email protected]"
    },
    {
      "userId": 1,
      "firstName": "John",
      "lastName": "Smith",
      "email": "[email protected]"
    }
  ],
  ...
}
$ curl http://localhost:8080/products
{
  "content": [
    {
      "productId": 2,
      "productName": "Pencil",
      "description": "Mechanical pencil",
      "price": 2.5
    },
    {
      "productId": 1,
      "productName": "Notebook",
      "description": "200 page notebook",
      "price": 7.5
    }
  ],
  ...
}
$ curl http://localhost:8080/orders
{
  "content": [
    {
      "orderTime": "2019-05-10T04:26:54.590+0000",
      "orderId": "999ae272-f2f4-46a1-bede-5ab765bb27fe",
      "user": {
        "userId": 2,
        "firstName": "Tom",
        "lastName": "Stewart",
        "email": "[email protected]"
      },
      "userId": null,
      "orderTotal": 25,
      "products": []
    },
    {
      "orderTime": "2019-05-10T04:26:48.074+0000",
      "orderId": "1598c8d4-1857-4725-a9ab-14deb089ab4e",
      "user": {
        "userId": 2,
        "firstName": "Tom",
        "lastName": "Stewart",
        "email": "[email protected]"
      },
      "userId": null,
      "orderTotal": 15,
      "products": []
    }
  ],
  ...
}

Explore the source

As highlighted earlier, the source for the above application is available in the orm-examples repository.