MySQL view to CSV file using Java, Spring, and Hibernate

Question

I've been asked to output a CSV file from a view in MySQL. The app I currently am writing uses Spring and Hibernate to create the database, but the view is just handed to me.

Hibernate doesn't know anything about this view, but I'd want to do something like this:

public List<Object> getCsvView() {
  return (List<Object>) getHibernateTemplate().find("from myView");
}

My guess was that I could map a native query so that hibernate knows about the view. This got a little tricky when I read the docs:

You can also map a native query[...]To achieve that, you need to describe the SQL resultset structure using @SqlResultSetMapping[...].

Now, I'm really not interested in mapping the structure of the result. I'm happy to have the structure just be a bunch of objects.

Furthermore they might change this view at any time. I'm really not thrilled about my app even knowing about the view.

So, is there an easy way to do this in the Spring/Hibernate world, or am I attacking this problem the hard way?

Answer 1

Just perform a native query on the view and you'll get a List of Object[] with scalar values for each column as result. From the documentation:

16.1.1. Scalar queries

The most basic SQL query is to get a list of scalars (values).

sess.createSQLQuery("SELECT * FROM CATS").list();
sess.createSQLQuery("SELECT ID, NAME, BIRTHDATE FROM CATS").list();

These will return a List of Object arrays (Object[]) with scalar values for each column in the CATS table. Hibernate will use ResultSetMetadata to deduce the actual order and types of the returned scalar values.

To avoid the overhead of using ResultSetMetadata, or simply to be more explicit in what is returned, one can use addScalar():

sess.createSQLQuery("SELECT * FROM CATS")
 .addScalar("ID", Hibernate.LONG)
 .addScalar("NAME", Hibernate.STRING)
 .addScalar("BIRTHDATE", Hibernate.DATE)

This query specified:

• the SQL query string
• the columns and types to return

This will return Object arrays, but now it will not use ResultSetMetadata but will instead explicitly get the ID, NAME and BIRTHDATE column as respectively a Long, String and a Short from the underlying resultset. This also means that only these three columns will be returned, even though the query is using * and could return more than the three listed columns.

It is possible to leave out the type information for all or some of the scalars.

sess.createSQLQuery("SELECT * FROM CATS")
 .addScalar("ID", Hibernate.LONG)
 .addScalar("NAME")
 .addScalar("BIRTHDATE")

This is essentially the same query as before, but now ResultSetMetaData is used to determine the type of NAME and BIRTHDATE, where as the type of ID is explicitly specified.

How the java.sql.Types returned from ResultSetMetaData is mapped to Hibernate types is controlled by the Dialect. If a specific type is not mapped, or does not result in the expected type, it is possible to customize it via calls to registerHibernateType in the Dialect.

Looks perfect to generate a CVS file without knowing anything about the view :)