querytranslate.java

Extjs2增删改查

package com.tcg.core.Utils;

import static com.tcg.core.AppService.getService;
import org.hibernate.util.StringHelper;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.text.SimpleDateFormat;

/**
 * Created by IntelliJ IDEA.
 * User: Administrator
 * Date: 2008-11-4
 * Time: 9:44:32
 * To change this template use File | Settings | File Templates.
 */
public class QueryTranslate {
    private String basicQry;
    private List<Condition> conditions;
    private static final String dot = ".";
    private static final String blank = " ";

    public QueryTranslate(String basicHql, List<Condition> conditions) {
        this.basicQry = basicHql;
        this.conditions = conditions;
    }

    public String getBasicQry() {
        return basicQry;
    }

    public void setBasicQry(String basicSql) {
        this.basicQry = basicSql;
    }

    public List<Condition> getConditions() {
        return conditions;
    }

    public void setConditions(List<Condition> conditions) {
        this.conditions = conditions;
    }

    public String getColumnName(Condition condition) {
        if (null != condition.getEntity() && !StringHelper.isEmpty(condition.getEntity())) {
            return condition.getEntity() + dot + condition.getColumnName();
        } else {
            return condition.getColumnName();
        }
    }

    /**
     * to verdict the operator whether it is  a Comparison sign.
     *
     * @param condition
     * @return
     */
    public boolean opIsComparison(Condition condition) {
        boolean isComparison = false;
        if (!StringHelper.isEmpty(condition.getOperator())) {
            if (condition.getOperator().equals(Constant.OPRATER_EQUAL)) {
                isComparison = true;
            } else if (condition.getOperator().equals(Constant.OPRATER_UNEQUAL)) {
                isComparison = true;
            } else if (condition.getOperator().equals(Constant.OPRATER_GREATER)) {
                isComparison = true;
            } else if (condition.getOperator().equals(Constant.OPRATER_GREATER_AND_EQUAL)) {
                isComparison = true;
            } else if (condition.getOperator().equals(Constant.OPRATER_LESS)) {
                isComparison = true;
            } else if (condition.getOperator().equals(Constant.OPRATER_LESS_AND_EQUAL)) {
                isComparison = true;
            }
        }
        return isComparison;
    }

    /**
     * to verdict the operator whether it is  contains "null".
     *
     * @param condition
     * @return
     */
    public boolean opContainsNull(Condition condition) {
        boolean containsNull = false;
        if (!StringHelper.isEmpty(condition.getOperator())) {
            if (condition.getOperator().equals(Constant.OPRATER_ISNULL)) {
                containsNull = true;
            } else if (condition.getOperator().equals(Constant.OPRATER_ISNOTNULL)) {
                containsNull = true;
            }
        }
        return containsNull;
    }

    public boolean opContainsLike(Condition condition) {
        boolean containsLike = false;
        if (!StringHelper.isEmpty(condition.getOperator())) {
            if (condition.getOperator().equals(Constant.OPRATER_CONTAINS)) {
                containsLike = true;
            } else if (condition.getOperator().equals(Constant.OPRATER_NOT_CONTAINS)) {
                containsLike = true;
            } else if (condition.getOperator().equals(Constant.OPRATER_RIGHT_CONTAINS)) {
                containsLike = true;
            } else if (condition.getOperator().equals(Constant.OPRATER_LEFT_CONTAINS)) {
                containsLike = true;
            }
        }
        return containsLike;
    }


    public String comparisonToQuery(Condition condition) {
        if(condition.getType().equals(Constant.TYPE_IS_DATETIME))
        {
            SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
            return getColumnName(condition) + condition.getOperator() + "'" +  df.format(new Date(Long.valueOf(condition.getFirstValue())))+ "'";
        }
        else
        {
            return getColumnName(condition) + condition.getOperator() + "'" +condition.getFirstValue() + "'";
        }
    }

    public String containNullToQuery(Condition condition) {
        return getColumnName(condition) + " " + condition.getOperator();
    }

    public String containLikeToQuery(Condition condition) {
        return getColumnName(condition) + " " + condition.getOperator().replace("|", condition.getFirstValue());
    }

    public String betweenQuery(Condition condition) {
        if(condition.getType().equals(Constant.TYPE_IS_DATETIME))
        {
            SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd");
            return getColumnName(condition) + " between '" + df.format(new Date(Long.valueOf(condition.getFirstValue())))+ "' and '" + df.format(new Date(Long.valueOf(condition.getSecondValue())))+ "'";
        }
        else
        {
            return getColumnName(condition) + " between '" + condition.getFirstValue()+ "' and '" + condition.getSecondValue()+ "'";
        }
    }

    public String conditionToString() {
        List<Condition> conditionList = conditions;
        String queryConditon = "";
        for (Condition condition : conditionList) {
            if (opIsComparison(condition)) {
                queryConditon += comparisonToQuery(condition);
            } else if (opContainsNull(condition)) {
                queryConditon += containNullToQuery(condition);
            } else if (opContainsLike(condition)) {
                queryConditon += containLikeToQuery(condition);
            } else if (condition.getOperator().equals(Constant.OPRATER_BETWEEN)) {
                queryConditon += betweenQuery(condition);
            }
            if(!StringHelper.isEmpty(condition.getOperator())){
                if (!StringHelper.isEmpty(condition.getAndOr())) {
                    queryConditon += blank + condition.getAndOr() + blank;
                } else {
                    queryConditon += " and ";
                }
            }
        }
        queryConditon = queryConditon.substring(0,queryConditon.length()-4);           //delete the last join-sign
        return queryConditon;
    }


    public String toString() {
        String ret = basicQry;
        String coditionString = conditionToString();
        if (coditionString != null) {

            if (!"".equals(coditionString)) {
                if (basicQry.toUpperCase().indexOf("WHERE") == -1) {
                    ret = ret + " WHERE ";
                }

                if (!basicQry.endsWith(" ")) {
                    ret = ret + " ";
                }

                if (!ret.trim().toUpperCase().endsWith("WHERE")) {
                    ret = ret + " AND ";
                }

                ret += "(" + coditionString + ")";
            }
        }
        return ret;
    }

    public static void main(String[] args) {
        Condition condition = new Condition();
        condition.setColumnName("name");
        condition.setEntity("s");
        condition.setFirstValue("张山");
        condition.setType("String");
        condition.setOperator("=");
        condition.setAndOr("and");
        List list = new ArrayList();
        list.add(condition);
        Condition condition1 = new Condition();
        condition1.setColumnName("sex");
        condition1.setFirstValue("男");
        condition1.setType("String");
        condition1.setOperator("=");
        condition1.setAndOr("and");

        list.add(condition1);
        Condition condition2 = new Condition();
        condition2.setColumnName("schooltime");
        condition2.setFirstValue("2008-10-01");
        condition2.setSecondValue("2008-12-22");
        condition2.setType("Datetime");
        condition2.setOperator("between");
        list.add(condition2);
        String hql = "from Student s";
        QueryTranslate queryTranslate = new QueryTranslate(hql, list);
        String s = queryTranslate.toString();
        System.out.println("s = " + s);
        List list1 = getService().findByQuery(s);
        System.out.println("list1 = " + list1);
    }
}