Creating the DynamicQueryDataserverController

Viewpoint
Teradata Viewpoint is Teradata's strategic and innovative SOV (single operational view) for Teradata DB, Aster, and HDP Hadoop systems management and monitoring that enables Teradata's Unified Data Architecture (UDA).
Teradata Employee

Creating the DynamicQueryDataserverController

Once we have implemented the ConnectionManager to successfully retrieved our SQL query data, we need to wire it up so we make ajax calls to the server and run a query using the ConnectionManager. This is where the DynamicQueryDataserverController comes in.

Step 1: Creating the DynamicQueryDataserverController

The DynamicQueryDataserverController is a new class that we will create and implement. When we are finished, we will be able to dynamically invoke a call to this class that will query the database with a query we have submitted via ajax. We want to add the class to the com.teradata.portlets.dynamicquery.controllers package and call it DynamicQueryDataserverController. There are three elements that we will need to implement in this class:

  1. We need to extend the Controller class
  2. We will also be using a DynamicQueryManager object to query the database, so we will add that as a member variable
  3. We need to create the executeQuery() function that will be responsible for retrieving the user's parameter settings, executing the query and constructing the TableWidget object from the result set
DynamicQuery\src\java\com\teradata\portlets\dynamicquery\controllers\DynamicQueryDataserverController.java


package com.teradata.portlets.dynamicquery.controllers;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.sql.ResultSet;

import org.springframework.context.support.ResourceBundleMessageSource;

import com.teradata.portlets.dynamicquery.exception.DynamicQueryException;
import com.teradata.portlets.dynamicquery.service.DynamicQueryManager;
import com.teradata.tags.widgets.model.TableWidget;
import com.teradata.portlets.mvc.Controller;
import com.teradata.portlets.mvc.ControllerContext;

/**
* The DynamicQueryDataserverController is a controller that is invoked dynamically via an ajax
* call to retrieve data from a specifed database.
*/
public class DynamicQueryDataserverController implements Controller
{
/**
* The DynamicQueryManager object used to execute the query. Injected by Spring at runtime
*/
private DynamicQueryManager manager;

private ResourceBundleMessageSource messageSource = null;

/**
* The entry point for a call to the /DynamicQueryPortlet/dataserver/executeQuery URL on the
* client-side. This function is used to retrieve the query parameters specified by the user
* and execute the query on the system. A TableWidget object is then populated with the query
* result set. The TableWidget object is then added to the view.
*/
public void executeQuery(ControllerContext ctx)
{
final String CONTENT_TYPE = "text/html";
final String CONTEXT = "context";
final String SYSTEM = "system";
final String USERNAME = "username";
final String PASSWORD = "password";
final String SQL = "sql";
final String STATUS_MESSAGE = "statusMessage";
final String QUERY_RESULTS = "queryResults";
final String QUERY_FAILED = "queryFailed";

// Set the name of the view
ctx.setViewName("executeQuery");
// The context id of the portlet
final String context = ctx.getParameter(CONTEXT);
// Get the username parameter from the request
final String username = ctx.getParameter(USERNAME);
// Get the password parameter from the request
final String password = ctx.getParameter(PASSWORD);
// Get the system id parameter from the request
final Integer systemId;
// Execute the query with the user specified parameters
final ResultSet resultSet;
// The TableWidget object to store the query data in
TableWidget table;
// The SQL query to execute
String sql = null;

try
{
// Get the SQL statement parameter from the request
sql = ctx.getParameter(SQL);
// Decode the SQL since it was encoded for the POST
sql = URLDecoder.decode(sql, "UTF-8");
}
catch(final UnsupportedEncodingException uEE)
{
// simply catch the exception since it should never happen; the case will be handled
// below
}

try
{
// Parse the system parameter to get the system id
systemId = Integer.parseInt(ctx.getParameter(SYSTEM));

if(systemId == null || username == null || password == null || sql == null)
{
throw new DynamicQueryException("One or more parameters could not be " +
"retrieved!");
}
// Execute the query and construct the TableWidget object from the result set of the
// query
table = manager.executeQuery(systemId, username, password, sql);
ctx.addViewObject(STATUS_MESSAGE, "Query succeeded! " + table.getData().length
+ " rows returned!");
ctx.addViewObject(QUERY_RESULTS, table);
}
catch(final DynamicQueryException dQE)
{
ctx.addViewObject(STATUS_MESSAGE, "Query failed! Error: " + dQE.getMessage());
// Set the "queryFailed" flag to true
ctx.addViewObject(QUERY_FAILED, true);
}
catch (final Exception e)
{
ctx.addViewObject(STATUS_MESSAGE, "Failed to process query results! Error: "
+ e.getMessage());
// Set the "queryFailed" flag to true
ctx.addViewObject(QUERY_FAILED, true);
}

// Set the content type of the response
ctx.setContentType(CONTENT_TYPE);
}

/**
* Setter for manager.
*
* @param manager the manager to set
*/
public void setManager(final DynamicQueryManager manager)
{
this.manager = manager;
}

/**
* Setter for the message source. This is required so that Spring can wire up the
* implementation at runtime.
*
* @param value The MessageSource to use for looking up localized strings.
*/
public void setMessageSource(ResourceBundleMessageSource value)
{
messageSource = value;
}
}


Step 2: Configuring the DynamicQueryDataserverController

Now that we have written the DynamicQueryDataserverController, we need to hook it into Spring so we can make calls to it. We need to add our controller bean, so we need to first add its definition to the configuration file called applicationContext.xml in our \WEB-INF folder.

DynamicQuery\web\WEB-INF\applicationContext.xml

<beans>
<!-- Controllers -->
<!-- Add the following section below -->
<bean id="dynamicQueryDataserverController" class="
com.teradata.portlets.dynamicquery.controllers.DynamicQueryDataserverController">
<description>
The controller used to dynamically submit a SQL query
</description>
<property name="manager" ref="dynamicQueryManager" />
<property name="messageSource" ref="messageSource" />
</bean>
<!-- End add section -->
<!-- Auto generated bean definitions here -->
</beans>


Next, we will reference the bean. The dataserver-servlet.xml configuration file maps the DynamicQueryDataserverController class we created to a Spring URL handler. So when /executeQuery is called, our Spring URL handler maps the call to the specified controller:

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

<beans>
<!-- Existing configurations here -->

<!-- map requests to controllers by mapping url to the controller name -->
<bean id="handlerMapping" class="org.springframework.web.servlet.handler.SimpleUrlHandlerMapping">
<property name="mappings">
<props>
<prop key="/summary">dynamicQueryViewController</prop>
<prop key="/configuration">
dynamicQueryPreferencesController
</prop>
<!-- Add property below -->
<prop key="/executeQuery">
dynamicQueryDataserverController
</prop>
<!-- End add property -->
</props>
</property>
</bean>
</beans>


Now we can make calls to the URL /DynamicQueryPortlet/dataserver/executeQuery and we will be mapped to the executeQuery() function of the DynamicQueryDataserverController.

Tags (2)