I would like introduce you a simple helper class to simplify creating SQL statements using chain of responsibility design pattern.
If you recall creating a sql statement dynamically while checking and ignoring null fields in where clause would be bit tedious.
So let’s try to simplify the process using a helper util class. Below is the class where sql statement is generated dynamically based on the query parameters coming in.
public class Example {
@Autowired
private SqlHelper sqlHelper;
private PreparedStatement getSettlementStatement(Map<String, String> queryParam, Connection connection) throws SQLException {
QueryParam queryParams = new QueryParam(queryParam).extract();
PreparedStatement preparedStatement =sqlHelper.resetData()
.selectFieldAdd("application")
.selectFieldAdd("currency")
.selectFieldAdd("sum(amount)")
.fromFieldAdd("payments")
.whereBetweenFieldAdd("timestamp", queryParams.getFormattedStartDate(),queryParams.getFormattedEndDate())
.whereFieldAdd("status", "Successful")
.whereFieldAdd("country", queryParams.getCountryCode() )
.groupByFieldAdd("application")
.groupByFieldAdd("api")
.orderByFieldAdd("application")
.orderByFieldAdd("currency")
.getPreparedStatement(connection)
;
return preparedStatement;
}
}This code will create a sql prepared statement based on the query parameters passed in.
QueryParam queryParams = new QueryParam(queryParam).extract();This statement will create a POJO using the input parameters and then used in the sql to generate the data required.
PreparedStatement preparedStatement =sqlHelper.resetData()......The next line will generate the complete sql statement and fill the parameters and then generate the final sql statement.
This is the piece of code I am tacking under this blog post.
sqlHelper is a object generated from the sqlHelper util class and then resetData() method will clear the content if set already. You can view the helper class code at the end of this blog.
.selectFieldAdd("application")
.selectFieldAdd("currency")
.selectFieldAdd("sum(amount)")This code will insert the select fields one by one and if the fields is the first record then will add select keyword in front else add a comma and add the next field. So output of this will be:
select application_name, currency_code, sum(amount)Next will add the from part
.fromFieldAdd("payments")Same as select this will add from keyword if its the first word else a comma and table name. So output of this will be:
select application_name, currency_code, sum(amount) from paymentsNext will add time between where clause.
.whereBetweenFieldAdd("timestamp", queryParams.getStartDate(),queryParams.getEndDate())Now output will be
select application_name, currency_code, sum(amount) from payments
where timestamp between ? and ? Next will add where clauses:
.whereFieldAdd("status", "Successful")
.whereFieldAdd("country", queryParams.getCountryCode() )Output will be
select application_name, currency_code, sum(amount) from payments
where timestamp between ? and ? and status='Successful' and country = ?Next will add the Group By clause:
.groupByFieldAdd("application")
.groupByFieldAdd("api")New Output will be:
select application_name, currency_code, sum(amount) from payments
where timestamp between ? and ? and status='Successful' and country = ?
group by application, apiNext will add the Order By clause:
.orderByFieldAdd("application")
.orderByFieldAdd("currency")Output will be:
select application_name, currency_code, sum(amount) from payments
where timestamp between ? and ? and status='Successful' and country = ?
group by application, api
order by application, currencyFinally
.getPreparedStatement(connection)This will generate the prepared SQL statement from given parameters and replaces the ?.
So new out put will be:
select application_name, currency_code, sum(amount) from payments
where timestamp between '2022-12-29 00:00:00.000' and '2023-01-27 23:59:59.999'
and status='Successful' and country = 'Sri LAnka'
group by application, api
order by application, currencySo this explains how easy its to create the sql statement using the helper class.
Now let’s look at the helper class how this has been implemented.
Helper Class
@Service
public class SqlHelper {
private StringBuilder sqlString;
private List<String> parameterList;
SqlHelper(){
sqlString=new StringBuilder();
parameterList = new ArrayList<>();
}
public SqlHelper resetData(){
sqlString.setLength(0);
parameterList.clear();
return this;
}
public String getSqlString(){
return sqlString.toString();
}
public SqlHelper selectFieldAdd(String aSelect){
sqlBuilderSelectHelper(aSelect,"select");
return this;
}
public SqlHelper fromFieldAdd(String aFrom){
sqlBuilderSelectHelper(aFrom,"from");
return this;
}
public SqlHelper whereFieldAdd(String field,String queryParam){
sqlBuilderWhereHelper( sqlString, parameterList, field, queryParam);
return this;
}
public SqlHelper whereBetweenFieldAdd(String field, String queryParam1, String queryParam2){
sqlBuilderBetweenHelper( sqlString, parameterList, field, queryParam1,queryParam2);
return this;
}
public SqlHelper groupByFieldAdd(String aGroupBy){
sqlBuilderSelectHelper(aGroupBy,"group by");
return this;
}
public SqlHelper orderByFieldAdd(String aOrderBy){
sqlBuilderSelectHelper(aOrderBy,"order by");
return this;
}
private void sqlBuilderSelectHelper( String field, String sqlKey){
if(!field.isEmpty()){
if(sqlString.indexOf(sqlKey) ==-1)
sqlString.append(" "+sqlKey+" ");
else
sqlString.append(" , ");
sqlString.append(field);
}
}
public void sqlBuilderWhereHelper(StringBuilder queryBuilder, List<String> parameterList, String aWhereClause,String queryParam){
if(!queryParam.isEmpty()){
if(queryBuilder.indexOf("where") ==-1)
queryBuilder.append(" where ");
else
queryBuilder.append(" and ");
queryBuilder.append(aWhereClause+" = ? ");
parameterList.add(queryParam);
}
}
public void sqlBuilderBetweenHelper(StringBuilder queryBuilder, List<String> parameterList, String aWhereClause,String queryParam1,String queryParam2){
if(!queryParam1.isEmpty() && !queryParam2.isEmpty()){
if(queryBuilder.indexOf("where") ==-1)
queryBuilder.append(" where ");
else
queryBuilder.append(" and ");
queryBuilder.append(aWhereClause+" BETWEEN ? and ?");
parameterList.add(queryParam1);
parameterList.add(queryParam2);
}
}
public PreparedStatement getPreparedStatement(Connection connection) throws SQLException{
return sqlPreparedStatementHelper( connection, sqlString,
parameterList );
}
public PreparedStatement sqlPreparedStatementHelper(Connection connection, StringBuilder queryBuilder,
List<String> parameterList ) throws SQLException {
PreparedStatement preparedStatement = connection.prepareStatement(queryBuilder.toString(),
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
for (int i = 0; i < parameterList.size(); i++) {
preparedStatement.setString(i + 1, parameterList.get(i));
}
return preparedStatement;
}
}If you make a closer look at all the methods, most of the methods returns “this”. That is the current object. This is an implementation of chain of responsibility design pattern. It helps us to write complete sql code as a single statement in the Example class.
private StringBuilder sqlString;
private List<String> parameterList;These two variables will store SQL statement and parameters passed in as private objects.
We should be clear the private variables as below.
public SqlHelper resetData(){
sqlString.setLength(0);
parameterList.clear();
return this;
}Below method will add the passed in fields with required sql keywords. For example when you add select field it checks for the exciting select and if not adds one or else add a “,” and adds the given field. This method will be used in select, from, group by, order by parameters insertions.
private void sqlBuilderSelectHelper( String field, String sqlKey){
if(!field.isEmpty()){
if(sqlString.indexOf(sqlKey) ==-1)
sqlString.append(" "+sqlKey+" ");
else
sqlString.append(" , ");
sqlString.append(field);
}
}Where statements are handled by below codes:
public void sqlBuilderWhereHelper(StringBuilder queryBuilder, List<String> parameterList, String aWhereClause,String queryParam){
if(!queryParam.isEmpty()){
if(queryBuilder.indexOf("where") ==-1)
queryBuilder.append(" where ");
else
queryBuilder.append(" and ");
queryBuilder.append(aWhereClause+" = ? ");
parameterList.add(queryParam);
}
}
public void sqlBuilderBetweenHelper(StringBuilder queryBuilder, List<String> parameterList, String aWhereClause,String queryParam1,String queryParam2){
if(!queryParam1.isEmpty() && !queryParam2.isEmpty()){
if(queryBuilder.indexOf("where") ==-1)
queryBuilder.append(" where ");
else
queryBuilder.append(" and ");
queryBuilder.append(aWhereClause+" BETWEEN ? and ?");
parameterList.add(queryParam1);
parameterList.add(queryParam2);
}
}Finally PreparedStatement
public PreparedStatement sqlPreparedStatementHelper(Connection connection,
StringBuilder queryBuilder, List<String> parameterList ) throws SQLException {
PreparedStatement preparedStatement = connection.prepareStatement(
queryBuilder.toString(), ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
for (int i = 0; i < parameterList.size(); i++) {
preparedStatement.setString(i + 1, parameterList.get(i));
}
return preparedStatement;
}Just before coming to this method we will have a sql statement generated with ? marks for where clauses and the corresponding values will be stored in parameter list. PraparedStatement mehtod will replace the ? will the real values.
So this is the end. happy coding guys!
Before You Leave …
Please do consider following me!