锐客网

  1. 首页 > it技术 > >

用建造者模式实现一个防SQL注入的ORM框架

本文节选自《设计模式就该这样学》
1 建造者模式的链式写法 以构建一门课程为例，一个完整的课程由PPT课件、回放视频、课堂笔记、课后作业组成，但是这些内容的设置顺序可以随意调整，我们用建造者模式来代入理解一下。首先创建一个产品类Course。
@Data public class Course {private String name; private String ppt; private String video; private String note; private String homework; @Override public String toString() { return "CourseBuilder{" + "name='" + name + '\'' + ", ppt='" + ppt + '\'' + ", video='" + video + '\'' + ", note='" + note + '\'' + ", homework='" + homework + '\'' + '}'; } }

然后创建建造者类CourseBuilder，将复杂的创建过程封装起来，创建步骤由用户决定。
public class CourseBuilder {private Course course = new Course(); public CourseBuilder addName(String name){ course.setName(name); return this; }public CourseBuilder addPpt(String ppt){ course.setPpt(ppt); return this; }public CourseBuilder addVideo(String video){ course.setVideo(video); return this; }public CourseBuilder addNote(String note){ course.setNote(note); return this; }public CourseBuilder addHomework(String homework){ course.setHomework(homework); return this; }public Course builder(){ return course; }}

最后编写客户端测试代码。
public static void main(String[] args) { CourseBuilder builder = new CourseBuilder() .addName("设计模式") .addPPT("【PPT课件】") .addVideo("【回放视频】") .addNote("【课堂笔记】") .addHomework("【课后作业】"); System.out.println(builder.build()); }

【用建造者模式实现一个防SQL注入的ORM框架】这样的写法是不是很眼熟？后面分析建造者模式在框架源码中的应用时大家就会明白。再来看一下类图的变化，如下图所示。
用建造者模式实现一个防SQL注入的ORM框架
文章图片

2 使用静态内部类实现建造者模式 事实上，在平常的编码中，我们通常都会忽略对象的复杂性，优先考虑使用工厂模式创建对象，而不是建造者模式。因为工厂模式和建造者模式的作用都是创建一个产品对象，而工厂模式的结构更加简洁直接（没有Builder和 Director），因此更常使用。
一般情况下，我们更习惯使用静态内部类的方式实现建造者模式，即一个产品类内部自动带有一个具体建造者，由它负责该产品的组装创建，不再需要Builder和Director，这样，产品表示与创建之间的联系更加紧密，结构更加紧凑，同时使得建造者模式的形式更加简洁。
如果采用静态内部类形式实现建造者模式，则前面的案例可以改写如下。
@Data public class Course { private String name; private String ppt; private String video; private String note; private String homework; @Override public String toString() { return "Course{" + "name='" + name + '\'' + ", ppt='" + ppt + '\'' + ", video='" + video + '\'' + ", note='" + note + '\'' + ", homework='" + homework + '\'' + '}'; }public static class Builder {private Course course = new Course(); public Builder addName(String name){ course.setName(name); return this; }public Builder addPpt(String ppt){ course.setPpt(ppt); return this; }public Builder addVideo(String video){ course.setVideo(video); return this; }public Builder addNote(String note){ course.setNote(note); return this; }public Builder addHomework(String homework){ course.setHomework(homework); return this; }public Course builder(){ return course; }} }

客户端测试代码如下。
public static void main(String[] args) { Course course = new Course.Builder() .addName("设计模式") .addPpt("【PPT课件】") .addVideo("【录播视频】") .builder(); System.out.println(course); }

这样，代码也会看上去更加简洁，不会让人感觉到多了一个类。
3 使用建造者模式动态构建SQL语句 下面来看一个实战案例，这个案例参考了开源框架JPA的SQL构造模式。我们在构造SQL查询条件的时候，需要根据不同的条件来拼接SQL字符串。如果查询条件复杂，则SQL拼接的过程也会变得非常复杂，从而给代码维护带来非常大的困难。因此，我们用建造者类QueryRuleSqlBuilder将复杂的SQL构造过程进行封装，用QueryRule对象专门保存SQL查询时的条件，最后根据查询条件，自动生成SQL语句。首先创建QueryRule类，代码如下。
import java.io.Serializable; import java.util.ArrayList; import java.util.List; /** * QueryRule，主要功能用于构造查询条件 * * @author Tom */ public final class QueryRule implements Serializable { private static final long serialVersionUID = 1L; public static final int ASC_ORDER = 101; public static final int DESC_ORDER = 102; public static final int LIKE = 1; public static final int IN = 2; public static final int NOTIN = 3; public static final int BETWEEN = 4; public static final int EQ = 5; public static final int NOTEQ = 6; public static final int GT = 7; public static final int GE = 8; public static final int LT = 9; public static final int LE = 10; public static final int ISNULL = 11; public static final int ISNOTNULL = 12; public static final int ISEMPTY = 13; public static final int ISNOTEMPTY = 14; public static final int AND = 201; public static final int OR = 202; private List ruleList = new ArrayList(); private List queryRuleList = new ArrayList(); private String propertyName; private QueryRule() {}private QueryRule(String propertyName) { this.propertyName = propertyName; }public static QueryRule getInstance() { return new QueryRule(); }/** * 添加升序规则 * @param propertyName * @return */ public QueryRule addAscOrder(String propertyName) { this.ruleList.add(new Rule(ASC_ORDER, propertyName)); return this; }/** * 添加降序规则 * @param propertyName * @return */ public QueryRule addDescOrder(String propertyName) { this.ruleList.add(new Rule(DESC_ORDER, propertyName)); return this; }public QueryRule andIsNull(String propertyName) { this.ruleList.add(new Rule(ISNULL, propertyName).setAndOr(AND)); return this; }public QueryRule andIsNotNull(String propertyName) { this.ruleList.add(new Rule(ISNOTNULL, propertyName).setAndOr(AND)); return this; }public QueryRule andIsEmpty(String propertyName) { this.ruleList.add(new Rule(ISEMPTY, propertyName).setAndOr(AND)); return this; }public QueryRule andIsNotEmpty(String propertyName) { this.ruleList.add(new Rule(ISNOTEMPTY, propertyName).setAndOr(AND)); return this; }public QueryRule andLike(String propertyName, Object value) { this.ruleList.add(new Rule(LIKE, propertyName, new Object[] { value }).setAndOr(AND)); return this; }public QueryRule andEqual(String propertyName, Object value) { this.ruleList.add(new Rule(EQ, propertyName, new Object[] { value }).setAndOr(AND)); return this; }public QueryRule andBetween(String propertyName, Object... values) { this.ruleList.add(new Rule(BETWEEN, propertyName, values).setAndOr(AND)); return this; }public QueryRule andIn(String propertyName, List values) { this.ruleList.add(new Rule(IN, propertyName, new Object[] { values }).setAndOr(AND)); return this; }public QueryRule andIn(String propertyName, Object... values) { this.ruleList.add(new Rule(IN, propertyName, values).setAndOr(AND)); return this; }public QueryRule andNotIn(String propertyName, List values) { this.ruleList.add(new Rule(NOTIN, propertyName, new Object[] { values }).setAndOr(AND)); return this; }//此处省略部分代码public List getRuleList() { return this.ruleList; }public List getQueryRuleList() { return this.queryRuleList; }public String getPropertyName() { return this.propertyName; }protected class Rule implements Serializable { private static final long serialVersionUID = 1L; private int type; //规则的类型 private String property_name; private Object[] values; private int andOr = AND; public Rule(int paramInt, String paramString) { this.property_name = paramString; this.type = paramInt; }public Rule(int paramInt, String paramString, Object[] paramArrayOfObject) { this.property_name = paramString; this.values = paramArrayOfObject; this.type = paramInt; }public Rule setAndOr(int andOr){ this.andOr = andOr; return this; }public int getAndOr(){ return this.andOr; }public Object[] getValues() { return this.values; }public int getType() { return this.type; }public String getPropertyName() { return this.property_name; } } }
然后创建QueryRuleSqlBuilder类。
package com.tom.vip.pattern.builder.sql; /** * 根据QueryRule自动构建SQL语句 * @author Tom * */ public class QueryRuleSqlBuilder { private int CURR_INDEX = 0; //记录参数所在的位置 private List properties; //保存列名列表 private List values; //保存参数值列表 private List orders; //保存排序规则列表private String whereSql = ""; private String orderSql = ""; private Object [] valueArr = new Object[]{}; private Map valueMap = new HashMap(); /** * 获得查询条件 * @return */ private String getWhereSql(){ return this.whereSql; }/** * 获得排序条件 * @return */ private String getOrderSql(){ return this.orderSql; }/** * 获得参数值列表 * @return */ public Object [] getValues(){ return this.valueArr; }/** * 获得参数列表 * @return */ private Map getValueMap(){ return this.valueMap; }/** * 创建SQL构造器 * @param queryRule */ public QueryRuleSqlBuilder(QueryRule queryRule) { CURR_INDEX = 0; properties = new ArrayList(); values = new ArrayList(); orders = new ArrayList(); for (QueryRule.Rule rule : queryRule.getRuleList()) { switch (rule.getType()) { case QueryRule.BETWEEN: processBetween(rule); break; case QueryRule.EQ: processEqual(rule); break; case QueryRule.LIKE: processLike(rule); break; case QueryRule.NOTEQ: processNotEqual(rule); break; case QueryRule.GT: processGreaterThen(rule); break; case QueryRule.GE: processGreaterEqual(rule); break; case QueryRule.LT: processLessThen(rule); break; case QueryRule.LE: processLessEqual(rule); break; case QueryRule.IN: processIN(rule); break; case QueryRule.NOTIN: processNotIN(rule); break; case QueryRule.ISNULL: processIsNull(rule); break; case QueryRule.ISNOTNULL: processIsNotNull(rule); break; case QueryRule.ISEMPTY: processIsEmpty(rule); break; case QueryRule.ISNOTEMPTY: processIsNotEmpty(rule); break; case QueryRule.ASC_ORDER: processOrder(rule); break; case QueryRule.DESC_ORDER: processOrder(rule); break; default: throw new IllegalArgumentException("type"+rule.getType()+"not supported."); } } //拼装where语句 appendWhereSql(); //拼装排序语句 appendOrderSql(); //拼装参数值 appendValues(); }/** * 去掉order * * @param sql * @return */ private String removeOrders(String sql) { Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE); Matcher m = p.matcher(sql); StringBuffer sb = new StringBuffer(); while (m.find()) { m.appendReplacement(sb, ""); } m.appendTail(sb); return sb.toString(); }/** * 去掉select * * @param sql * @return */ private String removeSelect(String sql) { if(sql.toLowerCase().matches("from\\s+")){ int beginPos = sql.toLowerCase().indexOf("from"); return sql.substring(beginPos); }else{ return sql; } }/** * 处理like * @param rule */ privatevoid processLike(QueryRule.Rule rule) { if (ArrayUtils.isEmpty(rule.getValues())) { return; } Object obj = rule.getValues()[0]; if (obj != null) { String value = https://www.it610.com/article/obj.toString(); if (!StringUtils.isEmpty(value)) { value = value.replace('*', '%'); obj = value; } } add(rule.getAndOr(),rule.getPropertyName(),"like","%"+rule.getValues()[0]+"%"); }/** * 处理between * @param rule */ privatevoid processBetween(QueryRule.Rule rule) { if ((ArrayUtils.isEmpty(rule.getValues())) || (rule.getValues().length < 2)) { return; } add(rule.getAndOr(),rule.getPropertyName(),"","between",rule.getValues()[0],"and"); add(0,"","","",rule.getValues()[1],""); }//此处省略部分代码/** * 加入SQL查询规则队列 * @param andOr and或者or * @param key 列名 * @param split 列名与值之间的间隔 * @param value 值 */ privatevoid add(int andOr,String key,String split ,Object value){ add(andOr,key,split,"",value,""); }/** * 加入SQL查询规则队列 * @param andOr and或者or * @param key 列名 * @param split 列名与值之间的间隔 * @param prefix 值前缀 * @param value 值 * @param suffix 值后缀 */ privatevoid add(int andOr,String key,String split ,String prefix,Object value,Stringsuffix){ String andOrStr = (0 == andOr ? "" :(QueryRule.AND == andOr ? " and " : " or ")); properties.add(CURR_INDEX, andOrStr + key + " " + split + prefix + (null != value ? " ? " : " ") + suffix); if(null != value){ values.add(CURR_INDEX,value); CURR_INDEX ++; } }/** * 拼装where语句 */ private void appendWhereSql(){ StringBuffer whereSql = new StringBuffer(); for (String p : properties) { whereSql.append(p); } this.whereSql = removeSelect(removeOrders(whereSql.toString())); }/** * 拼装排序语句 */ private void appendOrderSql(){ StringBuffer orderSql = new StringBuffer(); for (int i = 0 ; i < orders.size(); i ++) { if(i > 0 && i < orders.size()){ orderSql.append(","); } orderSql.append(orders.get(i).toString()); } this.orderSql = removeSelect(removeOrders(orderSql.toString())); }/** * 拼装参数值 */ private void appendValues(){ Object [] val = new Object[values.size()]; for (int i = 0; i < values.size(); i ++) { val[i] = values.get(i); valueMap.put(i, values.get(i)); } this.valueArr = val; }public String builder(String tableName){ String ws = removeFirstAnd(this.getWhereSql()); String whereSql = ("".equals(ws) ? ws : (" where " + ws)); String sql = "select * from " + tableName + whereSql; Object [] values = this.getValues(); String orderSql = this.getOrderSql(); orderSql = (StringUtils.isEmpty(orderSql) ? " " : (" order by " + orderSql)); sql += orderSql; return sql; }private String removeFirstAnd(String sql){ if(StringUtils.isEmpty(sql)){return sql; } return sql.trim().toLowerCase().replaceAll("^\\s*and", "") + " "; }}
接着创建Order类。
/** * SQL排序组件 * @author Tom */ public class Order { private boolean ascending; //升序还是降序 private String propertyName; //哪个字段升序，哪个字段降序public String toString() { return propertyName + ' ' + (ascending ? "asc" : "desc"); }/** * Constructor for Order. */ protected Order(String propertyName, boolean ascending) { this.propertyName = propertyName; this.ascending = ascending; }/** * Ascending order * * @param propertyName * @return Order */ public static Order asc(String propertyName) { return new Order(propertyName, true); }/** * Descending order * * @param propertyName * @return Order */ public static Order desc(String propertyName) { return new Order(propertyName, false); }}

最后编写客户端测试代码。
public static void main(String[] args) { QueryRule queryRule = QueryRule.getInstance(); queryRule.addAscOrder("age"); queryRule.andEqual("addr","Changsha"); queryRule.andLike("name","Tom"); QueryRuleSqlBuilder builder = new QueryRuleSqlBuilder(queryRule); System.out.println(builder.builder("t_member")); System.out.println("Params: " + Arrays.toString(builder.getValues())); }

这样一来，客户端代码就非常清楚，运行结果如下图所示。
用建造者模式实现一个防SQL注入的ORM框架
文章图片

本文为“Tom弹架构”原创，转载请注明出处。技术在于分享，我分享我快乐！
如果本文对您有帮助，欢迎关注和点赞；如果您有任何建议也可留言评论或私信，您的支持是我坚持创作的动力。关注微信公众号『 Tom弹架构 』可获取更多技术干货！

    推荐阅读


    上一篇：页面路径分析模型全新上线，让你的产品更懂用户

    下一篇：java第7天（数组）