Configuring Data Mashup Projects Using Joins
After creating a Data Mashup project wherein you have brought all your diverse data into a common staging area, you are ready to create a federated view of the data. This topic provides the necessary steps for you to create data joins using the data stored in the staging tables.
Data Mashup Topics
To see a list of all the Data Mashup topics, click Using the Sun Data Mashup Engine
To Add a Join
Before You Begin
Before creating a join, you must have a virtual database, Data Mashup project, the NetBeans IDE must be running, and you must be connected to the virtual database.
- If necessary, connect to the virtual database.
- In the NetBeans IDE, select the Services tab and expand the Databases.
- Right-click the database you want to start and select Connect.
In this procedure, start VirtualMashupDB_1222123456789.
- In the Project tree expand your Data Mashup project.
Under the default package you should see the Data Mashup file you created when creating a Data Mashup project, demoDMfile.edm (see Designing Data Mashup Projects).
Note – demoDMfile.edm also appears as a tab in the center pane.
- In the NetBeans IDE center pane, double-click demoDMfile.edm to open the NetBeans IDE Canvas.
- Add the tables you want to join to the canvas.
- In the EDM Editor tool bar click the Add Table icon.
Note – If the Projects tab covers some of the tools available for use with the canvas, and you do not see the Add Table icon, click the Projects tab to close the pane or click the Restore Window icon on the canvas tool bar.
- In the Select Source Table window, select the database.
In this procedure, use VirtualMashupDB_1222123456789. After selecting the database, the tables you added to it appear in the Tables area on the right side of the window. You are using the SUPPLIER_ADDRESS and COMPANY_DATA tables.
- Highlight tables and click Select.
Repeat this step if necessary to add both tables to the Table Selection area.
- Click OK.
The window closes and tables, along with Runtime Input, appear on the canvas. You are now ready to create the join.
- From the Table Operators palette, drag the Join operator onto the canvas.
You are now ready to merge your tables into one table. In the example you are going to merge SUPPLIER_ADDRESS and COMPANY_DATA.
- In the Create New Join View window, click All> to move the tables to the Selected Tables list.
In the example, both tables appear in the Preview area and are linked to the join. You can edit the join types using the drop-down dialog.
* Inner returns only the records in the selected tables that match.
* Left Outer returns all records in the left table regardless if there are any matches with the right table. When there is no matching in the right table, NULL fills in the field.
* Right Outer returns all records in the right table regardless if there are any matches with the left table. When there is no matching in the left table, NULL fills in the field.
* Full Outer returns the all records from the left and right tables in the merged table. All fields that do not match are filled with NULL.
- Click OK.
The root join is added to the canvas and is linked to the two tables.
To Edit a Join Condition
Before You Begin
Before creating a join, you must have a virtual database, Data Mashup project, the NetBeans IDE must be running, you must be connected to the virtual database, and added a join.
- Right-click the Root Join Widget on the canvas and then click Edit Join Condition.
The Edit Join Condition window, also called the Condition Builder, opens.
- Create a join condition in the Edit Join Condition window.
- Drag a column from a table onto the empty canvas on the right.
- Drag and drop a comparison, string, or another operator onto the canvas.
In this example, use the equal (=) sign.
- Drag a column from the second table onto the canvas to complete the query.
Even though there are two join conditions that match from the tables, VENDOR and VENDOR_CODE, you only need to complete one join with the equal sign.
- When satisfied with your join conditions, click OK.
The Edit Join Condition window closes. You are almost ready to create and deploy a composite application which contains your Data Mashup project. This step enables you to view your data on the web. However, first you need to save your project.
- To save the project, right-click it and select Build Project.
A WSDL is created within the Collaboration node. In the example, DemoDMProject_demoDMfile_engine.wsdl.
You are now ready to create and deploy a composite application in the Sun GlassFish Enterprise Server. For detailed instructions on how to perform this task, see Creating and Deploying a Composite Application to Complete a Data Mashup Project