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.
15. Allan Paulo Oliveros02/17/2010 11:05:18 PM
Homepage: http://zaido_rider09@yahoo.com
can u give me a code of domino pls ??i need help
16. Alok Kumar04/14/2010 06:25:22 AM
Homepage: http://www.extendcode.com
Nice article. It really gave us a lot of information. We were trying to get information on some subject which is related to this to an extent for last couple of days and this article really helped us.
17. Anitha01/06/2011 08:15:22 AM
John
Great Post. I am doing samething. I need to bring the list of vendors on click of the button. I followed all your steps no data shown in the combo box. I added alert in the javascript function it gives me error message. Can you please help me how to debug this to see where it is going wrong?Thank you so much.
18. Laura01/19/2011 01:28:58 PM
Hey there John!
Don't know if you remember me. We had some email conversations about all this stuff a year ago. My project was on hold for awhile and now I am back at it! I am connecting to DB2 and Oracle perfectly this way, dumping the info into java beans... and returning statuses/data back to my XPage. Plus, I have buttons, the users can click to add selected bean data to a Notes document. That is all beautiful.
But (there is always a "but" isn't there? lol), I am trying to place all of this functionality on a different server within our corporation... only to find that the "button" to pull and Notes document by UNID is not working the same on this server. I am effectively getting a session and a database (I have verified both of these via sysouts to the console). The CommonUserName for the session is the server's name.
However, when I make a call to get the doc, it says it is returning something... but I don't really think it is.
I call:
Document doc = ndb.getDocumentByUNID(UNID);
Sysouts for the following:
doc.isValid() = true
doc.hasItem("Form") = false (THIS IS NOT TRUE)
If I call a doc.save and check the boolean returned, it states false.
Any ideas off the top of your head? Is there any documentation on how to setup security on a server that will be serving up XPages and java backend classes (NOT java agents)?
Any help would be greatly appreciated!!
Thanks,
Laura
19. Laura Sponaas01/20/2011 08:15:21 AM
John,
7 hours later I found my problem(s)!!! A few different security issues that sound simple, but false positives that java was giving me kept me searching in the wrong direction for a little while. The problem I described in my previous comment was a readers list issue. And the other was based on the fact that the calling db was anonymous and the db to write to was not. It's a long story and if anyone is interested I can send the blog post to them that I created on my companies internal site.
Any feedback or suggestions regarding security are welcome. But, at least I have my problems fixed now! Thanks for "listening".
20. Leif Lagebrand04/04/2011 12:36:29 PM
I'm interested Laura. Could you send the internal blog post to elle1234 at gmail com?
21. Naseer07/08/2011 11:16:50 PM
Hi John,
Thanks for this wonderful article. I am trying to connect to DB2 database on os400. I did followed all the steps said by you, But when I run it on server it throughs me the below error
"Error 403
HTTP Web Server: You are forbidden to perform this operation "
Regards,
Naseer
22. michaelw.jackson@srs.gov09/22/2011 12:45:54 PM
Can you tell me how to find out what goes in the forName() for an Oracle database?
Thanks,
MJ
package com.SAIS.data;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class OracleQuery {
public static ResultSet executeQuery(String connDB, String connUserName, String connPwd, String query){
ResultSet resultSet=null;
try {
// load the Oracle Driver
class.forName("What should go here to connect to Oracle?");
// establish a connection to Oracle
Connection oracleconn = DriverManager.getConnection(connDB,connUserName,connPwd);
Statement st = oracleconn.createStatement();
// execute the query
resultSet = st.executeQuery(query);
}
catch(Exception e){
e.printStackTrace();
}
return resultSet;
}
}
23. michaelw.jackson@srs.gov09/22/2011 12:51:57 PM
oops...we use the ojdbc4.jar driver.
MJ
24. LearningJAVA10/07/2011 09:46:35 AM
thanks for the demo db, I want to try this with a remote IBM iSeries machine. I am having problems getting the format of the connSting correct. Do I use an odbc connection name? or can I use the system name?
var connString="jdbc:as400:?????...."
thanks for the help
25. Johnson12/29/2011 10:40:21 PM
Dear John:
thanks for the greate sample.I can insert,update,delete a record to oracle DB. May be I can try to run a procedure from xpage.Now to meet a problem:When I insert a new record to oracle DB but duplicate.How to receive the sqlexception from xpage?
Any help would be greatly appreciated!!
Thanks,
Johnson
26. Neeraj02/02/2012 10:28:16 PM
Hello..
i tried this test but in domino i am getting the following Error pls Help.
HTTP JVM: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=ADMINISTRATOR.DEPARTMENT, DRIVER=3.59.81
