一个处理SQL语句的Object对象

起因：

为了一个复杂的所谓的“万能查询”,允许用户自己选择各种条件进行查询。

根据查询条件和查询内容的不同，可能需要增加表，增加表时候还要显示这个表提供的可供选择的条件等等...

下面实现的内容是这个万能查询的SQL基础。

说明：

private final String SELECT = "SELECT";private final String FROM = "FROM";private final String WHERE = "WHERE";private final String GROUP_BY = "GROUP BY";private final String HAVING = "HAVING";private final String ORDER_BY = "ORDER BY";private List<String> selectList;private List<String> fromList; private List<String> whereList;private List<String> groupList;private List<String> havingList; private List<String> orderList;

程序根据查询语句创建了这六个list,分别存放对应的内容

针对这六个list提供了多种方式进行赋值和取值操作

1.其中getter和setter方法为默认的list赋值取值方式

2.提供了String类型的getter和setter(String)方法

3.提供了put(String)和remove(String)方法对List进行修改

重写toString()方法输出内容。

用途：在对SQL进行更复杂的拼接时可以用到。

package com.insigma.siis.local.comm.entity;import java.util.ArrayList;import java.util.HashSet;import java.util.List;public class SqlStr {/**SELECT select_list   [ INTO new_table ]   FROM table_source   [ WHERE search_condition ]   [ GROUP BY group_by_expression ]   [ HAVING search_condition ]   [ ORDER BY order_expression [ ASC | DESC ] ] */private final String SELECT = "SELECT";private final String FROM = "FROM";private final String WHERE = "WHERE";private final String GROUP_BY = "GROUP BY";private final String HAVING = "HAVING";private final String ORDER_BY = "ORDER BY";private List<String> selectList;private List<String> fromList; private List<String> whereList;private List<String> groupList;private List<String> havingList; private List<String> orderList;public void Init(){selectList = new ArrayList<String>();fromList = new ArrayList<String>();whereList = new ArrayList<String>();groupList = new ArrayList<String>();havingList = new ArrayList<String>();orderList = new ArrayList<String>();}public SqlStr(){Init();}public SqlStr(String sql) throws Exception{Init();String SQL = sql.toUpperCase();int selectindex = SQL.indexOf(SELECT);int fromindex = SQL.indexOf(FROM);int whereindex = SQL.indexOf(WHERE);int groupindex = SQL.indexOf(GROUP_BY);int havingindex = SQL.indexOf(HAVING);int orderindex = SQL.indexOf(ORDER_BY);if(selectindex==-1||fromindex==-1){throw new Exception("sql不是一个符合要求的语句");}String selectString = sql.substring(SELECT.length(), fromindex);String fromString = sql.substring(FROM.length()+fromindex, whereindex!=-1?whereindex:(groupindex!=-1?groupindex:(havingindex!=-1?havingindex:(orderindex!=-1?orderindex:sql.length()))));String whereString = null;String groupString = null;String havingString = null;String orderString = null;putSelectStr(selectString);putFromStr(fromString);if(whereindex!=-1){whereString = sql.substring(WHERE.length()+whereindex, groupindex!=-1?groupindex:(havingindex!=-1?havingindex:(orderindex!=-1?orderindex:sql.length())));putWhereStr(whereString);}if(groupindex!=-1){groupString = sql.substring(GROUP_BY.length()+groupindex,havingindex!=-1?havingindex:(orderindex!=-1?orderindex:sql.length()));putGroupStr(groupString);}if(havingindex!=-1){havingString = sql.substring(HAVING.length()+havingindex,orderindex!=-1?orderindex:sql.length());putHavingStr(havingString);}if(orderindex!=-1){orderString = sql.substring(ORDER_BY.length()+orderindex);putOrderStr(orderString);}}@Overridepublic String toString() {checkAll();if(selectList.size()==0||fromList.size()==0){return "";}StringBuffer sBuffer = new StringBuffer("select ");sBuffer.append(ListToString(selectList, ","));sBuffer.append(" from ");sBuffer.append(ListToString(fromList, ","));if(whereList.size()>0){sBuffer.append(" where ");sBuffer.append(ListToString(whereList, " and "));}if(groupList.size()>0){sBuffer.append(" group by ");sBuffer.append(ListToString(groupList, ","));}if(havingList.size()>0){sBuffer.append(" having ");sBuffer.append(ListToString(havingList, ","));}if(orderList.size()>0){sBuffer.append(" order by ");sBuffer.append(ListToString(orderList, ","));}return sBuffer.toString();}@SuppressWarnings({ "unchecked", "rawtypes" })public List checkList(List list){HashSet set = new HashSet(list); if(set.size() != list.size()){return new ArrayList<String>(set);}return list;}@SuppressWarnings("unchecked")public void checkAll(){selectList = checkList(selectList);fromList = checkList(fromList);whereList = checkList(whereList);groupList = checkList(groupList);havingList = checkList(havingList);orderList = checkList(orderList);}/** * *****************************String类型的put********************************************** */@SuppressWarnings("unchecked")public void putSelectStr(String selectStr) {this.selectList.addAll(StringToList(selectStr, ","));}@SuppressWarnings("unchecked")public void putFromStr(String fromStr) {this.fromList.addAll(StringToList(fromStr, ","));}@SuppressWarnings("unchecked")public void putWhereStr(String whereStr) {this.whereList.addAll(StringToList(whereStr, "and"));}@SuppressWarnings("unchecked")public void putGroupStr(String groupStr) {this.groupList.addAll(StringToList(groupStr, ","));}@SuppressWarnings("unchecked")public void putHavingStr(String havingStr) {this.havingList.addAll(StringToList(havingStr, ","));}@SuppressWarnings("unchecked")public void putOrderStr(String orderStr) {this.orderList.addAll(StringToList(orderStr, ","));}/** * *****************************String类型的remove********************************************** */public void removeSelectStr(String selectStr) {this.selectList.removeAll(StringToList(selectStr, ","));}public void removeFromStr(String fromStr) {this.fromList.removeAll(StringToList(fromStr, ","));}public void removeWhereStr(String whereStr) {this.whereList.removeAll(StringToList(whereStr, " and "));}public void removeGroupStr(String groupStr) {this.groupList.removeAll(StringToList(groupStr, ","));}public void removeHavingStr(String havingStr) {this.havingList.removeAll(StringToList(havingStr, ","));}public void removeOrderStr(String orderStr) {this.orderList.removeAll(StringToList(orderStr, ","));}/** * *****************************String类型的getter和setter********************************************** */public String getSelectStr() {return ListToString(selectList,",");}@SuppressWarnings("unchecked")public void setSelectStr(String selectStr) {this.selectList = StringToList(selectStr, ",");}public String getFromStr() {return ListToString(fromList,",");}@SuppressWarnings("unchecked")public void setFromStr(String fromStr) {this.fromList = StringToList(fromStr, ",");}public String getWhereStr() {return ListToString(whereList," and ");}@SuppressWarnings("unchecked")public void setWhereStr(String whereStr) {this.whereList = StringToList(whereStr, ",");}public String getGroupStr() {return ListToString(groupList,",");}@SuppressWarnings("unchecked")public void setGroupStr(String groupStr) {this.groupList = StringToList(groupStr, ",");}public String getHavingStr() {return ListToString(havingList,",");}@SuppressWarnings("unchecked")public void setHavingStr(String havingStr) {this.havingList = StringToList(havingStr, ",");}public String getOrderStr() {return ListToString(orderList,",");}@SuppressWarnings("unchecked")public void setOrderStr(String orderStr) {this.orderList = StringToList(orderStr, ",");}/** * *****************************默认的getter和setter********************************************** */public List<String> getSelectList() {return selectList;}public void setSelectList(List<String> selectList) {this.selectList = selectList;}public List<String> getFromList() {return fromList;}public void setFromList(List<String> fromList) {this.fromList = fromList;}public List<String> getWhereList() {return whereList;}public void setWhereList(List<String> whereList) {this.whereList = whereList;}public List<String> getGroupList() {return groupList;}public void setGroupList(List<String> groupList) {this.groupList = groupList;}public List<String> getHavingList() {return havingList;}public void setHavingList(List<String> havingList) {this.havingList = havingList;}public List<String> getOrderList() {return orderList;}public void setOrderList(List<String> orderList) {this.orderList = orderList;}//list转为字符串    @SuppressWarnings("rawtypes")    public static String ListToString(List list,String spilt){        StringBuffer sBuffer = new StringBuffer();        for(int i=0;i<list.size();i++){            if(!sBuffer.toString().equals("")){                sBuffer.append(spilt);            }            sBuffer.append(list.get(i));        }        return sBuffer.toString();    }    //字符串转list    @SuppressWarnings({ "rawtypes", "unchecked" })    public static List StringToList(String str,String spilt){        String[] strs = str.split(spilt);        if(strs.length>0){            List list = new ArrayList();            for(int i=0;i<strs.length;i++){                list.add(strs[i].trim());            }            return list;        }        return null;    }        /**     * 测试方法     * @param args     * @throws Exception     */public static void main(String[] args) throws Exception {SqlStr sql4 = new SqlStr("select aac001,aac002,aac003 from ac17,ac01,ac02 where aac001=6864483 group by aac001,aac002 having count(aac001)>1 order by aac002");sql4.putFromStr("ac17,ac05");sql4.removeSelectStr("aac001,aac003");System.out.println(sql4);SqlStr sqlStr = new SqlStr("select * from ac01");System.out.println(sqlStr);}}

测试方法的输出结果：

select aac002 from ac17,ac01,ac05,ac02 where aac001=6864483 group by aac001,aac002 having count(aac001)>1 order by aac002