Export sql-Abfrage Ergebnis in eine csv oder excel

Mit Einsatz von openCSV-API können Sie den export Ihrer Daten in csv-Datei.

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();

Es ist schwierig, export ResultSet die Daten von jedem tool.

ex: beim Export ResultSet Daten zu .csv-Datei exportiert nicht richtig, wenn Daten enthält (,)

bitte konsultieren Sie bitte die folgenden java-code :

es funktioniert perfekt mit jedem beliebigen query-input und alle Art von Daten im ResultSet

package com.demo.export;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class dataExportService {
    public void getDefendants(Connection con , String db) throws Exception  { 
        @SuppressWarnings("unused")
        Workbook readWorkbook = WorkbookFactory.create(new FileInputStream(".xls file path(C:\\Users\\CEPL\\Desktop\\test.xls)") );
        @SuppressWarnings("resource")
        Workbook writeWorkbook = new HSSFWorkbook();
        Sheet desSheet = writeWorkbook.createSheet("new sheet");

        Statement stmt = null;
        ResultSet rs = null;
        try{
            String query ="QUERY";

            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnsNumber = rsmd.getColumnCount();

            Row desRow1 = desSheet.createRow(0);
            for(int col=0 ;col < columnsNumber;col++) {
                Cell newpath = desRow1.createCell(col);
                newpath.setCellValue(rsmd.getColumnLabel(col+1));
            }
            while(rs.next()) {
                System.out.println("Row number" + rs.getRow() );
                Row desRow = desSheet.createRow(rs.getRow());
                for(int col=0 ;col < columnsNumber;col++) {
                    Cell newpath = desRow.createCell(col);
                    newpath.setCellValue(rs.getString(col+1));  
                }
                FileOutputStream fileOut = new FileOutputStream(".xls file path(C:\\Users\\CEPL\\Desktop\\test.xls)");
                writeWorkbook.write(fileOut);
                fileOut.close();
            }
        }
        catch (SQLException e) {
            System.out.println("Failed to get data from database");
        }
    }

}

Dies ist meine Lösung.
Code zum einfügen in die main-Klasse:

import java.io.*;
import java.sql.*;
import com.company.*;
/**
 * Created by MAXNIGELNEGRO
*/
  String[] filePath =       new String[] {"C:\\Users\\Documents\\MyFile.csv"};
  String[] driverDB =       new String[] {"oracle.jdbc.driver.OracleDriver"};
  String[] stringConnDB =   new String[] {"jdbc:oracle:thin:@//127.0.0.1:1881/mydb"};
  String[] userDB =         new String[] {"pippo"};
  String[] passDB =         new String[] {"pluto"};
  String[] charSep =        new String[] {";"};
  Boolean colomn=   new Boolean (true);
  String[] queryDB =        new String[] {"select * FROM MYQUERY"};


try{
    System.out.println("---------------File exist?------------" + filePath[0]);
    File fileTemp = new File(filePath[0].toString());
    if (fileTemp.exists()){ 
        fileTemp.delete();
        System.out.println("---------------DELETE FILE------------" + filePath[0] );
                } 
   System.out.println("QUERY: ---->"+ queryDB[0].toString());
   exportQueryToCsv exp = new exportQueryToCsv();
   exp.exportQueryToCsv(filePath,driverDB,stringConnDB,userDB,passDB,queryDB, colomn,charSep);
   if (fileTemp.exists()){ 
     System.out.println("---File created---" + filePath[0]);
  }

}
catch(Exception e){
         e.printStackTrace();
      }

Der Kern-Klasse:

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Created by MAXNIGELNEGRO
 */
public class exportQueryToCsv {
    public exportQueryToCsv(){}
    public static void  exportQueryToCsv (String[] filename, String[] driverDB, String[] connDB
                                            , String[] userDB, String[] passDB, String[] queryDB, Boolean intestaFile
                                            , String[] charSep) throws SQLException, IOException {
        Statement stmt=null;
        ResultSet rset=null;
        Connection conn=null;
        try { DBConn connessione = new DBConn();
        conn=connessione.connect(driverDB[0],connDB[0],userDB[0],passDB[0]);
        conn.setAutoCommit(false);

        stmt = conn.createStatement();

        rset = stmt.executeQuery(queryDB[0]);

        ExportData2CSV csv = new ExportData2CSV();
        csv.ExportData2CSV(rset,filename[0],intestaFile,charSep[0]);

            csv.createFileCsv();
        } catch (SQLException e) {
                e.printStackTrace();
        } catch (IOException e) {
                e.printStackTrace();
        }
        finally {
            if (stmt != null) {stmt.close();}
            if (conn != null) {conn.close();}
            if (rset != null) {rset.close();}



        }


    }
}

Dies ist die Klasse DBConn für die Verbindung zur Datenbank

import java.sql.*;

/**
 * Created by MAXNIGELNEGRO
 */
public class DBConn {
    public DBConn() {
    }
    public Connection connect(String driverDB, String db_connect_str, String db_userid, String db_password) {
        Connection conn;

        try {
            Class.forName(driverDB).newInstance();
            conn = DriverManager.getConnection(db_connect_str, db_userid, db_password);


        } catch (Exception e) {
            e.printStackTrace();
            conn = null;

        }
        return conn;
    }


}

Dies ist die Klasse für die Daten abruft aus der Tabelle auf die Ergebnismenge und schreibt Sie in eine csv-Datei

package com.company;

import java.io.FileWriter;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
 * Created by MAXNIGELNEGRO 
 */
public class ExportData2CSV {
    public ResultSet rset;
    public String filename;
    public Boolean colomnName;
    public String charSep;

    public void ExportData2CSV(ResultSet rset, String filename, Boolean colomnName, String charSep) {
        this.rset = rset;
        this.filename = filename;
        this.colomnName = colomnName;
        this.charSep = charSep;
    }

    public void createFileCsv() throws SQLException, IOException {
        FileWriter cname = null;
        try {

                //WRITE COLOMN NAME
            ResultSetMetaData rsmd = rset.getMetaData();
            cname = new FileWriter(filename);
            if (colomnName) {
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    cname.append(rsmd.getColumnName(i));
                    cname.append(charSep);
                    cname.flush();
                }
                cname.append(System.getProperty("line.separator"));
            }

            //WRITE DATA
            while (rset.next()) {
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    if (rset.getObject(i) != null) {
                        String data = rset.getObject(i).toString().replaceAll(charSep, "");
                        cname.append(data);
                        cname.append(charSep);
                    } else {
                        String data = "null";
                        cname.append(data);
                        cname.append(charSep);
                    }

                }
                //new line entered after each row
                cname.append(System.getProperty("line.separator"));

            }


        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (cname != null) {
                cname.flush();
                cname.close();
            }
            if (rset != null) {
                rset.close();
            }

        }

    }
}

Können Sie JDBC für das abrufen der Datensätze aus DB in java und verwenden Sie dann Apache POI für den Export der Daten in CSV/Excel.

Darüber hinaus können Sie die desktop-API von java zum senden von E-Mails mit Ihrem Standard-Mail-client.

Ja!

Können Sie eine Verbindung zu verschiedenen Datenbank-Typen verwenden von jdbc und erstellen Sie dann ein Excel mit den Ergebnissen (Sehen Sie hier).

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

public class TestAccessExcel {
  public static Connection getConnection() throws Exception {
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
    String url = "jdbc:odbc:excelDB";
    String username = "username";
    String password = "pass";
    Class.forName(driver);
    return DriverManager.getConnection(url, username, password);
  }

  public static void main(String args[]) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      stmt = conn.createStatement();
      String excelQuery = "select * from [Sheet1$]";
      rs = stmt.executeQuery(excelQuery);

      while (rs.next()) {
        System.out.println(rs.getString("BadgeNumber") + " " + rs.getString("FirstName") + " "
            + rs.getString("LastName"));
      }
    } catch (Exception e) {
      System.err.println(e.getMessage());
    } finally {
      try {
        rs.close();
        stmt.close();
        conn.close();

      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}