0

I'm trying to get lists of records which their id is in a specific set. I will get this set from another application via web service. I should write the query like this: SELECT * FROM tbl_data WHERE id IN (?, ?, ?, ?). But the problem is (?, ?, ?, ?) part is variable length. For one request it is like: SELECT * FROM tbl_data WHERE id IN (?, ?, ?) and for another it is like: SELECT * FROM tbl_data WHERE id IN (?, ?, ?, ?, ?, ?). I really don't like to loop and get records one by one. Is there any way to build this query?

Majid Azimi
  • 5,575
  • 13
  • 64
  • 113
  • SELECT * FROM tbl_data WHERE id = yourID? – Allan Spreys May 04 '13 at 08:08
  • Are you using pure JDBC? In what form do you have the IDs? – Michal Borek May 04 '13 at 08:11
  • Thank you for duplicating the most popular question in SO, http://stackoverflow.com/questions/337704/parameterizing-an-sql-in-clause. Keep creating duplicates. Programmers must avoid reuse. For sustainability, we must reduce the reuse and recycling. – Val May 04 '13 at 08:14

3 Answers3

3

You can generate IN part of query in your code. If you know what should be instead of ? symbol, you just run loop and build it.

String sqlPart = "(";
for (every symbol but last){
sqlPart += symbol;
sqlPart += ",";
}
sqlPart += lastSymbol;
sqlPart += ")";

String sql = "SELECT * FROM tbl_data WHERE id IN " + sqlPart;
Martin Perry
  • 9,232
  • 8
  • 46
  • 114
2

You could instead loop and construct the string "?, ?, ? ... , ?", just put as many question marks as ids you have to ask the DB for. Use StringBuilder to construct the String

Here some sample code:

@Test
public void genSqlInParameterString() {
    String select = "SELECT * FROM my_table WHERE ";
    System.out.println(select + genSqlInParameterString(null));
    System.out.println(select + genSqlInParameterString(new String[]{}));
    System.out.println(select + genSqlInParameterString(new String[]{"A"}));
    System.out.println(select + genSqlInParameterString(new String[]{"A", "B"}));
}


public String genSqlInParameterString(String[] args) {
    StringBuilder sb = new StringBuilder();
    if(args != null && args.length > 0) {
        sb.append("IN (");
        for(int i = 0; i < args.length ; i++) {
            sb.append('\'').append(args[i]).append('\'');
            if(i < args.length-1) {
                sb.append(", ");
            }
        }
        sb.append(")");
    }
    if(sb.length() == 0) {
        // condition evaluates to false, so that select returns nothing 
        // you may instead return 1=1 so that all records are returned
        // handling no-paramters case depends on your requirements
        sb.append("1=0"); 
    }
    return sb.toString();
}

Output

SELECT * FROM my_table WHERE 1=0
SELECT * FROM my_table WHERE 1=0
SELECT * FROM my_table WHERE IN ('A')
SELECT * FROM my_table WHERE IN ('A', 'B')
A4L
  • 17,353
  • 6
  • 49
  • 70
2

Use QueryDSL or JOOQ instead of writing SQL code manually.

Piotr Gwiazda
  • 12,080
  • 13
  • 60
  • 91