Just as companies may have applications based on different technologies, it is equally common for data to be stored in different repositories. You can easily direct XPage elements to surface data from different Notes applications. It turns out not to be much harder to surface data from different back end data sources with XPages.
The key to integrating different data technologies is by binding them together with Javascript. Almost any XPage control that surfaces data has the option to surface that data from a Javascript source. So any data repository you can get to from Javascript can be used by XPages.
Relational databases (RDB) are a very widely used technology for storing data. Many relational databases are accessible through Java Database Connectivity (JDBC). And this Java interface can be called from Javascript. This is our key to surfacing RDB data in an XPage. For our example here we will show connectivity to the MySQL relational database. For an example showing connectivity to DB2, see John Mackey's blog here.
We're going to break this task down into four parts. First is creating a Javascript library, second is creating a Repeat based view of our data, third is creating a form to view a single record, and finally is using that form to update the data. This shows how to connect to a RDB from first principles as an illustration in data integration. We call out below where you might improve this as part of a production system.
RDB Javascript Library
The first step we will take for this is to create a basic Javascript library Since we will be initializing our connection to JDBC several times, it makes sense to group the common functionality into a library.
(1) | function getStatement() { |
| var driverClass:java.lang.Class; |
| var driver:java.sql.Driver; |
| var connection:java.sql.Connection; |
| var statement:java.sql.Statement; |
(2) | driverClass = java.lang.Thread.currentThread().getContextClassLoader().loadClass("org.gjt.mm.mysql.Driver"); |
| driver = driverClass.newInstance(); |
| java.sql.DriverManager.registerDriver(driver); |
(3) | connection = java.sql.DriverManager.getConnection("jdbc:mysql://dataserver.mycompany.com/tours?user=datauser&password=datapw"); |
(4) | statement = connection.createStatement(); |
| return statement; |
| } |
The first function (1) in the library returns a JDBC statement. (Mostly this is used by the other two functions.) In order to indicate to the generic JDBC level which specific driver we are using, we force the loading of the class containing the driver (2). Note, the jar file containing our driver must be part of this application. The easiest way to do so is adding it to the \xsp\shared\lib directory on your Domino server and restarting the http task.
Once the driver is registered we can create a connection (3) to our specific database, create a statement (4) and return it. (In a production application you would want to parameterize the ip address of your data server, the database, the name and the password used to access it. Also, for performance you would probably want to cache the connection in the applicationScope.)
Two simple functions remain:
| function executeQuery(query:String) { |
| var statement:java.sql.Statement; |
| var results:java.sql.ResultSet; |
| statement = getStatement(); |
(1) | result = statement.executeQuery(query); |
| return result; |
| } |
| function executeUpdate(update:String) { |
| var statement:java.sql.Statement; |
| var results:java.sql.ResultSet; |
(2) | statement = getStatement(); |
| statement.executeUpdate(update); |
| } |
Each of these gets a Statement, via the previous function. They then call either executeQuery() (1) or executeUpdate (2). These facilitate the XPages code in calling an executing SQL statements. They are more readable and less prone to error.
RDB View
So, with these functions in place, the first user interface we're going to create is something very like a Notes view. We can't actually use an XPage View control. This is highly optimized to use with Notes views. Instead we need to use a Repeat control. This can iterate over any type of data. When we create a repeat, in the "Iteration' section on the property sheet, we select "Javascript" and enter in a script that returns a Javascript array.
| var results:java.sql.ResultSet; |
(1) | result = executeQuery("select CITY_ID, CITY_NAME, COUNTRY from CITIES order by CITY_NAME"); |
(2) | var results = new Array(); |
| var idx:integer; |
| idx = 0; |
(3) | while (result.next()) { |
(4) | results[idx] = new Array(); |
(5) | results[idx][0] = result.getInt(1); |
| results[idx][1] = result.getString(2); |
| results[idx][2] = result.getString(3); |
| idx++; |
| } |
(6) | return results; |
We call our library routine to evaluate our SQL statement that retrieves our records (1). We then create a Javascript array to hold the results (2).
We step through the results (3), and for each entry, we create another array to store the values in that row (4). Using the JDBC calls we pull the values from the results and populate our array with them (5). When we've iterated through them all, we return the values. (6)
(Since this code is called each time the repeat draws itself, the code, as written, is very inefficient. In a production version you might choose it populate the array only once, and then store it in a scope variable. If this data is the same for all users, an ApplicationScope variable might be appropriate. If it's different for each user, SessionScope should be used. If the expected size of the data is very long, then you may want to initialize each time, but only populate the areas that the repeat is showing. This can be discovered by context values the repeat makes available to you. It's also likely in a production system that you would use prepared SQL statements.)
As the repeat is evaluated, the values are iterated over, and the contents of the repeat duplicated for each value. When being rendered those can access the current value. For example, if you named your repeat iterator "rowData", you might have a calculated label that displays a text of "rowData[1]" to show the City Name.
A particularly useful thing is that you can add a pager to your XPage and link it up to the repeat. It doesn't care where you get the data from. It will let you page over it!
RDB Display Form
When we're not dealing with collections of records, we want to be able to deal with a single record. For Notes databases we use a form for that. For relational databases we do things a little differently.
Normally in XPages you use a Data source to aggregate data with the form. In a future version of XPages we will be able to create data sources tied to other sources (e.g relational database). For now we are going to query our RDB on the page load, and populate some sessionScope variables with the data we find. We will then use those variables to interact with the data.
If you focus on the XPage itself, and look at the Events tab, you will see that you can trap the "beforePageLoad" event. This is an ideal place to do your RDB lookup. But how do we know what record to look up? The actual key or keys you need is going to be dependant on your data query. The handiest way to pass that information is along the link that goes to the page. Back on the view page, instead of having a label to display the values, we can make it a link. We can then make the link go to a calculated URL. For this we can specify the page that we've put the RDB display form on, and add a parameter to the end of it to indicate which record we want. In our example here the key field is CITY_ID, which we read into the zeroth position in the array of values. So our link URL calculation field looks like:
"RDBForm.jsp?cityID="+rowData[0]
On RDBForm we retrieve and use this value in the beforePageLoad event with this code:
| var results:java.sql.ResultSet; |
| var id:String; |
(1) | id = paramValues.get("cityID")[0]; |
(2) | result = executeQuery("select CITY_ID, CITY_NAME, COUNTRY, AIRPORT, LANGUAGE, COUNTRY_ISO_CODE from CITIES where CITY_ID="+id+""); |
(3) | if (result.next()) { |
(4) | sessionScope.city_id = result.getInt(1); |
| sessionScope.city_name = result.getString(2); |
| sessionScope.country = result.getString(3); |
| sessionScope.airport = result.getString(4); |
| sessionScope.language = result.getString(5); |
| sessionScope.country_iso_code = result.getString(6); |
| } |
We retrieve the ID from the paramsValues global variable (1) and use it to create our select statement. If we find a record (3) we then extract the values from the reply and set them to chosen sessionScope variables. (In production code we would handle the case where we do not find a value. We would alsouse more unique sessionScope variables!)
We can now create controls on our form and populate their values from these sessionScope variables.
RDB Update Form
Displaying data is only half the job. We also want to be able to update that data.
The simplest way to do this is with edit fields, just like you would if you had created a data source bound to a Notes form. Only in this case on the Data tab of the Properties sheet, we specify that we're going to use "Advanced" data binding. We select "Scoped Variable", "Session Scope" and enter the name we chose above appropriate to the edit control we have placed.
This will display the form as above, but also let the user edit the values in it. We can put whatever sort of validation and other constraints we want on to it just like we would for any other sort of data binding.
To save the data we need to add a save button to our page. On the Events tab for it, we add a new event, to lunch some Javascript. The script below extracts the values back from the scoped variables, and saves them to the RDB.
| var update:String; |
(1) | update = "REPLACE INTO CITIES "; |
(2) | update += "(CITY_ID, CITY_NAME, COUNTRY, AIRPORT, LANGUAGE, COUNTRY_ISO_CODE)"; |
| update += " VALUES ("; |
(3) | update += "'"+sessionScope.city_id+"', "; |
| update += "'"+sessionScope.city_name+"', "; |
| update += "'"+sessionScope.country+"', "; |
| update += "'"+sessionScope.airport+"', "; |
| update += "'"+sessionScope.language+"', "; |
| update += "'"+sessionScope.country_iso_code+"') "; |
(4) | executeUpdate(update); |
Because the SQL statement to replace the value is a bit complicated, we break it's construction over several lines. First we create the basic statement (1), then add the columns we want to update (2). Then, we add each of the corresponding values for each column one by one (3). Finally (4) we pass in the SQL statement to be executed.
So, in this section we have shown how you can present data in an XPage from a relational database. Since XPages supports multiple data sources, there is no reason you could not "mix and match" data retrieved in this way from data retrieved from a Notes database. You could use a large granularity approach and have separate parts of your application using each data store. Or you can use a small granularity approach and integrate related records to the point that the end user does not realize they come from completely different back ends.
Another example of using other back end data would be any data repository accessible via Web Services. This article shows how to access a web service. Another way of achieving the same end would be to put all of your database calls into a Java managed bean. This article show how to access java code from an XPage which could be used to access your managed Java bean and implement it that way.
For other examples of using XPages as an integration platform, please see this article. |