Storing and expanding SQL statements

[Bryan Castillo]

I was looking to put all of the SQL for a web application into an XML
file for a project I am working on. It is a struts 1.1 app running on
Tomcat. I am not using EJB's nor am I using any O/R tools. Anyway, I
thought it would be nice to store all of the SQL in one place, so
DBA's and other programmers can review the SQL very easily, as it is
not embedded in any java files. It is also conceivable that the SQL
can be changed out withought chaning the code using parameters ?'s. I
could even change to stored procedures for some operations. (I've
already been storing my SQL in property files for some time now).

Anyway I had a basic SQL factory class that reads the XML and gives
back the statement bases on the name of the statement.

Here is a basic example of the xml (which will be expanded - perhaps
to include documentation strings).

<sql-statements>
<statement name="update.terminals">
UPDATE terminals
SET strategy_id = ?
WHERE merid = ?
</statement>
</sql-statements>

This is all fine and dandy given that I want to use
PreparedStatements, however I see that it would be nice to use some
type of MACRO expansion of the SQL. We have one database system where
each customer has its own table with exactly the same structure as
each other customer. Some people will of course say that it should
all be 1 table with a customer ID column added, however it was created
long ago and there are reasons to leave it the way it is for now. So
I might have an SQL statement to query or update one of the customer
tables, but I can't use a ? mark for a table name as a means of giving
a parameter to a PreparedStatement.

Here is an example of a statement where I might want to expand some
variable
customer_id as part of a table name.

<sql-statements>
<statement name="update.terminals">
UPDATE cust_txns_${customer_id}
SET status = ?
WHERE txid = ?
</statement>
</sql-statements>

I would like to use something similar to the way Ant expands variables
or perhaps something like the messages used by Struts which use ${0},
${1}, etc...

I looked through jakarta-commons and tried to see if there was a
library for expanding MACROS similar to what Ant does. There doesn't
seem to be. It should be simple enough to write my own, or pull the
source from Ant.

Question 1: Is there a standalone java library typically used for
MACRO expansion?

I was also considering using Velocity to create the SQL statements. I
think Velocity would give me a lot of flexibility, but I was a little
hesitant to introduce another language/technology many people on my
team do not know. One positive thing I could gain from using
velocity, is that I could embed conditional logic in the SQL statement
where I am building a fairly dynamic query. I have one query right
now that is built a little differently depending on what parameters
are being searched upon (it brings in some outer joins in some cases).

Anyone else out there use some strategies for pulling your SQL out of
the java code? Do you use any macro expansions on SQL?


Thanks.

 

[Roedy Green]

Question 1: Is there a standalone java library typically used for
MACRO expansion?

Perhaps the route to go is to write your macros in Java, and invoke
them with your own custom sqlxml: tag

I designed a scheme for writing HTML macros in Java. It find it
orders of magnitude easier to write than any macro language I have
used previously. My macros do things I would never have dreamed of
pulling off in other macro languages.

1. I have the full power of Java available.

2. I know Java already.

3. Java is a full featured language. You don't have to kludge to get
around some oversight missing language feature.

4. Java is readable.

5. You can use OO, methods etc. to avoid the rat's nest that macros
usually turn into.

6. Java is relatively bug free.

Another thought. Provide an example template, code that would
actually run. Choose strange names in it that are unique for the
replaceable bits. Then provide a list of name=value for strictly text
replacement. Don't fart around with unreadable syntax like $1 &x2.
etc. You can never proofread the template.

 

[Bryan Castillo]

Perhaps the route to go is to write your macros in Java, and invoke
them with your own custom sqlxml: tag

I designed a scheme for writing HTML macros in Java. It find it
orders of magnitude easier to write than any macro language I have
used previously. My macros do things I would never have dreamed of
pulling off in other macro languages.

1. I have the full power of Java available.

2. I know Java already.

3. Java is a full featured language. You don't have to kludge to get
around some oversight missing language feature.

4. Java is readable.

5. You can use OO, methods etc. to avoid the rat's nest that macros
usually turn into.

6. Java is relatively bug free.

Another thought. Provide an example template, code that would
actually run. Choose strange names in it that are unique for the
replaceable bits. Then provide a list of name=value for strictly text
replacement. Don't fart around with unreadable syntax like $1 &x2.
etc. You can never proofread the template.

I tried implementing macro expansion in 2 ways.
1 uses Velocity to expand variables while the other uses Beanshell.

Here is my xml file containing sql (some sql statements are really macros)
(These statements are just simple examples)

<?xml version="1.0"?>
<sql-statements>

<sql name="terminal.updateset" type="prepared"><![CDATA[
UPDATE terminal
SET termid = ?
WHERE merid = ?
]]></sql>

<sql name="veltest" type="velocity"><![CDATA[
UPDATE ${table}
SET termid = ?
WHERE merid = ?
]]></sql>

<sql name="veltest2" type="velocity"><![CDATA[
SELECT * FROM merchant
WHERE ${column} IN (
#foreach($value in $values)
#if ($velocityCount > 1)
,$value
#else
$value
#end
#end
)
]]></sql>

<sql name="bshtest" type="beanshell"><![CDATA[
return "UPDATE " + table + " " +
"SET termid = ? " +
"WHERE merid = ? ";
]]></sql>

<sql name="bshtest2" type="beanshell"><![CDATA[
String sql = "SELECT * FROM merchant " +
"WHERE " + column + " IN ( ";
for (int i=0; i<values.size(); i++) {
if (i>0)
sql += ",";
sql += values.get(i);
}
sql += ")";
return sql;
]]></sql>

</sql-statements>


I expand the statements like this:

SQLRepository repository = SQLRepository.getInstance();

System.out.println(">>veltest");
SQLStatement st = repository.getSql("veltest");
HashMap map = new HashMap();
map.put("table", "bryanc");
String stmt = VelocityUtil.expandSQL(st.getSql(), map);
System.out.println(stmt);


System.out.println(">>veltest2");
st = repository.getSql("veltest2");
map = new HashMap();
map.put("column", "merid");
ArrayList values = new ArrayList();
values.add(new Integer(1));
values.add(new Integer(2));
values.add(new Integer(3));
map.put("values", values);
stmt = VelocityUtil.expandSQL(st.getSql(), map);
System.out.println(stmt);

System.out.println(">>bshtest");
st = repository.getSql("bshtest");
map = new HashMap();
map.put("table", "bryanc");
stmt = BshUtil.expandSQL(st.getSql(), map);
System.out.println(stmt);

System.out.println(">>bshtest2");
st = repository.getSql("bshtest2");
map = new HashMap();
map.put("column", "merid");
values = new ArrayList();
values.add(new Integer(1));
values.add(new Integer(2));
values.add(new Integer(3));
map.put("values", values);
stmt = BshUtil.expandSQL(st.getSql(), map);
System.out.println(stmt);


And here is the output


[java] >>veltest


[java] UPDATE bryanc
[java] SET termid = ?
[java] WHERE merid = ?

[java]
[java] >>veltest2


[java] SELECT * FROM merchant
[java] WHERE merid IN (
[java] 1
[java] ,2
[java] ,3
[java] )

[java]
[java] >>bshtest
[java] UPDATE bryanc SET termid = ? WHERE merid = ?
[java] >>bshtest2
[java] SELECT * FROM merchant WHERE merid IN ( 1,2,3)


Does anyone have a preference for one style over the other?
(Perhaps I'll just keep both)