What is a CData JDBC Driver?

CData JDBC Drivers are powerful tools that allow Java developers to connect their applications to more than 270 data sources, treating each source as if it were a traditional relational database. This capability simplifies data integration and enables seamless interaction with data from SaaS, NoSQL, Big Data, and more.

CData JDBC Drivers are meticulously designed to adhere to the standard JDBC API, ensuring seamless integration with Java applications. This adherence guarantees compatibility with features such as connection pooling, transaction management, and statement execution, enabling developers to leverage familiar tools and techniques while working with diverse data sources.

In this article, we explore how to use a CData JDBC Driver to:

Connect to a data source
Retrieve table (or entity) metadata
Query data for desired results
Execute stored procedures for specific actions

For demonstration, we will use the CData JDBC Driver for CSV, but the principles apply to any of the 270+ data sources we support.

Connecting to a Data Source

The CData JDBC Driver for CSV uses the standard JDBC Connection & DriverManager classes.

Creating Connection Objects

Each CData JDBC Driver comes with comprehensive documentation on connecting to its underlying data source. When working with CSV files, you typically create a connection string that includes the path to your CSV files (for example, C:/Users/Public/MyCSVs). Below is a simple example of creating a JDBC connection using the CData CSV driver:

import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;

    public class CSVConnectionExample {
    public static void main(String[] args) {
        String connectionString = "jdbc:csv:URI=C:/Users/Public/MyCSVs";
        // Depending on your driver, you might need to load it explicitly:
        Class.forName("cdata.jdbc.csv.CSVDriver");
        
        try (Connection connection = DriverManager.getConnection(connectionString)) {
            System.out.println("Connection established!");
            // Use the connection
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Using Metadata Calls for Schema Discovery

Once connected, you can use JDBC metadata calls to explore the structure of your data. The driver surfaces non-relational sources (like CSV files, SaaS, or services) as tables, views, and columns in a tabular model. For CSV files:

Files in a directory appear as database tables (read/write) or views (read-only).
Fields within the CSV files appear as columns.

Retrieving Table Listings

In JDBC, you typically use the DatabaseMetaData interface to discover schema details such as tables and columns. Below is a simplified example of how to list all tables (i.e., CSV files) available in a directory.

import java.sql.*;

public class SchemaDiscoveryExample {
public static void main(String[] args) {
    String connString = "jdbc:csv:URI=C:/Users/Public/MyCSVs";
    
    try (Connection conn = DriverManager.getConnection(connString)) {
        DatabaseMetaData metaData = conn.getMetaData();
        // Get tables: Depending on the driver, set arguments to null or patterns as needed
        try (ResultSet tables = metaData.getTables(null, null, "%", null)) {
            while (tables.next()) {
                System.out.println("Table Name: " + tables.getString("TABLE_NAME"));
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
}

Retrieving Column Metadata

Similarly, you can retrieve information about columns within a specific table:

import java.sql.*;

public class ColumnMetadataExample {
    public static void main(String[] args) {
        String connString = "jdbc:csv:URI=C:/Users/Public/MyCSVs";
        String tableName = "MyFile.csv"; // The file in your CSV directory
        
        try (Connection conn = DriverManager.getConnection(connString)) {
            DatabaseMetaData metaData = conn.getMetaData();
            try (ResultSet columns = metaData.getColumns(null, null, tableName, null)) {
                while (columns.next()) {
                    System.out.println("COLUMN_NAME: " + columns.getString("COLUMN_NAME"));
                    System.out.println("TYPE_NAME: " + columns.getString("TYPE_NAME"));
                    System.out.println("IS_NULLABLE: " + columns.getString("IS_NULLABLE"));
                    // Additional metadata if desired
                    System.out.println("---------");
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Querying the Data and Retrieving Results

With CData JDBC Drivers, you use standard JDBC classes (e.g., Statement, PreparedStatement, ResultSet) to retrieve data. The driver handles the logic of reading and parsing CSV files behind the scenes, exposing them as if they were database tables.

Using a Statement and ResultSet

A ResultSet provides a forward-only cursor over your data. It is efficient for reading large datasets as it fetches rows from the data source on demand.

import java.sql.*;

public class QueryExample {
    public static void main(String[] args) {
        String connString = "jdbc:csv:URI=C:/Users/Public/MyCSVs";
        
        try (Connection connection = DriverManager.getConnection(connString);
                Statement statement = connection.createStatement();
                ResultSet rs = statement.executeQuery("SELECT Email, Username FROM [MyFile.csv]")) {
            
            while (rs.next()) {
                System.out.println(
                    rs.getString("Email") + " | " + rs.getString("Username")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Performing Actions Using Stored Procedures

Many CData JDBC Drivers define stored procedures as function-like interfaces for advanced or custom operations beyond standard SELECT, INSERT, UPDATE, and DELETE queries. These stored procedures accept parameters, execute their task, and return relevant results or status codes.

For example, the CData JDBC Driver for CSV offers a MoveFile procedure to move a file from one location to another:

SourcePath: The path of the file to move
DestinationPath: The new destination path

You can call these procedures using a simple EXEC statement. In Java, you might execute it like this:

import java.sql.*;

public class StoredProcedureExample {
    public static void main(String[] args) {
        String connString = "jdbc:csv:URI=C:/Users/Public/MyCSVs";
        String execStatement = "EXEC MOVEFILE @SourcePath = 'C:/TestFolder/Account.csv', @DestinationPath = 'C:/Users/Public/MyCSVs'";
        
        try (Connection connection = DriverManager.getConnection(connString);
                Statement stmt = connection.createStatement()) {
            
            // Execute the stored procedure
            stmt.execute(execStatement);
            System.out.println("MoveFile stored procedure executed successfully.");
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Depending on the driver and procedure, you may also use CallableStatement to manage input/output parameters more explicitly.

JDBC Drivers for Data Developers

CData JDBC Drivers enhance the capabilities of JDBC by offering consistent, SQL-based connectivity to more than 270 data sources beyond traditional databases, including SaaS, NoSQL, and Big Data systems. They provide advanced features such as efficient querying with ResultSet, data modification, batch processing, transaction management, connection pooling, and the ability to call stored procedures.

With the CData JDBC Drivers, you get Java libraries to access your data in Java projects, all through familiar SQL. Request a free, 30-day trial and start building data-driven apps today!

Feedback