Here we create a target data source to receive order details from a CSV text file. This CSV data source was set up as a source of our data transfer, see Example CSV source. The second task in creating this data transfer is to create an appropriate JDBC target data source, discussed here. Lastly we will have to define the mappings between the two, see Example JDBC mapping.
In this step we must consider the structure of the JDBC data source required for it to be a target of the data transfer. The CSV source file contains header details and differing numbers of order lines. To build up a database of these orders the JDBC target will require at least two tables, OrderHeader and OrderLine, say, with appropriate columns for the information being transferred. We might also include a lookup table to extract a product description for each order line.
First we create the new JDBC data source to be the target of this transfer. Selecting JDBC data source type in Explorer, we then choose New JDBC data source from the right-click menu or press the New icon, see New data source for more. The Properties panel for a JDBC data source appears and here we enter an appropriate name and other property details, see JDBC properties. The Definition tab on this panel is where we set up the structure appropriate for this data transfer action. We must create the entities (tables) OrderHeader and OrderLine, and each entity needs appropriate Columns to hold the CSV file data. To create each entity, press the New entity icon on the Properties toolbar, then enter details in the Entity properties panel. Choose the Columns tab in an Entity properties window then press Add, to create the Column headings, for example here is the Entity properties window as column headings are being created:
When all fields are showing on the Definition screen (use the Show//hide field headings icon to display them), the target JDBC data source will look like this:
The arrow showing between the entities in the definition panel indicates a relation has been defined between these tables, see Relationship properties. In this example OrderID is the field used to form the relationship between the two tables OrderHeader and OrderLine, so the definition of the target JDBC data source defines the relationship as an inner join between the two OrderID fields. Select the Child entity (the OrderLine in this example), then press the Relationship icon to see the Relationship properties window where you can define this relationship:
Now that the JDBC target data source has been defined, with properties and structure set, the next task is to set up the mappings for the data transfer, between the source CSV file and this target JDBC data source, see Example JDBC mapping for more.
Related topics include Example CSV source, Example JDBC mapping