net.sf.jasperreports.engine.query.SQLBetweenBaseClause Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of jasperreports Show documentation
Show all versions of jasperreports Show documentation
Free Java Reporting Library
/*
* JasperReports - Free Java Reporting Library.
* Copyright (C) 2001 - 2019 TIBCO Software Inc. All rights reserved.
* http://www.jaspersoft.com
*
* Unless you have purchased a commercial license agreement from Jaspersoft,
* the following license terms apply:
*
* This program is part of JasperReports.
*
* JasperReports is free software: you can redistribute it and/or modify
* it under the terms of the GNU Lesser General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* JasperReports is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public License
* along with JasperReports. If not, see .
*/
package net.sf.jasperreports.engine.query;
import net.sf.jasperreports.engine.JRRuntimeException;
/**
* Base BETWEEN clause function for SQL queries.
*
* The purpose of this clause function is to find that a given
* value can be found in a given A...B interval.
*
*
* The first token in the $X{...} syntax is the function ID token. Possible values for
* the BETWEEN clause function ID token are:
*
* BETWEEN
- in this case the A...B interval will be considered open: (A,B)
* [BETWEEN
- in this case the A...B interval will be considered right-open: [A,B)
* BETWEEN]
- in this case the A...B interval will be considered left-open: (A,B]
* [BETWEEN]
- in this case the A...B interval will be considered closed: [A,B]
*
*
*
* @author Sanda Zaharia ([email protected])
*/
public abstract class SQLBetweenBaseClause implements JRClauseFunction
{
public static final String EXCEPTION_MESSAGE_KEY_QUERY_BETWEEN_CLAUSE_DB_COLUMN_TOKEN_MISSING = "query.between.clause.db.column.token.missing";
public static final String EXCEPTION_MESSAGE_KEY_QUERY_BETWEEN_CLAUSE_LEFT_PARAMETER_TOKEN_MISSING = "query.between.clause.left.parameter.token.missing";
public static final String EXCEPTION_MESSAGE_KEY_QUERY_BETWEEN_CLAUSE_NAME_TOKEN_MISSING = "query.between.clause.name.token.missing";
public static final String EXCEPTION_MESSAGE_KEY_QUERY_BETWEEN_CLAUSE_RIGHT_PARAMETER_TOKEN_MISSING = "query.between.clause.right.parameter.token.missing";
protected static final int POSITION_CLAUSE_ID = 0;
protected static final int POSITION_DB_COLUMN = 1;
protected static final int POSITION_LEFT_PARAMETER = 2;
protected static final int POSITION_RIGHT_PARAMETER = 3;
protected static final String CLAUSE_TRUISM = "0 = 0";
/**
* Creates a BETWEEN-like SQL clause, depending on the clause ID.
*
*
* The method expects three clause tokens (after the ID token):
*
* - The first token is the SQL column (or column combination) to be used in the clause.
* - The second token is the name of the parameter that contains the left member value.
* - The second token is the name of the parameter that contains the right member value.
*
*
*
* The method constructs one of the following clauses:
*
* (column > ? AND column < ?)
if the clause ID is BETWEEN
* (column >= ? AND column < ?)
if the clause ID is [BETWEEN
* (column > ? AND column <= ?)
if the clause ID is BETWEEN]
* (column >= ? AND column <= ?)
if the clause ID is [BETWEEN]
*
* If the left member value is null, one of the following clauses will be generated:
*
* column < ?
if the clause ID is BETWEEN
or BETWEEN
* column <= ?
if the clause ID is BETWEEN]
or [BETWEEN]
*
* If the right member value is null, one of the following clauses will be generated:
*
* column > ?
if the clause ID is BETWEEN
or BETWEEN]
* column >= ?
if the clause ID is [BETWEEN
or [BETWEEN]
*
* If the both left and right member values are null, the method generates a SQL clause that
* will always evaluate to true (e.g. 0 = 0
).
*
* @param clauseTokens
* @param queryContext
*/
@Override
public void apply(JRClauseTokens clauseTokens, JRQueryClauseContext queryContext)
{
String clauseId = clauseTokens.getToken(POSITION_CLAUSE_ID);
String col = clauseTokens.getToken(POSITION_DB_COLUMN);
String leftParam = clauseTokens.getToken(POSITION_LEFT_PARAMETER);
String rightParam = clauseTokens.getToken(POSITION_RIGHT_PARAMETER);
if (clauseId == null)
{
throw
new JRRuntimeException(
EXCEPTION_MESSAGE_KEY_QUERY_BETWEEN_CLAUSE_NAME_TOKEN_MISSING,
(Object[])null);
}
if (col == null)
{
throw
new JRRuntimeException(
EXCEPTION_MESSAGE_KEY_QUERY_BETWEEN_CLAUSE_DB_COLUMN_TOKEN_MISSING,
(Object[])null);
}
if (leftParam == null)
{
throw
new JRRuntimeException(
EXCEPTION_MESSAGE_KEY_QUERY_BETWEEN_CLAUSE_LEFT_PARAMETER_TOKEN_MISSING,
(Object[])null);
}
if (rightParam == null)
{
throw
new JRRuntimeException(
EXCEPTION_MESSAGE_KEY_QUERY_BETWEEN_CLAUSE_RIGHT_PARAMETER_TOKEN_MISSING,
(Object[])null);
}
ClauseFunctionParameterHandler leftParamHandler = createParameterHandler(queryContext, clauseId, leftParam, true);
ClauseFunctionParameterHandler rightParamHandler = createParameterHandler(queryContext, clauseId, rightParam, false);
StringBuffer sbuffer = queryContext.queryBuffer();
if(leftParamHandler.hasValue() && rightParamHandler.hasValue())
{
sbuffer.append("( ");
handleGreaterClause(sbuffer, clauseId, col, leftParamHandler, queryContext);
sbuffer.append(" AND ");
handleLessClause(sbuffer, clauseId, col, rightParamHandler, queryContext);
sbuffer.append(" )");
}
else if(!leftParamHandler.hasValue())
{
if(!rightParamHandler.hasValue())
{
sbuffer.append(CLAUSE_TRUISM);
}
else
{
handleLessClause(sbuffer, clauseId, col, rightParamHandler, queryContext);
}
}
else
{
handleGreaterClause(sbuffer, clauseId, col, leftParamHandler, queryContext);
}
}
protected abstract ClauseFunctionParameterHandler createParameterHandler(JRQueryClauseContext queryContext,
String clauseId, String parameterName, boolean left);
/**
*
* @param clauseId the clause ID
* @return the '>
' or '>=
' sign
*/
protected String getGreaterOperator(String clauseId)
{
return isLeftClosed(clauseId) ? ">=" : ">";
}
protected boolean isLeftClosed(String clauseId)
{
return clauseId.startsWith("[");
}
/**
*
* @param clauseId the clause ID
* @return the '<
' or '<=
' sign
*/
protected String getLessOperator(String clauseId)
{
return isRightClosed(clauseId) ? "<=" : "<";
}
protected boolean isRightClosed(String clauseId)
{
return clauseId.endsWith("]");
}
/**
* Generates either a 'column > ?
' or a 'column >= ?
' clause
*
* @param sbuffer the StringBuffer that contains the generated query
* @param clauseId the clause ID
* @param col the name of the column, or a column names combination
* @param leftParamHandler the parameter handler that contains the left member value
* @param queryContext the query context
*/
protected void handleGreaterClause(
StringBuffer sbuffer,
String clauseId,
String col,
ClauseFunctionParameterHandler leftParamHandler,
JRQueryClauseContext queryContext
)
{
sbuffer.append(col);
sbuffer.append(' ');
sbuffer.append(getGreaterOperator(clauseId));
sbuffer.append(' ');
sbuffer.append('?');
leftParamHandler.addQueryParameter();
}
/**
* Generates either a 'column < ?
' or a 'column <= ?
' clause
*
* @param sbuffer the StringBuffer that contains the generated query
* @param clauseId the clause ID
* @param col the name of the column, or a column names combination
* @param rightParamHandler
* @param queryContext the query context
*/
protected void handleLessClause(
StringBuffer sbuffer,
String clauseId,
String col,
ClauseFunctionParameterHandler rightParamHandler,
JRQueryClauseContext queryContext
)
{
sbuffer.append(col);
sbuffer.append(' ');
sbuffer.append(getLessOperator(clauseId));
sbuffer.append(' ');
sbuffer.append('?');
rightParamHandler.addQueryParameter();
}
}