javamysqlprepared-statement

Java SQL Prepared statement with *ANY* criteria


Meet ps, a simple PreparedStatement that does the job:

PreparedStatement ps = cnx.prepareStatement( "SELECT * FROM mytable WHERE ref=? AND time=>? AND time<?");

I need to add 3 optional criteria (C1,C2,C3). For clarity, they may be required or not based on runtime parameters.
Brute force tells me I can write 9 prepared statements to cover all possibilities. But what I would really like to write is more something like:

SELECT * FROM mytable WHERE ref=? AND time=>? AND time<? AND C1=? AND C2=? AND C3=?;

and use a trick like setInt(5, "ANY") or ignoreParameter(5) before executing the statement
Is there such a thing?


Solution

  • You have the option of using a framework, doing it yourself or doing some sql tricks. E.g. JPA has a CriteriaBuilder.

    Otherwise split up your query in a static and a dynamic part. Depending on your dynamic part you would have to do the binding. You would have

    SELECT * FROM mytable WHERE ref=? AND time=>? AND time<?
    

    as your static part and add AND C1 = ?only when needed.

    For SQL options you could add 3 more parameters and set them to 0 or 1.

    SELECT * FROM mytable WHERE ref=? AND time=>? AND time<? 
    AND (CHECKC1 = ? OR C1=?) AND (CHECKC2 = ? OR C2=?) AND (CHECKC3 = ? OR C3=?);
    

    However I wouldn't call it a good solution.