I have to generate PL/SQL using Java. Most of the procedures are common. Only a few keeps changing. How to do that?

Question

I have to generate PL-SQL code, with some common code(invariable) and a variable code. I don't want to use any external tools.

Some ways that I can think:

• Can I go and maintain the common code in a template and with markers, where my java code will generate code in the markers and generate a new file.

• Maintain the common code in static constant String and then generate the whole code in StringBuffer and at last write to file.

But, I am not at all satisfied with both the ideas. Can you please suggest any better ways of doing this or the use of any design patterns or anything?

Thanks in Advance.

Answer 1

The complexity of your substitution requirements are a bit vague, but if your solution must use java (which is not really the best tool for that purpose), then your suggested approaches are both reasonable. Using a template file may be preferable as it allows non-java programmers to update the PL-SQL template more easily.

One thing to consider, if you're using ant as your build tool, is to do the substitution with ant, see the filterset tag.

Answer 2

A possible approach could be to model the template SQL (preferably) in an external resource (txt file or the like) but wrap this and present it to the rest of your system as a PLSQLTemplate (for want of a better name) class. You could then use one of two approaches in designing this class:

1. Template Method pattern. Assuming you know beforehand what the "variable" portion of the query is going to be you could use the Template Method pattern, extending the PLSQLTemplate class and implementing a set of abstract "variable provider" methods to return the specifics of the query. (Kind of a JDBC templates approach).

2. Factory Pattern (sort of). Turn PLSQLTemplate into a "SQL" factory. You could publish the public API for the class such that you can call "setter" methods on the instance to set the variable portions of the code and have a factory method which returns a PreparedStatement for you that wraps your now complete SQL. A preferred approach here would be to construct the PLSQLTemplate instance with the "variables" passed in the constructor to ensure a consistent object state. That would depend on how many variable portions you need to set on the SQL statement as you probably don't want a really wide constructor signature.

Hope this helps.

Answer 3

IMO you should keep your templates in files and use a template engine to fill in the variable parts. I would also consider using JDBC to directly generate the pl/sql packages (functions, procedures resp.) directly from your program.

Answer 4

You can use template engine like FreeMarker or Velocity. Storing templates in plain Java is not a good idea.