I want to copy data from file to PostgreSQL DB using JDBC. I was using JDBC statement object to copy the file into DB. It is very slow.
I came to know that we can also use copy out command to copy file to DB. But, how can I do that with JDBC. Even good reference material having an example of copy in JDBC would help.
PS: thanks in advance
This works...
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
public class PgSqlJdbcCopyStreamsExample {
public static void main(String[] args) throws Exception {
if(args.length!=4) {
System.out.println("Please specify database URL, user, password and file on the command line.");
System.out.println("Like this: jdbc:postgresql://localhost:5432/test test password file");
} else {
System.err.println("Loading driver");
Class.forName("org.postgresql.Driver");
System.err.println("Connecting to " + args[0]);
Connection con = DriverManager.getConnection(args[0],args[1],args[2]);
System.err.println("Copying text data rows from stdin");
CopyManager copyManager = new CopyManager((BaseConnection) con);
FileReader fileReader = new FileReader(args[3]);
copyManager.copyIn("COPY t FROM STDIN", fileReader );
System.err.println("Done.");
}
}
}
(BaseConnection) con might not work; in certain cases the connection is wrapped in some other connection type (e.g. when using connection pools or spies). What worked for me was to use con.unwrap(BaseConnection.class) instead.
(based on aliasmrchips' answer:) if you have a Groovy environment (like me using it within ANT) you could do it like this (substituting the Oracle specifics with Postgres):
// exec.groovy
this.class.classLoader.rootLoader.addURL('${postgres-jdbc-driver-path}')
PgScript.load()
// PgScript.groovy
// (we cannot use the org.postgres.* classes in exec.groovy already!)
import java.io.FileReader
import java.sql.DriverManager
import org.postgresql.copy.CopyManager
import org.postgresql.core.BaseConnection
class PgScript {
static void load() {
DriverManager.getConnection (
'${jdbc-db-url}', '${db-usr}', '${db-usr-pass}'
).withCloseable {conn ->
new CopyManager((BaseConnection) conn).
copyIn('COPY t FROM STDIN', new FileReader('${sqlfile}'))
}
}
}
Based also on this javaworld.com article.
While there is an accepted answer already, its less likely that we get input data in files and in the required format. In such cases the below code ( java 21 ) may help
private int bulkInsertToTable(List<Games> entities, String tableName, Connection conn) throws Exception {
PgConnection pgConn = conn.unwrap(PgConnection.class);
CopyManager copyManager = new CopyManager(pgConn);
String copySql = String.format(
"COPY %s (column_name_1, c2, c3, c4, c5 ) FROM STDIN WITH DELIMITER '|'",
fullTableName
);
CopyIn copyIn = copyManager.copyIn(copySql);
// actual COPY operation happens here
for (Games entity : entities) {
byte[] bytes = entity.toRow().getBytes(StandardCharsets.UTF_8);
copyIn.writeToCopy(bytes, 0, bytes.length);
}
long rowsInserted = copyIn.endCopy();
conn.commit();
long duration = System.currentTimeMillis() - start;
log.info("Bulk inserted {} records into {} in {} ms", rowsInserted, fullTableName, duration);
return (int) rowsInserted;
}
public String toRow() {
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
return String.join("|",
String.valueOf(column_name_1),
String.valueOf(c2),
dateFormat.format(c3),
String.valueOf(c4),
c5 + "\n");
}
