Storing and expanding SQL statements

Discussion in 'Java' started by Bryan Castillo, Apr 13, 2004.

  1. 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.
    Bryan Castillo, Apr 13, 2004
    #1
    1. Advertising

  2. Bryan Castillo

    Roedy Green Guest

    On 13 Apr 2004 15:17:23 -0700, (Bryan Castillo)
    wrote or quoted :

    >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.
    --
    Canadian Mind Products, Roedy Green.
    Coaching, problem solving, economical contract programming.
    See http://mindprod.com/jgloss/jgloss.html for The Java Glossary.
    Roedy Green, Apr 14, 2004
    #2
    1. Advertising

  3. Roedy Green <> wrote in message news:<>...
    > On 13 Apr 2004 15:17:23 -0700, (Bryan Castillo)
    > wrote or quoted :
    >
    > >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.



    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)
    Bryan Castillo, Apr 14, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Neil Zanella
    Replies:
    8
    Views:
    1,181
    mfmehdi
    Oct 20, 2006
  2. Harry George
    Replies:
    6
    Views:
    371
    Bart Nessux
    Feb 23, 2004
  3. Simone
    Replies:
    3
    Views:
    332
    Simone
    Mar 21, 2007
  4. Vince
    Replies:
    12
    Views:
    746
    Martin Gregorie
    Jan 21, 2008
  5. John Crichton
    Replies:
    6
    Views:
    259
    John Crichton
    Jul 12, 2010
Loading...

Share This Page