While mentioning it should not be done vanishes any chance to not do it I mention do not do it since my opinion it is that all a wrapper of a standard can do is to decrease efficiency without adding value, it is just a third party imposed between a standard and the users of the standard, of course if the users agree to use the third party library in spite of the standard.
If for some random reason(s) there are insistence on being implemented there are two concerns to address (1) the build of sql statements and (2) the use of the built statements therefore there should be an sql statement builder and data access objects (DAOs) using the builder. The sql statement builder sole concern should be to build sql statements with proper sql syntax. The DAOs concern should be to sanitise any value set in the statement, create the query (prepared statement or plain statement), run it and parse the result set. The exceptions thrown while any step of a DAO method should be let bubble to the layer calling the DAO methods since the method could be part of a transaction (that is a sac connection with autocommit set to false) that have to be aware of the success/failure of each query being part of it to be able to rollback in case of failure.
Since the DAO methods are classical implementations following is a depict of what an SQL statement builder implementation. The test class...
public class SqlStatementTest {
public static void main(String[] args) {
String sql = PlainSqlBuilder.select("column")
.from("tableName")
.where("column = value")
.groupBy("grouped_column")
.having("grouped_column > 1")
.and("grouped_column < 10")
.orderBy("order_column")
.sql();
System.out.println(sql);
System.out.println(PlainSqlBuilder.select("column").sql());
System.out.println(PlainSqlBuilder.select("column")
.from("table")
.sql());
System.out.println(PlainSqlBuilder.select("t.column")
.from("table t")
.orderBy("t.column DESC")
.sql());
}
}
...outputs...
SELECT column FROM tableName WHERE column = value GROUP BY grouped_column HAVING grouped_column > 1 AND ggrouped_column < 10 ORDER BY order_column
SELECT column
SELECT column FROM table
SELECT t.column FROM table t ORDER BY t.column DESC
...with the sql builder possible implementation consisting of an interface declaring a sole method used to describe parts of the sql statements...
interface Builder {
String sql();
}
...and its implementations encapsulated into a builder with fluent interface design pattern implementation...
import java.util.List;
import java.util.stream.Stream;
public class PlainSqlBuilder {
public static SelectBuilder select(String... columns) {
return SelectBuilder.columns(columns);
}
private PlainSqlBuilder() {}
public static class SelectBuilder implements Builder {
public static SelectBuilder columns(String... names) {
return new SelectBuilder(names);
}
private enum SqlClause {
WHERE("WHERE"), HAVING("HAVING");
public String keyword;
private SqlClause(String keyword) {
this.keyword = keyword;
}
}
public static class LogicOperator implements Builder {
private Builder prefix;
private StringBuilder conditions;
private LogicOperator(Builder prefix
, SqlClause clause
, String condition) {
this.conditions = new StringBuilder(" ").append(clause.keyword)
.append(" ")
.append(condition);
this.prefix = prefix;
}
public LogicOperator or(String condition) {
this.conditions.append(" OR ").append(condition);
return this;
}
public LogicOperator and(String condition) {
this.conditions.append(" AND ").append(condition);
return this;
}
public LogicOperator or(String... conditions) {
Stream.of(conditions).forEach(condition -> this.conditions
.append(" OR ")
.append(condition));
return this;
}
public LogicOperator and(String... conditions) {
Stream.of(conditions).forEach(condition -> this.conditions
.append(" AND ")
.append(condition));
return this;
}
public GroupByBuilder groupBy(String column) {
return new GroupByBuilder(this, column);
}
public OrderByBuilder orderBy(String... columns) {
return new OrderByBuilder(this, columns);
}
public String sql() {
return this.prefix.sql() + this.conditions.toString();
}
}
public static class OrderByBuilder implements Builder {
private Builder prefix;
private List<String> columns;
private OrderByBuilder(Builder prefix, String... columns) {
this.prefix = prefix;
this.columns = List.of(columns);
}
public String sql() {
return this.prefix.sql() + " ORDER BY "
+ String.join(", ", this.columns);
}
}
public static class GroupByBuilder implements Builder {
private Builder prefix;
private String column;
private GroupByBuilder(Builder prefix, String column) {
this.prefix = prefix;
this.column = column;
}
public LogicOperator having(String condition) {
return new LogicOperator(this, SqlClause.HAVING, condition);
}
public OrderByBuilder orderBy(String column) {
return new OrderByBuilder(this, column);
}
public String sql() {
return this.prefix.sql() + " GROUP BY " + this.column;
}
}
public static class SqlClauseBuilder implements Builder {
private Builder prefix;
private SqlClauseBuilder(Builder prefix) {
this.prefix = prefix;
}
public LogicOperator where(String condition) {
return new LogicOperator(this.prefix, SqlClause.WHERE, condition);
}
public GroupByBuilder groupBy(String column) {
return new GroupByBuilder(this.prefix, column);
}
public OrderByBuilder orderBy(String column) {
return new OrderByBuilder(this.prefix, column);
}
@Override
public String sql() {
return this.prefix.sql();
}
}
private String[] names;
private String tableName;
private SelectBuilder(String[] names) {
this.names = names;
}
public SqlClauseBuilder from(String tableName) {
this.tableName = tableName;
return new SqlClauseBuilder(this);
}
private String names() {
return names == null
|| names.length == 0 ? "*"
: String.join(", ", names);
}
public String sql() {
StringBuilder sql = new StringBuilder("SELECT ").append(this.names());
if (tableName != null) {
sql.append(" FROM ").append(this.tableName);
}
return sql.toString();
}
}
}