The following call should be sufficient to run a basic SQL query (e.g. Select …) in Java versus an established database, tested against TeamSite 6.2, LiveSite 3.1.
Useage is described towards the end of the article.
package com.littleforest.examples;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.dom4j.Document;
import org.dom4j.Element;
import com.interwoven.livesite.dom4j.Dom4jUtils;
import com.interwoven.livesite.external.ExternalUtils;
import com.interwoven.livesite.external.ParameterHash;
import com.interwoven.livesite.runtime.RequestContext;
import com.interwoven.livesite.runtime.servlet.RequestUtils;
import com.littleforest.examples.WebUtils;
public class SQLUtils {
//protected static final Logger logger = Logger.getLogger(SQLUtils.class);
public static final String ELEMENT_ROW = "Row";
public static final String ELEMENT_SQLSTATEMENT = "sqlStatement";
/**
* getSQL Requred parameter: Pool = The SQL pool to run against Required
* parameter: cs = The sql query to execute XPath: //external
*
* @param context Request
* @return Document
*/
public static Document getSQL(RequestContext context) {
// create new document
System.out.println("--------------------------------------------------------------------------------");
System.out.println("START: getSQL");
System.out.println("--------------------------------------------------------------------------------");
Document doc = Dom4jUtils.newDocument("");
String pool = context.getParameterString("Pool");
String formatted = context.getParameterString("formatted");
String removePAGE_LINK = context.getParameterString("stripPageLink");
boolean removePageLink = false;
if ((formatted == null) || (formatted.length() == 0)) {
formatted = "formatted";
}
if (null == pool) { return ExternalUtils.newErrorDocument("executeSqlQuery: Parameter 'pool' not found."); }
if (removePAGE_LINK != null && !removePAGE_LINK.equals("")) {
removePageLink = true;
}
// check we've got a SQL string
String sqlStatement = context.getParameterString("cs");
if (null == sqlStatement) { return ExternalUtils.newErrorDocument("executeSqlQuery: Parameter 'cs' not found."); }
Element siteElem = doc.getRootElement().addElement("site");
siteElem.addAttribute("name", context.getSite().getName());
siteElem.addAttribute("path", context.getSite().getPath());
siteElem.addAttribute("branch", context.getSite().getBranch());
if (formatted.equalsIgnoreCase("raw")) {
System.out.println("Passing raw SQL to getSQLStatement");
sqlStatement = getSQLStatement(context.getParameters(), sqlStatement, "sqlstring", "raw");
} else {
System.out.println("Passing formatted SQL to getSQLStatement");
sqlStatement = getSQLStatement(context.getParameters(), sqlStatement, "sqlstring", "formatted");
}
if (removePageLink) {
String overridePageRegex = "^$PAGE_LINK[(.+)]$";
Pattern overridePagePattern = Pattern.compile(overridePageRegex);
Matcher overridePageMatcher = overridePagePattern.matcher(sqlStatement);
while (overridePageMatcher.find()) {
if (overridePageMatcher.group(1) != null) {
sqlStatement = overridePageMatcher.group(1);
}
}
}
System.out.println("Final SQL: " + sqlStatement);
Document resultSets = runSQL(pool, sqlStatement);
doc.getRootElement().add(resultSets.getRootElement().detach());
System.out.println("--------------------------------------------------------------------------------");
System.out.println("END: getSQL");
System.out.println("--------------------------------------------------------------------------------");
return WebUtils.exposeData(context, doc);
}
/**
* This is simple convenience method to close a database connection and
* ignore an exception that is thrown if the connection is not open. If you
* pass null instead of a connection instance, the method will simply
* return.
*
* @param conn The connection instance.
*/
private static void closeConnection(Connection conn) {
if (conn == null) return;
try {
conn.close();
} catch (SQLException sqlEx) {
}
;
}
/**
* getSQLStatement - creates the SQL statement from the statement passed in
* and by substituting all occurences of {PARAMNAME} with the value of the
* parameter
*
* @param context Request
* @return Document
*/
private static String getSQLStatement(ParameterHash params, String sqlStatement, String type, String method) {
System.out.println(" Start getSQLStatement");
sqlStatement = parseForSelectMultiples(sqlStatement, params);
System.out.println(" parseforSelectMultiples returned: " + sqlStatement);
String pageLinkRegex = "^$PAGE_LINK[(.+)]$";
Pattern pageLinkPattern = Pattern.compile(pageLinkRegex);
Matcher pageLinkMatcher;
// get iterator of params
Iterator it = params.keySet().iterator();
while (it.hasNext()) {
String key = it.next().toString();
System.out.println(" Param key: " + key);
Object value = params.get(key);
// prepare the parameters
if (value == null) {
System.out.println(" value is null");
// no param for this found so forget it
} else if (value instanceof ArrayList) {
System.out.println(" value is ArrayList");
ArrayList vals = (ArrayList) value;
String newString = "";
for (int i = 0; i < vals.size(); i++) {
if (i > 0) {
newString += ",";
}
newString += (String) vals.get(i);
}
pageLinkMatcher = pageLinkPattern.matcher(newString);
while (pageLinkMatcher.find()) {
if (pageLinkMatcher.group(1) != null) {
newString = pageLinkMatcher.group(1);
}
}
System.out.println(" pageLinkMatcher regex returned: " + newString);
// newString.replaceAll( "'", "''" ) - replaces the freakin single quote to be two - a sql thing
if (method.equalsIgnoreCase("raw")) {
System.out.println(" raw sqlStatement pre-repleace: " + sqlStatement);
sqlStatement = sqlStatement.replaceFirst(("{" + key + "}"), newString);
System.out.println(" raw sqlStatement post-replace: " + sqlStatement);
} else {
System.out.println(" formatted sqlStatement pre-repleace: " + sqlStatement);
sqlStatement = sqlStatement.replaceFirst(("{" + key + "}"), "'" + newString.replaceAll("'", "''") + "'");
System.out.println(" formatted sqlStatement post-replace: " + sqlStatement);
}
} else if (!(value.toString().equalsIgnoreCase("Pool") || value.toString().equalsIgnoreCase("cs"))) {
System.out.println(" value is String");
// newString.replaceAll( "'", "''" ) - replaces the freakin single quote to be two - a sql thing
String valueString = value.toString();
pageLinkMatcher = pageLinkPattern.matcher(valueString);
while (pageLinkMatcher.find()) {
if (pageLinkMatcher.group(1) != null) {
valueString = pageLinkMatcher.group(1);
}
}
System.out.println(valueString);
if (method.equalsIgnoreCase("raw")) {
sqlStatement = sqlStatement.replaceFirst(("{" + key + "}"), valueString);
} else {
sqlStatement = sqlStatement.replaceFirst(("{" + key + "}"), "'" + valueString.replaceAll("'", "''") + "'");
}
}
}
if (!method.equalsIgnoreCase("raw")) {
// replace any unmatched params with nulls
sqlStatement = sqlStatement.replaceAll("{(.*?)}", "null");
}
if (type.equals("sproc")) {
sqlStatement = "{call " + sqlStatement + "}";
} else {
//leave it as it is
}
return sqlStatement;
}
}
Note this is depend on a secondary library, shown:
import com.littleforest.examples.WebUtils
This class is a modification of the out of the box Interwoven code containing various Web related external calls. It adds new abilities to Get and Post data and is available on request from www.littleforest.co.uk
There are two other pre-requisites before this querying is functional.
- It will be necessary to download the MS SQL jdbc jar, and place it in {IWHOME}/local/config/lib/content_center/livesite_customer_src/lib folder (see www.findjar.com if required)
- Also change the database.properties files under {IWHOME}/local/config/lib/content_center/livesite_customer_src/etc/conf/livesite_customer. There should be already example for MS SQL jdbc connection, just change the name, port, username and password.
And livesite_customer_src recompiled
The Database Querying can be run by setting up a new LiveSite component and detailing the following section in Content XML:
livesite
SELECT PATH, TITLE, CONTENT from NEWS WHERE PATH like '%{whereParameter}%'
raw
/default/main/Fidelity/EMEA/UK/Marketing/WORKAREA/Content/templatedata/
getSQL
The key parameters used here are:
ID=”formatted”: formatted SQL or raw SQL statement, assumed default is raw but must be set explicit.
ID=”pool”: livesite is value usually assigned (required), consult www.littleforest.co.uk for alternative values
ID=”cs”: select statement used.
ID=”whereParameter”: allows to pass in specific TeamSite locations e.g. workarea/paths etc if required
