Hi! In this post, I'm going to show you how we can insert multiple records into the database table using data from a text file. I'm using sample Emp data that needs to be inserted into db. So, I created a table with just three columns and my table structure is as below.
1. Create a SOA project with suitable name.
Drag and drop a File adapter to the Exposed Services side. Give a name to it. Click Next.
Leave the default interface as "Define from Operation and schema(specified later).
Select the JNDI name.
Select the Operation Type as Read.
Browse the folder where you have the text file that contains data and click Next. Here I've unchecked the option "Delete files after successful retrieval" because I don't want it to be deleted after reading. It is up to you whether you check or uncheck this option.
Specify the pattern of the file(s) it should take. In my case, I gave the pattern as "EmpData*.txt" which means it would take the files starting with EmpData that are of .txt type.
For example, in my case, I have a file named EmpData.txt. If I have the files such as EmpData123.txt, EmpData85.txt., they would also be taken for processing. Check the option Files contain Multiple Messages. Click Next.
Leave the default Polling Frequency, you can change it if you want. Click Next.
Here, we need to create a schema. So click on the highlighted icon to create a native schema.
You can change the name if you want. Click Next.
Select the file type as Delimited.
Select the file that you want to give as sample. Click Next.
Click Next.
Give a name for the element that will represent record. It will use it to generate the element in XML creation.
Click Next.
I have the header names in my file. So I want to use the first record as field names. If you don't have the header records, don't check this option "Use the first record as the field names".
It will generate XML based on the data. You can test it by clicking on Test option.
Click Finish.
Now that we've done with schema creation, it will take the schema that is just created. Click Next.
In the next step, click Finish.
2. Drag and drop a BPEL from the Components window. Select No Service in the "Template Type".
Wire the BPEL to file adapter.
3. Drag and drop a db adapter into the External References side. Give it a suitable name and click Next.
Select the connection and click Next.
Select the Operation Type as Insert Only and click Next.
Click on the "Import Tables" to import a table.
Select the required table and click Next.
Click Next.
Click Next.
Select the columns where you want to insert data.
Click Next.
Click Next.
Click Finish. With this, we've completed creating a database adapter based on Insert operation.
4. Wire the BPEL to the db adapter as well.
Edit the BPEL and drag and drop a Receive activity. Select the Partner link as the file adapter and create an Input variable. Click Apply and then OK.
Drag and drop an Invoke activity just after Receive activity. Select the Insert database partner link and create an Input variable. Click Apply and click OK.
Now, drag and drop an Assign activity to map the input variables from File to database adapter. Click Apply and then OK.
With this, you've completed all the steps in designing a service that takes input from a text file and inserts records into the database table.
Deploy your composite and search the Flow Instances.
Now check your table to see if the records have been inserted into it.
Hope this post helped you. If you have any queries, hit them in the comment section below. See you in the next post. Until then, Happy Learning. Cheers!!
