Life without xPages # 3 – a little more JAVA please

This week, after reading the tempest of comments generated by Marky Roden’s post, I though “Wow, what a perfect time to write a post entirely focused on JAVA”.

And just to contribute my measly 2 pence, I believe you need to learn BOTH Java and JavaScript, and you do need a working knowledge of xPages and a solid understanding of Domino, and that’s just to set up your REST Services!

So, more JAVA? Yes please, but just a little bit.

In LWOXP #1 I demonstrated a simple example that returns all Contact records as JSON.

As my examples become more full featured, my Contacts REST Service needs to be a little more robust, so here are the requirements.

  • GET – The service will be able to return one or all Contact records.
  • POST – The service will be able to create a new Contact record.
  • PUT – The service will be able to update a Contact record.
  • DELETE – The service will able to delete a Contact record.

The Contacts REST services will be called via an xAgent.

The first thing I’ll need is a Contact Class.

This Contact class will be a Plain Old Java Object (POJO), will know all of the attributes that define a Contact, and have methods to read or write those attributes to or from a JSON object or Domino document.

To keep things simple, the class property names match the Domino document field names. Also, notice that I’m using the IBM Commons JSON library to handle converting JSON strings to a HashMap.

This is my Contact POJO:

Ok, so we have a class that models a Contact, now we need a class to handle the REST Service.

Here is an excellent REST Service Template that will get you started.

I’ve posted my entire Contacts REST service at the end of this article.

Let’s examine each REST method the Contacts Service implements.

GET – If a UNID is passed as a URL parameter, the service will return only the target Contact’s JSON, otherwise return a JSON array of all Contacts.

Get a contact by UNID: ( if the UNID isn’t found, just return an empty JSON model. )

Get all Contacts:

POST – The POST method takes a JSON string, creates a new Domino Document, and returns a JSON object updated with the new document’s UNID.

PUT – The PUT method uses the UNID passed as a URL parameter, and a JSON string of the changed Contact attributes. It will return an updated JSON string for the Contact record.

DELETE –  The DELETE method uses the UNID passed as a URL parameter to located the target Domino document and deletes it from the database. Returns, if successful, Boolean true is and a success message, otherwise a Boolean false is returned along with an error message.

That’s it.  The JAVA madness is over! That’s all server side code we’ll need.

Stay tuned.  In LWOXP #5,  I’ll show you how to test the Contacts REST Service using Backbone.js.

Until then, Happy Coding.

– Jeff

Life without xPages #2 – Where Do I Put My Stuff?

Any time you move into a new neighborhood you have to learn your way around.

It’s no different in my Life Without xPages ( LWOXP ).  I soon discovered I needed a new place to put my stuff.

My old neighborhood was the Applications tab and everything was all laid out for me, forms here, views there, very organized. They even added a new place for my JAVA code. Thanks fellas.

My new neighborhood is in the Package Explorer and things are a bit more loosey-goosey, but I like it!

There’s plenty of room to grow!

Like any new home owner, I want to fix things up the way I like them.

Head on over to the Rabbit Hole and read Nathan’s take of setting up the designer, and then take Paul Withers advice and backing up your settings.

Just because I don’t live on the Applications Tab anymore doesn’t mean I don’t visit.

I still have business there.  In LWOXP #1 I demonstrated one of many ways to get at your JSON. I used all of the old standards; a view, a view column of computed json, and a page. Very old school.

Now I want to show you how I’ve set up my new diggs.

3-16-2014 2-24-03 PM

I hope you like my folder feng-shui.  I believe it’s a pretty standard configuration.  My new home comes with new tricks, like dragging and dropping entire libraries into my file folders.

Lets talk about those folders. Remember, anything under WebContent looks like any ‘ole file system to your browser.

This really opens things up in a world without xPages.

We can branch out and play with all of the cool new JavaScript toys.  Just drag-n-drop your favorite example code into a folders and and open it in your browser, it just works!

Every new blog post deserves a demo, and in today’s demonstration, I’ll show a simple web page that will display our Contacts JSON.

Disclaimer:  I did not create this example! I merely adapted it to Domino.

The original example can be found here.

We’ll start with Index.html.

3-16-2014 2-56-17 PM

That’s a pretty bare bones HTML set up.  One CSS file and four JavaScript libraries. Oh, there is one div and something called a template. What gives?

The “contacts” div is where we will place our list of contacts, and the “contactTemplate” is the Underscope.js template is we’ll use format the contact information when it is displayed.

In xPages speak, the template is a custom control that will be rendered by a repeat control. ( But I think this is MUCH simpler ).

I’m using the Backbone.js library to render by Contact information.  Backbone has dependencies on jQuery and Underscore.js.  More on Backbone.js in later posts.  For now just Google it.  It’ll rock your world and open yours eyes to the possibilities of Life Without xPages.

That leaves us app.js, the last library in our list..


var app = app || {}; // Global application var
(function ($) { // Self executing function

// define Backbone Model for each contact
app.Contact = Backbone.Model.extend({
 defaults: {
    id:"",
    name:"",
    company:"",
    city:"",
    state:"",
    email:"",
    photo: "img/placeholder.png"
 }
});

// define directory collection of model for our Contacts
app.Directory = Backbone.Collection.extend({
 url:'json/contacts.json', //url to fetch the contacts
 model: app.Contact // model to use for each contact
});

//define a BackBone.js View for each contact
app.ContactView = Backbone.View.extend({
 tagName: "article",
 className: "contact-container",
 template: $("#contactTemplate").html(),
 render: function () {
    var tmpl = _.template(this.template);
    $(this.el).html(tmpl(this.model.toJSON()));
    return this;
 }
});

//define a BackBone view to list all Contacts
app.DirectoryView = Backbone.View.extend({
 el: $("#contacts"), // This is the div for the collection

 // Called to render all Contacts in the collection
 render: function () {
    var that = this;
    _.each(this.collection.models, function (item) {
      that.renderContact(item);
    }, this );
 },

 // Called to render an individual Contact
 renderContact: function (item) {
    var contactView = new app.ContactView({
       model: item
    });

    this.$el.append(contactView.render().el);
  }
});

// create a Backbone Collection contacts from our Notes Data
// Use the Directory collection that we defined above.
app.contacts = new app.Directory()
// call fetch to get Conacts JSON,
// use then() to make sure the fetch is complete.
app.contacts.fetch().then( function(){
  // After the fetch is complete, create the master view
  app.directory = new app.DirectoryView({collection:app.contacts});

  // now render all the contact cards.
  app.directory.render();
});

}(jQuery));

First, if you have a studied your JavaScript Closures, ( and I have), you’ll see this is a self executing function., and that I’m passing in jQuery as a parameter.

We set up our Backbone models, collections, and views and master view to call the whole thing.

Notice how our Directory collection use the contacts JSON we created in part one.

The final result looks like this:

3-16-2014 3-34-30 PMI’ve skimmed over a lot if information in this post, but not to worry, I’ll be covering Backbone.js models, views and collections. There is a LOT more out there.

Oh, one final thing since I’m here up on my soap box.  I am so over using grids to display data. You can show so much more information using divs, or a list, or any combination of the two.  Throw in Bootstrap and go crazy with new inventive ways to present the user with information.  ( Stepping down now, rant complete. )

Until next time then, thanks for reading.

– Jeff

Life without xPages #1 – Where’s my JSON?

The first challenge in my Life Without xPages was to get at my Domino data.

With the Domino platform that is not really an issue as there are many ways to get data, and by data I mean JSON.

If the web were a country, JSON would be the coin of the realm!

Since Domino 7 we’ve been able to get JSON from a view:
ByrdWeb.nsf/vwContacts?readviewentries&outputformat=JSON

View JSON 1

View JSON 1

Not very pretty, but chock full o’ data.

Here’s how I want my JSON served up:
ByrdWeb.nsf/json/contacts.json

3-9-2014 3-30-24 PMMuch nicer, and easier to consume.

There’s nothing new here folks, but it’s worth reviewing.

Other, better,  blog posts have covered creating a view column to format your data as JSON.

This is my View Column Formula to format the Contact Information as JSON:

3-9-2014 3-36-11 PM

3-9-2014 3-41-20 PM

But I don’t want to call a view, to many parameters, not enough control.

The missing piece here, the element that I had forgotten, ( and I did create this blog to help remember things I had forgotten ), was using a Domino Page to get my JSON.

3-9-2014 3-42-14 PM

3-9-2014 3-43-40 PMThe Domino Page’s Web Access is to Other “application /json”. We’re telling Domino not “help” in any way when rendering the page. The browser treats the page like any other file sitting in the json folder.

Again, nothing new here, I was inspired by this blog post, Getting JSON from Domino Pages.

So why am I writing this, other than I had forgotten about this trick?

I’ll show you Part #2 of this series. I’ll demonstrate how to use Require.js on the client side to load your JSON into a Backbone.js model.

Cheers,

– Jeff

What I learned without xPages

Happy 2014!  I haven’t been around for a while and I’ll tell you why.

I got a new job last summer and it’s been quite a culture shock.  First shock, huge Domino infrastructure. Second shock, even though they have Domino 8.5.3 servers, no xPages.

What! This wasn’t in the brochure.

Okay, steady now, let’s take stock and see what we do have here.  Crap, no xPages. I’ve spent the last three years learning the voodoo that is components, and repeat controls, and every other widget that’s in xPages .

But now what am I gonna do? ( pause, sniffle ), I can’t go back there, I can’t!

I refuse to use Notes Forms and pass-thru html to create web pages.

I will not return to “key-hole” programming where tiny bits of code scattered about in hundreds of hidey holes.

But you know what?  While learning xPages I also learned JAVA, and REST, and Servlets/xAgents/Headless Agents.

I learned there is a Package Explorer in the Designer that is the gateway to the new Domino.

I learned that a directory structure under Web Content that appears to a web browser as though it came from any old Apache server.

I learned that JavaScript is the Lingua Franca of the internet and if you don’t know it, you aren’t speaking the native tongue.

I learned that I don’t need xPages!  They are a crutch.

xPages stands between you and the Web. What is the ultimate aim of a xPage? To create a Web Page!

Skip the xPage and start using HTML 5 and JavaScript for your Domino applications.

There are amazing things happening in the JavaScript community. New libraries, ideas, and design patterns. And we Domino programmers can use them all!

Currently I’m using jQuery, Backbone, Underscore, Momentum, and many other small, focused JavaScript Libraries.

I wrap my JavaScript code in AMD Modules and test it them with Jasmine.

I depend Require.js to ensures everything is loaded in the proper order.

My interface is pure Bootstrap 3.

I get my Domino data as JSON from the server using a REST service that I’ve written in JAVA and call with an xAgent.

For to long I treated JavaScript as something to be written as independent functions, fragments of code to be called when needed.

I’ve learned that JavaScript is much, much, more than that.

I’m going to start blogging again, because I’ve found my cause and I’m passionate about it.

So to all of you I say, open your eyes, read, learn, and realize that Domino + JavaScript is so much more capable than you know!

– Jeff

I will be presenting “Getting Started with xPages” at DCLUG

I’m excited to announce that I’ll be speaking at this year’s 2nd meeting of the DCLUG on 24 April 2013. The meeting is from 12:00pm-1:00pm with 30 minutes before and after for networking.

I’m plan on having a nice and easy go at xPages covering design principals, basic CRUD and Views.  So if you’ve been meaning to check out xPages, but haven’t had the chance, please join us.

We’ll be at the IEG Briefing Center,  600 14th Street, NW , Washington, DC

For more information please join the meetup group where the meetings will be announced

http://www.meetup.com/DC-Lotus-Professionals

and we also have a community site on SocialBizUg.org which we will be updating with more information.

xPages Roll Your Own Domino REST Service part 2 – Sorting

In part 1 of this series we created a simple Domino REST Service which responded to page requests to a Domino view.

Now we can add additional functionality by handling sort parameters. Again, I am using the EXTJS Grid as my JSON consumer. The request with sort parameters looks like this:

4-5-2013 7-29-47 AM

In yet another previous post I demonstrated how to handle the sort parameters so there’s no need to go over it again.

Important Note:  To have server side sortable columns in a grid, those columns must be sortable, ascending and descending, in the target domino view.

This code manages the sort parameter.  For a Domino View, we can only specify a single sorted column, so we need only get the first sort parameter.


// Defaullt Sort is by Last Name
String sortCol = "lastname";
String sortDir = "ASC";

String sortParam = request.getParameter("sort");

if (StringUtil.isNotEmpty(sortParam)) {
   doSort = true;
   JsonJavaFactory factory = JsonJavaFactory.instanceEx;
   // Turn the string into an array of JSON objects
   Object sortVars = JsonParser.fromJson(factory, sortParam);
   sortCol = (String) sortVars[0].getJsonProperty("property");
   sortDir = (String) sortVars[0].getJsonProperty("direction");	   
   for (Iterator<Object> oSort = factory.iterateArrayValues(sortVars); oSort.hasNext();) {
      JsonJavaObject o = (JsonJavaObject) oSort.next();
      sortCol = (String) o.getJsonProperty("property");
      sortDir = (String) o.getJsonProperty("direction");
      // We only care about the first, and probably only, sort parameter
      break;      
   }
}

Later, after we have the view, sort the view by the desired column and direction.

boolean sort = sortDir.equals("ASC") ? true : false;
view.resortView(sortCol, sort);

Here is my updated code.

package com.dominotricks;

import static com.ibm.xsp.extlib.util.ExtLibUtil.getCurrentDatabase;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import lotus.domino.Database;
import lotus.domino.NotesException;
import lotus.domino.View;
import lotus.domino.ViewColumn;
import lotus.domino.ViewEntry;
import lotus.domino.ViewNavigator;

import com.ibm.commons.util.StringUtil;
import com.ibm.commons.util.io.json.JsonGenerator;
import com.ibm.commons.util.io.json.JsonJavaFactory;
import com.ibm.commons.util.io.json.JsonJavaObject;
import com.ibm.commons.util.io.json.JsonParser;

public class PeopleRESTsort {

    public static String doGet(HttpServletRequest request, HttpServletResponse response) {
        return doPost(request, response);
    }

    public static String doPost(HttpServletRequest request, HttpServletResponse response) {
        String strValue = "";
        Database database = null;
        View view = null;
        ViewNavigator nav = null;
        int count = 0;
        int totalCount = 0;
        try {

            // Get the paging parameters
            int start = Integer.parseInt(request.getParameter("start"));
            int limit = Integer.parseInt(request.getParameter("limit"));

            // Add Sort
            String sortParam = request.getParameter("sort");

            // Defaullt Sort is by Last Name
            String sortCol = "lastname";
            String sortDir = "ASC";

            if (StringUtil.isNotEmpty(sortParam)) {

                JsonJavaFactory factory = JsonJavaFactory.instanceEx;
                // Turn the string into an array of JSON objects
                Object sortVars = JsonParser.fromJson(factory, sortParam);

                for (Iterator < Object > oSort = factory.iterateArrayValues(sortVars); oSort.hasNext();) {
                    JsonJavaObject o = (JsonJavaObject) oSort.next();
                    sortCol = (String) o.getJsonProperty("property");
                    sortDir = (String) o.getJsonProperty("direction");
                    break;

                }
            }

            // Set the response headers
            response.setContentType("application/json");
            response.setHeader("Cache-Control", "no-cache");

            database = getCurrentDatabase();
            view = database.getView("byFirstName");

            if (view != null) {

                boolean sort = sortDir.equals("ASC") ? true : false;
                view.resortView(sortCol, sort);

                view.setAutoUpdate(false);
                // get the total number of entries in the view
                totalCount = view.getEntryCount();
                nav = view.createViewNav();
                nav.setEntryOptions(ViewNavigator.VN_ENTRYOPT_NOCOUNTDATA);
                // No need to buffer any more entries than we actually need
                nav.setBufferMaxEntries(limit);
                // try to skip  entries and return the # of entries actually skipped
                int skippedEntries = nav.skip(start);

                if (skippedEntries == start) {

                    // Get the View Column Names ( or titles )
                    Map < Integer, String > columnNameMap = new HashMap < Integer, String > ();
                    for (ViewColumn col: (List < ViewColumn > ) view.getColumns()) {
                        if (col.getColumnValuesIndex() < 65535) {
                            // We can return the Column Names or Column Items
                            // columnNameMap.put(col.getColumnValuesIndex(), col.getTitle());
                            columnNameMap.put(col.getColumnValuesIndex(), col.getItemName());
                        }
                    }

                    // This list of employee information will be turned into JSON.
                    List emplData = new ArrayList();

                    // read the current entry after the skip operation
                    ViewEntry entry = nav.getCurrent();

                    while (entry != null && count <= (limit - 1)) {
                        count++;
                        // Get a list of the column values
                        List < Object > columnValues = entry.getColumnValues();
                        // Create a map of column:value pairs
                        HashMap < String, String > entryMap = new HashMap < String, String > ();
                        entryMap.put("@UNID", entry.getUniversalID());
                        entryMap.put("@position", entry.getPosition('.'));
                        for (Integer index: columnNameMap.keySet())
                            entryMap.put(columnNameMap.get(index), columnValues.get(index).toString());

                        // Add the entry map to the data variable that was passed in
                        emplData.add(entryMap);

                        ViewEntry tmpentry = nav.getNext(entry);
                        entry.recycle();
                        entry = tmpentry;
                    } // while

                    // Create a JSON object to wrap the employee Json array and provide the root element items
                    JsonJavaObject returnJSON = new JsonJavaObject();
                    // set success to true
                    returnJSON.put("success", true);
                    // set the total number of Items
                    returnJSON.put("total", totalCount);
                    // set the data element to the employee JSON list.
                    returnJSON.put("data", emplData);
                    // Return a JSON string generated from our JsonJavaObject
                    strValue = JsonGenerator.toJson(JsonJavaFactory.instanceEx, returnJSON);

                }

                view.recycle();
                nav.recycle();
            }

            database = null;

        } catch (NotesException e) {
            strValue = "{\"success\":false,\"message\":\"" + e.getMessage() + "\"}";
            e.printStackTrace();
        } catch (Exception e) {
            strValue = "{\"success\":false,\"message\":\"" + e.getMessage() + "\"}";
            e.printStackTrace();
        }
        return strValue;
    }

}

Adding the sort really stumped me for awhile. I knew it could be done, and, as always, it’s simple once you know how. :-)

I never cease to be amazed at the power Domino gives us and I’m always trying to figure out better ways to use it.  Learning JAVA is a huge step to getting more out of xPages.

Happy coding,

– Jeff

xPages Roll Your Own Domino REST Service part 1 – Paging

In this article I want to show a way to create your own domino REST Service to access a database view in your xPage application.

This project may not be technically RESTful, but I’ll try to implement a few REST-like concepts starting with GET and POST.

I’ve been intrigued by Marky Roden’s series at xomino.com on the EXTJS Grid. Dr. Marky’s excellent articles will guide you in setting up and using the grid. No need to go over that again. Marky is using the xPages Extension Library REST service with his grids, which is fantastic, but I thought it would it wouldn’t be that hard to write my own simple REST service in JAVA.

Why, you ask?  <queue evil laughter>, Control! Complete and total control. I want my REST service to understand the parameters provided by the EXTJS Grird, or, the Dojo Grid, or the jQuery Grid. I want it to format the JSON output for the grid without Me having to jump through hoops or preform magic with session variables.  Besides, all of the ExtLib REST controls are rendered down to JAVA anyway, so I’ll just skip the middle man and go straight to JAVA.

My project here is to use an xAgent as my REST service.  The xAgent actually does nothing more than act as a JAVA servlet and pass HTTPRequest and HTTPResponse objects to my JAVA class.  My JAVA Class is responsible for actually handling the request.

Here is the xAgent, what there is of it.  It took ages to realize that I could just pass the HTTPRequest and HTTPResponse objects to my class.  Previously, I was doing all the work of getting the URL parameters and passing them to the class.  It’s much easier this way.

 

What request parameters sent to the xAgent? There can be different parameters, depending on what grid we are using.  The EXTJS Grid sends start and limit to handle paging.

3-19-2013 7-47-32 AM

The PeopleREST class has to read the request parameters, open the view, skip to the starting row, save the column values to a map, and, return the JSON string.

package com.dominotricks;

import static com.ibm.xsp.extlib.util.ExtLibUtil.getCurrentDatabase;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import lotus.domino.Database;
import lotus.domino.NotesException;
import lotus.domino.View;
import lotus.domino.ViewColumn;
import lotus.domino.ViewEntry;
import lotus.domino.ViewNavigator;

import com.ibm.commons.util.io.json.JsonGenerator;
import com.ibm.commons.util.io.json.JsonJavaFactory;
import com.ibm.commons.util.io.json.JsonJavaObject;

public class PeopleREST {

	public static String doGet(HttpServletRequest request, HttpServletResponse response) {
		return doPost(request, response);
	}

	public static String doPost(HttpServletRequest request, HttpServletResponse response) {
		String strValue = "";
		Database database = null;
		View view = null;
		ViewNavigator nav = null;
		int count = 0;
		int totalCount = 0;
		try {

			// Get the paging parameters
			int start = Integer.parseInt(request.getParameter("start"));
			int limit = Integer.parseInt(request.getParameter("limit"));

			// Set the response headers
			response.setContentType("application/json");
			response.setHeader("Cache-Control", "no-cache");

			database = getCurrentDatabase();
			view = database.getView("People");

			if (view != null) {
				view.setAutoUpdate(false);
				// get the total number of entries in the view
				totalCount = view.getEntryCount();
				nav = view.createViewNav();
				nav.setEntryOptions(ViewNavigator.VN_ENTRYOPT_NOCOUNTDATA);
				// No need to buffer any more entries than we actually need
				nav.setBufferMaxEntries(limit);
				// try to skip <start> entries and return the # of entries actually skipped
				int skippedEntries = nav.skip(start);

				if (skippedEntries == start) {

					// Get the View Column Names ( or titles )
					Map<Integer, String> columnNameMap = new HashMap<Integer, String>();
					for (ViewColumn col : (List<ViewColumn>) view.getColumns()) {
						if (col.getColumnValuesIndex() < 65535) {
							// We can return the Column Names or Column Items
							// columnNameMap.put(col.getColumnValuesIndex(), col.getTitle());
							columnNameMap.put(col.getColumnValuesIndex(), col.getItemName());
						}
					}

					// This list of employee information will be turned into JSON.
					List emplData = new ArrayList();

					// read the current entry after the skip operation
					ViewEntry entry = nav.getCurrent();

					while (entry != null && count <= (limit - 1)) {
						count++;
						// Get a list of the column values
						List<Object> columnValues = entry.getColumnValues();
						// Create a map of column:value pairs
						HashMap<String, String> entryMap = new HashMap<String, String>();
						entryMap.put("@UNID", entry.getUniversalID());
						entryMap.put("@position", entry.getPosition('.'));
						for (Integer index : columnNameMap.keySet())
							entryMap.put(columnNameMap.get(index), columnValues.get(index).toString());

						// Add the entry map to the data variable that was passed in
						emplData.add(entryMap);

						ViewEntry tmpentry = nav.getNext(entry);
						entry.recycle();
						entry = tmpentry;
					} // while

					// Create a JSON object to wrap the employee Json array and provide the root element items
					JsonJavaObject returnJSON = new JsonJavaObject();
					// set success to true
					returnJSON.put("success", true);
					// set the total number of Items
					returnJSON.put("total", totalCount);
					// set the data element to the employee JSON list.
					returnJSON.put("data", emplData);
					// Return a JSON string generated from our JsonJavaObject
					strValue = JsonGenerator.toJson(JsonJavaFactory.instanceEx, returnJSON);

				}

				view.recycle();
				nav.recycle();
			}

			database = null;

		} catch (NotesException e) {
			strValue = "{\"success\":false,\"message\":\"" + e.getMessage() + "\"}";
			e.printStackTrace();
		} catch (Exception e) {
			strValue = "{\"success\":false,\"message\":\"" + e.getMessage() + "\"}";
			e.printStackTrace();
		}
		return strValue;
	}

}

That’s it. Not to much scary JAVA code. There is a HUGE speed improvement by using the ViewNavigator.skip() method. Karsten Lehmann blogged about it here.

 

Give it a try on Jake’s 40,000 record Fake Names database. It’s quite speedy. This URL request:

  ./aPeopleJSON.xsp?start=2000&limit=3  

Returned this JSON

{
    "success": true,
    "total": 40000,
    "data": [
        {
            "@UNID": "0BADF6C37ACD570580257A0A003AC06F",
            "@position": "1",
            "InternetAddress": "Zoe.K.Barry@mytrashmail.com",
            "$23": "mytrashmail.com",
            "$24": "01/02/2007 01:42:19 PM EST",
            "OfficeCountry": "GB",
            "$17": "Barry, Zoe"
        },
        {
            "@UNID": "FAFA753960DB587A80257258007287CF",
            "@position": "2",
            "InternetAddress": "retineo.horum@cavi.org",
            "$23": "cavi.org",
            "$24": "01/03/2007 03:52:03 PM EST",
            "OfficeCountry": "Bulgaria",
            "$17": "Barr, Carson"
        },
        {
            "@UNID": "9D93E80306A7AA88802572580072717A",
            "@position": "3",
            "InternetAddress": "suppellectilus@invetero.net",
            "$23": "invetero.net",
            "$24": "01/03/2007 03:51:14 PM EST",
            "OfficeCountry": "Grenada",
            "$17": "Barr, Cary"
        }
    ]
}

Next up is Part #2 – Column Sorting.

Happy Coding.

– Jeff

xPages JSON with IBM Commons

On a current effort I find myself consuming and creating more and more JSON. There are many excellent JSON libraries out there, however, within xPages we already have access to com.ibm.commons.util.io.json.

Here’s an example of how I use it.

Let’s say you’ve created a custom REST service to use with the EXTJS Grid. A parameter you might receive is the sort parameter. In this case, Sort first by Organization Id and then Employee Id. The parameter will look like this:

[{"property":"ORG_ID","direction":"ASC"},{"property":"EMPL_ID","direction":"ASC"}]

We can turn the sort parameter string  into some useful JSON like this:

// Create the JAVA json factory
JsonJavaFactory factory = JsonJavaFactory.instanceEx;
// Turn the sort parameter string into an array of JSON objects
Object sortVars = JsonParser.fromJson(factory, sortParam);
// Loop through the JSON objects and do something
for (Iterator<object> oSort = factory.iterateArrayValues(sortVars); oSort.hasNext();) {</object>
     JsonJavaObject o = (JsonJavaObject) oSort.next();
     String sortCol = (String) o.getJsonProperty("property");
     String sortDir = (String) o.getJsonProperty("direction");
     System.out.print( "Sort " + sortCol + " in direction " + sortDir );
}

After gathering and sorting the employee data, we have to return it as a JSON string.

Assume our data is in a list of Employee Beans, (employees). We have to return that employee list as a JSON string. Oh, and we also have to return the total count.

Here is what that might look like.

// This list of employee information will be turned into JSON.
List emplJSON = new ArrayList();
int count = 0; // Track the count

for (int i = 0; i < employees.size(); i++) {
     // Get an employee data bean from the employee list
     EmplData bean = employees.get(i);
     // Create a 'property : value' list of the employee properties we want to return as JSON.
     HashMap<string, object=""> emplObj = new HashMap<string, object="">();
     emplObj.put("EMPL_ID", bean.getEMPL_ID());
     emplObj.put("FULL_NAME", bean.getFULL_NAME());
     emplObj.put("TITLE", bean.getTITLE_DESC());
     emplObj.put("ORG_ID", bean.getORG_ID());
     emplObj.put("WORK_PHONE", bean.getWORK_PHONE());
     emplObj.put("CELL_PHONE", bean.getMOBIL_PHONE());
     emplObj.put("TAXBLE_ENTITY_ID", bean.getTAXBLE_ENTITY_ID());
     emplObj.put("EMAIL", bean.getEMAIL_ID());
     // Add the employee information to our list of employee JSON data
     emplJSON.add(emplObj);
     // Keep track of the count
     count ++;
}

// Create a JSON object to wrap the employee Json array and provide the root element items
JsonJavaObject returnJSON = new JsonJavaObject();
// set success to true
returnJSON.put("success", true);
// set the total number of Items
returnJSON.put("total", count);
// set the data element to the employee JSON list.
returnJSON.put("data", emplJSON );
//  Return a JSON string generated from our JsonJavaObject
return JsonGenerator.toJson(JsonJavaFactory.instanceEx, returnJSON);

The output JSON looks like this:

 {
    "success": true,
    "total": 1000,
    "data": [
    {
       "WORK_PHONE": "555-211-5855",
       "CELL_PHONE": "555-211-5855",
       "FULL_NAME": "Parker, Peter",
       "ORG_ID": "01.000.002",
       "EMPL_ID": "00001",
       "TAXBLE_ENTITY_ID": "BUGLE",
       "TITLE": "Photographer",
       "EMAIL": "peterparker@dailybulge.com"
    },{
       "WORK_PHONE": "555-211-5775",
       "CELL_PHONE": "555-211-5775",
       "FULL_NAME": "Jamison, James J ",
       "ORG_ID": "01.000.003",
       "EMPL_ID": "00003",
       "TAXBLE_ENTITY_ID": "BUGLE",
       "TITLE": "Sr Editor",
       "EMAIL": "jamesjjamison@dailybulge.com"
    }
  ]
}

So stop hand coding your JSON! At least if your using JAVA.

More on that custom REST service later.

Happy coding.

– Jeff Byrd

Note:  Below is a link where I found helpful information. I’m sure there is a lot more out there.

http://guedebyte.wordpress.com/2012/09/11/json-arrray-parsing-with-com-ibm-commons-util-io-json-jsonjavafactory/

Turn your JAVA Beans into a POI Excel Spreadsheet

In my last article I demonstrated how to use the Apache Commons DbUtils to turn a JDBC result set into a list of JAVA beans.

In this article I’ll to show you how to take a list of JAVA beans and generate an Excel spreadsheet using the Apache Commons POI library, with help from the Apache Commons BeanUtils library.

I’m not the first to write about using POI to export xPages data. Paul Calhoun posted an excellent video on David Leedy’s Notes-in-Nine site, (episode #98), and I’d like to think that this article builds on that.

I started out using a JDBC result set as a data source to create the spreadsheet using POI. That code was put together from various sources on the web.

For various reasons, ahem, memory leaks, I wanted to use a list of Beans as my spreadsheet data source. It looked pretty straight forward. Loop through a list of beans, call the bean properties for each report column, format the column values, and write the formatted value to the spreadsheet cell. Easy enough, if you know beforehand all about the bean’s properties. But what if you don’t know anything about the report’s data bean?

Once again the Apache Comons to the rescue with BeanUtils.

As xPage developers we use beans. I think BeanUtils will be essential if you write any utility Java Classes around beans. For the Bean2Excel report class, all I really needed was the BeanUtils PropertyUtils.getProperty().

In the example below, the spreadsheet data is a list of Employee beans. The “Name” column value is the Employee bean’s name property.

Using standard Java Bean syntax, I’d write String name = employee.name, but at run time I don’t know the property name!

With PropertyUtils I can write String name = PropertyUtils.getProperty( employee, “name”). Fantastic! Reminds me of the old LotusScript doc.getItemValue()(0).

So now I can call a bean property at runtime, how to configure a report column? My solution is to create a ReportColumn class. Each report column object defines the column header, the column value property name of the report data bean, and the column value’s format. Optionally, you also set the column font and background color.

In my example, the report data is in the Employee Class, the ReportColumn Class defines the spreadsheet columns, and the Bean2Excel class creates the spreadsheet. The example writes the spreadsheet to a file on disk, but could easily be called from an xPage xAgent and streamed to a browser.

Employee Class:

package com.demo.excel;
import java.util.Date;
public class Employee {
	private String m_name;
	private Integer m_id;
	private Date m_hireDate;
	private Double m_salary;

	public Employee() {}

	public Employee(Integer id, String name, Date hiredate, Double salary) {
		this.m_id = id;
		this.m_name = name;
		this.m_hireDate = hiredate;
		this.m_salary = salary;
	}
	public String getName() {
		return m_name;
	}
	public void setName(String name) {
		this.m_name = name;
	}
	public Integer getId() {
		return m_id;
	}
	public void setId(Integer id) {
		this.m_id = id;
	}
	public Date getHireDate() {
		return m_hireDate;
	}
	public void setHireDate(Date hireDate) {
		this.m_hireDate = hireDate;
	}
	public Double getSalary() {
		return m_salary;
	}
	public void setSalary(Double salary) {
		this.m_salary = salary;
	}
}

ReportColumn Class:

package com.demo.excel;
import org.apache.poi.hssf.usermodel.HSSFFont;
import com.demo.excel.Bean2Excel.FormatType;

public class ReportColumn {
	private String m_method;
	private String m_header;
	private FormatType m_type;
	private HSSFFont m_font;
	private Short m_color;

	public ReportColumn(String method, String header, FormatType type,
			HSSFFont font, Short color) {
		this.m_method = method;
		this.m_header = header;
		this.m_type = type;
		this.m_font = font;
		this.m_color = color;
	}
	public ReportColumn(String method, String header, FormatType type,
			HSSFFont font) {
		this(method, header, type, font, null);
	}
	public ReportColumn(String method, String header, FormatType type,
			Short color) {
		this(method, header, type, null, color);
	}

	public ReportColumn(String method, String header, FormatType type) {
		this(method, header, type, null, null);
	}

	public String getMethod() {
		return m_method;
	}

	public void setMethod(String method) {
		this.m_method = method;
	}

	public String getHeader() {
		return m_header;
	}

	public void setHeader(String header) {
		this.m_header = header;
	}

	public FormatType getType() {
		return m_type;
	}

	public void setType(FormatType type) {
		this.m_type = type;
	}

	public HSSFFont getFont() {
		return m_font;
	}

	public void setFont(HSSFFont m_font) {
		this.m_font = m_font;
	}

	public Short getColor() {
		return m_color;
	}

	public void setColor(Short m_color) {
		this.m_color = m_color;
	}
}

Bean2Excel Class:

package com.demo.excel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFCellUtil;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellUtil;

public class Bean2Excel {
	private HSSFWorkbook workbook;
	private HSSFFont boldFont;
	private HSSFDataFormat format;

	public static void main(String[] args) {

		// Used to create the hire dates
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy, MM, dd");

		try {
			// Create the report object
			Bean2Excel oReport = new Bean2Excel();

			// Create a list of employee data
			List employees = new ArrayList();
			employees.add(new Employee(100, "Abe Adams", sdf
					.parse("2009, 12, 1"), 10000.00));
			employees.add(new Employee(101, "Betty Barnes", sdf
					.parse("2010, 11, 1"), 11000.00));
			employees.add(new Employee(102, "Caleb Crown", sdf
					.parse("2011, 10, 1"), 12000.00));
			employees.add(new Employee(103, "Dirk Daniels", sdf
					.parse("2012, 09, 1"), 13000.00));

			// Create an array of report column objects
			ReportColumn[] reportColumns = new ReportColumn[] {
					new ReportColumn("id", "Id", FormatType.INTEGER),
					new ReportColumn("name", "Last Name", FormatType.TEXT),
					new ReportColumn("hireDate", "Hire Date", FormatType.DATE),
					new ReportColumn("salary", "Salary", FormatType.MONEY) };

			// Create a worksheet with our employee data and report columns
			oReport.addSheet(employees, reportColumns, "sheet1");

			// Set the Hire Date Column text to Bold and background to Green
			reportColumns[2].setColor(HSSFColor.GREEN.index);

			// Add a 2nd sheet with the same data.
			oReport.addSheet(employees, reportColumns, "sheet2");

			// Create an output stream to write the report to.
			OutputStream output = new FileOutputStream("c:\\temp\\report1.xls");

			// Write the report to the output stream
			oReport.write(output);

			// Finally, save the report
			output.close();

		} catch (ParseException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public Bean2Excel() {
		workbook = new HSSFWorkbook();
		boldFont = workbook.createFont();
		boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		format = workbook.createDataFormat();
	}

	public void addSheet(List<?> data, ReportColumn[] columns, String sheetName) {

		HSSFSheet sheet = workbook.createSheet(sheetName);
		int numCols = columns.length;
		int currentRow = 0;
		HSSFRow row;

		try {

			// Create the report header at row 0
			row = sheet.createRow(currentRow);
			// Loop over all the column beans and populate the report headers
			for (int i = 0; i < numCols; i++) {
				// Get the header text from the bean and write it to the cell
				writeCell(row, i, columns[i].getHeader(), FormatType.TEXT,
						null, this.boldFont);
			}

			currentRow++; // increment the spreadsheet row before we step into
							// the data

			// Write report rows
			for (int i = 0; i < data.size(); i++) {
				// create a row in the spreadsheet
				row = sheet.createRow(currentRow++);
				// get the bean for the current row
				Object bean = data.get(i);
				// For each column object, create a column on the current row
				for (int y = 0; y < numCols; y++) {
					Object value = PropertyUtils.getProperty(bean,
							columns[y].getMethod());
					writeCell(row, y, value, columns[y].getType(),
							columns[y].getColor(), columns[y].getFont());
				}
			}

			// Autosize columns
			for (int i = 0; i < numCols; i++) {
				sheet.autoSizeColumn((short) i);
			}

		} catch (Exception e) {
			System.err.println("Caught Generate Error exception: "
					+ e.getMessage());
		}

	}

	public HSSFFont boldFont() {
		return boldFont;
	}

	public void write(OutputStream outputStream) throws Exception {
		workbook.write(outputStream);
	}

	private void writeCell(HSSFRow row, int col, Object value,
			FormatType formatType, Short bgColor, HSSFFont font)
			throws Exception {

		HSSFCell cell = HSSFCellUtil.createCell(row, col, null);

		if (value == null) {
			return;
		}

		if (font != null) {
			HSSFCellStyle style = workbook.createCellStyle();
			style.setFont(font);
			cell.setCellStyle(style);
		}

		switch (formatType) {
		case TEXT:
			cell.setCellValue(value.toString());
			break;
		case INTEGER:
			cell.setCellValue(((Number) value).intValue());
			HSSFCellUtil.setCellStyleProperty(cell, workbook,
					CellUtil.DATA_FORMAT,
					HSSFDataFormat.getBuiltinFormat(("#,##0")));
			break;
		case FLOAT:
			cell.setCellValue(((Number) value).doubleValue());
			HSSFCellUtil.setCellStyleProperty(cell, workbook,
					CellUtil.DATA_FORMAT,
					HSSFDataFormat.getBuiltinFormat(("#,##0.00")));
			break;
		case DATE:
			cell.setCellValue((Date) value);
			HSSFCellUtil.setCellStyleProperty(cell, workbook,
					CellUtil.DATA_FORMAT,
					HSSFDataFormat.getBuiltinFormat(("m/d/yy")));
			break;
		case MONEY:
			cell.setCellValue(((Number) value).intValue());
			HSSFCellUtil.setCellStyleProperty(cell, workbook,
					CellUtil.DATA_FORMAT,
					format.getFormat("$#,##0.00;$#,##0.00"));
			break;
		case PERCENTAGE:
			cell.setCellValue(((Number) value).doubleValue());
			HSSFCellUtil.setCellStyleProperty(cell, workbook,
					CellUtil.DATA_FORMAT,
					HSSFDataFormat.getBuiltinFormat("0.00%"));
		}

		if (bgColor != null) {
			HSSFCellUtil.setCellStyleProperty(cell, workbook,
					CellUtil.FILL_FOREGROUND_COLOR, bgColor);
			HSSFCellUtil.setCellStyleProperty(cell, workbook,
					CellUtil.FILL_PATTERN, HSSFCellStyle.SOLID_FOREGROUND);
		}

	}

	public enum FormatType {
		TEXT, INTEGER, FLOAT, DATE, MONEY, PERCENTAGE
	}
}

Here are the results:sheet1

I hope you will find this useful.

Happy Coding!

–Jeff

Using Apache Commons DbUtils to improve your XPages JDBC connections

Like many xPagers, the more complex work I do in xPages, the more I use JAVA.

The more I use JAVA, the more I have to careful to clean up after myself, else the Domino server will die a tragic death from lack of memory.

With a good chunk of our corporate date residing in an ORACLE database, I find that I’m using Java JDBC more and more to get at that data.

It was while using JDBC that I first brought down the server.

Here is a traditional JDBC pattern for opening a connection, creating and executing a query , then processing the result set.

Connection conn = null;
String jdbcURL = "jdbc:oracle:thin:@DEVCOPY:1521";
String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
String user = "admin";
String password = "p@$$word";
String query = "SELECT EMPL_ID,LAST_NAME, FIRST_NAME FROM EMPL";
try {
   Class.forName(jdbcDriver);
   Connection con = DriverManager.getConnection(jdbcURL, user, password);
   try {
      Statement st = conn.createStatement();
      try {
         ResultSet resultSet = st.executeQuery(query);
         try {
            while (resultSet.next()) {
               // Do something with the result set row
            }
         } finally {
            resultSet.close();
         }
      } finally {
         st.close();
      }
   } finally {
     conn.close()
   }
} catch (SQLException e) {
   e.printStackTrace();
} catch (Exception e) {
   e.printStackTrace();
}

That’s a large chunk of code. If I  forget any part of that pattern memory leaks will abound.

Then I discovered the Apache Commons DbUtils. (commons.apache.org/dbutils)

And here is what my JDBC pattern looks like now.

Connection conn = null;
String jdbcURL = "jdbc:oracle:thin:@DEVCOPY:1521";
String jdbcDriver = "oracle.jdbc.driver.OracleDriver";
String user = "admin";
String password = "p@$$word";
String query = "SELECT EMPL_ID,LAST_NAME, FIRST_NAME FROM EMPL";
try {
   DbUtils.loadDriver(jdbcDriver);
   conn = DriverManager.getConnection(jdbcURL, user, password);
   BeanListHandler handler = new BeanListHandler(Employee.class)
   QueryRunner qRunner = new QueryRunner();
   List employees = (List) qRunner.query(conn, query,handler);
   for (int i = 0; i < employees.size(); i++) {
      Employee bean = (Employee) employees.get(i);
      bean.print();
   }
} catch (SQLException e) {
   e.printStackTrace();
} finally {
   DbUtils.closeQuietly(conn);
}

class Employee {
   private int m_id;
   private String m_lastname;
   private String m_firstname;

   public Employee() {}

   public void setLAST_NAME(String lastname) {
      this.m_lastname = lastname;
   }
   public String getLAST_NAME() {
      return this.m_lastname;
   }
   public void setFIRST_NAME(String firstname) {
      this.m_firstname = firstname;
   }
   public String getFIRST_NAME() {
      return this.m_firstname;
   }
   public void setEMPL_ID(int id) {
     this.m_id = id;
   }
   public int geEMPL_ID() {
      return this.m_id;
   }
   public void print() {
      System.out.println("id="+this.m_id+" name="+
      this.m_lastname + ", " + this.m_firstname);
   }
}

Much cleaner!  And the result is a list of beans. (I like Beans!)

So what is happening here? The query runner is taking the connection, the query, and the handler that I’ve specified, and returning me a list of Employee beans.

This happens because I’ve created the Employee class with properties that match the table columns in my query. I’ve also ensured that each Employee class property type matches the table column type.

I don’t have to use all of the columns from my select statement. Any table column that doesn’t have a corresponding class property will be ignored.

There are lots of other query handlers, that return different objects, that you can use.

Did I mention that I can also easily use prepared statements.

I’ve added a parameter to the query string and created an array to hold my search value, in this case the employee Id. If I had more than one parameter, I’d add another element to the array. Now I only have to add my parameter array to the query runner.

String query = "SELECT EMPL_ID,LAST_NAME, FIRST_NAME FROM EMPL WHERE EMPL_ID=?";
Object[] params = new Object[]{30082};
BeanListHandler handler = new BeanListHandler(Employee.class)
List employees = (List) qRunner.query(conn, query, params, handler );

When all is said and done, your database connections are all properly closed and you end up with a list of data objects that can be held in a session scope or passed to another process.

I’ve only scratched the surface of Apache Commons DbUtils. There a lot more that can happen with the different types of Query Runners, so go have a look and see what you can come up with.

Happy Coding!

– Jeff