How to build your own SQLBuilder from scartch

Hibernate is used widely for data access. It is great to release developer from tons of sql scripts. However, the performance is about 50% less than pure jdbc access. For core function, my current project is still using the pure jdbc to access. The pain part is the current code is all write down by stringbuilder which is easy to make a mistake in syntax and missing space between key sql words.

My purpose is trying to create simple java class which help build sql prepared statement.

The class should be working in the following feature as the first version:
1. Support select, Update, delete sql generation.

2.  Code usage should go with the flow feature
3. Support where, join, and, or key words.
4. support parameters for prepared statement.


Let's start from simple simple select/delete statement, the unit test will be as following at first:

	@Test
	public void deleteNoWhereOk() {
	final var validateSql = "DELETE FROM table1";
	var sqlStatement = SQLBuilder.newSql().delete().from("table1").build();
	assertEquals(validateSql, sqlStatement.getSqlString());
	}
	@Test
	public void selectNoWhereOk() {
	final var validateSql = "SELECT * FROM table1";
	var sqlStatement = SQLBuilder.newSql().select().from("table1").build();
	assertEquals(validateSql, sqlStatement.getSqlString());
	}

view raw SQLBuilderTest.java hosted with ❤ by GitHub

The implementation will be simple as well:

	public SQLExpr(String expr) {
	this.expr(expr);
	}
	public SQLExpr() {
	}
	public SQLExpr from(String table) {
	return expr("FROM", table);
	}
	public SQLExpr delete() {
	return expr("DELETE");
	}
	public SQLStatement build() {
	var compositedSql = this.sb.toString().trim();
	if (this.columnValues.size() > 0) {
	compositedSql = compositedSql.replaceAll(COLUMNS_VALUES_PLACEHOLDER, String.join(", ", columnValues.stream().map((colVal) -> colVal.getColumn()
	+ " = ?").collect(Collectors.toList())));
	}
	return new SQLStatement(compositedSql, columnValues);
	}
	public SQLExpr select() {
	return this.select("*");
	}
	public SQLExpr select(String columns) {
	return this.expr("SELECT", columns);
	}
	public SQLExpr expr(String... exprs) {
	this.sb.append(String.join(" ", exprs));
	this.sb.append(" ");
	return this;
	}

view raw SQLExpr.java hosted with ❤ by GitHub

After that, we add support of "WHERE" "UPDATE" as well as all other SQL key words, always have the unittest firstly and then implementation:

	@Test
	public void deleteNoWhereOk() {
	final var validateSql = "DELETE FROM table1";
	var sqlStatement = SQLBuilder.newSql().delete().from("table1").build();
	assertEquals(validateSql, sqlStatement.getSqlString());
	}
	@Test
	public void selectNoWhereOk() {
	final var validateSql = "SELECT * FROM table1";
	var sqlStatement = SQLBuilder.newSql().select().from("table1").build();
	assertEquals(validateSql, sqlStatement.getSqlString());
	}
	@Test
	public void updateNoWhereOk() {
	final var validateSql = "UPDATE table1 SET column1 = ?, column2 = ?";
	var sqlStatement = SQLBuilder.newSql().update().to("table1").set().column("column1", "value1").column("column2", 3).build();
	assertEquals(validateSql, sqlStatement.getSqlString());
	}
	@Test
	public void updateNoWhereLiternalStringOk() {
	final var validateSql = "UPDATE table1 SET column1 = 'value1', column2 = 3";
	var sqlStatement = SQLBuilder.newSql().update().to("table1").set().column("column1", "value1").column("column2", 3).build();
	assertEquals(validateSql, sqlStatement.getLiteralSqlString());
	}
	@Test
	public void deleteWhereOk() {
	final var validateSql = "DELETE FROM table1 WHERE table1.column1 = 'value'";
	var sqlStatement = SQLBuilder.newSql().delete().from("table1").where("table1.column1 = 'value'").build();
	assertEquals(validateSql, sqlStatement.getSqlString());
	}
	@Test
	public void deleteWithWhereOk() {
	final var validateSql = "DELETE FROM table1 WHERE a = b";
	var sqlStatement = SQLBuilder.newSql().delete().from("table1").where("a = b").build();
	assertEquals(validateSql, sqlStatement.getSqlString());
	}
	@Test
	public void deleteWithWhereConditionsOk() {
	final var validateSql = "DELETE FROM table1 WHERE a = b";
	var sqlStatement = SQLBuilder.newSql().delete().from("table1").where().condition("a = b").build();
	assertEquals(validateSql, sqlStatement.getSqlString());
	}
	@Test
	public void deleteWithWhereComplexConditionsOk() {
	final var validateSql = "DELETE FROM table1 WHERE a = b AND c = d OR f = g AND ( l = m OR h = i )";
	var sqlStatement = SQLBuilder.newSql().delete().from("table1")
	.where().condition("a = b")
	.and()
	.condition("c = d")
	.or()
	.condition("f = g")
	.and()
	.aggCondition(new SQLExpr("l = m").or().condition("h = i"))
	.build();
	assertEquals(validateSql, sqlStatement.getSqlString());
	}
	@Test
	public void selectJoinTablesOk() {
	final var validateSql = "SELECT t1.c1, t2.c2 FROM table1 t1 " +
	"JOIN table2 t2 on t1.id = t2.id " +
	"WHERE a = b AND c = d OR f = g AND ( l = m OR h = i )";
	var sqlStatement = SQLBuilder.newSql().select("t1.c1, t2.c2").from("table1 t1")
	.join(JointTableType.JOIN, "table2 t2").on("t1.id = t2.id")
	.where().condition("a = b")
	.and()
	.condition("c = d")
	.or()
	.condition("f = g")
	.and()
	.aggCondition(new SQLExpr("l = m").or().condition("h = i"))
	.build();
	assertEquals(validateSql, sqlStatement.getSqlString());
	}

view raw SQLBuilderTest.java hosted with ❤ by GitHub

Based on code above, the sql builder can create sql with incorrect sql syntax. for example, where().where().select()... The next step is to add possible syntax support.
Firstly, we draw the sql syntax state change diagram:

Support syntax of select, update, and delete

	public class InitSQLExpr extends SQLExpr{
	public FromSQLExpr delete() {
	return expr("DELETE").fillAndGet(FromSQLExpr.class);
	}
	public FromSQLExpr select() {
	return this.select("*");
	}
	public FromSQLExpr select(String columns) {
	return this.expr("SELECT", columns).fillAndGet(FromSQLExpr.class);
	}
	public ToSQLExpr update() {
	return this.expr("UPDATE").fillAndGet(ToSQLExpr.class);
	}
	}

view raw InitSQLExpr.java hosted with ❤ by GitHub

Support syntax of from:

	public class FromSQLExpr extends SQLExpr {
	public WhereOrJoinSQLExpr from(String table) {
	return expr("FROM", table).fillAndGet(WhereOrJoinSQLExpr.class);
	}
	}

view raw FromSQLExpr.java hosted with ❤ by GitHub

See https://github.com/LeiZheng/sql-utils for more detail