Java CopyManager类代码示例

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
public void saveAll(PGConnection connection, Stream<TEntity> entities) throws SQLException {

        CopyManager cpManager = connection.getCopyAPI();
        CopyIn copyIn = cpManager.copyIn(getCopyCommand());

        try (PgBinaryWriter bw = new PgBinaryWriter()) {

            // Wrap the CopyOutputStream in our own Writer:
            bw.open(new PGCopyOutputStream(copyIn));

            // Insert Each Column:
            entities.forEach(entity -> this.saveEntity(bw, entity));
        }
    }
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
@Override
public Long call() throws SQLException, IOException {
  try {
    CopyManager mgr = new CopyManager((BaseConnection) conn);
    return mgr.copyIn(sql, pipeIn);
  } finally {
    try {
      pipeIn.close();
    } catch (IOException ignore) {
    }
  }
}
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
@Override
public void execute(Connection connection,
                    TypeConverterStore converterStore,
                    SqlCache sqlCache,
                    DataSourceAdapter dataSourceAdapter) throws Exception {
    String theSql = sql == null ? sqlCache.get(filename) : sql;
    PGConnection pgConn = dataSourceAdapter.unwrapPgConnection(connection);
    CopyManager copyManager = (pgConn).getCopyAPI();
    fileReader = new FileReader(copyFile);
    copyManager.copyIn(theSql, fileReader);
}
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
/**
 *
 * @param con open postgres! connection
 * @param folder the classpath folder to scan for table data files
 * @param truncate if true first truncates the tables
 * @throws Exception
 */
public static void load(Connection con, String folder, boolean truncate) throws Exception {
    con.setAutoCommit(false);
    LOG.info("Load data from " + folder);
    CopyManager copy = ((PGConnection)con).getCopyAPI();
    List<String> tables = listTables(folder);
    if (truncate) {
        truncate(con, tables);
    }
    con.commit();

    for (String table : tables) {
        LOG.debug("Load table " + table);
        InputStreamWithoutHeader in = new InputStreamWithoutHeader(FileUtils.classpathStream(folder + "/" + table + FILE_SUFFIX), '\t', '\n');
        String header = HEADER_JOINER.join(in.header);
        copy.copyIn("COPY " + table + "(" + header + ") FROM STDOUT WITH NULL '\\N'", in);
    }
    con.commit();
}
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
private void metadataToPostgres() {
	try {
		CopyManager cpManager = new CopyManager((BaseConnection) conn);
		st.executeUpdate("TRUNCATE TABLE metadata;");
		String metaFile = this.dataDirectory + File.separator + this.metadataFile;
		this.logger.log(Level.INFO, "Importing metadata from " + metaFile);
		Long n = cpManager.copyIn("COPY metadata FROM STDIN WITH CSV", new FileReader(metaFile));
		this.logger.log(Level.INFO, n + " metadata imported");
	} catch (Exception e) {
		e.printStackTrace();
		System.exit(1);
	}
}
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
public static void testCopy() throws ClassNotFoundException, SQLException, IOException {
	Long start = System.nanoTime();
	Class.forName("org.postgresql.Driver");
	Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.100.8:5866/sqoop", "hive", "highgo");
	CopyManager cm = new CopyManager((BaseConnection) conn);
	Reader reader = new FileReader(new File("result"));
	cm.copyIn("copy testnumberictypes from STDIN", reader);
	Long end = System.nanoTime();
	System.out.println((end - start) / Math.pow(10, 9));
}
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
@Override
public void execute(Connection connection,
                    TypeConverterStore converterStore,
                    SqlCache sqlCache,
                    DataSourceAdapter dataSourceAdapter) throws Exception {
    String theSql = sql == null ? sqlCache.get(filename) : sql;
    PGConnection pgConn = dataSourceAdapter.unwrapPgConnection(connection);
    CopyManager copyManager = (pgConn).getCopyAPI();
    fileWriter = new FileWriter(copyFile);
    copyManager.copyOut(theSql, fileWriter);
}
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
/**
 * Loads known usages from checklistbank backbone.
 */
public IdLookupImpl load(ClbConfiguration clb, boolean includeDeleted) throws SQLException, IOException {
  try (Connection c = clb.connect()) {
    final CopyManager cm = new CopyManager((BaseConnection) c);
    final String delClause = includeDeleted ? "" : " AND deleted is null";

    // first read bulk of regular usages - we add pro parte usage later
    LOG.info("Reading existing nub usages {}from postgres ...", includeDeleted ? "incl. deleted " : "");
    try (Writer writer = new UsageWriter()) {
      cm.copyOut("COPY ("
          + "SELECT u.id, coalesce(NULLIF(trim(n.canonical_name), ''), n.scientific_name), n.authorship, n.year, u.rank, u.kingdom_fk, deleted is not null"
          + " FROM name_usage u join name n ON name_fk=n.id"
          + " WHERE dataset_key = '" + Constants.NUB_DATASET_KEY + "'" + delClause + " AND pp_synonym_fk is null)"
          + " TO STDOUT WITH NULL ''", writer);
      LOG.info("Added {} nub usages into id lookup", usages.size());
    }
    final int uCount = usages.size();

    // now load pro parte keys separately saving us from doing complex aggregations
    LOG.info("Reading existing pro parte nub usages {}from postgres ...", includeDeleted ? "incl. deleted " : "");
    try (Writer writer = new ProParteUsageWriter()) {
      cm.copyOut("COPY ("
          + "SELECT u.id, u.parent_fk, u.pp_synonym_fk, coalesce(NULLIF(trim(n.canonical_name), ''), n.scientific_name), n.authorship, n.year, u.rank, u.kingdom_fk, deleted is not null"
          + " FROM name_usage u join name n ON name_fk=n.id"
          + " WHERE dataset_key = '" + Constants.NUB_DATASET_KEY + "'" + delClause + " AND pp_synonym_fk is not null"
          + " ORDER BY pp_synonym_fk)"
          + " TO STDOUT WITH NULL ''", writer);
      LOG.info("Added {} pro parte usages into id lookup", usages.size() - uCount);
    }
    LOG.info("Loaded existing nub with {} usages and max key {} into id lookup", usages.size(), keyMax);
  }
  return this;
}
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
@Override
public void initNeo(NeoUsageWriter writer) throws Exception {
  try (BaseConnection c = (BaseConnection) clb.connect()) {
    final CopyManager cm = new CopyManager(c);
    cm.copyOut("COPY ("
        + "SELECT u.id, u.parent_fk, u.basionym_fk, u.rank,"
        + " coalesce(u.status, CASE WHEN (u.is_synonym) THEN 'SYNONYM'::taxonomic_status ELSE 'ACCEPTED'::taxonomic_status END),"
        + " u.nom_status, n.scientific_name, c.citation"
        + " FROM name_usage u JOIN name n ON u.name_fk=n.id LEFT JOIN citation c ON u.name_published_in_fk=c.id"
        + " WHERE u.dataset_key = '" + key + "')"
        + " TO STDOUT WITH NULL ''", writer);
  }
}
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
/**
 * Executes a COPY operation and reads the output stream to a file
 * @param dbName
 * @param sql
 * @param file
 * @return
 * @throws IOException
 * @throws ClassNotFoundException
 * @throws SQLException
 * @throws Exception
 */
private File exportData(String server, String dbName, String sql, File file) throws IOException, ClassNotFoundException, SQLException, Exception{
	//
	// Get DBUtils instance for the specified database
	//
	//ORDSPostgresDBUtils dbUtils = new ORDSPostgresDBUtils(server, dbName, user, password);

	//
	// Get QueryRunner instance for the specified database
	//
	QueryRunner qr = new QueryRunner(server, dbName);

	//
	// Create a writer for outputting the CSV to the file, and get
	// the Postgres DB connection we'll use for reading the input from
	//
	BufferedWriter writer = new BufferedWriter(new OutputStreamWriter( new FileOutputStream(file), "UTF8"));
	Connection conn = qr.getConnection();

	try {
		//
		// Obtain CopyManager from the Postgres Connection
		//
		PGConnection pg_conn = (PGConnection)conn;
		CopyManager copyManager = pg_conn.getCopyAPI();

		//
		// Copy out the data to the writer
		//
		copyManager.copyOut(sql, writer);
	} catch (Exception e) {
		file.deleteOnExit();
		throw new Exception("Error exporting table", e);
	} finally {
		//
		// Close writer
		//
		writer.close();

		//
		// Close DB connection
		//
		conn.close();
	}
	return file;
}
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
@Override
public void appendDataFromFile(String server, String dbName, String tableName, File file, boolean headerRow, boolean useColumnNames) throws Exception{

	//
	// Get DBUtils instance for the specified database
	//
	//ORDSPostgresDBUtils dbUtils = new ORDSPostgresDBUtils(server, dbName, user, password);

	//
	// Get QueryRunner instance for the specified database
	//
	QueryRunner qr = new QueryRunner(server, dbName);


	//
	// Define the copy operation
	//

	String sql = "COPY "+tableName;
	if (useColumnNames) {
		String names[];
		
		if (headerRow) {
			names = getColumnNames(server, dbName, tableName, file, true);
		}
		else {
			names = this.getColumnNamesFromDatabase(server, dbName, tableName);
		}
		sql += " ("+GeneralUtils.implode(",", names) + ")";
	}
	sql +=  " FROM STDIN WITH CSV ENCODING 'UTF8'";
	if ( headerRow ) {
		sql +=  " HEADER";
	}
	
	//
	// Create a reader for getting the data from the file and get
	// the Postgres DB connection we'll use for writing the output
	//
	BufferedReader reader = new BufferedReader( new InputStreamReader( new FileInputStream(file), "UTF8"));

	Connection conn = qr.getConnection();

	try {
		//
		// Obtain CopyManager from the Postgres Connection
		//
		PGConnection pg_conn = (PGConnection)conn;
		CopyManager copyManager = pg_conn.getCopyAPI();	
		//
		// Copy in the data
		//
		copyManager.copyIn(sql, reader);
		
	}finally {
		//
		// Close writer
		//
		reader.close();

		//
		// Close DB connection
		//
		conn.close();
	}
}
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
/**
 * This is a blocking call - run in an execBlocking statement
 * import data in a tab delimited file into columns of an existing table
 * Using only default values of the COPY FROM STDIN Postgres command
 * @param path - path to the file
 * @param tableName - name of the table to import the content into
 */
public void importFile(String path, String tableName) {

 long recordsImported[] = new long[]{-1};
 vertx.<String>executeBlocking(dothis -> {

  try {
    String host = postgreSQLClientConfig.getString(HOST);
    int port = postgreSQLClientConfig.getInteger(PORT);
    String user = postgreSQLClientConfig.getString(_USERNAME);
    String pass = postgreSQLClientConfig.getString(_PASSWORD);
    String db = postgreSQLClientConfig.getString(DATABASE);

    log.info("Connecting to " + db);

    Connection con = DriverManager.getConnection(
      "jdbc:postgresql://"+host+":"+port+"/"+db, user , pass);

    log.info("Copying text data rows from stdin");

    CopyManager copyManager = new CopyManager((BaseConnection) con);

    FileReader fileReader = new FileReader(path);
    recordsImported[0] = copyManager.copyIn("COPY "+tableName+" FROM STDIN", fileReader );

  } catch (Exception e) {
    log.error(messages.getMessage("en", MessageConsts.ImportFailed), e);
    dothis.fail(e);
  }
  dothis.complete("Done.");

}, whendone -> {

  if(whendone.succeeded()){

    log.info("Done importing file: " + path + ". Number of records imported: " + recordsImported[0]);
  }
  else{
    log.info("Failed importing file: " + path);
  }

});

}
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
@Test
@Ignore("not used right now in read code")
public void testCopy100RecordsIntoFile() throws Exception {
    System.out.println("Starting");

    int a = 0;

    long start = System.currentTimeMillis();
    try (Connection connection = dbManager.getConnection();
         PreparedStatement ps = connection.prepareStatement(ReportingDBDao.insertMinute)) {

        String userName = "[email protected]";
        long minute = (System.currentTimeMillis() / AverageAggregatorProcessor.MINUTE) * AverageAggregatorProcessor.MINUTE;

        for (int i = 0; i < 100; i++) {
            ReportingDBDao.prepareReportingInsert(ps, userName, 1, 0, (byte) 0, 'v', minute, (double) i);
            ps.addBatch();
            minute += AverageAggregatorProcessor.MINUTE;
            a++;
        }

        ps.executeBatch();
        connection.commit();
    }

    System.out.println("Finished : " + (System.currentTimeMillis() - start)  + " millis. Executed : " + a);


    try (Connection connection = dbManager.getConnection();
         Writer gzipWriter = new OutputStreamWriter(new GZIPOutputStream(new FileOutputStream(new File("/home/doom369/output.csv.gz"))), "UTF-8")) {

        CopyManager copyManager = new CopyManager(connection.unwrap(BaseConnection.class));


        String selectQuery = "select pintype || pin, ts, value from reporting_average_minute where project_id = 1 and email = '[email protected]'";
        long res = copyManager.copyOut("COPY (" + selectQuery + " ) TO STDOUT WITH (FORMAT CSV)", gzipWriter);
        System.out.println(res);
    }


}
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
/**
 * Uses the given {@link Connection connection} to COPY the table information from the table given
 * as table name to the directory given as testDataDirectory.
 *
 * @param connection The connection over which to execute the COPY from the database.
 * @param testDataDirectory The test data directory where the result of the COPY is stored.
 * @param tableName The name of the table which is currently dumped from the database.
 *
 * @throws SQLException if the COPY command on the SQL server fails.
 * @throws IOException if the dump result could not be written successfully.
 */
private static void dumpTableInformation(
    final Connection connection, final File testDataDirectory, final String tableName)
        throws SQLException, IOException {
  final CopyManager manager = new CopyManager((BaseConnection) connection);
  try (FileWriter fw = new FileWriter(new File(testDataDirectory, tableName + ".sql"))) {
    manager.copyOut("COPY " + tableName + " TO STDOUT", fw);
  } catch (final IOException exception) {
    CUtilityFunctions.logException(exception);
  } 
}
 

import org.postgresql.copy.CopyManager; //导入依赖的package包/类
/**
 * This returns the COPY API for the current connection.
 * @since 8.4
 */
public CopyManager getCopyAPI() throws SQLException;