examples package: Using
$parentJDBCBatchCursor to insert multiple rows into a table with foreign keys
examples package provides services that demonstrate how to
insert rows into database tables with foreign key constraints. To prevent
foreign key errors, the child cursor's
$parentJDBCBatchCursor SQL service property is mapped to
the parent cursor. This will cause the child cursor to execute the parent's batch operation before executing its own,
which results in rows for the parent table being inserted first regardless of the parent's batch size at the time.
The rows for the child table are then consequently inserted. This ensures that when the child rows are inserted, the
parent rows already exist.
$parentJDBCBatchCursor isn't used properly, there is a chance that the batch for the child table will reach
it's maximum size before the parent. This will cause the child rows to be inserted without the parent table's rows
being inserted, which may result in foreign key related errors.
Please see the following articles for more information:
In the Navigator, expand the
examples package and navigate to the
code folder, then expand the
cursors package. This package contains the
files and/or directories as shown below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
cursors.parentCursor.model package, you will see
Country.model; this 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 related via a foreign key.
cursors.sql package are the SQL services for inserting, deleting, and selecting
rows from each of the tables.
cursors.parentCursor.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
1 2 3 4 5 6
This example shows how Gloop maintains data consistency when inserting multiple data entries to database tables linked together with a foreign key. Only when the parent 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:
- 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.
- Create a new service where that will call the two previously created SQL services.
- Call the SQL service for batch inserting the parent entries in the created service.
- Create an extra output property via the Mapper view and map the parent batch insert SQL service's output cursor to this property.
- Call the SQL service for batch inserting the child entries in the created service. Make sure to map the
output cursor from the previous call the the input (
$parentJDBCBatchCursor) of this service, as shown in the screenshot below (the green map line)