XPages Example: Calling Java Methods directly from server side JavaScript - performing an SQL query Part 107/07/2008I have been reading many of the discussions this week concerning JavaScript as the language for XPages. I can understand many of the concerns and questions as to why JavaScript? So, I decided to demonstrate one of the advantages of using JavaScript as the server side language for XPages. In the below example I demonstrate how easy it is, and how powerful it is, to call Java methods directly from JavaScript. You can download the example Domino database by clicking here: XPagesJSToJava.nsf
JavaScript can call Java methods directly and pass variables to and from those methods. There are some "type" differences between Java and JavaScript that you need to be aware of. I believe the charts on this site outline the variable conversions well: Javascript to Java Conversions. In the below example I pass string values directly from JavaScript to and from Java, which is straightforward and without any conversion issues.
Since Domino 8.5 is Eclipse based, you can switch to the Java perspective and add Java code to your project. The nsf database is the project. (If you haven't done this yet, it is interesting to switch perspectives and look around at the structure and the code.) By switching to the Java perspective, you open up your code to the importing or referencing of Java libraries, or the creation of your own classes.
What makes calling Java methods directly so powerful in XPages is that nearly every property in an XPage can be computed, and wherever these's a computation you can use JavaScript for the value or formula. For example, this will allow you to call Java for: populating data sources, labels, view column labels, view data columns, computed text, visibility formulas, any place you see a diamond (which is everywhere)...as well as all server side events. So, wherever you can call JavaScript you can also basically call Java methods!
Example Overview
In this example I am calling a Java method that will return data from a DB2 database. I pass in the query and I receive a concatenated string as the result. If you want to follow the example, and you do not have DB2, you can download DB2 9C for free from here: DB2 Expess-c 9.5. After the install you will see an option to create the Sample database, select this and you will have pre-populated tables to work with.
**Just a note that this is just for demo purposes and performance was not a key consideration. Hopefully access to relational databases will be native in a future release.
This is a screen shot of the Employee XPage. The Combo Box Control's values are populated with a JavaScript call to "getDeptNumbers()". This function calls the Java method "javaMethods.SQLQuery.executeQuery()" which is the Packagename.Class.Method() of the method we want to call. The last line of the JavaScript function I use an "@Explode" in order to convert the string to an Array for the Combo Box values. There's very minimum amount of code in this example so it will be easy to implement.
** update 7/9/08 - I changed the location of the SQLQuery.java file in Part 2 of this article and it is different than in the Eclipse screen shot
Here is a screen shot of the Employee View where I perform a dynamic SQL lookup on the last column. Nathan Freeman posted an example of performing a @DbLookup on a view column. It's a great example, and a great change in how we as Domino developers can utilize views in XPages. View columns can contain any JavaScript formula in XPages, so they can be dynamic.
As the view is rendering it is dynamically performing SQL lookups for the Department Name by passing the ColumnValue "Depart. No." to the JavaScript function "getDepartment(deptNo)". This function makes a call to executeQuery passing in the "where" clause so we only retrieve the single Department Name for each Dept. No. in a row. Now....I Know what you are thinking....performance obviously would be a consideration in really using this technique....but it's really cool example nonetheless!
How to build it
Click here to continue with Part 2: How to build it.....
Technorati: Domino 8.5 XPages
1. Jo Grant07/08/2008 01:52:06 AM
Homepage: http://111george.com/jaymin
A word of caution: I believe that the Local direction gets deleted when you do a Project->Clean. It isn't a safe place to put Java files.
However I think that Web Resources\WEB-INF\sources is. I've had success with that.
2. John Mackey07/08/2008 07:29:48 AM
Homepage: http://www.jmackey.net
@ Jo,
I have cleaned the project and do not lose the java files. I appreciate the advice and will look into it though.
Thanks,
John
3. Philippe Riand07/08/2008 05:10:13 PM
Jo is right, as the Local directory is a store outside the NSF itself. It is intented to be used by the XPages compiler, which generates temporary Java files. Project->clean doesn't doesn't clear out those files as they are not generated, but the java files won't be available from a different Designer workspace.
The best solution is to create a Folder, like WebContent/WEB-INF/source, put the Java files inside that one and point the Java compiler source path to this directory. Each file is then an NSF file resource.
4. John Mackey07/08/2008 09:50:00 PM
Homepage: http://www.jmackey.net
Phil,
Thanks. I had followed up with Jo and he explained it further. I appreciate the input.
I'll update the blog entry with the new best practice once I test it.
John
5. Howard07/16/2008 02:21:23 AM
John, I get a classnotfoundexception:com.ibm.db2.jss.db2driver on the server when I try the example. Is this related to to the comments above? thanks,
6. John Mackey07/16/2008 03:36:08 AM
Howard,
It's not related to the above comments.
The error should be "db2.jcc" not "db2.jss" so please check the spelling. If the spelling is correct in your code, did you copy the db2 drivers as outlined in part 2?
Regards,
John
7. Vayasin10/29/2008 09:11:16 AM
Dear John, I am also getting the error:
classnotfoundexception:com.ibm.db2.js.db2driver
And I used your database, can you explain to me how to add the db2driver to the domino server?
8. Vayasin10/29/2008 09:17:43 AM
Sorry I ment db2.jcc of course.......
9. John Mackey11/01/2008 07:51:58 AM
Hi Vayasin,
You can find instructions on page 2 of the article. Here's a snippet:
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.
John
10. Vayasin11/03/2008 02:31:11 AM
Hi John,
This worked for me 
i forgot to copy the dbjcc_license_cu.jar.
Thank you for the fast reply.
11. Francesco01/22/2009 10:04:05 AM
Hi John! I've modified your solution to connect to an as400 system, using the jt400 drivers, but when i try to execute the java class i've got a classnotfoundexception:SQLQuery !! I've put the java files in the source folder ander WEB-INF and added it as source in the project...isn't it correct?
Thank you in advance
12. John Mackey01/23/2009 06:38:11 AM
@Francesco. It sounds like you made all the correct changes. It's hard to say what is wrong without seeing the db and reading the documentation for that driver.
If you are interested, you can send me the db and i'll take a look. My email is found here: { Link }
John
13. Veer02/19/2009 01:47:40 PM
John,
Great post. I was looking for something like this.
I am going to download this and play with it.
A question, have you written any JUnit tests with the Java code which you are writting. If not, do you know if anyone else has?
Thanks
14. Roland Reddekop03/02/2009 07:24:44 PM
Homepage: http://www.bleedyellow.com/blogs/sanitycheck/
@11 Francesco and John,
My antenna went up when I heard you mention connecting to an AS400. Were you eventually successful. This would be a big thing for us.
