How to do a single method for multipurpose results?

Question

I want to make three type of queries:

SELECT * FROM table
SELECT * FROM table WHERE code IN (...)
SELECT * FROM table WHERE code IN (...) AND username = 'xxx'
SELECT * FROM table WHERE username = 'xxx'

I know I can build the query using 'if' sentences depending on the parameters, but I don't sure that it is well or not, for example I can use a preparedStatement like:

SELECT * FROM table WHERE code IN(?) AND username = ?

But there's another problem, because, how to avoid (code IN(?)) or (AND username) depending on the parameters? The only way I know is using string concatenation like:

if (codes is not null) then
    query = query + " WHERE code IN( codes )"

if (username is not null) then
    query = query + " AND username = ? "

There is possible to build a unique query in a preparedStatement using mysql?

LIKE:

SELECT * FROM table WHERE if (codes is not null) code IN ( ? ) AND if (username is not null) username = 'xxx'

What is your exact problem? Can't you build the query dynamically in Java? — Mick Mnemonic, Mar 25 '16 at 20:42
Yes, I want to make dinamically depending on the parameters, in order to no create a method per each — Sequoya, Mar 25 '16 at 20:44
I don't think you have a problem here, you clearly demonstrate you know how to solve this by building your string in pieces using `if then` in your java code. No, there is not SQL logic for adding/excluding WHERE statements based on existing parameters. You can use IF or CASE logic to perform something like this http://stackoverflow.com/questions/87821/sql-if-clause-within-where-clause but that's probably about as close as you'll get. — Squeegy, Mar 25 '16 at 21:05
This is known as a "swiss army knife" method and is a programming anti-pattern. If you have three different queries then write three different methods. — Steve C, Mar 26 '16 at 08:05

score 0 · Answer 1 · answered Mar 25 '16 at 20:44

It looks the where portion is the 'non-common' factor here. You could pass a string for the Where clause and pass null if there was none. Something like this...

if you want `WHERE`...
    String WHERE = "code IN (...)"
    doWork(String WHERE);

else
    doWork(null);

And doWork would be something like...

private static void doWork(String WHERE) {
    String st = "SELECT * FROM table ";
    if(WHERE != null) {
        st += WHERE;
    .....
}

Roshana Pitigala · Answer 2 · 2018-09-12T14:40:42.493

Build your query using java according to the conditions you need.

public String getQuery(String code, String username) {
    StringBuilder sb = new StringBuilder("SELECT * FROM `table`");

    boolean isCodeAdded = false;
    if (code != null) {
        sb.append(" WHERE `code` IN (?)");
        isCodeAdded = true;
    }

    if (username != null) {
        sb.append(isCodeAdded ? " AND" : " WHERE");
        sb.append(" `username` = ?");
    }
    return sb.toString();
}

How to do a single method for multipurpose results?

2 Answers2