CODEWITHSUNDEEP
javamysqlsqlsql-serveroracle-database
Apex Predator
Apex Predator

Reputation: 61

Dynamic SQL Query in Java

I have a function

public void executeMyQuery(Connection con) {
    PreparedStatement ps = con.prepareStatement("SELECT * FROM STUDENT WHERE ID = ?");
    ps.setInt(1, 7);
    ps.executeQuery();
}

if i will run this it will work fine. But I want to do like this.

  1. if I will setInt it should include WHERE clause. (returns matched row)
  2. if I don't setInt it should exclude WHERE clause. (returns whole table)

Or is there is any way to dynamically remove or modify the string after WHERE Clause.

Upvotes: 2

Views: 24273

Answers (5)

Tri Dave
Tri Dave

Reputation: 11

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.

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220762

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:

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.

Upvotes: 4

Grzegorz Oledzki
Grzegorz Oledzki

Reputation: 24251

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.

Upvotes: -1

Michael Dz
Michael Dz

Reputation: 3834

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();}

Upvotes: 2

Heratom
Heratom

Reputation: 35

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 !

Upvotes: -3

Related Questions