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
More from my site
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.