SQLHelper.java

Go to the documentation of this file.

/*
 * Turró i Cutiller Foundation. License notice.
 * Copyright (C) 2011 Lluis Turró Cutiller <http://www.turro.org/>
 *
 * 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 Affero General Public License for more details.
 *
 * You should have received a copy of the GNU Affero General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package org.turro.elephant.db;
 
import java.util.Collection;
import org.turro.string.Strings;
 
public class SQLHelper {
 
  private SQLHelper() {
  }
  
  public static String convertToLike(String value) {
    if(value == null) return null;
    return value.replaceAll("\\*", "%").replaceAll("\\?", "_");
  }
 
  public static String convertToPartialLike(String value) {
    if(value == null) return null;
    return "%" + value.replaceAll("\\*", "%").replaceAll("\\?", "_") + "%";
  }
 
  public static String convertToIn(Collection collection) {
    String result = null;
    for(Object o : collection) {
      if(o instanceof String) {
        result = (result == null) ? ("'" + o + "'") : (result + ",'" + o + "'");
      } else {
        result = (String) ((result == null) ? o + "" : (result + "," + o));
      }
    }
    return result;
  }
 
  public static String quoteTokensForIn(String inValue) {
    String[] tokens = inValue.split(",");
    String result = null;
    for(String s : tokens) {
      result = (result == null) ? ("'" + s + "'") : (result + ",'" + s + "'");
    }
    return result;
  }
 
  public static WhereClause getWhereClause(String[] fields, String value) {
    return getWhereClause(null, fields, value);
  }
 
  public static WhereClause getWhereClause(WhereClause wc, String[] fields, String value) {
    if(wc == null) wc = new WhereClause();
    if(value != null) {
      if(value.startsWith("=")) {
        // value must be equal
        value = value.substring(1);
        return /*"%".equals(value) ? wc :*/ getWhereClause(wc, fields, value, null);
      } else if(value.startsWith("?")) {
        // where portion as is
        wc.addClause(value.substring(1));
        return wc;
      }
    }
    return /*"%".equals(value) ? wc :*/ getWhereClause(wc, fields, value, " ");
  }
  
  public static WhereClause getWhereClause(String[] fields, String value, String regexp) {
    return getWhereClause(null, fields, value, regexp);
  }
  
  public static WhereClause getWhereClause(WhereClause wc, String[] fields, String value, String regexp) {
    if(value != null) {
      if(regexp != null) {
        return getWhereClause(wc, fields, value.split(regexp));
      } else {
        return getWhereClause(wc, fields, new String[] { value });
      }
    }
    return new WhereClause();
  }
 
  public static WhereClause getWhereClause(String[] fields, String[] values) {
    return getWhereClause(fields, values, true);
  }
 
  public static WhereClause getWhereClause(WhereClause wc, String[] fields, String[] values) {
    return getWhereClause(wc, fields, values, true);
  }
 
  public static WhereClause getWhereClause(String[] fields, String[] values, boolean namedValues) {
    return getWhereClause(null, fields, values, namedValues);
  }
 
  public static WhereClause getWhereClause(WhereClause wc, String[] fields, String[] values, boolean namedValues) {
    if(wc == null) wc = new WhereClause();
    
    String result = "", partial, param;
    
    for(int f = 0; f < fields.length; f++) {
      partial = "";
      for(int v = 0; v < values.length; v++) {
        if(!Strings.isBlank(values[v])) {
          if(partial.length() > 0) {
            partial += " " + wc.getFieldOcurrence() + " ";
          }
          if(namedValues) {
            param = fields[f].replaceAll("\\.", "_") + wc.getUniqueSuffix();
            partial += "UCASE(" + fields[f] + ") like :" + param;
            if("%".equals(values[v])) {
              wc.addNamedValue(param, "%");
            } else {
              wc.addNamedValue(param, "%" + values[v].toUpperCase() + "%");
            }
          } else {
            partial += "UCASE(" + fields[f] + ") like ?";
            if("%".equals(values[v])) {
              wc.addValue("%");
            } else {
              wc.addValue("%" + values[v].toUpperCase() + "%");
            }
          }
        }
      }
 
      if(partial.length() > 0) {
        if(result.length() > 0) {
          result += " or ";
        }
        result += "(" + partial + ")";
      }
    }
    
    if(result.length() > 0) {
      result = wc.getPrefix() + " (" + result + ") ";
    }
    
    wc.addClause(result);
    
    return wc;
  }
  
}