package com.aspose.words.examples.mail_merge;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import com.aspose.words.Document;
import com.aspose.words.examples.Utils;
import com.aspose.words.net.System.Data.DataRelation;
import com.aspose.words.net.System.Data.DataSet;
import com.aspose.words.net.System.Data.DataTable;
public class NestedMailMergeRegions {
private static final String dataDir = Utils.getSharedDataDir(NestedMailMergeRegions.class) + "MailMerge/";
private static Connection mConnection;
public static void main(String[] args) throws Exception {
// Create the dataset which will hold each DataTable used for mail merge.
DataSet pizzaDs = new DataSet();
// Create a connection to the database
createConnection(dataDir);
// Populate each DataTable from the database. Each query which returns a ResultSet object containing the data from the table.
// This ResultSet is wrapped into an Aspose.Words implementation of the DataTable class and added to a DataSet.
DataTable orders = new DataTable(executeQuery("SELECT * from Orders"), "Orders");
pizzaDs.getTables().add(orders);
DataTable itemDetails = new DataTable(executeQuery("SELECT * from Items"), "Items");
pizzaDs.getTables().add(itemDetails);
// In order for nested mail merge to work, the mail merge engine must know the relation between parent and child tables.
// Add a DataRelation to specify relations between these tables.
pizzaDs.getRelations().add(new DataRelation("OrderToItemDetails", orders, itemDetails, new String[] { "OrderID" }, new String[] { "OrderID" }));
// Open the template document.
Document doc = new Document(dataDir + "Invoice Template.doc");
// Trim trailing and leading whitespaces mail merge values
//doc.getMailMerge().setTrimWhitespaces(false);
// Execute nested mail merge with regions
doc.getMailMerge().executeWithRegions(pizzaDs);
// Save the output to disk
doc.save(dataDir + "Invoice Out.doc");
assert doc.getMailMerge().getFieldNames().length == 0 : "There was a problem with mail merge";
}
/**
* Executes a query to the demo database using a new statement and returns
* the result in a ResultSet.
*/
protected static ResultSet executeQuery(String query) throws Exception {
return createStatement().executeQuery(query);
}
/**
* Utility function that creates a connection to the Database.
*/
public static void createConnection(String dataDir) throws Exception {
Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
String connectionString = "jdbc:ucanaccess://" + dataDir + "InvoiceDB.mdb";
// Create a connection to the database.
mConnection = DriverManager.getConnection(connectionString);
}
/**
* Utility function that creates a statement to the database.
*/
public static Statement createStatement() throws Exception {
return mConnection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
}
}