Support a wider range of data sources i.e. not just `PreparedStatement`

[donalmurtagh]

Is your feature request related to a problem? Please describe.

Currently, the only way to provide the data for a worksheet is via a PreparedStatement. This is not very convenient for projects that don't use raw SQL or don't use a relational database at all.

Describe the solution you'd like In my specific case, database access is usually one of the following

• A method of a Spring Data JPA repository that defines a JPQL query
• A criteria query

As far as I know, there is no way to get a PreparedStatement from a Spring Data JPA repository method such as this one

public interface UserRepository extends CrudRepository<User, UUID> {

    @Query("""
        from    UserRole ur
        where   ur.role.name = 'SYSTEM_ADMIN'""")
    Collection<UserRole> findAllSysAdmins();
}

In this case, the query is relatively simple, so it would not be too arduous to define a PreparedStatement that executes the equivalent SQL query, but for more complex queries the effort required to translate the JPQL/criteria query into SQL could be considerable.

Although support for JPA queries would solve my problem, perhaps it would be better to consider a type that hides the details of where the data comes from, e.g.

interface WorksheetData {
  /* Provides the labels to be used for the columns in row 1 of the worksheet */
  List<String> columnHeaders()

  /* Provides the values to be shown in rows 2 onwards of the worksheet */
  List<List<Object> cellValues()
}

Then a user could provide their own implementation of this object (instead of a PreparedStatement) which might get the data from a web service, a text file, etc. but MemPOI shouldn't know or care where the data comes from.

[firegloves]

Hi Donal,

I've thought a lot about your requests. First of all, let me say that MemPOI has been designed with the single goal of automating the export from DBs to Excel files. I've chosen the PreparedStatement approach because it allows MemPOI to be agnostic of the underlying DB.

That said, currently MemPOI leverages ResultSet's metadata to infer column data types and call proper ApachePOI methods via reflection. As you can argue, this is the core of MemPOI and changing it will involve a lot of work.

Now let's talk about your requests.

I've tried in the past to build an integration with Spring data but I wasn't able to find an ingress point to obtain a PreparedStatement, a ResultSet or the plain SQL resulting from the Spring data processing. Most of the time, MemPOI faces massive exports, so I would like to keep ORMs out of MemPOI's door, due to performance issues. If you have any suggestions about this I'll be glad to delve into them.

A possible way could pass through the criteria query, if I remember correctly it's possible to extract a SQL query from there, even if with a lot of custom abstraction (but things could be changed since I looked in this field).

For what concerns JPQL, if I remember well, we should rely on external libs to extract the plain SQL. It could be done, I have to do some research around that.

For what concern the last request, the possibility of providing custom data using a List of Objects, would completely break MemPOI internals and I'm not so happy to expose methods accepting Objects. It could be a good idea, but I think it's too much, at least for the moment and for myself that I'm working alone on this project.