Spring Integration - Externalizing JDBC Queries

Question

Is there a simple way to externalize big sql queries from jdbc outbound gateways, instead of inlining it? The reason being that we're having to many big queries to make, and we'd like to have them on their own files, or at least externalize them in beans.

Some caveats:

I don't have control over the database, so I can't create anything there (e.g. stored procedures)
I don't want to create classes just for this matter, I just want to organize/refactor it a bit, and not make it more complex introducing many other steps
I'd prefer to create bare .sql files, but putting the queries in an xml withing a bean is okay too
I don't have the option of using hibernate, stuck to spring integration jdbc

Suggestions on how to better organize this, considering that we're going to have many others outbound gateways are welcome :)

For instance, I wouldn't like to have the SQL inline in the "int-jdbc:outbound-gateway" element as follows:

<int-jdbc:outbound-gateway
         data-source="datasource"
         request-channel="reqChannel"
         reply-channel="respChannel"
         row-mapper="datamapper" max-rows-per-poll="1000"
         query=" SELECT Field1, Field2, ManyOthers
                 FROM Table T
                 JOIN A ON A.id = T.id [... many other joins here ...]
                 WHERE SOMECONDITION=:payload">
</int-jdbc:outbound-gateway>

What I've done using the answers

Simply:

<bean id="myCoolQuery" class="java.lang.String">
    <constructor-arg>
      <value>
        <![CDATA[
                 SELECT Field1, Field2, ManyOthers
                 FROM Table T
                 JOIN A ON A.id = T.id [... many other joins here ...]
                 WHERE SOMECONDITION=:payload
        ]]>
      </value>
    </constructor-arg>
</bean> 

<int-jdbc:outbound-gateway
         data-source="datasource"
         request-channel="reqChannel"
         reply-channel="respChannel"
         row-mapper="datamapper" max-rows-per-poll="1000"
         query="#{myCoolQuery}">
</int-jdbc:outbound-gateway>

It also works with the ":payload" parameter used inside the bean.

Gary Russell · Accepted Answer · 2014-08-28 20:42:35Z

3

Yes, you can put them in a properties file, and use properties placeholders ${...} to resolve them, or you can use SpEL...

"#{myQueryBean.queryOne}"

where myQueryBean is a <bean/> that's an instance of a class with a method...

public String getQueryOne() {...}

or a static constant on a class...

"#{T(foo.Queries).QUERY_ONE}"

public static final String QUERY_ONE = "...";

answered Aug 28, 2014 at 20:42

Gary Russell

176k14 gold badges168 silver badges194 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JeanK Over a year ago

"#{myQueryBean.queryOne}" -> This part was also essential, I wasn't aware that I could access the beans like this, thanks very much

Nathan Hughes · Accepted Answer · 2014-08-28 21:00:56Z

2

You can define your queries in XML as spring beans:

<bean id="exampleQuerySql" class="java.lang.String">
    <constructor-arg>
      <value>
          <![CDATA[
select * from foo
where whatever_ind = 'A'
          ]]>
      </value>
    </constructor-arg>
</bean>

Using CDATA the query text can include newlines, angle brackets, etc., so it's legible and you can cut and paste it directly into a SQL tool.

You can refer to the bean using SpEL.

edited Aug 28, 2014 at 21:00

answered Aug 28, 2014 at 20:49

Nathan Hughes

96.7k21 gold badges193 silver badges288 bronze badges

Collectives™ on Stack Overflow

Spring Integration - Externalizing JDBC Queries

What I've done using the answers

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

What I've done using the answers

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related