25997 - How to create a JSP TableView that connects to a JDBC datasource

Sample 25997: How to create a JSP TableView that connects to a JDBC datasource

Overview

This sample shows a JSP TableView connecting to data via a JDBC connection, which involves creating a TableViewComposite. The TableViewComposite is a TransformationBean^TM that is composed of other TransformationBeans that include the following subcomponents: MenuBar, NavigationBar, TableView, Title and Footer.

Here are the steps for creating a simple TableView:

Create a TableView.
Create a TableModel.
Set the model on the TableView.
Create an ActionProvider.
Set the ActionProvider on the TableView.
Render the TableView.

The code shown on the Full Code tab of this sample is JSP and servlet code that is generated by the JDBC TableView Servlet template.

Changing Data

To change the data that you are connecting to, edit the following line in the Java file:

String jdbcQuery = "select * from sashelp.prdsale";

TableView vs TableViewComposite

The main difference between the TableViewComposite tag (used in this sample) and the TableView tag is that the TableViewComposite tag with the RelationalMenuBar tag shows a limited number of rows at a time and enables you to scroll through the remaining rows. The TableView displays all of the rows in the table on one page. To modify this sample to use the TableView tag instead of the TableViewComposite tag, make the following changes in the JSP file.

    <sas:TableViewComposite id="sas_TableView1_JDBCTableViewExample" model="sas_model_JDBCTableViewExample" actionProvider="sas_actionProvider_JDBCTableViewExample" scope="session">
       <sas:RelationalMenuBar />
    </sas:TableViewComposite>

    <sas:TableView id="sas_TableView1_JDBCTableViewExample" model="sas_model_JDBCTableViewExample" actionProvider="sas_actionProvider_JDBCTableViewExample" scope="session">
    </sas:TableView>

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 TableView Servlet template.
The template will generate a servlet and a JSP file, which, by default, are named JDBCTableViewExampleControllerServlet.java and JDBCTableViewExampleViewer.jsp. The contents of these files is similar to what is shown below, except that your machine information will be specified. The code below is included for reference purposes.
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 JDBCTableViewExampleViewer.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:TableViewComposite id="sas_TableView1_JDBCTableViewExample" model="sas_model_JDBCTableViewExample" actionProvider="sas_actionProvider_JDBCTableViewExample" scope="session">
		<sas:RelationalMenuBar />
	</sas:TableViewComposite>
</body>
</html>

Code for JDBCTableViewExampleControllerServlet.java

Required changes

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 JDBCTableViewExampleControllerServlet.java ...

The jdbc connection string.

private static final String JDBC_DATABASE_URL = "jdbc:sasiom://machine:8591";

The URL mapping for the action provider, which must match the servlet mapping for the servlet.

sas_actionProvider.setControllerURL(request.getContextPath()+ "/JDBCTableViewExample");

The jsp page that the servlet forwards to.

RequestDispatcher rd = getServletContext().getRequestDispatcher("/JDBCTableViewExampleViewer.jsp");


package servlets;

import java.io.*;
import java.util.Properties;
import javax.servlet.*;
import javax.servlet.http.*;
import com.sas.actionprovider.HttpActionProvider;
import com.sas.servlet.util.BaseUtil;
import com.sas.storage.jdbc.JDBCConnection;
import com.sas.storage.jdbc.JDBCToTableModelAdapter;
import com.sas.util.SasPasswordString;
import com.sas.web.keys.ComponentKeys;
import listeners.ExamplesSessionBindingListener;

public class JDBCTableViewExampleControllerServlet 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_JDBCTableViewExample";
	// Global webapp JDBC variables
	private static final String	SAS_MODEL = "sas_model_JDBCTableViewExample";
	private static final String	JDBC_CONNECTION = "sas_JDBCConnection_JDBCTableViewExample";
	private static final String	JDBC_DRIVER_NAME = "com.sas.rio.MVADriver";
	private static final String	JDBC_DATABASE_URL = "jdbc:sasiom://machine: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() + "/JDBCTableViewExample");
				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);
				}
			}

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

			//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 ServletException(e);
				}
			}
		}

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

    }
}