hot-topics mule and the cloud what's new in mule 3 apache tomcat tips and tricks developer tools

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

german.solis on Friday, November 18, 2011

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 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 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.

Related posts:

  1. Including Files Into Tomcat’s server.xml Using XML Entity Includes
  2. First release including the new XMPP transport
  3. Measuring the Performance of your Mule ESB Application
  4. It’s a Boy! It’s a Girl! It’s a “Mule Application”!

Filed under: Mule ESB | Social tagging: > > > >

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

  1. Marcos Contreras, on November 20th, 2011 at 10:28 am Said:

    How can you pass parameters to the referenced query?

  2. german.solis, on November 24th, 2011 at 6:28 am Said:

    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. Vitaliy, on December 6th, 2011 at 1:20 pm Said:

    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…

Leave a Comment

« «