1

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?

Improve this question
0

2 Answers 2

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I saw DbUnit being mentioned a lot on the other articles, I might use it if i have more database code that has to be tested in the future. The Spring one looks really nice, but Spring is a really big dependency just for a ScriptLoader. I might refactor my application to use a bigger framework in the future - if it is Spring, i will make use of that!
Spring Framework is highly moduled project. This class is located inside of spring-jdbc module, which also contains a lot of useful classes for jdbc code . JdbcTemplate and NamedParameterJdbcTemplate are just wonderful classes, those you should use in your DAO classes for sure. They handle closing/opening connections, provide a lot of useful methods, such as queryForObject,queryForList and generally make writing jdbc code much more pleasant. You should really consider using it!
I didn't know it was split up that well, thank you for mentioning that!
0

While not an "out of the box" JDBC solution, I would be inclined to use SqlTool. It is especially friendly with HSQLDB, but it can be used with (almost?) any JDBC driver. (The documentation shows examples on how to connect with PostgreSQL, SQL Server, Oracle, Derby, etc.) It's available via Maven, and the JAR file is only ~150KB. For an example of how to run an SQL script with it, see my other answer here.

Regarding the proposed Spring "ScriptUtils" solution in light of the comment in your question ...

i would prefer [some other solution] over using a big Script-Executer implementation (The executer would probably be bigger than all my data access code combined).

... note that the dependencies for spring-jdbc include spring-core, spring-beans, spring-tx, and commons-logging-1.2 for a total of ~2.5MB. That is over 15 times larger than the SqlTool JAR file, and it doesn't even include any additional DbUnit "wrappers" that may or may not be required to make ScriptUtils easier to use.

Improve this answer

1 Comment

All solutions (including SqlTools) were not what i was searching for. DbUnit in combination with spring-jdbc would probably solve all my problems, but it is quite heavy weight only to run some unit tests on a sqlite database. I chose a workaround using a prototype database that i can quickly restore to its original state between tests. Sqlite also provides a in-memory mode, which makes this a really good choice. My whole database test suite (testing every DAO i have) runs in under 5 seconds and that with a database reset between every test.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.