Spring Integration - 外部化JDBC查询

问题描述

是否有一种简单的方法将来自jdbc出站网关的大型sql查询外化，而不是内联它？原因是我们需要做很多大型的查询，并且我们希望将它们放在自己的文件中，或者至少将它们在bean中进行外部化。

一些注意事项：

• 我无法控制数据库，所以我无法在其中创建任何
  （例如存储过程）


• 我不想为这个事情创建类，我只想组织/重构它，而不是使它更复杂
  引入多个其他步骤


• 我宁愿创建纯粹的.sql文件，但将查询放入xml中并使用bean也是可以的。


• 没有选择使用hibernate，坚持Spring集成jdbc

关于如何更好地组织这个的建议，考虑到我们例如，我不希望在int-jdbc：outbound-gateway中内联SQL，元素如下：

 < int-jdbc：outbound-gateway 
 data-source =datasource
 request-channel =reqChannel
回复通道=respChannel
 row-mapper =datamappermax-rows-per-poll =1000
 query =SELECT Field1，Field2，ManyOthers 
 FROM Table T 
加入A.id = T.id [...许多其他连接...] 
 WHERE SOMECONDITION =：payload> 
< / int-jdbc：outbound-gateway> 
  

简单地说：

 < bean已经完成了使用答案





id =myCoolQueryclass =java.lang.String>
< constructor-arg>
<值>
SELECT Field1，Field2，ManyOthers
FROM Table T
加入A.id = T.id [...许多其他连接。 ..]
WHERE SOMECONDITION =：有效载荷
]]>
< /值>
< / constructor-arg>
< / bean>

data-source =datasource
request-channel =reqChannel
reply-channel =respChannel
row-mapper =datamappermax-rows-per-poll =1000
query =＃{myCoolQuery}>
< / int-jdbc：outbound-gateway>


它也适用于bean内使用的：payload参数。

解决方案

您可以在XML中将您的查询定义为spring bean：

 < bean id =exampleQuerySqlclass =java.lang.String> 
< constructor-arg> 
<值> 
<！[CDATA [
 select * from foo 
 where whatever_ind ='A'
]]> 
< /值> 
< / constructor-arg> 
< / bean> 
  

使用CDATA查询文本可以包含换行符，尖括号等，因此它很清晰，您可以将它直接剪切并粘贴到SQL工具中。

您可以使用SpEL引用该bean。

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 anythingthere (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 complexintroducing 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.

解决方案

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.

这篇关于Spring Integration - 外部化JDBC查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！