Narayana Tutorial

Online Java Tutorial Blog

CSV File Writing By ResultSet

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.

 

 

 

Narayanaswamy

Hello! I am Narayanaswamy founder and admin of narayanatutorial.com. I have been working in IT industry more than 7 years. NarayanaTutorial is my web technologies blog. My specialties are Java / J2EE, Spring, Hibernate, Struts, Webservices, PHP, Oracle, MySQL, SQLServer, Web Hosting and Website Development.

I am a self learner and passionate about training and writing. I am always trying my best to share my knowledge through my blog.

The Author

Narayanaswamy

Narayanaswamy

Hello! I am Narayanaswamy founder and admin of narayanatutorial.com. I have been working in IT industry more than 7 years. NarayanaTutorial is my web technologies blog. My specialties are Java / J2EE, Spring, Hibernate, Struts, Webservices, PHP, Oracle, MySQL, SQLServer, Web Hosting and Website Development. I am a self learner and passionate about training and writing. I am always trying my best to share my knowledge through my blog.

Leave a Reply

Narayana Tutorial © 2016 Frontier Theme
Show Button
Hide Button