Writing data from database to .csv file

Java 8Object Oriented ProgrammingProgramming

You can write data into a .csv file using the OpenCSV library and, you can communicate with MySQL database through a Java program using the mysql-java-connector.

Maven dependency

The following are the dependencies you need to include in your pom.xml file to write data to a .csv file from a database table.


Writing data to a CSV file

The CSVWriter class of the com.opencsv package represents a simple CSV writer. While instantiating this class you need to pass a Writer object representing the file, to which you want to write the data, as a parameter to its constructor. It provides methods named writeAll() and writeNext() to write data to a .csv file

Using the writeNext() method

The writeNext() method of the CSVWriter class writes the next line to the .csv file


Assume we have created a table with name empDetails and populated it using the following queries −

CREATE TABLE empDetails (ID INT, Name VARCHAR(255), Salary INT, start_date VARCHAR(255), Dept VARCHAR(255));
Insert INTO empDetails values (1, 'Krishna', 2548, '2012-01-01', 'IT');
Insert INTO empDetails values (2, 'Vishnu', 4522, '2013-02-26', 'Operations');
Insert INTO empDetails values (3, 'Raja', 3021, '2016-10-10', 'HR');
Insert INTO empDetails values (4, 'Raghav', 6988, '2012-01-01', 'IT');

The following Java program creates a csv file from the above-created table.

import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import com.opencsv.CSVWriter;
public class DbToCSV {
   public static void main(String args[]) throws SQLException, IOException {
      //Getting the connection
      String url = "jdbc:mysql://localhost/mydb";
      Connection con = DriverManager.getConnection(url, "root", "password");
      System.out.println("Connection established......");
      //Creating the Statement
      Statement stmt = con.createStatement();
      //Query to retrieve records
      String query = "Select * from empDetails";
      //Executing the query
      stmt.executeQuery("use mydb");
      ResultSet rs = stmt.executeQuery(query);
      //Instantiating the CSVWriter class
      CSVWriter writer = new CSVWriter(new FileWriter("D://output.csv"));
      ResultSetMetaData Mdata = rs.getMetaData();
      //Writing data to a csv file
      String line1[] = {Mdata.getColumnName(1), Mdata.getColumnName(2), Mdata.getColumnName(3), Mdata.getColumnName(4), Mdata.getColumnName(5)};
      String data[] = new String[5];
      while(rs.next()) {
         data[0] = new Integer(rs.getInt("ID")).toString();
         data[1] = rs.getString("Name");
         data[2] = new Integer(rs.getInt("Salary")).toString();
         data[3] = rs.getString("start_date");
         data[4] = rs.getString("Dept");
      //Flushing data from writer to file
      System.out.println("Data entered");


Connection established......
Data entered

If you verify the generated .csv file you can observe its contents as follows −

Published on 12-Sep-2019 06:47:26