My day job involves heavy use of data. We use relational databases to store everything, because we rely on enterprise level data management. Sometimes 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. This 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. The 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.
We want to ensure that we have a clean user interface that is intuitive. For that reason, I chose not to display any data on the screen. Rather, 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. Let's begin by setting up the database infrastructure to make this export utility possible.
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. The export utility allows one to export customer data into a spreadsheet. The 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 project within your environment.
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. The end result will resemble a user interface that looks like Figure 1.
Figure 1: Data Export Utility
Developing the PickList Component
To begin, create the data infrastructure to support the PickList component. This 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. In 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.
-- Add support for data export
create table column_model(
id int primary key,
column_name varchar(30),
column_label varchar(150));
-- Optional sequence for primary key generation
create sequence column_model_s
start with 1
increment by 1;
-- Load with field (database column) names
insert into column_model values(
1,
'addressline1',
'Address Line 1');
insert into column_model values(
2,
'addressline2',
'Address Line 2');
insert into column_model values(
3,
'city',
'City');
insert into column_model values(
4,
'creditLimit',
'Credit Limit');
insert into column_model values(
5,
'customerId',
'Customer Id');
insert into column_model values(
6,
'discountCode',
'Discount Code');
insert into column_model values(
7,
'email',
'Email');
insert into column_model values(
8,
'fax',
'Fax');
insert into column_model values(
9,
'name',
'Name');
insert into column_model values(
10,
'phone',
'Phone');
insert into column_model values(
11,
'state',
'State');
insert into column_model values(
12,
'zip',
'Zip');
Next, create an entity class that can be used for accessing the column data from within the component. If you use an IDE such as NetBeans, this can be done very easily via a wizard. If 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. When the list of tables populates, select the COLUMN_MODEL table, as shown in Figure 2. Lastly, choose "Next" and "Finish" to create the entity class.
Figure 2. NetBeans IDE New Entity Classes from Database
Once completed, the entity class entitled ColumnModel should look as follows:
package com.acme.acmepools.entity;
import java.io.Serializable;
import java.math.BigDecimal;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;
import javax.xml.bind.annotation.XmlRootElement;
/**
*
* @author Juneau
*/
@Entity
@Table(name = "COLUMN_MODEL")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "ColumnModel.findAll", query = "SELECT c FROM ColumnModel c"),
@NamedQuery(name = "ColumnModel.findById", query = "SELECT c FROM ColumnModel c WHERE c.id = :id"),
@NamedQuery(name = "ColumnModel.findByColumnName", query = "SELECT c FROM ColumnModel c WHERE c.columnName = :columnName"),
@NamedQuery(name = "ColumnModel.findByColumnLabel", query = "SELECT c FROM ColumnModel c WHERE c.columnLabel = :columnLabel")})
public class ColumnModel implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@NotNull
@Column(name = "ID")
private BigDecimal id;
@Size(max = 30)
@Column(name = "COLUMN_NAME")
private String columnName;
@Size(max = 150)
@Column(name = "COLUMN_LABEL")
private String columnLabel;
public ColumnModel() {
}
public ColumnModel(BigDecimal id) {
this.id = id;
}
public BigDecimal getId() {
return id;
}
public void setId(BigDecimal id) {
this.id = id;
}
public String getColumnName() {
return columnName;
}
public void setColumnName(String columnName) {
this.columnName = columnName;
}
public String getColumnLabel() {
return columnLabel;
}
public void setColumnLabel(String columnLabel) {
this.columnLabel = columnLabel;
}
@Override
public int hashCode() {
int hash = 0;
hash += (id != null ? id.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof ColumnModel)) {
return false;
}
ColumnModel other = (ColumnModel) object;
if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
return false;
}
return true;
}
@Override
public String toString() {
return "com.acme.acmepools.entity.ColumnModel[ id=" + id + " ]";
}
}
Next, create an EJB session bean for the newly generated entity class so that the component can query the column data. You can use your IDE for this as well if you'd like. If using NetBeans, right-click on the com.acme.acmepools.session package, and select "New"->"Session Beans for Entity Classes". Once the dialog opens, select the entity class "com.acme.acmepools.entity.ColumnModel" from the left-hand list, and click "Finish" (Figure 3).
Figure 3: NetBeans IDE Session Beans for Entity Classes Dialog
After 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. The full sources for the ColumnModelFacade should look as follows:
package com.acme.acmepools.session;
import com.acme.acmepools.entity.ColumnModel;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
/**
*
* @author Juneau
*/
@Stateless
public class ColumnModelFacade extends AbstractFacade {
@PersistenceContext(unitName = "com.acme_AcmePools_war_AcmePools-1.0-SNAPSHOTPU")
private EntityManager em;
@Override
protected EntityManager getEntityManager() {
return em;
}
public ColumnModelFacade() {
super(ColumnModel.class);
}
public ColumnModel findId(String columnName){
return (ColumnModel) em.createQuery("select object(o) from ColumnModel as o " +
"where o.columnName = :columnName")
.setParameter("columnName", columnName)
.getSingleResult();
}
}
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:<
package com.acme.acmepools.bean;
import java.math.BigDecimal;
/**
*
* @author juneau
*/
public class ColumnBean {
private BigDecimal id;
private String columnName;
private String columnLabel;
public ColumnBean(BigDecimal id, String columnName, String columnLabel){
this.id = id;
this.columnName = columnName;
this.columnLabel = columnLabel;
}
/**
* @return the id
*/
public BigDecimal getId() {
return id;
}
/**
* @param id the id to set
*/
public void setId(BigDecimal id) {
this.id = id;
}
/**
* @return the columnName
*/
public String getColumnName() {
return columnName;
}
/**
* @param columnName the columnName to set
*/
public void setColumnName(String columnName) {
this.columnName = columnName;
}
/**
* @return the columnLabel
*/
public String getColumnLabel() {
return columnLabel;
}
/**
* @param columnLabel the columnLabel to set
*/
public void setColumnLabel(String columnLabel) {
this.columnLabel = columnLabel;
}
}
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.
package com.acme.acmepools.bean;
/**
*
* @author juneau
*/
import java.util.ArrayList;
import java.util.List;
import com.acme.acmepools.entity.ColumnModel;
import org.primefaces.model.DualListModel;
public class PickListBean {
private DualListModel<ColumnBean> columns;
private List<ColumnBean> source = null;
private List<ColumnBean> target = null;
public PickListBean(List<ColumnModel> columnModelList) {
//Columns
source = new ArrayList<ColumnBean>();
target = new ArrayList<ColumnBean>();
for(ColumnModel column:columnModelList){
ColumnBean bean = new ColumnBean(column.getId(), column.getColumnName(), column.getColumnLabel());
source.add(bean);
}
columns = new DualListModel<ColumnBean>(source, target);
}
public DualListModel<ColumnBean> getColumns() {
return columns;
}
public void setColumns(DualListModel<ColumnBean> columns) {
this.columns = columns;
}
}
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):
@Named
@SessionScoped
public class ColumnModelController implements Serializable {
@EJB
ColumnModelFacade ejbFacade;
private PickListBean pickListBean;
private List<ColumnModel> columns;
public DualListModel<ColumnBean> getColumns() {
pickListBean = new PickListBean(ejbFacade.findAll());
return pickListBean.getColumns();
}
public void setColumns(DualListModel<ColumnBean> columns) {
pickListBean.setColumns(columns);
}
}
As you can see, the getColumns() method queries the ColumnModel entity, which populates the DualListModel<ColumnBean> via the PickListBean constructor.
That takes care of the database infrastructure and business logic...now let's look at the PrimeFaces component that is used for the PickList. The following excerpt, taken from the WebPages/poolCustomer/CustomerExport.xhtml view, contains the markup for the PickList component:
<p:panel header="Choose Columns for Export">
<p:picklist effect="bounce" itemlabel="#{column.columnLabel}" itemvalue="#{column.columnName}" showsourcecontrols="true" showtargetcontrols="true" value="#{columnModelController.columns}" var="column">
<f:facet name="sourceCaption">Columns</f:facet>
<f:facet name="targetCaption">Selected</f:facet>
</p:picklist>
</p:panel>
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. The 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. In this exercise, we will use a PrimeFaces DataExporter component to extract the data and store it into an Excel spreadsheet. In 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. These methods will allow us to process the DataTable dynamically, so that we can construct columns based upon those that are chosen within the PickList. In 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. (We could modify this query by adding a filter, but that is beyond the scope of this post). To 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. First, add a method that will be invoked when we click the "Export" button. This method will be responsible for building the currently selected column list:
public void preProcess(Object document) {
System.out.println("starting preprocess");
updateColumns();
}
Next, let's take a look at the code for updateColumns(), which is invoked by the preProcess() method:
/**
* Called as preprocessor to export (after clicking Excel icon) to capture
* the table component and call upon createDynamicColumns()
*/
public void updateColumns() {
//reset table state
UIComponent table = FacesContext.getCurrentInstance().getViewRoot().findComponent(":customerExportForm:customerTable");
table.setValueExpression("sortBy", null);
//update columns
createDynamicColumns();
}
The updateColumns() method binds a UIComponent to the table within the JSF view. It then has the capability of providing sorting, if elected. Subsequently, lets now look at the createDynamicColumns() method that is called upon.
private void createDynamicColumns() {
String[] columnKeys = this.getIncludedColumnsByName().split(",");
columns = new ArrayList<>();
for (String columnKey : columnKeys) {
String key = columnKey.trim();
columns.add(new ColumnModel(getColumnLabel(key), key));
}
}
The createDynamicColumns() method does a few things. First, it captures all of the selected columns from the PickList, and stores them into a String[] named columnKeys. To do this we use the helper method named getIncludedColumnsByName(), and split the results by comma. The 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.
public String getIncludedColumnsByName() {
String tempIncludedColString = null;
System.out.println("Number of included columns:" + pickListBean.getColumns().getTarget().size());
List localSource = pickListBean.getColumns().getTarget();
for (int x = 0; x <= localSource.size() - 1; x++) {
String tempModel = (String) localSource.get(x);
if (tempIncludedColString == null) {
tempIncludedColString = tempModel;
} else {
tempIncludedColString = tempIncludedColString + "," + tempModel;
}
}
return tempIncludedColString;
}
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.
Now let's take a look at the markup that is used to construct the DataExport utility:
<p:datatable id="customerTable" rendered="false" value="#{customerController.items}" var="item" widgetvar="customerTable">
<p:columns columnindexvar="colIndex" value="#{columnModelController.dynamicColumns}" var="column">
<f:facet name="header">
<h:outputtext value="#{column.header}">
</h:outputtext></f:facet>
<h:outputtext value="#{item[column.property]}">
</h:outputtext></p:columns>
</p:datatable>
<hr />
<h:outputtext value="Type of file to export: ">
<h:commandlink>
<p:graphicimage value="/faces/resources/images/excel.png">
<p:dataexporter filename="customers" id="propertyXlsExport" preprocessor="#{columnModelController.preProcess}" target="customerTable" type="xls">
</p:dataexporter></p:graphicimage></h:commandlink>
</h:outputtext>
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:
public List<ColumnModel> getDynamicColumns() {
return columns;
}
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:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.7</version>
</dependency>
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. This code has been written in NetBeans IDE 8.0, and deployed to GlassFish 4.0. I utilized PrimeFaces 5.0 for this project.
GitHub Sources:
https://github.com/juneau001/AcmePools
