Creating the ConnectionManager

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 ConnectionManager

Once the project is generated, we will create and implement the ConnectionManager to run single statement queries against the Teradata Database using JDBC. We will implement the Business Delegate pattern to loosely couple our implementation to the DynamicQueryManager. This allows us to easily change our ConnectionManager implementation without having to rewrite the connection method in the DynamicQueryManager (which we will do in the next step).

Step 1: Create the ConnectionManager Interface

First, we will create the ConnectionManager interface that we can implement. This will be created in the com.teradata.portlets.dynamicquery.service package:

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


package com.teradata.portlets.dynamicquery.service;

import java.sql.Connection;
import java.sql.ResultSet;

import com.teradata.dcs.config.model.Login;
import com.teradata.dcs.config.model.System;
import com.teradata.portlets.dynamicquery.exception.DynamicQueryException;

/**
* The ConnectionManager interface should be implemented so that a JDBC connection to a Teradata
* System can be established through a custom implementation. This class implements the Business
* Delegate pattern. (see
* http://java.sun.com/blueprints/corej2eepatterns/Patterns/BusinessDelegate.html).
*/
public interface ConnectionManager
{
/**
* The implementation of executeQuery() should satisfy the following conditions:
* <ol>
* <li>Execute the specified query with the parameter connection object.</li>
* <li>Return a ResultSet object that contains the result set of the executed query.</li>
* </ol>
*
* @param connection
* The Connection object to execute the query with
* @param sql
* The SQL statement to execute once with the parameter connection object
* @return A ResultSet object populated with the result set of the SQL that has been run
* @throws DynamicQueryException
* A DynamicQueryException is thrown if there is an exception executing the
* parameter query
*/
public ResultSet executeQuery(final Connection connection, final String sql)
throws DynamicQueryException;

/**
* The implementation of getConnection() should satisfy the following conditions:
* <ol>
* <li>Establish a JDBC connection to the database with the specified credentials.</li>
* <li>Return a Connection object obtained with the specified credentials.</li>
* </ol>
*
* @param system
* The System that the query will be executed on
* @param login
* The Login credentials to run the query with
* @return A Connection object using the specified credentials
* @throws DynamicQueryException
* A DynamicQueryException is thrown if there is an exception retrieving a
* connection to the database
*/
public Connection getConnection(final System system, final Login login)
throws DynamicQueryException;

/**
* The implementation of the closeConnection() should satisfy the following conditions:
* <ol>
* <li>The parameter connection to the database should be closed if the connection object is a
* valid connection that is not <code>null</code>.</li>
* <li>Return true if the connection was closed successfully or the connection object is
* <code>null</code>, and false if the connection was not closed successfully.</li>
* </ol>
*
* @param connection
* The Connection object to close
* @return True if the connection was closed successfully, false otherwise
*/
public boolean closeConnection(final Connection connection);
}


Step 2: Creating the DynamicQueryException Class

Upon close inspection of the ConnectionManager interface, you will notice that the executeQuery() and getConnection() functions throws a DynamicQueryException. This is a custom exception wrapper for the DynamicQuery portlet which we will create. This allows us to throw a generic exception that is independent of our implementation since different implementations of this interface could throw different exceptions. The DynamicQueryException is a straight-forward wrapper of the Exception class that will be created in a new package com.teradata.portlets.dynamicquery.exception:

DynamicQuery\src\java\com\teradata\portlets\dynamicquery\exception\DynamicQueryException.java


package com.teradata.portlets.dynamicquery.exception;

/**
* The DynamicQueryException class is a wrapper for the Exception class so exceptions that
* are thrown within the DynamicQuery portlet can be essentially re-classed with a custom
* message specific to the DynamicQuery portlet.
*/
public class DynamicQueryException extends Exception
{
/**
* serial UID
*/
public static final long serialVersionUID = 3823498408234234L;

/**
* Default no args constructor. Creates a DynamicQueryException object with a
* <code>null</code> detail message.
*/
public DynamicQueryException()
{
super();
}

/**
* Construct a DynamicQueryException object with the parameter message.
*
* @param message
* The message associated with this exception
*/
public DynamicQueryException(final String message)
{
super(message);
}

/**
* Construct a DynamicQueryException object with the parameter message and the parameter
* cause.
*
* @param message
* The message associated with this exception
* @param cause
* The Throwable cause associated with this exception. A <code>null</code> value
* is permitted.
*/
public DynamicQueryException(final String message, final Throwable cause)
{
super(message, cause);
}

/**
* construct a DynamicQueryException object with the parameter cause.
*
* @param cause
* The Throwable cause associated with this exception. A <code>null</code> value
* is permitted.
*/
public DynamicQueryException(final Throwable cause)
{
super(cause);
}
}


Step 3: Implementing the ConnectionManager Interface

Next, we will implement the ConnectionManager interface that we just created so we can establish a JDBC connection to the database for running queries. Our implementation will use the TeradataDataSourceFactory object to establish a JDBC connection with the database. We will call this implementation ConnectionManagerImpl and create it under the packagecom.teradata.portlets.dynamicquery.service.impl:

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


package com.teradata.portlets.dynamicquery.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.teradata.dcs.config.model.Login;
import com.teradata.dcs.config.model.System;
import com.teradata.dcs.monitor.dao.jdbc.TeradataDataSourceFactory;
import com.teradata.portlets.dynamicquery.exception.DynamicQueryException;
import com.teradata.portlets.dynamicquery.service.ConnectionManager;

/**
* The ConnectionManagerImpl class implements the ConnectionManager interface. This class
* establishes a JDBC connection with the Teradata database using the specified credentials and
* if successful, executes the specified SQL statement with the credentials. The class uses the
* TeradataDataSourceFactory to establish a JDBC connection with the database.
*/
public class ConnectionManagerImpl implements ConnectionManager
{
// The Teradata factory to retrieve a data source connection from
private TeradataDataSourceFactory teradataDataSourceFactory;

/**
*
* (non-Javadoc)
*
* @see com.teradata.portlets.dynamicquery.service.ConnectionManager#executeQuery(
* java.sql.Connection, java.lang.String)
*/
public ResultSet executeQuery(final Connection connection, final String sql)
throws DynamicQueryException
{
// A ResultSet object with the result set of the query
final ResultSet resultSet;
// A Statement object to create a SQL for execution
final Statement statement;

try
{
// Create a Statement object for execution of the SQL statement
statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
// Execute the parameter SQL statement
resultSet = statement.executeQuery(sql);
}
catch (final SQLException sE)
{
throw new DynamicQueryException("Error executing SQL with specified connection! "
+ "Error: " + sE.getMessage());
}

return resultSet;
}

/**
*
* (non-Javadoc)
*
* @see com.teradata.portlets.dynamicquery.service.ConnectionManager#getConnection(System,
* Login)
*/
public Connection getConnection(final System system, final Login login)
throws DynamicQueryException
{
// A JDBC data source object
final DataSource dataSource;
// A Connection object
Connection connection = null;

try
{
// Get the JDBC data source
dataSource = teradataDataSourceFactory.getDataSource(system, login, null, true, false);
// Establish a connection with the data source
connection = dataSource.getConnection();
}
catch (final SQLException sE)
{
throw new DynamicQueryException(
"Error creating connection with specified credentials! " + "Error: "
+ sE.getMessage());
}

return connection;
}

/**
* (non-Javadoc)
*
* @see com.teradata.portlets.dynamicquery.service.ConnectionManager#closeConnection(
* java.sql.Connection)
*/
public boolean closeConnection(final Connection connection)
{
boolean success = true;

try
{
if (connection != null)
{
// Attemp to close the connection
connection.close();
// Check if the connection was closed successfully
success = connection.isClosed();
}
}
catch (final SQLException sE)
{
// There is nothing further we can do if the connection failed to close
success = false;
}

return success;
}

/**
* Setter for teradataDataSourceFactory.
*
* @param teradataDataSourceFactory
* the teradataDataSourceFactory to set
*/
public void setTeradataDataSourceFactory(
final TeradataDataSourceFactory teradataDataSourceFactory)
{
this.teradataDataSourceFactory = teradataDataSourceFactory;
}
}


Now we need to add a TeradataDataSourceFactory bean declaration to our configuration so Spring can inject an instance of the object into ConnectionManagerImpl at runtime.

DynamicQuery\web\WEB-INF\dataSourceiBatis.xml 

<beans>
<!-- Add the following section below -->

<bean id="teradataDataSourceFactory"
class="com.teradata.dcs.monitor.dao.jdbc.TeradataDataSourceFactory">
<description>
Data source used to run queries against the Teradata database
</description>
</bean>
<!-- End add section -->

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

Lastly, we need to add a new Bean declaration so Spring will create an instance of the ConnectionManagerImpl class for injection. We also need to tell Spring to inject an instance of the teradataDataSourceFactory bean into the ConnectionManagerImpl class:

DynamicQuery\web\WEB-INF\applicationContext.xml

<beans>
<description>Application context definition for DynamicQuery Portlet.</description>
<!-- Add the following section below -->

<!-- The ConnectionManager implementation -->
<bean id="connectionManager"
class="com.teradata.portlets.dynamicquery.service.impl.ConnectionManagerImpl">
<description>The business delegate for the JDBC connection to the database</description>
<property name="teradataDataSourceFactory" ref="teradataDataSourceFactory" />
</bean>

<!-- End add section -->

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


Tags (2)