When working with JDBC (Java Database Connectivity) to retrieve data from a database, you may sometimes need to export the result of a query into a CSV file for further analysis or reporting. CSV (Comma-Separated Values) is a widely used format for handling tabular data, and Java provides several ways to write data to CSV files. This article will explore how to convert a JDBC ResultSet to a CSV file in Java, using the native Java approach and a third-party library (Apache Commons CSV).
1. Table Structure and Sample Data
Suppose that we have a customers table in a database with the following structure:
The table contains the following sample data:
We will write code to export this data into a CSV file.
2. Using Java Without External Libraries
The code below shows how to convert a ResultSet to a CSV file using core Java functionality.
public class JdbcToCsvExporter {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/customer";
String username = "root";
String password = "password";
String query = "SELECT * FROM customers"; // Replace with your SQL query
try (Connection connection = DriverManager.getConnection(url, username, password); PreparedStatement statement = connection.prepareStatement(query); ResultSet resultSet = statement.executeQuery()) {
// Define the CSV file path
String csvFile = "customers.csv";
// Create a BufferedWriter to write to the CSV file
try (BufferedWriter writer = new BufferedWriter(new FileWriter(csvFile))) {
// Write the header (column names)
int columnCount = resultSet.getMetaData().getColumnCount();
for (int i = 1; i <= columnCount; i++) {
writer.write(resultSet.getMetaData().getColumnName(i));
if (i < columnCount) {
writer.write(",");
}
}
writer.newLine();
// Write the data rows
while (resultSet.next()) {
for (int i = 1; i <= columnCount; i++) {
writer.write(resultSet.getString(i));
if (i < columnCount) {
writer.write(",");
}
}
writer.newLine();
}
System.out.println("CSV file created successfully at: " + csvFile);
} catch (IOException e) {
System.err.println("Error writing to CSV file: " + e.getMessage());
}
} catch (SQLException e) {
System.err.println("SQL error: " + e.getMessage());
}
}
}
The process begins by establishing a JDBC connection to the database using the DriverManager.getConnection() method. Ensure you replace the placeholders for the database URL, username, and password with the actual details of your database. Once connected, a PreparedStatement executes a SQL query to retrieve all records from the customers table. The results of this query are stored in a ResultSet.
To create the CSV file, a BufferedWriter is wrapped around a FileWriter. Initially, a loop iterates over the ResultSet metadata to extract column names, writing them as headers in the CSV. Next, another loop processes each row of the ResultSet, appending the corresponding data values, separated by commas.
When the program is executed, the customers.csv file is generated with the following content:
id,fullname,email,address,city,course 3,Alice Johnson,alice.johnson@example.com,123 Maple Street,New York,Java Programming 4,Bob Smith,bob.smith@example.com,456 Oak Avenue,Los Angeles,Data Science 5,Charlie Brown,charlie.brown@example.com,789 Pine Road,Chicago,Web Development 6,Diana Prince,diana.prince@example.com,101 Birch Lane,Houston,Cybersecurity 7,Ethan Hunt,ethan.hunt@example.com,202 Elm Street,San Francisco,Cloud Computing
3. Using Apache Commons CSV Library
The native Java approach works well but can be tricky for complex CSV needs. The Apache Commons CSV library makes it easier by automatically managing many details of the CSV format. To use Apache Commons CSV, add the following dependency to your pom.xml:
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-csv</artifactId>
<version>1.10.0</version>
</dependency>
The following Java code shows how to use the Apache Commons CSV library to convert a ResultSet into a CSV file.
public class JdbcToCsvWithApacheCommons {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/customer";
String username = "root";
String password = "password";
String query = "SELECT * FROM customers";
try (Connection connection = DriverManager.getConnection(url, username, password);
PreparedStatement statement = connection.prepareStatement(query);
ResultSet resultSet = statement.executeQuery()) {
// Define the CSV file path
String csvFile = "customers_commons.csv";
// Create a BufferedWriter to write to the CSV file
try (BufferedWriter writer = new BufferedWriter(new FileWriter(csvFile));
CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT.withHeader(getColumnNames(resultSet)))) {
// Write the rows
while (resultSet.next()) {
for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
csvPrinter.print(resultSet.getString(i));
}
csvPrinter.println();
}
csvPrinter.flush(); // Ensure data is written to the file
System.out.println("CSV file created successfully at: " + csvFile);
} catch (IOException e) {
System.err.println("Error writing to CSV file: " + e.getMessage());
}
} catch (SQLException e) {
System.err.println("SQL error: " + e.getMessage());
}
}
// Helper method to get the column names from the ResultSet
private static String[] getColumnNames(ResultSet resultSet) throws SQLException {
int columnCount = resultSet.getMetaData().getColumnCount();
String[] columnNames = new String[columnCount];
for (int i = 0; i < columnCount; i++) {
columnNames[i] = resultSet.getMetaData().getColumnName(i + 1);
}
return columnNames;
}
}
In this example, the CSVPrinter class is used to write data in CSV format, configured with CSVFormat.DEFAULT for a standard CSV setup without custom delimiters or escaping rules. The withHeader() method automatically writes column names as headers, fetching them from the ResultSet metadata. This simplifies generating CSV files with well-defined headers matching the database table structure.
Rows from the ResultSet are iterated, and their values are written to the CSV file using csvPrinter.print(). Once all rows are processed, csvPrinter.flush() ensures that any remaining data is written to the file. A helper method, getColumnNames(), extracts column names from the ResultSet metadata and returns them as an array, which is used to define the CSV headers.
When the program is run, it will produce the same result as the previous example.
4. Conclusion
In this article, we demonstrated how to convert a JDBC ResultSet to a CSV file using both a native Java approach and an external library (Apache Commons CSV). The native approach is great for simple scenarios, while Apache Commons CSV provides a more flexible and cleaner way to handle complex CSV export tasks.
5. Download the Source Code
This article covered how to convert a JDBC ResultSet to CSV in Java.
You can download the full source code of this example here: jdbc convert resultset csv
