Export table from Postgres database(on server) to csv file(on local) in java

Question

I would like to export a table from the database to CSV file on my local machine. Now, I can connect to the database and display all the elements in the console with System.out.println(). But I want to redirect these elements in a csv file. Please help.

    //STEP 1. Import required packages
    import java.sql.*;

    public class ExportDataToCSV {
     // JDBC driver name and database URL
    static final String JDBC_DRIVER = "org.postgresql.Driver";  
    static final String DB_URL = "jdbc:postgresql://[localhost]:54432/database";

     //  Database credentials
    static final String USER = "login";
    static final String PASS = "pwd";

     public static void main(String[] args) {
     Connection conn = null;
     Statement stmt = null;
     try{
        //STEP 2: Register JDBC driver
    Class.forName("org.postgresql.Driver");

    //STEP 3: Open a connection
    System.out.println("Connecting to database...");
    conn = DriverManager.getConnection(DB_URL,USER,PASS);

    //STEP 4: Execute a query
    System.out.println("Creating statement...");
    stmt = conn.createStatement();
    String sql;
    sql = "SELECT id, age, first, last FROM table";
    ResultSet rs = stmt.executeQuery(sql);

    //STEP 5: Extract data from result set
    while(rs.next()){
       //Retrieve by column name
       String id  = rs.getString("id");
       String age = rs.getString("age");
       String first = rs.getString("first");
       String last = rs.getString("age");

       //Display values
       System.out.print("ID: " + id);
       System.out.print(", Age: " + age);
       System.out.print(", First: " + first);
       System.out.println(", Last: " + last);
    }

    //STEP 5: Clean-up environment
    rs.close();
    stmt.close();
    conn.close();  }catch(SQLException se){
    //Handle errors for JDBC
    se.printStackTrace();  }catch(Exception e){
    //Handle errors for Class.forName
    e.printStackTrace();  }finally{
    //finally block used to close resources
    try{
       if(stmt!=null)
          stmt.close();
    }catch(SQLException se2){
    }// nothing we can do
    try{
       if(conn!=null)
          conn.close();
    }catch(SQLException se){
       se.printStackTrace();
    }//end finally try  }//end try }//end main }//end FirstExample

This question has been asked before: http://stackoverflow.com/questions/1517635/save-postgres-sql-output-to-csv-file?rq=1 — mwarren, Jun 23 '14 at 15:50
Thanks for answering mwarren ! But this answer allows you to copy the content of the table in an another server's directory (tmp). I'w like to copy it from the server to my local machine. — Kazman, Jun 23 '14 at 15:53

score 4 · Accepted Answer · answered Jun 23 '14 at 16:22

4

Use PgJDBC's CopyManager, which you obtain from the PGConnection you can cast a java.sql.Connection for a PgJDBC connection to.

If you use a connection pool you might need to find out how to unwrap the proxied Connection to get the real underlying PgJDBC Connection object.

CopyManager supports COPY ... TO STDOUT, allowing you to stream server-generated CSV data to the client application.

answered Jun 23 '14 at 16:22

Craig Ringer

259,831
56
584
684

2

Thank you very very much ! It works !! The correct statement is : CopyManager cm = new CopyManager((BaseConnection) conn); cm.copyOut("COPY (SELECT * FROM noticadmin.inex_transaction) TO STDOUT WITH DELIMITER ';'", new BufferedWriter(new FileWriter("C:/export_transaction.csv"))); – Kazman Jun 24 '14 at 08:11
1

@Kazman I recommend `WITH (FORMAT CSV)` instead - it'll handle things like newlines embedded in strings better. – Craig Ringer Jun 24 '14 at 11:27

score 0 · Answer 2 · answered Jun 23 '14 at 16:09

0

Well it depends, but you probably need to write a web app with a web page with a response containing the .csv file and a Content-Type response header of "text/csv"

answered Jun 23 '14 at 16:09

mwarren

2,110
1
18
23

Export table from Postgres database(on server) to csv file(on local) in java

2 Answers2

Linked