jQuery UI Widgets Forums Grid ServerPaging with Oracle and JSP

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

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
  • ServerPaging with Oracle and JSP #89653

    ligun89
    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();
    %>
    • This topic was modified 7 years, 5 months ago by  ligun89.
    • This topic was modified 7 years, 5 months ago by  ligun89.
    • This topic was modified 7 years, 5 months ago by  ligun89.
    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/

    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.

    • This reply was modified 7 years, 5 months ago by  ligun89.
    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.

    Navigated to http://192.168.211.28:8080/serverPaging.html
    GET http://192.168.211.28:8080/jsp/select-filtered-data.jsp?filterscount=0&grou…0&pagenum=0&pagesize=3&recordstartindex=0&recordendindex=3&_=1481186720117 404 (Not Found)
    XHR finished loading: GET “http://192.168.211.28:8080/jsp/select-filtered-data.jsp?filterscount=0&grou…0&pagenum=0&pagesize=3&recordstartindex=0&recordendindex=3&_=1481186720117”.
    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)

    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/

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

You must be logged in to reply to this topic.