- import java.util.ArrayList;
- import java.util.Collection;
- import java.util.HashMap;
- import java.util.LinkedList;
- import java.util.List;
- import java.util.Map;
- /**
- * SQL动态封装工具
- *
- * simple::
- *
- * BaseSelector select = new BaseSelector();
- * select.setCommand("select * from stat_user ");
- * select.createCriteria().andEqualTo("org_code", "YZ1201")
- * .andGreaterThanOrEqualTo("day_code", "2009-01-01")
- * .andLessThanOrEqualTo("day_code", "2009-03-31");
- * List
- *
- * output:select * from stat_user where org_code = 'YZ1201' and day_code >= '2009-01-01' and day_code <= '2009-03-31'
- *
- */
- public class BaseSelector {
- /**
- * sql语句
- */
- private String command;
- /**
- * 排序字段
- */
- protected String orderByClause;
- /**
- * sql条件
- */
- protected List oredCriteria;
- public String getCommand()
- {
- return command;
- }
- public void setCommand(String command)
- {
- this.command = command;
- }
- public BaseSelector() {
- oredCriteria = new ArrayList ();
- }
- protected BaseSelector(BaseSelector example) {
- this.orderByClause = example.orderByClause;
- this.oredCriteria = example.oredCriteria;
- }
- /**
- * 生成最终sql语句
- */
- public String toSql()
- {
- if(oredCriteria==null||oredCriteria.size()<=0)
- return command;
- StringBuffer sqlAll = new StringBuffer();
- sqlAll.append(command);
- if (command != null && command.toUpperCase().indexOf(" WHERE ") == -1)
- sqlAll.append(" WHERE ");
- else
- sqlAll.append(" AND ");
- for (Criteria cri : oredCriteria)
- {
- if(!cri.isValid())
- continue;
- sqlAll.append("(");
- StringBuffer sql = new StringBuffer();
- criteriaWithoutValueSql(sql,cri.criteriaWithoutValue);
- criteriaWithSingleValueSql(sql,cri.criteriaWithSingleValue);
- criteriaWithListValueSql(sql,cri.criteriaWithListValue);
- criteriaWithBetweenValueSql(sql,cri.criteriaWithBetweenValue);
- sqlAll.append(sql.toString());
- sqlAll.append(")");
- sqlAll.append(" or ");
- }
- return sqlAll.substring(0, sqlAll.length()-4);
- }
- @SuppressWarnings("unchecked")
- private String criteriaWithoutValueSql(StringBuffer sql,List list)
- {
- if(list==null)
- return "";
- int n = list.size();
- for (int i = 0;i
- {
- sql.append(list.get(i));
- if(i
- sql.append(" and ");
- }
- return sql.toString();
- }
- @SuppressWarnings("unchecked")
- private String criteriaWithSingleValueSql(StringBuffer sql,List list)
- {
- if(list==null)
- return "";
- if (sql.length() > 0&&list.size()>0)
- sql.append(" and ");
- int n = list.size();
- for (int i = 0;i
- {
- Map map = (Map) list.get(i);
- sql.append(map.get("condition")).append(map.get("value"));
- if(i
- sql.append(" and ");
- }
- return sql.toString();
- }
- @SuppressWarnings("unchecked")
- private String criteriaWithListValueSql(StringBuffer sql,List list)
- {
- if(list==null)
- return "";
- if (sql.length() > 0&&list.size()>0)
- sql.append(" and ");
- int n = list.size();
- for (int i = 0;i
- {
- Map map = (Map) list.get(i);
- sql.append(map.get("condition")).append("("+join((Collection) map.get("values"),",")+")");
- if(i
- sql.append(" and ");
- }
- return sql.toString();
- }
- @SuppressWarnings("unchecked")
- private String criteriaWithBetweenValueSql(StringBuffer sql,List list)
- {
- if(list==null)
- return "";
- if (sql.length() > 0&&list.size()>0)
- sql.append(" and ");
- int n = list.size();
- for (int i = 0;i
- {
- Map map = (Map) list.get(i);
- sql.append(map.get("condition")).append(join((Collection) map.get("values")," and "));
- if(i
- sql.append(" and ");
- }
- return sql.toString();
- }
- @SuppressWarnings("unchecked")
- private String join(Collection list,String spe)
- {
- if(list==null)
- return "";
- Object array[] = list.toArray();
- StringBuffer buff = new StringBuffer();
- for (int i = 0; i < array.length; i++)
- {
- buff.append(array[i]);
- if(i
- buff.append(spe);
- }
- return buff.toString();
- }
- /**
- * 顺序排序
- * @param field
- */
- public void setOrderByClauseAsc(String field) {
- this.orderByClause = getFieldName(field) + " ASC";
- }
- /**
- * 倒序排序
- * @param field
- */
- public void setOrderByClauseDesc(String field) {
- this.orderByClause = getFieldName(field) + " DESC";
- }
- public String getOrderByClause() {
- return orderByClause;
- }
- public List getOredCriteria() {
- return oredCriteria;
- }
- /**
- * or 条件
- */
- public void or(Criteria criteria) {
- oredCriteria.add(criteria);
- }
- /**
- * 创建条件对象
- */
- public Criteria createCriteria() {
- Criteria criteria = createCriteriaInternal();
- if (oredCriteria.size() == 0) {
- oredCriteria.add(criteria);
- }
- return criteria;
- }
- protected Criteria createCriteriaInternal() {
- Criteria criteria = new Criteria();
- return criteria;
- }
- /**
- * 清除条件
- */
- public void clear() {
- oredCriteria.clear();
- }
- static String getFieldName(String field)
- {
- if (field == null ) {
- throw new RuntimeException( field + " cannot be null");
- }
- return field.toUpperCase();
- //
- //
- // Pattern pattern = Pattern.compile("[A-Z]{1}");
- // Matcher m = pattern.matcher(field);
- // StringBuffer sbr = new StringBuffer();
- // while(m.find())
- // m.appendReplacement(sbr, "_"+m.group());
- // m.appendTail(sbr);
- // return sbr.toString().toUpperCase();
- }
- /**
- * 查询条件
- */
- public static class Criteria {
- protected List criteriaWithoutValue;
- protected List
- protected List
- protected List
- protected Criteria() {
- super();
- criteriaWithoutValue = new ArrayList ();
- criteriaWithSingleValue = new ArrayList
- criteriaWithListValue = new ArrayList
- criteriaWithBetweenValue = new ArrayList
- }
- public boolean isValid() {
- return criteriaWithoutValue.size() > 0
- || criteriaWithSingleValue.size() > 0
- || criteriaWithListValue.size() > 0
- || criteriaWithBetweenValue.size() > 0;
- }
- public List getCriteriaWithoutValue() {
- return criteriaWithoutValue;
- }
- public List
- return criteriaWithSingleValue;
- }
- public List
- return criteriaWithListValue;
- }
- public List
- return criteriaWithBetweenValue;
- }
- protected void addCriterion(String condition) {
- if (condition == null||"".equals(condition)) {
- return;
- }
- criteriaWithoutValue.add(condition);
- }
- protected void addCriterion(String condition, Object value, String property) {
- if (value == null||"".equals(value)) {
- return;
- }
- Map map = new HashMap ();
- map.put("condition", condition);
- map.put("value", value);
- criteriaWithSingleValue.add(map);
- }
- protected void addCriterion(String condition, List values, String property) {
- if (values == null || values.size() == 0) {
- return;
- }
- Map map = new HashMap ();
- map.put("condition", condition);
- map.put("values", values);
- criteriaWithListValue.add(map);
- }
- protected void addCriterion(String condition, Object value1, Object value2, String property) {
- if (value1 == null || value2 == null) {
- return;
- }
- List
sql的动态条件拼装
最新推荐文章于 2024-04-26 11:55:15 发布