26052 - How to Create a JSP DualTreeSelector that connects to a JDBC Data Source

Sample 26052: How to Create a JSP DualTreeSelector that connects to a JDBC Data Source

Overview

The DualTreeSelector is a TransformationBean^TM that generates the appropriate HTML 4.0 and JavaScript for creating a tree-to-tree selector. A DualTreeSelector uses two tree views that enable you to move single or multiple items between the tree views and submit the selected item(s) that are on the target tree view.

Here are the steps for creating a simple DualTreeSelector:

Create a TreeModel.
Create a DualTreeSelector
Set the model on the DualTreeSelector.
Render the DualTreeSelector.

CAUTION: This sample does not create a complete Web application. The purpose of the sample is to demonstrate how to use a particular component. The sample 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 sample will persist until the associated HTTPSession times out. If this sample is used in a multi-user environment, it is possible to exhaust the available resources until HTTPSessions time out and frees up their associated resources.

The sample is based on the JDBC Default Servlet template that is available in SAS^® AppDev Studio. The Full Code tab in this sample contains instructions for modifying the template project.

Additional Documentation

For a list of samples for common tasks, see SAS Note 32218.
The SAS AppDev Studio Developer's Site contains detailed information that will assist you when developing Web applications with SAS AppDev Studio, including an API reference and the custom tag reference for the sas tagset.

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Instructions

Build a new Web application project in SAS AppDev Studio by using the JDBC Default Servlet template.
The template will generate a servlet and a JSP file, which, by default, are named JDBCDefaultExampleControllerServlet.java and JDBCDefaultExampleViewer.jsp. Replace the contents of these files with the code below.
Create a new jsp page called pushButtonSubmit.jsp using the code below.
Execute the servlet on the test Tomcat server.

Tip: For help with building a Web application project and testing a Web application, see SAS Note 32218.

Code for JDBCDefaultExampleViewer.jsp


<%@ taglib uri="http://www.sas.com/taglib/sas" prefix="sas" %>
<%@ page pageEncoding="UTF-8"%>
<%@page import="javax.swing.tree.TreeModel"%>

<html>
<head>
    <link href="styles/sasComponents.css" rel="STYLESHEET" type="text/css">
</head>
<body>

<jsp:useBean id="dualselector" class="com.sas.servlet.tbeans.dualselector.html.DualTreeSelector" scope="session">
	  <jsp:setProperty name="dualselector" property="name" value="dualselector"/>        
</jsp:useBean>

<%
	com.sas.servlet.tbeans.form.html.Form yourForm = new com.sas.servlet.tbeans.form.html.Form();
	yourForm.setName("myFormName");
	yourForm.setMethod("get");
	yourForm.setAction("pushButtonSubmit.jsp");
	yourForm.writeHeader(out); 
	
	// disable the dual selector form, so that it uses the one we create here
	dualselector.setFormEnabled(false);
	dualselector.setFormObject(yourForm);
	
	//since the form object on the dualselector is not enabled, 
	//user is responsible for writing the footer.
	yourForm.writeFooter(out); 
	
%> 

<%
	// write out the dual selector
	TreeModel model = (TreeModel)session.getAttribute("sas_model_JDBCDefaultExample");
	dualselector.setModel(model);
	dualselector.setResponse(response);
	dualselector.setRequest(request);
	dualselector.write(out);
%>

</body>
</html>

Code for pushButtonSubmit.jsp


<html>
<head>
<title>Submit PushButton Processing</title> 
</head>

<body>

<%   
	java.util.Enumeration params = request.getParameterNames(); 
	while(params.hasMoreElements()) { 
		String property = (String)params.nextElement(); 
		out.println("<p>The request attributes are: '" + property + "'</p>"); 
		String[] cmbObj2 = (String[])request.getParameterValues(property); 
		for(int i=0; i < cmbObj2.length; i++){ 
			out.println("<p>The object was: '" + cmbObj2[i] + "'</p>");
		}
	} 
%> 

</body>
</html>

Code for JDBCDefaultExampleControllerServlet.java

For demonstration purposes, the code below creates a temporary data set in the Work library. To use a permanent data set, make the following changes.

Update the SQL query to point to the data set that you want to use.
String jdbcQuery = "select * from work.leveltree";
Comment out or remove the following line that calls the methods to create the test data set.
createLevelDataSet(sas_JDBCConnection);
(Optional) Remove the createLevelDataSet and insertRow methods that are used to create the test data set.

Required changes

Edit the value of the JDBC_DATABASE_URL field to match your environment. If you entered values for your JDBC connection while creating the project template, then use the generated values to update the code below. The code uses the following as an example.
private static final String JDBC_DATABASE_URL = "jdbc:sasiom://servername:8591";
If you did not use the default values during the generation of the project, you might need to update the following values.
- The package for the servlet.
  package servlets;
- The servlet name.
  public class JDBCDefaultExampleControllerServlet ...
- The URL mapping for the action provider, which must match the servlet mapping for the servlet.
  sas_actionProvider.setControllerURL(request.getContextPath()+ "/JDBCDefaultExampleViewer.jsp");


package servlets;

import java.io.IOException;
import java.sql.Statement;
import java.util.Properties;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import listeners.ExamplesSessionBindingListener;

import com.sas.actionprovider.HttpActionProvider;
import com.sas.servlet.tbeans.models.JDBCToTreeViewModelAdapter;
import com.sas.servlet.util.BaseUtil;
import com.sas.storage.jdbc.JDBCConnection;
import com.sas.util.SasPasswordString;
import com.sas.web.keys.ComponentKeys;

public class JDBCDefaultExampleControllerServlet extends
		javax.servlet.http.HttpServlet {
	// Declare a default version ID since parent class implements
	// java.io.Serializable
	private static final long serialVersionUID = 1L;
	// Global webapp Strings
	private static final String ACTION_PROVIDER = "sas_actionProvider_JDBCDefaultExample";
	// Global webapp JDBC variables
	private static final String SAS_MODEL = "sas_model_JDBCDefaultExample";
	private static final String JDBC_CONNECTION = "sas_JDBCConnection_JDBCDefaultExample";
	private static final String JDBC_DRIVER_NAME = "com.sas.rio.MVADriver";
	private static final String JDBC_DATABASE_URL = "jdbc:sasiom://servername:8591";

	private static final Properties staticJDBCConnectionProperties = new Properties();
	static { // Specify JDBC connection properties here
		// Use: staticJDBCConnectionProperties.put("", "");
	}

	/*
	 * doPost() Respond to the Post message.
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// Note: Calling doGet to provide same behavior to POST and GET HTTP
		// methods.
		doGet(request, response);
	}

	/*
	 * doGet() Respond to the Get message.
	 */
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// Note: Add User DO_GET code here
		HttpSession session = request.getSession();

		// Ensure character set is specified before calling
		// response.getWriter().
		String charset = BaseUtil.getOutputCharacterEncoding(request);
		response.setContentType("text/html; charset=" + charset);
		// Setup the ActionProvider
		HttpActionProvider sas_actionProvider = null;
		synchronized (session) {
			if (session != null) {
				sas_actionProvider = (HttpActionProvider) session
						.getAttribute(ACTION_PROVIDER);
			}
			// if ActionProvider is null, create one and put it on the session
			if (sas_actionProvider == null) {
				sas_actionProvider = new HttpActionProvider();
				sas_actionProvider.setLocale(request.getLocale());
				sas_actionProvider.setControllerURL(request.getContextPath()
						+ "/JDBCDefaultExample");
				sas_actionProvider.setName(ACTION_PROVIDER);
				// store object in its scope
				if (session != null)
					session.setAttribute(ACTION_PROVIDER, sas_actionProvider);
			}
			// else execute the ActionProvider command
			else {
				sas_actionProvider.executeCommand(request, response, response
						.getWriter());
			}
		}

		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();
					Properties connectionProperties = new Properties();
					// Add static JDBC connection properties
					connectionProperties.putAll(staticJDBCConnectionProperties);
					// Add additional JDBC connection properties
					String username = sc.getInitParameter("username");
					if (username != null && username.length() > 0) {
						connectionProperties.put("user", username);
						String password = sc.getInitParameter("password");
						if (password != null && password.length() > 0) {
							// Add password property, decode if SAS password
							// encoded
							connectionProperties.put("password",
									SasPasswordString.decode(password));
						}
					}
					sas_JDBCConnection.setConnectionInfo(connectionProperties);
					session.setAttribute(JDBC_CONNECTION, sas_JDBCConnection);
				} catch (Exception e) {
					throw new ServletException(e);
				}
			}

			// Use the sample table that is generated by this project
			String jdbcQuery = "select * from work.leveltree";

			// Setup the JDBC model adapter
			JDBCToTreeViewModelAdapter adapter = null;
			if (session != null) {
				adapter = (JDBCToTreeViewModelAdapter) session
						.getAttribute(SAS_MODEL);
			}
			if (adapter == null) {
				try {
					// create the sample data set
					createLevelDataSet(sas_JDBCConnection);

					adapter = new JDBCToTreeViewModelAdapter();
					adapter.setLevelColumnName("level");
					adapter.setIndexColumnName("index");
					adapter.setTextColumnName("text");
					adapter.setTrimUsed(true);
					adapter.setQueryStatement(jdbcQuery);
					adapter.setModel(sas_JDBCConnection);

					// Set it on the session
					if (session != null) {
						session.setAttribute(SAS_MODEL, adapter);
						ExamplesSessionBindingListener.getInstance(session)
								.addAdapter(adapter);
					}
				} catch (Exception e) {
					throw new ServletException(e);
				}
			}
		}

		// Forward the request to the JSP for display
		String sas_forwardLocation = request
				.getParameter(ComponentKeys.FORWARD_LOCATION);
		if (sas_forwardLocation == null) {
			sas_forwardLocation = "/JDBCDefaultExampleViewer.jsp";
		}
		RequestDispatcher rd = getServletContext().getRequestDispatcher(
				sas_forwardLocation);
		rd.forward(request, response);

	}

	public void createLevelDataSet(JDBCConnection connection) {
		try {
			java.sql.Statement stmt = null;
			java.sql.DatabaseMetaData metaData = connection.getMetaData();
			java.sql.ResultSet sampleTableResultSet = metaData.getTables(null,
					"WORK", "LEVELTREE", new String[] { "TABLE" });
			if (!sampleTableResultSet.next()) { // create the example table if
				// it was not found
				String treeDataSetName = "WORK.LEVELTREE";
				int level = 1;
				int index = 1;
				stmt = connection.createStatement(
						java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
						java.sql.ResultSet.CONCUR_UPDATABLE);
				stmt
						.executeUpdate("CREATE TABLE "
								+ treeDataSetName
								+ " (TEXT VARCHAR(32), INDEX INTEGER, LEVEL INTEGER"
								+ ", NAME VARCHAR(32), EXPANDED VARCHAR(6)"
								+ ", DEFAULTIMAGE VARCHAR(32), EXPANDEDIMAGE VARCHAR(32)"
								+ ", URL VARCHAR(32))");
				String open = "arrow-down-default.gif";
				String closed = "arrow-right-default.gif";
				String leaf = "MultiplyBlack.gif";
				int i = 1;
				insertRow(stmt, "Root Node", i++, 1, "RootNode", "true",
						closed, open, "RootNode");
				insertRow(stmt, "Sub Root 1", i++, 2, "SubRoot1", "true",
						closed, open, "SubRoot1");
				insertRow(stmt, "Leaf 11", i++, 3, "leaf11", "", leaf, "",
						"Leaf 11");
				insertRow(stmt, "Leaf 12", i++, 3, "leaf12", "", leaf, "",
						"Leaf 12");
				insertRow(stmt, "Sub Root 13", i++, 3, "subroot13", "", closed,
						open, "Sub Root 13");
				insertRow(stmt, "Leaf 131", i++, 4, "leaf131", "", leaf, "",
						"Leaf 131");
				insertRow(stmt, "Leaf 132", i++, 4, "leaf132", "", leaf, "",
						"Leaf 132");
				insertRow(stmt, "Sub Root 2", i++, 2, "subroot2", "",
						"arrow-right-end-default.gif",
						"arrow-down-end-default.gif", "Sub Root 2");
				insertRow(stmt, "Leaf 21", i++, 3, "leaf21", "", leaf, "",
						"Leaf 21");
				insertRow(stmt, "Leaf 22", i++, 3, "leaf22", "", leaf, "",
						"Leaf 22");
			}
			if (stmt != null) {
				stmt.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void insertRow(Statement stmt, String text, int index, int level,
			String name, String expanded, String defImage, String expImage,
			String url) throws Exception {
		stmt.executeUpdate("INSERT INTO WORK.LEVELTREE VALUES (" + "  '" + text
				+ "', " + index + " , " + level + " ,'" + name + "','"
				+ expanded + "','" + defImage + "','" + expImage + "','" + url
				+ "')");
	}

}