I want to implement the code from this tutorial
I populated an Oracle table with four rows of data, but when I try to move between JSF pages every time the second page is empty:
This is the JSF page code:
<?xml version='1.0' encoding='UTF-8' ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" xmlns:h="http://java.sun.com/jsf/html" xmlns:f="http://java.sun.com/jsf/core" xmlns:ui="http://java.sun.com/jsf/facelets"> <h:head> <ui:insert name="header"> <ui:include src="header.xhtml"/> </ui:insert> </h:head> <h:body> <h1><img src="resources/css/images/icon.png" alt="NVIDIA.com" /> History Center</h1> <div id="toolbar" style="margin: 0 auto; width:1180px; height:30px; position:relative; background-color:black"> <ui:insert name="Navigation"> <ui:include src="Navigation.xhtml"/> </ui:insert> </div> <div id="greenBand" class="ui-state-default ui-corner-allh" style="position:relative; top:35px; left:0px;"> <h:graphicImage alt="Dashboard" style="position:relative; top:-20px; left:9px;" value="resources/images/logo_sessions.png" /> </div> <div id="main" style="margin: 0 auto; width:1190px; height:700px; position:absolute; background-color:transparent; top:105px"> <div id="mainpage" style="margin: 0 auto; width:1190px; height:500px; position:absolute; background-color:transparent; top:80px"> <div id="settingsHashMap" style="width:750px; height:400px; position:absolute; background-color:r; top:20px; left:1px"> <h:form id="form"> <h:dataTable value="#{SessionsController.dataList}" var="item"> <h:column> <f:facet name="header"> <h:commandLink value="Account Session ID" actionListener="#{SessionsController.sort}"> <f:attribute name="sortField" value="Account Session ID" /> </h:commandLink> </f:facet> <h:outputText value="#{item.aSessionID}" /> </h:column> <h:column> <f:facet name="header"> <h:commandLink value="User ID" actionListener="#{SessionsController.sort}"> <f:attribute name="sortField" value="User ID" /> </h:commandLink> </f:facet> <h:outputText value="#{item.userID}" /> </h:column> <h:column> <f:facet name="header"> <h:commandLink value="Activity Start Time" actionListener="#{SessionsController.sort}"> <f:attribute name="sortField" value="Activity Start Time" /> </h:commandLink> </f:facet> <h:outputText value="#{item.activityStart}" /> </h:column> <h:column> <f:facet name="header"> <h:commandLink value="Activity End Time" actionListener="#{SessionsController.sort}"> <f:attribute name="sortField" value="Activity End Time" /> </h:commandLink> </f:facet> <h:outputText value="#{item.activityEnd}" /> </h:column> <h:column> <f:facet name="header"> <h:commandLink value="Activity" actionListener="#{SessionsController.sort}"> <f:attribute name="sortField" value="Activity" /> </h:commandLink> </f:facet> <h:outputText value="#{item.activity}" /> </h:column> </h:dataTable> <h:commandButton value="first" action="#{SessionsController.pageFirst}" disabled="#{SessionsController.firstRow == 0}" /> <h:commandButton value="prev" action="#{SessionsController.pagePrevious}" disabled="#{SessionsController.firstRow == 0}" /> <h:commandButton value="next" action="#{SessionsController.pageNext}" disabled="#{SessionsController.firstRow + SessionsController.rowsPerPage >= SessionsController.totalRows}" /> <h:commandButton value="last" action="#{SessionsController.pageLast}" disabled="#{SessionsController.firstRow + SessionsController.rowsPerPage >= SessionsController.totalRows}" /> <h:outputText value="Page #{SessionsController.currentPage} / #{SessionsController.totalPages}" /> <br /> <ui:repeat value="#{SessionsController.pages}" var="page"> <h:commandLink value="#{page}" actionListener="#{SessionsController.page}" rendered="#{page != SessionsController.currentPage}" /> <h:outputText value="#{page}" escape="false" rendered="#{page == SessionsController.currentPage}" /> </ui:repeat> <br /> <h:outputLabel for="rowsPerPage" value="Rows per page" /> <h:inputText id="rowsPerPage" value="#{SessionsController.rowsPerPage}" size="3" maxlength="3" /> <h:commandButton value="Set" action="#{SessionsController.pageFirst}" /> <h:message for="rowsPerPage" errorStyle="color: red;" /> </h:form> </div> <div id="settingsdiva" style="width:350px; height:400px; position:absolute; background-color:transparent; top:20px; left:400px"> </div> <div id="settingsdivb" style="width:350px; height:400px; position:absolute; background-color:transparent; top:20px; left:800px"> </div> </div> </div> </h:body> </html>
Sessions:
import java.io.Serializable; import javax.enterprise.context.SessionScoped; // or import javax.faces.bean.SessionScoped; import javax.inject.Named; /* include SQL Packages */ import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.annotation.PostConstruct; import javax.sql.DataSource; import javax.annotation.Resource; import javax.faces.bean.ViewScoped; import javax.faces.component.UICommand; import javax.faces.context.FacesContext; import javax.inject.Inject; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpSession; // or import javax.faces.bean.ManagedBean; import javax.faces.event.ActionEvent; import org.glassfish.osgicdi.OSGiService; // source http://balusc.blogspot.com/2008/10/effective-datatable-paging-and-sorting.html @Named("SessionsController") @ViewScoped public class Sessions implements Serializable { /* Call the Oracle JDBC Connection driver */ @Resource(name = "jdbc/Oracle") private DataSource ds; // Data. private List<ActiveSessionObj> dataList; // Rows private int totalRows; // Paging. private int firstRow; private int rowsPerPage; private int totalPages; private int pageRange; private Integer[] pages; private int currentPage; // Sorting. private String sortField; private boolean sortAscending; /* Constructor */ public Sessions() { /* Set default properties */ rowsPerPage = 10; // Default rows per page (max amount of rows to be displayed at once). pageRange = 10; // Default page range (max amount of page links to be displayed at once). sortField = "ASESSIONID"; // Default sort field. sortAscending = true; // Default sort direction. } public static class ActiveSessionObj { /* Oracle table structure CREATE TABLE ACTIVESESSIONSLOG( ASESSIONID VARCHAR2(30 ) NOT NULL, USERID VARCHAR2(30 ), ACTIVITYSTART TIMESTAMP(6), ACTIVITYEND TIMESTAMP(6), ACTIVITY CLOB ) */ private String aSessionID; private String userID; private Date activityStart; private Date activityEnd; private String activity; public ActiveSessionObj(String aSessionID, String userID, Date activityStart, Date activityEnd, String activity) { this.aSessionID = aSessionID; this.userID = userID; this.activityStart = activityStart; this.activityEnd = activityEnd; this.activity = activity; } public String getaSessionID() { return aSessionID; } public void setaSessionID(String aSessionID) { this.aSessionID = aSessionID; } public String getactivity() { return activity; } public void setactivity(String activity) { this.activity = activity; } public Date getactivityEnd() { return activityEnd; } public void setactivityEnd(Date activityEnd) { this.activityEnd = activityEnd; } public Date getactivityStart() { return activityStart; } public void setactivityStart(Date activityStart) { this.activityStart = activityStart; } public String getuserID() { return userID; } public void setuserID(String userID) { this.userID = userID; } private ActiveSessionObj() { throw new UnsupportedOperationException("Not yet implemented"); } } // Paging actions ----------------------------------------------------------------------------- public void pageFirst() { page(0); } public void pageNext() { page(firstRow + rowsPerPage); } public void pagePrevious() { page(firstRow - rowsPerPage); } public void pageLast() { page(totalRows - ((totalRows % rowsPerPage != 0) ? totalRows % rowsPerPage : rowsPerPage)); } public void page(ActionEvent event) { page(((Integer) ((UICommand) event.getComponent()).getValue() - 1) * rowsPerPage); } private void page(int firstRow) { this.firstRow = firstRow; loadDataList(); // Load requested page. } // Sorting actions ---------------------------------------------------------------------------- public void sort(ActionEvent event) { String sortFieldAttribute = (String) event.getComponent().getAttributes().get("sortField"); // If the same field is sorted, then reverse order, else sort the new field ascending. if (sortField.equals(sortFieldAttribute)) { sortAscending = !sortAscending; } else { sortField = sortFieldAttribute; sortAscending = true; } pageFirst(); // Go to first page and load requested page. } // Loaders ------------------------------------------------------------------------------------ private void loadDataList() { // Load list and totalCount. try { dataList = list(firstRow, rowsPerPage, sortField, sortAscending); totalRows = countDBRowNum(); //count the tablerows } catch (Exception e) { throw new RuntimeException(e); } // Set currentPage, totalPages and pages. currentPage = (totalRows / rowsPerPage) - ((totalRows - firstRow) / rowsPerPage) + 1; totalPages = (totalRows / rowsPerPage) + ((totalRows % rowsPerPage != 0) ? 1 : 0); int pagesLength = Math.min(pageRange, totalPages); pages = new Integer[pagesLength]; // firstPage must be greater than 0 and lesser than totalPages-pageLength. int firstPage = Math.min(Math.max(0, currentPage - (pageRange / 2)), totalPages - pagesLength); // Create pages (page numbers for page links). for (int i = 0; i < pagesLength; i++) { pages[i] = ++firstPage; } } // Getters ------------------------------------------------------------------------------------ public List<ActiveSessionObj> getdataList() { if (dataList == null) { loadDataList(); // Preload page for the 1st view. } return dataList; } public int getTotalRows() { return totalRows; } public int getFirstRow() { return firstRow; } public int getRowsPerPage() { return rowsPerPage; } public Integer[] getPages() { return pages; } public int getCurrentPage() { return currentPage; } public int getTotalPages() { return totalPages; } // Setters ------------------------------------------------------------------------------------ public void setRowsPerPage(int rowsPerPage) { this.rowsPerPage = rowsPerPage; } // Actions ------------------------------------------------------------------------------------ /** * Returns list of ActiveSessionObj items starting at the given first index with the given row count, * sorted by the given sort field and sort order. * @param firstRow First index of rows to be returned. * @param rowCount Amount of rows to be returned. * @param sortField Field to sort the data on. * @param sortAscending Whether to sort data ascending or not. * @return list of ActiveSessionObj items starting at the given first index with the given row count, * sorted by the given sort field and sort order. * @throws DAOException If something fails at DAO level. */ public List<ActiveSessionObj> list(int firstRow, int rowCount, String sortField, boolean sortAscending) throws SQLException { String SqlStatement = null; if (ds == null) { throw new SQLException(); } Connection conn = ds.getConnection(); if (conn == null) { throw new SQLException(); } String sortDirection = sortAscending ? "ASC" : "DESC"; SqlStatement = "SELECT c.*" + " FROM (SELECT b.*, rownum rn" + " FROM (SELECT a.*" + " FROM activeSessionsLog a" + " ORDER BY %s %s) b" + " WHERE rownum <= ?) c" + " WHERE rn >= ?"; String sql = String.format(SqlStatement, sortField, sortDirection); PreparedStatement ps = null; ResultSet resultSet = null; List<ActiveSessionObj> dataList = new ArrayList<ActiveSessionObj>(); try { conn.setAutoCommit(false); boolean committed = false; try { ps = conn.prepareStatement(sql); ps.setInt(1, firstRow); ps.setInt(2, rowCount); resultSet = ps.executeQuery(); /* take the result from the SQL query and insert it into Array List collection */ dataList = ActiveSessionsArrayList(resultSet); conn.commit(); committed = true; } finally { if (!committed) { conn.rollback(); } } } finally { ps.close(); conn.close(); } return dataList; } /** * Returns total amount of rows in table. * @return Total amount of rows in table. * @throws DAOException If something fails at DAO level. */ public int countDBRowNum() throws Exception { String SqlStatement = null; if (ds == null) { throw new SQLException(); } Connection conn = ds.getConnection(); if (conn == null) { throw new SQLException(); } SqlStatement = "SELECT count(*) FROM ACTIVESESSIONSLOG"; PreparedStatement ps = null; ResultSet resultSet = null; int count = 0; try { conn.setAutoCommit(false); boolean committed = false; try { SqlStatement = "SELECT count(*) FROM ACTIVESESSIONSLOG"; ps = conn.prepareStatement(SqlStatement); resultSet = ps.executeQuery(); if (resultSet.next()) { count = resultSet.getInt(1); } conn.commit(); committed = true; } finally { if (!committed) { conn.rollback(); } } } finally { ps.close(); conn.close(); } return count; } /** * Map the current row of the given ResultSet to ActiveSessionObj. * @param resultSet The ResultSet of which the current row is to be mapped to ActiveSessionObj. * @return The mapped ActiveSessionObj from the current row of the given ResultSet. * @throws SQLException If something fails at database level. */ private static ArrayList<ActiveSessionObj> ActiveSessionsArrayList(ResultSet rs) throws SQLException { ArrayList<ActiveSessionObj> list = new ArrayList<>(); try { while (rs.next()) { list.add(new ActiveSessionObj( rs.getString("ASESSIONID"), rs.getString("USERID"), timestampToDate(rs.getTimestamp("ACTIVITYSTART")), timestampToDate(rs.getTimestamp("ACTIVITYEND")), rs.getString("ACTIVITY"))); } } catch (Exception x) { x.printStackTrace(); } return list; /* CREATE TABLE ACTIVESESSIONSLOG( ASESSIONID VARCHAR2(30 ) NOT NULL, USERID VARCHAR2(30 ), ACTIVITYSTART TIMESTAMP(6), ACTIVITYEND TIMESTAMP(6), ACTIVITY CLOB ) */ } // select all button handle private Map<Long, Boolean> selectedIds = new HashMap<Long, Boolean>(); // get the list from the JSF page public Map<Long, Boolean> getSelectedIds() { return selectedIds; } // call this method from the JSF page to delete the selected rows public String deleteAction() { // do something with the selected rows return null; } private static Date timestampToDate(java.sql.Timestamp ts) { Date d = null; try { d = new Date(ts.getTime()); } catch (Exception e) { e.printStackTrace(); } return d; } }
Maybe the pagination algorithm is incorrect or the SQL query is not fixed? I did not touch the pagination algorithm from the source code.
Best regards
EDIT
SQL query is invalid.
EDIT 2 I updated the SQL code, but now the first page is incorrect, and the second, etc. Display data. The problem here is somewhere:
EDIT 3 Work Code
public List List (int firstRow, int rowCount, String sortField, boolean sortAscending) throws SQLException {
String SqlStatement = null; if (ds == null) { throw new SQLException(); } Connection conn = ds.getConnection(); if (conn == null) { throw new SQLException(); } int countrow = firstRow + rowCount; String sortDirection = sortAscending ? "ASC" : "DESC"; SqlStatement = "SELECT c.*" + " FROM (SELECT b.*, rownum rn" + " FROM (SELECT a.*" + " FROM activeSessionsLog a" + " ORDER BY %s %s) b" + " WHERE rownum <= ?) c" + " WHERE rn > ?"; String sql = String.format(SqlStatement, sortField, sortDirection); PreparedStatement ps = null; ResultSet resultSet = null; List<ActiveSessionObj> dataList = new ArrayList<ActiveSessionObj>(); try { conn.setAutoCommit(false); boolean committed = false; try { ps = conn.prepareStatement(sql); ps.setInt(1, countrow); ps.setInt(2, firstRow); resultSet = ps.executeQuery(); dataList = ActiveSessionsArrayList(resultSet); conn.commit(); committed = true; } finally { if (!committed) { conn.rollback(); } } } finally { ps.close(); conn.close(); } return dataList;
}