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
– Adrian Shum, Commented Oct 20, 2016 at 4: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
– SometimesNeedSomeHelp, Commented Oct 20, 2016 at 12:00

Community · Accepted Answer · 2017-05-23 12:07:16Z

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, 2017 at 12:07

CommunityBot

11 silver badge

answered Oct 19, 2016 at 14:15

Boris Schegolev

3,7115 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SometimesNeedSomeHelp Over a year ago

Thank you very much for the awesome answer! :) Beside the accept, I'd vote up but I don't have enough reputation...

Jeronimo Backes · Accepted Answer · 2016-10-20 07:24:56Z

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, 2016 at 7:24

answered Oct 20, 2016 at 3:47

Jeronimo Backes

6,2992 gold badges27 silver badges31 bronze badges

Collectives™ on Stack Overflow

Get results as CSV from postgresql using hibernate

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related