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
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’);
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
References
1. Creating SQL Connection in Java
2. Apache Commons CSV Documentation
3. Java SQL API Documentation
Hello, folks, I am a founder of idineshkrishnan.com. I love open source technologies, If you find my tutorials are useful, please consider making donations to these charities.
No Responses