I want to implement select all checkbox into JSF h:datatable. This is the code if the h:datatable:
<?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="DX-57" /> History Center</h1>
<!-- layer for black background of the buttons -->
<div id="toolbar" style="margin: 0 auto; width:1180px; height:30px; position:relative; background-color:black">
<!-- Include page Navigation -->
<ui:insert name="Navigation">
<ui:include src="Navigation.xhtml"/>
</ui:insert>
</div>
<div id="logodiv" 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">
<ui:debug />
<!-- The sortable data table -->
<h:dataTable id="dataTable" value="#{SessionsController.dataList}" binding="#{table}" var="item">
<!-- Check box -->
<h:column>
<f:facet name="header">
<h:outputText value="Select" />
</f:facet>
<h:selectBooleanCheckbox onclick="highlight(this)" value="#{SessionsController.selectedIds[item.aSessionID]}" />
</h:column>
<h:column>
<f:facet name="header">
<h:commandLink value="№" actionListener="#{SessionsController.sort}">
<f:attribute name="№" value="№" />
</h:commandLink>
</f:facet>
<h:outputText value="#{table.rowIndex + SessionsController.firstRow + 1}" />
</h:column>
<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>
<!-- The paging buttons -->
<h:commandButton value="first" action="#{SessionsController.pageFirst}"
disabled="#{SessionsController.firstRow == 0}" >
<f:ajax render="@form" execute="@form"></f:ajax>
</h:commandButton>
<h:commandButton value="prev" action="#{SessionsController.pagePrevious}"
disabled="#{SessionsController.firstRow == 0}" >
<f:ajax render="@form" execute="@form"></f:ajax>
</h:commandButton>
<h:commandButton value="next" action="#{SessionsController.pageNext}"
disabled="#{SessionsController.firstRow + SessionsController.rowsPerPage >= SessionsController.totalRows}" >
<f:ajax render="@form" execute="@form"></f:ajax>
</h:commandButton>
<h:commandButton value="last" action="#{SessionsController.pageLast}"
disabled="#{SessionsController.firstRow + SessionsController.rowsPerPage >= SessionsController.totalRows}" >
<f:ajax render="@form" execute="@form"></f:ajax>
</h:commandButton>
<h:outputText value="Page #{SessionsController.currentPage} / #{SessionsController.totalPages}" />
<br />
<!-- The paging links -->
<ui:repeat value="#{SessionsController.pages}" var="page">
<h:commandLink value="#{page}" actionListener="#{SessionsController.page}"
rendered="#{page != SessionsController.currentPage}" >
<f:ajax render="@form" execute="@form"></f:ajax>
</h:commandLink>
<h:outputText value="#{page}" escape="false"
rendered="#{page == SessionsController.currentPage}" />
</ui:repeat>
<br />
<!-- Set rows per page -->
<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}" >
<f:ajax render="@form" execute="@form"></f:ajax>
</h:commandButton>
<h:message for="rowsPerPage" errorStyle="color: red;" />
<h:commandButton value="Delete" action="#{SessionsController.deleteSelectedIDs}" >
</h:commandButton>
</h:form>
</div>
<div id="settingsdivb" style="width:350px; height:400px; position:absolute; background-color:transparent; top:20px; left:800px">
</div>
</div>
</div>
</h:body>
</html>
This is the managed bean:
package com.DX_57.HM_57;
/*
* include default packages for Beans
*/
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.*;
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;
@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 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;
//private boolean selected;
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 int getFirstRow() {
return firstRow;
}
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 SqlStatementSmall = null;
String SQLStatementLarge = 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";
/*
* For small tables
*/
SqlStatementSmall = "SELECT c.*"
+ " FROM (SELECT b.*, rownum rn"
+ " FROM (SELECT a.*"
+ " FROM activeSessionsLog a"
+ " ORDER BY %s %s) b"
+ " WHERE rownum <= ?) c"
+ " WHERE rn > ?";
/*
* For huge datatables optimized for perfomance
*/
SQLStatementLarge = "select * from"
+ " (select asl.*, row_number() over (order by userid asc) r "
+ " from activesessionslog asl)"
+ " where r > 980750 and r <= 980800;";
String sql = String.format(SqlStatementSmall, sortField, sortDirection);
PreparedStatement ps = null;
ResultSet resultSet = null;
List<ActiveSessionObj> dataList = new ArrayList<ActiveSessionObj>();
try {
conn.setAutoCommit(false);
boolean committed = false;
ps = conn.prepareStatement(sql);
ps.setInt(1, countrow);
ps.setInt(2, firstRow);
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 {
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 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;
}
//get the selected rows
private HashMap<String, Boolean> selectedIds = new HashMap<>();
// get the list from the JSF page
public Map<String, Boolean> getSelectedIds() {
return selectedIds;
}
//delete all selected checkboxes
public void deleteSelectedIDs() throws SQLException {
boolean success = false;
if (ds == null) {
throw new SQLException();
}
Connection conn = ds.getConnection();
if (conn == null) {
throw new SQLException();
}
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
conn.setAutoCommit(false);
boolean committed = false;
try {
String sqlDeleteQuery = "DELETE FROM ACTIVESESSIONSLOG WHERE ASESSIONID = ?";
Set<String> keySet = selectedIds.keySet();
String[] keys = new String[]{};
keys = selectedIds.keySet().toArray(keys);
ps = conn.prepareStatement(sqlDeleteQuery);
for (int i = 0; i < keys.length; i++) {
ps.setString(1, keys[i]);
ps.executeUpdate();
ps.clearParameters();
selectedIds.put(keys[i], false); //get(keys[i]) = false;
}
conn.commit();
committed = true;
//selectedIds.clear();
} finally {
if (!committed) {
conn.rollback();
}
}
} finally {
ps.close();
conn.close();
}
/*
* // Get selected items. boolean success = false; try { String
* sqlDeleteQuery = "";
*
* Set<String> keySet = selectedIds.keySet(); Iterator keyIterator =
* keySet.iterator(); while(keyIterator.hasNext()) { keyIterator.next();
* }
*
* }
* catch(Exception x) { success = false; } // Do your thing with the
* MyData items in List selectedDataList.
*/
//return success; // Navigation case.
}
private static Date timestampToDate(java.sql.Timestamp ts) {
Date d = null;
try {
d = new Date(ts.getTime());
} catch (Exception e) {
e.printStackTrace();
}
return d;
}
}
My question is how I can implement elective select all button. One way to create java method which gets all keys of the database table and create hashmap which stores all keys. Something like this:
private HashMap<String, Boolean> selectedIds = new HashMap<>();
This code works very well when I tried to implement select row check boxes. The problem is what happens when I have one million rows or more into the into the database table? In this case all keys will be inserted into one very big hashmap and maybe I will get error because the memory consumption will be too big. Is there more optimized solution?
Best wishes Peter
UPDATE
I have implemented simple check box for selecting a rows and passing them to delete java method which is deleting the rows into the database table using key. But how I can implement select all into JavaScript client side and call java method which deletes all records from the database table?
