com.j256.ormlite.stmt.Where Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of ormlite-core Show documentation
Show all versions of ormlite-core Show documentation
Lightweight Object Relational Model (ORM) for persisting objects to SQL databases.
package com.j256.ormlite.stmt;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.j256.ormlite.dao.CloseableIterator;
import com.j256.ormlite.dao.Dao;
import com.j256.ormlite.dao.GenericRawResults;
import com.j256.ormlite.db.DatabaseType;
import com.j256.ormlite.field.FieldType;
import com.j256.ormlite.stmt.QueryBuilder.InternalQueryBuilderWrapper;
import com.j256.ormlite.stmt.query.Between;
import com.j256.ormlite.stmt.query.Clause;
import com.j256.ormlite.stmt.query.Exists;
import com.j256.ormlite.stmt.query.In;
import com.j256.ormlite.stmt.query.InSubQuery;
import com.j256.ormlite.stmt.query.IsNotNull;
import com.j256.ormlite.stmt.query.IsNull;
import com.j256.ormlite.stmt.query.ManyClause;
import com.j256.ormlite.stmt.query.NeedsFutureClause;
import com.j256.ormlite.stmt.query.Not;
import com.j256.ormlite.stmt.query.Raw;
import com.j256.ormlite.stmt.query.SimpleComparison;
import com.j256.ormlite.table.TableInfo;
/**
*
* Manages the various clauses that make up the WHERE part of a SQL statement. You get one of these when you call
* {@link StatementBuilder#where} or you can set the where clause by calling {@link StatementBuilder#setWhere}.
*
*
*
* Here's a page with a good tutorial of SQL commands.
*
*
*
* To create a query which looks up an account by name and password you would do the following:
*
*
*
* QueryBuilder<Account, String> qb = accountDao.queryBuilder();
* Where where = qb.where();
* // the name field must be equal to "foo"
* where.eq(Account.NAME_FIELD_NAME, "foo");
* // and
* where.and();
* // the password field must be equal to "_secret"
* where.eq(Account.PASSWORD_FIELD_NAME, "_secret");
* PreparedQuery<Account, String> preparedQuery = qb.prepareQuery();
*
*
*
* In this example, the SQL query that will be generated will be approximately:
*
*
*
* SELECT * FROM account WHERE (name = 'foo' AND passwd = '_secret')
*
*
*
* If you'd rather chain the methods onto one line (like StringBuilder), this can also be written as:
*
*
*
* queryBuilder.where().eq(Account.NAME_FIELD_NAME, "foo").and().eq(Account.PASSWORD_FIELD_NAME, "_secret");
*
*
*
* If you'd rather use parens and the like then you can call:
*
*
*
* Where where = queryBuilder.where();
* where.and(where.eq(Account.NAME_FIELD_NAME, "foo"), where.eq(Account.PASSWORD_FIELD_NAME, "_secret"));
*
*
*
* All three of the above call formats produce the same SQL. For complex queries that mix ANDs and ORs, the last format
* will be necessary to get the grouping correct. For example, here's a complex query:
*
*
*
* Where where = queryBuilder.where();
* where.or(where.and(where.eq(Account.NAME_FIELD_NAME, "foo"), where.eq(Account.PASSWORD_FIELD_NAME, "_secret")),
* where.and(where.eq(Account.NAME_FIELD_NAME, "bar"), where.eq(Account.PASSWORD_FIELD_NAME, "qwerty")));
*
*
*
* This produces the following approximate SQL:
*
*
*
* SELECT * FROM account WHERE ((name = 'foo' AND passwd = '_secret') OR (name = 'bar' AND passwd = 'qwerty'))
*
*
* @author graywatson
*/
public class Where {
private final static int CLAUSE_STACK_START_SIZE = 4;
private final TableInfo tableInfo;
private final StatementBuilder statementBuilder;
private final FieldType idFieldType;
private final String idColumnName;
private final DatabaseType databaseType;
private Clause[] clauseStack = new Clause[CLAUSE_STACK_START_SIZE];
private int clauseStackLevel;
private NeedsFutureClause needsFuture = null;
protected Where(TableInfo tableInfo, StatementBuilder statementBuilder, DatabaseType databaseType) {
// limit the constructor scope
this.tableInfo = tableInfo;
this.statementBuilder = statementBuilder;
this.idFieldType = tableInfo.getIdField();
if (idFieldType == null) {
this.idColumnName = null;
} else {
this.idColumnName = idFieldType.getColumnName();
}
this.databaseType = databaseType;
}
/**
* AND operation which takes the previous clause and the next clause and AND's them together.
*/
public Where and() {
ManyClause clause = new ManyClause(pop("AND"), ManyClause.AND_OPERATION);
push(clause);
addNeedsFuture(clause);
return this;
}
/**
* AND operation which takes 2 (or more) arguments and AND's them together.
*
*
* NOTE: There is no guarantee of the order of the clauses that are generated in the final query.
*
*
* NOTE: I can't remove the generics code warning that can be associated with this method. You can instead
* use the {@link #and(int)} method.
*
*/
public Where and(Where first, Where second, Where... others) {
Clause[] clauses = buildClauseArray(others, "AND");
Clause secondClause = pop("AND");
Clause firstClause = pop("AND");
addClause(new ManyClause(firstClause, secondClause, clauses, ManyClause.AND_OPERATION));
return this;
}
/**
* This method needs to be used carefully. This will absorb a number of clauses that were registered previously with
* calls to {@link Where#eq(String, Object)} or other methods and will string them together with AND's. There is no
* way to verify the number of previous clauses so the programmer has to count precisely.
*
*
* NOTE: There is no guarantee of the order of the clauses that are generated in the final query.
*
*
*
* NOTE: This will throw an exception if numClauses is 0 but will work with 1 or more.
*
*/
public Where and(int numClauses) {
if (numClauses == 0) {
throw new IllegalArgumentException("Must have at least one clause in and(numClauses)");
}
Clause[] clauses = new Clause[numClauses];
for (int i = numClauses - 1; i >= 0; i--) {
clauses[i] = pop("AND");
}
addClause(new ManyClause(clauses, ManyClause.AND_OPERATION));
return this;
}
/**
* Add a BETWEEN clause so the column must be between the low and high parameters.
*/
public Where between(String columnName, Object low, Object high) throws SQLException {
addClause(new Between(columnName, findColumnFieldType(columnName), low, high));
return this;
}
/**
* Add a '=' clause so the column must be equal to the value.
*/
public Where eq(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.EQUAL_TO_OPERATION));
return this;
}
/**
* Add a '>=' clause so the column must be greater-than or equals-to the value.
*/
public Where ge(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.GREATER_THAN_EQUAL_TO_OPERATION));
return this;
}
/**
* Add a '>' clause so the column must be greater-than the value.
*/
public Where gt(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.GREATER_THAN_OPERATION));
return this;
}
/**
* Add a IN clause so the column must be equal-to one of the objects from the list passed in.
*/
public Where in(String columnName, Iterable objects) throws SQLException {
addClause(new In(columnName, findColumnFieldType(columnName), objects, true));
return this;
}
/**
* Same as {@link #in(String, Iterable)} except with a NOT IN clause.
*/
public Where notIn(String columnName, Iterable objects) throws SQLException {
addClause(new In(columnName, findColumnFieldType(columnName), objects, false));
return this;
}
/**
* Add a IN clause so the column must be equal-to one of the objects passed in.
*/
public Where in(String columnName, Object... objects) throws SQLException {
return in(true, columnName, objects);
}
/**
* Same as {@link #in(String, Object...)} except with a NOT IN clause.
*/
public Where notIn(String columnName, Object... objects) throws SQLException {
return in(false, columnName, objects);
}
/**
* Add a IN clause which makes sure the column is in one of the columns returned from a sub-query inside of
* parenthesis. The QueryBuilder must return 1 and only one column which can be set with the
* {@link QueryBuilder#selectColumns(String...)} method calls. That 1 argument must match the SQL type of the
* column-name passed to this method.
*
*
* NOTE: The sub-query will be prepared at the same time that the outside query is.
*
*/
public Where in(String columnName, QueryBuilder subQueryBuilder) throws SQLException {
return in(true, columnName, subQueryBuilder);
}
/**
* Same as {@link #in(String, QueryBuilder)} except with a NOT IN clause.
*/
public Where notIn(String columnName, QueryBuilder subQueryBuilder) throws SQLException {
return in(false, columnName, subQueryBuilder);
}
/**
* Add a EXISTS clause with a sub-query inside of parenthesis.
*
*
* NOTE: The sub-query will be prepared at the same time that the outside query is.
*
*/
public Where exists(QueryBuilder subQueryBuilder) {
// we do this to turn off the automatic addition of the ID column in the select column list
subQueryBuilder.enableInnerQuery();
addClause(new Exists(new InternalQueryBuilderWrapper(subQueryBuilder)));
return this;
}
/**
* Add a 'IS NULL' clause so the column must be null. '=' NULL does not work.
*/
public Where isNull(String columnName) throws SQLException {
addClause(new IsNull(columnName, findColumnFieldType(columnName)));
return this;
}
/**
* Add a 'IS NOT NULL' clause so the column must not be null. '<>' NULL does not work.
*/
public Where isNotNull(String columnName) throws SQLException {
addClause(new IsNotNull(columnName, findColumnFieldType(columnName)));
return this;
}
/**
* Add a '<=' clause so the column must be less-than or equals-to the value.
*/
public Where le(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.LESS_THAN_EQUAL_TO_OPERATION));
return this;
}
/**
* Add a '<' clause so the column must be less-than the value.
*/
public Where lt(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.LESS_THAN_OPERATION));
return this;
}
/**
* Add a LIKE clause so the column must mach the value using '%' patterns.
*/
public Where like(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.LIKE_OPERATION));
return this;
}
/**
* Add a '<>' clause so the column must be not-equal-to the value.
*/
public Where ne(String columnName, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value,
SimpleComparison.NOT_EQUAL_TO_OPERATION));
return this;
}
/**
* Used to NOT the next clause specified.
*/
public Where not() {
/*
* Special circumstance here when we have a needs future with a not. Something like and().not().like(...). In
* this case we satisfy the and()'s future as the not() but the not() becomes the new needs-future.
*/
Not not = new Not();
addClause(not);
addNeedsFuture(not);
return this;
}
/**
* Used to NOT the argument clause specified.
*/
public Where not(Where comparison) {
addClause(new Not(pop("NOT")));
return this;
}
/**
* OR operation which takes the previous clause and the next clause and OR's them together.
*/
public Where or() {
ManyClause clause = new ManyClause(pop("OR"), ManyClause.OR_OPERATION);
push(clause);
addNeedsFuture(clause);
return this;
}
/**
* OR operation which takes 2 arguments and OR's them together.
*
*
* NOTE: There is no guarantee of the order of the clauses that are generated in the final query.
*
*
* NOTE: I can't remove the generics code warning that can be associated with this method. You can instead
* use the {@link #or(int)} method.
*
*/
public Where or(Where left, Where right, Where... others) {
Clause[] clauses = buildClauseArray(others, "OR");
Clause secondClause = pop("OR");
Clause firstClause = pop("OR");
addClause(new ManyClause(firstClause, secondClause, clauses, ManyClause.OR_OPERATION));
return this;
}
/**
* This method needs to be used carefully. This will absorb a number of clauses that were registered previously with
* calls to {@link Where#eq(String, Object)} or other methods and will string them together with OR's. There is no
* way to verify the number of previous clauses so the programmer has to count precisely.
*
*
* NOTE: There is no guarantee of the order of the clauses that are generated in the final query.
*
*
*
* NOTE: This will throw an exception if numClauses is 0 but will work with 1 or more.
*
*/
public Where or(int numClauses) {
if (numClauses == 0) {
throw new IllegalArgumentException("Must have at least one clause in or(numClauses)");
}
Clause[] clauses = new Clause[numClauses];
for (int i = numClauses - 1; i >= 0; i--) {
clauses[i] = pop("OR");
}
addClause(new ManyClause(clauses, ManyClause.OR_OPERATION));
return this;
}
/**
* Add a clause where the ID is equal to the argument.
*/
public Where idEq(ID id) throws SQLException {
if (idColumnName == null) {
throw new SQLException("Object has no id column specified");
}
addClause(new SimpleComparison(idColumnName, idFieldType, id, SimpleComparison.EQUAL_TO_OPERATION));
return this;
}
/**
* Add a clause where the ID is from an existing object.
*/
public Where idEq(Dao dataDao, OD data) throws SQLException {
if (idColumnName == null) {
throw new SQLException("Object has no id column specified");
}
addClause(new SimpleComparison(idColumnName, idFieldType, dataDao.extractId(data),
SimpleComparison.EQUAL_TO_OPERATION));
return this;
}
/**
* Add a raw statement as part of the where that can be anything that the database supports. Using more structured
* methods is recommended but this gives more control over the query and allows you to utilize database specific
* features.
*
* @param rawStatement
* The statement that we should insert into the WHERE.
*
* @param args
* Optional arguments that correspond to any ? specified in the rawStatement. Each of the arguments must
* have either the corresponding columnName or the sql-type set. WARNING, you cannot use the
* {@code SelectArg("columnName")} constructor since that sets the _value_, not the name. Use
* {@code new SelectArg("column-name", null);}.
*/
public Where raw(String rawStatement, ArgumentHolder... args) {
for (ArgumentHolder arg : args) {
String columnName = arg.getColumnName();
if (columnName == null) {
if (arg.getSqlType() == null) {
throw new IllegalArgumentException("Either the column name or SqlType must be set on each argument");
}
} else {
arg.setMetaInfo(findColumnFieldType(columnName));
}
}
addClause(new Raw(rawStatement, args));
return this;
}
/**
* Make a comparison where the operator is specified by the caller. It is up to the caller to specify an appropriate
* operator for the database and that it be formatted correctly.
*/
public Where rawComparison(String columnName, String rawOperator, Object value) throws SQLException {
addClause(new SimpleComparison(columnName, findColumnFieldType(columnName), value, rawOperator));
return this;
}
/**
* A short-cut for calling {@link QueryBuilder#prepare()}.
*/
public PreparedQuery prepare() throws SQLException {
return statementBuilder.prepareStatement(null, false);
}
/**
* A short-cut for calling {@link QueryBuilder#query()}.
*/
public List query() throws SQLException {
return checkQueryBuilderMethod("query()").query();
}
/**
* A short-cut for calling {@link QueryBuilder#queryRaw()}.
*/
public GenericRawResults queryRaw() throws SQLException {
return checkQueryBuilderMethod("queryRaw()").queryRaw();
}
/**
* A short-cut for calling {@link QueryBuilder#queryForFirst()}.
*/
public T queryForFirst() throws SQLException {
return checkQueryBuilderMethod("queryForFirst()").queryForFirst();
}
/**
* A short-cut for calling {@link QueryBuilder#queryRawFirst()}.
*/
public String[] queryRawFirst() throws SQLException {
return checkQueryBuilderMethod("queryRawFirst()").queryRawFirst();
}
/**
* A short-cut for calling {@link QueryBuilder#countOf()}.
*/
public long countOf() throws SQLException {
return checkQueryBuilderMethod("countOf()").countOf();
}
/**
* A short-cut for calling {@link QueryBuilder#iterator()}.
*/
public CloseableIterator iterator() throws SQLException {
return checkQueryBuilderMethod("iterator()").iterator();
}
/**
* Reset the Where object so it can be re-used.
*/
public Where reset() {
for (int i = 0; i < clauseStackLevel; i++) {
// help with gc
clauseStack[i] = null;
}
clauseStackLevel = 0;
return this;
}
/**
* Returns the associated SQL WHERE statement.
*/
public String getStatement() throws SQLException {
StringBuilder sb = new StringBuilder();
appendSql(null, sb, new ArrayList());
return sb.toString();
}
/**
* Returns the {@link QueryBuilder} used to create this Where, if present
*
* @returns the QueryBuilder object
*/
public QueryBuilder queryBuilder() throws SQLException {
if (statementBuilder instanceof QueryBuilder) {
return (QueryBuilder) statementBuilder;
} else {
throw new SQLException("Cannot cast " + statementBuilder.getType() + " to QueryBuilder");
}
}
/**
* Used by the internal classes to add the where SQL to the {@link StringBuilder}.
*
* @param tableName
* Name of the table to prepend to any column names or null to be ignored.
*/
void appendSql(String tableName, StringBuilder sb, List columnArgList) throws SQLException {
if (clauseStackLevel == 0) {
throw new IllegalStateException("No where clauses defined. Did you miss a where operation?");
}
if (clauseStackLevel != 1) {
throw new IllegalStateException(
"Both the \"left-hand\" and \"right-hand\" clauses have been defined. Did you miss an AND or OR?");
}
if (needsFuture != null) {
throw new IllegalStateException(
"The SQL statement has not been finished since there are previous operations still waiting for clauses.");
}
// we don't pop here because we may want to run the query multiple times
peek().appendSql(databaseType, tableName, sb, columnArgList);
}
@Override
public String toString() {
if (clauseStackLevel == 0) {
return "empty where clause";
} else {
Clause clause = peek();
return "where clause: " + clause;
}
}
private QueryBuilder checkQueryBuilderMethod(String methodName) throws SQLException {
if (statementBuilder instanceof QueryBuilder) {
return (QueryBuilder) statementBuilder;
} else {
throw new SQLException("Cannot call " + methodName + " on a statement of type "
+ statementBuilder.getType());
}
}
private Where in(boolean in, String columnName, Object... objects) throws SQLException {
if (objects.length == 1) {
if (objects[0].getClass().isArray()) {
throw new IllegalArgumentException("Object argument to " + (in ? "IN" : "notId")
+ " seems to be an array within an array");
}
if (objects[0] instanceof Where) {
throw new IllegalArgumentException("Object argument to " + (in ? "IN" : "notId")
+ " seems to be a Where object, did you mean the QueryBuilder?");
}
if (objects[0] instanceof PreparedStmt) {
throw new IllegalArgumentException("Object argument to " + (in ? "IN" : "notId")
+ " seems to be a prepared statement, did you mean the QueryBuilder?");
}
}
addClause(new In(columnName, findColumnFieldType(columnName), objects, in));
return this;
}
private Where in(boolean in, String columnName, QueryBuilder subQueryBuilder) throws SQLException {
if (subQueryBuilder.getSelectColumnCount() != 1) {
if (subQueryBuilder.getSelectColumnCount() == 0) {
throw new SQLException("Inner query must have only 1 select column specified instead of *");
} else {
throw new SQLException("Inner query must have only 1 select column specified instead of "
+ subQueryBuilder.getSelectColumnCount() + ": " + subQueryBuilder.getSelectColumnsAsString());
}
}
// we do this to turn off the automatic addition of the ID column in the select column list
subQueryBuilder.enableInnerQuery();
addClause(new InSubQuery(columnName, findColumnFieldType(columnName), new InternalQueryBuilderWrapper(
subQueryBuilder), in));
return this;
}
private Clause[] buildClauseArray(Where[] others, String label) {
Clause[] clauses;
if (others.length == 0) {
clauses = null;
} else {
clauses = new Clause[others.length];
// fill in reverse order
for (int i = others.length - 1; i >= 0; i--) {
clauses[i] = pop(label);
}
}
return clauses;
}
private void addNeedsFuture(NeedsFutureClause clause) {
if (needsFuture != null) {
throw new IllegalStateException(needsFuture + " is already waiting for a future clause, can't add: "
+ clause);
}
needsFuture = clause;
}
private void addClause(Clause clause) {
if (needsFuture == null) {
push(clause);
} else {
// we have a binary statement which was called before the right clause was defined
needsFuture.setMissingClause(clause);
needsFuture = null;
}
}
private FieldType findColumnFieldType(String columnName) {
return tableInfo.getFieldTypeByColumnName(columnName);
}
private void push(Clause clause) {
// if the stack is full then we need to grow it
if (clauseStackLevel == clauseStack.length) {
// double its size each time
Clause[] newStack = new Clause[clauseStackLevel * 2];
// copy the entries over to the new stack
for (int i = 0; i < clauseStackLevel; i++) {
newStack[i] = clauseStack[i];
// to help gc
clauseStack[i] = null;
}
clauseStack = newStack;
}
clauseStack[clauseStackLevel++] = clause;
}
private Clause pop(String label) {
if (clauseStackLevel == 0) {
throw new IllegalStateException("Expecting there to be a clause already defined for '" + label
+ "' operation");
}
Clause clause = clauseStack[--clauseStackLevel];
// to help gc
clauseStack[clauseStackLevel] = null;
return clause;
}
private Clause peek() {
return clauseStack[clauseStackLevel - 1];
}
}