Hibernate: Use Excel as Data Source

It is very convenient for testing purposes (and not only) to keep a simple database in Excel format.

In order to do this, every sheet in our excel file must represent one table.

The first row must hold always the column names.

Then we can create an ODBC connection (in our example it has name “xxbank”),

using a Microsoft Excel Driver, which points to our excel file.

In hibernate, we need to put these parameters:

jdbc.driverClassName=sun.jdbc.odbc.JdbcOdbcDriver
jdbc.url=jdbc:odbc:xxbank
jdbc.username=
jdbc.password=

In hibernate configuration, for each table, we must take care of the following:

<hibernate-mapping>
    <class name="com.bank.ext.datamodel.Customer" table="[customers$]"><!--  catalog="xxbank" -->

which means that the catalog attribute must be removed and the table must be writen using the [xxxxx$] convention.

You can download a sample excel here

Leave a Reply