Using the Connection Manager

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

Using the Connection Manager

Once we have created our ConnectionManager interface and ConnectionManagerImpl class that implements the interface, we need to make our implementation useful by hooking it into our DynamicQueryManager since it handles our business logic.

Step 1: Injecting the ConnectionManager

First, we must make use of the ConnectionManager by telling Spring to inject a ConnectionManager object into our DynamicQueryManager at runtime. To do this, we must wire up Spring to properly initialize and inject a ConnectionManager object.

We will add a private instance of the ConnectionManager object to our DynamicQueryManagerImpl (and an accompanying setter) so Spring can inject the ConnectionManager at runtime:

DynamicQuery\src\java\com\teradata\portlets\dynamicquery\service\impl\DynamicQueryManagerImpl.java 


//***Add the following section***/
import java.util.List;
import com.teradata.dcs.config.dao.SystemDAO;
import com.teradata.portlets.dynamicquery.service.ConnectionManager;
//***End add section***/

public class DynamicQueryManagerImpl implements DynamicQueryManager
{
// Auto generated member variables here

//***Add the following section***/

/**
* The SystemDAO used to retrieve Teradata system information. Injected by Spring at runtime.
*/
private SystemDAO systemDAO;

/**
* The ConnectionManager used to query the database. Injected by Spring at runtime.
*/
private ConnectionManager connectionManager;

/**
* Setter for connectionManager.
*
* @param connectionManager the connectionManager to set
*/
public void setConnectionManager(final ConnectionManager connectionManager)
{
this.connectionManager = connectionManager;
}

/**
* Setter for systemDAO.
*
* @param systemDAO
* the systemDAO to set
*/
public void setSystemDAO(final SystemDAO systemDAO)
{
this.systemDAO = systemDAO;
}

//***End add section***/

// Auto generated functions here
}


Next we need to declare the systemDAO bean so Spring can inject it at runtime. We do this in the dataSourceiBatis.xml file:

DynamicQuery\web\WEB-INF\dataSourceiBatis.xml

<beans>
<!-- Auto generated bean definitions -->

<!-- DAO's -->
<!-- Add the following section -->
<bean id="systemDAO" class="com.teradata.dcs.config.dao.ibatis.SystemDAOiBatis">
<description>DAO for accessing System information</description>
<property name="sqlMapClient" ref="dcsSqlMapClient" />
</bean>
<!-- End add section -->
<bean id="preferencesDAO"
class="com.teradata.portal.shared.dao.ibatis.PreferencesDAOiBatis">
<description>DAO for accessing preferences table</description>
<property name="sqlMapClient" ref="tdPortalMapClient" />
</bean>
</beans>

Now we need to tell Spring to inject the connectionManager and systemDAO beans into our DynamicQueryManager at runtime. We do this in the applicationContext.xml file:

DynamicQuery\web\WEB-INF\applicationContext.xml

<beans>
<description>Application context definition for DynamicQuery Portlet.</description>

<!-- The DynamicQuery manager implemenation -->
<bean id="dynamicQueryManager"
class="com.teradata.portlets.dynamicquery.service.impl.DynamicQueryManagerImpl">
<description>The business delegate for this application</description>

<!-- Add following section -->
<property name="systemDAO" ref="systemDAO" />
<property name="connectionManager" ref="connectionManager" />
<!-- End add section -->

</bean>

<!-- Auto generated bean definitions here -->
</beans>

Step 2: Add executeQuery() to DynamicQueryManager

Next, we need to add a new function called executeQuery() to the DynamicQueryManager interface so we can execute a query through the DynamicQueryManager with the ConnectionManager we are injecting:

DynamicQuery\src\java\com\teradata\portlets\dynamicquery\service\DynamicQueryManager.java


//***Add following section***/
import java.util.List;

import com.teradata.portlets.dynamicquery.exception.DynamicQueryException;
import com.teradata.dcs.config.model.System;
import.com.teradata.tags.widgets.model.TableWidget;
//***End add section***/

public interface DynamicQueryManager
{
//***Add following section***/

/**
* Executes the parameter sql statement using the parameter credentials. The query result set is
* returned as a TableWidget object populated with the data from the query executed.
*
* @param systemId
* The id of the system to execute the statement on
* @param username
* The username to execute the statement under
* @param password
* The password associated with the username credential
* @param sql
* The SQL statement to be executed
* @return A TableWidget object that is populated with the data returned by the query that has
* been executed
* @throws DynamicQueryException
* Thrown if there is an error while executing the query
*/
public TableWidget executeQuery(final Integer systemId, final String username,
final String password, final String sql) throws DynamicQueryException;

/**
* Returns a List of Teradata systems that can be queried.
*
* @return A List of Teradata systems
*/
public List<System> getSystemList();

//***End add section***/
}


Step 3: Implement executeQuery() in DynamicQueryManagerImpl

Now that we have added executeQuery() to the DynamicQueryManager interface, we need to implement the new function in the DynamicQueryManagerImpl class so we can use the function. In order to properly implement the executeQuery(), we need to construct a System object with the id of the system specified and a Login object with the specified credentials. We then pass the necessary parameters to the ConnectionManager to execute the query and then construct the result set as a TableWidget object:

DynamicQuery\src\java\com\teradata\portlets\dynamicquery\service\impl\DynamicQueryManagerImpl.java


//***Add following section***/
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Connection;
import java.util.Collections;

import com.teradata.dcs.config.model.Login;
import com.teradata.dcs.config.model.System;
import com.teradata.portlets.dynamicquery.exception.DynamicQueryException;
import com.teradata.tags.widgets.model.TableColumn;
import com.teradata.tags.widgets.model.TableWidget;
import com.teradata.tags.widgets.model.TableWidget.ColumnType;

//***End add section***/

public class DynamicQueryManagerImpl implements DynamicQueryManager
{
// Auto generated member variables here

//***Add following section***/
/**
* The List of available systems, each represented by a System object
*/
private List<System> systemList;

//***End add section***/

// Auto generated functions here

//***Add following section***/

/**
* (non-Javadoc)
*
* @see com.teradata.portlets.dynamicquery.service.DynamicQueryManager#getSystemList()
*/
public List<System> getSystemList()
{
// If the List has not yet been populated
if (systemList == null)
{
// If the systemDAO has not been initialized, the List cannot be retrieved
if (systemDAO == null)
{
throw new NullPointerException(
"systemDAO must be initialized to retrieve a system list!");
}
systemList = Collections.unmodifiableList(systemDAO.getSystems());
}

return systemList;
}

/**
* This function returns a System object that corresponds to the specified systemName
* parameter String. If no corresponding System object is found, <code>null</code> is
* returned.
*
* @param systemId
* The system id to retrieve the corresponding System object for
* @return A System object that corresponds to the parameter systemId, or <code>null</code>
* if no corresponding System object is found
*/
private System getSystem(final Integer systemId)
{
System system = null;

try
{
// Iterate over the list of available System objects
for (final System s : getSystemList())
{
// If the current System's id and the parameter systemId match, the
// System object has been found
if (s.getId().compareTo(systemId) == 0)
{
system = s;
break;
}
}
}
catch (final NullPointerException nPE)
{
// Do nothing and return null
}

return system;
}

/**
*
* (non-Javadoc)
*
* @see com.teradata.portlets.dynamicquery.service.DynamicQueryManager#executeQuery(
* java.lang.Integer, java.lang.String, java.lang.String, java.lang.String)
*/
public TableWidget executeQuery(final Integer systemId, final String username,
final String password, final String sql) throws DynamicQueryException
{
// The System object corresponding to the system name
final System system;
// A Login object that contains the specified credentials
final Login login;
// ResultSet object that contains the results of the query
ResultSet resultSet = null;
// The Connection object used to connect to the database to run the query
Connection connection = null;
// The TableWidget object that is populated with the data returned by the query resultSet
TableWidget tableWidget = new TableWidget();

system = getSystem(systemId);
// Check that a valid System object has been retrieved
if (system == null)
{
throw new DynamicQueryException("Specified system not found!");
}

login = new Login();
// Set the login username
login.setUsername(username);
// Set the login password
login.setPassword(password);
// Set the account string to a blank string
login.setAccountString("");

try
{
// Attempt to get a connection to the parameter system with the parameter credentials
connection = connectionManager.getConnection(system, login);

if (connection != null)
{
// Execute the query with the connection and get the result set
resultSet = connectionManager.executeQuery(connection, sql);
// Construct the TableWidget object from the resultSet returned by the query
tableWidget = constructTableData(resultSet, tableWidget);
}
}
finally
{
// Close the connection if it was successfully created
connectionManager.closeConnection(connection);
}

return tableWidget;
}

/**
* Constructs a TableWidget object with the data from the ResultSet object. The TableWidget
* object is used to construct a table with the vp:TableWidget tag.
*
* @param resultSet
* The ResultSet object that contains the data from the query
* @param tableWidget
* The TableWidget object to populate with data
* @return A TableWidget object populated with the data from the query
* @throws DynamicQueryException
* Thrown if there is an error reading the resultSet data
*/
private TableWidget constructTableData(final ResultSet resultSet, final TableWidget tableWidget)
throws DynamicQueryException
{
try
{
// Get the resultSets metaData for column information
final ResultSetMetaData metaData = resultSet.getMetaData();
// Create a TableColumn array to store column names
final TableColumn[] columns = new TableColumn[metaData.getColumnCount()];
// The number of rows returned
final int rowCount = getResultSetSize(resultSet);
// All the data from the resultSet
final String[][] data = new String[rowCount][columns.length];

// Get all of the column names
for (int i = 0; i < columns.length; i++)
{
columns[i] = new TableColumn(metaData.getColumnName(i + 1), null, null,
ColumnType.STRING);
}
// Set the columns for the table
tableWidget.setColumns(columns);

// While there is more data in the resultSet
while (resultSet.next())
{
// Get the current row number
final int currentRow = resultSet.getRow() - 1;
// For each column in the current row, retrieve its data
for (int i = 0; i < columns.length; i++)
{
data[currentRow][i] = resultSet.getString(i + 1);
}
}
// Set the data for the table
tableWidget.setData(data);
}
catch (final SQLException sE)
{
throw new DynamicQueryException("Error constructing table data! Error: "
+ sE.getMessage());
}

return tableWidget;
}

/**
* Returns the size (number of rows) of the parameter resultSet. Returns zero (0) if there are
* no rows in the resultSet OR there was an error getting the size.
*
* @param resultSet
* The resultSet to get the size from
* @return The size (number of rows) of the resultSet parameter
*/
private int getResultSetSize(final ResultSet resultSet)
{
int size;

try
{
// Move the cursor to the first row in the resultSet
// resultSet.first();
// Get the current position of the cursor
final int currentPos = resultSet.getRow();
// Move to the last row to get the row count
resultSet.last();
// Get the count of the last row
size = resultSet.getRow();
// Return to the current cursor position
resultSet.absolute(currentPos);
}
catch (final SQLException sE)
{
size = 0;
}

return size;
}

//***End add section***/
}

Tags (2)