Dynamic SQL Query in Java

Disadvantages of using string based SQL for dynamic SQL

Other answers have shown how to achieve dynamic SQL using string based JDBC usage. There are many disadvantages to building SQL strings dynamically using string concatenation, including:

• High risk of SQL injection if you accidentally concatenate user input to your SQL queries
• Difficult to avoid syntax errors in non-trivial cases, when dynamic SQL gets more complex

Also, when you're using plain JDBC (which only supports indexed bind variables), rather than some utility such as Spring JdbcTemplate, MyBatis, jOOQ, etc. you will have to manually match ? placeholders with their corresponding indexes, which is another subtle source of errors.

Using a query builder

At some point, when you implement dynamic SQL queries more often, query builders will definitely help. The most popular ones are:

• jOOQ (for dynamic SQL querying)
• JPA Criteria Query (for dynamic JPAL querying)

There are many other options that are more or less well maintained. For very trivial cases (like the one in your question), you can also build your own simple predicate builder.

Disclaimer: I work for the company behind jOOQ.

You have to build your query dynamically, at the beginning of the method check whether id is null or equal 0. To make it easier you can use trick in where clause with 1=1 so where clause can be included all the time in the query.

public void executeMyQuery( Connection con, Integer id) {
String query = "SELECT *FROM STUDENT WHERE 1=1";
if(id != null){
 query += "AND ID = ?";
}
PreparedStatement ps = con.prepareStatement(query);
if(id != null){
 ps.setInt(1, id);
}
ps.executeQuery();}

ifNull/Coalesce work nicely for this, if you pass a null, it will select where the field equals itself.

SELECT * 
FROM STUDENT 
WHERE 1 = 1
and ID = ifNull(:ID, ID)

I'd also suggest something other than using ? for your variables, fine when you a couple but as you get a ton of them, difficult to keep track or modify. I've found https://github.com/dejlek/jlib/blob/master/src/main/java/com/areen/jlib/sql/NamedParameterStatement.java pretty easy, made a few modifications to fit my particular needs but SQL is much easier to read and doing substitutions in intellij db console are much easier when developing the SQL.

You can have two PreparedStatements defined in your program - one without the WHERE ID = ? clause, and another one with it.

Moreover, you are supposed to keep your PreparedStatements and re-use, so you better store them as a field, etc.

And then, when needing to get the data - call either the first prepared statement, or the second one.

Michael Dz is close to the solution in his answer, but there is a problem in the code : he calls setInt on a non existing preparedStatement.

Try something like this :

public void executeMyQuery( Connection con, int Id) {
 StringBuffer sql = new StringBuffer();
 sql.append("Select * FROM STUDENT");
 if(Id > -1) {
 sql.append(" Where ID = ?");
 }
 preparedStatement ps = con.prepareStatement(sql.toString());
 if(ID > -1) {
 ps.setInt(1, Id);
 }
 ps.executeQuery(); // You might want to catch the result of the query as well
}

Hope this helps !

• java
• mysql
• sql
• sql-server
• oracle-database