SQL语句拼接工具，简化SQL语句拼写代码

package com.hjw.framework.dao.util; import java.util.ArrayList; import java.util.List; /** * SQL语句生成类，最终生成参数查询方式的SQL语句 * com.hebrf.mzzx.wssc.common.util * 2009-12-23 * @author 黄建伟 */ public abstract class SqlBuilder { private static final String EQUAL = "="; private static final String NO_EQUAL = "<>"; private static final String LESS = "<"; private static final String NO_LESS = ">="; private static final String GREAT = ">"; private static final String NO_GREAT = "<="; private static final String BETWEEN = " between "; private static final String NO_BETWEEN = " not between "; private static final String IS_NULL = " is null"; private static final String NOT_NULL = " is not null"; /** * select初始语句包含where子句 */ public static final int WIDTH_WHERE = 1; /** * select初始语句不包含where子句 */ public static final int NO_WHERE = 2; protected StringBuilder sqlBuilder; private int type;//条件类型，指是否生成where关键字 private StringBuilder selectBuilder;//select构造子句 protected StringBuilder conditionBuilder;//条件构造子句 protected List<Object> values;//值集合 private StringBuilder groupBuilder;//分组构造子句 private StringBuilder orderBuilder;//排序构造子句 protected boolean ischange;//判断是否添加了条件 protected Integer firstResult;//查询开始记录 protected Integer maxResult;//查询数量 /** * 清空内容 * */ public void dispose(){ selectBuilder = null; conditionBuilder = null; groupBuilder = null; orderBuilder = null; sqlBuilder = null; } /** * * @param select 初始查询语句 * @param type */ protected SqlBuilder(String select,int type){ selectBuilder = new StringBuilder(select); this.ischange = true; this.type = type; } protected SqlBuilder(StringBuilder select,int type){ selectBuilder = select; this.ischange = true; this.type = type; } /** * 返回MySql版本实例，初始语句中不包含where子句 * @param select * @return */ public static SqlBuilder builder(String select){ return SqlBuilderForMySql.builder(select); } public static SqlBuilder builder(StringBuilder select){ return SqlBuilderForMySql.builder(select); } /** * 返回MySql版本实例，初始语句中包含where子句 * @param select * @return */ public static SqlBuilder builderWithWhere(String select){ return SqlBuilderForMySql.builderWithWhere(select); } public static SqlBuilder builderWithWhere(StringBuilder select){ return SqlBuilderForMySql.builderWithWhere(select); } /** * 返回Oracle版本实例，初始语句中不包含where子句 * @param select * @return */ public static SqlBuilder builder4Oracle(String select){ return SqlBuilderForOracle.builder(select); } public static SqlBuilder builder4Oracle(StringBuilder select){ return SqlBuilderForOracle.builder(select); } /** * 返回Oracle版本实例，初始语句中包含where子句 * @param select * @return */ public static SqlBuilder builderWithWhere4Oracle(String select){ return SqlBuilderForOracle.builderWithWhere(select); } public static SqlBuilder builderWithWhere4Oracle(StringBuilder select){ return SqlBuilderForOracle.builderWithWhere(select); } /** * 添加字符串相等条件 * @param field 数据库字段名称 * @param value　比较的值 * @return */ public SqlBuilder equalsValue(String field,Object value){ return appendValue(field, value,EQUAL); } /** * 添加字符串不相等条件(field<>value) * @param field 数据库字段名称 * @param value　比较的值 * @return */ public SqlBuilder noEqualsValue(String field,Object value){ return appendValue(field, value,NO_EQUAL); } /** * 添加字段值小于字符串条件(field<value) * @param field 数据库字段名称 * @param value　比较的值 * @return */ public SqlBuilder lessValue(String field,Object value){ return appendValue(field, value,LESS); } /** * 添加字段值不小于字符串条件(field>=value) * @param field 数据库字段名称 * @param value　比较的值 * @return */ public SqlBuilder noLessValue(String field,Object value){ return appendValue(field, value,NO_LESS); } /** * 添加字段值大于字符串条件(filed>value) * @param field 数据库字段名称 * @param value　比较的值 * @return */ public SqlBuilder greatValue(String field,Object value){ return appendValue(field, value,GREAT); } /** * 添加字段值不大于字符串条件(filed<=value) * @param field 数据库字段名称 * @param value　比较的值 * @return */ public SqlBuilder noGreatValue(String field,Object value){ return appendValue(field, value,NO_GREAT); } /** * 添加字段值在两个字符串值之间的条件(filed between start and end) * @param field 数据库字段名称 * @param value　比较的值 * @return */ public SqlBuilder betweenValues(String field,Object start,Object end){ return appendValue(field, start, end,BETWEEN); } /** * 添加字段值不在两个字符串值之间的条件(filed not between start and end) * @param field 数据库字段名称 * @param value　比较的值 * @return */ public SqlBuilder noBetweenValues(String field,Object start,Object end){ return appendValue(field, start, end,NO_BETWEEN); } /** * 添加字段值在两个日期之间(field between start and end) * @param field * @param start * @param end * @return */ public SqlBuilder betweenDate(String field,String start,String end){ if(start == null || end == null ||start.equals("") || end.equals("")) return this; return this.betweenValues(field, this.toStartDate(start),this.toEndDate(end)); } /** * 添加字段值不在两个日期之间(field not between start and end) * @param field * @param start * @param end * @return */ public SqlBuilder noBetweenDate(String field,String start,String end){ if(start == null || end == null ||start.equals("") || end.equals("")) return this; return this.noBetweenValues(field, this.toStartDate(start),this.toEndDate(end)); } /** * 添加字段值大于一个日期的条件(field > value) */ public SqlBuilder greatDate(String field,String value){ if(value == null || value.equals("")) return this; return this.greatValue(field, this.toEndDate(value)); } /** * 添加字段值不大于一个日期的条件(field <= value) */ public SqlBuilder noGreatDate(String field,String value){ if(value == null || value.equals("")) return this; return this.noGreatValue(field, this.toEndDate(value)); } /** * 添加字段值小于一个日期的条件(field < value) */ public SqlBuilder lessDate(String field,String value){ if(value == null || value.equals("")) return this; return this.lessValue(field, this.toStartDate(value)); } /** * 添加字段值不小于一个日期的条件(field >= value) */ public SqlBuilder noLessDate(String field,String value){ if(value == null || value.equals("")) return this; return this.noLessValue(field, toStartDate(value)); } /** * 返回数据库的开始日期转换方式字符串 * @param value * @return */ public String toStartDate(String value){ return value + " 00:00:00"; } /** * 返回数据库的结束日期转换方式字符串 * @param value * @return */ public String toEndDate(String value){ return value + " 23:59:59"; } /** * 添加等于null的条件 */ public SqlBuilder isNull(String field){ return appendNullValue(field,IS_NUL