Devender demonstrated shelling out to SQL*Plus to run a script, figured I had to trackback with a jDBI example that doesn't have to shell out, just for alternatives (and because this is a common mechanism for me to construct test schema =)
Our script, with various comment styles (this was taken from the regression tests for jDBI, btw):
-- insert 3 lines
insert into something(id, name) values (1, 'one');
# line 2
insert into something (id, name) values (2, 'two');
// line 3
insert into something (id, name) values (3, 'three');
and our code to run the script:
public void testScriptExample() throws Exception
{
Handle h = DBI.open("jdbc:derby:testing");
assertEquals(0, h.query("select * from something").size());
h.script("src/test-etc/insert-script-with-comments.sql");
assertEquals(3, h.query("select * from something").size());
h.close();
}
Shelling out to SQL*Plus is always an option I guess =) This did remind me that I need to change jDBI to use a script locator mechanism analogous to its named statement locator, in case you want to store your scripts on a web server, in the database, in JNDI, or... wherever comes to mind. Guess 1.3.3 may be out soon!