MyBatis3下的通用SQL的select语句执行类
项目中为了使项目的数据库操作从dao层转移到service层,只好写一个通用的sql语句拼接类和相应的mapper类和mapper的xml文件,自定义的SelectBuilder.java的代码如下:
- import java.io.IOException;
- import java.util.ArrayList;
- import java.util.List;
-
- import com.util.StringHelper;
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public class SelectBuilder
- {
- private static final String AND = ") \nAND (";
-
- private static final String OR = ") \nOR (";
-
- List<String> select = new ArrayList<String>();
-
- List<String> from = new ArrayList<String>();
-
- List<String> join = new ArrayList<String>();
-
- List<String> innerJoin = new ArrayList<String>();
-
- List<String> outerJoin = new ArrayList<String>();
-
- List<String> leftOuterJoin = new ArrayList<String>();
-
- List<String> rightOuterJoin = new ArrayList<String>();
-
- List<Criterion> where = new ArrayList<Criterion>();
-
- List<Criterion> having = new ArrayList<Criterion>();
-
- List<String> groupBy = new ArrayList<String>();
-
- List<String> orderBy = new ArrayList<String>();
-
- List<String> lastList = new ArrayList<String>();
-
- boolean distinct;
-
-
-
-
-
-
-
-
- public void SELECT(String conditons)
- {
- select.add(conditons);
- }
-
-
-
-
-
-
-
- public void SELECT_DISTINCT(String columns)
- {
- distinct = true;
- SELECT(columns);
- }
-
-
-
-
-
-
- public void FROM(String table)
- {
- from.add(table);
- }
-
-
-
-
-
-
- public void JOIN(String join)
- {
- this.join.add(join);
- }
-
-
-
-
-
-
- public void INNER_JOIN(String join)
- {
- innerJoin.add(join);
- }
-
-
-
-
-
-
- public void LEFT_OUTER_JOIN(String join)
- {
- leftOuterJoin.add(join);
- }
-
-
-
-
-
-
- public void RIGHT_OUTER_JOIN(String join)
- {
- rightOuterJoin.add(join);
- }
-
-
-
-
-
-
- public void OUTER_JOIN(String join)
- {
- outerJoin.add(join);
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public void WHERE(String conditions)
- {
- if(StringHelper.isNotBlank(conditions))
- where.add( new Criterion(conditions));
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public void WHERE(String conditions,Object value)
- {
- if(value!=null)
- {
- if (value instanceof List<?>)
- {
- if(!((List)value).isEmpty() )
- where.add( new Criterion(conditions,value));
- }
- else
- where.add( new Criterion(conditions,value));
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public void WHERE(String conditions,Object value,String close)
- {
- if(value!=null)
- {
- if (value instanceof List<?>)
- {
- if(!((List)value).isEmpty() )
- where.add( new Criterion(conditions,value,close));
- }
- else
- where.add( new Criterion(conditions,value,close));
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- public void WHERE(String conditions,Object value,Object secondValue,String close)
- {
- if(value!=null&&secondValue!=null)
- where.add( new Criterion(conditions,value,secondValue,close));
- }
-
-
-
-
-
-
-
- public void GROUP_BY(String columns)
- {
- groupBy.add(columns);
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public void HAVING(String conditions)
- {
- if(StringHelper.isNotBlank(conditions))
- having.add( new Criterion(conditions));
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public void HAVING(String conditions,Object value)
- {
- if(value!=null)
- {
- if (value instanceof List<?>)
- {
- if(!((List)value).isEmpty() )
- having.add( new Criterion(conditions,value));
- }
- else
- having.add( new Criterion(conditions,value));
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- public void HAVING(String conditions,Object value,String close)
- {
- if(value!=null)
- {
- if (value instanceof List<?>)
- {
- if(!((List)value).isEmpty() )
- having.add( new Criterion(conditions,value,close));
- }
- else
- having.add( new Criterion(conditions,value,close));
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
- public void HAVING(String conditions,Object value,Object secondValue,String close)
- {
- if(value!=null&&secondValue!=null)
- having.add( new Criterion(conditions,value,secondValue,close));
- }
-
-
-
-
-
-
- public void ORDER_BY(String conditions)
- {
- orderBy.add(conditions);
- }
-
- private StringBuilder selectClause( String keyword,
- List<String> parts, String open, String close, String conjunction)
- {
- StringBuilder builder=new StringBuilder();
- if (!parts.isEmpty())
- {
- if (builder.length() > 0)
- builder.append("\n");
- builder.append(keyword);
- builder.append(" ");
- builder.append(open);
- String last = "________";
- for (int i = 0, n = parts.size(); i < n; i++)
- {
- String part = parts.get(i);
- if (i > 0 && !part.equals(AND) && !part.equals(OR) && !last.equals(AND)
- && !last.equals(OR))
- {
- builder.append(conjunction);
- }
- builder.append(part);
- last = part;
- }
- builder.append(close);
- }
- return builder;
- }
-
- private void whereClause()
- {
- if(where.size()>0)
- {
- System.out.print(" WHERE 1=1 ");
- for(Criterion cri:where)
- {
- if(cri.isNoValue())
- System.out.print(cri.getCondition());
- if(cri.isSingleValue())
- System.out.print(cri.getCondition()+" '"+cri.getValue()+"'");
- if(cri.isBetweenValue())
- System.out.print(cri.getCondition()+" '"+cri.getValue()+"' and '"+cri.getSecondValue()+"'");
- if(cri.isListValue())
- {
- StringBuilder strb= new StringBuilder();
- strb.append(cri.getCondition());
- strb.append(" (");
- List list=(List)cri.getValue();
- for(int i=0;i<list.size();i++)
- {
- Object o=list.get(i);
- strb.append("'"+o+"'");
- if(i<list.size()-1)
- strb.append(",");
- }
- strb.append(")");
- System.out.print(strb);
- }
-
- if(cri.close==null||cri.close.equals(""))
- System.out.println();
- else
- System.out.println(cri.close);
-
- }
- }
- }
- private void havingClause()
- {
- if(having.size()>0)
- {
- System.out.print(" HAVING 1=1 ");
- for(Criterion cri:having)
- {
- if(cri.isNoValue())
- System.out.print(cri.getCondition());
- if(cri.isSingleValue())
- System.out.print(cri.getCondition()+" '"+cri.getValue()+"'");
- if(cri.isBetweenValue())
- System.out.print(cri.getCondition()+" '"+cri.getValue()+"' and '"+cri.getSecondValue()+"'");
- if(cri.isListValue())
- {
- StringBuilder strb= new StringBuilder();
- strb.append(cri.getCondition());
- strb.append(" (");
- List list=(List)cri.getValue();
- for(int i=0;i<list.size();i++)
- {
- Object o=list.get(i);
- strb.append("'"+o+"'");
- if(i<list.size()-1)
- strb.append(",");
- }
- strb.append(")");
- System.out.print(strb);
- }
-
- if(cri.close==null||cri.close.equals(""))
- System.out.println();
- else
- System.out.println(cri.close);
- }
- }
- }
-
-
-
- public void printSQL()
- {
- if(!select.isEmpty())
- System.out.println(selectClause( "SELECT", this.select, "", "", ", "));
- if(!from.isEmpty())
- System.out.println(selectClause( "FROM", this.from, "", "", ", "));
- if(!innerJoin.isEmpty())
- System.out.println(selectClause("INNER JOIN", this.innerJoin, "", "", "\nINNER JOIN "));
- if(!outerJoin.isEmpty())
- System.out.println(selectClause("OUTER JOIN", this.outerJoin, "", "", "\nOUTER JOIN "));
- if(!leftOuterJoin.isEmpty())
- System.out.println(selectClause(" LEFT OUTER JOIN", this.leftOuterJoin, "", "", "\nLEFT OUTER JOIN "));
- if(!rightOuterJoin.isEmpty())
- System.out.println(selectClause(" RIGHT OUTER JOIN", this.rightOuterJoin, "", "", "\nRIGHT OUTER JOIN "));
- whereClause();
- if(!groupBy.isEmpty())
- System.out.println(selectClause(" GROUP BY", this.groupBy, "", "", ", "));
- havingClause();
- if(!orderBy.isEmpty())
- System.out.println(selectClause(" ORDER BY", this.orderBy, "", "", ", "));
-
-
-
- }
-
-
-
-
-
- public class Criterion {
- private String condition;
-
- private Object value;
-
- private Object secondValue;
-
- private boolean noValue;
-
- private boolean singleValue;
-
- private boolean betweenValue;
-
- private boolean listValue;
-
- private String close;
-
- private String typeHandler;
-
- public String getCondition() {
- return condition;
- }
-
- public Object getValue() {
- return value;
- }
-
- public Object getSecondValue() {
- return secondValue;
- }
-
- public boolean isNoValue() {
- return noValue;
- }
-
- public boolean isSingleValue() {
- return singleValue;
- }
-
- public boolean isBetweenValue() {
- return betweenValue;
- }
-
- public boolean isListValue() {
- return listValue;
- }
-
- public String getTypeHandler() {
- return typeHandler;
- }
-
- public String getClose() {
- return close;
- }
-
- public void setClose(String close) {
- this.close = close;
- }
-
- protected Criterion(String condition) {
- super();
- this.condition = condition;
- this.typeHandler = null;
- this.noValue = true;
- }
-
-
- protected Criterion(String condition, Object value) {
- this.condition = condition;
- this.value = value;
- if (value instanceof List<?>) {
- this.listValue = true;
- } else {
- this.singleValue = true;
- }
- }
- protected Criterion(String condition, Object value,String close) {
- this.condition = condition;
- this.value = value;
- this.close=close;
- if (value instanceof List<?>) {
- this.listValue = true;
- } else {
- this.singleValue = true;
- }
- }
-
- protected Criterion(String condition, Object value, Object secondValue, String close) {
- super();
- this.condition = condition;
- this.value = value;
- this.secondValue = secondValue;
- this.close = close;
- this.betweenValue = true;
- }
-
- }
-
- public static void main(String[] args) throws IOException {
- SelectBuilder sql=new SelectBuilder();
- sql.SELECT("l.mc,l.id");
- System.out.println(sql.selectClause( "SELECT", sql.select, "", "", ", "));
- sql.FROM("bmd l,user u");
- System.out.println(sql.selectClause( "FROM", sql.from, "", "", ", "));
- sql.INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
- System.out.println(sql.selectClause("INNER JOIN", sql.innerJoin, "", "", "\nINNER JOIN "));
- sql.WHERE(" l.id=u.id");
- List l=new ArrayList();
- l.add("1");
- l.add("2");
- sql.WHERE("or( l.id in",l);
- sql.WHERE("and l.id between",1,2,")");
- sql.whereClause();
- sql.GROUP_BY("l.id,u.id");
- System.out.println(sql.selectClause( "GROUP BY", sql.groupBy, "", "", ", "));
- sql.HAVING(" l.id=u.id");
- List l2=new ArrayList();
- l2.add("1");
- l2.add("2");
- sql.HAVING("or( l.id in",l2);
- sql.HAVING("and l.id between","1","2",")");
- sql.havingClause();
-
-
- }
-
- public List<String> getSelect() {
- return select;
- }
-
-
- public List<String> getFrom() {
- return from;
- }
-
- public List<String> getJoin() {
- return join;
- }
-
- public List<String> getInnerJoin() {
- return innerJoin;
- }
-
-
- public List<String> getOuterJoin() {
- return outerJoin;
- }
-
-
- public List<String> getLeftOuterJoin() {
- return leftOuterJoin;
- }
-
- public List<String> getRightOuterJoin() {
- return rightOuterJoin;
- }
-
-
- public List<Criterion> getWhere() {
- return where;
- }
-
- public List<Criterion> getHaving() {
- return having;
- }
-
-
- public List<String> getGroupBy() {
- return groupBy;
- }
-
- public List<String> getOrderBy() {
- return orderBy;
- }
-
-
- public boolean isDistinct() {
- return distinct;
- }
-
-
- }
评论暂时关闭