Sending a list of SQL Statements to the Server (out of the java program)
Joined: Jan 20, 2008
I just wrote a small tool which dynamicly produces a list of SQL statements with respect to a few often changing attributes:
USE DatabaseName GO
IF EXISTS(select name from master.dbo.sysdatabases WHERE name = 'Database') DROP DATABASE Database;
IF NOT EXISTS ( SELECT 1 FROM information_schema.columns WHERE column_name = 'Total' AND table_name = 'Table' ) BEGIN ALTER TABLE tArticles_Welle3 ADD Total VARCHAR(6) NULL DEFAULT 'Total' WITH VALUES; END
and so on...
the whole query will then be stored in a .sql-File.
But instead of storing it in the sql File and then executing it manually in Microsoft SQL Server, I would like to send the statements to the server (they are stored in one String).
Let me see if I can sum up what you're asking... you'd like to load a script file in JDBC. The short answer is you can do so with plugin tools like ant.
The longer answer is, doing so isn't really JDBC. The idea behind JDBC is you use java to construct/manage your SQL code, and in larger environments your entire database connection. In other words, why not skip the generating of SQL statements and execute the code directly? For example, JDBC can handle batch inserts intelligently with some massaging. You can create a single prepared statement from a single connection, and use it to perform multiple updates either as a single call or as a set of batched calls. Yes it's more work to use JDBC this way but what you get is code that is a lot more maintainable and will work out of the box for most databases right out of the box, instead of just one (unless your SQL queries are particularly database dependent).
Oh, and keep in mind most JDBC code (most I write anyway) does not issue ALTER TABLE commands or modify the database in anyway except as part of an installation/upgrade tool. I can think of no good reason code should be modifying the database structure based on regular user tasks.