CSV File Writing By ResultSet

In this post I am going to explaining CSV File Writing By ResultSet by using opencsv jar file. You can see what is CSV file?. Find the below example and see how to create csv file by using ResultSet.

Before going to write java program we need to download opencsv jar file and assign to build path.

Here we require database connection class to retrieve data in the form of ResultSet to write into csv file.

DatabaseConnection.java

package com.narayanatutorial.opencsv;

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

public class DatabaseConnection {

    static String driver="oracle.jdbc.driver.OracleDriver";
    static String url="jdbc:oracle:thin:@localhost:1521:xe";
    static String username="TestUSer";
    static String password="TestPwd";
    Connection con=null;
    Statement stmt=null;
    PreparedStatement pstmt=null;
    ResultSet rs=null;
    String SQL="";

     public void CreateConnection()  {
       try {
        Class.forName(driver);
        con = DriverManager.getConnection(url,username,password);

         } catch (Exception e) {
            System.out.println("Exception getting connection.");
            e.printStackTrace();
        }
    }
     public ResultSet getData(){
         try{
             SQL="SELECT * FROM PROFILE";
             stmt=con.createStatement();
             rs=stmt.executeQuery(SQL);
         }catch(SQLException s){
             s.printStackTrace();
         }
         return rs;
     }

     public void closeConnections() throws SQLException {
        try{
        con.commit();
        }catch(Exception e){
            System.out.println("Exception while commit the connection.."+e);
        }


        if (rs != null) {
            rs.close();
            rs = null;
        }

        if (stmt != null) {
            stmt.close();
            stmt = null;
        }

         if (pstmt != null) {
            pstmt.close();
            pstmt = null;
        }
        if (con != null) {
            con.close();
            con = null;
        }
    }
}

OpenCsvWriterByResultSet.java

package com.narayanatutorial.opencsv;

import au.com.bytecode.opencsv.CSVWriter;
import java.io.FileWriter;
import java.sql.ResultSet;

public class OpenCsvWriterByResultSet {

    public static void main(String args[]) {
                String csvFilename = "D:/narayanatutorial/SampleFiles/OpenCSVWriteByResultSet.csv";
        try {
            DatabaseConnection dbcon=new DatabaseConnection();
            CSVWriter writer = new CSVWriter(new FileWriter(csvFilename));
            String[] header="FIRSTNAME,LASTNAME,AGE".split(",");
            writer.writeNext(header);
            dbcon.CreateConnection();
            ResultSet rs=dbcon.getData();
            writer.writeAll(rs, true); //And the second argument is boolean which represents whether you want to write header columns (table column names) to file or not.
            writer.close();
            dbcon.closeConnections();
            System.out.println("CSV file created succesfully.");
        } catch (Exception e) {
            System.out.println("exception :" + e.getMessage());
        }

    }
}

Output

CSV file created succesfully.

Open the generated csv file in the word excel and find the following output.

FIRSTNAME	LASTNAME	AGE
Narayana	Ragi	30

Open the generated csv file in the notepad and find the following output.

"FIRSTNAME","LASTNAME","AGE"
"Narayana","Ragi","'30"

Description

 writer.writeAll(rs, true);

First parameter is ResultSet and the second argument is boolean which represents whether you want to write csv header columns with as it is of table column names or not. if it is Yes then writing database columns as header in the csv file else writing assigned columns as header in the csv file.

 

you can find the above sample program in the github. If you have git account you can download from github and other sample programs also available and find the below github link to download. We used Netbeans IDE to develop sample programs. So you can directly import into Netbeans if you are having Netbeans else create sample project in your IDE and replace src folder.

Git link

I hope you understand CSV File Writing using RestultSet by using opencsv jar file.