exporting sql query result to a csv or excel

Question

I want to write the result of an sql query to a csv or excel file and save it in a particular folder.I would like to know if this can be achieved using a java program which can be reused for any sql query result.I would also like to know if this can be used for different types of databases(oracle,mysql,MS sql server etc).I plan to attach the saved file to an email(is this possible directly exporting a sql query result to an email).Please help.

Answer 1

Simplest solution.

Main Method

 private List<String> resultSetArray=new ArrayList<>();
 private String username ="";     // Enter DB Username
 private String password = "";    // Enter DB password
 private String url = "";         // Enter DB URL

 Connection connection=DriverManager.getConnection(url,user,pwd);   

 public static void main(String args[]) throws Exception{

        fetchDataFromDatabase("SQL queries", connection);
        printToCsv(resultArray);                

 }

fetchDataFromDatabase

The code below count the number of columns in a table, and store in a result array.

private void fetchDataFromDatabase(String selectQuery,Connection connection) throws  Exception{
            try {


                Statement stmt = connection.createStatement();
                ResultSet rs = stmt.executeQuery(selectQuery);
                int numCols = rs.getMetaData().getColumnCount();

                while(rs.next()) {
                    StringBuilder sb = new StringBuilder();

                    for (int i = 1; i <= numCols; i++) {
                        sb.append(String.format(String.valueOf(rs.getString(i))) + " ");

                    }
                    resultSetArray.add(sb.toString());

                }

            } catch (SQLException e) {
                LOGGER.error("Sql exception " + e.getMessage());
            }

        }

printToCsv

 public static void printToCsv(List<String> resultArray) throws Exception{

        File csvOutputFile = new File(file_name);
        FileWriter fileWriter = new FileWriter(csvOutputFile, false);


        for(String mapping : resultArray) {
            fileWriter.write(mapping + "\n");
         }

        fileWriter.close();

    }

Answer 2

With use of openCSV API, you can export your data in csv file.

CSVWriter writer = new CSVWriter(new FileWriter("yourfile.csv"), '\t');
Boolean includeHeaders = true;

java.sql.ResultSet myResultSet = .... //your resultset logic here

writer.writeAll(myResultSet, includeHeaders);

writer.close();

Answer 3

Here is an example:

import java.io.*;
import java.sql.*;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;

public class ExcelFile {
        public static void main(String[] args) {
                try {
                        Class.forName("com.mysql.jdbc.Driver").newInstance();
                        Connection connection = DriverManager.getConnection(
                                        "jdbc:mysql://localhost:3306/test", "root", "root");
                        PreparedStatement psmnt = null;
                        Statement st = connection.createStatement();
                        ResultSet rs = st.executeQuery("Select * from student");

                        HSSFWorkbook wb = new HSSFWorkbook();
                        HSSFSheet sheet = wb.createSheet("Excel Sheet");
                        HSSFRow rowhead = sheet.createRow((short) 0);
                        rowhead.createCell((short) 0).setCellValue("Roll No");
                        rowhead.createCell((short) 1).setCellValue("Name");
                        rowhead.createCell((short) 2).setCellValue("Class");
                        rowhead.createCell((short) 3).setCellValue("Marks");
                        rowhead.createCell((short) 4).setCellValue("Grade");

                        int index = 1;
                        while (rs.next()) {

                                HSSFRow row = sheet.createRow((short) index);
                                row.createCell((short) 0).setCellValue(rs.getInt(1));
                                row.createCell((short) 1).setCellValue(rs.getString(2));
                                row.createCell((short) 2).setCellValue(rs.getString(3));
                                row.createCell((short) 3).setCellValue(rs.getInt(4));
                                row.createCell((short) 4).setCellValue(rs.getString(5));
                                index++;
                        }
                        FileOutputStream fileOut = new FileOutputStream("c:\\excelFile.xls");
                        wb.write(fileOut);
                        fileOut.close();
                        System.out.println("Data is saved in excel file.");
                        rs.close();
                        connection.close();
                } catch (Exception e) {
                }
        }
}

Reference