How do I combine Domino view data and JDBC query in a repeat control

Question

Currently, I have a repeat control with computed fields that display column values from a Domino view. In each row in the repeat control I have another computed field that executes a SQL query that returns a value from a SQL table. The SQL query has a parameter that uses one of the column values from the Domino view.

For the SQL computed field I wrote a function that instantiates a JDBC connection and then executes a SQL query for each row in the repeat control. The function looks like this (the pTextNo argument comes from one of the column values in the Domino view):

function getFormulaTextDetailRows(pTextNo){
if(pTextNo == null){return ""};
var con:java.sql.Connection;
try {
    con = @JdbcGetConnection("as400");
    vStatement = "SELECT TXSQN, TXDTA FROM LIB1.PRTEXTS WHERE RTRIM(TEXT#) = ? ORDER BY TXSQN";
    var vParam = [pTextNo];
    var resultset:java.sql.ResultSet = @JdbcExecuteQuery(con, vStatement, vParam);
    var returnList = "<ul>";
    //format the results
    while(resultset.next()){
        returnList += ("<li>" + resultset.getString("TXDTA").trim() + "</li>");
    }
    returnList += "</ul>";
}catch(e){
    returnList = e.toString()
}finally{
    con.close();
}
    return returnList;
}

This works fine but I'm sure this isn't the most efficient way of utilising the JDBC connection. Opening and closing a JDBC connection on each row in the repeat control isn't right and I'm concerned that when more than one person opens the XPage the server will run into difficulties with the number of open connections.

After doing some research on the internet it seems I should be using a jdbcConnectionManager on the page.

I added a jdbcConnectionManager to my custom control and also added a jdbcQuery data source to the panel that holds the repeat control. The jdbcConnectionManager looks like this:

<xe:jdbcConnectionManager
    id="jdbcConnectionManager1"
    connectionName="as400">
</xe:jdbcConnectionManager>

And the jdbcQuery data source looks like this:

<xe:jdbcQuery
    var="jdbcProcessText"
    scope="request"
    calculateCount="true"
    sqlQuery="SELECT TXSQN,TXDTA FROM DOMINO.PRTEXTS WHERE RTRIM(TEXT#) = ? AND TXSQN != '0' ORDER BY TXSQN"
    connectionManager="jdbcConnectionManager1">
    <xe:this.sqlParameters>
        <xe:sqlParameter value="#{javascript:requestScope.processTextNo}">
        </xe:sqlParameter>
    </xe:this.sqlParameters>
</xe:jdbcQuery>

My computed field's value property in the repeat control looks like this:

requestScope.processTextNo = textrow.getDocument().getItemValueString('ProcessTextNo');
var vCount = jdbcProcessText.getCount();
var returnList = "<ul>";
for(i=0; i<vCount; i++){
    returnList += ("<li>" + jdbcProcessText.get(i).getColumnValue("TXDTA") + "</li>");
}
returnList += "</ul>";
return returnList;

The problem I've run into is that I don't get any data from the JDBC query at all. Even if I hard code a value I know exists in the SQL table in the sqlParameter property of the jdbcQuery object I still get no results. I suspect I'm not calling the jdbcQuery object correctly but I can't figure out how to do so. Any help will be greatly appreciated.

Answer 1

You may want to reconsider your approach. I would suggest creating a Java bean to get the Domino view data, loop through that and call out to your query for each row in the view. Build up a List (Java List) of a Row class that has all the data you want to show. Then in the repeat call to your Java bean to a method that returns the List of Row classes. In each control in the repeat you would call to the getXXX method in your Row class. This way you can quickly build the List the repeat works on. Doing it your way in the control in the repeat will be very slow.

Howard

Answer 2

Here's the bean I wrote to do the job. At the start it opens a connection to the SQL data source, grabs a viewEntryCollection using a document UNID as a key, and then puts the column values into a HashMap for each row in the viewEntryCollection. One of the values in the HashMap is pulled from a SQL query. My repeat control iterates over the List returned by the bean. In other words the bean returns a List of HashMaps where most of the values in each HashMap comes from Domino view entry data and one value comes from SQL (not sure if that's the correct way of saying it, but it makes sense to me!).

Here's my code:

package com.foo;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Vector;

import javax.faces.context.FacesContext;

import lotus.domino.Database;
import lotus.domino.NotesException;
import lotus.domino.View;
import lotus.domino.ViewEntry;
import lotus.domino.ViewEntryCollection;

import com.ibm.xsp.extlib.relational.util.JdbcUtil;
import com.ibm.xsp.extlib.util.ExtLibUtil;

public class ProcessTextLines implements Serializable {

    private static final long serialVersionUID = 1L;

    private Connection conn = null;

    public int rowCount = 0;

    public int getRowCount() {
        return rowCount;
    }

    // public void setRowCount(int rowCount) {
    // this.rowCount = rowCount;
    // }

    public ProcessTextLines() {
        /*
         * argumentless constructor
         */
        try {
            // System.out.println("ProcessTextLines.java - initialising connection to as400");
            this.conn = this.initialiseConnection();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        finally {
            if (this.conn != null) {
                // System.out.println("ProcessTextLines.java - closing connection to as400");
                try {
                    this.conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    public List<HashMap<String, String>> getRows(final String unid)
            throws NotesException {

        List<HashMap<String, String>> result = new ArrayList<HashMap<String, String>>();

        try {
            Database db = ExtLibUtil.getCurrentSession().getCurrentDatabase();
            View view = db.getView("luProductMasterFormula");
            view.setAutoUpdate(false);
            ViewEntryCollection vec = view.getAllEntriesByKey(unid, true);
            ViewEntry ve = vec.getFirstEntry();
            while (ve != null) {

                result.add(processRowVals(ve));
                rowCount++;

                ViewEntry tmp = vec.getNextEntry(ve);
                ve.recycle();
                ve = tmp;
            }

            view.recycle();
            db.recycle();
            vec.recycle();
        } catch (NotesException e) {
            e.printStackTrace();
        }

        return result;

    }

    /*
     * Create a HashMap of names + column values from a ViewEntry
     */
    @SuppressWarnings("unchecked")
    private HashMap<String, String> processRowVals(ViewEntry ve) {

        HashMap<String, String> processRow = new HashMap<String, String>();

        try {
            Vector cols = ve.getColumnValues();

            processRow.put("sequenceNo", cols.get(1).toString());
            processRow.put("textNo", cols.get(3).toString());
            processRow.put("status", cols.get(6).toString());
            processRow.put("textLines", getProcessTextLines(cols.get(3).toString()));
            // unid of the entry's doc
            processRow.put("unid", ve.getUniversalID());

        } catch (NotesException e) {
            e.printStackTrace();
        }

        return processRow;
    }

    private Connection initialiseConnection() throws SQLException {

        Connection connection = null;

        try {
            connection = JdbcUtil.createNamedConnection(FacesContext
                    .getCurrentInstance(), "as400");

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;
    }

    private String getProcessTextLines(String textNo) {

        String resultHTML = "<ul class=\"processTextList\">";

        try {
            // System.out.println("ProcessTextLines.java - setting SQL parameter: " + textNo);
            PreparedStatement prep = conn
                    .prepareStatement("SELECT TXSQN,TXDTA FROM LIB1.PRTEXTS WHERE RTRIM(TEXT#) = ? AND TXSQN != '0' ORDER BY TXSQN");
            // supply a value to the PreparedStatement's parameter (the first
            // argument is 1 because it is the first parameter)
            prep.setString(1, textNo);

            ResultSet resultSet = prep.executeQuery();
            while (resultSet.next()) {
                resultHTML += ("<li>" + resultSet.getString("TXDTA").trim() + "</li>");
            }

        } catch (SQLException e) {
            // e.printStackTrace();
        }

        resultHTML += "</ul>";
        return resultHTML;

    }
}

It took me a while because of my lack of Java knowledge but with the pointers @Howard gave plus the few bits and pieces I found on the web I was able to cobble this together.

Opening and closing the SQL connection in the constructor feels counter intuitive to me, but it seems to work.