Lab 4 - Database

	In this lab you will process a CSV file and insert a record in a Database for each line. To do this, you will use the a Bindy component to parse the CSV file, and a JPA component to insert rows in a PostgreSQL DB.

1. Import Lab4.zip from ~/FuseWorkshop/support/Lab4/ as an “Existing Project into Workspace”.
2. Disregard the errors.
3. Right-click on the Lab4 project, and select New-> JPA Entities from tables.
4. Click on the “add connections” button.
5. Select PostgreSQL and click Next.

1. Click on the “New driver definition” button .
2. Select PostgreSQL JDBC Driver and click on the JAR List tab.
3. Select the driver file and click on the Edit JAR/Zip button.

1. Browse to ~/FuseWorkshop/support/Lab4 and select postgresql-9.4-1201.jdbc4.jar

2. Click on the Properties tab and complete the following:

3. Connection URL: jdbc:postgresql://localhost:5432/JBossBank

4. Database Name: JBossBank
5. Password: postgres

6. User ID: postgres

7. Click OK.

1. Click on the Test Connection button to validate the connection.
2. Click FInish.
3. In the Select Tables dialog, select the public schema and check the transactions table.

1. Click Next.
2. Click Next
3. In the Domain java class area, enter com.jbossbank as the package.
4. Click Finish.

1. Open blueprint.xml by double-clicking it.

2. Add a File component from the Components palette.

3. Edit its properties

4. Generic - URI: file:TransactionsDir

5. Add an Unmarshal component from the Transformation palette.

6. Edit its properties

7. Select the bindy tab

8. Class Type: com.jbossbank.Transaction
9. Type: Csv

1. Add a Generic component from the Components palette.

2. Edit its properties

3. Uri: jpa:

4. Advanced-> Path

5. Entity Type: java.util.ArrayList

6. Advanced-> General

7. Persistence Unit: Lab4

	We define java.util.ArrayList as the Entity Type because we will be sending a list of transactions (read and unmarshalled from the csv file) to be inserted in the table.

1. Link the File component to the Unmarshal component.
2. Link the Unmarshal component to the JPA component.
3. Save the blueprint file (Ctrl+S).
4. Open Transaction.java under src/main/java/com.jbossbank.
5. Add the following annotation before “@Entity”: @CsvRecord( separator = ";" , skipFirstLine = true )
6. Add the following annotation before the transactionid property: @DataField(pos = 1)
7. Add the following annotation before the accountid property: @DataField(pos = 2)
8. Add the following annotation before the amount property: @DataField(pos = 3)
9. Add the following annotation before the clientid property: @DataField(pos = 4)

1. Click on the error marker next to @CsvRecord annotation and select the first suggestion (Import CsvRecord)

1. Click on the error marker next to any @DataField annotation and select the first suggestion (Import DataField).
2. Save the file and close it.
3. Right-click on src/main/resources/META-INF folder and select Import -> General -> File System
4. Browse to ~/FuseWorkshop/support/Lab4 and select persistence.xml.
5. Accept the Overwrite warning and click Yes.

6. Open persistence.xml file under JPA Content, and review its contents.

7. Run the Camel Route by right-clicking blueprint.xml, Run As -> Local Camel Context (without tests).

8. Once the route is running, right-click on the TransactionsDir folder.

9. If it doesn’t appear, just refresh Lab4 project.

10. Click Import, General -> File System

11. Browse to ~/FuseWorkshop/support/Lab4, and select Transactions.csv
12. Click Finish.
13. The route will process the CSV file and insert each line into the PostgreSQL database.
14. Stop the camel route by clicking on the Stop button .
15. Close all open files.
16. Double-click on the pgAdmin3 icon in the desktop

1. In pgAdmin III, right-­click on local, and select Connect

1. Expand Databases -> JBossBank -> Schemas -> public
2. Right-click on ​Tables­>transactions, select ​View Data­>View Top 100 Rows.
3. Validate that there are 3 new records in the table coming from the CSV file.
4. Close pgAdmin3.