Luca Fülbier Luca Fülbier - 3 months ago 14
Java Question

Execute .sql files using JDBC

I want to test a handwritten DAO that uses the SQLite JDBC driver. My plan was to keep the schema and data insertion in version control as .sql files and execute them before the test to get a populated database that i can use for testing.

Searching for a solution to execute a whole sql script using JDBC turned up a bunch of Stackoverflow threads saying that it is not possible and providing some parsing scripts that split the sql script into separate sql statements (SQLScriptRunner).

These posts were mostly 3+ years old, so i am wondering if there still is no "easy" way to execute sql scripts using the JDBC API.

I am asking, because SQLite provides me with the option to clone a database from an existing one, which i would prefer over using a big Script-Executer implementation (The executer would probably be bigger than all my data access code combined).

So, is there a easy out of the box approach to execute sql scripts using JDBC, or is it still only possible using some parsing script?

Answer

I guess Spring Framework ScriptUtils might do this trick for you.

Please check

http://docs.spring.io/autorepo/docs/spring/4.0.9.RELEASE/javadoc-api/org/springframework/jdbc/datasource/init/ScriptUtils.html

My plan was to keep the schema and data insertion in version control as .sql files and execute them before the test to get a populated database that i can use for testing.

For this purpose there is such library as DbUnit http://dbunit.sourceforge.net/

Which i personally find a little bit tricky to use without a propper wrapper.

Some of those wrappers:

Spring Test DbUnit https://springtestdbunit.github.io/spring-test-dbunit/

Unitils DbUnit http://www.unitils.org/tutorial-database.html