Learn more
 

Discovering SaaS, Big Data, and NoSQL Metadata with JDBC


Understanding your data is critical for processes that range from analytics to transformation to storage and replication. The JDBC standard provides a straightforward means of accessing database metadata, whether programmatically from Java applications or in JDBC-driven tools and applications. With the CData JDBC Drivers, you can access metadata from more than 110+ data sources, working with a relational model of your RDBMS, SaaS, Big Data, and even NoSQL data.

This article will walk through using the CData JDBC Drivers in Java code to connect to a data source and retrieve and view data source metadata enabling deeper understanding and improved consumption. While the examples and sample code refer to the CData JDBC Driver for Salesforce, the principals can be applied to any of the CData JDBC drivers.

Connect to Data


Before you can discover data source metadata, you first need to connect to the data source. If you do not already have a CData JDBC Driver installed, you can download a free, 30-day trial of any of the drivers. Be sure to use a driver for a data source where you have actual data.

With the driver downloaded and installed, you are ready to connect to your data. Create a new Java project and add the JDBC driver(s) to the build path.

  • In Eclipse:
    1. Right click on Project and select Build Path -> Add External Archives.
    2. Navigate to the JAR file for the JDBC Driver (cdata.jdbc.salesforce.jar, for example).
  • In IntelliJ:
    1. Right click on Project and select Open module.
    2. Navigate to Libraries under Project Settings and click Add to locate and add the JDBC JAR file.

With the driver added to the build path, you can explore the code needed to connect to the data source. Start by optionally (as of JDBC 4.0) loading the driver and using the DriverManager class to create a connection to the data source (Salesforce in this case). To connect, you will need to configure a JDBC URL, passing the necessary connection properties and values (the specifics of which properties are needed can be found in the online Help documentation for the data source): 

String sfURL = "jdbc:salesforce:[email protected];Password=my_sf_password;SecurityToken=my_sf_security_token;";

//optional
Class.forName("cdata.jdbc.salesforce.SalesforceDriver");

Connection sfConn = DriverManager.getConnection(sfURL);

Once you have created the connection, you can start discovering the metadata for the data source.

Discover Tables & Views


The getTables method of the DatabaseMetaData interface in Java allows you to retrieve the list of tables from a data source. Depending on your data source, these tables and views can represent of variety of datasets, ranging from simple REST endpoints of SaaS APIs to hierarchical data structures like those commonly found in NoSQL databases. The CData drivers are built to present a sensible data model, no matter what the data source. The code below retrieves the full list of tables and views from Salesforce:

Sample Code

  //Collect the metadata
  DatabaseMetaData sfMeta = sfConn.getMetaData();
  
  //Retrieve all tables and views
  ResultSet rs = sfMeta.getTables(null, null, "%", null);

  //Print the results
  while (rs.next()) {
    System.out.println(rs.getString("TABLE_NAME"));
  }

Expected Output

  AcceptedEventRelation
  Account
  AccountCleanInfo
  AccountContactRole
  ...
  VerificationHistory
  VisualforceAccessMetrics
  Vote
  WebLink

If you wish to limit the results to tables, views, system tables, or any combination, pass an array of strings as the type parameter to the getTables method:

  • Tables: getTables(null, null, "%", new String[]{"TABLE"})
  • Views: getTables(null, null, "%", new String[]{"VIEW"})
  • System Tables: getTables(null, null, "%", new String[]{"SYSTEM TABLE"})

The sample code above only displays the table name. For reference, the getTables method returns the following columns:

Column Name Data Type Description
TABLE_CAT String The table catalog.
TABLE_SCHEM String The table schema.
TABLE_NAME String The table name.
TABLE_TYPE String The table type.
REMARKS String The table description.

Once you know the tables in your data source, you can use the getColumns method to get information about the columns for a specified table.

Discover Columns


The getColumns method of the DatabaseMetaData interface in Java allows you to retrieve the list of columns for selected tables from a data source. The tableNamePattern parameter allows you to restrict the results by table name. For the SaaS, Big Data, and NoSQL data sources supported by CData drivers, the existing data structures (whether those are parts of a REST response for a SaaS API or individual elements found in the structure of a NoSQL document) are parsed based on your configuration of the driver to present a database-like model, no matter the data source. The code sample below retrieves the list of columns for the Account table from Salesforce:

Sample Code

  //Collect the metadata
  DatabaseMetaData sfMeta = sfConn.getMetaData();
  
  //Retrieve the columns of the "Account" table
  ResultSet rs = sfMeta.getColumns(null, null, "Account", null);

  //Output the results
  while (rs.next()) {
    System.out.println(rs.getString("COLUMN_NAME"));
  }

Expected Output

  Id
  IsDeleted
  MasterRecordId
  Name
  ...
  NumberofLocations__c
  UpsellOpportunity__c
  SLASerialNumber__c
  SLAExpirationDate__c

Note the columns appended with "__c". These are custom columns retrieved from the Salesforce API through the JDBC driver. Because the driver retrieves the metadata dynamically to detect columns, adding or removing a custom field is reflected when you reconnect.

The sample code only displays the column name for the request table. For reference, the getColumns method returns the following columns for each table retrieved:

Column Name Data Type Description
TABLE_CAT String The database name.
TABLE_SCHEM String The table schema.
TABLE_NAME String The table name.
COLUMN_NAME String The column name.
DATA_TYPE int The data type identified by the value of a constant defined in java.sql.Types.
TYPE_NAME String The data type name used by the driver.
COLUMN_SIZE int The length in characters of the column or the numeric precision.
BUFFER_LENGTH int The buffer length.
DECIMAL_DIGITS int The column scale or number of digits to the right of the decimal point.
NUM_PREC_RADIX int The radix, or base.
NULLABLE int Whether the column can contain null as defined by the following JDBC DatabaseMetaData constants: columnNoNulls (0) or columnNullable (1).
REMARKS String The column description.
COLUMN_DEF String The default value for the column.
SQL_DATA_TYPE int Reserved by the specification.
SQL_DATETIME_SUB int Reserved by the specification.
CHAR_OCTET_LENGTH int The maximum length of binary and character-based columns.
ORDINAL_POSITION int The column index, starting at 1.
IS_NULLABLE String Whether a null value is allowed: YES or NO.
SCOPE_CATALOG String The table catalog that is the scope of a reference attribute.
SCOPE_SCHEMA String The table schema that is the scope of a reference attribute.
SCOPE_TABLE String The table name that is the scope of a reference attribute.
SOURCE_DATA_TYPE int The source type of a distinct type. Or, a user-generated Ref type. If DATA_TYPE is not DISTINCT, this value is null. If a user-generated Ref, this value is null.
IS_AUTOINCREMENT String Whether the column value is assigned by Salesforce in fixed increments.
IS_GENERATEDCOLUMN String Whether the column is generated: YES or NO.
ISREADONLY boolean Whether the column is read-only.
ISKEY boolean Whether the column is a key.

With the tables and columns discovered for your data source, you are ready to begin performing informed analytics, transformations, and other operations on your data.

Universal Metadata Discovery


With JDBC Drivers for more than 110 different SaaS, Big Data, and NoSQL data sources, CData allows you to easily discover the metadata for your data source. Since all of the drivers adhere to the JDBC standard, the metadata queries will be consistent no matter the data source. Thanks to innovative data processing functionality, CData drivers are even able to provide a reliable, database-like model for your NoSQL data. Download a free, 30 day trial of any of the JDBC Drivers and see the CData difference for yourself.

In this article

Related articles