Java tutorial
/************************************************************************************** * 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.antlr.NoCaseSensitiveStream; import com.espertech.esper.client.ConfigurationDBRef; import com.espertech.esper.client.EventType; import com.espertech.esper.client.hook.SQLColumnTypeContext; import com.espertech.esper.client.hook.SQLColumnTypeConversion; import com.espertech.esper.client.hook.SQLOutputRowConversion; import com.espertech.esper.client.hook.SQLOutputRowTypeContext; import com.espertech.esper.core.context.util.EPStatementAgentInstanceHandle; import com.espertech.esper.epl.expression.ExprValidationException; import com.espertech.esper.epl.generated.EsperEPL2GrammarLexer; import com.espertech.esper.epl.spec.DBStatementStreamSpec; import com.espertech.esper.event.EventAdapterService; import com.espertech.esper.util.*; import com.espertech.esper.view.HistoricalEventViewable; import org.antlr.runtime.CharStream; import org.antlr.runtime.CommonTokenStream; import org.antlr.runtime.Token; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import java.io.IOException; import java.io.StringReader; import java.io.StringWriter; import java.sql.*; import java.util.*; /** * Factory for a view onto historical data via SQL statement. */ public class DatabasePollingViewableFactory { /** * Placeholder name for SQL-where clause substitution. */ public static final String SAMPLE_WHERECLAUSE_PLACEHOLDER = "$ESPER-SAMPLE-WHERE"; /** * Creates the viewable for polling via database SQL query. * @param streamNumber is the stream number of the view * @param databaseStreamSpec provides the SQL statement, database name and additional info * @param databaseConfigService for getting database connection and settings * @param eventAdapterService for generating event beans from database information * @param epStatementAgentInstanceHandle is the statements-own handle for use in registering callbacks with services * @param columnTypeConversionHook hook for statement-specific column conversion * @param outputRowConversionHook hook for statement-specific row conversion * @param enableJDBCLogging indicator to enable JDBC logging * @return viewable providing poll functionality * @throws ExprValidationException if the validation failed */ public static HistoricalEventViewable createDBStatementView(String statementId, int streamNumber, DBStatementStreamSpec databaseStreamSpec, DatabaseConfigService databaseConfigService, EventAdapterService eventAdapterService, EPStatementAgentInstanceHandle epStatementAgentInstanceHandle, SQLColumnTypeConversion columnTypeConversionHook, SQLOutputRowConversion outputRowConversionHook, boolean enableJDBCLogging) throws ExprValidationException { // Parse the SQL for placeholders and text fragments List<PlaceholderParser.Fragment> sqlFragments; try { sqlFragments = PlaceholderParser.parsePlaceholder(databaseStreamSpec.getSqlWithSubsParams()); } catch (PlaceholderParseException ex) { String text = "Error parsing SQL"; throw new ExprValidationException(text + ", reason: " + ex.getMessage()); } // Assemble a PreparedStatement and parameter list String preparedStatementText = createPreparedStatement(sqlFragments); SQLParameterDesc parameterDesc = getParameters(sqlFragments); if (log.isDebugEnabled()) { log.debug(".createDBEventStream preparedStatementText=" + preparedStatementText + " parameterDesc=" + parameterDesc); } // Get a database connection String databaseName = databaseStreamSpec.getDatabaseName(); DatabaseConnectionFactory databaseConnectionFactory; ColumnSettings metadataSetting; try { databaseConnectionFactory = databaseConfigService.getConnectionFactory(databaseName); metadataSetting = databaseConfigService.getQuerySetting(databaseName); } catch (DatabaseConfigException ex) { String text = "Error connecting to database '" + databaseName + '\''; log.error(text, ex); throw new ExprValidationException(text + ", reason: " + ex.getMessage()); } Connection connection; try { connection = databaseConnectionFactory.getConnection(); } catch (DatabaseConfigException ex) { String text = "Error connecting to database '" + databaseName + '\''; log.error(text, ex); throw new ExprValidationException(text + ", reason: " + ex.getMessage()); } // On default setting, if we detect Oracle in the connection then don't query metadata from prepared statement ConfigurationDBRef.MetadataOriginEnum metaOriginPolicy = metadataSetting.getMetadataRetrievalEnum(); if (metaOriginPolicy == ConfigurationDBRef.MetadataOriginEnum.DEFAULT) { String connectionClass = connection.getClass().getName(); if ((connectionClass.toLowerCase().contains("oracle") || (connectionClass.toLowerCase().contains("timesten")))) { // switch to sample statement if we are dealing with an oracle connection metaOriginPolicy = ConfigurationDBRef.MetadataOriginEnum.SAMPLE; } } QueryMetaData queryMetaData; try { if ((metaOriginPolicy == ConfigurationDBRef.MetadataOriginEnum.METADATA) || (metaOriginPolicy == ConfigurationDBRef.MetadataOriginEnum.DEFAULT)) { queryMetaData = getPreparedStmtMetadata(connection, parameterDesc.getParameters(), preparedStatementText, metadataSetting); } else { String sampleSQL; boolean isGivenMetadataSQL = true; if (databaseStreamSpec.getMetadataSQL() != null) { sampleSQL = databaseStreamSpec.getMetadataSQL(); isGivenMetadataSQL = true; if (log.isInfoEnabled()) { log.info(".createDBStatementView Using provided sample SQL '" + sampleSQL + "'"); } } else { // Create the sample SQL by replacing placeholders with null and // SAMPLE_WHERECLAUSE_PLACEHOLDER with a "where 1=0" clause sampleSQL = createSamplePlaceholderStatement(sqlFragments); if (log.isInfoEnabled()) { log.info(".createDBStatementView Using un-lexed sample SQL '" + sampleSQL + "'"); } // If there is no SAMPLE_WHERECLAUSE_PLACEHOLDER, lexical analyse the SQL // adding a "where 1=0" clause. if (parameterDesc.getBuiltinIdentifiers().length != 1) { sampleSQL = lexSampleSQL(sampleSQL); if (log.isInfoEnabled()) { log.info(".createDBStatementView Using lexed sample SQL '" + sampleSQL + "'"); } } } // finally get the metadata by firing the sample SQL queryMetaData = getExampleQueryMetaData(connection, parameterDesc.getParameters(), sampleSQL, metadataSetting, isGivenMetadataSQL); } } catch (ExprValidationException ex) { try { connection.close(); } catch (SQLException e) { // don't handle } throw ex; } // Close connection try { connection.close(); } catch (SQLException e) { String text = "Error closing connection"; log.error(text, e); throw new ExprValidationException(text + ", reason: " + e.getMessage()); } // Create event type // Construct an event type from SQL query result metadata Map<String, Object> eventTypeFields = new HashMap<String, Object>(); int columnNum = 1; for (Map.Entry<String, DBOutputTypeDesc> entry : queryMetaData.getOutputParameters().entrySet()) { String name = entry.getKey(); DBOutputTypeDesc dbOutputDesc = entry.getValue(); Class clazz; if (dbOutputDesc.getOptionalBinding() != null) { clazz = dbOutputDesc.getOptionalBinding().getType(); } else { clazz = SQLTypeMapUtil.sqlTypeToClass(dbOutputDesc.getSqlType(), dbOutputDesc.getClassName()); } if (columnTypeConversionHook != null) { Class newValue = columnTypeConversionHook.getColumnType(new SQLColumnTypeContext( databaseStreamSpec.getDatabaseName(), databaseStreamSpec.getSqlWithSubsParams(), name, clazz, dbOutputDesc.getSqlType(), columnNum)); if (newValue != null) { clazz = newValue; } } eventTypeFields.put(name, clazz); columnNum++; } EventType eventType; if (outputRowConversionHook == null) { String outputEventType = statementId + "_dbpoll_" + streamNumber; eventType = eventAdapterService.createAnonymousMapType(outputEventType, eventTypeFields); } else { Class carrierClass = outputRowConversionHook .getOutputRowType(new SQLOutputRowTypeContext(databaseStreamSpec.getDatabaseName(), databaseStreamSpec.getSqlWithSubsParams(), eventTypeFields)); if (carrierClass == null) { throw new ExprValidationException("Output row conversion hook returned no type"); } eventType = eventAdapterService.addBeanType(carrierClass.getName(), carrierClass, false, false, false); } // Get a proper connection and data cache ConnectionCache connectionCache; DataCache dataCache; try { connectionCache = databaseConfigService.getConnectionCache(databaseName, preparedStatementText); dataCache = databaseConfigService.getDataCache(databaseName, epStatementAgentInstanceHandle); } catch (DatabaseConfigException e) { String text = "Error obtaining cache configuration"; log.error(text, e); throw new ExprValidationException(text + ", reason: " + e.getMessage()); } PollExecStrategyDBQuery dbPollStrategy = new PollExecStrategyDBQuery(eventAdapterService, eventType, connectionCache, preparedStatementText, queryMetaData.getOutputParameters(), columnTypeConversionHook, outputRowConversionHook, enableJDBCLogging); return new DatabasePollingViewable(streamNumber, queryMetaData.getInputParameters(), dbPollStrategy, dataCache, eventType); } private static QueryMetaData getExampleQueryMetaData(Connection connection, String[] parameters, String sampleSQL, ColumnSettings metadataSetting, boolean isUsingMetadataSQL) throws ExprValidationException { // Simply add up all input parameters List<String> inputParameters = new LinkedList<String>(); inputParameters.addAll(Arrays.asList(parameters)); Statement statement; try { statement = connection.createStatement(); } catch (SQLException ex) { String text = "Error creating statement"; log.error(text, ex); throw new ExprValidationException(text + ", reason: " + ex.getMessage()); } ResultSet result = null; try { result = statement.executeQuery(sampleSQL); } catch (SQLException ex) { try { statement.close(); } catch (SQLException e) { log.info("Error closing statement: " + e.getMessage(), e); } String text; if (isUsingMetadataSQL) { text = "Error compiling metadata SQL to retrieve statement metadata, using sql text '" + sampleSQL + "'"; } else { text = "Error compiling metadata SQL to retrieve statement metadata, consider using the 'metadatasql' syntax, using sql text '" + sampleSQL + "'"; } log.error(text, ex); throw new ExprValidationException(text + ", reason: " + ex.getMessage()); } Map<String, DBOutputTypeDesc> outputProperties; try { outputProperties = compileResultMetaData(result.getMetaData(), metadataSetting); } catch (SQLException ex) { try { result.close(); } catch (SQLException e) { // don't handle } try { statement.close(); } catch (SQLException e) { // don't handle } String text = "Error in statement '" + sampleSQL + "', failed to obtain result metadata"; log.error(text, ex); throw new ExprValidationException(text + ", please check the statement, reason: " + ex.getMessage()); } finally { if (result != null) { try { result.close(); } catch (SQLException e) { log.warn("Exception closing result set: " + e.getMessage()); } } if (statement != null) { try { statement.close(); } catch (SQLException e) { log.warn("Exception closing result set: " + e.getMessage()); } } } return new QueryMetaData(inputParameters, outputProperties); } /** * Lexes the sample SQL and inserts a "where 1=0" where-clause. * @param querySQL to inspect using lexer * @return sample SQL with where-clause inserted * @throws ExprValidationException to indicate a lexer problem */ protected static String lexSampleSQL(String querySQL) throws ExprValidationException { querySQL = querySQL.replaceAll("\\s\\s+|\\n|\\r", " "); StringReader reader = new StringReader(querySQL); CharStream input; try { input = new NoCaseSensitiveStream(reader); } catch (IOException ex) { throw new ExprValidationException("IOException lexing query SQL '" + querySQL + '\'', ex); } int whereIndex = -1; int groupbyIndex = -1; int havingIndex = -1; int orderByIndex = -1; List<Integer> unionIndexes = new ArrayList<Integer>(); EsperEPL2GrammarLexer lex = new EsperEPL2GrammarLexer(input); CommonTokenStream tokens = new CommonTokenStream(lex); List tokenList = tokens.getTokens(); for (int i = 0; i < tokenList.size(); i++) { Token token = (Token) tokenList.get(i); if ((token == null) || token.getText() == null) { break; } String text = token.getText().toLowerCase().trim(); if (text.equals("where")) { whereIndex = token.getCharPositionInLine() + 1; } if (text.equals("group")) { groupbyIndex = token.getCharPositionInLine() + 1; } if (text.equals("having")) { havingIndex = token.getCharPositionInLine() + 1; } if (text.equals("order")) { orderByIndex = token.getCharPositionInLine() + 1; } if (text.equals("union")) { unionIndexes.add(token.getCharPositionInLine() + 1); } } // If we have a union, break string into subselects and process each if (unionIndexes.size() != 0) { StringWriter changedSQL = new StringWriter(); int lastIndex = 0; for (int i = 0; i < unionIndexes.size(); i++) { int index = unionIndexes.get(i); String fragment; if (i > 0) { fragment = querySQL.substring(lastIndex + 5, index - 1); } else { fragment = querySQL.substring(lastIndex, index - 1); } String lexedFragment = lexSampleSQL(fragment); if (i > 0) { changedSQL.append("union "); } changedSQL.append(lexedFragment); lastIndex = index - 1; } // last part after last union String fragment = querySQL.substring(lastIndex + 5, querySQL.length()); String lexedFragment = lexSampleSQL(fragment); changedSQL.append("union "); changedSQL.append(lexedFragment); return changedSQL.toString(); } // Found a where clause, simplest cases if (whereIndex != -1) { StringWriter changedSQL = new StringWriter(); String prefix = querySQL.substring(0, whereIndex + 5); String suffix = querySQL.substring(whereIndex + 5, querySQL.length()); changedSQL.write(prefix); changedSQL.write("1=0 and "); changedSQL.write(suffix); return changedSQL.toString(); } // No where clause, find group-by int insertIndex; if (groupbyIndex != -1) { insertIndex = groupbyIndex; } else if (havingIndex != -1) { insertIndex = havingIndex; } else if (orderByIndex != -1) { insertIndex = orderByIndex; } else { StringWriter changedSQL = new StringWriter(); changedSQL.write(querySQL); changedSQL.write(" where 1=0 "); return changedSQL.toString(); } try { StringWriter changedSQL = new StringWriter(); String prefix = querySQL.substring(0, insertIndex - 1); changedSQL.write(prefix); changedSQL.write("where 1=0 "); String suffix = querySQL.substring(insertIndex - 1, querySQL.length()); changedSQL.write(suffix); return changedSQL.toString(); } catch (Exception ex) { String text = "Error constructing sample SQL to retrieve metadata for JDBC-drivers that don't support metadata, consider using the " + SAMPLE_WHERECLAUSE_PLACEHOLDER + " placeholder or providing a sample SQL"; log.error(text, ex); throw new ExprValidationException(text, ex); } } private static QueryMetaData getPreparedStmtMetadata(Connection connection, String[] parameters, String preparedStatementText, ColumnSettings metadataSetting) throws ExprValidationException { PreparedStatement prepared; try { if (log.isInfoEnabled()) { log.info(".getPreparedStmtMetadata Preparing statement '" + preparedStatementText + "'"); } prepared = connection.prepareStatement(preparedStatementText); } catch (SQLException ex) { String text = "Error preparing statement '" + preparedStatementText + '\''; log.error(text, ex); throw new ExprValidationException(text + ", reason: " + ex.getMessage()); } // Interrogate prepared statement - parameters and result List<String> inputParameters = new LinkedList<String>(); try { ParameterMetaData parameterMetaData = prepared.getParameterMetaData(); inputParameters.addAll(Arrays.asList(parameters).subList(0, parameterMetaData.getParameterCount())); } catch (Exception ex) { try { prepared.close(); } catch (SQLException e) { // don't handle } String text = "Error obtaining parameter metadata from prepared statement, consider turning off metadata interrogation via configuration, for statement '" + preparedStatementText + '\''; log.error(text, ex); throw new ExprValidationException(text + ", please check the statement, reason: " + ex.getMessage()); } Map<String, DBOutputTypeDesc> outputProperties; try { outputProperties = compileResultMetaData(prepared.getMetaData(), metadataSetting); } catch (SQLException ex) { try { prepared.close(); } catch (SQLException e) { // don't handle } String text = "Error in statement '" + preparedStatementText + "', failed to obtain result metadata, consider turning off metadata interrogation via configuration"; log.error(text, ex); throw new ExprValidationException(text + ", please check the statement, reason: " + ex.getMessage()); } if (log.isDebugEnabled()) { log.debug(".createDBEventStream in=" + inputParameters.toString() + " out=" + outputProperties.toString()); } // Close statement try { prepared.close(); } catch (SQLException e) { String text = "Error closing prepared statement"; log.error(text, e); throw new ExprValidationException(text + ", reason: " + e.getMessage()); } return new QueryMetaData(inputParameters, outputProperties); } private static String createPreparedStatement(List<PlaceholderParser.Fragment> parseFragements) { StringBuilder buffer = new StringBuilder(); for (PlaceholderParser.Fragment fragment : parseFragements) { if (!fragment.isParameter()) { buffer.append(fragment.getValue()); } else { if (fragment.getValue().equals(SAMPLE_WHERECLAUSE_PLACEHOLDER)) { continue; } buffer.append('?'); } } return buffer.toString(); } private static String createSamplePlaceholderStatement(List<PlaceholderParser.Fragment> parseFragements) { StringBuilder buffer = new StringBuilder(); for (PlaceholderParser.Fragment fragment : parseFragements) { if (!fragment.isParameter()) { buffer.append(fragment.getValue()); } else { if (fragment.getValue().equals(SAMPLE_WHERECLAUSE_PLACEHOLDER)) { buffer.append(" where 1=0 "); break; } else { buffer.append("null"); } } } return buffer.toString(); } private static SQLParameterDesc getParameters(List<PlaceholderParser.Fragment> parseFragements) { List<String> eventPropertyParams = new LinkedList<String>(); for (PlaceholderParser.Fragment fragment : parseFragements) { if (fragment.isParameter()) { if (!fragment.getValue().equals(SAMPLE_WHERECLAUSE_PLACEHOLDER)) { eventPropertyParams.add(fragment.getValue()); } } } String[] parameters = eventPropertyParams.toArray(new String[eventPropertyParams.size()]); String[] builtin = eventPropertyParams.toArray(new String[eventPropertyParams.size()]); return new SQLParameterDesc(parameters, builtin); } private static Map<String, DBOutputTypeDesc> compileResultMetaData(ResultSetMetaData resultMetaData, ColumnSettings columnSettings) throws SQLException { Map<String, DBOutputTypeDesc> outputProperties = new HashMap<String, DBOutputTypeDesc>(); for (int i = 0; i < resultMetaData.getColumnCount(); i++) { String columnName = resultMetaData.getColumnLabel(i + 1); if (columnName == null) { columnName = resultMetaData.getColumnName(i + 1); } int columnType = resultMetaData.getColumnType(i + 1); String javaClass = resultMetaData.getColumnTypeName(i + 1); ConfigurationDBRef.ColumnChangeCaseEnum caseEnum = columnSettings.getColumnCaseConversionEnum(); if ((caseEnum != null) && (caseEnum == ConfigurationDBRef.ColumnChangeCaseEnum.LOWERCASE)) { columnName = columnName.toLowerCase(); } if ((caseEnum != null) && (caseEnum == ConfigurationDBRef.ColumnChangeCaseEnum.UPPERCASE)) { columnName = columnName.toUpperCase(); } DatabaseTypeBinding binding = null; String javaTypeBinding = null; if (columnSettings.getJavaSqlTypeBinding() != null) { javaTypeBinding = columnSettings.getJavaSqlTypeBinding().get(columnType); } if (javaTypeBinding != null) { binding = DatabaseTypeEnum.getEnum(javaTypeBinding).getBinding(); } DBOutputTypeDesc outputType = new DBOutputTypeDesc(columnType, javaClass, binding); outputProperties.put(columnName, outputType); } return outputProperties; } private static final Log log = LogFactory.getLog(DatabasePollingViewableFactory.class); }