3

Which is the best way to export a JPA query result in a CSV format? I tried opencsv but it wants a java.sql.ResultSet object and I don't understand how to create it. I tried the following code 

public ResultSet getAllUsersMap() {
    String query = "SELECT p from Participant p ORDER BY p.lastName";
    ResultSet rs = (ResultSet) em.createQuery(query).getResultList();
    return rs;
}

but I receive the exception java.lang.ClassCastException: java.util.Vector cannot be cast to java.sql.ResultSet

Enrico Morelli
  • 185
  • 3
  • 16

1 Answers1

0

JPA is an ORM framework spec. It's not directly related to creating CSV files. The error is just saying that you are casting the return value of method getResultList() to the wrong type.

Using opencsv, something like this should work:

String query = "SELECT p from Participant p ORDER BY p.lastName";
List resultList = em.createQuery(query).getResultList();

BufferedWriter out = new BufferedWriter(new FileWriter("result.csv"));
CSVWriter writer = new CSVWriter(out);
for (Object item : resultList) {
    String line = formatParticipantToLine(item) // method to format properties of Participant with comma's
    writer.writeNext(item);
}
writer.close();

UPDATE: You can also use http://opencsv.sourceforge.net/apidocs/au/com/bytecode/opencsv/bean/BeanToCsv.html with Participant your bean of course.

K.C.
  • 2,084
  • 2
  • 25
  • 38
  • @k-c thanks for your answer. Only to understand, if I've to write a method to create a line with all properties of each item separated by commas, why I've to use opencsv? I thought that opencsv did it automatically. Isn't it? – Enrico Morelli Jan 02 '14 at 15:15
  • At the end I've to create a String[] object to use CSVWriter. – Enrico Morelli Jan 02 '14 at 15:39
  • Read the API of opencsv, you can use for example : http://opencsv.sourceforge.net/#javabean-integration – K.C. Jan 02 '14 at 17:45
  • I read about that, but I don't understand well what means. I'll check the opencsv test case to learn more about it. Thanks. – Enrico Morelli Jan 03 '14 at 10:24
  • I don't know if I understand well, but reading the test cases, seems that you can read a csv file and bind the content to entity properties. Not in the other way. – Enrico Morelli Jan 03 '14 at 12:10
  • There is not only a `CsvToBean` class, but also a `BeanToCsv` class. See link in my updated response. – K.C. Jan 03 '14 at 12:23
  • Ok, I'll check. Thanks. – Enrico Morelli Jan 03 '14 at 14:19
  • The API documentation is referred to a 2.4 version that I'm unable to find. The latest version released is the 2.3 that hasn't the BeanToCsv class. For the moment I solved mixing your code with a method that create a String[] object from the entity properties. – Enrico Morelli Jan 03 '14 at 14:32
  • Not in the .gz file indeed, but I found it in the trunk: http://sourceforge.net/p/opencsv/code/HEAD/tree/trunk/src/au/com/bytecode/opencsv/bean/ You will have to check the trunk in and build opencsv yourself. I don't know if it's bugfree ... – K.C. Jan 03 '14 at 19:32
  • Thanks again. I'll check. – Enrico Morelli Jan 13 '14 at 11:26