Searching with only some criteria
I have a database table with six columns, all of which are nullable.
The user has a search screen with one input for each column,
and I'd like to return all matching values. I'm using a prepared
statement that looks something like:
select *
from MY_TABLE
where COLUMN1 like ?
and COLUMN2 like ?
...
My problem arises when a user doesn't enter values for all six
search conditions. I can't put in "" or null for the parameters, because
then it will explicitly search for those values. I can't not call setString()
on the column numbers I'm not interested in.
Right now I'm stuck with:
privatefinalstatic String SEARCH =
"select * from TABLE where";
publicvoid myMethod(){
final StringBuffer buffer =new Stringbuffer(SEARCH);
final String name = httpRequest.getParameter("name").trim();
if (!name.equals("")){
buffer.append("NAME like '");
buffer.append(name);
buffer.append("'");
}
// magic 'and' insertion here if required
final String id = httpRequest.getParameter("id").trim();
if (!id.equals("")){
buffer.append("ID like '");
buffer.append(id);
buffer.append("'");
}
}
That gets really ugly over multiple items. Is there no better way?
My coworkers insist this is my only choice, but I don't believe them.
Thanks!
[2235 byte] By [
es5f2000a] at [2007-11-27 3:50:15]
# 7
I wound up writing a helper class to handle the situation. Right now
it only works with Strings.
/**
*
This class wraps any search query with a dynamic 'where' clause.
*
*
Create a ConditionalQuery instance with the static clauses which
* appear before ("select FOO from BAR") and after ("order by BAZ") your
* dynamic 'where' clause. For each search parameter you may be using,
* invoke addCondition() with the column name and the search value. If
* the value is "", it will be ignored. Otherwise, the value will be
* added to the search criteria.
*
*
Once you have passed in all criteria, invoke getStatement() to
* get a PreparedStatement you can query. The values for the query
* parameters will already be added to the PreparedStatement.
*
*
Sample usage:
* <code><pre>
private static final String baseQuery = "select MAIL from LD_USERS_DUMP";
private static final String orderClause = "order by MAIL";
public void getEmailAddress(final String lastName, final String comitID) {
final ConditionalQuery query =
new ConditionalQuery(baseQuery, orderClause);
query.addCondition("SN", lastName);
query.addCondition("LDAP_UID", comitID);
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = DatabaseFactory.getConnection("Oracle");
preparedStatement = query.getStatement(connection);
resultSet = preparedStatement.executeQuery();
} catch (final SQLException se) {
LOGGER.error("Database error.", se);
} finally {
DatabaseUtilities.close(resultSet);
DatabaseUtilities.close(preparedStatement);
DatabaseUtilities.close(connection);
}
}
</pre></code>
*
*
This class is final because it is not designed to be extended.
*
This class is <strong>not</strong> thread-safe.
*
* @author Eric Stein (xbbjgns)
*/
public final class ConditionalQuery {
/**
* Contains the contents of this query up to the end of the
* 'where' clause.
*/
private final StringBuffer query = new StringBuffer();
/** Clause which goes after the 'where' clause. May not be null.*/
private final String postConditionClause;
/**
* String list of condition values, in the order they were added. */
private final List values = new ArrayList();
/**
* True if this query has one or more conditions, false if there
* are zero conditions.
*/
private boolean hasConditions = true;
/**
* Constructor.
* @param baseQuery clause to put before the 'where' clause in
* the query. May not be null.
* @throws NullPointerException if the baseQuery parameter is null.
*/
public ConditionalQuery(final String baseQuery)
throws NullPointerException {
this(baseQuery, "");
}
/**
* Constructor.
* @param baseQuery clause to put before the 'where' clause in
* the query. May not be null.
* @param postConditionClause clause to put after the 'where' clause
* in the query. May not be null.
* @throws NullPointerException if either parameter is null.
*/
public ConditionalQuery(final String baseQuery,
final String postConditionClause)
throws NullPointerException {
ValidationUtilities.checkNotNull(baseQuery, "baseQuery");
ValidationUtilities.checkNotNull(postConditionClause,
"postConditionClause");
this.query.append(baseQuery.trim());
this.query.append(" ");
this.postConditionClause = postConditionClause;
}
/**
* Adds a condition to this query. If the value to search for is
* "", this method will do nothing. If there is a search value,
* the column and value get added to the prepared statement. By
* default, conditions are <strong>not</strong> case-sensitive.
* @param columnName the name of the column a condition is being
* added for. May not be null.
* @param value the value for the condition being added to the query.
* May not be null.
* @throws NullPointerException if either parameter is null
*/
public void addCondition(final String columnName, final String value)
throws NullPointerException {
this.addCondition(columnName, value, false);
}
/**
* Adds a condition to this query. If the value to search for is
* "", this method will do nothing. If there is a search value,
* the column and value get added to the prepared statement.
* @param columnName the name of the column a condition is being
* added for. May not be null.
* @param value the value for the condition being added to the query.
* May not be null.
* @param caseSensitive true for string comparisons to be
* case-sensitive, false for them to ignore case.
* @throws NullPointerException if the columnName or value parameters
* are null.
*/
public void addCondition(final String columnName, final String value,
boolean caseSensitive)
throws NullPointerException {
ValidationUtilities.checkNotNull(columnName, "columnName");
ValidationUtilities.checkNotNull(value, "value");
final String trimmedValue = value.trim();
if (trimmedValue.equals("")) {
return;
}
if (this.hasConditions) {
this.query.append("and ");
} else {
this.query.append("where ");
this.hasConditions = true;
}
if (caseSensitive) {
this.query.append(columnName);
} else {
this.query.append("upper(");
this.query.append(columnName);
this.query.append(") ");
}
this.query.append(" like ");
if (caseSensitive) {
this.query.append("? ");
} else {
this.query.append("upper(?) ");
}
this.values.add(value);
}
/**
* Creates a PreparedStatement from this query. The parameters
* for the PreparedStatement are already set.
* query. Will never return null.
* @param connection the connection a prepared statement is being
* created for. May not be null.
* @return a PreparedStatement whose values are set. Will never return
* null.
* @throws NullPointerException if the connection parameter is null.
* @throws SQLException if an error occurs while preparing the
* statement.
*/
public PreparedStatement getStatement(final Connection connection)
throws NullPointerException, SQLException {
ValidationUtilities.checkNotNull(connection, "connection");
final PreparedStatement statement =
connection.prepareStatement(this.getQuery());
for (int i = 0; i < this.values.size(); i++) {
statement.setString(i + 1, (String) this.values.get(i));
}
return statement;
}
/**
* Returns true if this query has at least one search condition,
* or false if it has no conditions.
* @return true if this query has at least one search condition,
* or false if it has no conditions.
*/
public boolean hasConditions() {
return this.hasConditions;
}
/**
* Returns a string representation of this query.
* @return a string representation of this query. Will never
* return null.
*/
public String getQuery() {
return this.query.toString() + this.postConditionClause;
}
}
