examples
package: Using $parentJDBCBatchCursor
to insert multiple rows into a table with foreign keys
The 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.
If the $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.
Related articles
Please see the following articles for more information:
Try it!
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 |
|
In the 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.
In the cursors.sql
package are the SQL services for inserting, deleting, and selecting
rows from each of the tables.
The 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
explanation2.
Output of cursors.parentCursor.ParentCursorExample.gloop
1 2 3 4 5 6 |
|
Explanation
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)