Creating Dynamic SQL queries in Java

While developing a UI project, there would be requirement for search screens. The search screens as we know requires dynamic query generation based on the input criterias provided. The frameworks like hibernate/JPA and other ORM (Object Relational Mapping) frameworks have got criteria API for this purpose. But, if our project uses plain JDBC, we don't have an API along with the JDBC framework for creating dynamic SQL.

For handling simpler use case involving just one table or a couple of tables which will always be part of the join in all scenarios, you can solve this issue directly in the query itself by using a condition on the input value as in the following examples:

Single table query in which you may or may not have some parameters in the query is as follows:

 select t1.id, t1.col1, t1.col2,

  from table1 t1

where (:col1Value is null or t1.col1 = :col1Value)

    and (:col2Value is null or t1.col2 = :col2Value);

This query will work even if col1Value or col2Value is null by ignoring the remainder of the the condition inside bracket.

Similarly query involving join between two tables with some of the parameters may or may not be present in the query is as follows:

select t1.id, t1.col1, t1.col2, t2.col3, t2.col4

  from table1 t1, table2 t2

 where (:col1Value is null or t1.col1 = :col1Value)

   and (:col3Value is null or t2.col3 = :col3Value)

   and t1.id = t2.id;

But you may need more flexible handling when some of the tables may or may not be part of the query or sorting condition needs to be based on input  etc.

For handling such scenarios, the following custom framework with just 3 classes can be used.
The framework is explained below in details:

source code available at https://github.com/prasune/DynamicSqlGenerator

1. Table - A class representing the table to be used in the select query. This class also keeps a list of columns that needs to be selected in the select query.
         

package com.prasune.sql.builder; import java.util.ArrayList; import java.util.List; public class Table {           private final String name;     private final String alias;     private List<String> columnsWithAlias = new ArrayList<String>();     private List<String> groupFunctions = new ArrayList<String>();     public Table(String name, String alias) {         this.name = name;         this.alias = alias;     }     public String getName() {         return name;     }     public String getAlias() {         return alias;     }         public void addColumnsToSelect(String column){       columnsWithAlias.add(alias + "." + column);     }         public List<String> getColumnsWithAlias(){       return columnsWithAlias;     }         public void addGroupFunctions(String groupFunction){       groupFunctions.add(groupFunction);     }         public List<String> getGroupFunctions(){       return groupFunctions;     } }

  

 2. Criteria: A class representing the criterias or the where clause conditions to be used in the select statement. Here we just have left and right values of the criteria and the operator. This class can be modified with more conditional operators and support as you require.

package com.prasune.sql.builder; public class Criteria {     public static final String EQUALS = "=";     public static final String GREATER = ">";     public static final String GREATEREQUAL = ">=";     public static final String LESS = "<";     public static final String LESSEQUAL = "<=";     public static final String LIKE = "LIKE";     public static final String NOTEQUAL = "<>";     public static final String IN = "IN";     public static final String OUTER_JOIN = "(+)";     private final String left;     private final String operator;     private final String right;     public Criteria(String left, String operator, String right) {         this.left = left;         this.operator = operator;         this.right = right;     }         public String toString(){       StringBuilder criteria = new StringBuilder();       criteria.append(left);       criteria.append(" ");       criteria.append(operator);       criteria.append(" ");       criteria.append(right);       return criteria.toString();     } }

  

3. SelectQuery: This class represents the Select query. It consists of the tables used in the select query and the criterias. It also have support for group by clause. The SelectQuery.toString() generates the required select query based on the tables and criterias added to the select query. Support for Order By clause etc can be easily added by adding the clause to the toString() method based on an additional flag as required. The framework can be extended as per your requirement by holding the additional data in SelectQuery and interpreting toString() accordingly.

package com.prasune.sql.builder; import java.util.ArrayList; import java.util.List; import java.util.ListIterator; public class SelectQuery {     private final List<Table> tables = new ArrayList<Table>();     private final List<Criteria> criterias = new ArrayList<Criteria>();     private final List<String> groupByColumns = new ArrayList<String>();     private boolean isDistinct = false;     public void setDistinct(boolean isDistinct)     {         this.isDistinct = isDistinct;     }     public void addRightOuterJoin(Table table1, String column1, Table table2,                         String column2)     {         String left = getColumnWithAlias(table1, column1);         String right = getColumnWithAlias(table2, column2)                        + Criteria.OUTER_JOIN;         Criteria joinCriteria = new Criteria(left, Criteria.EQUALS, right);         criterias.add(joinCriteria);     }     public void addJoin(Table table1, String column1, Table table2,                         String column2)     {         String left = getColumnWithAlias(table1, column1);         String right = getColumnWithAlias(table2, column2);         Criteria joinCriteria = new Criteria(left, Criteria.EQUALS, right);         criterias.add(joinCriteria);     }     public void addCriteria(Table table, String column, String operator,                             Object value)     {         String left = getColumnWithAlias(table, column);         Criteria simpleCriteria =             new Criteria(left, operator, value.toString());         criterias.add(simpleCriteria);     }     public void addCriteria(Table table, String column, String operator,                             List values)     {         String left = getColumnWithAlias(table, column);         StringBuilder right = new StringBuilder();         ListIterator<Object> valueIterator = values.listIterator();         right.append("(");         while (valueIterator.hasNext())         {             right.append(valueIterator.next().toString());             if (valueIterator.hasNext())             {                 right.append(",");             }         }         right.append(")");         Criteria simpleCriteria =             new Criteria(left, operator, right.toString());         criterias.add(simpleCriteria);     }     public void addGroupByColumn(Table table, String column)     {         String columnWithAlias = getColumnWithAlias(table, column);         groupByColumns.add(columnWithAlias);     }     public void addTable(Table table)     {         tables.add(table);     }     public String toString()     {         StringBuilder sql = new StringBuilder();         appendColumnSelect(sql);         appendTables(sql);         appendCriterias(sql);         appendGroupBy(sql);         return sql.toString();     }     private void appendColumnSelect(StringBuilder sql)     {         sql.append("SELECT ");         if (isDistinct)         {             sql.append("DISTINCT ");         }         ListIterator<Table> tableIterator = tables.listIterator();         List<String> selectValues = new ArrayList<String>();         while (tableIterator.hasNext())         {             Table table = tableIterator.next();             selectValues.addAll(table.getColumnsWithAlias());             selectValues.addAll(table.getGroupFunctions());         }         appendSelectValues(sql, selectValues);     }     private void appendSelectValues(StringBuilder sql,                                     List<String> selectValues)     {         ListIterator<String> selectValueIterator = selectValues.listIterator();         while (selectValueIterator.hasNext())         {             String selectValue = selectValueIterator.next();             sql.append(selectValue);             if (selectValueIterator.hasNext())             {                 sql.append(",");             }             sql.append(" ");         }     }        private void appendTables(StringBuilder sql)     {         sql.append("\n");         sql.append("FROM ");         ListIterator<Table> tableIterator = tables.listIterator();         while (tableIterator.hasNext())         {             Table table = tableIterator.next();             sql.append(table.getName());             sql.append(" ");             sql.append(table.getAlias());             if (tableIterator.hasNext())             {                 sql.append(",");             }             sql.append(" ");         }     }     private void appendCriterias(StringBuilder sql)     {         if (criterias.size() > 0)         {             sql.append("\n");             sql.append("WHERE ");         }         ListIterator<Criteria> criteriaIterator = criterias.listIterator();         while (criteriaIterator.hasNext())         {             Criteria criteria = criteriaIterator.next();             sql.append(criteria);             sql.append("\n");             if (criteriaIterator.hasNext())             {                 sql.append(" AND ");             }         }     }     private void appendGroupBy(StringBuilder sql)     {         if (groupByColumns.size() > 0)         {             sql.append(" GROUP BY ");         }         ListIterator<String> columnIterator =             groupByColumns.listIterator();         while (columnIterator.hasNext())         {             String column = columnIterator.next();             sql.append(column);             if (columnIterator.hasNext())             {                 sql.append(",");             }             sql.append(" ");         }     }     private String getColumnWithAlias(Table table1, String column1)     {         return table1.getAlias() + "." + column1;     } }

Sample Usage:

In your search criteria screen, you will be having a set of inputs which can be stored to a SearchCriteria object, in the example SampleSearchCriteria is used. This should be the input for your MyQueryBuilder class. Further, it is recommended not to append the search values from the screen directly into the generated SQL as it opens possibility of SQL injection.
So, it is recommended to add the string parameters that needs to be passed as such as input to the SQL to a parameters list and have them as "?" in the SQL query. Value of "?" should be set in java.sql.PreparedStatement that is used to execute the query. PreparedStatement will do the necessary validation to avoid SQL injection.
An example usage for generating query based on the above framework is as follows:

package com.prasune.sql; import static com.prasune.sql.builder.Criteria.LIKE; import java.util.ArrayList; import java.util.List; import com.prasune.search.SampleSearchCriteria; import com.prasune.sql.builder.SelectQuery; import com.prasune.sql.builder.Table; public class MyQueryBuilder {       private static String WILDCARD = "%";             public static String build(SampleSearchCriteria searchCriteria,                                  List<String> parameterValues) {         SelectQuery sql = new SelectQuery();                Table table1 = new Table("TABLE1", "t1");         table1.addColumnsToSelect("column1");         table1.addColumnsToSelect("column2");                   Table table2 = new Table("TABLE2", "t2");         table2.addGroupFunctions("count(columnx) x_count");                 sql.addTable(table1);         sql.addTable(table2);         sql.addJoin(table2, "column1", table1, "column1");                 Table table3 = new Table("TABLE3", "t3");         sql.addTable(table3);         sql.addRightOuterJoin(table3, "columny", table1, "column1");         sql.addCriteria(table3, "columnz", LIKE, "?");         parameterValues.add(searchCriteria.getValue() + WILDCARD);         sql.addGroupByColumn(table1, "column1");         sql.addGroupByColumn(table1, "column2");             return sql.toString();       }             public static void main(String[] args) {         SampleSearchCriteria searchCriteria                   = new SampleSearchCriteria();         searchCriteria.setValue("sample");         List<String> parameters = new ArrayList<String>();         System.out.println(build(searchCriteria,parameters));       } }

source code available at https://github.com/prasune/DynamicSqlGenerator

Other frameworks for generating dynamic SQL:

There are also a couple of open source frameworks which can be used for this purpose.
1. Squiggle - This is a Java library which generates dynamic SQL query string for you.
You require to download squiggle.jar for this purpose.

Examples can be found at Squiggle.


2. SqlBuilder - This is another Java library which generates plain SQL for execution. This framework supports not only select statements but also DML and DDL statements. It also supports outer joins. This is a much more complete framework.
You require to download sqlbuilder.jar for this purpose.

Examples can be found at SqlBuilder examples.