CODEWITHSUNDEEP
javaspringgroovyspring-integration
JeanK
JeanK

Reputation: 1775

Spring Integration - Externalizing JDBC Queries

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:

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.

Upvotes: 2

Views: 1767

Answers (2)

Nathan Hughes
Nathan Hughes

Reputation: 96385

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.

Upvotes: 2

Gary Russell
Gary Russell

Reputation: 174554

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 = "...";

Upvotes: 3

Related Questions