Running SQL scripts through JdbcTemplate

[rhasselbaum]

Hi,

I'm working on a program to incrementally upgrade our application's database schema. Our app already uses Spring, so what I'd really like to do is use JdbcTemplate to execute DDL script fragments stored as class path resources (i.e. multiple DDL statements in a single string).

Is this feasible? What JdbcTemplate call should I be using? I tried using execute(String), but Oracle barfs with an "invalid character" error when it encounters the semi-colon used to separate statements.

Any ideas?

[wpoitras]

You may need to break up the statements by parsing for the semi-colon yourself.

You might consider using the BatchSqlUpdate object to execute all statements in one call.

[rhasselbaum]

Yes, after much Googling, I've come to the same conclusion. I can't believe no one has written a little Java API to do this. This would be a very nice enhancement to JdbcTemplate! Hmmm...

Someone on one of the Sun forums pointed out that Ant has a built-in SQL task. The source code for that might be adaptable. Haven't looked into it yet.

[rhasselbaum]

Well, if anyone's interested, I've created a simple script parser and submitted the code through JIRA (SPR-1197). It's loosely based on the SQL task in Ant, but with some changes. This one will ignore comment delimiters in string literals, for example.

Enjoy.