Using MySQL Data in XPages

July 3, 2011 – 8:43 am

In todays world, data is not stored in a single place but in different systems and on different platforms. On possibility is a SQL database. Assume, you want to access this data and use it in your XPages application.

I have created a new project and contributed the code on OpenNTF.

The javascript lib contains a single function “getSQLData()” that establishes the connection and retrieves the data acording to the connection parameters and SQL statement you provide.

The resultset can then be used in a XPages DataTable control. Simply bind the control to the “getSQLData()” function. This also works in a repeat control or a combo box.

Use “Select * from people” to retrieve all columns in the table; you can also use “Select FIRSTNAME, LASTNAME from people” to return only specific values.

Here is some sample code for custom control containing a DataTable using the result from an SQL statement as source.

<?xml version="1.0" encoding="UTF-8"?>
<xp:view xmlns:xp="http://www.ibm.com/xsp/core">
<xp:this.resources>
<xp:script src="/ssMysql.jss" clientSide="false" />
</xp:this.resources>
<xp:dataTable id="dataTable1" rows="30"
value="#{javascript:getSQLData();}" var="rs">
<xp:column id="column1">
<xp:this.facets>
<xp:span xp:key="header" style="font-weight:bold">FirstName</xp:span>
</xp:this.facets>
<xp:text escape="true" id="firstname">
<xp:this.value><![CDATA[#{javascript:rs[1]}]]></xp:this.value>
</xp:text>
</xp:column>
<xp:column id="column2">
<xp:this.facets>
<xp:span xp:key="header" style="font-weight:bold">LastName</xp:span>
</xp:this.facets>
<xp:text escape="true" id="lastname">
<xp:this.value><![CDATA[#{javascript:rs[2]}]]></xp:this.value>
</xp:text>
</xp:column>
<xp:column id="column3">
<xp:this.facets>
<xp:span xp:key="header" style="font-weight:bold">
Country
</xp:span>
</xp:this.facets>
<xp:text escape="true" id="computedField1">
<xp:this.value><![CDATA[#{javascript:rs[3]}]]></xp:this.value>
</xp:text>
</xp:column>
<xp:column id="column4">
<xp:this.facets>
<xp:span xp:key="header" style="font-weight:bold">Age</xp:span>
</xp:this.facets>
<xp:text escape="true" id="computedField2">
<xp:this.value><![CDATA[#{javascript:rs[4]}]]></xp:this.value>
</xp:text>
</xp:column>
</xp:dataTable>

</xp:view>

And here is the code for the XPage

<?xml version="1.0" encoding="UTF-8"?>
<xp:view xmlns:xp="http://www.ibm.com/xsp/core" xmlns:xc="http://www.ibm.com/xsp/custom">
<xc:ccSQLViewSample SQLQuery="Select * from people">
<xc:this.connection>
<xc:connection port="3306" db="xtest" password="password"
server="localhost" username="root" />
</xc:this.connection>
</xc:ccSQLViewSample>
</xp:view>

And finally here is the output in the browser

If you want to access data from a DB2 datasource instead of MySQL, simply use the appropriate driver.

  1. 4 Responses to “Using MySQL Data in XPages”

  2. awesome… how is this working from the performance perspective?

    By Hora_ce on Jul 3, 2011

  3. I am already using MySQL as datastore, but what I need is persistent and/or multiple connections.
    Looking into linking xPages to a native Java servlet.

    By Vince Schuurman on Jul 3, 2011

  4. Hello,

    Very useful! Some comments:

    1. I have run into out of memory issues in the past when attaching relatively big jar files within my NSF directly – especially when called from client-side agents. I have found that putting jar resources in ‘X:\Program Files\IBM\Lotus\Domino\jvm\lib\ext’ (for browser clients) solved those issues.
    2. Performance – you should consider using a connection pooling mechanism. I am using hibernate. Creating a brand new connection per request is very expensive!

    @Vince: instead of native Java servlets you may want to look at the following:
    1. http://www.openntf.org/internal/home.nsf/response.xsp?action=openDocument&documentId=00A38CDF382F4893862578B80082DD15&MainID=63E16EDCA59F2CF4862578AE00502D2F (This is a new feature in 8.5.3)
    2. http://www.openntf.org/internal/home.nsf/project.xsp?action=openDocument&name=Servlet%20Sample

    By Pieter Malan on Jul 5, 2011

  5. This is great stuff, had no problems with it, except it is not yet running on my dev server. It does not seem to return a resultset, I get a blank Xpage form.

    …other than that localhost version is smooth-sailing. The project is well-documented for trials, good going on that! Can’t wait to see more…

    Köll

    By Köll on Jul 15, 2011

Sorry, comments for this entry are closed at this time.