使用hibernate时，后台生成SQL的实体类

前段时间闲着没事就搞了个封装SQL的实体类。

主要是看代码里面的各种拼接insert update select太多了。看着心烦

方法有些没有带注释，拿来就能用的。  能扩展的一起扩展呀，这个没写完，好多点也没考虑到。非喜勿喷啊。 毕竟是个三年的菜鸡。

import org.hibernate.Session;
import org.hibernate.query.Query;
import org.hibernate.type.Type;

import java.util.*;

/**
 * SqlBuilderEntity api
 * create           创建一个sql，需要传入一个sql
 * createSelectAll  创建一个查询所有的sql  需要传入表的名字
 * createSelectOne  创建一个查询某个字段的sql   需要传入查询的字段名称和表名字
 * getCountSql  得到select  count  sql。
 * append  在sql后面追加一个and ，考虑key和value可能不一样，所以单独提了出来
 * appendWhere 在sql后面追加一个where
 */
public class SqlBuilderEntity {

    private static final String SELECT_ALL                       = " SELECT * FROM ";
    private static final String SELECT                           = "SELECT";
    private static final String UPDATE                           = "UPDATE";
    private static final String DELETE                           = "DELETE";
    private static final String INSERT                           = "INSERT INTO";
    private static final String VALUES                           = "VALUES";
    private static final String LEFT_BRACKET                     = "(";
    private static final String RIGHT_BRACKET                    = ")";
    private static final String SET                              = "SET";
    private static final String WHERE                            = "WHERE";
    private static final String ONE_EQ_ONE                       = "1=1";
    private static final String FORM                             = "FROM";
    private static final String ORDER                            ="ORDER";
    private static final String AND                              ="AND";
    private static final String SEPARATION_COLON                 = ":";
    private static final String EQUAL_SIGN                       = "=";
    private static final String SPACE                            = " ";
    private static final String COMMA                            = ",";
    private static final String NO_EQ                            = "<>";
    private StringBuilder sqlBuilder;
    private int firstResult = 0;
    private int maxResults = 0;
    //value是object的操作集
    private List<String> params;
    private List<Object> values;
    private List<Type>  types;
    //value是集合的操作集
    private List<String> paramsList;
    private List<Collection<Object>> valuesList;
    private List<Type> typesList;
    //value是数组的操作集
    private List<String> paramsArray;
    private List<Object[]> valuesArray;
    private List<Type> typesArray;

    private int errorCode = -1;

    public int getErrorCode() {
        return errorCode;
    }

    public void setErrorCode(int errorCode) {
        this.errorCode = errorCode;
    }

    public String getErrorMsg() {
        return errorMsg;
    }

    public void setErrorMsg(String errorMsg) {
        this.errorMsg = errorMsg;
    }

    private String errorMsg;

    public int getFirstResult() {
        return firstResult;
    }

    public void setFirstResult(int firstResult) {
        this.firstResult = firstResult;
    }

    public int getMaxResults() {
        return maxResults;
    }

    public void setMaxResults(int maxResults) {
        this.maxResults = maxResults;
    }

    /**
     * 写入分页分数
     * @param query
     * @return
     */
    public Query setPaging(Query query) {
        query.setMaxResults(this.maxResults);
        query.setFirstResult((this.firstResult-1)*this.maxResults);
        return query;
    }


    /**
     * 构造
     */
    public SqlBuilderEntity () {
        this.sqlBuilder = new StringBuilder();
    }

    /**
     * 构造Sql
     * @param sql
     */
    public SqlBuilderEntity(String sql) {
        this.sqlBuilder = new StringBuilder(sql);
    }

    /**
     * 创建SqlBuilderEntity
     * @param t
     * @return
     */
    public static SqlBuilderEntity create(String t) {
        SqlBuilderEntity sqlBuilderEntity = new SqlBuilderEntity(t);
        return sqlBuilderEntity;
    }

    /**
     * 创建查询所有的SQL
     *
     * @param t 表名称
     * @return
     */
    public static SqlBuilderEntity createSelectAll(String t) {
        SqlBuilderEntity sqlBuilderEntity = SqlBuilderEntity.create(SELECT_ALL);
        sqlBuilderEntity.append(t);
        return sqlBuilderEntity;
    }

    /**
     * 创建只查询一条的SQL语句
     * @param f 需要查询的字段
     * @param t 需要查询的表名称
     * @return
     */
    public static SqlBuilderEntity createSelectOne(String f,String t) {
        SqlBuilderEntity sqlBuilderEntity = SqlBuilderEntity.create(SELECT);
        sqlBuilderEntity.sqlBuilder.append(SPACE).append(f.toUpperCase(Locale.ROOT))
                .append(SPACE).append(FORM).append(SPACE).append(t);
        return sqlBuilderEntity;
    }


    /**
     * 创建一个update语句头  update  t  set
     * @param t
     * @return
     */
    public static SqlBuilderEntity createUpdate(String t) {
        SqlBuilderEntity sqlBuilderEntity = SqlBuilderEntity.create(UPDATE);
        sqlBuilderEntity.append(t).appendSet();
        return sqlBuilderEntity;
    }

    public static SqlBuilderEntity createInsert(String t) {
        SqlBuilderEntity sqlBuilderEntity = SqlBuilderEntity.create(INSERT);
        sqlBuilderEntity.append(t).append(LEFT_BRACKET);
        return sqlBuilderEntity;
    }

    /**
     * 格式化update  去除 ,  一定要拼接完,格式化一下。
     * @return
     */
    public SqlBuilderEntity formatUpdateByComma(){
        if(StringUtil.isHasLastIndexOfStr(this.sqlBuilder,COMMA)){
            this.sqlBuilder = new StringBuilder(this.sqlBuilder.substring(0,this.sqlBuilder.lastIndexOf(COMMA)));
        }
        return this;
    }

    /**
     * 获取查询count的Sql
     *
     * @return countSql
     */
    public String getCountSql(){
        String countSql = this.sqlBuilder.toString();
        int fromIndex = countSql.toUpperCase(Locale.ROOT).indexOf(FORM);
        String projectionSql = countSql.substring(0, fromIndex);
        countSql = countSql.substring(fromIndex);
        String rowCountSql = countSql;
        int index = rowCountSql.indexOf(ORDER);
        if (index > 0) {
            rowCountSql = rowCountSql.substring(0, index);
        }

        countSql = this.wrapProjection(projectionSql) + rowCountSql;
        return countSql;
    }

    /**
     * 拼装查询个数的SQL
     *
     * @param projection
     * @return
     */
    private String wrapProjection(String projection) {
        return projection.indexOf("SELECT") == -1 ? "SELECT COUNT(*) " : projection.replace("SELECT", "SELECT COUNT(") + ") ";
    }

    /**
     * 增加where
     *
     * @param isUpdate 是不是修改 是不需要拼接1=1
     * @return
     */
    public  SqlBuilderEntity appendWhere(boolean isUpdate) {
        this.sqlBuilder.append(SPACE).append(WHERE).append(SPACE);
        if(!isUpdate) {
            this.sqlBuilder.append(ONE_EQ_ONE).append(SPACE);
        }
        return this;
    }

    /**
     * 增加set
     *
     * @return
     */
    public  SqlBuilderEntity appendSet() {
        this.sqlBuilder.append(SPACE).append(SET).append(SPACE).append(SPACE);
        return this;
    }

    /**
     * append key
     * @param v
     * @return [ AND `key` ]
     */
    public SqlBuilderEntity appendAnd(String v) {
        this.sqlBuilder.append(AND).append(SPACE).append(v).append(SPACE);
        return this;
    }

    public SqlBuilderEntity append(String v) {
        this.sqlBuilder.append(SPACE).append(v).append(SPACE);
        return this;
    }

    public SqlBuilderEntity append(StringBuilder v) {
        this.sqlBuilder.append(SPACE).append(v).append(SPACE);
        return this;
    }

    public SqlBuilderEntity append(SqlBuilderEntity sqlBuilderEntity) {
        this.sqlBuilder.append(SPACE).append(sqlBuilderEntity.getOrigSql()).append(SPACE);
        return this;
    }

    public SqlBuilderEntity appendInsertValues() {
        this.sqlBuilder.append(RIGHT_BRACKET).append(SPACE).append(VALUES).append(SPACE).append(LEFT_BRACKET);
        return this;
    }

    public SqlBuilderEntity appendRightBracket() {
        this.sqlBuilder.append(RIGHT_BRACKET).append(SPACE);
        return this;
    }

    /**
     * append value
     * @param f
     * @param isInsert
     * @return [= :value ]
     */
    public SqlBuilderEntity appendEqValueFiled(String f, boolean isInsert) {
        if(isInsert) {
            this.sqlBuilder.append(SPACE).append(SEPARATION_COLON).append(f).append(SPACE);
        } else {
            this.sqlBuilder.append(EQUAL_SIGN).append(SPACE).append(SEPARATION_COLON).append(f).append(SPACE);
        }
        return this;
    }

    public SqlBuilderEntity appendNoEqValueFiled(String f) {
        this.sqlBuilder.append(NO_EQ).append(SPACE).append(SEPARATION_COLON).append(f).append(SPACE);
        return this;
    }


    /**
     * 拼接逗号
     * @return
     */
    public SqlBuilderEntity appendComma() {
        this.sqlBuilder.append(SPACE).append(COMMA).append(SPACE);
        return this;
    }
    /***
     **数据封装开始**
     ****/

    /**
     * 对一个map进行操作
     *
     * @param map
     * @return
     */
    public SqlBuilderEntity setParamByMap(Map<String, Object> map) {
        if(map != null && !map.isEmpty()) {
            Set<String> keySet = map.keySet();
            for(String key : keySet) {
                this.setParam(key,map.get(key));
            }
        }
        return this;
    }

    /**
     * 写入key value type
     * @param p 键
     * @param v 值
     * @param t 类型
     * @return sqlbuilderEntity
     */
    public SqlBuilderEntity setParam(String p, Object v, Type t) {
        this.getParams().add(p);
        this.getValues().add(v);
        this.getTypes().add(t);
        return this;
    }

    public SqlBuilderEntity setParam(String p, Object v) {
        return this.setParam(p, v, (Type) null);
    }


    public List<String> getParams() {
        if(this.params == null) {
            this.params =  new ArrayList<String>();
        }
        return this.params;
    }


    public List<Object> getValues() {
        if(this.values == null) {
            this.values =  new ArrayList<Object>();
        }
        return this.values;
    }


    public List<Type> getTypes() {
        if(this.types == null) {
            this.types =  new ArrayList<Type>();
        }
        return this.types;
    }


    public List<String> getParamsList() {
        if(this.paramsList == null) {
            this.paramsList =  new ArrayList<String>();
        }
        return this.paramsList;
    }

    public List<Collection<Object>> getValuesList() {
        if(this.valuesList == null) {
            this.valuesList =  new ArrayList<Collection<Object>>();
        }
        return this.valuesList;
    }


    public List<Type> getTypesList() {
        if(this.typesList == null) {
            this.typesList =  new ArrayList<Type>();
        }
        return this.typesList;
    }


    public List<String> getParamsArray() {
        if(this.paramsArray == null) {
            this.paramsArray =  new ArrayList<String>();
        }
        return this.paramsArray;
    }


    public List<Object[]> getValuesArray() {
        if(this.valuesArray == null) {
            this.valuesArray = new ArrayList<Object[]>();
        }
        return this.valuesArray;
    }


    public List<Type> getTypesArray() {
        if(this.typesArray == null) {
            this.typesArray = new ArrayList<Type>();
        }
        return typesArray;
    }



    /*****获取查询的Query*****/
    /**
     * 把值写入
     *
     * @param query
     * @return
     */
    public Query setParamsToQuery(Query query){
        int i;
        // value 是个object
        if(this.params != null) {
            for(i = 0; i < params.size(); i++){
                if(this.types.get(i) != null) {
                    query.setParameter((String)this.params.get(i), this.values.get(i), (Type) this.types.get(i));
                } else {
                    query.setParameter((String)this.params.get(i), this.values.get(i));
                }
            }
        }
        // value是个list
        if(this.paramsList != null) {
            for(i = 0; i < paramsList.size(); i++){
                if(this.typesList.get(i) != null) {
                    query.setParameterList((String)this.paramsList.get(i), (Collection)this.valuesList.get(i), (Type) this.typesList.get(i));
                } else {
                    query.setParameterList((String)this.paramsList.get(i), (Collection)this.valuesList.get(i));
                }
            }
        }

        // value 是个数组
        if(this.paramsArray != null) {
            for(i = 0; i < paramsArray.size(); i++){
                if(this.typesArray.get(i) != null) {
                    query.setParameterList((String)this.paramsArray.get(i), (Object[])this.valuesArray.get(i), (Type) this.typesArray.get(i));
                } else {
                    query.setParameterList((String)this.paramsArray.get(i), (Object[])this.valuesArray.get(i));
                }
            }
        }

        return query;
    }

    /**
     * 创建一个query
     *
     * @param s
     * @return
     */
    public Query createQuery(Session s) {
        return this.setParamsToQuery(s.createNativeQuery(this.getOrigSql()));
    }

    /**
     * 返回Sql
     * @return
     */
    public String getOrigSql() {
        return this.sqlBuilder.toString();
    }

}