Skip to main content
A JDBC topic loads the rows returned by a SQL query executed over a JDBC connection, and feeds them to channels. It is configured through IJdbcTopicConfigurator, which extends the shared ITopicConfigurator. For the general topic and channel concepts, see the data loading overview.

​
How to run a SQL query as a topic

  1. Register a named JDBC connection in the global settings, under IJdbcTopicConfigurator.connectionSetting(connectionName), holding a JdbcConnectionSettings.
  2. Call configurator.channels().addChannel(channelName, tableName) to declare the channel that loads rows into the target table.
  3. Call configurator.topics().addTopic(topicName, IJdbcTopicConfigurator.class) to declare a JDBC topic.
  4. Call connection(connectionName) to name the connection the topic loads through. Topics that do not name one use the connection registered under IJdbcTopicConfigurator.DEFAULT_CONNECTION.
  5. Call sql(query) to set the SQL query producing the topic’s rows. This is required.
  6. Call attachChannel(channelName) on the topic to feed the channel.
The following example registers a products-db connection, then loads the product table into the Products table. The query uses column aliases to rename its columns to the target-table fields:

​
How are JDBC connections registered?

A connection is registered in the global settings under connectionSetting(name), which holds a JdbcConnectionSettings. Build the settings with JdbcConnectionSettings.of(url, username, password, driverClass). All four arguments are required and must be non-blank, and driverClass is the fully-qualified name of the JDBC driver class. The settings mask the password in their toString(). Read the values from the application environment, such as properties or a vault, rather than hardcoding them. Topics reference a connection by name, so the party that registers the credentials and the party that declares the topics can be different. A topic referencing a connection name that is never registered fails when the data load configuration is assembled. Single-database applications can register their one connection under the IJdbcTopicConfigurator.DEFAULT_CONNECTION setting: topics that do not call connection() use it automatically. The JDBC sources are synthesized from the registered connections, so there is no separate source bean to declare. Each topic names the one connection it loads through, so a single application can read from several databases at once by registering several connections and pointing each topic at the right one.

​
How are query columns mapped to table fields?

The query’s column names, or their aliases, are matched by name against the fields of the tables targeted by the attached channels. A column whose name matches a field fills that field. To rename a query column to the target field, alias it in the query with SELECT column AS field. The example above renames product_id, name, and color to ProductId, ProductName, and ProductColor this way.

​
How to pass parameters to the query

The SQL query may contain JDBC placeholders, written as ?. Bind each placeholder to a load-scope entry with parameterOrder(List<String>). Each placeholder is bound, in order, to the value of the load-scope entry named at the same position in the list. The load scope carries the parameters supplied when the load is requested, so the same topic can return different rows from one load to the next. Leave parameterOrder unset, or pass an empty list, when the query has no placeholder. The following example filters on a cob_date column with a single ? placeholder, and binds it to the asOfDate entry of the load scope:
  • Load data: general topic, channel, and field calculator concepts.
  • Load CSV files: file selection, column parsers, and parsing options for the CSV topic.
  • Load tuples from a supplier: rows built in code by a supplier function, including tuples derived from a datastore query.