Package io.debezium.jdbc

Class JdbcConnection

java.lang.Object
io.debezium.jdbc.JdbcConnection
All Implemented Interfaces:
AutoCloseable
@NotThreadSafe public class JdbcConnection extends Object implements AutoCloseable
A utility that simplifies using a JDBC connection and executing transactions composed of multiple statements.
Author:
Randall Hauch

  • Field Details

  • Constructor Details

    • JdbcConnection

      public JdbcConnection(JdbcConfiguration config, JdbcConnection.ConnectionFactory connectionFactory, String openingQuoteCharacter, String closingQuoteCharacter)
      Create a new instance with the given configuration and connection factory.
      Parameters:
      config - the configuration; may not be null
      connectionFactory - the connection factory; may not be null

    • JdbcConnection

      protected JdbcConnection(JdbcConfiguration config, JdbcConnection.ConnectionFactory connectionFactory, JdbcConnection.Operations initialOperations, String openingQuotingChar, String closingQuotingChar)
      Create a new instance with the given configuration and connection factory, and specify the operations that should be run against each newly-established connection.
      Parameters:
      config - the configuration; may not be null
      connectionFactory - the connection factory; may not be null
      initialOperations - the initial operations that should be run on each new connection; may be null
      openingQuotingChar - the opening quoting character
      closingQuotingChar - the closing quoting character

  • Method Details

    • patternBasedFactory

      public static JdbcConnection.ConnectionFactory patternBasedFactory(String urlPattern, Field... variables)
      Create a JdbcConnection.ConnectionFactory that replaces variables in the supplied URL pattern. Variables include:
      • ${hostname}
      • ${port}
      • ${dbname}
      • ${username}
      • ${password}
      Parameters:
      urlPattern - the URL pattern string; may not be null
      variables - any custom or overridden configuration variables
      Returns:
      the connection factory

    • patternBasedFactory

      public static JdbcConnection.ConnectionFactory patternBasedFactory(String urlPattern, String driverClassName, ClassLoader classloader, Field... variables)
      Create a JdbcConnection.ConnectionFactory that uses the specific JDBC driver class loaded with the given class loader, and obtains the connection URL by replacing the following variables in the URL pattern:
      • ${hostname}
      • ${port}
      • ${dbname}
      • ${username}
      • ${password}

      This method attempts to instantiate the JDBC driver class and use that instance to connect to the database.

      Parameters:
      urlPattern - the URL pattern string; may not be null
      driverClassName - the name of the JDBC driver class; may not be null
      classloader - the ClassLoader that should be used to load the JDBC driver class given by `driverClassName`; may be null if this class' class loader should be used
      variables - any custom or overridden configuration variables
      Returns:
      the connection factory

    • propsWithMaskedPassword

      private static Properties propsWithMaskedPassword(Properties props)

    • getCurrentTimestamp

      public Optional<Timestamp> getCurrentTimestamp() throws SQLException
      Throws:
      SQLException

    • combineVariables

      private static Field[] combineVariables(Field[] overriddenVariables, Field... defaultVariables)

    • findAndReplace

      private static String findAndReplace(String url, Properties props, Field... variables)

    • findAndReplace

      private static String findAndReplace(String url, String name, Properties props, String defaultValue)

    • config

      public JdbcConfiguration config()
      Obtain the configuration for this connection.
      Returns:
      the JDBC configuration; never null

    • setAutoCommit

      public JdbcConnection setAutoCommit(boolean autoCommit) throws SQLException
      Throws:
      SQLException

    • commit

      public JdbcConnection commit() throws SQLException
      Throws:
      SQLException

    • rollback

      public JdbcConnection rollback() throws SQLException
      Throws:
      SQLException

    • connect

      public JdbcConnection connect() throws SQLException
      Ensure a connection to the database is established.
      Returns:
      this object for chaining methods together
      Throws:
      SQLException - if there is an error connecting to the database

    • execute

      public JdbcConnection execute(String... sqlStatements) throws SQLException
      Execute a series of SQL statements as a single transaction.
      Parameters:
      sqlStatements - the SQL statements that are to be performed as a single transaction
      Returns:
      this object for chaining methods together
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements
      See Also:

    • execute

      public JdbcConnection execute(JdbcConnection.Operations operations) throws SQLException
      Execute a series of operations as a single transaction.
      Parameters:
      operations - the function that will be called with a newly-created Statement, and that performs one or more operations on that statement object
      Returns:
      this object for chaining methods together
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements

    • query

      public JdbcConnection query(String query, JdbcConnection.ResultSetConsumer resultConsumer) throws SQLException
      Execute a SQL query.
      Parameters:
      query - the SQL query
      resultConsumer - the consumer of the query results
      Returns:
      this object for chaining methods together
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements
      See Also:

    • queryAndMap

      public <T> T queryAndMap(String query, JdbcConnection.ResultSetMapper<T> mapper) throws SQLException
      Execute a SQL query and map the result set into an expected type.
      Type Parameters:
      T - type returned by the mapper
      Parameters:
      query - the SQL query
      mapper - the function processing the query results
      Returns:
      the result of the mapper calculation
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements
      See Also:

    • call

      public JdbcConnection call(String sql, JdbcConnection.CallPreparer callPreparer, JdbcConnection.ResultSetConsumer resultSetConsumer) throws SQLException
      Execute a stored procedure.
      Parameters:
      sql - the SQL query; may not be null
      callPreparer - a JdbcConnection.CallPreparer instance which can be used to set additional parameters; may be null
      resultSetConsumer - a JdbcConnection.ResultSetConsumer instance which can be used to process the results; may be null
      Returns:
      this object for chaining methods together
      Throws:
      SQLException - if anything unexpected fails

    • query

      public JdbcConnection query(String query, JdbcConnection.StatementFactory statementFactory, JdbcConnection.ResultSetConsumer resultConsumer) throws SQLException
      Execute a SQL query.
      Parameters:
      query - the SQL query
      statementFactory - the function that should be used to create the statement from the connection; may not be null
      resultConsumer - the consumer of the query results
      Returns:
      this object for chaining methods together
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements
      See Also:

    • prepareQuery

      public JdbcConnection prepareQuery(String[] multiQuery, JdbcConnection.StatementPreparer preparer, JdbcConnection.BlockingMultiResultSetConsumer resultConsumer) throws SQLException, InterruptedException
      Execute multiple SQL prepared queries where each query is executed with the same set of parameters.
      Parameters:
      multiQuery - the array of prepared queries
      preparer - the function that supplies arguments to the prepared statement; may not be null
      resultConsumer - the consumer of the query results
      Returns:
      this object for chaining methods together
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements
      InterruptedException
      See Also:

    • prepareQuery

      public JdbcConnection prepareQuery(String[] multiQuery, JdbcConnection.StatementPreparer[] preparers, JdbcConnection.BlockingMultiResultSetConsumer resultConsumer) throws SQLException, InterruptedException
      Execute multiple SQL prepared queries where each query is executed with the same set of parameters.
      Parameters:
      multiQuery - the array of prepared queries
      preparers - the array of functions that supply arguments to the prepared statements; may not be null
      resultConsumer - the consumer of the query results
      Returns:
      this object for chaining methods together
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements
      InterruptedException
      See Also:

    • queryAndMap

      public <T> T queryAndMap(String query, JdbcConnection.StatementFactory statementFactory, JdbcConnection.ResultSetMapper<T> mapper) throws SQLException
      Execute a SQL query and map the result set into an expected type.
      Type Parameters:
      T - type returned by the mapper
      Parameters:
      query - the SQL query
      statementFactory - the function that should be used to create the statement from the connection; may not be null
      mapper - the function processing the query results
      Returns:
      the result of the mapper calculation
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements
      See Also:

    • queryWithBlockingConsumer

      public JdbcConnection queryWithBlockingConsumer(String query, JdbcConnection.StatementFactory statementFactory, JdbcConnection.BlockingResultSetConsumer resultConsumer) throws SQLException, InterruptedException
      Throws:
      SQLException
      InterruptedException

    • prepareQueryWithBlockingConsumer

      public JdbcConnection prepareQueryWithBlockingConsumer(String preparedQueryString, JdbcConnection.StatementPreparer preparer, JdbcConnection.BlockingResultSetConsumer resultConsumer) throws SQLException, InterruptedException
      Execute a SQL prepared query.
      Parameters:
      preparedQueryString - the prepared query string
      preparer - the function that supplied arguments to the prepared statement; may not be null
      resultConsumer - the consumer of the query results
      Returns:
      this object for chaining methods together
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements
      InterruptedException
      See Also:

    • prepareQuery

      public JdbcConnection prepareQuery(String preparedQueryString) throws SQLException
      Executes a SQL query, preparing it if encountering it for the first time.
      Parameters:
      preparedQueryString - the prepared query string
      Returns:
      this object for chaining methods together
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements

    • prepareQuery

      public JdbcConnection prepareQuery(String preparedQueryString, JdbcConnection.StatementPreparer preparer, JdbcConnection.ResultSetConsumer resultConsumer) throws SQLException
      Execute a SQL prepared query.
      Parameters:
      preparedQueryString - the prepared query string
      preparer - the function that supplied arguments to the prepared statement; may not be null
      resultConsumer - the consumer of the query results
      Returns:
      this object for chaining methods together
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements
      See Also:

    • prepareQueryAndMap

      public <T> T prepareQueryAndMap(String preparedQueryString, JdbcConnection.StatementPreparer preparer, JdbcConnection.ResultSetMapper<T> mapper) throws SQLException
      Execute a SQL prepared query and map the result set into an expected type..
      Type Parameters:
      T - type returned by the mapper
      Parameters:
      preparedQueryString - the prepared query string
      preparer - the function that supplied arguments to the prepared statement; may not be null
      mapper - the function processing the query results
      Returns:
      the result of the mapper calculation
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements
      See Also:

    • prepareUpdate

      public JdbcConnection prepareUpdate(String stmt, JdbcConnection.StatementPreparer preparer) throws SQLException
      Execute a SQL update via a prepared statement.
      Parameters:
      stmt - the statement string
      preparer - the function that supplied arguments to the prepared stmt; may be null
      Returns:
      this object for chaining methods together
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements
      See Also:

    • prepareQuery

      public JdbcConnection prepareQuery(String preparedQueryString, List<?> parameters, JdbcConnection.ParameterResultSetConsumer resultConsumer) throws SQLException
      Execute a SQL prepared query.
      Parameters:
      preparedQueryString - the prepared query string
      parameters - the list of values for parameters in the query; may not be null
      resultConsumer - the consumer of the query results
      Returns:
      this object for chaining methods together
      Throws:
      SQLException - if there is an error connecting to the database or executing the statements
      See Also:

    • print

      public void print(ResultSet resultSet)

    • print

      public void print(ResultSet resultSet, Consumer<String> lines)

    • delimiter

      private String delimiter(int columnCount, int[] columnSizes)

    • findMaxLength

      private int[] findMaxLength(ResultSet resultSet) throws SQLException
      Throws:
      SQLException

    • isConnected

      public boolean isConnected() throws SQLException
      Throws:
      SQLException

    • isValid

      public boolean isValid() throws SQLException
      Throws:
      SQLException

    • connection

      public Connection connection() throws SQLException
      Throws:
      SQLException

    • connection

      public Connection connection(boolean executeOnConnect) throws SQLException
      Throws:
      SQLException

    • parseSqlStatementString

      protected List<String> parseSqlStatementString(String statements)

    • close

      public void close() throws SQLException
      Close the connection and release any resources.
      Specified by:
      close in interface AutoCloseable
      Throws:
      SQLException

    • doClose

      private void doClose() throws SQLException
      Throws:
      SQLException

    • readAllCatalogNames

      public Set<String> readAllCatalogNames() throws SQLException
      Get the names of all of the catalogs.
      Returns:
      the set of catalog names; never null but possibly empty
      Throws:
      SQLException - if an error occurs while accessing the database metadata

    • readAllSchemaNames

      public Set<String> readAllSchemaNames(Predicate<String> filter) throws SQLException
      Get the names of all of the schemas, optionally applying a filter.
      Parameters:
      filter - a Predicate to test each schema name; may be null in which case all schema names are returned
      Returns:
      the set of catalog names; never null but possibly empty
      Throws:
      SQLException - if an error occurs while accessing the database metadata

    • tableTypes

      public String[] tableTypes() throws SQLException
      Throws:
      SQLException

    • readAllTableNames

      public Set<TableId> readAllTableNames(String[] tableTypes) throws SQLException
      Get the identifiers of all available tables.
      Parameters:
      tableTypes - the set of table types to include in the results, which may be null for all table types
      Returns:
      the set of TableIds; never null but possibly empty
      Throws:
      SQLException - if an error occurs while accessing the database metadata

    • readTableNames

      public Set<TableId> readTableNames(String databaseCatalog, String schemaNamePattern, String tableNamePattern, String[] tableTypes) throws SQLException
      Get the identifiers of the tables.
      Parameters:
      databaseCatalog - the name of the catalog, which is typically the database name; may be an empty string for tables that have no catalog, or null if the catalog name should not be used to narrow the list of table identifiers
      schemaNamePattern - the pattern used to match database schema names, which may be "" to match only those tables with no schema or null if the schema name should not be used to narrow the list of table identifiers
      tableNamePattern - the pattern used to match database table names, which may be null to match all table names
      tableTypes - the set of table types to include in the results, which may be null for all table types
      Returns:
      the set of TableIds; never null but possibly empty
      Throws:
      SQLException - if an error occurs while accessing the database metadata

    • connectionString

      public String connectionString(String urlPattern)
      Returns a JDBC connection string using the current configuration and url.
      Parameters:
      urlPattern - a String representing a JDBC connection with variables that will be replaced
      Returns:
      a String where the variables in urlPattern are replaced with values from the configuration

    • username

      public String username()
      Returns the username for this connection
      Returns:
      a String, never null

    • database

      public String database()
      Returns the database name for this connection
      Returns:
      a String, never null

    • resolveNativeType

      protected int resolveNativeType(String typeName)
      Provides a native type for the given type name. There isn't a standard way to obtain this information via JDBC APIs so this method exists to allow database specific information to be set in addition to the JDBC Type.
      Parameters:
      typeName - the name of the type whose native type we are looking for
      Returns:
      A type constant for the specific database or -1.

    • resolveJdbcType

      protected int resolveJdbcType(int metadataJdbcType, int nativeType)
      Resolves the supplied metadata JDBC type to a final JDBC type.
      Parameters:
      metadataJdbcType - the JDBC type from the underlying driver's metadata lookup
      nativeType - the database native type or -1 for unknown
      Returns:
      the resolved JDBC type

    • readSchema

      public void readSchema(Tables tables, String databaseCatalog, String schemaNamePattern, Tables.TableFilter tableFilter, Tables.ColumnNameFilter columnFilter, boolean removeTablesNotFoundInJdbc) throws SQLException
      Create definitions for each tables in the database, given the catalog name, schema pattern, table filter, and column filter.
      Parameters:
      tables - the set of table definitions to be modified; may not be null
      databaseCatalog - the name of the catalog, which is typically the database name; may be null if all accessible databases are to be processed
      schemaNamePattern - the pattern used to match database schema names, which may be "" to match only those tables with no schema or null to process all accessible tables regardless of database schema name
      tableFilter - used to determine for which tables are to be processed; may be null if all accessible tables are to be processed
      columnFilter - used to determine which columns should be included as fields in its table's definition; may be null if all columns for all tables are to be included
      removeTablesNotFoundInJdbc - true if this method should remove from tables any definitions for tables that are not found in the database metadata, or false if such tables should be left untouched
      Throws:
      SQLException - if an error occurs while accessing the database metadata

    • supportedTableTypes

      protected String[] supportedTableTypes()

    • isTableType

      protected boolean isTableType(String tableType)

    • resolveCatalogName

      protected String resolveCatalogName(String catalogName)

    • getColumnsDetails

      protected Map<TableId,List<Column>> getColumnsDetails(String databaseCatalog, String schemaNamePattern, String tableName, Tables.TableFilter tableFilter, Tables.ColumnNameFilter columnFilter, DatabaseMetaData metadata, Set<TableId> viewIds) throws SQLException
      Throws:
      SQLException

    • getAttributeDetails

      protected Map<TableId,List<Attribute>> getAttributeDetails(TableId tableId)

    • readTableColumn

      protected Optional<ColumnEditor> readTableColumn(ResultSet columnMetadata, TableId tableId, Tables.ColumnNameFilter columnFilter) throws SQLException
      Returns a ColumnEditor representing the current record of the given result set of column metadata, if included in column.include.list.
      Throws:
      SQLException

    • overrideColumn

      protected ColumnEditor overrideColumn(ColumnEditor column)
      Allow implementations an opportunity to adjust the current state of the ColumnEditor that has been seeded with data from the column metadata from the JDBC driver. In some cases, the data from the driver may be misleading and needs some adjustments.
      Parameters:
      column - the column editor, should not be null
      Returns:
      the adjusted column editor instance

    • readPrimaryKeyNames

      public List<String> readPrimaryKeyNames(DatabaseMetaData metadata, TableId id) throws SQLException
      Throws:
      SQLException

    • readTableUniqueIndices

      public List<String> readTableUniqueIndices(DatabaseMetaData metadata, TableId id) throws SQLException
      Throws:
      SQLException

    • readPrimaryKeyOrUniqueIndexNames

      protected List<String> readPrimaryKeyOrUniqueIndexNames(DatabaseMetaData metadata, TableId id) throws SQLException
      Throws:
      SQLException

    • isTableUniqueIndexIncluded

      protected boolean isTableUniqueIndexIncluded(String indexName, String columnName)
      Allows the connector implementation to determine if a table's unique index should be include when resolving a table's unique indices.
      Parameters:
      indexName - the index name
      columnName - the column name
      Returns:
      true if the index is to be included; false otherwise.

    • cleanupPreparedStatement

      private void cleanupPreparedStatement(PreparedStatement statement)

    • createPreparedStatement

      private PreparedStatement createPreparedStatement(String preparedQueryString)

    • executeWithoutCommitting

      public JdbcConnection executeWithoutCommitting(String... statements) throws SQLException
      Executes a series of statements without explicitly committing the connection.
      Parameters:
      statements - a series of statements to execute
      Returns:
      this object so methods can be chained together; never null
      Throws:
      SQLException - if anything fails

    • isNullable

      protected static boolean isNullable(int jdbcNullable)

    • singleResultMapper

      public <T> JdbcConnection.ResultSetMapper<T> singleResultMapper(JdbcConnection.ResultSetExtractor<T> extractor, String error) throws SQLException
      Throws:
      SQLException

    • querySingleValue

      public static <T> T querySingleValue(Connection connection, String queryString, JdbcConnection.StatementPreparer preparer, JdbcConnection.ResultSetExtractor<T> extractor) throws SQLException
      Throws:
      SQLException

    • buildSelectWithRowLimits

      public String buildSelectWithRowLimits(TableId tableId, int limit, String projection, Optional<String> condition, Optional<String> additionalCondition, String orderBy)

    • readTableStatement

      public Statement readTableStatement(CommonConnectorConfig connectorConfig, OptionalLong tableSize) throws SQLException
      Allow per-connector query creation to override for best database performance depending on the table size.
      Throws:
      SQLException

    • readTablePreparedStatement

      public PreparedStatement readTablePreparedStatement(CommonConnectorConfig connectorConfig, String sql, OptionalLong tableSize) throws SQLException
      Allow per-connector prepared query creation to override for best database performance depending on the table size.
      Throws:
      SQLException

    • getColumnValue

      public Object getColumnValue(ResultSet rs, int columnIndex, Column column, Table table) throws SQLException
      Reads a value from JDBC result set and execute per-connector conversion if needed
      Throws:
      SQLException

    • rowToArray

      public Object[] rowToArray(Table table, ResultSet rs, ColumnUtils.ColumnArray columnArray) throws SQLException
      Converts a ResultSet row to an array of Objects
      Throws:
      SQLException

    • quotedTableIdString

      public String quotedTableIdString(TableId tableId)
      Converts a table id into a string with all components of the id quoted so non-alphanumeric characters are properly handled.
      Parameters:
      tableId -
      Returns:
      formatted string

    • quotedColumnIdString

      public String quotedColumnIdString(String columnName)
      Prepares qualified column names with appropriate quote character as per the specific database's rules.

    • loadKeyStore

      public KeyStore loadKeyStore(String filePath, char[] passwordArray)
      Read JKS type keystore/truststore file according related password.