jQWidgets Forums

Tagged: grid, JAVA, Java integration, jqxgrid, JSP, oracle, Oracle Database, server paging, server side paging, url

This topic contains 4 replies, has 2 voices, and was last updated by Dimitar 8 years, 4 months ago.

Viewing 5 posts - 1 through 5 (of 5 total)

Author

ServerPaging with Oracle and JSP Posts

December 8, 2016 at 2:28 am ServerPaging with Oracle and JSP #89653

Participant

Howdy guys!
I have a tiny question and wish you guys help me out :'(

I have a Oracle DB and its size is kinda huge tho. So I would use ServerPaging with Oracle and JSP
I was gonna use a example like this (http://www.jqwidgets.com/jquery-widgets-documentation/documentation/java-integration/grid-server-side-sorting-paging-filtering-using-jsp.htm). However, I got to have some errors.. my sources are as below.

<!DOCTYPE html>
<html lang="en">
<head>
    <title id='Description'>This example demonstrates how to implement server sorting, paging and filtering with jqxGrid, JSP and MySQL.</title>
    <link rel="stylesheet" href="jqwidgets/styles/jqx.base.css" type="text/css" />
    <script type="text/javascript" src="scripts/jquery-1.11.1.min.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxcore.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxbuttons.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxscrollbar.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxlistbox.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxmenu.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.selection.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.filter.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.sort.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxdata.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxgrid.pager.js"></script>
    <script type="text/javascript" src="jqwidgets/jqxdropdownlist.js"></script>
    <script type="text/javascript" src="scripts/demos.js"></script>
    <script type="text/javascript">
        $(document).ready(function() {
            var source = {
                datatype: "json",
                datafields: [{
                    name: 'NAME',
                    type: 'string'
                }, {
                    name: 'CONTENT',
                    type: 'string'
                }, {
                    name: 'SYS_DATE',
                    type: 'string'
                }],
                cache: false,
                url: 'jsp/select-filtered-data.jsp',
                filter: function() {
                    // update the grid and send a request to the server.
                    $("#jqxgrid").jqxGrid('updatebounddata', 'filter');
                },
                sort: function() {
                    // update the grid and send a request to the server.
                    $("#jqxgrid").jqxGrid('updatebounddata', 'sort');
                },
                beforeprocessing: function(data) {
                    if (data != null && data.length > 0) {
                        source.totalrecords = data[0].totalRecords;
                    }
                }
            };
            var filterChanged = false;
            var dataadapter = new $.jqx.dataAdapter(source, {
                /*
                // remove the comment to debug
                formatData: function(data) {
                    alert(JSON.stringify(data));
                    return data;
                },*/
                downloadComplete: function(data, status, xhr) {
                    if (!source.totalRecords) {
                        source.totalRecords = data.length;
                    }
                },
                loadError: function(xhr, status, error) {
                    throw new Error(error);
                }
            });
            // initialize jqxGrid
            $("#jqxgrid").jqxGrid({
                width: 550,
                source: dataadapter,
                filterable: true,
                sortable: true,
                autoheight: true,
                pageable: true,
                pagesize: 3,
                pagesizeoptions: ['3', '4', '5'],
                virtualmode: true,
                rendergridrows: function(obj) {
                    return obj.data;
                },
                columns: [{
                    text: 'NAME',
                    datafield: 'NAME',
                    width: 100
                }, {
                    text: 'CONTENT',
                    datafield: 'CONTENT',
                    width: 100
                }, {
                    text: 'SYS_DATE',
                    datafield: 'SYS_DATE',
                    width: 180
                }]
            });
        });
    </script>

</head>
<body class='default'>
    <div id="jqxgrid">
    </div>
</body>
</html>

<%@ page import="java.lang.*,java.sql.*,com.google.gson.*,org.json.simple.*,java.util.*"%>

<%
    String where = "";
    Integer filterscount = 0;
    if (null != request.getParameter("filterscount"))
    {
        try
        {
            filterscount = Integer.parseInt(request.getParameter("filterscount"));
        }
        catch (NumberFormatException nfe) {}
        {
        }       
    }
    if (filterscount > 0) {
        where = " WHERE (";
        
        String tmpdatafield = "";
        String tmpfilteroperator = "";
        for (Integer i=0; i < filterscount; i++)
        {
            String filtervalue = request.getParameter("filtervalue" + i);
            String filtercondition = request.getParameter("filtercondition" + i);
            String filterdatafield = request.getParameter("filterdatafield" + i);
            filterdatafield = filterdatafield.replaceAll("([^A-Za-z0-9])", "");
            String filteroperator = request.getParameter("filteroperator" + i);
            
            if (tmpdatafield.equals(""))
            {
                tmpdatafield = filterdatafield;         
            }
            else if (!tmpdatafield.equals(filterdatafield))
            {
                where += ")AND(";
            }
            else if (tmpdatafield.equals(filterdatafield))
            {
                if (tmpfilteroperator.equals("0"))
                {
                    where += " AND ";
                }
                else where += " OR ";   
            }
                
            // build the "WHERE" clause depending on the filter's condition, value and datafield.
            switch(filtercondition)
            {
                case "CONTAINS":
                    where += " " + filterdatafield + " LIKE '%" + filtervalue + "%'";
                    break;
                case "CONTAINS_CASE_SENSITIVE":
                    where += " " + filterdatafield + " LIKE BINARY '%" + filtervalue + "%'";
                    break;
                case "DOES_NOT_CONTAIN":
                    where += " " + filterdatafield + " NOT LIKE '%" + filtervalue + "%'";
                    break;
                case "DOES_NOT_CONTAIN_CASE_SENSITIVE":
                    where += " " + filterdatafield + " NOT LIKE BINARY '%" + filtervalue + "%'";
                    break;
                case "EQUAL":
                    where += " " + filterdatafield + " = '" + filtervalue + "'";
                    break;
                case "EQUAL_CASE_SENSITIVE":
                    where += " " + filterdatafield + " LIKE BINARY '" + filtervalue + "'";
                    break;
                case "NOT_EQUAL":
                    where += " " + filterdatafield + " NOT LIKE '" + filtervalue + "'";
                    break;
                case "NOT_EQUAL_CASE_SENSITIVE":
                    where += " " + filterdatafield + " NOT LIKE BINARY '" + filtervalue + "'";
                    break;
                case "GREATER_THAN":
                    where += " " + filterdatafield + " > '" + filtervalue + "'";
                    break;
                case "LESS_THAN":
                    where += " " + filterdatafield + " < '" + filtervalue + "'";
                    break;
                case "GREATER_THAN_OR_EQUAL":
                    where += " " + filterdatafield + " >= '" + filtervalue + "'";
                    break;
                case "LESS_THAN_OR_EQUAL":
                    where += " " + filterdatafield + " <= '" + filtervalue + "'";
                    break;
                case "STARTS_WITH":
                    where += " " + filterdatafield + " LIKE '" + filtervalue + "%'";
                    break;
                case "STARTS_WITH_CASE_SENSITIVE":
                    where += " " + filterdatafield + " LIKE BINARY '" + filtervalue + "%'";
                    break;
                case "ENDS_WITH":
                    where += " " + filterdatafield + " LIKE '%" + filtervalue + "'";
                    break;
                case "ENDS_WITH_CASE_SENSITIVE":
                    where += " " + filterdatafield + " LIKE BINARY '%" + filtervalue + "'";
                    break;
                case "NULL":
                    where += " " + filterdatafield + " IS NULL";
                    break;
                case "NOT_NULL":
                    where += " " + filterdatafield + " IS NOT NULL";
                    break;
            }
                                
            if (i == filterscount - 1)
            {
                where += ")";
            }
                
            tmpfilteroperator = filteroperator;
            tmpdatafield = filterdatafield;         
        }
    }
    String orderby = "";
    String sortdatafield = request.getParameter("sortdatafield");
    String sortorder = request.getParameter("sortorder");
    if (sortdatafield != null && sortorder != null && (sortorder.equals("asc") || sortorder.equals("desc")))
    {
        sortdatafield = sortdatafield.replaceAll("([^A-Za-z0-9])", "");
    
        orderby = "order by " + sortdatafield + " " + sortorder;
    }
    
    
    Integer pagenum = 0;
    if (null != request.getParameter("pagenum"))
    {
        try
        {
            pagenum = Integer.parseInt(request.getParameter("pagenum"));
        }
        catch (NumberFormatException nfe) {}
        {
        }       
    }
    
    Integer pagesize = 1000;
    if (null != request.getParameter("pagesize"))
    {
        try
        {
            pagesize = Integer.parseInt(request.getParameter("pagesize"));
        }
        catch (NumberFormatException nfe) {}
        {
        }       
    }
    
    Integer start = pagesize * pagenum;
    
    // database connection
    // "jdbc:mysql://localhost:3306/northwind" - the database url of the form jdbc:subprotocol:subname
    Connection dbConnection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.xxx.xxx:TEST", "dbusername", "dbpassword");
    // retrieve necessary records from database
    Statement getFromDb = dbConnection.createStatement();
    ResultSet totalEmployees = getFromDb.executeQuery("SELECT COUNT(*) AS Count FROM PEG.TEST_BOARD" + where);
    String totalRecords = ""; 
    while (totalEmployees.next()) {
        totalRecords = totalEmployees.getString("Count");
    }
    totalEmployees.close();
    String sql = "SELECT NAME,CONTENT,SYS_DATE FROM PEG.TEST_BOARD " + where + " " + orderby + " LIMIT ?,?";
    PreparedStatement stmt = dbConnection.prepareStatement(sql);
    stmt.setInt(1, start);
    stmt.setInt(2, pagesize);
    
    ResultSet employees = stmt.executeQuery();
    boolean totalRecordsAdded = false;
    // format returned ResultSet as a JSON array
    JsonArray recordsArray = new JsonArray();
    while (employees != null && employees.next()) {
        JsonObject currentRecord = new JsonObject();
        currentRecord.add("NAME", new JsonPrimitive(employees.getString("NAME")));
        currentRecord.add("CONTENT", new JsonPrimitive(employees.getString("CONTENT")));
        currentRecord.add("SYS_DATE", new JsonPrimitive(employees.getString("SYS_DATE")));
        if (totalRecordsAdded == false) {
            // add the number of filtered records to the first record for client-side use
            currentRecord.add("totalRecords", new JsonPrimitive(totalRecords));
            totalRecordsAdded = true;
        }
        recordsArray.add(currentRecord);
    }
    
    out.print(recordsArray);
    out.flush();
%>

December 8, 2016 at 7:25 am ServerPaging with Oracle and JSP #89666

Dimitar

Participant

Hello ligun89,

Could you, please, share what errors are thrown in your browser’s console? Please also note that we can only assist you with your client-side code regarding the use of jQWidgets and that we generally do not review server-side code.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

December 8, 2016 at 8:15 am ServerPaging with Oracle and JSP #89669

ligun89

Participant

This is an error I got from Chrome console.

Failed to load resource: the server responded with a status of 404 (Not Found)
Uncaught Error: Not Found
at Object.loadError (http://192.168.211.28:8080/serverPaging.html:64:27)
at Object.error (http://192.168.211.28:8080/jqwidgets/jqxdata.js:7:22547)
at j (http://192.168.211.28:8080/scripts/jquery-1.11.1.min.js:2:27244)
at Object.fireWith [as rejectWith] (http://192.168.211.28:8080/scripts/jquery-1.11.1.min.js:2:28057)
at S (http://192.168.211.28:8080/jqwidgets/jqxdata.js:7:64496)
at XMLHttpRequest.H (http://192.168.211.28:8080/jqwidgets/jqxdata.js:7:70895)

I understand this part of source is not your stuff..but i’m striving to solve this problem in order to use JQWidget.
So, if you have any idea, please let me know that. Thank you.

December 8, 2016 at 8:51 am ServerPaging with Oracle and JSP #89675

ligun89

Participant

Sorry, ignore above error message. I mistook my server address.
This is an error… please look at this.

December 8, 2016 at 1:20 pm ServerPaging with Oracle and JSP #89695

Dimitar

Participant

Hello ligun89,

Are you sure there is a file select-filtered-data.jsp in your project? This is the name of the demo JSP file from our tutorial. Please set the source object’s url to the path to an existing file that processes data from your database.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

Author

Posts

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.