Sharing data between MySQL and Lotus Domino

I’ve been working on a demo database to show some of the things you can do with XPages that you can’t do with traditional Domino development techniques. So I figured it would be cool to show Domino data right along side data from a MySQL DB. This actually wasn’t as difficult as I thought it might be and it was a fun exercise and produced some code that may be useful at a later date. But I’ll briefly go over the technique here and also make the xpages app and MySQL db available for download so you can examine it yourself and just follow along with my write up. I’m doing this post this way because I’m afraid I would run out of room (32K limit) for this post.

The requirements for this application is that it should look nice and use a variant of the OneUI (as I suck at user interface design). The application should also intermingle data that will be stored in the NSF and data stored in MySQL. Data from both places should be available in a “view” of some sort and we should have simple CRUD (create, read, update, delete) functionality.

With these requirements in hand I came up with a form which has 3 fields. This is where our domino data will be stored. Of course there is a view with the 3 fields and an additional column that shows the UNID of each document. This UNID is what we’ll be using to get, insert and update MySQL records. For the MySQL table we’ll also need 3 fields (they’re called columns in MySQL), an “id” column which is our primary key and a unid column. In a production environment you would probably want the help of a real MySQL developer to make the primary key the unid column. You would also probably want this type of person as they are more familiar with the different data types and what the sizes for these columns actually mean that may or may not increase performance and the size of the database. I can get around a little bit with MySQL but it’s really not my forte.

For the user interface I used the XPages Framework from OpenNTF. This is a great piece of work by Steve Castledine and Niklas Heidloff and makes for a quick UI that looks really nice. Just fill out a configuration file, set a few properties and you’re ready to go. I created 2 custom controls and corresponding xpages, one for the form and one for the view. The one for the form contains 6 fields, 3 from the domino form and 3 for the MySQL data. Since I will be using a managed bean to manage the data transfer back and forth I only set a datasource to make it easy to drag/drop the fields from the data palette onto a form. Once that’s done I remove the document data source as it won’t be needed and can actually cause some issues when using a managed bean to manage all the data.

I would like to thank Karsten Lehmann for his excellent XPages series and specifically #11 in that series. In the demo database provided for that series he shows how to use a backing bean to manage and log data changes for an XPages application. The technique he uses makes it possible to have this one bean as the backing bean for all the forms in an XPages application, which is excellent. You’ll have to modify it a bit to handle all the different data types that you may encounter in a full blown production application, but the technique is solid and works exceptionally well. This bean is what we’ll be using to handle all the data from Domino and MySQL.

Before we go much farther you’ll need to download the MySQL JDBC driver and add it to the BuildPath of your application. You’ll also need to place this .jar file in the WEB-INF\lib folder. This makes it where the Domino server can find it.
MySQL-JDBC-DriverPlacement.png

Hopefully you’ve stuck with me here as this will be getting a little more meaty here soon. I’m trying not to be too detailed on the little stufff. But using Karsten’s technique, you’ll need to define 2 managed beans. One for the DataProvider class (data) and one for the PageActionBean class (pageAction). I’m not 100% sure of why Karsten done it this way, but if feels that it leaves a lot of room for expansion and probably the PageActionBean is where you would define the save method for many different data backing beans that handle different data sources (i.e. Domino, MySQL, etc.). Now, on our custom control, we’ll change the value binding for the 6 fields to be an EL Expresssion of “data.FieldName”. Well add 2 buttons to the custom control, one labelled save with an onclick event of “pageAction[‘save’].execute(‘formname’)”. This will actually call the save(String) method of our DataProvider class. The other button with a onclick value of “data.deleteRecord()”.
ccFormMyForm - Custom Control - IBM Lotus Domino Designer_2011-06-06_22-36-08.png

Events - IBM Lotus Domino Designer_2011-06-06_22-35-47.png

Onto our DataProvider class, aka data bean. This class has a few methods that I would like to draw your attention to. The first is getValue(Object itemName). This method will actually be called by the JSF engine itself to get the value of all the fields. Also, there is the setValue(Object itemName, Object itemValue) method. This method too will be called by the JSF engine to set the value of a field. For our purposes setValue actually just adds the changed value to a HashMap (think array here) called changedValues. For reference, here are those 2 methods:

public Object getValue(Object itemName) {
	String idStr = itemName.toString();
	Object itemValue = null;
	if (changedValues.containsKey(idStr)) {
		return changedValues.get(idStr);
	} else if (cachedValues.containsKey(idStr)) {
		return cachedValues.get(idStr);
	} else {
		try {
			if (getDocument() == null || getDocUNID().equals("")) {
				return null;
			} else {
				if (itemName.toString().startsWith("My")) {
					String unid = docUNID;
					if (unid == null) {
						return itemValue;
					} else {
						try {
							MySQLUtil mySql = new MySQLUtil("mysql", "localhost", "3306", "lotusmysqldemo", "root", "secret");
							ResultSet rs = mySql.executeQuery("SELECT " + itemName.toString() + " FROM data WHERE unid='" + unid + "'");
							if (rs.next()) {
								itemValue = rs.getString(itemName.toString());
							} else {
								itemValue = "";
							}
							mySql.closeAll();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}
					cachedValues.put(itemName.toString(), itemValue);
				} else {
					Document doc = getDocument();
					Item notesItem = doc.getFirstItem(itemName.toString());
					if (notesItem != null) {
						if (!notesItem.getValues().isEmpty()) {
							itemValue = notesItem.getValueString();
						} else {
							itemValue = "";
						}
						cachedValues.put(doc.getFirstItem(itemName.toString()).getName(), itemValue);
					}
				}
				return itemValue;
			}
		} catch (NotesException e) {
			e.printStackTrace();
		} catch (NullPointerException e) {
			return null;
		}
	}
	return null;
}

public void setValue(Object itemName, Object itemValue) {
	Object oldValue = getValue(itemName);
	Boolean valueChanged = false;
	if ((oldValue != null && !itemValue.equals(oldValue)) || (itemValue != null && oldValue == null)) {
		valueChanged = true;
	}
	if (valueChanged) {
		if (itemValue == null || itemValue.equals("")) {
			changedValues.put(itemName.toString(), null);
		} else {
			changedValues.put(itemName.toString(), itemValue);
		}
	}
}

In the getValue method above, about half way down, you’ll see the try/catch block which get’s our value from MySQL. Also notice I’m checking if the itemName starts with “My”. If we split this up into 2 separate beans, this check wouldn’t be necessary. But for something simple, it works.

Now, the next method (which I’m not going to post here because it doesn’t really contain anything relevant to moving data to/from MySQL is the save(String formName) method. This is the method called when the Save button on the form is clicked. This method loops through all the values stored in the changedValues hash map and updates the document/mysql table accordingly. As it loops through each item in the changedValues hashmap it calls another method called writeItemValue(Document doc, String itemName, Object itemValue). This method checks if the itemName starts with “My” if it does, then we update an existing record or insert a new record if none exists for the unid of the passed document. The writeItemValue method is shown below:

private void writeItemValue(Document doc, String itemName, Object itemValue) {
	try {
		if (itemName.startsWith("My")) {
			MySQLUtil mySql = new MySQLUtil("mysql", "localhost", "3306", "lotusmysqldemo", "root", "secret");
			ResultSet rs = mySql.executeQuery("SELECT * FROM data WHERE unid='" + docUNID + "'");
			if (itemValue == null) {
				itemValue = "";
			}
			if (rs == null || !rs.next()) {
				rs = mySql.executeQuery("INSERT INTO data (" + itemName + ",unid) VALUES ('" + itemValue.toString() + "','" + docUNID
						+ "')");
			} else {
				rs = mySql.executeQuery("UPDATE data SET " + itemName + "='" + itemValue + "' WHERE unid='" + docUNID + "'");
			}
			updateCachedValue(itemName, itemValue);
			mySql.closeAll();
		} else {
			doc.replaceItemValue(itemName, itemValue);
			updateCachedValue(itemName, itemValue);
		}
	} catch (NotesException e) {
		e.printStackTrace();
	} catch (Exception e) {
		e.printStackTrace();
	}
}

Now, lastly I want to draw your attention to these last 2 methods, deleteRecord() and getMySqlValue(String unid, String itemName). The deleteRecord does exactly what it says it will and the getMySqlValue method is actually used to display the records for a repeat control. Again, I’m not going to go into specifics about the repeat control, I assume you know all about them, if you haven’t had the chance to play with them yet, check out the demo download. But this repeat control repeats a row layout for a table. It has a data source defined for the view that was created earlier. For the last 3 columns where we’re going to show our data from MySQL we use the getMySqlValue method to get that data.

public String getSqlValue(String unid, String itemName) {
	String itemValue = null;
	try {
		MySQLUtil mySql = new MySQLUtil("mysql", "localhost", "3306", "lotusmysqldemo", "root", "secret");
		ResultSet rs = mySql.executeQuery("SELECT " + itemName.toString() + " FROM data WHERE unid='" + unid + "'");
		if (rs.next()) {
			itemValue = rs.getString(itemName.toString());
		} else {
			itemValue = "";
		}
		mySql.closeAll();
	} catch (SQLException e) {
		e.printStackTrace();
	}
	return itemValue;
}

public void deleteRecord() throws NotesException {
	String unid = docUNID;
	Document thisDoc = getDocument();
	thisDoc.remove(true);
	MySQLUtil mySql = new MySQLUtil("mysql", "localhost", "3306", "lotusmysqldemo", "root", "secret");
	ResultSet rs = mySql.executeQuery("DELETE FROM data WHERE unid='" + unid + "'");
	mySql.closeAll();
}

This has been a very brief overview of a technique to combine Lotus Domino data with data from MySQL. I apologize for being so brief but time and space is at a premium (at the moment). Download the demo database and look at the DataProvider class. I think it’s pretty straight forward and is yet another example of “If I can learn to do this with Java, so can you”. You just have to jump in and start learning it. Oh yeah, here’s the demo database download.

Mozilla Firefox_2011-06-06_23-29-25.png

Share This: