If you spend enough time working with XPages, you're eventually going to want to access some SQL data, either because of an integration project or because you just want to. It's pretty well-trodden ground, but, in one of my recent projects, I came up with an approach I rather like.
Now, before I get into it, fair warning: this is not scalable, efficient, or good programming practice. This is for when you just want to do a quick query and get back data in a usable fashion. It doesn't handle paging properly and it can easily stomp all over separation of concerns. But if your needs are simple, it may do the job perfectly.
Basically, the problem I had was this: I want to do a couple quick SQL "select" queries on an XPage, but I got tired of writing out similar "stmt = conn.prepareStatement(...); stmt.setString(...)" stuff over and over. Ideally, the solution would be to abstract it all away, but it was a small page deserving of a small fix, so I wrote myself a function:
SQLTools = {}
SQLTools.fetchQuery = function(query, args) {
args = args == null ? [] : args
var stmt = SQL.getConnection().prepareStatement(query)
for(var i = 0; i < args.length; i++) {
stmt.setObject(i+1, args[i])
}
var rs = stmt.executeQuery()
var cols = []
var meta = rs.getMetaData()
for(var i = 0; i < meta.getColumnCount(); i++) {
cols.push({
name: meta.getColumnName(i+1),
type: meta.getColumnType(i+1)
})
}
var result = []
while(rs.next()) {
var row = {}
for(var i = 0; i < cols.length; i++) {
row[cols[i].name] = rs.getObject(cols[i].name)
}
result.push(row)
}
return result
}
The actual job of connecting to the SQL DB with the right driver is handled by a Java bean called SQL - the aforementioned link covers that part pretty well. What makes this code different is how easy it makes a quick query with some interleaved parameters and how useful the resultant value is for standard XPage controls. For example, you could do a search like this...
SQLTools.fetchQuery("select firstname,lastname,degree from people where age > ? and lastname like ?", [30, "%son"])
...and you'd get back an array containing column-keyed hashes. Your XPage doesn't have to care about ResultSets or ".getString()"s or anything. You could end up with relatively clean code like:
<xp:repeat var="person">
<xp:this.value><![CDATA[#{javascript:
SQLTools.fetchQuery("select firstname,lastname,degree from people where age > ? and lastname like ?", [30, "%son"])
}]]></xp:this.value>
<xp:div><xp:text value="#{person.firstname}"/> <xp:text value="#{person.lastname}"/>, <xp:text value="#{person.degree}"/></xp:div>
</xp:repeat>
That's probably not a final product for displaying the information, but it gets the point across - it's a pretty clean traversal from "I want to query the database" to having XPages-friendly Server JavaScript objects.
