how to copy data from file to PostgreSQL using JDBC?
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.");
}
}
}
(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.