com.sri.common.util
Class QueryUtils

java.lang.Object
  com.sri.common.util.QueryUtils

public class QueryUtils
extends Object

Title: QueryUtils

Description: Utilities for formatting keywords and where clauses for queries

Note: DBObj.buildWhereClause() basically works for building where clauses, so why do I write similar methods here? There are some issues with buildWhereClause and package protection for it (and various) methods, so I had to do my own methods here. Hopefully a later version of Expresso will make more methods public instead of protected. Here are some of the issues with using DBObject.buildWhereClause() in the version of Expresso we are currently using:

1. ) it only allows AND as a conjunction; no option to do OR, which I need for keyword matches
2. it automatically prepends WHERE to the front (WHERE is added by setWhereClause) so we'd have to manually strip it
3. it's package protected instead of public so all DBObjects you want to call buildWhereClause on will have to implement a new public method that ust calls buildWhereClause

So, I wrote my own below. In some of the methods below I would have liked to pass the DBObject and just look for what fields were set (like buildWhereClause does) but I can't even access getAllFieldsIterator() here (it is package protected also) so I can't loop through the fields in the object to see which ones are set. Aaag! So I just construct and pass arrays with the relevant fields instead.

Author:

Patti Schank and Zaz Harris

Field Summary

static String	DESC_SORT
static String	ENCLOSE_CHAR
static String	ESCAPE_CHAR
static String[]	LIKE_WILDCARDS
static String[]	STRIP_CHARS
static String	UNKNOWN_VALUE

Constructor Summary

QueryUtils()

Method Summary

static String	addAnyRequiredField(String fieldsToRetrieve, String[] requiredFields) Adds necessary fields to fieldsToRetrieve.
static String	alphaLikeClause(String field, String alphaChunk) See comments in other method signature.
static String	alphaLikeClause(String field, String alphaChunk, boolean reverseChunk) Takes a field and an alphabetical "chunk" and constructs a query to check for values for the field passed in starting with characters within that chunk's (inclusive) range.
static String	escapeChar(String value, String charToEsc) Precedes each instance of a character passed in with the escape character in the string passed in.
static String	fieldEqualsManyValuesClause(String field, String[] values) Constructs a SQL where clause checking if a field equals any of the values in the array passed in.
static String	fieldEqualsManyValuesClause(String field, String[] values, boolean caseSensitive, boolean not) Constructs a SQL where clause checking if a field equals any of the values in the array passed in.
static String	fieldEqualsNullClause(String field, boolean not) Generate a subclause that determines if the values is null a given field of an object.
static String	fieldEqualsValueClause(String[] fields, String[] values) Generate a subclause that determines if the values exist for the given fields of an object.
static String	fieldEqualsValueClause(String[] fields, String[] values, boolean useOr, boolean caseSensitive, boolean not) Generate a subclause that determines if the values exist for the given fields of an object.
static String	fieldEqualsValueClause(String field, String value) Generate a subclause that determines if the values exist for the given fields of an object.
static String	fieldEqualsValueClause(String field, String value, boolean caseSensitive, boolean not) Generate a subclause that determines if the values exist for the given fields of an object.
static String	fieldInSetClause(String field, String[] values, boolean not) Generate a subclause that determines if a field has a value in the given set of values.
static String	fieldIsBlankClause(String field, boolean inverse) Returns a REGEXP clause to determine if a field is blank.
static String	fieldLikeValue(String field, String value) Return a LIKE clause of the form LIKE '%%' Returns null if value or field is null.
static String	getBoolStr(String dataContext, boolean value) Returns a boolean string for use in a database query, given the database context found in the request.
static boolean	isValueInAlphaChunk(String alphaChunk, String value, boolean reverseChunk) Return true if the value passed in is in the alphabetical chunk passed in.
static String	keywordMatchClause(String[] keywordArray, String field) Generate a subclause that determines if all of the keywords exist in the given field of an object using AND.
static String	keywordMatchClause(String[] keywordArray, String[] fields) Generate a subclause that determines if all of the keywords exist in the given fields of an object using AND.
static String	keywordMatchClause(String keywordToMatch, String field) Generate a subclause that determines if the keywords exist in the given fields of an object.
static String	keywordMatchClause(String keywordToMatch, String[] fields) Generate a subclause that determines if the keywords exist in the given fields of an object.
static String	prepEqualsValue(String value) Prepares a value to be in a equals clause by escaping the enclosing character and stripping any characters db can't handle.
static String	prepLikeValue(String value) Prepares a value to be in a like clause by escaping the enclosing character and any wildcards and stripping any characters db can't handle.
static String	stripChars(String value) Removes undesirable characters from a string.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

STRIP_CHARS

public static final String[] STRIP_CHARS

LIKE_WILDCARDS

public static final String[] LIKE_WILDCARDS

ESCAPE_CHAR

public static final String ESCAPE_CHAR

See Also:

Constant Field Values

ENCLOSE_CHAR

public static final String ENCLOSE_CHAR

See Also:

Constant Field Values

UNKNOWN_VALUE

public static String UNKNOWN_VALUE

DESC_SORT

public static String DESC_SORT

Constructor Detail

QueryUtils

public QueryUtils()

Method Detail

prepLikeValue

public static String prepLikeValue(String value)

Prepares a value to be in a like clause by escaping the enclosing character and any wildcards and stripping any characters db can't handle. Returns null if value is null.

Parameters:

value - The value to be modified

Returns:

The modified value ready to be used in a like clause

prepEqualsValue

public static String prepEqualsValue(String value)

Prepares a value to be in a equals clause by escaping the enclosing character and stripping any characters db can't handle. Returns null if value is null.

Parameters:

value - The value to be modified

Returns:

The modified value ready to be used in an equals clause

stripChars

public static String stripChars(String value)

Removes undesirable characters from a string. Returns null if value is null.

Parameters:

value - The string value passed in

Returns:

The stripped string

escapeChar

public static String escapeChar(String value,
                                String charToEsc)

Precedes each instance of a character passed in with the escape character in the string passed in.

Parameters:

value - The string to modify

charToEsc - The character precede with the escape character

Returns:

The modified string

fieldEqualsValueClause

public static String fieldEqualsValueClause(String[] fields,
                                            String[] values)

Generate a subclause that determines if the values exist for the given fields of an object. Uses AND and is case sensitive. Could use DBObj.buildWhereClause but that only works within the expresso package and doesn't allow option of OR conjunction! Can't use DBObj methods because they are protected (duh, why?) Use this if you are constructing a custom clause clause for other purposes and need to do an exact match on fields also. Returns a clause of the form

DbObj.FIELDNAME1 = '' AND DbObj.FIELDNAME2 = '' AND ... (repeat for field and value 2...n)

Parameters:

fields - the array of fields

values - the array of values

Returns:

a String with the clause

fieldEqualsValueClause

public static String fieldEqualsValueClause(String[] fields,
                                            String[] values,
                                            boolean useOr,
                                            boolean caseSensitive,
                                            boolean not)

Generate a subclause that determines if the values exist for the given fields of an object. Uses AND by default, unless the parameters specify to use OR. Use this if you are constructing a custom clause clause for other purposes and need to do an exact match on fields also. Returns a clause of the form

DbObj.FIELDNAME1 = '' DbObj.FIELDNAME2 = '' .... (repeat for field and value 2...n)

Parameters:

fields - the array of fields

values - the array of values

useOr - true if the clause should OR results, otherwise it will AND

caseSensitive - true if the field match should be case sensitive

not - If true, matches field that aren't equal

Returns:

a String with the clause

fieldEqualsManyValuesClause

public static String fieldEqualsManyValuesClause(String field,
                                                 String[] values)

Constructs a SQL where clause checking if a field equals any of the values in the array passed in. For example:

((Field1 == 'Value1') OR (Field1 == 'Value2') OR (Field1 == 'Value3'))

where field = "Field1" and value = ["Value1", "Value2", "Value3"]

Parameters:

field - The field to check

values - The values to check the field against

Returns:

The constructed SQL clause; null if not needed

fieldEqualsManyValuesClause

public static String fieldEqualsManyValuesClause(String field,
                                                 String[] values,
                                                 boolean caseSensitive,
                                                 boolean not)

Constructs a SQL where clause checking if a field equals any of the values in the array passed in. For example:

((Field1 == 'Value1') OR (Field1 == 'Value2') OR (Field1 == 'Value3'))

where field = "Field1" and value = ["Value1", "Value2", "Value3"]

Parameters:

field - The field to check

values - The values to check the field against

caseSensitive - If true, query is case sensitive

not - If true, checks if field doesn't equal any of the values

Returns:

The constructed SQL clause; null if not needed

fieldEqualsValueClause

public static String fieldEqualsValueClause(String field,
                                            String value)

Generate a subclause that determines if the values exist for the given fields of an object. Returns a clause of the form

DbObj.FIELDNAME = ''

Parameters:

field - The field of the object to look in

value - The value to look for

Returns:

a String with the clause

fieldEqualsValueClause

public static String fieldEqualsValueClause(String field,
                                            String value,
                                            boolean caseSensitive,
                                            boolean not)

Generate a subclause that determines if the values exist for the given fields of an object. Returns a clause of the form

DbObj.FIELDNAME = ''

Parameters:

field - The field of the object to look in

value - The value to look for

caseSensitive - If true, query is case sensitive

not - If true, query checks if field does NOT equal value

Returns:

a String with the clause

fieldEqualsNullClause

public static String fieldEqualsNullClause(String field,
                                           boolean not)

Generate a subclause that determines if the values is null a given field of an object. Returns a clause of the form

DbObj.FIELDNAME = ''

Parameters:

field - The field of the object to look in

not - If true, query checks if field is NOT null

Returns:

a String with the clause

keywordMatchClause

public static String keywordMatchClause(String[] keywordArray,
                                        String field)

Generate a subclause that determines if all of the keywords exist in the given field of an object using AND. Returns a clause of the form:

( LIKE '' AND LIKE '' AND LIKE '' AND ... )

Returns null if field is null or all keywords to match are null.

Parameters:

keywordArray - The keyword array to match

field - The field to match

Returns:

a String with the clause

keywordMatchClause

public static String keywordMatchClause(String[] keywordArray,
                                        String[] fields)

Generate a subclause that determines if all of the keywords exist in the given fields of an object using AND. Returns a clause of the form:

(( LIKE '' OR LIKE '' OR LIKE '') AND ( LIKE '' OR LIKE '' OR LIKE '') AND ...)

Returns null if all fields are null or all keywords to match are null.

Parameters:

keywordArray - the keyword array to match

fields - The fields to match

Returns:

a String with the clause

keywordMatchClause

public static String keywordMatchClause(String keywordToMatch,
                                        String field)

Generate a subclause that determines if the keywords exist in the given fields of an object. Strips any dangerous characters like single or double quote or wildcard match (%) from clause. Returns a clause of the form:

LIKE '%%'

Returns null if keywordToMatch or field is null.

Parameters:

keywordToMatch - the keyword string to match

field - the field of the object to look in

Returns:

a String with the clause

keywordMatchClause

public static String keywordMatchClause(String keywordToMatch,
                                        String[] fields)

Generate a subclause that determines if the keywords exist in the given fields of an object. Allows unlimited fields to be specified, any of which can be null. Strips any dangerous characters like single or double quote or wildcard match (%) from clause. Returns a clause of the form:

LIKE '%%' LIKE '%%' LIKE '%%' ... )

Returns null if all fields are null or keywordToMatch is null.

Parameters:

keywordToMatch - The keyword string to match

fields - The fields to match

Returns:

a String with the clause

fieldIsBlankClause

public static String fieldIsBlankClause(String field,
                                        boolean inverse)

Returns a REGEXP clause to determine if a field is blank. This is MySQL dependent.

Parameters:

field - The field to check

inverse - If true, checks if field is NOT blank

Returns:

A REGEXP clause

fieldLikeValue

public static String fieldLikeValue(String field,
                                    String value)

Return a LIKE clause of the form LIKE '%%' Returns null if value or field is null.

Parameters:

field - the database object field

value - the value to match

Returns:

A LIKE clause

alphaLikeClause

public static String alphaLikeClause(String field,
                                     String alphaChunk)
                              throws ControllerException

See comments in other method signature.

Parameters:

field - The field to use in the clause

alphaChunk - The alphabetical "chunk" to use as a range

Returns:

The constructed like clause checking for an alphabetical range

Throws:

ControllerException

alphaLikeClause

public static String alphaLikeClause(String field,
                                     String alphaChunk,
                                     boolean reverseChunk)
                              throws ControllerException

Takes a field and an alphabetical "chunk" and constructs a query to check for values for the field passed in starting with characters within that chunk's (inclusive) range.

Valid chunks are three characters in the form A-C, where A is the beginning of the range, C is the end, and - is any placeholder character.

So if field,A-C produces:

((field LIKE 'A%') || (field LIKE 'B%') || (field LIKE 'C%'))

If field or alphaChunk is null, returns null.

Parameters:

field - The field to use in the clause

alphaChunk - The alphabetical "chunk" to use as a range

reverseChunk - If true, the sort is reversed

Returns:

The constructed like clause checking for an alphabetical range

Throws:

ControllerException

getBoolStr

public static String getBoolStr(String dataContext,
                                boolean value)
                         throws DBException

Returns a boolean string for use in a database query, given the database context found in the request.

Parameters:

dataContext - The database context

value - The boolean value

Returns:

A boolean value string for use in a database query

Throws:

DBException

addAnyRequiredField

public static String addAnyRequiredField(String fieldsToRetrieve,
                                         String[] requiredFields)

Adds necessary fields to fieldsToRetrieve.

Parameters:

fieldsToRetrieve - String of fields to retrieve

requiredFields - Required fields

Returns:

Appended fieldsToRetrieve string

isValueInAlphaChunk

public static boolean isValueInAlphaChunk(String alphaChunk,
                                          String value,
                                          boolean reverseChunk)
                                   throws ControllerException

Return true if the value passed in is in the alphabetical chunk passed in.

Parameters:

alphaChunk - The alphabetical chunk

value - The value to check

reverseChunk - If true, the sort is reversed

Returns:

True if the value passed in is in the alphabetical chunk

Throws:

ControllerException

fieldInSetClause

public static String fieldInSetClause(String field,
                                      String[] values,
                                      boolean not)

Generate a subclause that determines if a field has a value in the given set of values.

DbObj.FIELDNAME IN ('','' ...)

Parameters:

field - the field name

values - the array of values

not - If true, matches field that aren't equal

Returns:

a String with the clause