Brian's Waste of Time

Fri, 18 Nov 2005

SQL Scripts in Java

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 ="jdbc:derby:testing");
        assertEquals(0, h.query("select * from something").size());
        assertEquals(3, h.query("select * from something").size());

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!

1 writebacks [/src/java/jdbi] permanent link