26034 - How to Create a JSP TreeView That Uses a JDBC Connection to Access Data

Sample 26034: How to Create a JSP TreeView That Uses a JDBC Connection to Access Data

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.
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"%>

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

    <sas:TreeView id="treeView1" model="sas_model_JDBCDefaultExample"
       nodeLoading="PROGRESSIVE" selectionMode="SINGLE_SELECTION" scope="session">
	</sas:TreeView>

</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 dataset
createLevelDataSet(sas_JDBCConnection);
(Optional) Remove the createLevelDataSet and insertRow methods that are used to create the test dataset

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);
				}
			}

			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 {
					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 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
				+ "')");
	}

	public void createLevelDataSet(JDBCConnection connection) {
		try {
			java.sql.Statement stmt = null;

			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, "http://www.sas.com");
			insertRow(stmt, "Sub Root 1", i++, 2, "SubRoot1", "true", closed,
					open, "http://www.sas.com");
			insertRow(stmt, "Leaf 11", i++, 3, "leaf11", "", leaf, "",
					"http://www.sas.com");
			insertRow(stmt, "Leaf 12", i++, 3, "leaf12", "", leaf, "",
					"http://www.sas.com");
			insertRow(stmt, "Sub Root 13", i++, 3, "subroot13", "", closed,
					open, "http://www.sas.com");
			insertRow(stmt, "Leaf 131", i++, 4, "leaf131", "", leaf, "",
					"http://www.sas.com");
			insertRow(stmt, "Leaf 132", i++, 4, "leaf132", "", leaf, "",
					"http://www.sas.com");
			insertRow(stmt, "Sub Root 2", i++, 2, "subroot2", "",
					"arrow-right-end-default.gif",
					"arrow-down-end-default.gif", "http://www.sas.com");
			insertRow(stmt, "Leaf 21", i++, 3, "leaf21", "", leaf, "",
					"http://www.sas.com");
			insertRow(stmt, "Leaf 22", i++, 3, "leaf22", "", leaf, "",
					"http://www.sas.com");

			if (stmt != null) {
				stmt.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

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.

Date Modified:	2008-08-06 14:31:25
Date Created:	2006-02-17 12:34:27

Product Family	Product	Host	Product Release		SAS Release
Product Family	Product	Host	Starting	Ending	Starting	Ending
SAS System	SAS AppDev Studio	Microsoft Windows Server 2003 Enterprise Edition	3.2		9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Datacenter Edition	3.2		9.1 TS1M3 SP4
		Microsoft Windows NT Workstation	3.2		9.1 TS1M3 SP4
		Microsoft Windows 2000 Professional	3.2		9.1 TS1M3 SP4
		Microsoft Windows 2000 Server	3.2		9.1 TS1M3 SP4
		Microsoft Windows 2000 Datacenter Server	3.2		9.1 TS1M3 SP4
		Microsoft Windows 2000 Advanced Server	3.2		9.1 TS1M3 SP4
		Microsoft® Windows® for x64	3.2		9.1 TS1M3 SP4
		Windows Vista	3.2		9.1 TS1M3 SP4
		Microsoft Windows XP Professional	3.2		9.1 TS1M3 SP4
		Microsoft Windows Server 2003 Standard Edition	3.2		9.1 TS1M3 SP4

E-mail This Page

Knowledge Base

Sample 26034: How to Create a JSP TreeView That Uses a JDBC Connection to Access Data

Overview

Additional Documentation

Instructions

Code for JDBCDefaultExampleViewer.jsp

Code for JDBCDefaultExampleControllerServlet.java

Required changes

Operating System and Release Information

E-mail:
Password