Mule Tip: Including formatted SQL statements from external files in your application

November 18 2011

5 comments 0
motif

When developing a Mule Application the normal way to define an statement is by declaring it directly in the connector, as shown in the following snippet.

However is possible that you’ll face a situation in which you have to use large and complex queries. For that scenario the previous approach is not adequate since you’ll end up with a configuration difficult to read. So the best thing to do is to externalize these queries in one or more files.

But here’s the catch, using the property placeholder files will not completely solve the problem since you’ll want to have this large queries formatted and indexed in order to make them readable for the human eye, and when using the property files you are limited to a “one-line” property.

So, in order to solve the problem what you can do is to create a separate configuration file in which you defined a Spring Bean of the class java.lang.String.

By doing so you will be able to format the query string at your convenience. Then all you have to do is to reference that bean from the JDBC connector definition using the value-ref key

And that’s it, by applying this simple steps you can have any number of large and complex SQL statements and at the same time have them easily readable and maintainable.


We'd love to hear your opinion on this post


5 Responses to “Mule Tip: Including formatted SQL statements from external files in your application”

  1. How can you pass parameters to the referenced query?

  2. Hi!
    You could simply use a mule expression to set parameters at runtime in your query, for instance something like this:

    SELECT *
    FROM TEST_JDBC
    WHERE FIELD1 > #[payload:]

    For more detail on Mule Expressions you can go to this documentation page:
    http://www.mulesoft.org/documentation/display/MULE3USER/Expressions+Configuration+Reference

    Thanks!

  3. It is not completely true, that you are limited to just one line in the properties file with property placeholder..
    You can use the following construct:
    my.sql.query=\
    SELECT my_field \
    FORM my_table\
    WHERE blah=blah

    but in this case your query become un-testable outside of the properties file…

  4. 1. Mule Studio (1.3.1) doesn’t like this. It complains that <jdbc:query requires a value attribute. I runs fine though.

    2. You could use the XML cdata tag so that your formatting doesn't get lost:

    10″ />
    ]]>

  5. Oops.. xml was lost in my comment… just google XML CDATA. You will also need to use value as an xml tag instead of an attribute.