public ArrayList<DocumentNote> searchDocumentNotes( final String searchTerm, final ArrayList<User> users, final ArrayList<DocumentClass> documentClasses) { ArrayList<DocumentNote> result = new ArrayList<DocumentNote>(); kLogger.fine("Searching journal notes for search term =" + searchTerm); try { Connection connection = ConnectionPoolManager.getInstance().getConnection(); String likeClause = ""; StringTokenizer strTok = new StringTokenizer(searchTerm, " "); while (strTok.hasMoreTokens()) { String value = strTok.nextElement().toString().toUpperCase(); value = value.replaceAll("'", "''"); likeClause += "UPPER(NOTEDATA) LIKE '%" + value + "%' OR "; } likeClause += " UPPER(NOTEDATA) = 'XXXXXXXXX'"; String documentClassCriteria = "0"; for (DocumentClass documentClass : documentClasses) { documentClassCriteria += "," + documentClass.getClassId(); } String userNameCriteria = "'PUBLIC'"; for (User user : users) { userNameCriteria += ",'" + user.getUserName() + "'"; } String query = "SELECT ID, NOTEDATA, NOTES.USERNAME, NOTES.CREATED,DOCUMENTS.DOCUMENTID,DOCUMENTS.CLASSID,NOTETYPE FROM " + "NOTES,DOCUMENTCLASSES,DOCUMENTS WHERE (" + likeClause + ") AND " + "DOCUMENTCLASSES.CLASSID=DOCUMENTS.CLASSID " + "AND DOCUMENTS.DOCUMENTID=NOTES.DOCUMENTID AND DOCUMENTCLASSES.CLASSID IN" + "(" + documentClassCriteria + ") AND NOTES.USERNAME IN (" + userNameCriteria + ") AND NOTES.ACTIVE='Y' ORDER BY DOCUMENTCLASSES.CLASSDESC,DOCUMENTS.DOCUMENTID"; Statement statement = connection.createStatement(); ResultSet rs = statement.executeQuery(query); while (rs.next()) { DocumentNote jNote = new DocumentNote(); jNote.setNoteId(rs.getInt("ID")); jNote.setNoteData(rs.getString("NOTEDATA")); jNote.setUserName(rs.getString("USERNAME")); jNote.setCreated(rs.getString("CREATED")); jNote.setDocumentId(rs.getInt("DOCUMENTID")); jNote.setActive(true); jNote.setClassId(rs.getInt("CLASSID")); jNote.setNoteType(rs.getString("NOTETYPE")); result.add(jNote); } rs.close(); statement.close(); connection.close(); } catch (Exception e) { kLogger.warning("Unable to search journal notes for term : " + searchTerm); e.printStackTrace(); } return result; }