简介
- 适用对象:
- Experience Manager 6.4
- Experience Manager 6.5
- 主题:
- 自适应表单
创建对象:
- 有经验的
- 开发人员
客户通常希望以CSV格式导出提交的表单数据。 本文将重点介绍以CSV格式导出表单数据所需的步骤。 本文假定提交的表单存储在RDBMS表格中。 以下屏幕截图详细说明了存储提交的表单所需的最低表结构。
如您所见,架构的名称为aemformstutorial。在此架构内是表单提交表,其中定义了以下列
- formdata:此列保存提交的formdata
- formname:此列保存提交的表单的名称
- id:这是主键,设置为自动递增
表名和两列名称作为OSGi配置属性显示,如下面的屏幕快照所示:
该代码读取这些值,并构造要执行的相应SQL查询。 例如,根据上述值执行以下查询
SELECT formdata FROM aemformstutorial.formsubmissions where formname=timeoffrequestform
在上述查询中,表单(timeoffrequestform)的名称作为请求参数传递给servlet。
创建OSGi服务
创建了以下OSGI服务,以将提交的数据导出为CSV格式。
-
第37行:我们正在访问Apache Sling Connection Pooled DataSource。
-
第89行:这是服务的入口点。方法
getCSVFile(..)
采用formName作为输入参数,并获取与给定表单名称相关的已提交数据。
package com.aemforms.storeandexport.core;
import java.io.IOException;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
@Component(service = StoreAndExport.class)
public class StoreAndExportImpl implements StoreAndExport {
private final Logger log = LoggerFactory.getLogger(getClass());
@Reference
StoreAndExportConfigurationService config;
@Reference(target = "(&(objectclass=javax.sql.DataSource)(datasource.name=aemformstutorial))")
private DataSource dataSource;
private List<String> getRowValues(String row) {
List<String> rowValues = new ArrayList<String>();
//API to obtain DOM Document instance
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = null;
try {
builder = factory.newDocumentBuilder();
Document doc = builder.parse(new InputSource(new StringReader(row)));
XPathFactory xpf = XPathFactory.newInstance();
XPath xpath = xpf.newXPath();
Node dataNode = (Node) xpath.evaluate("//afData/afUnboundData/data", doc, XPathConstants.NODE);
NodeList dataElements = dataNode.getChildNodes();
for (int i = 0; i < dataElements.getLength(); i++) {
log.debug("The name of the node is" + dataElements.item(i).getNodeName() + " the node value is " + dataElements.item(i).getTextContent());
rowValues.add(i, dataElements.item(i).getTextContent());
}
return rowValues;
} catch (Exception e) {
log.debug(e.getMessage());
}
return null;
}
private List<String> getHeaderValues(String row) {
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
List<String> rowValues = new ArrayList<String>();
DocumentBuilder builder = null;
try {
//Create DocumentBuilder with default configuration
builder = factory.newDocumentBuilder();
Document doc = builder.parse(new InputSource(new StringReader(row)));
XPathFactory xpf = XPathFactory.newInstance();
XPath xpath = xpf.newXPath();
Node dataNode = (Node) xpath.evaluate("//afData/afUnboundData/data", doc, XPathConstants.NODE);
NodeList dataElements = dataNode.getChildNodes();
for (int i = 0; i < dataElements.getLength(); i++) {
rowValues.add(i, dataElements.item(i).getNodeName());
}
return rowValues;
} catch (Exception e) {
log.debug(e.getMessage());
}
return null;
}
@Override
public StringBuilder getCSVFile(String formName) {
log.debug("In get CSV File");
String selectStatement = "SELECT " + config.getFORM_DATA_COLUMN() + " FROM aemformstutorial." + config.getTABLE_NAME() + " where " + config.getFORM_NAME_COLUMN() + "='" + formName + "'" + "";
log.debug("The select statment is " + selectStatement);
Connection con = getConnection();
Statement st = null;
ResultSet rs = null;
CSVUtils csvUtils = new CSVUtils();
try {
st = con.createStatement();
rs = st.executeQuery(selectStatement);
log.debug("Got Result Set in getCSVFile");
StringBuilder sb = new StringBuilder();
while (rs.next()) {
if (rs.isFirst()) {
sb = csvUtils.writeLine(getHeaderValues(rs.getString(1)), sb);
}
sb = csvUtils.writeLine(getRowValues(rs.getString(1)), sb);
log.debug("$$$$The current strng buffer is " + sb.toString());
}
return sb;
} catch (Exception e) {
log.debug(e.getMessage());
} finally {
try {
rs.close();
} catch (Exception e) { /* ignored */ }
try {
st.close();
} catch (Exception e) { /* ignored */ }
try {
con.close();
} catch (Exception e) { /* ignored */ }
}
return null;
}
private Connection getConnection() {
log.debug("Getting Connection ");
Connection con = null;
try {
con = dataSource.getConnection();
log.debug("got connection");
return con;
} catch (Exception e) {
log.debug("not able to get connection ");
log.debug(e.getMessage());
}
return null;
}
@Override
public void inserFormData(String formData) {
String formDataColumn = config.getFORM_DATA_COLUMN();
String formNameColumn = config.getFORM_NAME_COLUMN();
String tableName = config.getTABLE_NAME();
String insertStatement = "Insert into aemformstutorial." + tableName + "(" + formDataColumn + "," + formNameColumn + ") VALUES(?,?)";
log.debug("The insert statment is" + insertStatement);
Connection con = getConnection();
PreparedStatement pstmt = null;
try {
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = null;
builder = factory.newDocumentBuilder();
Document xmlDoc = builder.parse(new InputSource(new StringReader(formData)));
XPath xPath = javax.xml.xpath.XPathFactory.newInstance().newXPath();
org.w3c.dom.Node submittedFormNameNode = (org.w3c.dom.Node) xPath.compile("/afData/afSubmissionInfo/afPath").evaluate(xmlDoc, javax.xml.xpath.XPathConstants.NODE);
String paths[] = submittedFormNameNode.getTextContent().split("/");
String formName = paths[paths.length - 1];
log.debug("The form name submiited is" + formName);
pstmt = null;
pstmt = con.prepareStatement(insertStatement);
pstmt.setString(1, formData);
pstmt.setString(2, formName);
log.debug("Executing the insert statment " + pstmt.execute());
con.commit();
} catch (SQLException e) {
log.debug(e.getMessage());
} catch (ParserConfigurationException e) {
log.debug(e.getMessage());
} catch (SAXException e) {
log.debug(e.getMessage());
} catch (IOException e) {
log.debug(e.getMessage());
} catch (XPathExpressionException e) {
log.debug(e.getMessage());
} finally {
try {
pstmt.close();
} catch (Exception e) { /* ignored */ }
try {
con.close();
} catch (Exception e) { /* ignored */ }
}
}
}
配置服务
我们已将以下三个属性作为OSGI配置属性公开。 SQL查询是通过在运行时读取这些值来构建的。
package com.aemforms.storeandexport.core;
import org.osgi.service.metatype.annotations.AttributeDefinition;
import org.osgi.service.metatype.annotations.ObjectClassDefinition;
@ObjectClassDefinition(name="Store and Export Configuration", description = "Details on the Database ")
public @interface StoreAndExportConfiguration {
@AttributeDefinition(name = "Table Name", description = "Name of the table to store the submitted data")
String tableName() default "formsubmissions";
@AttributeDefinition(name = "Form Data Column Name", description = "Column name to hold submitted form data")
String formDataColumn() default "formdata";
@AttributeDefinition(name = "Form Name Column Name", description = "Column name to hold submitted form name")
String formNameColumn() default "formname";
}
Servlet
以下是调用服务的getCSVFile(..)
方法的servlet代码。 该服务返回StringBuffer对象,该对象随后被流式传输回调用应用程序
package com.aemforms.storeandexport.core.servlets;
import java.io.IOException;
import javax.servlet.Servlet;
import javax.servlet.ServletOutputStream;
import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import com.aemforms.storeandexport.core.StoreAndExport;
@Component(
service = {Servlet.class},
property = {"sling.servlet.methods=get", "sling.servlet.paths=/bin/streamformdata"}
)
public class StreamCSVFile extends SlingAllMethodsServlet {
private static final long serialVersionUID = -3703364266795135086L;
@Reference
StoreAndExport createCSVFile;
protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response) {
StringBuilder stringToStream = createCSVFile.getCSVFile(request.getParameter("formName"));
response.setHeader("Content-Type", "text/csv");
response.setHeader("Content-Disposition", "attachment;filename=\"formdata.csv\"");
try {
final ServletOutputStream sout = response.getOutputStream();
sout.print(stringToStream.toString());
} catch (IOException e) {
log.debug(e.getMessage());
}
}
}
在您的服务器上部署
- 使用MySQL Workbench将SQL文件导入MySQL服务器。 这将创建名为 aemformstutorial 的架构和名为 formsubmissions 的表,其中包含一些示例数据。
- 使用Felix Web控制台部署OSGi包
- 获取TimeOffRequest提交。 您应会将CSV文件流式传输回给您。