How to Convert ResultSet to CSV in Java

Convert ResultSet to CSV in Java

In this example, We will show simple program example about, How to convert ResultSet to CSV in Java. The example has been tested with MySQL database server and output shared in the same post.

Project Structure

How to Convert ResultSet to CSV in Java

Maven Configuration (pom.xml)

In order to convert ResultSet to CSV in Java. We have to include the third party library called Apache Commons CSV to the project.

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>com.dineshkrish</groupId>
	<artifactId>CommonCollection</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	
	<dependencies>
		
		<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-csv -->
		<dependency>
		    <groupId>org.apache.commons</groupId>
		    <artifactId>commons-csv</artifactId>
		    <version>1.4</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
		    <groupId>mysql</groupId>
		    <artifactId>mysql-connector-java</artifactId>
		    <version>5.1.6</version>
		</dependency>
	
	</dependencies>
	
</project>

Table Structure (MySQL)

Step 1 (Create Database) : -> CREATE DATABASE dineshkrish;

Step 2 (Select Database) : -> USE dineshkrish;

Step 3 (Create Table) : -> CREATE TABLE customer (customer_id INT, customer_name VARCHAR(20), customer_city VARCHAR(20));


Step 4 (Insert Records) ->
INSERT INTO customer VALUES(101, ‘Dinesh’, ‘New Yark’);
INSERT INTO customer VALUES(102, ‘John’, ‘Chennai’);
INSERT INTO customer VALUES(103, ‘Smith’, ‘Mumbai’);
INSERT INTO customer VALUES(104, ‘William’, ‘Dellas’);
INSERT INTO customer VALUES(105, ‘James’, ‘Bangalore’);

How to Convert ResultSet to CSV in Java

Getting the Connection (ConnectionProvider.java)

package com.dineshkrish;

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

public class ConnectionProvider {

	private static Connection connection;

	private static final String DRIVER_NAME = "com.mysql.jdbc.Driver";

	// Connection URL 
	private static final String CONNECTION_URL = "jdbc:mysql://localhost:3306/dineshkrish";
	
	// your user name
	private static final String USERNAME = "root";

	// your password
	private static final String PASSWORD = "root";
	
	public static Connection getConnection() {

		try {

			Class.forName(DRIVER_NAME);

			connection = DriverManager.getConnection(CONNECTION_URL, USERNAME, PASSWORD);

		} catch (ClassNotFoundException e) {

			e.printStackTrace();
			
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return connection;
	}
}

Passing the Query to Database (SQLService.java)

package com.dineshkrish;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 
 * @author Dinesh Krishnan
 *
 */

public class SQLService {

	public static ResultSet getResult(String query) {

		// getting the connection
		Connection connection = ConnectionProvider.getConnection();

		ResultSet result = null;

		try {
		
			// creating the statement
			Statement stmt = connection.createStatement();
			
			if (!query.isEmpty()) {

				// injecting the query
				result = stmt.executeQuery(query);
			}

			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		return result;
	}

}

Calling the Service Class (Application.java)

package com.dineshkrish;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Scanner;

import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;

/**
 * 
 * @author Dinesh Krishnan
 *
 */

public class Application {

	public static void main(String[] args) throws IOException, SQLException {

		// creating the csv format
		CSVFormat format = CSVFormat.DEFAULT.withRecordSeparator("\n");

		// file name
		final String FILE_NAME = "customer.csv";
		
		// creating the file object
		File file = new File(FILE_NAME);
		
		// creating file writer object
		FileWriter fw = new FileWriter(file);

		// creating the csv printer object
		CSVPrinter printer = new CSVPrinter(fw, format);

		Scanner scanner = new Scanner(System.in);
		
		System.out.println("Enter the query ----> ");
		
		// reading the query from user as input
		String query = scanner.nextLine();
		
		// printing the result in 'CSV' file
		printer.printRecords(SQLService.getResult(query));
		
		System.out.println("Query has been executed successfully...");
		
		// closing all resources
		scanner.close();

		fw.close();

		printer.close();

	}
}

Download Source Code

Download the source code here

Run it

Enter the query —->
SELECT * FROM customer
Query has been executed successfully…

Output

How to Convert ResultSet to CSV in Java

References

1. Creating SQL Connection in Java
2. Apache Commons CSV Documentation
3. Java SQL API Documentation

Hello, folks, I am a founder of dineshkrish.com. I love Java and Open source technologies, If you find my tutorials are useful, please consider making donations to these charities.