How to indicate a session instance to Postgresql CopyManager

Question

I have a java web project using hibernate to manage database operations.

There are two SQLs executed in order:

execute "CREATE TEMP temp_table...;" through hibernate native sql query.
execute "COPY temp_table from SDTIN" through org.postgresql.copy.CopyManager to transmit a csv file into temp_table.

My code:

public boolean loadElectricFishData(Integer fileId, File eventfile, File samplefile) {
    Session session = stagingSessionFactory.getCurrentSession();
    String elecCountTableName = "temp_eb_elec_count_" + fileId;

    return createTable(session, elecCountTableName , "nemostaging.eb_elec_count")
            && copyTableFromCSV(session, elecCountTableName, eventfile) ? true : false;
}

private boolean createTable(Session session, String tableName, String likeTableName) {
    SQLQuery query = session.createSQLQuery("CREATE TABLE nemostaging." + tableName + " (LIKE " + likeTableName + " INCLUDING ALL)");
    query.executeUpdate();
    session.flush();
    session.close();
    return true;
}

private boolean copyTableFromCSV(Session session, final String tableName, final File csv) {
    SessionImpl sessionImpl = (SessionImpl)session;
    Connection connection = sessionImpl.getTransactionCoordinator().getJdbcCoordinator().getLogicalConnection().getConnection();
    FileReader fileReader = null;
    try {
        CopyManager copyManager = new CopyManager((BaseConnection) connection);
        fileReader = new FileReader(csv);
        copyManager.copyIn("COPY nemostaging." + tableName + " FROM STDIN DELIMITER ',' CSV HEADER", fileReader);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    } finally {
        if (fileReader != null) {
            try {
                fileReader.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    return false;
}

My problem is I need the same session for these two operations, because temporary table is invisible to another session. While CopyManager will not reuse the same session. Anyone has an idea how I can indicate the current session to org.postgresql.copy.CopyManager but not only the current connection?

Thanks a lot!

------Update-2015-11-13----- Hi Craig Ringer, I tried your suggestion, but got an exception:

java.lang.reflect.UndeclaredThrowableException at com.sun.proxy.$Proxy20.unwrap(Unknown Source) at nz.co.niwa.nemo.uploadservice.core.dao.impl.StagingLoadDataDaoImpl$1.execute(StagingLoadDataDaoImpl.java:88) at org.hibernate.jdbc.WorkExecutor.executeWork(WorkExecutor.java:54) at org.hibernate.internal.SessionImpl$2.accept(SessionImpl.java:1933) at org.hibernate.internal.SessionImpl$2.accept(SessionImpl.java:1930) at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.coordinateWork(JdbcCoordinatorImpl.java:211) at org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:1951) at org.hibernate.internal.SessionImpl.doWork(SessionImpl.java:1937) at nz.co.niwa.nemo.uploadservice.core.dao.impl.StagingLoadDataDaoImpl.loadSpotlightData(StagingLoadDataDaoImpl.java:85) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:319) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) at com.sun.proxy.$Proxy14.loadSpotlightData(Unknown Source) at nz.co.niwa.nemo.uploadservice.core.manage.impl.StagingLoadDataManageImpl.loadSpotlightData(StagingLoadDataManageImpl.java:30) at nz.co.niwa.nemo.uploadservice.core.service.impl.StagingLoadDataServiceImpl.loadSpotlightData(StagingLoadDataServiceImpl.java:44) at nz.co.niwa.nemo.uploadservice.core.test.service.StagingLoadDataServiceTest.testLoadSpotlightData(StagingLoadDataServiceTest.java:39) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74) at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:83) at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:231) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222) at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61) at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:71) at org.junit.runners.ParentRunner.run(ParentRunner.java:300) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:174) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192) Caused by: java.lang.reflect.InvocationTargetException at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:130) at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) ... 55 more Caused by: java.sql.SQLException: Cannot unwrap to org.postgresql.copy.CopyManager at org.postgresql.jdbc4.AbstractJdbc4Connection.unwrap(AbstractJdbc4Connection.java:269) ... 61 more

My code:

public boolean loadSpotlightData(final Integer fileId, final File datafile) {
    final String spotCountTableName = "tmp_eb_spot_count_" + fileId;
    Session session = stagingSessionFactory.getCurrentSession();
    SQLQuery query = session.createSQLQuery("CREATE TEMP TABLE " + spotCountTableName + " (LIKE nemostaging.eb_spot_count INCLUDING ALL)");
    query.executeUpdate();
    session.doWork(new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {
            CopyManager copyManager = connection.unwrap(org.postgresql.copy.CopyManager.class);
            FileReader fileReader = null;
            try {
                fileReader = new FileReader(datafile);
                copyManager.copyIn("COPY " + spotCountTableName + " FROM STDIN DELIMITER ',' CSV HEADER", fileReader);
            } catch (SQLException e) {
                LOG.error("Errors occur while COPY nemostaging." + spotCountTableName + " FROM STDIN DELIMITER ',' CSV HEADER;", e);
            } catch (FileNotFoundException e) {
                LOG.error("The CSV file cannot be found while copying into database.", e);
            } catch (IOException e) {
                LOG.error("Errors occur while reading the csv file.", e);
            } finally {
                if (fileReader != null) {
                    try {
                        fileReader.close();
                    } catch (IOException e) {
                        LOG.error("Errors occur while closing file reader.", e);
                    }
                }
            }
        }
    });

    Query query1 = session.getNamedQuery("staging.loadSpotlightData");
    query1.setParameter("fileId", fileId);
    query1.list();

    return true;
}

Can't you tell your obfuscation layer (=Hibernate) that you want to do all that in a single transaction? — a_horse_with_no_name
@a_horse_with_no_name Thanks for you reply! I tried to include all jobs in one sql-query of hibernate mapping. The problem is I don't know how to use postgresql "COPY" in this situation. "COPY {table} FROM STDIN" needs a client to give inputsteam, I have no idea how to interact with postgresql in my code; "COPY {table} FROM {filename}" needs the file path in the database server. When I used "\COPY" instead of "COPY", hibernate complained syntax error with "\". It seems only "org.postgresql.copy.CopyManager" can manage "COPY" command. — Willie Z
\copy is a psql command, not a SQL command. So yes, you can't use that through JDBC (or anything other than psql). copy from stdin can easily be used from within JDBC (as you do in your example). If you can't control Hibernate's transaction, you need to bypass your obfuscation layer when creating the table as well, and run that statement through plain JDBC on the same connection you use to call the CopyManager — a_horse_with_no_name
@a_horse_with_no_name The point is only the same connection is not enough. Because temporary table will be invisible to another session used by CopyManager. — Willie Z
If you do everything with the same (physical) connection, then all statements will share the same session (physical connection == session). — a_horse_with_no_name

Craig Ringer Craig Ringer · Accepted Answer · 2015-11-12T04:37:26

What you must do is run the Hibernate query, then ask the Hibernate Session to get the underlying Connection object that implements org.postgresql.PGConnection and use that.

You used to be able to unwrap the session with the Session.connection() method, but that's deprecated now. Instead use the doWork API. The java.sql.Connection you get passed is probably wrapped by your connection pool, too, so you might have to unwrap that to get the actual connection object. If you're using JDBC4 drivers and pools (you should be), just use the unwrap(...) method from the Wrapper interface implemented by Connection, e.g. unwrap(org.postgresql.copy.CopyManager.class).

Something like (untested):

session.doWork(
    new Work() {
        public void execute(Connection connection) throws SQLException 
        { 
            CopyManager cm = connection.unwrap(org.postgresql.copy.CopyManager.class);
            ... do stuff ...
        }
    }
);

You can't create a new, separate connection directly from the DriverManager and use it with another session's temp tables. Nor do you need to. It's better to unwrap the pooled connection from the Hibernate session.

How to indicate a session instance to Postgresql CopyManager

1 Answers