1

Our company code base still has some code that will create the database, tables, stored procedures and the like.Coding the SQL stored procedures line by line in Java, in string format is a real pain.

I was wondering if those out there using alternative methods are willing to give me some pointer in case I'd like to optimize the current code / process. What alternative techniques or possibly pre-existing tools could be used that are useful or popular?

The last couple of years our focus has been more on getting skilled in web development technologies, and we have not given much attention to the maintainability of our stand alone applications, that are years old.

in terms of maintainability I mean readability and how easy it is to make changes and deploy them to the client site.

  • I have a formatting tool that will quote the text and put new line command so I can copy from SQL IDE but that just eases the pain. – paparazzo Feb 23 '17 at 14:48
  • Any decent IDE is going to have features to generate all the DB structures. – Thomas Carlisle Feb 24 '17 at 1:08
5

Many databases have a "dump" feature that creates a text-based representation of the database. MySQL has mysqldump, for example.

Depending on your database, you can specify to only dump structures and procedures, and leave behind the data. Or you might be able to selectively dump some reference data. If your database has this feature, there's a pretty good chance that it is also capable of creating an entirely new database from the dump file.

So, you could create a dump file with the content you need from a database that you know is in a good state. Then you could write a Java program that runs the database's loading command and tells it to load the dump file. The Java program to do this should be pretty small, and trivial. And then you have a portable SQL file that contains your database, and is completely separate from the Java file.

  • 2
    Also, ORM libraries often have tools to generate the DB structure from the models and dump it to an SQL file. – 9000 Feb 23 '17 at 17:04
2

What's stopping you from writing all the relevant SQL to a text file, reading that in and then executing that?

As you've noticed, putting that SQL in Java is a pain, so use a tool that is better suited to holding lots of text-based data (like a file). Use the tool best designed to solve your problem. Don't force a tool to fit a problem if you don't have to.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.