Postgres use CopyManger copyIn with a fileDescriptor inputStream

Support
#1

Hi guys,

I am trying to copy a large dataset file into postgres. I am having trouble with the production server in that postgres cannot access the created temporary files. So I am wondering if you would know how I could get something like the following to work (inserting from an inputStream):

 try (Connection con = DriverManager.getConnection(url, user, password)) {

            CopyManager cm = new CopyManager((BaseConnection) con);
            
            String fileName = "src/main/resources/friends.txt";

            try (FileInputStream fis = new FileInputStream(fileName);
                    InputStreamReader isr = new InputStreamReader(fis, 
                            StandardCharsets.UTF_8)) {

                cm.copyIn("COPY friends FROM STDIN WITH DELIMITER '|'", isr);
            }

        }

I cannot figure out how to get the “Connection” as a BaseConnection

I have been trying:

DataSource dataSource = persistence.getDataSource();
    try (Connection connection = dataSource.getConnection()) {
        CopyManager cm = new CopyManager((BaseConnection) connection);
        cm.copyIn("COPY friends FROM STDIN WITH DELIMITER '|'",fileStorageAPI.openStream(fileDescriptor));

    }

But get the following error:
java.lang.ClassCastException: class com.haulmont.cuba.core.sys.jdbc.ProxyConnection cannot be cast to class org.postgresql.core.BaseConnection (com.haulmont.cuba.core.sys.jdbc.ProxyConnection is in unnamed module of loader org.apache.catalina.loader.ParallelWebappClassLoader @5bf17128; org.postgresql.core.BaseConnection is in unnamed module of loader java.net.URLClassLoader @3daa422a)

Unless there is some other way to go about it?

This works:

  String url = "jdbc:postgresql://localhost:5432/"+AppContext.getProperty("cuba.dataSource.dbName");

        try (Connection conn = DriverManager.getConnection(url, AppContext.getProperty("cuba.dataSource.username"), AppContext.getProperty("cuba.dataSource.password"))) {
            CopyManager cm = new CopyManager((BaseConnection)conn);
            cm.copyIn(queryString, fileStorageAPI.openStream(fileDescriptor));
        }

but pretty convinced it is not a good practice…?

#2

Hi,
If you look into the source code of the com.haulmont.cuba.core.sys.jdbc.ProxyConnection class, you will see that it wraps another connection.
The unwap() method is used to unwrap underlying connections from proxies.

This code should work:

BaseConnection baseConn = connection.unwrap(BaseConnection.class);
#3

Aweseome, thank you.

I thought I had tried something like that but apparently not…

For anyone else that needs. Working code:

String queryString =  "COPY my_table " +
                                  "FROM STDIN WITH (format csv, header); " ;

try (BaseConnection conn = persistence.getDataSource().getConnection().unwrap(BaseConnection.class)) {
                    CopyManager cm = new CopyManager(conn);
                    cm.copyIn(queryString, fileStorageAPI.openStream(fileDescriptor));
}
1 Like