Hello Roller Dev,
I have developed a jsp for Roller that provides a search interface for
finding users in large blogging communities. The interface returns a
list of bloggers starting with the string as a user types it. It is
modeled on Google Suggest:
http://www.google.com/search?complete=1&hl=en
I have attached both the jsp and a sample AJAX html/javascript page that
interfaces with it. I developed this against Roller 1.3. The jsp just
needs to reside in the /webapps/roller directory. Because the jsp does
a direct sql query it does not use the persistence layer. It does
however store the results using the LRUCache2 in up to 100 unique cached
lists. The cached lists are keyed on initial search char+column
searched. The interface allows users to search on username, full name
or blog title. If users hit return on a highlighted result, the same
jsp fetches their rss feed and displays it below the search results.
So far, I have only tested the javascript against Mozilla 1.7 and Opera
8.01 for Solaris. I have noticed that there are some problems with the
textbox character highlighting and cursor position in Opera.
Looking forward to any advice on how to proceed or comments/suggestions,
Thank you,
John Hoffmann
http://blogs.sun.com/roller/page/hoffie
<%@ page import="javax.naming.*,java.sql.*,javax.sql.*,java.net.*,java.lang.*,
java.io.*,java.util.*,javax.servlet.*,javax.servlet.http.*,
org.roller.util.LRUCache2" session="false" %>
<%
/**
Author: hoffie [EMAIL PROTECTED]
Purpose: To provide a user listing of bloggers
Arguments: Takes an input string: username
and a column to search against: column
Approach: Perform a sql query on the first character only
then use java startsWith to trim the list to
exact matches
**/
%>
<%!
public class CachedSet {
ArrayList weblogTitle;
ArrayList entryCount;
ArrayList blogHandle;
ArrayList fullName;
public CachedSet() {
this.weblogTitle = new ArrayList();
this.entryCount = new ArrayList();
this.blogHandle = new ArrayList();
this.fullName = new ArrayList();
}
public void writeRow(String weblogTitle, String entryCount,
String blogHandle, String fullName) {
this.weblogTitle.add(weblogTitle);
this.entryCount.add(entryCount);
this.blogHandle.add(blogHandle);
this.fullName.add(fullName);
}
public int getSize() {
return this.weblogTitle.size();
}
public String getWeblogTitle(int rowNumber) {
return (String)this.weblogTitle.get(rowNumber);
}
public String getEntryCount(int rowNumber) {
return (String)this.entryCount.get(rowNumber);
}
public String getBlogHandle(int rowNumber) {
return (String)this.blogHandle.get(rowNumber);
}
public String getFullName(int rowNumber) {
return (String)this.fullName.get(rowNumber);
}
}
final long CACHE_TIMEOUT = 1000*60*60; // 1 hour
final int ALPHA_NUMERICS_CACHE_SIZE = 3*(26+10); // 3 columns times (a-z + 0-9)
final int WEBLOG_TITLE = 1;
final int ENTRY_COUNT = 2;
final int BLOG_HANDLE = 3;
final int FULL_NAME = 4;
LRUCache2 suggestCache = new LRUCache2(ALPHA_NUMERICS_CACHE_SIZE,CACHE_TIMEOUT);
// end of static block
%>
<%
CachedSet displayCachedSet = new CachedSet();
String queryStartsWithParam = "A";
String queryStartsWithChar = "A";
String queryColumn = "username";
String likeClause = "";
queryColumn = request.getParameter("column");
if ("".equals(queryColumn)) {
queryColumn = "username";
}
queryStartsWithParam = request.getParameter("startswith").toUpperCase();
if (null != queryStartsWithParam &&
queryStartsWithParam.indexOf("%") == -1 &&
queryStartsWithParam.length() > 0) {
queryStartsWithParam = queryStartsWithParam.replaceAll("\\p{Punct}","");
if (queryStartsWithParam.length() > 0) {
queryStartsWithChar = queryStartsWithParam.substring(0,1);
}
if (queryColumn.equals("fullname")) {
likeClause = "upper(rolleruser.fullname) like '"+queryStartsWithChar+"%'
";
} else if (queryColumn.equals("title")) {
likeClause = "upper(website.name) like '"+queryStartsWithChar+"%' ";
} else {
likeClause = "upper(rolleruser.username) like '"+queryStartsWithChar+"%'
";
}
String key = queryColumn+"-"+queryStartsWithChar;
if (suggestCache.get(key) == null) {
final String sqlSelect = "select "+
" website.name as weblog_title, "+
" count(*) as blog_count, "+
" rolleruser.username, "+
" rolleruser.fullname "+
"from"+
" weblogentry, website, rolleruser "+
"where "+
" rolleruser.id = website.userid and "+
" website.id = weblogentry.websiteid and "+
likeClause +
"group by "+
" rolleruser.username "+
"order by "+
" blog_count desc";
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try {
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/rollerdb");
conn = ds.getConnection();
stmt = conn.createStatement();
rset = stmt.executeQuery(sqlSelect);
// Loop through the result set copying the data to custom result set
while (rset.next()) {
displayCachedSet.writeRow(rset.getString(WEBLOG_TITLE),
rset.getString(ENTRY_COUNT),
rset.getString(BLOG_HANDLE),
rset.getString(FULL_NAME));
}
// Store the custom result set in the cache
suggestCache.put(key,displayCachedSet);
} catch (SQLException SQLe) {
out.println("<P><B><font color='red'>Database error.</B></font>");
out.println("<P>Stack Trace:<P>"+SQLe);
} catch (Exception e) {
out.println("<P><B><font color='red'>General error.</B></font>");
out.println("<P>Stack Trace:<P>"+e);
} finally {
stmt.close();
conn.close();
}
} else {
// Had a cache hit so use it
displayCachedSet = (CachedSet)suggestCache.get(key);
}
// Now output from the custom result set
%>
<xml>
<%
// Iterate through the cachedSet and print the user data
int cachedSetSize = displayCachedSet.getSize();
int blogger = 0;
while (blogger < cachedSetSize) {
if (
( queryColumn.equals("username") &&
displayCachedSet.getBlogHandle(blogger).toUpperCase().startsWith(queryStartsWithParam)
) ||
( queryColumn.equals("fullname") &&
displayCachedSet.getFullName(blogger).toUpperCase().startsWith(queryStartsWithParam)
) ||
( queryColumn.equals("title") &&
displayCachedSet.getWeblogTitle(blogger).toUpperCase().replaceAll("\\p{Punct}","").startsWith(queryStartsWithParam)
)
) {
%>
<blogger>
<weblog_title><%=displayCachedSet.getWeblogTitle(blogger)%></weblog_title>
<entry_count><%=displayCachedSet.getEntryCount(blogger)%></entry_count>
<blog_handle><%=displayCachedSet.getBlogHandle(blogger)%></blog_handle>
<full_name><%=displayCachedSet.getFullName(blogger)%></full_name>
</blogger>
<%
}
blogger++;
}
%>
</xml>
<%
}
%>
