Resources

SAS® AppDev Studio™ 3.0 Developer's Site

ActionProvider: Master/Detail via a Custom Action   About It Build It  

Data Source: JDBC

Please install the latest webAF template updates prior to building this example. For more information about the server-side example templates used by this example, see Web Application Example Templates and Built-in Web Application Templates and Options.

The following example is not meant to be a complete Web application, rather it is to show how to use a particular component(s). The example does not address the issue of immediately freeing up resources when the user navigates off the Web application or closes the Web browser. Any necessary resources created in the example will stay around until the associated HTTPSession times out. If this example is used in a multi-user environment, it is possible to exhaust the available resources until HTTPSessions time out and free up their associated resources.

Step 1: Create a project in webAF

  1. Create a new project named MasterDetailExample.
  2. Select Web Application from the webAF Projects list.
  3. Accept the defaults as you go through the Web App wizard until you have reached step #4 of the wizard.
    At this step you will need to select Examples in the radio box titled Display list for. Choose JDBC TableView Servlet from the list box titled Type of initial content.
  4. Continue accepting defaults as you complete the Web App wizard.

Step 2: Set up access to the data source

  1. Open the JDBCTableViewExampleControllerServlet.java file from the Files Tab of the Project Navigator.
  2. Copy the import section from the example below to your version of the file.
  3. In the second synchronized block where the default JDBConnection is initialized, change the value given to the jdbcQuery variable from ENTER_QUERY_STRING_HERE to select * from sasuser.empdata.
  4. Copy the code starting with the commented line "Setup the second query for the detail data" through to the next to last closing bracket and paste it to your own version of the file.

    The position of this new code should be at the end of, and within, the same synchronized block where the default JDBCConnection is initialized.

The following code shows the end result of these modifications to the data access section of the default JSP.

import commands.SubsetCommand;
import com.sas.actionprovider.Area;
import com.sas.actionprovider.HttpAction;
import com.sas.actionprovider.support.tableview.HttpTableViewSupport;
import com.sas.entities.AttributeDescriptorInterface;
import com.sas.servlet.tbeans.StyleInfo;
import com.sas.servlet.tbeans.menubar.html.MenuBar;
import com.sas.servlet.tbeans.tableview.html.TableView;
import com.sas.servlet.tbeans.tableview.html.TableViewComposite;
import java.util.HashMap;
import java.util.Map;
import java.util.Vector;


synchronized (session) {
	//Setup the JDBC connection
	JDBCConnection sas_JDBCConnection = null;
	if (session != null){
		sas_JDBCConnection = (JDBCConnection)session.getAttribute(JDBC_CONNECTION);
	}

	if (sas_JDBCConnection == null){
		try{
			sas_JDBCConnection = new JDBCConnection();
			sas_JDBCConnection.setDriverName(JDBC_DRIVER_NAME);
			sas_JDBCConnection.setDatabaseURL(JDBC_DATABASE_URL);

			ServletConfig sc = getServletConfig();
	   		session.setAttribute(JDBC_CONNECTION, sas_JDBCConnection);
		}
		catch(Exception e){
			throw new RuntimeException(e);
		}
	}

	//Setup the query for the connection, such as "select * from sashelp.class"
	String jdbcQuery = "select * from sasuser.timecrd";

	//Setup the JDBC model adapter
	JDBCToTableModelAdapter adapter = null;
	if (session != null){
		adapter = (JDBCToTableModelAdapter)session.getAttribute(SAS_MODEL);
	}
	if (adapter == null){
		try{
			//Create the model adapter and set it on the session
			adapter = new JDBCToTableModelAdapter(sas_JDBCConnection, jdbcQuery);
			if (session != null){
				session.setAttribute(SAS_MODEL, adapter);
				ExamplesSessionBindingListener.getInstance(session).addAdapter(adapter);
			}
		}
		catch(Exception e){
			throw new RuntimeException(e);
		}
	}

	//Setup the second query for the detail data
	String jdbcQuery2 = "select * from sasuser.empdata";

	//Setup the JDBC model adapter for the detail data
	JDBCToTableModelAdapter adapter2 = null;
	if (session != null){
		adapter2 = (JDBCToTableModelAdapter)session.getAttribute("sas_model2_JDBCTableViewExample");
	}
	if (adapter2 == null){
		try{
			//Create the model adapter and set it on the session
			adapter2 = new JDBCToTableModelAdapter(sas_JDBCConnection, jdbcQuery2);
			if (session != null){
				session.setAttribute("sas_model2_JDBCTableViewExample", adapter2);
			}
		}
		catch(Exception e){
			throw new RuntimeException(e);
		}
	}

	// Create two tables.  The first for the master view. The second
	// for the detail view.
	TableViewComposite sas_TableView1 = null;
	TableViewComposite sas_TableView2 = null;
	if (session != null){
		sas_TableView1 = (TableViewComposite)session.getAttribute("sas_TableView1_JDBCTableViewExample");
		sas_TableView2 = (TableViewComposite)session.getAttribute("sas_TableView2_JDBCTableViewExample");
	}

	if (sas_TableView1 == null){
		sas_TableView1 = new TableViewComposite();
		sas_TableView1.setModel(adapter);
		sas_TableView1.setActionProvider(sas_actionProvider);
		session.setAttribute("sas_TableView1_JDBCTableViewExample", sas_TableView1);

		sas_TableView2 = new TableViewComposite();
		sas_TableView2.setModel(adapter2);
		sas_TableView2.setActionProvider(sas_actionProvider);
		session.setAttribute("sas_TableView2_JDBCTableViewExample", sas_TableView2);

		// create a new HttpAction and associate it with a SubsetCommand
		HttpAction subsetAction = new HttpAction(new SubsetCommand(adapter2));
		subsetAction.setActionType("SUBSET_ACTION");
		subsetAction.putValue( subsetAction.NAME,
                    "%"+HttpTableViewSupport.AREA_VALUE_ATTRKEY);

		// define an attribute to be used as the subset value.
		subsetAction.putValue("employee", null );
		AttributeDescriptorInterface adi = subsetAction.getAttributeDescriptor("employee");
		adi.setSupplementalProperty(subsetAction.CUSTOM, Boolean.TRUE);
		// Column is 'employee' but command attribute is 'subsetValue' so use an alias.
		adi.setLabel(null,"subsetValue");

		// After executing the SubsetCommand, the
		//JDBCTableViewExampleControllerServlet should forward
		// control to JDBCTableViewExampleViewer2.jsp where the detail table is
		//presented.
		subsetAction.putValue(ComponentKeys.FORWARD_LOCATION, "/JDBCTableViewExampleViewer2.jsp" );
		adi = subsetAction.getAttributeDescriptor(ComponentKeys.FORWARD_LOCATION);
		adi.setSupplementalProperty(subsetAction.DYNAMIC, Boolean.FALSE);
		adi.setSupplementalProperty(subsetAction.EXTERNAL, Boolean.TRUE);

		// .tableviewmenuLink not defined in default sasComponents.css so use the
		// .tableviewmenuItemLink style class.
		Map styleMap = new HashMap();
		styleMap.put(MenuBar.MENU_LINK,new StyleInfo("menuItemLink"));
		subsetAction.putValue(subsetAction.STYLE_MAP, styleMap);

		// Register this table as the only viewer of this action
		Vector viewers = new Vector(1);
		viewers.add( sas_TableView1.getComponent(sas_TableView1.TABLEVIEW_TABLEDATA) );
		sas_actionProvider.setAction( subsetAction, viewers,
			new Area( HttpTableViewSupport.DATA_CELL_AREA, "EMPLOYEE" ));
	}
}

Step 3: Create a new Java command class for subsetting the detail data

  1. Select File [arrow] New to open the New window.
  2. On the Files tab of the New window, select Java Source File.
  3. Name it SubsetCommand.java and specify commands for the package name. Choose the Blank Java File option on the second page of the wizard.

The following Java code is used for the class.

package commands;

import com.sas.commands.BaseCommand;
import com.sas.storage.jdbc.JDBCToTableModelAdapter;

public class SubsetCommand extends BaseCommand
{
	private JDBCToTableModelAdapter model;
	private String subsetValue;

	public SubsetCommand(JDBCToTableModelAdapter adapter) {
		this.model = adapter;}

	public void setSubsetValue(String subsetValue) {
		this.subsetValue = subsetValue;}

	public String getSubsetValue() {
		return subsetValue;}

	public void execute(Object o) {
		String newQuery = "SELECT * FROM sasuser.empdata WHERE employee = '"+subsetValue+"'";
		model.setQueryStatement(newQuery);
	}
}

Step 4: Create the master and detail data sets

Run the following code in a SAS session to create the data sets.

data sasuser.timecrd(label='required data only');
  length employee $ 5;
  input employee hrsworkd;
  datalines;
01029 37.5
38741 40
62637 40
P1115 21
00188 39
P2243 16
99764 40
02156 42
;

proc datasets library=sasuser nolist;
  modify timecrd;
    ic create pmk_id = primary key(employee);
  run;
run;

data sasuser.empdata;
  length employee $ 5;
  input employee $ dept $ jobtitle $ payincr sex $ age state $;
  cards;
62637 MKT Marketingrep 4.6  F 42 NY
01029 ISD Technician   2.25 M 39 NC
38741 TS  HelpDesk     .    F 26 NC
P1115 SLS Salesrep     2.2  M 30 GA
00188 MKT Marketingrep 0.5  M 33 TX
P2243 QA  TechnicalSt  .    F 21 NC
99764 TS  Callbackman  0.25 M 25 NC
02156 EXE Executive    10.3 F 48 NC
;

proc datasets library=sasuser nolist;
  modify empdata;
    ic create forkey = foreign key(employee) references timecrd;
  run;
run;

Step 5: Add components to the JSP file

  1. Create a second JSP to handle the presentation of the detail data. Select File [arrow] New to open the New window.
  2. On the Files tab of the New Window, select Java Server Page.
  3. Name the file JDBCTableViewExampleViewer2.jsp.

The code in this JSP should be exactly the same as the code in the default JDBCTableViewExampleViewer.jsp with the exception of the values for the 'id' and 'model' attributes in the TableViewComposite tag and the addition of a Label tag.

<%@ taglib uri="http://www.sas.com/taglib/sas" prefix="sas" %>
<%@ page pageEncoding="UTF-8"%>
<html>
<head>
    <link href="styles/sasComponents.css" rel="STYLESHEET" type="text/css">
</head>
<body>
    <sas:TableViewComposite id="sas_TableView2_JDBCTableViewExample"
		model="sas_model2_JDBCTableViewExample"
		actionProvider="sas_actionProvider_JDBCTableViewExample"
		scope="session">
        <sas:RelationalMenuBar />
    </sas:TableViewComposite>

	<sas:Label id="label1" text="Back To Employee View"
		URL="JDBCTableViewExample"/>
</body>
</html>

For more information and options, see the Web Application Example Templates paper.

Step 6: Finish the project

  1. Build the project.
  2. Start the IOM Spawner by selecting Start Menu [arrow] SAS AppDev Studio [arrow] Services [arrow] SAS V9.1 [arrow] Start SAS V9.1 IOM Spawner.
  3. Start the Java Web server.
  4. Execute in browser.