Displaying Tabular Data using the DataGrid Widget

Extensibility
Extensibility covers the mechanisms by which you, as the user or developer, can extend the functionality of the Teradata Database, for example with the use of User Defined Functions, or UDFs.
Teradata Employee

Displaying Tabular Data using the DataGrid Widget

The following tutorial is a guide on how to implement the Viewpoint DataGrid Widget.  In this example we will show how the DataGrid Widget was incorporated into the Widgetopia Portlet.  The actual source code can be found in the Widgetopia Portlet supplied with the PDK starting from version 13.03.

What is the DataGrid Widget

The DataGrid Widget was created to address the issue of displaying large amounts of data in tabular format within portlets.  It is available starting from Viewpoint 13.03.  The widget includes an extensive list of built in features such as:

  • Data Paging with ability to jump between pages.
  • Column Sorting based on column type.
  • Column Filtering.
  • Custom Column Reordering and show/hide.
  • Locking/Fixed Columns.
  • Column Width Resizing.
  • Row Drilldowns.
  • Data Highlighting.
  • Data Caching and loading on demand.

What we will be doing in this tutorial

What we will do in this tutorial is take the same time zone data that is currently being displayed in the Widgetopia TableWidget example, and display it using the DataGrid instead.  We will start from incorporating the DataGrid tag in our summary.jsp page, and then creating the view controller and all the necessary back end support components.

Adding the DataGrid Widget tag to the JSP page

The first thing we will do is add an additional DIV tag in the summary.jsp page where we want the DataGrid to appear.  It is important that the DIV tag's ID  match what is supplied in the DataGrid tag.

Widgetopia\web\WEB-INF\portlet-jsp\summary.jsp:

...
<div id="dataGridDiv${context}" class="dataGridDiv${context}"></div>
...

The next thing we will do is add the DataGrid Widget JSP tag to the summary.jsp page in the Widgetopia Portlet.  For our example we will add this at the very bottom of the page.  For the tag we will be supplying the following attributes:

  • context (required): the unique context id of the portlet.
  • url (required): the target url for the ajax request to fetch data.
  • widgetdomId (required): the id of the containing div for the DataGrid widget.
  • width (optional): the width of the DataGrid in pixels.
  • height (optional): the height of the DataGrid in pixels.
  • pageSize (optional): the size of each page displayed by the DataGrid.

Widgetopia\web\WEB-INF\portlet-jsp\summary.jsp:

<vp:dataGrid context="${context}"
url="/WidgetopiaPortlet/dataserver/generateReport"
widgetDomId="dataGridDiv${context}"
width="454"
height="500"
pageSize="25" />

Hooking up the View Controller

The next step we construct the controller which will pass the necessary data from the back end service layer to the DataGrid Widget we added in summary.jsp.  We will name it the WidgetopiaDataGridController.

In the controller we will implement a method called generateReport(), which will be called by DataGrid widget tag in the summary page.  This method will construct the TableState object used by the DataGrid to generate the view, and also retrieve the data we want displayed from the back end service layer.

Widgetopia\src\java\com\teradata\portlets\widgetopia\controllers\WidgetopiaDataGridController.java

...
public void generateReport (ControllerContext context) throws JSONException
{
TableState newState = getTableStateParam(context);

//get the current preferences
WidgetopiaPreferences prefs = getPreferences(context);

//get the state of the datagrid
TableState savedState = prefs.getNormalTableState();

//if the request is to update the preference
if (isPreferenceUpdate(newState))
{
updateTableStatePreferences(context, prefs, savedState, newState);
}

//if the request is to get data
if (isDataRequest(context, newState))
{
//combine the current table data with the previous stored
TableState tableState = mergeTableStates(context, savedState, newState);

//get the new data for the datagrid
QueryResults results = widgetopiaManager.generateDataGridData(context.getTimeZone(), tableState.getFirst(),
tableState.getCount());
tableState.setResults(results);
tableState.setStatus(OK);

//put the data grid table state obj in the context so the widget can access it
context.addViewObject(TABLE_STATE, tableStateJSONBuilder.toJSON(tableState, context.getLocale()));
context.setViewName(REFRESH_DATAGRID);
}
}
...

The TableState object is a representation of the state of the DataGrid Widget.  It incorporates both the configuration settings of the widget, and any data that has been retrieved so far.

What you will need to do is get the current TableState object from the portlet context.  That will tell you what the current state of the DataGrid is - what page it is on, what columns are sorted, etc..

We will also need to identify what type of request is coming in from the DataGrid widget. 

There are two different types of requests:

  • Preference Update Request.
  • Data Update Request. 

Preference Update Request

A preference update request is one that persists the current configuration settings of the widget.  An example of such a request would be: resizing the column width, reordering columns in a report, etc..   The widget will send an HTTP request to the server with the "preferenceUpdate" flag set to true when this type of request is triggered.

Widgetopia\src\java\com\teradata\portlets\widgetopia\controllers\WidgetopiaDataGridController.java

/**
* Returns true if the tableState request is for preference update
*
* @param state the table state
* @return true if preference update, false otherwise
*/
private static boolean isPreferenceUpdate(TableState state)
{
return state != null && state.isPreferenceUpdate();
}

Data Update Request

A data update request is a request to retrieve new or additional data.  This typically happens when the user navigates to a different page.  When the user triggers such a request, the widget sends an HTTP request to the server with the "dataRequest" flag set to true. 

Widgetopia\src\java\com\teradata\portlets\widgetopia\controllers\WidgetopiaDataGridController.java

/**
* Returns true if the tableState request is for data
*
* @param ctx the controller context
* @param state the table state
* @return true if data request, false otherwise
*/
private static boolean isDataRequest(ControllerContext ctx, TableState state)
{
return Boolean.parseBoolean(ctx.getParameter(IS_REFRESH))
|| (state != null && state.isIncludeData());
}

When a data request is received, you will need to merge the current TableState with the previous saved state to preserve the settings.  You will then fetch the next page of data and place it into the merged TableState object to be passed back to the widget. 

Widgetopia\src\java\com\teradata\portlets\widgetopia\controllers\WidgetopiaDataGridController.java

/**
* Merge the existing table state with the new state
* @param context the portlet context
* @param savedState the saved table state
* @param newState the new table state
* @return the merged table state
*/
private TableState mergeTableStates(ControllerContext context, TableState savedState,
TableState newState)
{
// this ensures tableState gets correct page size in a use case where rewind request
// precedes initial datagrid request.
boolean isRefresh = Boolean.parseBoolean(context.getParameter(IS_REFRESH));
context.addViewObject(IS_REFRESH, isRefresh);

if (isRefresh)
{
savedState.setFirst(1);
}

// set the size of the page
savedState.setCount(DATA_GRID_PAGE_SIZE);

if (newState != null)
{
savedState.mergeDataRequest(newState);
}

savedState.setIncludeData(true);

return savedState;
}

Wiring up the Controller

Since we created a new WidgetopiaDataGridController, we will need to add it to the applicationContext.xml in order to wire it up.

Widgetopia\web\WEB-INF\applicationContext.xml

...
<bean id="widgetopiaDataGridController" class="com.teradata.portlets.widgetopia.controllers.WidgetopiaDataGridController">
<description>Controller for Data Grid</description>
<property name="widgetopiaManager" ref="widgetopiaManager"/>
<property name="messageSource" ref="messageSource" />
<property name="tableStateJSONBuilder" ref="widgetopiaTableStateJSONBuilder" />
</bean>

<bean id="widgetopiaTableStateJSONBuilder" class="com.teradata.portlets.json.datatable.TableStateJSONBuilder">
<constructor-arg index="0" ref="widgetopiaColumnDefinitionClass" />
<constructor-arg index="1" ref="messageSource" />
</bean>

<bean id="widgetopiaColumnDefinitionClass" class="java.lang.Class" factory-method="forName">
<constructor-arg index="0" value="com.teradata.portlets.widgetopia.model.WidgetopiaColumnDefinition" />
</bean>
...

We also need to map the request url to the new WigetopiaDataGridController.

Widgetopia\web\WEB-INF\dataserver-servlet.xml

<bean id="handlerMapping" class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping">
<property name="mappings">
<props>
<prop key="/updateTableData">widgetopiaViewController</prop>
<prop key="/updateSparklineData">widgetopiaViewController</prop>
<prop key="/myname">widgetopiaViewController</prop>
<prop key="/generateReport">widgetopiaDataGridController</prop>
</props>
</property>
</bean>

Creating the JSP return page that displays the JSON data need by the widget

The TableState object we created that contains the DataGrid settings and results need to be transformed into JSON so the widget can consume it.  We do this by calling the TableStateJSONBuilder, and then pass the generated JSON data to a simple jsp page for output and widget consumption.

Widgetopia\web\WEB-INF\portlet-jsp\refreshDataGrid.jsp

<%@ page contentType="text/html" language="java"%>

${tableState}

Back End Components

In order to support the DataGrid widget, there are several back end components that need to be created.

Implementing the TableColumnDefinition Interface

The TableColumnDefinition interface defines the methods that provide the necessary metadata for each of the columns in the DataGrid.  We will need to implement this interface and specify the column names and column types used in our DataGrid example.  Note, the columnName in the TableColumnDefinition needs to match the member name in the Model class (defined in the next section).

Here is a list of the standard column data types currently supported by the DataGrid widget:

TypeJava Class
Stringjava.lang.String
Integerjava.lang.Integer
Floatjava.lang.Float
Timestampjava.sql.Timestamp
Durationjava.lang.Integer
Checkboxjava.lang.Boolean
Iconany
Customany

Our example DataGrid will have three columns:

  • Time Zone - type String.
  • Time - type TimeStamp.
  • UTC offset - type Integer.

There are other column settings that can be set, such as turning on/off column filtering, hiding columns from the menu, and adding tool tips, etc..

Widgetopia\src\java\com\teradata\portlets\widgetopia\model\WidgetopiaColumnDefinition.java

public enum WidgetopiaColumnDefinition implements TableColumnDefinition
{
TIMEZONENAME("timeZoneName", TableColumnState.Type.STRING, true, false, null),
TIMEZONETIME("timeZoneTime", TableColumnState.Type.TIMESTAMP, false, false, null),
TIMEZONEOFFSET("timeZoneOffset", TableColumnState.Type.INTEGER, true, false, null);
private static final Object[] EMPTY_OBJECT_ARRAY = new Object[0];
private static final String PROPERTY_PREFIX = "widgetopia.datagrid.";
private String columnName;
private TableColumnState.Type type;
private boolean filterable;
private boolean hiddenFromMenu;
private String menuToolTip;
private List<TableColumnThresholdDefinition> thresholds;

/**
* Constructor
* @param columnName the name of the column
* @param type the data type of the column
* @param filterable defines whether the column can be sorted
* @param hiddenFromMenu defines whether the column is initially hidden from display
* @param menuTooltip defines the tool tip value
*/
private WidgetopiaColumnDefinition(final String columnName, final TableColumnState.Type type,
final boolean filterable, final boolean hiddenFromMenu, final String menuTooltip)
{
this.columnName = columnName;
this.type = type;
this.filterable = filterable;
this.hiddenFromMenu = hiddenFromMenu;
this.menuToolTip = menuTooltip;
}
...

Note the PROPERTY_PREFIX field is prepended to the column names to create an identifier for the column display name value.  They need to match the case-sensitive key name in the messages.properties file.

Widgetopia\src\resources\messages.properties

widgetopia.datagrid.TIMEZONENAME=Time Zone
widgetopia.datagrid.TIMEZONETIME=Time
widgetopia.datagrid.TIMEZONEOFFSET=UTC Offset(Hours)

# TOOLTIPS
propertyKeyName=teradata_portlets_widgetopia

 

Creating the Model class

We will need to create a model class to hold the column data needed for our example.

Widgetopia\src\java\com\teradata\portlets\widgetopia\model\WidgetopiaDataGridModel.java

public class WidgetopiaDataGridModel extends BaseModel
{
private String timeZoneName;
private Timestamp timeZoneTime;
private int timeZoneOffset;
... 

Adding preference items to support the DataGrid preferences

In order for the DataGrid to retain its settings after a user has logged out, the settings need to be saved in the portlet's preferences.  To do this, we will need to add the TableState object to the portlet's existing PreferencesModel with a PreferenceReference annotation.  You can add any number of objects, including different objects for normal view versus maximized view of a portlet if desired.

Widgetopia\src\java\com\teradata\portlets\widgetopia\model\WidgetopiaPreferences.java

public class WidgetopiaPreferences extends PreferencesModel
{
private static final long serialVersionUID = 6960306072306995665L;
private static final WidgetopiaColumnDefinition[] defaultNormalViewColumns = { WidgetopiaColumnDefinition.TIMEZONENAME, WidgetopiaColumnDefinition.TIMEZONETIME, WidgetopiaColumnDefinition.TIMEZONEOFFSET };
private static final WidgetopiaColumnDefinition[] defaultMaximizedViewColumns = { WidgetopiaColumnDefinition.TIMEZONENAME, WidgetopiaColumnDefinition.TIMEZONETIME, WidgetopiaColumnDefinition.TIMEZONEOFFSET };
private static final WidgetopiaColumnDefinition DEFAULT_SORT_COLUMN = WidgetopiaColumnDefinition.getDefaultSortColumn();

@PreferenceReference
private TableState normalTableState;

@PreferenceReference
private TableState maximizedTableState;

/**
* alert logs that are monitored, mapped by system
*/
public WidgetopiaPreferences()
{
normalTableState = new TableState();
maximizedTableState = new TableState();
initDefaultColumns(defaultNormalViewColumns, normalTableState);
initDefaultColumns(defaultMaximizedViewColumns, maximizedTableState);
}

/**
* Initialize the default columns for the Data Grid Example
* @param defaultColumns the default columns
* @param state the state of the table
*/
private void initDefaultColumns(final WidgetopiaColumnDefinition[] defaultColumns,
final TableState state)
{
for (final WidgetopiaColumnDefinition column : WidgetopiaColumnDefinition.values())
{
final TableColumnState col = new TableColumnState();
col.setName(column.name());
col.setColumnName(column.getColumnName());
col.setHidden(true);
if (column == DEFAULT_SORT_COLUMN)
{
col.setSortOrder(1);
col.setSortAscending(false);
}
for (final WidgetopiaColumnDefinition summaryColumn : defaultColumns)
{
if (column == summaryColumn)
{
col.setHidden(false);
break;
}
}
state.addColumn(col);
}
}
...

Retrieving the data to be displayed in our DataGrid

For our example we will generate 80 rows of random time zone data.  The data will be generated and then returned in a QueryResults object.  The QueryResults object will then be passed back to the view controller generateReport() method, and set as the results field in the TableState object.

Widgetopia\src\java\com\teradata\portlets\widgetopia\service\WidgetopiaManager.java

...
/**
* Get the query results for the DataGrid
* @param userProfileTimeZone the user's time zone
* @param currentPageNum the current page number
* @param numPerPage the number of rows displayed on each page
* @return the query results for the data grid
*/
public QueryResults<WidgetopiaDataGridModel> generateDataGridData(TimeZone userProfileTimeZone, int currentPageNum, int numPerPage)
{
QueryResults<WidgetopiaDataGridModel> queryResult = new QueryResults<WidgetopiaDataGridModel>();
List<WidgetopiaDataGridModel> list = null;
List<WidgetopiaDataGridModel> pageResult = null;

//get the data
list = generateDataGridSampleData(userProfileTimeZone);

int from = currentPageNum - 1;
int to = from + numPerPage;
to = Math.min(list.size(), to);

try
{
pageResult = list.subList(from, to);
}
catch (Exception e)
{
pageResult = Collections.emptyList();
}

queryResult.setRowCount(list.size());
queryResult.setResults(pageResult);

return queryResult;
}

/**
* Generate the sample data for the DataGrid Example
* @param userProfileTimeZone the user's time zone
* @return list of timezones
*/
private List<WidgetopiaDataGridModel> generateDataGridSampleData(TimeZone userProfileTimeZone)
{
List<WidgetopiaDataGridModel> list = new ArrayList<WidgetopiaDataGridModel>();

//number of rows of data
int rows = 80;
String[] tzIDs = TimeZone.getAvailableIDs();
Random rng = new Random();
WidgetopiaDataGridModel timeZone;

Calendar calendar = Calendar.getInstance();

for (int i = 0; i < rows; i++)
{
int tzIndex = rng.nextInt(Integer.MAX_VALUE) % tzIDs.length;
TimeZone tz = TimeZone.getTimeZone(tzIDs[tzIndex]);

String tzName = tz.getDisplayName();
int tzOffset = tz.getOffset(calendar.getTimeInMillis()) / MS_IN_HOUR;

//table factors in current user profile time zone, so we need to remove that for our example
int tableTimeZoneOffset = tzOffset - userProfileTimeZone.getOffset(calendar.getTimeInMillis()) / MS_IN_HOUR;

timeZone = new WidgetopiaDataGridModel();
timeZone.setTimeZoneName(tzName);

timeZone.setTimeZoneTime(new Timestamp(calendar.getTimeInMillis() + (tableTimeZoneOffset * 3600000)));
timeZone.setTimeZoneOffset(tzOffset);

list.add(timeZone);
}

return list;
}
...