Java CopyManager.copyOut方法代码示例

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包/类
@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);
  } 
}