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 TransformationBeanTM 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>
|
to
<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);
}
}
|
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.
This sample shows a JSP TableView connecting to data via a JDBC connection.
Date Modified: | 2008-07-02 12:10:11 |
Date Created: | 2006-01-04 11:21:34 |
Operating System and Release Information
SAS System | SAS AppDev Studio | Microsoft Windows XP Professional | 3.3 | | 9.1 TS1M3 SP4 | |
Microsoft Windows Server 2003 Standard Edition | 3.3 | | 9.1 TS1M3 SP4 | |
Windows Vista | 3.3 | | 9.1 TS1M3 SP4 | |
Microsoft® Windows® for x64 | 3.3 | | 9.1 TS1M3 SP4 | |
Microsoft Windows NT Workstation | 3.3 | | 9.1 TS1M3 SP4 | |
Microsoft Windows Server 2003 Datacenter Edition | 3.3 | | 9.1 TS1M3 SP4 | |
Microsoft Windows Server 2003 Enterprise Edition | 3.3 | | 9.1 TS1M3 SP4 | |
Microsoft Windows 2000 Professional | 3.3 | | 9.1 TS1M3 SP4 | |
Microsoft Windows 2000 Advanced Server | 3.3 | | 9.1 TS1M3 SP4 | |
Microsoft Windows 2000 Datacenter Server | 3.3 | | 9.1 TS1M3 SP4 | |
Microsoft Windows 2000 Server | 3.3 | | 9.1 TS1M3 SP4 | |