Spring Integration - 外部化 JDBC 查询 [英] Spring Integration - Externalizing JDBC Queries

问题描述

有没有一种简单的方法可以将来自 jdbc 出站网关的大型 sql 查询外部化，而不是内联它?原因是我们必须进行许多大查询，而且我们希望将它们放在自己的文件中，或者至少在 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.

一些注意事项:

• 我无法控制数据库，因此无法创建任何内容那里(例如存储过程)
• 我不想仅仅为此创建类，我只想稍微组织/重构它，而不是让它变得更复杂介绍许多其他步骤
• 我更喜欢创建裸 .sql 文件，但将查询放入带有 bean 的 xml 中也可以
• 我没有使用 hibernate 的选项，坚持使用 spring 集成 jdbc

考虑到我们将有许多其他出站网关，欢迎提出有关如何更好地组织的建议:)

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

例如，我不希望在int-jdbc:outbound-gateway"元素中内嵌 SQL，如下所示:

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>

简单地说:

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

它也适用于 bean 内部使用的:payload"参数.

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

推荐答案

您可以将 XML 中的查询定义为 spring 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> 

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

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.

您可以使用 SpEL 引用 bean.

You can refer to the bean using SpEL.

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