Background
Traditionally, Domino application development involved using Domino Designer to build forms, views, agents, etc that interact with the Domino database as the backend data store (usually in the form of documents). On the flip side, building traditional J2EE or other web applications involved a web server, an IDE, an application server (WebSphere) and a backend data store like DB2, MySQL, etc. The advent of XPages as a Domino design element has blurred the lines between these two approaches. What I will describe here (including the sample application) is an approach to using XPages and Serverside Javascript / Java to use DB2 as the data store for a Domino hosted application.
The application is built locally and can be run and tested as such using the following Setup.
Setup
-
Download the application file (nsf) and DB2 script (db2xpages.zip ~3.75MB from ibm.com/connections/Files)
-
Download a DB2 trial from IBM (I used DB2 Workgroup Server Edition version 9.7).
-
Install DB2 on your local machine.
-
Copy the DB2 libraries to your Notes jvm library directory. (/SQLLIB/java to notes/jvm/lib/ext). Files you need are db2jcc.jar and db2jcc_license_cu.jar.
-
You may need to also add the following to your java.policy file (notes/jvm/lib/security):
grant codeBase "xspnsf://server:0/db2sample-oneui.nsf/-" {
permission java.security.AllPermission;
};
-
Run the attached createSampleDb.sql file to build the local XDEMO database. (to run open a DB2 Command window and the cd to the directory where you downloaded the sample. then run db2 -tvf createSampleDb.sql).
-
Download the attached application db2-sampleoneui.nsf to your notes/data directory
-
Open the db2sample-oneui.nsf database and update the appropriate DB2 connect information in the Profile (username, password, and port #. Default DB2 port is 50000)
-
Open the db2sample-oneui.nsf in Domino Designer and sign the Design.
-
Open the Profile view and edit the profile document to add the DB2 username, password, and connection port.
-
Right click on the people pages in Designer and select Preview in Web Browser - Default Browser.
Debugging
If, after step #9 you do not see data then you can check the console.log file located in the : notes/data/IBM_TECHNICAL_SUPPORT directory.
The Application
The application (download the attached db2sample-oneui.nsf) is a simple application that manages a list of people. You can add, edit, view, or remove a person from the list of people. Here is a look at some of the final product:
|
List of People with the ability to Add a new person
|
|
List of People with the ability to Add a new person
|
Editing or deleting a person
|
Design Elements
The following are the specific design elements used in this application and how they tie together.
-
Document - there is only 1 document in this application: Profile that contains information about how to connect to DB2 (username, password, port)
-
ScriptLibrary - there is a single script, ServerSide Javascript, that returns the profile details
-
Java code - New feature of 8.5.x that allows you to create Java code directly in your application. In this application the Java code is used to make all of the JDBC calls to connect to DB2 and return data. The code uses Data Access Object to perform such operations as insertPerson, deletePerson, getLastTenPeople, updatePerson. Here is a sample of the getPerson method in the DemoDAO class:
1 public PersonObjectTO getPerson(int personUid) throws DAOException {
2 Connection conn = null;
3 PreparedStatement ps = null;
4 ResultSet rs = null;
5 PersonObjectTO person = null;
6 try {
7 conn = getConnection();
8 ps = conn.prepareStatement( "SELECT FIRST_NAME, LAST_NAME, AGE, COUNTRY FROM DEMOUSER WHERE UID=?");
9
10 ps.setInt( 1, personUid );
11 rs = ps.executeQuery();
12 if ( rs.next() ) {
13 person = new PersonObjectTO();
14 person.setUid( personUid );
15 person.setAge( rs.getInt("AGE") );
16 person.setFirstName( rs.getString("FIRST_NAME") );
17 person.setLastName( rs.getString("LAST_NAME") );
18 person.setCountry( rs.getString("COUNTRY") );
19 }
.
.
.
.
20 return person;
21 }
Line 7 establishes the connection with DB2 (using a separate ConnectionManager class). Line 8 is the SQL statement to get this record from this table (DEMOUSER) in the database (XDEMO). Line 10 substitutes the value of personUid into the query in the WHERE clause for the ?. Line 11 executes the query and gets the result. Lines 13-18 get the data from the results and puts it into the PersonObjectTO transfer object and returns the object on line 20.
Similar methods are defined in the DemoDAO to perform CRUD operations on the data.
-
faces-config.xml - configuration file (Window - Open Perspective - Java) allows you to define a class (PersonObjectTO in this case) to be used as a Managed Bean. Managed Beans bind to the forms on the XPages. Managed Bean entry in this application:
<managed-bean>
<managed-bean-name>Person</managed-bean-name>
<managed-bean-class>xpage.sample.helper.PersonObjectTO</managed-bean-class>
<managed-bean-scope>session</managed-bean-scope>
</managed-bean>
How is this bean used? When creating the input elements on a form you then bind the elements to attributes of the bean and the XPage session keeps the two in sync. In this screenshot the firstName edit box is bound to the Person bean to the bean's firstName attribute.
The XML source for this input field looks like:
<xp:inputText id="firstName" value="#{Person.firstName}" required="true" />
For those of you familiar with JSPs or JSF you will recognize this is Expression language used to get data from objects in the appropriate scope (request, session)
-
XPages - display all of the forms and views in the application. Server-side Javascript on XPage events allows you to call the Java code, connect to DB2 and return the data. For example, on the person XPage, the Add user button will use this bean and submit it to the DemoDAO for inserting into the DB2 database.
1 var profile = getProfileData();
2 var dao:xpage.sample.dao.DemoDAO = new xpage.sample.dao.DemoDAO( profile.username, profile.password );
3 dao.setPort( profile.port );
4 var person:xpage.sample.helper.PersonObjectTO = dao.insertPerson( sessionScope.get("Person") );
Here you can see the bean pulled from the session scope ("Person") and then using the DemoDAO, inserted into the database.
Conclusion
Domino now provides a full featured IDE for web development. Specifically, the XPage and Java elements bring great power to your application development. In this article I tried to show how to use these new elements to interact with DB2 as the data store. NO DATA is stored in the (Lotus Notes) Domino database. I encourage you to download the sample application, perform the setup and run the application to see how easy it really is. Keep in mind that there was no security enabled on these XPages but you can add ACLs to the XPage to prevent others from viewing / performing certain operations. You get lots of nice features using XPages in your development projects including: field validation (required vs. cutom), AJAX/dojo support, reusable components, and security.