This is Part 2 of this example.  Click here to read Part 1.

How to Build It
I'll go over the steps in detail here.  The steps are actually very few.  I found that the SQL.jar file is already included in the project, so you do not need to import any jar files into the Eclipse project.  You can download the example Domino database by clicking here: XPagesJSToJava.nsf

Database Setup
Install DB2 if you haven't already.  The sample table that we will be using is Department. This is a screen shot of the table and the data.:

**note: I used the Administrator id when generating the sample tables so the schema is Administrator.  This might be different for you.  You will need to use the correct schema name in your query.

You will need to copy the following db2 jar files from the DB2 installation directory "SQLLIB\java\db2jcc.jar" and "SQLLIB\java\dbjcc_license_cu.jar" to your "Domino\xsp\shared\lib" directory.  There might be another way to reference the jar files by editing the Notes.ini, but this works too.


Lotus Notes Designer
Create your new Lotus Notes db XPagesJSToJava.nsf in DDE.

Now switch to the Java perspective by selecting: Window->Open Perspective->Other.  You will be presented with the below list of choices.  Select Java and click OK.



Expand your project on the Left hand Project navigator.  Select the WebContent/WEB-INF Folder:
- right click and select New->Folder.  Name the folder source, click Finish.
- right click on the source folder and select New->Package.  Name the Package javaMethods click Finish.
- right click on the javaMethods package and select New->File.  Enter SQLQuery.java for the name.  Click Finish.  

Here's a screen shot of how it should look:


Now double click on the SQLQuery.java and paste in the following code:

package javaMethods; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class SQLQuery{         public static String executeQuery(String connDB,                         String connUserName, String connPwd, String query){                 String theResult="";                 ResultSet resultSet=null;                 try {                         // load the DB2 Driver                         Class.forName("com.ibm.db2.jcc.DB2Driver");                                                 // establish a connection to DB2                         Connection db2Conn =                         DriverManager.getConnection(connDB,connUserName,connPwd);                                                                         Statement st = db2Conn.createStatement();                                                 // execute the query                         resultSet = st.executeQuery(query);                         while (resultSet.next()) {                                 //theResult will be the 1st column separated using ";"                                 theResult=theResult+resultSet.getString(1)+";";                         }                 }                         catch(Exception e){                         System.out.println(e);                 }                 return theResult;         } }

We need to add the source folder to the java build path:
- select the project and then select Project->Properties from the menu (or right click)
- select the Java Build Path and click on Add Folder button.  Select the new source folder.  Click OK.


Your screen should look like this:



Save this and switch back to the Domino Designer perspective by selecting: Window->Open Perspective->Other.  Select Domino Designer.

We will now create the server side JavaScript library for our 2 functions.  Expand Code and then select Script Libraries and click on New Server JavaScript Library.  Enter SQLFunctions for the name. Paste in the following code.  You need to change the userName and pwd variable according to your DB2 installation.  Most likely the connString will be ok if this is on your local machine.

var connString="jdbc:db2://localhost:50000/sample"; var userName="db2admin"; var pwd="password"; function getDeptNumbers(){         var query="SELECT DEPTNO FROM ADMINISTRATOR.DEPARTMENT";         depts=javaMethods.SQLQuery.executeQuery(connString,userName,pwd,query);         return @Explode(depts,";"); } function getDepartment(deptNo){         print("dept="+deptNo);         var query="SELECT DEPTNAME FROM ADMINISTRATOR.DEPARTMENT WHERE DEPTNO='"+deptNo+"'";         dept=javaMethods.SQLQuery.executeQuery(connString,userName,pwd,query);         return dept; }

Create the Employee form.  Here is what the form fields look like:


Create the Employee XPage.  If you need a refresher in creating the XPage fields, look at one of my earlier examples posted on this site.

Add a Combo Box control bound to the DepartNo field.  Click on the Values tab and click on Add Formula Item. For the formula add the JavaScript function getDeptNumbers().  Here's a screen shot:



I added additional functionality that will look up the DepartmentName for any selected DepartNo.  Drag a new Button to the table. Change the label to Lookup.  On the Event tab for the button, select Script Editor and enter the below remark:

Even though the remark does nothing, it causes the page to make a round trip to the server and will compute any computed text.  Another option might have been to add that code to the onChange() event of the Combo Box. (probably a better choice in retrospect)

Add a Computed Field control to the table.  Enter the following formula that will look up the Department Name by calling the getDepartment() function:



Next we will create a view for the EmployeesView XPage.  Here's the layout:



Create a new XPage named EmployeesView and drag a View Control to the page.  
- Make the first column a link.
- Select the View and on the Data tab, select Employee for the default XPage to open.
- Select All Properties and expand data.  Enter "detailrow" as the var.  This will allow us to reference the column values so we can perform the lookup..



Next:
- append a new column in the view by right clicking on the Depart No. column and select Append Column.  
- click on the title for the new column and change the label to Department
- click on the column itself so we can enter a formula



Click on the All Properties tab for the new column.
- expand data
- click on the value property
- click on the diamond and select Computed Value
- enter the following JavaScript formula


Last Steps:
- Add the JS library to the XPages.  On each XPage, select the Page properties, select the Resources tab and click Add Script Library.  Select the SQLFunctions library
- Drag a new Button Control to the page that will create new Employees
- Circle back to the Employee form and create the Save and Cancel buttons that will navigate back to the EmployeeView page.

That's it, your done!  Test it out.


Technorati: Domino 8.5 XPages

1. chengqi11/01/2008 02:53:41 AM

This is a question not about Javascript to call Java, it is about something else. But I didn't find a post that is correlated, so I just posted my question here.


You know, after I switched to Java Perspective, I found that all the form/view elements are shown as XMLs, then does it mean that I can edit them manually(if I know how surely). This will also lead to some changes to Version Control System of DDE8.5 development system. Previously, we've to use something like TeamStudio/CIAO, so now we can use Subversion/ClearCase, is that correct? We can also even able to do form/view elements merge sometimes. If this is true, it is really great news.

Thanks.

---

2. chengqi11/01/2008 03:08:39 AM

This comment is not about JavaScript calling Java, but I didn't find a relevant post in the site, so I just posted it here.

After switching perspective from Domino Designer to Java, I found that all the form/view elements are just XMLs now, this is also a new change of Domino 8.5, correct? Does it mean that I can manually modify it if I know how to? Besides, if that's true, then we might be able to drop TeamStudio/CIAO version control system for NSF development, and adopt Subversion or ClearQuest, where we even can implement code merges, this will be another good news....

Thanks.
Cheng Qi

---

3. wgb.sky05/25/2009 03:03:23 AM

John,
can you tell me how to running java in the
java view?

---

4. Jerry Shelley06/18/2009 11:12:24 AM

I'm trying to get a JDBC DataDirect SQLServer connection working, but failing with ClassNotFoundException on "com.ddtek.jdbc.sqlserver.SQLServerDriver". Not the Lotus provided ones, as I can't find them, but a downloaded set from the JDBC DataDirect site.

I've added the sqlserver.jar file from the JDBC installation into the Domino\java\xsp\shared\lib (although you mention Domino\xsp\shared\lib so I've created that as well) to no effect.

I've also added the jar into the NSF Project using Project Properties, also with no joy.

Any thoughts on how to remedy would be gratefully received.
Thanks and Regards.
Jerry.

---

5. Sravan Kumar07/25/2009 10:47:02 PM

John, I get a error java.lang.classnotfoundexception:net.sourceforge.jtds.jdbc.Driver on the server when I try this example to connect MSSQL server.which Driver is supporting to MSSQL

thanks,

---

6. Laura S10/26/2009 11:37:33 AM

John,
This looks really cool and I think I am ALMOST there. Using 8.5.1... it will not allow me to create the javaMethods package under the source (normal folder) I create. It forces it under Local. If I try to make my "source" folder by right clicking and select New->"Source Folder"... it again forces my selection to the local folder only. Is there a feature change here? Or am I doing something wrong?

Thanks for any help!!
Laura

---

7. Scott Blaylock11/29/2009 08:35:26 AM

I'm using 8.5.1 and have found a way to get this working without having to add the database jar files to the Domino server. From the java perspective, create a 'lib' folder under 'WebContent\WEB-INF' (if it doesn't already exist). Right-click over the 'lib' folder and select Import. Twist open or double-click General, doulble-click 'File System'. Browse to the directory where your database .jar files are. In the right column select your database .jar(s). In my case I'm using MySQL on an iSeries (AS400), so the file I imported was mysql-connector-java-5.1.10-bin.jar. JAR files placed in this directory are stored in your NSF and automatically appended to the run time class path of JavaScript.

Scott.

---

8. Karthick04/16/2010 06:19:04 AM
Homepage: http://ozinisle.blogspot.com

Hi John,
This is very helpful. I followed all the steps and eventually things work fine for me.

When I attempted it the second time, I started facing the issue that was mentioned by Laura - the javaMethods package under the source which I create is forced under Local.

Proceeding further, creating the package and the java file under local, and dragging and dropping the same under the source folder works .

But is there any fix to this?... Am I missing some thing?

thanks,
Karthick

---

9. Atul Saxena04/26/2010 09:48:34 AM

Dear John,

Thanks for posting. It works beautifully with db2 on AS/400 and good tip by Scott Blaylock. This kind of posting will be very helpful in making Lotus Notes/Domino Successful.

Best Regards

Atul Saxena

---