Skip to content

Overview

Development

Overview

IDEs

API Explorer

examples Package: Using Gloop to Insert Multiple Rows into a Table with Foreign Keys Using $parentJDBCBatchCursor

Introduction

The examples package provides Gloop services that demonstrate how to insert multiple data entries to a database table with foreign keys. To prevent foreign key errors, statements against the parent table are executed first. The SQL statements for the child table are consequently executed, linking the entries to the parent table via the $parentJDBCBatchCursor Gloop SQL service property.

Related articles

Please see the following articles for more information:

Running the Service

Under the Coder Navigator, expand the examples package entry and navigate to the code folder. Afterwards, look for the cursors package. This package contains the files and/or directories shown below:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
examples
├── ...
└── code
    └── cursors
        └── parentCusor
            ├── conf
            │   └── StartUpService.gloop
            ├── model
            │   └── Country.model
            ├── sql
                ├── BatchInsertCountry.gloop
                ├── BatchInsertSubdivision.gloop
                ├── DeleteCountries.gloop
                ├── DeleteSubdivisions.gloop
                ├── SelectCountries.gloop
                └── SelectSubdivisions.gloop
            └── ParentCursorExample.gloop

In the cursors.model package, you will see Country.model; it is the entity type our services will insert into tables. We have two tables for this model: one table for the actual country entity, and another table for storing subdivisions1. These tables are linked together via a foreign key.

In the cursors.sql package are the Gloop SQL services for inserting, deleting, and selecting rows from each of the tables.

The ParentCursorExample.gloop service demonstrates how all of the components above work together in order to delete, insert, and select rows from two different tables linked together via a foreign key. Simply run this service to see it in action; line comments are provided for further explanation2.

Output of ParentCursorExample.gloop

1
2
3
4
5
6
{
    "output": {
        "insertedCountries": 7,
        "insertedSubdivisions": 97
    }
}

How It Works...

This example shows how Gloop maintains data consistency when inserting multiple data entries at once to database tables linked together with a foreign key. Only when the independent entries are inserted should the child rows be inserted and associated with their corresponding parent rows to prevent foreign key issues. The list below explains the steps undertaken in order to be able to do a batch insert in this example:

  1. Create two SQL services for batch inserting; one service is for batch inserting entries into the parent table, and the other is for batch inserting rows into the child table.
  2. Create a new Gloop service where we'll call the two previously created Gloop SQL services.
  3. Call the Gloop SQL service for batch inserting the parent entries in the created Gloop service.
  4. Create an extra output property via the Mapper view and map the parent batch insert Gloop SQL service's output cursor to this property.
  5. Call the Gloop SQL service for batch inserting the child entries in the created Gloop service. Make sure to map the output cursor from the previous call as an input ($parentJDBCBatchCursor) to this service.

  1. See the Country model's subdivisions property. 

  2. Open the Gloop service file to see comments. Make sure comments are also not hidden.