Export CSV file from MySQL database in JAVA program

In this tutorial, I will show you how to export data from mysql database in java program!
I used Commons CSV library. You can download at below link.
https://commons.apache.org/proper/commons-csv/index.html

Create two functions to get database connection and generate csv file.

private Connection getDBConnection() throws ClassNotFoundException, SQLException{
        Class.forName("com.mysql.jdbc.Driver");  
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/DB_NAME", "USER_NAME", "PASSWORD");   
        return conn;
    }

*** Replace actual database name, username and password in DB_NAME, USER_NAME, PASSWORD ***

private boolean exportFile(String sqlQuery, String csvFilePath){
        boolean isSuccess = true;
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        BufferedWriter writer = null;
        
        try{
            //Get mysql database connection
            conn = getDBConnection();
            stmt = conn.prepareStatement(sqlQuery);
            
            //Execute query and retrieve data
            rs = stmt.executeQuery();
            
            //File writer 
            writer = new BufferedWriter(new FileWriter(csvFilePath));
            
            //Write to the file
            CSVPrinter printer = new CSVPrinter(writer, CSVFormat.EXCEL.withHeader(rs));
            printer.printRecords(rs); 
            printer.close(true);
            
        }catch(IOException | ClassNotFoundException | SQLException ex){
            System.err.print(ex.getMessage());
            isSuccess = false;
        }finally{
            //Close all resources
            try {
                if(writer != null)
                    writer.close();
                
                if(rs != null)
                    rs.close();
                
                if(stmt != null)
                    stmt.close();
                
                if(conn != null)
                    conn.close();
            } catch (SQLException | IOException ex) {
                System.err.print(ex.getMessage());
            }
        }
        return isSuccess;
    }

CSVFormat.EXCEL.withHeader(rs) - Add resultset's column name as header into csv file

printer.printRecords(rs) - Write all resultset's data line by line into csv file

Finally, Call exportFile function in main function. It need to pass two parameters (1) Any database query, (2) CSV file location.

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;

public class CSVExporter {
    private Connection getDBConnection() throws ClassNotFoundException, SQLException{
        //...getDBConnection function codes...
    }
    
    private boolean exportFile(String sqlQuery, String csvFilePath){
        //...exportFile function codes...
    }
    
    public static void main(String[] args) throws ClassNotFoundException, SQLException{
        CSVExporter exporter = new CSVExporter();
        boolean success = exporter.exportFile("SELECT * FROM countries LIMIT 100", ".\\countries.csv");
        if(success){
            System.out.println("Country list is exported");
        }else{
            System.err.println("Something went wrong while exporting!");
        }
    }
}

Run the program, You will found csv file like this

More Examples

To read an Excel CSV file by index

Reader in = new FileReader("\\countries.csv");
Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(in);
for (CSVRecord record : records) {
    String countryId = record.get(0);
    String country = record.get(1);
}

To read an Excel CSV file by header auto detection 

Reader in = new FileReader("\\countries.csv");
Iterable<CSVRecord> records = CSVFormat.EXCEL.withFirstRecordAsHeader().parse(in);
for (CSVRecord record : records) {
    String isoCode = record.get("iso_code");
    String country = record.get("country_name");
}

If you like this post, please share it. Thank you for reading.

July 09, 2019 Tags : java