如何向Postgresql CopyManager指示会话实例

Question

null

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;
}

-----更新--2015-11-13----嗨，克雷格林格，我尝试了你的建议，但得到了一个例外：

我的代码：

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;
}

Answer 1

您必须做的是运行Hibernate查询，然后要求Hibernatesession获取实现org.postgresql.pgconnection的基础connection对象并使用它。

您过去可以使用session.connection()方法解包会话，但现在不推荐使用这种方法。而是使用DoWorkAPI。您传递的java.sql.connection也可能被您的连接池包装，因此您可能必须将其解包才能获得实际的连接对象。如果您正在使用JDBC4驱动程序和池（您应该使用），只需使用connection实现的wrapter接口的unwrap(...)方法，例如unwrap.org.postgresql.copy.copyManager.class)。

类似于（未经测试）：

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