{"id":33325,"date":"2014-12-01T01:00:12","date_gmt":"2014-11-30T23:00:12","guid":{"rendered":"http:\/\/www.javacodegeeks.com\/?p=33325"},"modified":"2014-11-30T11:04:33","modified_gmt":"2014-11-30T09:04:33","slug":"developing-a-data-export-utility-with-primefaces","status":"publish","type":"post","link":"https:\/\/www.javacodegeeks.com\/2014\/12\/developing-a-data-export-utility-with-primefaces.html","title":{"rendered":"Developing a Data Export Utility with PrimeFaces"},"content":{"rendered":"

My day job involves heavy use of data.\u00a0We use relational databases to store everything, because we rely on enterprise level data management.\u00a0Sometimes it is useful to have the ability to extract the data into a simple format, such as a spreadsheet, so that we can manipulate it as-needed.\u00a0This post outlines the steps that I’ve taken to produce a effective and easy-to-use JSF-based data export utility using PrimeFaces 5.0.\u00a0The export utility produces a spreadsheet, including column headers. The user has the ability to select which database fields to export, and in which order they should be exported.<\/p>\n

We want to ensure that we have a clean user interface that is intuitive.\u00a0For that reason, I chose not to display any data on the screen.\u00a0Rather, the user interface contains a PrimeFaces PickList component that lists the different data fields to choose from, along with a button to produce the export.\u00a0Let’s begin by setting up the database infrastructure to make this export utility possible.<\/p>\n

For this post, I’ve enhanced the AcmePools application, which was developed via my article that was posted on OTN entitled PrimeFaces in the Enterprise<\/a>.\u00a0The export utility allows one to export customer data into a spreadsheet. \u00a0The customer data is included in the sample database which is installed within Apache Derby by NetBeans, or you can use the SQL script for this post. To follow along with the creation of this export utility, please download or create the AcmePools<\/a> project within your environment.<\/p>\n

There are two parts to the data export utility, the first part being a PrimeFaces PickList component for the user to select which fields to export, and the second being an export button which will extract the selected field contents into a spreadsheet.\u00a0The end result will resemble a user interface that looks like Figure 1.<\/p>\n

\"Figure<\/a>
Figure 1: Data Export Utility<\/figcaption><\/figure><\/p>\n

 <\/p>\n

Developing the PickList Component<\/h2>\n

To begin, create the data infrastructure to support the PickList component.\u00a0This consists of a single database table to hold column names and labels for the entity data you wish to export, and optionally a database sequence to populate the primary key for that table.\u00a0In this case, the database table is named COLUMN_MODEL, and we populate the table with the entity field names that correspond to the database column names for the CUSTOMER database table.<\/p>\n

-- Add support for data export\r\ncreate table column_model(\r\nid                  int primary key,\r\ncolumn_name         varchar(30),\r\ncolumn_label        varchar(150));\r\n-- Optional sequence for primary key generation\r\ncreate sequence column_model_s\r\nstart with 1\r\nincrement by 1;\r\n-- Load with field (database column) names\r\ninsert into column_model values(\r\n1,\r\n'addressline1',\r\n'Address Line 1');\r\n\r\ninsert into column_model values(\r\n2,\r\n'addressline2',\r\n'Address Line 2');\r\n\r\ninsert into column_model values(\r\n3,\r\n'city',\r\n'City');\r\n\r\ninsert into column_model values(\r\n4,\r\n'creditLimit',\r\n'Credit Limit');\r\n\r\ninsert into column_model values(\r\n5,\r\n'customerId',\r\n'Customer Id');\r\n\r\ninsert into column_model values(\r\n6,\r\n'discountCode',\r\n'Discount Code');\r\n\r\ninsert into column_model values(\r\n7,\r\n'email',\r\n'Email');\r\n\r\ninsert into column_model values(\r\n8,\r\n'fax',\r\n'Fax');\r\n\r\ninsert into column_model values(\r\n9,\r\n'name',\r\n'Name');\r\n\r\ninsert into column_model values(\r\n10,\r\n'phone',\r\n'Phone');\r\n\r\ninsert into column_model values(\r\n11,\r\n'state',\r\n'State');\r\n\r\ninsert into column_model values(\r\n12,\r\n'zip',\r\n'Zip');<\/pre>\n
Next, create an entity class that can be used for accessing the column data from within the component.\u00a0If you use an IDE such as NetBeans, this can be done very easily via a wizard.\u00a0If using NetBeans, right click on the com.acme.acmepools.entity package, and select “New”-> “Entity Classes from Database”, and then choose the data source for our sample database.\u00a0When the list of tables populates, select the COLUMN_MODEL table, as shown in Figure 2.\u00a0Lastly, choose “Next” and “Finish” to create the entity class.<\/p>\n
\"Figure<\/a>
Figure 2. NetBeans IDE New Entity Classes from Database<\/figcaption><\/figure><\/p>\n
 
\nOnce completed, the entity class entitled ColumnModel should look as follows:<\/p>\n
package com.acme.acmepools.entity;\r\n\r\nimport java.io.Serializable;\r\nimport java.math.BigDecimal;\r\nimport javax.persistence.Basic;\r\nimport javax.persistence.Column;\r\nimport javax.persistence.Entity;\r\nimport javax.persistence.Id;\r\nimport javax.persistence.NamedQueries;\r\nimport javax.persistence.NamedQuery;\r\nimport javax.persistence.Table;\r\nimport javax.validation.constraints.NotNull;\r\nimport javax.validation.constraints.Size;\r\nimport javax.xml.bind.annotation.XmlRootElement;\r\n\r\n\/**\r\n *\r\n * @author Juneau\r\n *\/\r\n@Entity\r\n@Table(name = \"COLUMN_MODEL\")\r\n@XmlRootElement\r\n@NamedQueries({\r\n    @NamedQuery(name = \"ColumnModel.findAll\", query = \"SELECT c FROM ColumnModel c\"),\r\n    @NamedQuery(name = \"ColumnModel.findById\", query = \"SELECT c FROM ColumnModel c WHERE c.id = :id\"),\r\n    @NamedQuery(name = \"ColumnModel.findByColumnName\", query = \"SELECT c FROM ColumnModel c WHERE c.columnName = :columnName\"),\r\n    @NamedQuery(name = \"ColumnModel.findByColumnLabel\", query = \"SELECT c FROM ColumnModel c WHERE c.columnLabel = :columnLabel\")})\r\npublic class ColumnModel implements Serializable {\r\n    private static final long serialVersionUID = 1L;\r\n    @Id\r\n    @Basic(optional = false)\r\n    @NotNull\r\n    @Column(name = \"ID\")\r\n    private BigDecimal id;\r\n    @Size(max = 30)\r\n    @Column(name = \"COLUMN_NAME\")\r\n    private String columnName;\r\n    @Size(max = 150)\r\n    @Column(name = \"COLUMN_LABEL\")\r\n    private String columnLabel;\r\n\r\n    public ColumnModel() {\r\n    }\r\n\r\n    public ColumnModel(BigDecimal id) {\r\n        this.id = id;\r\n    }\r\n\r\n    public BigDecimal getId() {\r\n        return id;\r\n    }\r\n\r\n    public void setId(BigDecimal id) {\r\n        this.id = id;\r\n    }\r\n\r\n    public String getColumnName() {\r\n        return columnName;\r\n    }\r\n\r\n    public void setColumnName(String columnName) {\r\n        this.columnName = columnName;\r\n    }\r\n\r\n    public String getColumnLabel() {\r\n        return columnLabel;\r\n    }\r\n\r\n    public void setColumnLabel(String columnLabel) {\r\n        this.columnLabel = columnLabel;\r\n    }\r\n\r\n    @Override\r\n    public int hashCode() {\r\n        int hash = 0;\r\n        hash += (id != null ? id.hashCode() : 0);\r\n        return hash;\r\n    }\r\n\r\n    @Override\r\n    public boolean equals(Object object) {\r\n        \/\/ TODO: Warning - this method won't work in the case the id fields are not set\r\n        if (!(object instanceof ColumnModel)) {\r\n            return false;\r\n        }\r\n        ColumnModel other = (ColumnModel) object;\r\n        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {\r\n            return false;\r\n        }\r\n        return true;\r\n    }\r\n\r\n    @Override\r\n    public String toString() {\r\n        return \"com.acme.acmepools.entity.ColumnModel[ id=\" + id + \" ]\";\r\n    }\r\n    \r\n}<\/pre>\n
Next, create an EJB session bean for the newly generated entity class so that the component can query the column data. \u00a0You can use your IDE for this as well if you’d like. \u00a0If using NetBeans, right-click on the com.acme.acmepools.session package, and select “New”->”Session Beans for Entity Classes”. \u00a0Once the dialog opens, select the entity class “com.acme.acmepools.entity.ColumnModel” from the left-hand list, and click “Finish” (Figure 3).
 
<\/div> <\/div><\/p>\n
\"Figure<\/a>
Figure 3: NetBeans IDE Session Beans for Entity Classes Dialog<\/figcaption><\/figure><\/p>\n
 
\nAfter the session bean has been created, add a method named findId(), which can be used for returning the column id value based upon a specified column name. \u00a0The full sources for the ColumnModelFacade should look as follows:<\/p>\n
package com.acme.acmepools.session;\r\n\r\nimport com.acme.acmepools.entity.ColumnModel;\r\nimport javax.ejb.Stateless;\r\nimport javax.persistence.EntityManager;\r\nimport javax.persistence.PersistenceContext;\r\n\r\n\/**\r\n *\r\n * @author Juneau\r\n *\/\r\n@Stateless\r\npublic class ColumnModelFacade extends AbstractFacade {\r\n    @PersistenceContext(unitName = \"com.acme_AcmePools_war_AcmePools-1.0-SNAPSHOTPU\")\r\n    private EntityManager em;\r\n\r\n    @Override\r\n    protected EntityManager getEntityManager() {\r\n        return em;\r\n    }\r\n\r\n    public ColumnModelFacade() {\r\n        super(ColumnModel.class);\r\n    }\r\n\r\n    public ColumnModel findId(String columnName){\r\n        return (ColumnModel) em.createQuery(\"select object(o) from ColumnModel as o \" +\r\n                              \"where o.columnName = :columnName\")\r\n                              .setParameter(\"columnName\", columnName)\r\n                              .getSingleResult();\r\n    }\r\n    \r\n}<\/pre>\n
Next, create a some helper classes that will be utilized for loading and managing the data within the PickList component. The first class is named ColumnBean, and it is used to store the entity data, which is later passed off to the PickList for use. The code for ColumnBean is a simple POJO:<<\/p>\n
package com.acme.acmepools.bean;\r\n\r\nimport java.math.BigDecimal;\r\n\r\n\/**\r\n *\r\n * @author juneau\r\n *\/\r\npublic class ColumnBean {\r\n\r\n    private BigDecimal id;\r\n    private String columnName;\r\n    private String columnLabel;\r\n\r\n    public ColumnBean(BigDecimal id, String columnName, String columnLabel){\r\n        this.id = id;\r\n        this.columnName = columnName;\r\n        this.columnLabel = columnLabel;\r\n    }\r\n\r\n    \/**\r\n     * @return the id\r\n     *\/\r\n    public BigDecimal getId() {\r\n        return id;\r\n    }\r\n\r\n    \/**\r\n     * @param id the id to set\r\n     *\/\r\n    public void setId(BigDecimal id) {\r\n        this.id = id;\r\n    }\r\n\r\n    \/**\r\n     * @return the columnName\r\n     *\/\r\n    public String getColumnName() {\r\n        return columnName;\r\n    }\r\n\r\n    \/**\r\n     * @param columnName the columnName to set\r\n     *\/\r\n    public void setColumnName(String columnName) {\r\n        this.columnName = columnName;\r\n    }\r\n\r\n    \/**\r\n     * @return the columnLabel\r\n     *\/\r\n    public String getColumnLabel() {\r\n        return columnLabel;\r\n    }\r\n\r\n    \/**\r\n     * @param columnLabel the columnLabel to set\r\n     *\/\r\n    public void setColumnLabel(String columnLabel) {\r\n        this.columnLabel = columnLabel;\r\n    }\r\n\r\n}<\/pre>\n
The PickList component needs to use a PrimeFaces DualListModel for accessing and updating the data. Therefore, we must implement a class that can be used for coercing the entity data into our ColumnBean POJO, and then storing it into the DualListModel so that it can be utilized by the PickList component. In the following class, entitled PickListBean, the constructor accepts a List<ColumnModel>, which is the entity data as an argument, performs the coercion, and then stores it into a DualListModel<ColumnBean> collection for use by the component.<\/p>\n
package com.acme.acmepools.bean;\r\n\r\n\/**\r\n *\r\n * @author juneau\r\n *\/\r\n\r\nimport java.util.ArrayList;\r\nimport java.util.List;\r\nimport com.acme.acmepools.entity.ColumnModel;\r\n\r\nimport org.primefaces.model.DualListModel;\r\n\r\npublic class PickListBean {\r\n\r\n    private DualListModel<ColumnBean> columns;\r\n\r\n    private List<ColumnBean> source = null;\r\n    private List<ColumnBean> target = null;\r\n\r\n\r\n    public PickListBean(List<ColumnModel> columnModelList) {\r\n        \/\/Columns  \r\n        source = new ArrayList<ColumnBean>();\r\n        target = new ArrayList<ColumnBean>();\r\n   \r\n        for(ColumnModel column:columnModelList){\r\n            ColumnBean bean = new ColumnBean(column.getId(), column.getColumnName(), column.getColumnLabel());\r\n            source.add(bean);\r\n        }\r\n        \r\n\r\n        columns = new DualListModel<ColumnBean>(source, target);\r\n\r\n    }\r\n\r\n    public DualListModel<ColumnBean> getColumns() {\r\n        return columns;\r\n    }\r\n\r\n    public void setColumns(DualListModel<ColumnBean> columns) {\r\n        this.columns = columns;\r\n    }\r\n\r\n   \r\n}<\/pre>\n
Lastly, we need to create a controller class to access all of this data. To do so, create a class named ColumnModelController within the com.acme.acmepools.jsf package, and make it a CDI managed bean by annotating it with @Named and @SessionScoped. Make the class implement Serializable. The initial controller class should look as follows (we will be updating it later to include methods to facilitate the export):<\/p>\n
@Named\r\n@SessionScoped\r\npublic class ColumnModelController implements Serializable {\r\n\r\n    @EJB\r\n    ColumnModelFacade ejbFacade;\r\n\r\n    private PickListBean pickListBean;\r\n    private List<ColumnModel> columns;\r\n\r\n    public DualListModel<ColumnBean> getColumns() {\r\n        pickListBean = new PickListBean(ejbFacade.findAll());\r\n\r\n        return pickListBean.getColumns();\r\n    }\r\n    \r\n    public void setColumns(DualListModel<ColumnBean> columns) {\r\n        pickListBean.setColumns(columns);\r\n    }\r\n}<\/pre>\n
As you can see, the getColumns() method queries the ColumnModel entity, which populates the DualListModel<ColumnBean> via the PickListBean constructor.<\/p>\n
That takes care of the database infrastructure and business logic…now let’s look at the PrimeFaces component that is used for the PickList. \u00a0The following excerpt, taken from the WebPages\/poolCustomer\/CustomerExport.xhtml view, contains the markup for the PickList component:<\/p>\n
 <p:panel header=\"Choose Columns for Export\">\r\n                    <p:picklist effect=\"bounce\" itemlabel=\"#{column.columnLabel}\" itemvalue=\"#{column.columnName}\" showsourcecontrols=\"true\" showtargetcontrols=\"true\" value=\"#{columnModelController.columns}\" var=\"column\">\r\n                        <f:facet name=\"sourceCaption\">Columns<\/f:facet>\r\n                        <f:facet name=\"targetCaption\">Selected<\/f:facet>\r\n                    <\/p:picklist>\r\n             <\/p:panel><\/pre>\n
As you can see, the PickList is using columnModelController.columns for the data, which then uses the columnLabel field for displaying the names of the entity fields for export.\u00a0The titles for the source and target PickList windows are customizable via a facet. Adding the Export Functionality Now that we’ve developed a functional pick list, we need to do something with the data that is selected.\u00a0In this exercise, we will use a PrimeFaces DataExporter component to extract the data and store it into an Excel spreadsheet.\u00a0In reality, we need to incorporate a DataTable into the view to display the data first, and then we can use the DataExporter component to export the data which resides in the table. To construct the DataTable that will be used for displaying the data, we need to add a few methods to the ColumnModelController class.\u00a0These methods will allow us to process the DataTable dynamically, so that we can construct columns based upon those that are chosen within the PickList.\u00a0In reality, the DataTable will query all of the Customer data, and then it will only display those columns of data that are selected within the PickList.\u00a0(We could modify this query by adding a filter, but that is beyond the scope of this post).\u00a0To load the table with data, we simply call upon the com.acme.acmepools.jsf.CustomerController getItems() method to return all of the data… public List<Customer> getItems() { if (items == null) { items = getFacade().findAll(); } return items; }…Now let’s add the necessary methods to the ColumnModelController so that we can dynamically construct the table.\u00a0First, add a method that will be invoked when we click the “Export” button.\u00a0This method will be responsible for building the currently selected column list:<\/p>\n
public void preProcess(Object document) {\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 System.out.println(\"starting preprocess\");\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 updateColumns();\r\n\r\n\u00a0 \u00a0 }<\/pre>\n
Next, let’s take a look at the code for updateColumns(), which is invoked by the preProcess() method:<\/p>\n
\/**\r\n\r\n\u00a0 \u00a0 \u00a0* Called as preprocessor to export (after clicking Excel icon) to capture\r\n\r\n\u00a0 \u00a0 \u00a0* the table component and call upon createDynamicColumns()\r\n\r\n\u00a0 \u00a0 \u00a0*\/\r\n\r\n\u00a0 \u00a0 public void updateColumns() {\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \/\/reset table state\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 UIComponent table = FacesContext.getCurrentInstance().getViewRoot().findComponent(\":customerExportForm:customerTable\");\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 table.setValueExpression(\"sortBy\", null);\r\n\r\n\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \/\/update columns\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 createDynamicColumns();\r\n\r\n\u00a0 \u00a0 }<\/pre>\n
The updateColumns() method binds a UIComponent to the table within the JSF view.\u00a0It then has the capability of providing sorting, if elected. \u00a0Subsequently, lets now look at the createDynamicColumns() method that is called upon.<\/p>\n
\u00a0 \u00a0 private void createDynamicColumns() {\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 String[] columnKeys = this.getIncludedColumnsByName().split(\",\");\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 columns = new ArrayList<>();\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 for (String columnKey : columnKeys) {\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 String key = columnKey.trim();\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 columns.add(new ColumnModel(getColumnLabel(key), key));\r\n\r\n\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 }\r\n\r\n\u00a0 \u00a0 }<\/pre>\n
The createDynamicColumns() method does a few things.\u00a0First, it captures all of the selected columns from the PickList, and stores them into a String[] named columnKeys.\u00a0To do this we use the helper method named getIncludedColumnsByName(), and split the results by comma.\u00a0The sources for this method are as follows, and it basically grabs the currently selected columns from the PickListBean and appends each of them to a String, which is then returned to the caller.<\/p>\n
\u00a0 \u00a0 public String getIncludedColumnsByName() {\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 String tempIncludedColString = null;\r\n\r\n\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 System.out.println(\"Number of included columns:\" + pickListBean.getColumns().getTarget().size());\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 List localSource = pickListBean.getColumns().getTarget();\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 for (int x = 0; x <= localSource.size() - 1; x++) {\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 String tempModel = (String) localSource.get(x);\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 if (tempIncludedColString == null) {\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 tempIncludedColString = tempModel;\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 } else {\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 tempIncludedColString = tempIncludedColString + \",\" + tempModel;\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 }\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 }\r\n\r\n\r\n\r\n\u00a0 \u00a0 \u00a0 \u00a0 return tempIncludedColString;\r\n\r\n\u00a0 \u00a0 }<\/pre>\n
Next, the createDynamicColumns() method then uses a loop to parse through each of the selected columns within the String[], and add them to the columnList, which going to be used to construct the DataTable with the appropriate columns.<\/p>\n
Now let’s take a look at the markup that is used to construct the DataExport utility:<\/p>\n
<p:datatable id=\"customerTable\" rendered=\"false\" value=\"#{customerController.items}\" var=\"item\" widgetvar=\"customerTable\">                    \r\n                    <p:columns columnindexvar=\"colIndex\" value=\"#{columnModelController.dynamicColumns}\" var=\"column\">\r\n                        <f:facet name=\"header\">\r\n                            <h:outputtext value=\"#{column.header}\">\r\n                        <\/h:outputtext><\/f:facet>\r\n                        <h:outputtext value=\"#{item[column.property]}\">\r\n                    <\/h:outputtext><\/p:columns>\r\n                <\/p:datatable>\r\n                \r\n\r\n<hr \/>\r\n<h:outputtext value=\"Type of file to export: \">\r\n                <h:commandlink>\r\n\r\n                    <p:graphicimage value=\"\/faces\/resources\/images\/excel.png\">\r\n                    <p:dataexporter filename=\"customers\" id=\"propertyXlsExport\" preprocessor=\"#{columnModelController.preProcess}\" target=\"customerTable\" type=\"xls\">\r\n                <\/p:dataexporter><\/p:graphicimage><\/h:commandlink>\r\n<\/h:outputtext><\/pre>\n
As you can see, the DataTable is set to not render, because we really do not wish to display it. Instead, we wish to export its contents using the DataExporter component. To construct the DataTable dynamically, the columns call upon the columnModelController.dynamicColumns method to return the dynamic column list. This method looks as follows:<\/p>\n
public List<ColumnModel> getDynamicColumns() {\r\n        return columns;\r\n    }<\/pre>\n
Within the DataExporter utility component, the columnModelController.preProcess method is assigned to the preprocessor attribute to initiate the dynamic column list. The target is set to the customerTable widget, which is the DataTable that we’ve dynamically constructed based upon the selected columns. In order to export this to an xls spreadsheet, you must add the org.apache.poi dependency within the Maven POM for the project, as follows:<\/p>\n
<dependency>\r\n            <groupId>org.apache.poi<\/groupId>\r\n            <artifactId>poi<\/artifactId>\r\n            <version>3.7<\/version>\r\n        <\/dependency>\r\n<\/pre>\n
That’s it…now you should have a fully functional data export utility using PrimeFaces components. The complete sources are available on GitHub using the link below.\u00a0This code has been written in NetBeans IDE 8.0, and deployed to GlassFish 4.0.\u00a0I utilized PrimeFaces 5.0 for this project.<\/p>\n