Execute sql statements at application startup with OpenEJB and TomEE

Provisioning its database at application startup is common but how to do it with OpenEJB and TomEE?

OpenEJB and TomEE use commons-dbcp to create their datasources so you get pool features easily. But do you know commons-dbcp (and transitively OpenEJB and TomEE) can execute statements when the datasource is create?

Simply add initConnectionSqls and the statements as value and it should work.

Well that’s fine but putting it in the tomee.xml (or openejb.xml) is not always what we want. That’s why in OpenEJB and TomEE you can specify them in a file loadable from the classloader of the datasource (often <tomee>/lib is fine but can be the webapp if you use META-INF/resources.xml). The sql file is called import-<datasource-name>.sql.

So that’s great but it doesn’t add a new functionality…for the moment.

Now let’s have a look to what we often do in JPA unit testing: we let the provider creating the database for us. Fine…but “my datasource is created before the entitymanager” or “i use the same datasource for several entitymanagers”.

Fine! Just change a bit your import sql file renaming it import-<persistence-unit-name>.sql and it will be invoked after the entity manager creation using the non jta datasource.

Note: currently the limitation is you can’t use several lines for a statement, only single line statements are valid. If we have enough feedbacks on this feature we’ll probably enhance it to allow multiple line statements. Comments are supported (lines starting with “–” or “//”).


4 thoughts on “Execute sql statements at application startup with OpenEJB and TomEE

  1. Kay Wrobel

    Hi Romain. Do you think you can post a complete example of all the files involved? Like persistence.xml, resources.xml and where exactly the import-persistenceunit.sql should be placed inside a web app? I have an urgent need for this right now and cannot find any cohesive example and/or explanation of this using Google search for those terms.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s