Nitobi Knowledgebase Article - Loading Data (JSP)

Nitobi Knowledgebase - Loading Data (JSP)

Loading Data (JSP)

This simple walkthrough will show you how to connect a basic grid to a datasource. In this case the datasource is an Access MDB file, although you could easily convert it to connect to an MS SQL server. There are two pages in this walkthrough. The first builds a web page that displays a grid. The second web page connects to a datasource and supplies the grid with data.

The grid may return all or none of the following parameters to the gethandler (depending on the configuration of the grid)

gethandler.jsp?start=0&PageSize=15&SortColumn=CustomerName&SortDirection=ASC&SearchString=abc

start - The current page of data to be returned
PageSize - How many records to return
SortColumn - The data should be sorted by this xml field
SortDirection - The direction the data should be sorted.
SearchString - The text to search by (LOOKUP gethandlers only)

If this gethandler is being written for a LOOKUP field, only the searchstring parameter needs to be accounted for. The searchstring parameter is the text that is being typed by the user.

For the first part of this tutorial, see the page "Basic Grid Tutorial" referenced at the bottom of this page.

Data Handler (GetHandler) in JSP

Begin by importing the necessary libraries, including the EBA gethandler and savehandler classes.

<%@ page import="java.sql.*"%>
<%@ page import="eba.gethandler.*"%>

Next, write your database connection function. Perform a query.

            // get the complete path on the server of our database.
           String path = config.getServletContext().getRealPath("/")
                   + pathAfterContext + "dataMasterDetailDB.mdb";

           // get the database connection
           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
           Connection con = DriverManager.getConnection(
                   "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ="
                           + path, "", "");

           Statement st = con.createStatement();
            String newQuery = "SELECT TOP " + (pageSize + ordinalStart)
                   + " * FROM tblCustomers ORDER BY " + sortColumn + " "
                   + sortDirection + ";";

           ResultSet rs = st.executeQuery(newQuery);

Execute this function, and set up the getHandler.

// Lets Set up the GetHandler
GetHandler myGetHandler = new GetHandler(response, out);

Define our data columns.

            // First we define columns we are sending in each record.
            myGetHandler.defineField("CustomerID");
            myGetHandler.defineField("CustomerName");
            myGetHandler.defineField("ContactName");
            myGetHandler.defineField("ContactEmail");
            myGetHandler.defineField("ContactTitle");
            myGetHandler.defineField("PhoneNumber");
            myGetHandler.defineField("Address");
            myGetHandler.defineField("Country");

Loop through the database results and write out the records using the data columns we created.

            // loop through the ResultSet from the Database and set values to myGetHandler
            Record curRecord;
            String customerId;
            int addedRecords = 0;
            int ordinalRecordNumber = 0;
            while (rs.next() && addedRecords < pageSize) {
                if (ordinalRecordNumber >= ordinalStart) {
                    customerId = rs.getString("CustomerID");
                    curRecord = myGetHandler.createNewRecord(customerId);
                    curRecord.setField("CustomerID",     customerId);
                    curRecord.setField("ContactName",     rs.getString("ContactName"));
                    curRecord.setField("CustomerName",     rs.getString("CustomerName"));
                    curRecord.setField("ContactEmail",     rs.getString("ContactEmail"));
                    curRecord.setField("ContactTitle",     rs.getString("ContactTitle"));
                    curRecord.setField("PhoneNumber",     rs.getString("PhoneNumber"));
                    curRecord.setField("Address",         rs.getString("Address"));
                    curRecord.setField("Country",         rs.getString("Country"));

                    myGetHandler.addRecord(curRecord);
                    addedRecords++;
                }
                ordinalRecordNumber++;
            }

Finish by writing the output to the client.

myGetHandler.writeToClient("UTF-8");

            // clean up connections
            rs.close();
            st.close();
            con.close();

%>

Here is the entire code sample.

<%@ page import="java.sql.*"%>
<%@ page import="eba.gethandler.*"%>

<%
            // This file is used as a Get Handler for the Grid control. When the grid is initialized,
            // the get handler script (this page) is called and expected to return a properly formatted
            // xml stream. We have provided all the necessary functionality to do this without actually
            // requiring you to construct XML. Simply interface with your datasource and use the provided
            // classes in the namespaces eba.gethandler to create the necessary output.

            // Gethandlers must be able to output xml when called without any parameters. If paging is used,
            // they need to respond to the parameters "pagesize" and "start" ie:

            // ?pagesize=15&start=101&SortColumn&SortDirection

            // In this example above, the grid has asked the gethandler to return 15 records, beginning at record 101.
            // 101 is not an ID, its the ordinal position of the starting record.

            // Server side sorting is also possible, and so the gethandler may need to respond to the parameters
            // "sortcolumn" and "sortdirection" ie:

            // ?pagesize=50&start=99&SortColumn=FirstName&SortDirection=ASC

            // In this example, the grid has asked the gethandler to return 15 records, beginning at record 0,
            // from the database sorted by the FirstName column in ascending order.

            String startParameter = request.getParameter("start");
            if (startParameter == null) {
                startParameter = "0"; // define default value for parameter start if this page is called without parameter start.
            }
            int ordinalStart = Integer.parseInt(startParameter);

            String pagesizeParameter = request.getParameter("PageSize");
            if ((pagesizeParameter == null)
                    || (0 == pagesizeParameter.length())) {
                pagesizeParameter = "15"; // define default value for parameter pagesize if this page is called without parameter pagesize.
            }
            int pageSize = Integer.parseInt(pagesizeParameter);

            String sortColumn = request.getParameter("SortColumn");
            if ((sortColumn == null) || (0 == sortColumn.length())) {
                sortColumn = "CustomerID ";// define default value for parameter sortColumn if this page is called without parameter SortColumn.
            }

            String sortDirection = request.getParameter("SortDirection");
            if ((sortDirection == null) || (0 == sortDirection.length())) {
                sortDirection = "ASC";
            }

            /**************************************************************8
            Figure out the path to the mdb file that has the needed data

            We need to create:
            C:Program FileseBusiness ApplicationsGrid V3.XJsp TrialsamplesBasicLinkedGridsdataMasterDetailDB.mdb

            This breaks down to the following:
            [C:Program FileseBusiness ApplicationsGrid V3.XJsp Trial]+[samplesBasicLinkedGrids]+[dataMasterDetailDB.mdb]

            The above breakdown maps to the following:
            getRealPath("/") ---> [C:Program FileseBusiness ApplicationsGrid V3.XJsp Trial]
            getServletPath() ---> [samplesLinkedGrids] (get.jsp is removed)
            hard code string ---> [dataMasterDetail.mdb]

            */

            // from the URL we need to remove the first three tokens [http:/][/localhost:8080][/samplesContext/]
            // Token 4 to N need to be added to the value returned from getRealPath
            // Also the last token is the name of the get handler
            String pathAfterContext = request.getServletPath();
            // remove the get.jsp part.
            pathAfterContext = pathAfterContext.substring(0,
                    pathAfterContext.lastIndexOf("/")).replace('/', '');

            // get the complete path on the server of our database.
            String path = config.getServletContext().getRealPath("/")
                    + pathAfterContext + "dataMasterDetailDB.mdb";

            // get the database connection
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
            Connection con = DriverManager.getConnection(
                    "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ="
                            + path, "", "");

            Statement st = con.createStatement();

            String newQuery = "SELECT TOP " + (pageSize + ordinalStart)
                    + " * FROM tblCustomers ORDER BY " + sortColumn + " "
                    + sortDirection + ";";

            ResultSet rs = st.executeQuery(newQuery);
            // Lets Set up the GetHandler
            GetHandler myGetHandler = new GetHandler(response, out);

            // We could send back the query as an error message to help with debugging.
            // To do so, uncomment this following line.
            //myGetHandler.setErrorMessage(newQuery);

            // First we define columns we are sending in each record.
            myGetHandler.defineField("CustomerID");
            myGetHandler.defineField("CustomerName");
            myGetHandler.defineField("ContactName");
            myGetHandler.defineField("ContactEmail");
            myGetHandler.defineField("ContactTitle");
            myGetHandler.defineField("PhoneNumber");
            myGetHandler.defineField("Address");
            myGetHandler.defineField("Country");

            // loop through the ResultSet from the Database and set values to myGetHandler
            Record curRecord;
            String customerId;
            int addedRecords = 0;
            int ordinalRecordNumber = 0;
            while (rs.next() && addedRecords < pageSize) {
                if (ordinalRecordNumber >= ordinalStart) {
                    customerId = rs.getString("CustomerID");
                    curRecord = myGetHandler.createNewRecord(customerId);
                    curRecord.setField("CustomerID",     customerId);
                    curRecord.setField("ContactName",     rs.getString("ContactName"));
                    curRecord.setField("CustomerName",     rs.getString("CustomerName"));
                    curRecord.setField("ContactEmail",     rs.getString("ContactEmail"));
                    curRecord.setField("ContactTitle",     rs.getString("ContactTitle"));
                    curRecord.setField("PhoneNumber",     rs.getString("PhoneNumber"));
                    curRecord.setField("Address",         rs.getString("Address"));
                    curRecord.setField("Country",         rs.getString("Country"));

                    myGetHandler.addRecord(curRecord);
                    addedRecords++;
                }
                ordinalRecordNumber++;
            }

            myGetHandler.writeToClient("UTF-8");
            // clean up connections
            rs.close();
            st.close();
            con.close();
%>

We are very interested in improving out JSP and JSF support with a customer action tag. Please contact us with your ideas and suggestion for improvement in J2EE support.

Saving Data (JSP)

Comments:

Note: This article was taken from the Nitobi knowledgebase. To search the knowledgebase, go to http://www.nitobi.com/kb/.