Get results as CSV from postgresql using hibernate

Question

I execute a query which should return the results as a CSV to the STDOUT.

When I execute my query in the pgAdmin I successfully get results.

However when I execute the same query using hibernate I gets the following exception:

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet

I mustn't show the tables structure but I know that the sql is fine(I've copied the entire content of "sql" then I execute it in pgAdmin); The query looks like:

String sql = "COPY (" + sqlQuery + ") TO STDOUT WITH CSV";

Then I execute it as the following:

Query query = getEntityManager().createNativeQuery(sql);

Object result = query.getSingleResult(); // I also tried the other get results method...(`getFirstresult()` has returned 0)

In any related questions I have found, I saw that the OP put the csv into a file instead of stdout.

Is it possible to return csv result using hibernate?

Thanks in advance!

Why use Hibernate here? A simple JDBC query (or plus a CSV library) should be much easier. — Adrian Shum, Oct 20 '16 at 04:20
@AdrianShum That's the way I'm going to solve this issue... I just want to make sure that it is not possible using Hibernate... Thanks! :) — SometimesNeedSomeHelp, Oct 20 '16 at 12:00

score 1 · Accepted Answer · edited May 23 '17 at 12:07

1

AFAIK, COPY is not supported natively by PostgreSQL JDBC driver (last tested on postgresql-9.4.1208.jre7). Thus, Hibernate can not run the command.

If you really need to use COPY you should consider a CopyManager: how to copy a data from file to PostgreSQL using JDBC?

But personally, I would advocate you change your approach. Loading data with COPY looks like a kind of a hack to me.

edited May 23 '17 at 12:07

Community

1
1

answered Oct 19 '16 at 14:15

Boris Schegolev

3,601
5
21
34

Thank you very much for the awesome answer! :) Beside the accept, I'd vote up but I don't have enough reputation... – SometimesNeedSomeHelp Oct 20 '16 at 12:08

Jeronimo Backes · Answer 2 · 2016-10-20T07:24:56.807

0

You can have this done with univocity-parsers using two lines of code. You just need to get a resultset from your query and do this:

CsvRoutines routines = new CsvRoutines();
routines.write(resultset, new File("/path/to/output.csv"), "UTF-8");

The write() method takes care of everything. The resultset is closed by the routine automatically.

edited Oct 20 '16 at 07:24

answered Oct 20 '16 at 03:47

Jeronimo Backes

6,141
2
25
29

Get results as CSV from postgresql using hibernate

2 Answers2