/*
* Copyright (C) 2003-2008 eXo Platform SAS.
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU Affero General Public License
* as published by the Free Software Foundation; either version 3
* of the License, or (at your option) any later version.
*
* This program 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 General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program; if not, see<http://www.gnu.org/licenses/>.
*/
package org.exoplatform.services.wcm.utils;
import java.util.Calendar;
import org.apache.commons.lang.StringUtils;
import org.exoplatform.commons.utils.ISO8601;
/**
* Created by The eXo Platform SAS
* Author : Hoa Pham
* hoa.pham@exoplatform.com
* Oct 7, 2008
*/
/*
* This class is implementation of AbstractQueryBuilder for sql-based query.
* This class help developer create sql query statement easier.
* */
public class SQLQueryBuilder extends AbstractQueryBuilder {
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#isNull(java.lang
* .String, org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void isNull(String propertyName, LOGICAL condition) {
if(condition == LOGICAL.AND)
propertiesClause.append(" AND").append(propertyName).append("IS NULL ");
else if(condition == LOGICAL.OR)
propertiesClause.append(" OR").append(propertyName).append("IS NULL ");
else
propertiesClause.append(propertyName).append("IS NULL ");
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#isNotNull(java
* .lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void isNotNull(String propertyName, LOGICAL condition) {
if(condition == LOGICAL.AND)
propertiesClause.append("AND ").append(propertyName).append(" IS NOT NULL ");
else if(condition == LOGICAL.OR)
propertiesClause.append("OR ").append(propertyName).append(" IS NOT NULL ");
else
propertiesClause.append(propertyName).append(" IS NOT NULL ");
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#lessThan(java.
* lang.String, java.lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void lessThan(String propertyName, String value, LOGICAL condition) {
comparison(propertyName,value,condition,"<");
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#greaterThan(java
* .lang.String, java.lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void greaterThan(String propName, String value, LOGICAL condition) {
comparison(propName,value,condition,">");
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#lessThanOrEqual
* (java.lang.String, java.lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void lessThanOrEqual(String propName, String value, LOGICAL condition) {
comparison(propName,value,condition,"<=");
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#greaterOrEqual
* (java.lang.String, java.lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void greaterOrEqual(String propName,String value, LOGICAL condition) {
comparison(propName,value,condition,">=");
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#equal(java.lang
* .String, java.lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void equal(String propName, String value , LOGICAL condition) {
comparison(propName,value,condition,"=");
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#notEqual(java.
* lang.String, java.lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void notEqual(String propName, String value, LOGICAL condition) {
if(condition == LOGICAL.AND)
propertiesClause.append("AND ").append(propName).append(" <> '").append(value).append("' ");
else if(condition == LOGICAL.OR)
propertiesClause.append("OR ").append(propName).append(" <> '").append(value).append("' ");
else
propertiesClause.append(propName).append(" <> '").append(value).append("' ");
}
/**
* Comparison.
*
* @param propName the prop name
* @param value the value
* @param condition the condition
* @param symbol the symbol
*/
private void comparison(String propName, String value, LOGICAL condition, String symbol) {
if(condition == LOGICAL.AND)
propertiesClause.append("AND ").append(propName).append(" ").append(symbol).append(" '").append(value).append("' ");
else if(condition == LOGICAL.OR)
propertiesClause.append("OR ").append(propName).append(" ").append(symbol).append(" '").append(value).append("' ");
else
propertiesClause.append(propName).append(" ").append(symbol).append(" '").append(value).append("' ");
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#like(java.lang
* .String, java.lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void like(String propName, String value, LOGICAL condition) {
if(condition == LOGICAL.AND)
propertiesClause.append("AND ").append(propName).append(" LIKE '").append(value).append("%' ");
else if(condition == LOGICAL.OR)
propertiesClause.append("OR ").append(propName).append(" LIKE '").append(value).append("%' ");
else
propertiesClause.append(propName).append(" LIKE '").append(value).append("%' ");
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#reference(java
* .lang.String, java.lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void reference(String propName, String value, LOGICAL condition) {
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#beforeDate(java
* .lang.String, java.lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void beforeDate(String propName, String comparedDate, LOGICAL condition) {
Calendar calendar = ISO8601.parse(comparedDate);
String time = calendar.getTime().toString();
if(condition == LOGICAL.AND)
propertiesClause.append("AND ").append(propName).append(" <= '").append(time).append("' ");
else if(condition == LOGICAL.OR)
propertiesClause.append("OR ").append(propName).append(" <= '").append(time).append("' ");
else
propertiesClause.append(propName).append(" <= '").append(time).append("' ");
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#afterDate(java
* .lang.String, java.lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void afterDate(String propName, String comparedDate, LOGICAL condition) {
Calendar calendar = ISO8601.parse(comparedDate);
String time = calendar.getTime().toString();
if(condition == LOGICAL.AND)
propertiesClause.append("AND ").append(propName).append(" >= '").append(time).append("' ");
else if(condition == LOGICAL.OR)
propertiesClause.append("OR ").append(propName).append(" >= '").append(time).append("' ");
else
propertiesClause.append(propName).append(" >= '").append(time).append("' ");
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#betweenDates(java
* .lang.String, java.lang.String, java.lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void betweenDates(String propName, String startDate, String endDate, LOGICAL condition) {
String startTime = ISO8601.parse(startDate).getTime().toString();
String endTime = ISO8601.parse(endDate).getTime().toString();
if (condition == LOGICAL.AND)
propertiesClause.append("AND ")
.append(propName)
.append(" between TIMESTAMP '")
.append(startTime)
.append("' and TIMESTAMP '")
.append(endTime)
.append("' ");
else if (condition == LOGICAL.OR)
propertiesClause.append("OR ")
.append(propName)
.append(" between TIMESTAMP '")
.append(startTime)
.append("' and TIMESTAMP '")
.append(endTime)
.append("' ");
else
propertiesClause.append(propName)
.append(" between TIMESTAMP '")
.append(startTime)
.append("' and TIMESTAMP '")
.append(endTime)
.append("' ");
}
public void betweenDates(String propName, Calendar startDate, Calendar endDate, LOGICAL condition) {
String startTime = ISO8601.format(startDate);
String endTime = ISO8601.format(endDate);
if (condition == LOGICAL.AND)
propertiesClause.append("AND ")
.append(propName)
.append(" between TIMESTAMP '")
.append(startTime)
.append("' and TIMESTAMP '")
.append(endTime)
.append("' ");
else if (condition == LOGICAL.OR)
propertiesClause.append("OR ")
.append(propName)
.append(" between TIMESTAMP '")
.append(startTime)
.append("' and TIMESTAMP '")
.append(endTime)
.append("' ");
else
propertiesClause.append(propName)
.append(" between TIMESTAMP '")
.append(startTime)
.append("' and TIMESTAMP '")
.append(endTime)
.append("' ");
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#setQueryPath(java
* .lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.PATH_TYPE)
*/
public void setQueryPath(String path, PATH_TYPE pathtype) {
if(StringUtils.isEmpty(path)) return;
if (PATH_TYPE.EXACT == pathtype) {
if (path.indexOf("[%]") > 0)
pathClause = new StringBuilder().append("jcr:path LIKE '").append(path).append("' ");
else
pathClause = new StringBuilder().append("jcr:path = '").append(path).append("' ");
} else if (PATH_TYPE.CHILDNODES == pathtype) {
pathClause = new StringBuilder().append("jcr:path LIKE '")
.append(path)
.append("/%'")
.append("AND NOT jcr:path like '")
.append(path)
.append("/%/%' ");
} else if (PATH_TYPE.DECENDANTS == pathtype) {
pathClause = new StringBuilder().append("jcr:path LIKE '").append(path).append("/%' ");
} else if (PATH_TYPE.DECENDANTS_OR_SELFT == pathtype) {
pathClause = new StringBuilder().append("jcr:path LIKE '")
.append(path)
.append("'")
.append("OR jcr:path LIKE '")
.append(path)
.append("/%' ");
}
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#contains(java.
* lang.String, java.lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void contains(String scope, String term, LOGICAL condition) {
if (scope == null)
scope = ".";
if (LOGICAL.AND == condition)
containsClause.append("AND CONTAINS(").append(scope).append(",'").append(term).append("') ");
else if (LOGICAL.OR == condition)
containsClause.append("OR CONTAINS(").append(scope).append(",'").append(term).append("') ");
else
containsClause.append("CONTAINS(").append(scope).append(",'").append(term).append("') ");
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#notContains(java
* .lang.String, java.lang.String,
* org.exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void notContains(String scope, String term, LOGICAL condition) {
if (scope == null)
scope = ".";
if (LOGICAL.AND == condition)
containsClause.append("AND NOT CONTAINS(")
.append(scope)
.append(",'")
.append(term)
.append("') ");
else if (LOGICAL.OR == condition)
containsClause.append("OR NOT CONTAINS(")
.append(scope)
.append(",'")
.append(term)
.append("') ");
else
containsClause.append("NOT CONTAINS(").append(scope).append(",'").append(term).append("') ");
}
/* (non-Javadoc)
* @see org.exoplatform.services.wcm.search.AbstractQueryBuilder#fromNodeTypes(java.lang.String[])
*/
public void fromNodeTypes(String[] nodetypes) {
if(nodetypes == null) {
fromClause = new StringBuilder("FROM nt:base");
return;
}
fromClause = new StringBuilder("FROM ");
for(int i = 0; i<nodetypes.length; i++) {
fromClause.append(nodetypes[i]);
if(i<nodetypes.length-1)
fromClause.append(",");
}
}
/* (non-Javadoc)
* @see org.exoplatform.services.wcm.search.AbstractQueryBuilder#selectTypes(java.lang.String[])
*/
public void selectTypes(String[] returnTypes) {
if(returnTypes == null) {
selectClause = new StringBuilder("SELECT * ");
return;
}
selectClause = new StringBuilder("SELECT ");
for(int i = 0; i<returnTypes.length; i++) {
selectClause.append(returnTypes[i]);
if(i<returnTypes.length-1)
selectClause.append(",");
selectClause.append(" ");
}
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#orderBy(java.lang
* .String, org.exoplatform.services.wcm.search.AbstractQueryBuilder.ORDERBY)
*/
public void orderBy(String properyName, ORDERBY orderby) {
if(orderByClause.length()>0)
orderByClause = orderByClause.append(", ");
if(ORDERBY.ASC == orderby)
orderByClause.append(properyName).append(" ASC");
else
orderByClause.append(properyName).append(" DESC");
}
/* (non-Javadoc)
* @see org.exoplatform.services.wcm.search.AbstractQueryBuilder#excerpt(boolean)
*/
public void excerpt(boolean enable) {
if(enable)
excerptClause = new StringBuilder("excerpt(.)");
}
/* (non-Javadoc)
* @see org.exoplatform.services.wcm.search.AbstractQueryBuilder#spellCheck(java.lang.String)
*/
public void spellCheck(String value) {
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#openGroup(org.
* exoplatform.services.wcm.search.AbstractQueryBuilder.LOGICAL)
*/
public void openGroup(LOGICAL logical) {
if(LOGICAL.AND == logical)
propertiesClause = propertiesClause.append("AND( ");
else if(LOGICAL.OR == logical)
propertiesClause = propertiesClause.append("OR( ");
else if(LOGICAL.AND_NOT == logical)
propertiesClause = propertiesClause.append("AND NOT(");
else if(LOGICAL.OR_NOT == logical)
propertiesClause = propertiesClause.append("OR NOT(");
else
propertiesClause = propertiesClause.append("( ");
}
/* (non-Javadoc)
* @see org.exoplatform.services.wcm.search.AbstractQueryBuilder#closeGroup()
*/
public void closeGroup() {
propertiesClause = propertiesClause.append(")");
}
/* (non-Javadoc)
* @see org.exoplatform.services.wcm.search.AbstractQueryBuilder#createQueryStatement()
*/
public String createQueryStatement() {
StringBuffer statement = new StringBuffer();
statement = statement.append(selectClause.toString())
.append(fromClause.toString())
.append(" WHERE ");
if (containsClause.length() > 0) {
statement = statement.append(containsClause.toString());
if (pathClause.length() > 0) {
statement = statement.append("AND ").append(pathClause.toString());
}
} else {
if (pathClause.length() > 0) {
statement = statement.append(pathClause.toString());
}
}
if (propertiesClause.length() > 0) {
String propertiesStr = propertiesClause.toString();
if((propertiesStr.startsWith("AND") || propertiesStr.startsWith(" AND")) &&
statement.toString().endsWith("WHERE "))
propertiesStr = propertiesStr.substring(propertiesStr.lastIndexOf("AND") + 1, propertiesStr.length());
statement = statement.append(propertiesStr);
}
if (orderByClause.length() > 0) {
statement = statement.append("ORDER BY ").append(orderByClause.toString());
}
return statement.toString();
}
/*
* (non-Javadoc)
* @see
* org.exoplatform.services.wcm.search.AbstractQueryBuilder#merge(org.exoplatform
* .services.wcm.search.AbstractQueryBuilder)
*/
public void merge(AbstractQueryBuilder other) {
}
public void queryByNodeName(String rootPath, String nodeName) {
pathClause = new StringBuilder().append(" jcr:path LIKE '")
.append(rootPath)
.append("/%/")
.append(nodeName)
.append("' ")
.append(" or jcr:path like '")
.append(rootPath)
.append("/")
.append(nodeName)
.append("' ");
}
}