Pagination with DataTables

yzx779

I am new to working with Bootstrap and DataTables/Pagination so I don't fully understand.

Thant being said my situation is this: I am querying on average 30K - 50K rows per query in my application. I query an Oracle database and return the results in a Java result set which I use to populate my Java model (MVC). I then use a Java Spring variable to populate the HTML to show to the end user. I am using the Ajax .load function and within that is where I use .dataTable. I just recently added the "deferRender": true line to the code in an effort to improve performance but I did not notice much improvement.

My thought is that I want to implement pagination in the way that when the user selects the next page, previous page last page etc.. a smaller query is run and thus a smaller result set is returned and processed so that the performance is improved for the end user.

I am not sure how to even start implementing pagination in this way and would appreciate any feedback anyone can give from what changes I should implement in the code and the alterations I could make to my query.

Thanks so much in advance!

Joel

Javascript
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function runReport(){
document.getElementById("ajaxloading").style.display="block";
var site = document.getElementById("sites");
var building = document.getElementById("buildings");
var siteName = site.options[site.selectedIndex].text;
var buildingName = building.options[building.selectedIndex].text;
var siteAndOrBuilding = null;

     if(siteName){
        siteAndOrBuilding = "/"+siteName+"/";
        if(buildingName != "Please select a building"){
            siteAndOrBuilding = siteAndOrBuilding + buildingName+ "/";
            }
        }

            loadTable(siteAndOrBuilding);

            } // END runReport()

// END runReport()
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
function loadTable(siteAndBuilding) {

var hardCodedLocation = siteAndBuilding;
hardCodedLocation = hardCodedLocation.replace(/ /g, "+");

$( "#ReportContainer" ).load( "GenerateSiteBuilding.htm?siteAndBuilding=" + hardCodedLocation + " #ReportContainerContent", function() {
$('#InitialTable').dataTable({
"bRetrieve": true,
"order": [[ 0, "asc" ]],
"deferRender": true
});

                document.getElementById("ReportContainer").style.display="block"; 
                document.getElementById("ajaxloading").style.display="none";
            });

} // END function
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//GenerateSiteBuilding.htm

public String getList(@RequestParam("siteAndBuilding") String siteAndBuilding, HttpSession session, ModelMap model,HttpServletRequest httpRequest) throws IOException{

AsvOwnerDao dao = (AsvOwnerDao) new AsvOwnerDaoImpl();

     List<DriveInitialReportModel> InitialReportList = new ArrayList<DriveInitialReportModel>();
     List<JSONObject> objList = new ArrayList<JSONObject>();
     String contain="";
    // Map<String, Object> data = new HashMap<String, Object>();
        try {

            InitialReportList = dao.InitialReport(siteAndBuilding);
            model.addAttribute("reportList", InitialReportList); 
            session.setAttribute("reportList", InitialReportList);
     }
   catch (Exception e)
   {
     e.printStackTrace();

   }
        return "InitialReportResults";
 }

}
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
QUERY

SELECT
A.barcode,
A.assettag,
A.serialno,
A.EXTERNALASSETID AS "RFID TAG",
P.seassignment AS "STATUS",
A.status AS "Asset Status",
M.Name AS "MODEL",
N.Name AS "NATURE",
(select M2.Name FROM ammodel M2 where M2.lmodelid = M.lparentid and M2.Name not in ('VIRTUAL PLACE HOLDER', 'VM DESKTOPS', 'VMWARE SERVERS')) AS "SUB-NATURE",
B.Name AS "MANUFACTURER",
P.extpfiid AS "HPSMID",
L.fullname AS "LOCATION"
FROM amportfolio P
left join ammodel M on P.lmodelid = M.lmodelid
left join amasset A on P.lastid = A.lastid
left join amlocation L on P.llocaid = L.llocaid
left join amnature N on M.lnatureid = N.lnatureid
left join ambrand B on M.lbrandid = B.lbraNdid
where P.seassignment = 0 and
N.Name not in ('VIRTUAL', 'VIRTUAL PLACEHOLDER', 'SOFTWARE LICENSE', 'OPERATING SYSTEM')
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////