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

No comments.