Implement an ORM framework against SQL injection with builder mode

This article is excerpted from "design patterns should be learned this way"

1 chain writing of builder mode

Taking the construction of a Course as an example, a complete Course is composed of PPT courseware, playback video, classroom notes and after-school homework, but the setting order of these contents can be adjusted at will. Let's use the builder mode to understand it. First create a product class 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 + '\'' +
                '}';
    }
}

Then create the builder class CourseBuilder to encapsulate the complex creation process, and the creation steps are determined by the user.

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;
    }

}

Finally, write the client test code.


    public static void main(String[] args) {
        CourseBuilder builder = new CourseBuilder()
                    .addName("Design pattern")
                    .addPPT("[PPT Courseware]")
                    .addVideo("[Playback video]")
                    .addNote("[Classroom notes]")
                    .addHomework("[Homework]");

        System.out.println(builder.build());
}

Does this look familiar? Later, we will understand when we analyze the application of builder mode in the framework source code. Let's look at the changes of class diagram, as shown in the figure below.

2 using static inner classes to implement builder mode

In fact, in ordinary coding, we usually ignore the complexity of objects and give priority to using factory mode to create objects rather than Builder mode. Because the function of factory mode and Builder mode is to create a product object, and the structure of factory mode is more concise and direct (without Builder and Director), it is more often used.
Generally, we are more used to using static internal classes to implement the Builder mode, that is, a product class automatically has a specific Builder who is responsible for the assembly and creation of the product, and there is no need for Builder and Director. In this way, the relationship between product representation and creation is closer and the structure is more compact, At the same time, it makes the form of Builder mode more concise.
If the builder mode is implemented in the form of static internal classes, the previous case can be rewritten as follows.


@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;
        }

    }
}

The client test code is as follows.


    public static void main(String[] args) {
         Course course = new Course.Builder()
                .addName("Design pattern")
                .addPpt("[PPT Courseware]")
                .addVideo("[[recording and broadcasting video]")
                 .builder();

        System.out.println(course);
    }
		

In this way, the code will also look more concise and will not make people feel that there is an additional class.

3 build SQL statements dynamically using builder mode

Let's take a practical case, which refers to the SQL construction mode of the open source framework JPA. When constructing SQL query conditions, we need to splice SQL strings according to different conditions. If the query conditions are complex, the SQL splicing process will also become very complex, which brings great difficulties to code maintenance. Therefore, we use the builder class QueryRuleSqlBuilder to encapsulate the complex SQL construction process, use the QueryRule object to save the conditions of SQL query, and finally automatically generate SQL statements according to the query conditions. First create the QueryRule class with the following code.

import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;

/**
 * QueryRule,The main function is to construct query criteria
 * 
 * @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<Rule> ruleList = new ArrayList<Rule>();
	private List<QueryRule> queryRuleList = new ArrayList<QueryRule>();
	private String propertyName;

	private QueryRule() {}

	private QueryRule(String propertyName) {
		this.propertyName = propertyName;
	}

	public static QueryRule getInstance() {
		return new QueryRule();
	}
	
	/**
	 * Add ascending rule
	 * @param propertyName
	 * @return
	 */
	public QueryRule addAscOrder(String propertyName) {
		this.ruleList.add(new Rule(ASC_ORDER, propertyName));
		return this;
	}

	/**
	 * Add descending rule
	 * @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<Object> 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<Object> values) {
		this.ruleList.add(new Rule(NOTIN, 
									propertyName, 
									new Object[] { values }).setAndOr(AND));
		return this;
	}

	//Some codes are omitted here
	

	public List<Rule> getRuleList() {
		return this.ruleList;
	}

	public List<QueryRule> getQueryRuleList() {
		return this.queryRuleList;
	}

	public String getPropertyName() {
		return this.propertyName;
	}

	protected class Rule implements Serializable {
		private static final long serialVersionUID = 1L;
		private int type;	//Type of rule
		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;
		}
	}
}

Then create the QueryRuleSqlBuilder class.

package com.tom.vip.pattern.builder.sql;


/**
 * Automatically build SQL statements based on QueryRule
 * @author Tom
 *
 */
public class QueryRuleSqlBuilder {
	private int CURR_INDEX = 0; //Record the location of the parameter
	private List<String> properties; //Save list of column names
	private List<Object> values; //Save parameter value list
	private List<Order> orders; //Save collation list
	
	private String whereSql = ""; 
	private String orderSql = "";
	private Object [] valueArr = new Object[]{};
	private Map<Object,Object> valueMap = new HashMap<Object,Object>();
	
	/**
	 * Get query criteria
	 * @return
	 */
	private String getWhereSql(){
		return this.whereSql;
	}
	
	/**
	 * Get sort criteria
	 * @return
	 */
	private String getOrderSql(){
		return this.orderSql;
	}
	
	/**
	 * Get a list of parameter values
	 * @return
	 */
	public Object [] getValues(){
		return this.valueArr;
	}
	
	/**
	 * Get parameter list
	 * @return
	 */
	private Map<Object,Object> getValueMap(){
		return this.valueMap;
	}
	
	/**
	 * Create SQL constructor
	 * @param queryRule
	 */
	public QueryRuleSqlBuilder(QueryRule queryRule) {
		CURR_INDEX = 0;
		properties = new ArrayList<String>();
		values = new ArrayList<Object>();
		orders = new ArrayList<Order>();
		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.");
			}
		}
		//Assemble where statement
		appendWhereSql();
		//Assembly sort statement
		appendOrderSql();
		//Assembly parameter value
		appendValues();
	}
	
	/**
	 * Remove 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();
	}
	
	/**
	 * Remove 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;
		}
	}
	
	/**
	 * Handle like
	 * @param rule
	 */
	private  void processLike(QueryRule.Rule rule) {
		if (ArrayUtils.isEmpty(rule.getValues())) {
			return;
		}
		Object obj = rule.getValues()[0];

		if (obj != null) {
			String value = obj.toString();
			if (!StringUtils.isEmpty(value)) {
				value = value.replace('*', '%');
				obj = value;
			}
		}
		add(rule.getAndOr(),rule.getPropertyName(),"like","%"+rule.getValues()[0]+"%");
	}

	/**
	 * Processing between
	 * @param rule
	 */
	private  void 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],"");
	}
	

//Some codes are omitted here
	
	
	/**
	 * Join SQL query rule queue
	 * @param andOr and or
	 * @param key Listing
	 * @param split Interval between column name and value
	 * @param value value
	 */
	private  void add(int andOr,String key,String split ,Object value){
		add(andOr,key,split,"",value,"");
	}
	
	/**
	 * Join SQL query rule queue
	 * @param andOr and or
	 * @param key Listing
	 * @param split Interval between column name and value
	 * @param prefix Value prefix
	 * @param value value
	 * @param suffix Value suffix
	 */
	private  void add(int andOr,String key,String split ,String prefix,Object value,String  	suffix){
		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 ++;
		}
	}
	
	
	/**
	 * Assemble where statement
	 */
	private void appendWhereSql(){
		StringBuffer whereSql = new StringBuffer();
		for (String p : properties) {
			whereSql.append(p);
		}
		this.whereSql = removeSelect(removeOrders(whereSql.toString()));
	}
	
	/**
	 * Assembly sort statement
	 */
	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()));
	}
	
	/**
	 * Assembly parameter value
	 */
	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", "") + " ";
	}

}

Next, create the Order class.

/**
 * SQL Sorting component
 * @author Tom
 */
public class Order {
	private boolean ascending; //Ascending or descending
	private String propertyName; //Which field is in ascending order and which field is in descending order
	
	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);
	}

}

Finally, write the client test code.

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()));


}

In this way, the client code is very clear, and the running results are shown in the figure below.

This article is the original of "Tom bomb architecture". Please indicate the source for reprint. Technology lies in sharing, I share my happiness!
If this article is helpful to you, you are welcome to pay attention and praise; If you have any suggestions, you can also leave comments or private letters. Your support is the driving force for me to adhere to my creation. Focus on WeChat official account Tom structure, get more dry cargo!

Tags: Java Back-end architecture

Posted on Thu, 28 Oct 2021 06:37:48 -0400 by Kifebear