在数据库连接器之后,Mule ESB 3.6对象到XML不能正常工作 [英] Mule ESB 3.6 Object To XML not working correctly after Database Connector
问题描述
我是Mule ESB(3.6)的新手,正在测试一个简单的流程,该流程可从MS SQL Server Express数据库中检索记录,并使用对象到XML"转换器将结果转换为XML.
I'm new to Mule ESB (3.6) and was testing out a simple flow to retrieve records from a MS SQL Server Express DB and convert results to XML using the 'Object to XML' Transformer.
客户"表只有两行,而我的查询只是返回数据库中的所有字段.
The 'Customer' table only has 2 rows and my query is just returning all fields in the DB.
我的问题:返回的XML不是我期望的(基于链接列表而不是基于查询的XML结构).如果我将变压器替换为对象到JSON"版本,则返回的JSON仅代表查询结果(这是我期望的结果).
My problem: the XML returned is not what I expected (mapped based on a linked-list and not the XML structure based on the query). If I replace the transformer with a 'Object to JSON' version, the JSON returned represents the query results only (which is what I would expect).
我已经阅读了许多在线教程,并且没有提及要应用于Object to XML转换器的任何其他设置/值(因此假设使用默认值).
I have followed a number of online tutorials and in all there is no mention of any extra settings/values to apply to the Object to XML transformer (so assuming the defaults apply).
这是一个错误还是我需要在某个地方应用更多设置?
Is this a bug or do I need to apply more settings somewhere?
我的流程的XML
<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns:json="http://www.mulesoft.org/schema/mule/json" xmlns:data-mapper="http://www.mulesoft.org/schema/mule/ee/data-mapper" xmlns:file="http://www.mulesoft.org/schema/mule/file" xmlns:mulexml="http://www.mulesoft.org/schema/mule/xml" xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
xmlns:spring="http://www.springframework.org/schema/beans" version="EE-3.6.1"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-current.xsd
http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/ee/data-mapper http://www.mulesoft.org/schema/mule/ee/data-mapper/current/mule-data-mapper.xsd
http://www.mulesoft.org/schema/mule/xml http://www.mulesoft.org/schema/mule/xml/current/mule-xml.xsd
http://www.mulesoft.org/schema/mule/file http://www.mulesoft.org/schema/mule/file/current/mule-file.xsd
http://www.mulesoft.org/schema/mule/json http://www.mulesoft.org/schema/mule/json/current/mule-json.xsd">
<http:listener-config name="HTTP_Listener_Configuration" host="0.0.0.0" port="8081" basePath="/DBQuery" doc:name="HTTP Listener Configuration"/>
<db:generic-config name="Generic_Database_Configuration" url="jdbc:sqlserver://xxxx;databaseName=xxxx;user=xxxx;password=xxxx" doc:name="Generic Database Configuration" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"/>
<file:connector name="File" outputPattern="#[server.dateTime.format("yyyyMMdd'T'HHmmss.SSS")].xml" autoDelete="true" streaming="true" validateConnections="true" doc:name="File"/>
<flow name="dbqueryFlow">
<http:listener config-ref="HTTP_Listener_Configuration" path="/" doc:name="HTTP"/>
<db:select config-ref="Generic_Database_Configuration" doc:name="Database ">
<db:parameterized-query><![CDATA[SELECT CustomerNumber
,CustomerName
,CustomerAddressLine1
,CustomerAddressLine2
,CustomerSuburb
,CustomerStateName
,CustomerPostcode
FROM Customer]]>
</db:parameterized-query>
</db:select>
<mulexml:object-to-xml-transformer encoding="UTF-8" mimeType="text/xml" doc:name="Object to XML"/>
<file:outbound-endpoint path="C:\Mule\DBQuery\Backup" connector-ref="File" responseTimeout="10000" doc:name="File"/>
<logger level="INFO" doc:name="Logger" message="#[payload]"/>
</flow>
返回的XML
<linked-list>
<org.mule.util.CaseInsensitiveHashMap serialization="custom">
<unserializable-parents/>
<org.mule.util.CaseInsensitiveHashMap>
<default/>
<float>0.75</float>
<int>16</int>
<int>7</int>
<string>CustomerNumber</string>
<string>1 </string>
<string>CustomerSuburb</string>
<string>Mt Eliza </string>
<string>CustomerStateName</string>
<string>Victoria </string>
<string>CustomerAddressLine1</string>
<string>Street name </string>
<string>CustomerPostcode</string>
<string>3930 </string>
<string>CustomerAddressLine2</string>
<string> </string>
<string>CustomerName</string>
<string>Sarge </string>
</org.mule.util.CaseInsensitiveHashMap>
</org.mule.util.CaseInsensitiveHashMap>
<org.mule.util.CaseInsensitiveHashMap serialization="custom">
<unserializable-parents/>
<org.mule.util.CaseInsensitiveHashMap>
<default/>
<float>0.75</float>
<int>16</int>
<int>7</int>
<string>CustomerNumber</string>
<string>2 </string>
<string>CustomerSuburb</string>
<string>Balwyn </string>
<string>CustomerStateName</string>
<string>Victoria </string>
<string>CustomerAddressLine1</string>
<string>Street name </string>
<string>CustomerPostcode</string>
<string>3920 </string>
<string>CustomerAddressLine2</string>
<string> </string>
<string>CustomerName</string>
<string>Daniel </string>
</org.mule.util.CaseInsensitiveHashMap>
</org.mule.util.CaseInsensitiveHashMap>
</linked-list>
返回JSON(仅DB字段-这是我所期望的)
JSON returned (DB fields only - which is what I expected)
[{"CustomerNumber":"1 ","CustomerSuburb":"Mt Eliza ","CustomerStateName":"Victoria ","CustomerAddressLine1":"Street name ","CustomerPostcode":"3930 ","CustomerAddressLine2":" ","CustomerName":"Sarge "},{"CustomerNumber":"2 ","CustomerSuburb":"Balwyn ","CustomerStateName":"Victoria ","CustomerAddressLine1":"Street name ","CustomerPostcode":"3920 ","CustomerAddressLine2":" ","CustomerName":"Daniel "}]
还如何从输出中删除结尾的空格?
Also how do you remove the trailing white spaces from the output?
谢谢 大卫
推荐答案
成功!
经过大量的摸索(以及使用Java学习XML的速成课程)并接受了Eddu的上述文章,我才能够创建一个自定义转换器.
After a lot of mucking around (and a crash course in learning XML with Java) and taking Eddu's post above, I was able to create a custom transformer.
在我的流程中,将对象转换为XML"转换为"Java"转换. 创建了类SQLCustomerToXML(确保String的返回类型).
Changed the 'Object to XML' transformer to a 'Java' transformer in my flow. Created Class SQLCustomerToXML (ensuring return type of String).
变形金刚
Transformer
import java.io.StringWriter;
import java.util.List;
import java.util.Map;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.mule.api.transformer.TransformerException;
import org.mule.transformer.AbstractTransformer;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
public class SQLCustomerToXML extends AbstractTransformer {
@SuppressWarnings("unchecked")
@Override
protected Object doTransform(Object src, String enc)
throws TransformerException {
List<Map<String, Object>> list = (List<Map<String, Object>>) src;
Document doc;
try {
DocumentBuilderFactory docFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder docBuilder = docFactory.newDocumentBuilder();
// root elements
doc = docBuilder.newDocument();
Element rootElement = doc.createElement("Customers");
doc.appendChild(rootElement);
for (Map<String, Object> map : list) {
// New Customer Entry
logger.info("Customer:");
Element customerElement = doc.createElement("Customer");
rootElement.appendChild(customerElement);
// Each entry in Map for Customer
for (Map.Entry<String, Object> element : map.entrySet()) {
logger.info(" " + element.getKey() + ":" + element.getValue().toString());
Element e = doc.createElement(element.getKey());
e.setTextContent(element.getValue().toString());
customerElement.appendChild(e);
}
}
TransformerFactory transformerFactory = TransformerFactory.newInstance();
Transformer transformer = transformerFactory.newTransformer();
DOMSource source = new DOMSource(doc);
// Write XML to String
StringWriter writer = new StringWriter();
StreamResult result = new StreamResult(writer);
transformer.transform(source, result);
logger.info("XML : " + writer.toString());
return writer.toString();
} catch (ParserConfigurationException pce) {
pce.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
}
Java Transformer设置
Java Transformer settings
<custom-transformer returnClass="java.lang.String" encoding="UTF-8" mimeType="text/xml" class="SQLCustomerToXML" doc:name="Java"/>
XML结果(在文件和浏览器中)
<?xml version="1.0" encoding="UTF-8"?>
<Customers>
<Customer>
<CustomerNumber>1</CustomerNumber>
<CustomerSuburb>Mt Eliza</CustomerSuburb>
<CustomerStateName>Victoria</CustomerStateName>
<CustomerAddressLine1>Street name</CustomerAddressLine1>
<CustomerPostcode>3930</CustomerPostcode>
<CustomerAddressLine2 />
<CustomerName>Sarge</CustomerName>
</Customer>
<Customer>
<CustomerNumber>2</CustomerNumber>
<CustomerSuburb>Balwyn</CustomerSuburb>
<CustomerStateName>Victoria</CustomerStateName>
<CustomerAddressLine1>Street name</CustomerAddressLine1>
<CustomerPostcode>3920</CustomerPostcode>
<CustomerAddressLine2 />
<CustomerName>Daniel</CustomerName>
</Customer>
</Customers>
大卫
这篇关于在数据库连接器之后,Mule ESB 3.6对象到XML不能正常工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!