JDBC Developer Guide: Spring Boot with the CData JDBC Driver for CSV

CData JDBC Drivers empower Java developers to connect applications with 270+ data sources, including CSV files, as if they were databases. This guide demonstrates how to build a simple Spring Boot REST API that reads data from CSV files using the CData JDBC Driver for CSV.

Prerequisites

Java JDK 1.8+ (Download from Oracle or OpenJDK)
Maven (or Gradle)
Spring Boot (we'll use Maven for this guide)
CData JDBC Driver for CSV (Download here)
CData License File (cdata.jdbc.csv.lic)
- After downloading the CData JDBC Driver, open a terminal, navigate to the JDBC Driver's installation directory and run the following command: java -jar cdata.jdbc.csv.jar --license
  Enter your name, email, and license key (or TRIAL).
Sample CSV files in a directory (e.g., /home/user/TestFolder or C:\TestFolder) (Download here)

1. Project Setup

Follow the steps below to setup the project on your own. Alternatively, you can download the complete source project here: cdata-jdbc-spring-boot.zip.

a. Create a Spring Boot Project

You can use Spring Initializr or your IDE to create a new Maven project with the following dependencies:

Spring Web
Spring JDBC

b. Add the CData JDBC Driver to Your Local Maven Repository

After downloading the CData JDBC Driver for CSV, install it to your local Maven repository so it can be included in your fat JAR:

mvn install:install-file \
    -Dfile=lib/cdata.jdbc.csv.jar \
    -DgroupId=cdata \
    -DartifactId=jdbc-csv \
    -Dversion=24.0.9175.0 \
    -Dpackaging=jar

Note: Adjust the -Dfile path and -Dversion as needed.

2. Add the License File

Copy your cdata.jdbc.csv.lic license file into the src/main/resources directory of your project:

cp /path/to/cdata.jdbc.csv.lic src/main/resources/

This ensures the license file is included in your fat JAR and available on the classpath at runtime.

3. Update Your pom.xml

Add the CData dependency as a standard Maven dependency (no system scope or systemPath):


    cdata
    jdbc-csv
    23.0.8839.0

Your dependencies section should look like this:


    
    org.springframework.boot
    spring-boot-starter-jdbc
    
    
    org.springframework.boot
    spring-boot-starter-web
    
    
    org.springframework.boot
    spring-boot-starter-test
    test
    
    
    cdata
    jdbc-csv
    24.0.9175.0

4. Configure the DataSource

In src/main/resources/application.properties, add:

spring.datasource.url=jdbc:csv:URI='/absolute/path/to/your/csvfiles';RowScanDepth=0;
spring.datasource.driver-class-name=cdata.jdbc.csv.CSVDriver
spring.datasource.username=
spring.datasource.password=

Note:

Use an absolute path for the URI (e.g., /Users/yourname/Downloads/csvfiles).
Ensure the directory contains your CSV files.
Set RowScanDepth to 0 to optimize data type detection over performance (scans all rows in the CSV documents).

5. Create a Simple Repository

Let's create a repository to query CSV data.
src/main/java/com/example/csvjdbc/repository/CsvRepository.java

package com.example.csvjdbc.repository;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public class CsvRepository {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public List listTables() {
        return jdbcTemplate.queryForList(
            "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES", String.class
        );
    }

    public List> queryTable(String tableName) {
        // Limit to 20 rows for demo
        String sql = String.format("SELECT * FROM [%s] LIMIT 20", tableName);
        return jdbcTemplate.queryForList(sql);
    }
}

6. Create a REST Controller

src/main/java/com/example/csvjdbc/controller/CsvController.java

package com.example.csvjdbc.controller;

import com.example.csvjdbc.repository.CsvRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/api/csv")
public class CsvController {

    @Autowired
    private CsvRepository csvRepository;

    @GetMapping("/tables")
    public List getTables() {
        return csvRepository.listTables();
    }

    @GetMapping("/table/{tableName}")
    public List> getTableData(@PathVariable String tableName) {
        return csvRepository.queryTable(tableName);
    }
}

7. Main Application Class

src/main/java/com/example/csvjdbc/CsvJdbcApplication.java

package com.example.csvjdbc;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class CsvJdbcApplication {

    public static void main(String[] args) {
        SpringApplication.run(CsvJdbcApplication.class, args);
    }
}

8. Build and Run the Application

Build the project: ./mvnw clean package
This will create a fat JAR in the target/ directory, including the CData driver and license file.
Run the application: java -jar target/jdbc-spring-boot-0.0.1-SNAPSHOT.jar

9. Test the API

List tables (CSV files):
GET http://localhost:8080/api/csv/tables
Query a table (e.g., Opportunity.csv):
GET http://localhost:8080/api/csv/table/Opportunity.csv

10. Example Output

GET /api/csv/tables

[
    "Opportunity.csv",
    "Accounts.csv"
]

GET /api/csv/table/Opportunity.csv

[
    {
        "Id": "1",
        "Name": "Big Deal",
        "Amount": "10000",
        "Stage": "Closed Won"
    },
    ...
]

Notes & Customization

You can expand the repository to support filtering, pagination, or dynamic column selection.
The CData JDBC Driver supports many advanced features—see the official documentation for more.
If you encounter a licensing error, double-check that cdata.jdbc.csv.lic is present in src/main/resources before building.

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