Windows XP Professional, Lotus Notes Client 7, Domino Server 7, MySQL 5, MySQL JDBC driver 5.1

JDBC stands for Java Database Connectivity, which is a set of common APIs provided by Java for accessing relational database without writing custom vendor specific code.

For this tutorial we need MySQL database. If you want to know how to install MySQL database in your local Windows machine, please refer the ODBC tutorial.

Lotus Notes/Domino RDBMS integration using ODBC 

If your MySQL db is available in your network, you may access the same using the following information

• Host Name – Name of the machine where MySQL is installed. Alternatively you may use IP address.
• User Name / Password – for accessing MySQL

Ask your database administrator for the above details. You may download any SQL client like SQLYog for accessing database.

http://www.webyog.com/en/

Ok, first connect to MySQL. Then create a database named mydb. Switch to mydb database. Create an employee table with four fields.

• employeeid int not null auto increment
• name varchar(100)
• age int
• designation varchar(100)

Set the primary key as employeeid. Insert some records into employee table. Just note employeeid column values of some records. We have to use this in our Domino application.

You may execute the above commands using SQL statements or by utilizing GUI features of MySQL client. For SQL statements refer the ODBC tutorial provided in the above link.

Ok! now your database is ready with an employee table and some records. Here we are using the same case study for our ODBC tutorial, but in web environment. When a user opens Employee form in browser and input employee id as key, the employee details such as name, age, designation etc are imported from MySQL database. 

We have to download MySQL JDBC driver. Go to http://www.mysql.com/products/connector/ and download JDBC driver. Unzip the downloaded file. It contains JAR file named mysql-connector-java-<version>-bin.jar file. This JAR file contains APIs for JDBC. We need only this file for our application.

Let us get into Domino side.

Create a new database in server Go to File–>Database–>New. Give the file name as test.nsf 

 Open the database in Domino designer.

Let us create one Java agent. Go to agents under shared code in programmers pane. Create new agent. Select java as agent type in drop-down list. In agent properties enter GetEmployees as Name. In Runtime drop-down select Agent list selection and none under Target drop-down list.

In security tab, select Allow restricted operations in Set runtime security level drop-down list. Ensure that your Notes userid got sufficient rights to run Java agent in server.

Now we have to add the JDBC jar file to our agent. Click on Edit Project button in bottom pane. Organize Java Agent Files dialog box appears. Select the Base directory as the folder in which JDBC jar file is kept. Select the checkbox All in Show file types. Now left window lists all files in base directory. Select the jar file and click on Add/Replace Files.

 Now put the following code snippet  as agent code.

import lotus.domino.*;
import java.sql.*;
import java.io.*;
 
public class JavaAgent extends AgentBase {
 
 public void NotesMain() {
 
  try {
   Session session = getSession();
   AgentContext agentContext = session.getAgentContext();
 
   // (Your code goes here)
   PrintWriter pw = getAgentOutput();
   Database db=agentContext.getCurrentDatabase();
   String userName;
   String password;
   String driverClassName;
   String jdbcURL;
   int employeeID;
   Connection con;
   Statement stmt;
   ResultSet rs;
 
   Document doc=agentContext.getDocumentContext();
   employeeID=doc.getItemValueInteger("EmployeeID");
   userName="root";
   password="password";
   driverClassName="com.mysql.jdbc.Driver";
   jdbcURL="jdbc:mysql://192.168.8.119/mydb";
   //load driver class
   Class.forName(driverClassName).newInstance();
   con=DriverManager.getConnection(jdbcURL, userName, password); 
 
   stmt=con.createStatement();
   rs=stmt.executeQuery("select * from employee where employeeid="+employeeID+"");
   while(rs.next()){
    doc.replaceItemValue("EmployeeName", rs.getString("name"));
    doc.replaceItemValue("EmployeeAge", rs.getString("age"));
    doc.replaceItemValue("EmployeeDesignation", rs.getString("designation"));
   }
   rs.close();
   stmt.close();
   con.close();
   //save the document and open it in edit mode
   doc.save(false, false);
   pw.println("["+"/"+db.getFilePath()+"/$All/"+doc.getUniversalID()+"?EditDocument"+"]"); 

In the above code you have to make some changes. In jdbcURL variable replace 192.168.8.119 with the hostname/IP address of your MySQL db. Also userName and password variable values should be changed with your authetication information. Save the agent.

Create new form. In form properties set the Name as Employee. Create a table with four rows and two columns. In table properties set the width of cell as 2.0. Now enter text in left cells for field labels. Type the following as labels from top to bottom in left cells.

• Employee ID
• Name
• Age
• Designation

Now create fields in the following order from top to bottom in right cells.

• EmployeeID (Number)
• EmployeeName (Text)
• EmployeeAge (Number)
• EmployeeDesignation (Text)

Create one hotspot button for fetching employee details from MySQL. Place the cursor in first right cell just after EmployeeID field and go to Create a Hotspot Button. In button properties give the label as Go. Write the following formula in click event of button.

@Command([FileSave]);
@Command([FileCloseWindow])

In WebQuerySave event of form write the following formula to execute GetEmployees agent.

@Command([ToolsRunMacro]; "(GetEmployees)")

Save the form. Now open the form in browser. Enter employee id number of any record in MySQL and click on Go button. Name, Age and Designation field values are imported from MySQL database.

Now let us walk through the code.

On clicking Go button of employee form, the agent (GetEmployees) is executed since that agent is called in WebQuerySave event.

 PrintWriter pw = getAgentOutput();
 Database db=agentContext.getCurrentDatabase();

Here we are initializing PrintWriter object which is used to write to output stream, in our case it is browser. Then we get reference to current database.

Next section is declaring variables. Here we define variables for database connection, data retrieval and manipulation. Then we get a reference to current document and the EmployeeID value entered by user is stored in employeeID variable.

userName="root";
password="password";
driverClassName="com.mysql.jdbc.Driver";
jdbcURL="jdbc:mysql://192.168.8.119/mydb";

The database connectivity information is stored in the above variables like userName, password etc. The driver class name is “com.mysql.jdbc.Driver” . This may be different for other versions. So refer the JDBC driver documentation. In jdbcURL, we provide host name/IP address and database.

Class.forName(driverClassName).newInstance();
con=DriverManager.getConnection(jdbcURL, userName, password);

Here we initialize driver class and get a connection object to MySQL.

stmt=con.createStatement();
rs=stmt.executeQuery("select * from employee where employeeid="+employeeID+"");
while(rs.next()){
    doc.replaceItemValue("EmployeeName", rs.getString("name"));
    doc.replaceItemValue("EmployeeAge", rs.getString("age"));
    doc.replaceItemValue("EmployeeDesignation", rs.getString("designation"));
}
rs.close();
stmt.close();
con.close();

Here we create statement object and execute the query. Then we iterate through the result set and values are stored in document fields. Once data is fetched, we close the connection. Now the values are stored in document. We save the document and open it again in browser.

For further information regarding JDBC refer Java JDBC tutorials.

Please note that JDBC driver version should be compatible with JDK. Refer Domino Java documentation for JDK information.

cheers!