/**************************************************************************************
* Copyright (C) 2008 EsperTech, Inc. All rights reserved. *
* http://esper.codehaus.org *
* http://www.espertech.com *
* ---------------------------------------------------------------------------------- *
* The software in this package is published under the terms of the GPL license *
* a copy of which has been included with this distribution in the license.txt file. *
**************************************************************************************/
package com.espertech.esper.epl.db;
import com.espertech.esper.client.EPException;
import com.espertech.esper.client.EventBean;
import com.espertech.esper.client.EventType;
import com.espertech.esper.client.hook.*;
import com.espertech.esper.collection.Pair;
import com.espertech.esper.event.EventAdapterService;
import com.espertech.esper.event.bean.BeanEventType;
import com.espertech.esper.util.AuditPath;
import com.espertech.esper.util.DatabaseTypeBinding;
import com.espertech.esper.util.ExecutionPathDebugLog;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import java.sql.*;
import java.util.*;
import java.util.Date;
/**
* Viewable providing historical data from a database.
*/
public class PollExecStrategyDBQuery implements PollExecStrategy
{
private static final Log jdbcPerfLog = LogFactory.getLog(AuditPath.JDBC_LOG);
private static final Log log = LogFactory.getLog(PollExecStrategyDBQuery.class);
private final EventAdapterService eventAdapterService;
private final String preparedStatementText;
private final Map<String, DBOutputTypeDesc> outputTypes;
private final ConnectionCache connectionCache;
private final EventType eventType;
private final SQLColumnTypeConversion columnTypeConversionHook;
private final SQLOutputRowConversion outputRowConversionHook;
private final boolean enableJDBCLogging;
private Pair<Connection, PreparedStatement> resources;
/**
* Ctor.
* @param eventAdapterService for generating event beans
* @param eventType is the event type that this poll generates
* @param connectionCache caches Connection and PreparedStatement
* @param preparedStatementText is the SQL to use for polling
* @param outputTypes describe columns selected by the SQL
* @param outputRowConversionHook hook to convert rows, if any hook is registered
* @param columnTypeConversionHook hook to convert columns, if any hook is registered
*/
public PollExecStrategyDBQuery(EventAdapterService eventAdapterService,
EventType eventType,
ConnectionCache connectionCache,
String preparedStatementText,
Map<String, DBOutputTypeDesc> outputTypes,
SQLColumnTypeConversion columnTypeConversionHook,
SQLOutputRowConversion outputRowConversionHook,
boolean enableJDBCLogging)
{
this.eventAdapterService = eventAdapterService;
this.eventType = eventType;
this.connectionCache = connectionCache;
this.preparedStatementText = preparedStatementText;
this.outputTypes = outputTypes;
this.columnTypeConversionHook = columnTypeConversionHook;
this.outputRowConversionHook = outputRowConversionHook;
this.enableJDBCLogging = enableJDBCLogging;
}
public void start()
{
resources = connectionCache.getConnection();
}
public void done()
{
connectionCache.doneWith(resources);
}
public void destroy()
{
connectionCache.destroy();
}
public List<EventBean> poll(Object[] lookupValues)
{
List<EventBean> result;
try
{
result = execute(resources.getSecond(), lookupValues);
}
catch (EPException ex)
{
connectionCache.doneWith(resources);
throw ex;
}
return result;
}
private synchronized List<EventBean> execute(PreparedStatement preparedStatement,
Object[] lookupValuePerStream)
{
if (ExecutionPathDebugLog.isDebugEnabled && log.isInfoEnabled())
{
log.info(".execute Executing prepared statement '" + preparedStatementText + "'");
}
boolean hasJDBCLogging = enableJDBCLogging && jdbcPerfLog.isInfoEnabled();
// set parameters
SQLInputParameterContext inputParameterContext = null;
if (columnTypeConversionHook != null) {
inputParameterContext = new SQLInputParameterContext();
}
int count = 1;
Object[] parameters = null;
if (hasJDBCLogging) {
parameters = new Object[lookupValuePerStream.length];
}
for (int i = 0; i < lookupValuePerStream.length; i++)
{
try
{
Object parameter = lookupValuePerStream[i];
if (ExecutionPathDebugLog.isDebugEnabled && log.isInfoEnabled())
{
log.info(".execute Setting parameter " + count + " to " + parameter + " typed " + ((parameter == null)? "null" : parameter.getClass()));
}
if (columnTypeConversionHook != null) {
inputParameterContext.setParameterNumber(i + 1);
inputParameterContext.setParameterValue(parameter);
parameter = columnTypeConversionHook.getParameterValue(inputParameterContext);
}
setObject(preparedStatement, count, parameter);
if (parameters != null) {
parameters[i] = parameter;
}
}
catch (SQLException ex)
{
throw new EPException("Error setting parameter " + count, ex);
}
count++;
}
// execute
ResultSet resultSet;
if (hasJDBCLogging) {
long startTimeNS = System.nanoTime();
long startTimeMS = System.currentTimeMillis();
try
{
resultSet = preparedStatement.executeQuery();
}
catch (SQLException ex)
{
throw new EPException("Error executing statement '" + preparedStatementText + '\'', ex);
}
long endTimeNS = System.nanoTime();
long endTimeMS = System.currentTimeMillis();
jdbcPerfLog.info("Statement '" + preparedStatementText + "' delta nanosec " + (endTimeNS - startTimeNS) +
" delta msec " + (endTimeMS - startTimeMS) +
" parameters " + Arrays.toString(parameters));
}
else {
try
{
resultSet = preparedStatement.executeQuery();
}
catch (SQLException ex)
{
throw new EPException("Error executing statement '" + preparedStatementText + '\'', ex);
}
}
// generate events for result set
List<EventBean> rows = new LinkedList<EventBean>();
try
{
SQLColumnValueContext valueContext = null;
if (columnTypeConversionHook != null) {
valueContext = new SQLColumnValueContext();
}
SQLOutputRowValueContext rowContext = null;
if (outputRowConversionHook != null) {
rowContext = new SQLOutputRowValueContext();
}
int rowNum = 0;
while (resultSet.next())
{
int colNum = 1;
Map<String, Object> row = new HashMap<String, Object>();
for (Map.Entry<String, DBOutputTypeDesc> entry : outputTypes.entrySet())
{
String columnName = entry.getKey();
Object value;
DatabaseTypeBinding binding = entry.getValue().getOptionalBinding();
if (binding != null)
{
value = binding.getValue(resultSet, columnName);
}
else
{
value = resultSet.getObject(columnName);
}
if (columnTypeConversionHook != null) {
valueContext.setColumnName(columnName);
valueContext.setColumnNumber(colNum);
valueContext.setColumnValue(value);
valueContext.setResultSet(resultSet);
value = columnTypeConversionHook.getColumnValue(valueContext);
}
row.put(columnName, value);
colNum++;
}
EventBean eventBeanRow = null;
if (this.outputRowConversionHook == null) {
eventBeanRow = eventAdapterService.adapterForTypedMap(row, eventType);
}
else {
rowContext.setValues(row);
rowContext.setRowNum(rowNum);
rowContext.setResultSet(resultSet);
Object rowData = outputRowConversionHook.getOutputRow(rowContext);
if (rowData != null) {
eventBeanRow = eventAdapterService.adapterForTypedBean(rowData, (BeanEventType) eventType);
}
}
if (eventBeanRow != null) {
rows.add(eventBeanRow);
rowNum++;
}
}
}
catch (SQLException ex)
{
throw new EPException("Error reading results for statement '" + preparedStatementText + '\'', ex);
}
if (enableJDBCLogging && jdbcPerfLog.isInfoEnabled()) {
jdbcPerfLog.info("Statement '" + preparedStatementText + "' " + rows.size() + " rows");
}
try
{
resultSet.close();
}
catch (SQLException ex)
{
throw new EPException("Error closing statement '" + preparedStatementText + '\'', ex);
}
return rows;
}
private void setObject(PreparedStatement preparedStatement, int column, Object value) throws SQLException
{
// Allow java.util.Date conversion for JDBC drivers that don't provide this feature
if (value instanceof Date)
{
value = new Timestamp(((Date)value).getTime());
}
else if (value instanceof Calendar)
{
value = new Timestamp(((Calendar)value).getTimeInMillis());
}
preparedStatement.setObject(column, value);
}
}