CODEWITHSUNDEEP
javamysqlprepared-statement
MonoThreaded
MonoThreaded

Reputation: 12033

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?

Upvotes: 0

Views: 1020

Answers (2)

Udo Held
Udo Held

Reputation: 12538

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.

Upvotes: 1

JB Nizet
JB Nizet

Reputation: 691755

No, there isn't. You'll need to compose your query dynamically.

No need to create all the combinations, though. Just appending an additional "and c1 = ?" to the query if the parameter is defined is OK.

There are APIs that do this for you. See http://www.jooq.org/ for example.

Upvotes: 2

Related Questions