For this post I will be using:

1) H2 in memory database with JPA and Hibernate for backend
2) EJB for service layer
3) JSF with javax.faces.bean.ManagedBean and javax.faces.bean.ViewScoped for the controllers
4) PrimeFaces p:datatable component for UI representation

Datatables are convenient components to display data. But one must be aware of how many data are loaded on a page request. That’s why I will be using pagination on the dataset with JPA.

Defining the JPA User entity

@Entity
public class User implements Serializable {

    @Id
    @Column(name = "ID")
    private Long id;

    @Column(name = "FIRST_NAME")
    private String first;

    @Column(name = "LAST_NAME")
    private String last;

    @Column(name = "EMAIL")
    private String email;

    // getters/setters + toString omitted

}

Define persistence.xml and the init script

Now let’s create the persistence.xml for our H2 database and add it to the META-INF folder. (This example will be deployed on Wildfly so I’m using the default datasource that comes pre-installed). Also take notice how I define the import.sql to load data upon server start.

 <persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemalocation="http://java.sun.com/xml/ns/ http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"><persistence-unit name="test"><jta-data-source>java:jboss/datasources/ExampleDS</jta-data-source></persistence-unit></persistence> 

And this is the init script that should be placed on the project’s classpath.

INSERT INTO USER VALUES (0, 'Maxwell', 'Markwell', 'mmark@kiss.mock');
INSERT INTO USER VALUES (1, 'Porfirio', 'Starns', 'epapath@kiss.mock');
INSERT INTO USER VALUES (2, 'Simon', 'Falgout', 'sfalg@kiss.mock');
INSERT INTO USER VALUES (3, 'Roger', 'Gauer', 'rgau@kiss.mock');
INSERT INTO USER VALUES (4, 'Angelo', 'Krom', 'akrom@kiss.mock');
INSERT INTO USER VALUES (5, 'Felton', 'Bayles', 'fbayles@kiss.mock');
INSERT INTO USER VALUES (6, 'Gregorio', 'Dawn', 'gdawn@kiss.mock');
INSERT INTO USER VALUES (7, 'Dwayne', 'Metoyer', 'dmeto@kiss.mock');
INSERT INTO USER VALUES (8, 'Rogelio', 'Yocom', 'ryocom@kiss.mock');
INSERT INTO USER VALUES (9, 'Bradford', 'Lasker', 'blasker@kiss.mock');
INSERT INTO USER VALUES (10, 'Mason', 'Held', 'mheld@kiss.mock');
INSERT INTO USER VALUES (11, 'Ethan', 'Barsky', 'ebarsky@kiss.mock');
INSERT INTO USER VALUES (12, 'Sung', 'Neubert', 'sneubert@kiss.mock');
INSERT INTO USER VALUES (13, 'Royce', 'Newcomer', 'rnewcomer@kiss.mock');
INSERT INTO USER VALUES (14, 'George', 'Faas', 'gfaas@kiss.mock');
INSERT INTO USER VALUES (15, 'Harry', 'Merriman', 'hmerriman@kiss.mock');
INSERT INTO USER VALUES (16, 'Nathanael', 'Brannigan', 'nbrannigan@kiss.mock');
INSERT INTO USER VALUES (17, 'Marvin', 'Wines', 'mwines@kiss.mock');
INSERT INTO USER VALUES (18, 'Jack', 'Clifford', 'jcliff@kiss.mock');
INSERT INTO USER VALUES (19, 'Sherman', 'Lydick', 'slydick@kiss.mock');

Defining the service layer.

The #retrieveUsers method  fetches data paginated using an index and a pageSize, filtered using the filters param and ordered using the sortField and sortOrder params.

The #countUsers method count the Users that exist in the database using the _filters _param. This method is used by PrimeFaces to do calculation on how many pages are left.

@Stateless
public class UserService {

    @PersistenceContext
    private EntityManager em;

    public List<UserViewBean> retrieveUsers(int first, int pageSize,
                                            String sortField,
                                            SortOrder sortOrder,
                                            Map<String, Object> filters) {

        String sql = "from " + User.class.getName()+" user where 1=1 ";

        String emailFilter = (String) filters.get("email");
        String lastNameFilter = (String) filters.get("last");
        String firstNameFilter = (String) filters.get("first");

        if (emailFilter != null) {
            sql += " and user.email like :email ";
        }

        if (lastNameFilter != null) {
            sql += " and user.last like :lastName ";
        }

        if (firstNameFilter != null) {
            sql += " and user.first like :firstName ";
        }

        if (sortField != null) {
            sql += " order by "+sortField+" "
                    +(sortOrder.equals(SortOrder.ASCENDING) ?
                        "ASC" :
                        "DESC");
        }

        TypedQuery<User> query = em.createQuery(sql, User.class)
                .setFirstResult(first)
                .setMaxResults(pageSize);

        if (emailFilter != null) {
            query.setParameter("email", "%"+emailFilter+"%");
        }

        if (lastNameFilter != null) {
            query.setParameter("lastName", "%"+lastNameFilter+"%");
        }

        if (firstNameFilter != null) {
            query.setParameter("firstName", "%"+firstNameFilter+"%");
        }

        return convert(
                query.getResultList()
        );
    }

    public int countUsers(Map<String, Object> filters) {

        String sql = "select count(user) from "
                + User.class.getName() +
                " user where 1=1 ";

        String emailFilter = (String)filters.get("email");
        String lastNameFilter = (String)filters.get("last");
        String firstNameFilter = (String)filters.get("first");

        if (emailFilter != null) {
            sql += " and user.email like :email ";
        }

        if (lastNameFilter != null) {
            sql += " and user.last like :lastName ";
        }

        if (firstNameFilter != null) {
            sql += " and user.first like :firstName ";
        }

        TypedQuery<Long> query = em.createQuery(sql, Long.class);

        if (emailFilter != null) {
            query.setParameter("email", "%"+emailFilter+"%");
        }

        if (lastNameFilter != null) {
            query.setParameter("lastName", "%"+lastNameFilter+"%");
        }

        if (firstNameFilter != null) {
            query.setParameter("firstName", "%"+firstNameFilter+"%");
        }

        return query.getSingleResult().intValue();
    }

    // private convert method is omitted to save up space :)
}

JSF controller

The lazy datatable controller. Primefaces provides an abstract class to extend.

@javax.faces.bean.ManagedBean
@javax.faces.bean.ViewScoped
public class LazyDatatableController extends LazyDataModel<UserViewBean> implements Serializable {

    @EJB
    private UserService userService;

    @Override
    public List<UserViewBean> load(int first, int pageSize, 
            String sortField, SortOrder sortOrder, 
            Map<String, Object> filters) {
        List<UserViewBean> users = userService.retrieveUsers(first, pageSize, sortField, sortOrder, filters);
        setRowCount(userService.countUsers(filters));
        return users;
    }
}

The xhtml markup

<h:form id="form">
<p:datatable id="userDatatable" lazy="true" paginator="true" 
    paginatortemplate="{RowsPerPageDropdown} {FirstPageLink} {PreviousPageLink} {CurrentPageReport} {NextPageLink} {LastPageLink}" 
    rows="5" rowsperpagetemplate="5,10,15" 
    value="#{lazyDatatableController}" var="user" widgetvar="userDatatable">
    <p:column filterby="#{user.first}" sortby="#{user.first}">
        #{user.first}
    </p:column> 
    <p:column filterby="#{user.last}" sortby="#{user.last}">
        #{user.last}
    </p:column> 
    <p:column filterby="#{user.email}" sortby="#{user.email}">
        #{user.email}
    </p:column> 
    <p:column filterby="#{user.role}" sortby="#{user.role}">
        #{user.role}
    </p:column>
</p:datatable>
</h:form> 

The outcome

the outcome