将表从Postgres数据库（服务器上）导出到Java中的CSV文件（本地） [英] Export table from Postgres database(on server) to csv file(on local) in java

问题描述

我想将数据库中的表导出到本地计算机上的CSV文件
中。现在，我可以连接到数据库，并使用System.out.println（）在控制台中显示
所有元素。但是我
想在csv文件中重定向这些元素。

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

推荐答案

使用PgJDBC的 CopyManager ，可从 PGConnection 您可以投射 java.sql 。

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

如果使用连接池，则可能需要了解如何解开代理的 Connection 获取真正的基础PgJDBC Connection对象。

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 支持复制...到标准输出，允许您将服务器生成的CSV数据流式传输到客户端应用程序。

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

这篇关于将表从Postgres数据库（服务器上）导出到Java中的CSV文件（本地）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！