JDBC Developer Guide: Getting Started

CData JDBC Drivers are powerful tools that allow developers to connect Java applications with more than 270 cloud and on-prem data sources, treating them as if they were databases. This capability simplifies data integration and enables seamless interaction with data from SaaS, NoSQL, Big Data, and more.

This guide provides a step-by-step approach to building a simple Java console application using the CData JDBC Driver for CSV. The application will:

Prompt the user for the path to CSV files (download sample files)
Connect to that path as if it were a database
Retrieve the available "tables" (i.e., CSV files in the directory)
Query the data from a user-selected table
Display the results in the console

Below, we'll walk through the required setup, code, and how to run the application. While this guide focuses on CSV files, the same approach applies to any of the 270+ data sources supported by CData JDBC Drivers.

Prerequisites

1. Set Up Your Java Development Environment

Install Java: Make sure you have a Java Development Kit (JDK) installed (1.8 or higher). You can download and install from Oracle or use an open-source distribution like OpenJDK.
Choose an IDE or Build Tool: You can use an IDE (e.g., Eclipse, IntelliJ IDEA, NetBeans) or a build tool (e.g., Maven, Gradle) to manage your project. You can also compile and run Java code from the command line with javac and java.

2. Add the CData JDBC Driver for CSV

Download the Driver: Obtain the CData JDBC Driver for CSV from CData's website.
Install the Driver: For Windows or Mac machines, run the installer. For Linux/UNIX machines, extract the files to an appropriate location.
- On Mac, you may need to go to System Settings > Privacy & Security to allow the installer to run.
License the Driver: In a terminal, navigate to the installation directory and run the following command: java -jar cdata.jdbc.csv.jar --license
Enter your name, email, and license key (or TRIAL).
Include the JAR in Your Classpath:
- If you are using an IDE, add the JAR to your project libraries.
- If you are using Maven/Gradle, install or reference the JAR accordingly.
- If you are running via the command line, add the JAR file to your classpath with the -cp option.

3. Prepare Sample CSV Files

Sample CSV Files: Make sure you have one or more CSV files in a directory on your system that you can connect to and query. For example, a folder like C:\TestFolder (on Windows) or /home/user/TestFolder (on macOS/Linux) containing CSV files (download sample files).

4. Create a New Java Project

In Your IDE (e.g., IntelliJ, Eclipse):
1. Create a new Java project (e.g., "CSVJDBCApp").
2. Add the downloaded CData CSV JDBC Driver JAR to the project's library dependencies.
3. Add the license file (LIC) to the project's library dependencies.
From the Command Line:
1. Create a folder for your project (e.g., mkdir CSVJDBCApp).
2. Place your Java source file in CSVJDBCApp/src (or wherever you keep your code).
3. Include the CData JAR in your classpath when compiling and running.

This setup ensures that the driver is accessible to your code when it tries to connect to your CSV directory.

5. Configure the Connection to the CSV Files

To connect to CSV files with the CData JDBC Driver for CSV, you generally use a connection string similar to:

jdbc:csv:URI='C:/path/to/your/csvfiles';RowScanDepth=0;

You can customize various properties (e.g., RowScanDepth, security settings) via the connection string. In the sample below, we include RowScanDepth=0 to optimize how the driver infers column data types.

6. Build the Console Application

Below is a complete console-based Java application that demonstrates the basics:

Prompt the user for the directory containing CSV files.
Establish a JDBC connection to that directory.
List the CSV files in the directory (treated as database tables).
Prompt for a table name, then query and display the first 20 records, limiting output to 4 columns.

Sample Code (file name: CDataCSVApp.java):

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

class CDataCSVApp {
public static void main(String[] args) {
    try {
        BufferedReader buffer = new BufferedReader(new InputStreamReader(System.in));
        
        // 1. Get the directory from the user
        System.out.println("Please enter the path to your CSV files:");
        String path = buffer.readLine();
        
        // 2. Build the connection string and load the driver
        String cnxnString = "jdbc:csv:URI='" + path + "';RowScanDepth=0;";
        Class.forName("cdata.jdbc.csv.CSVDriver");
        Connection cnxn = DriverManager.getConnection(cnxnString);
        
        // 3. List the "tables" (CSV files)
        DatabaseMetaData metaData = cnxn.getMetaData();
        System.out.println("\nAvailable CSV Files (tables):");
        ResultSet tables = metaData.getTables(null, null, "%", null);
        int tableNum = 0;
        while (tables.next()) {
            System.out.println((tableNum + 1) + ". " + tables.getString("TABLE_NAME"));
            tableNum++;
        }
        
        // 4. Prompt the user to select one of the files
        System.out.println("\nPlease input a table name (include '.csv'):");
        String tableName = buffer.readLine();
        
        // 5. Query the data (displaying only the first 20 rows and up to 4 columns)
        String query = "SELECT * FROM [" + tableName + "] LIMIT 20";
        Statement stat = cnxn.createStatement();
        boolean ret = stat.execute(query);
        
        // 6. Display the data
        if (ret) {
            ResultSet tableData = stat.getResultSet();
            int columnCount = tableData.getMetaData().getColumnCount();
            columnCount = Math.min(columnCount, 4); // Limit columns to 4 for readability
            
            // Print column headers (truncate to 20 chars for each column)
            for (int i = 1; i <= columnCount; i++) {
                String colName = tableData.getMetaData().getColumnName(i);
                if (colName.length() > 20) {
                    colName = colName.substring(0, 20);
                }
                System.out.printf("%-20s", colName);
            }
            System.out.println();
            
            // Print separator
            for (int i = 1; i <= columnCount; i++) {
                System.out.print("-------------------");
            }
            System.out.println();
            
            // Print rows
            while (tableData.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    String value = tableData.getString(i);
                    if (value != null && value.length() > 20) {
                        value = value.substring(0, 20);
                    }
                    System.out.printf("%-20s", value != null ? value : "NULL");
                }
                System.out.println();
            }
        }
    } catch (SQLException ex) {
        System.out.println("SQL Exception: " + ex.getMessage());
    } catch (IOException | ClassNotFoundException ex) {
        System.out.println("Exception: " + ex.getMessage());
    }
}
}

Explanation of the Key Steps

Load the CData JDBC Driver: Class.forName("cdata.jdbc.csv.CSVDriver");
This ensures that the driver is registered with the DriverManager so DriverManager.getConnection can use it.
Establish a Connection: Connection cnxn = DriverManager.getConnection("jdbc:csv:URI='" + path + "';RowScanDepth=0;");
Replace path with the folder containing your CSV files. Additional properties can be appended to this string as needed.
Retrieve Tables (CSV Files): DatabaseMetaData metaData = cnxn.getMetaData(); ResultSet tables = metaData.getTables(null, null, "%", null);
A ResultSet is returned containing the names of your CSV files. We print them to let the user pick one.
Prompt User for Table:
We read the user's input from the console to get which CSV file they want to query.
Query Data: String query = "SELECT * FROM [" + tableName + "] LIMIT 20"; Statement stat = cnxn.createStatement(); stat.execute(query);
We run a simple SQL statement that selects all columns (up to 20 rows).
Display Results: We limit to 4 columns and truncate each cell to 20 characters for readability. This is purely for demonstration—you can tailor it to your needs.

7. Run the Application

Compile: javac -cp .;path\to\cdata.jdbc.csv.jar CDataCSV.java
Run: java -cp .;path\to\cdata.jdbc.csv.jar CDataCSV
(On macOS/Linux, replace ; with : as a path separator.)
Test:
1. Enter the path to your CSV files (e.g., C:/TestFolder).
2. Observe the list of CSV files displayed.
3. Enter the file name (e.g., Opportunity.csv).
4. Observe the queried data in the console.

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