0

I am working with a JDBC and am trying to figure out how to dynamically create queries - which is proving to be a rather challenging feat. What input can I put in a SQL query to have it return any input for a given column?

For example,

SELECT * FROM customers WHERE name = ?;

Essentially I want the previous statement to match

SELECT * FROM customers;

Instead of replacing the "Where name = ?", is there anything I can replace the ? with to get the database to return all rows with any value in the name column?

This is probably going to sound really stupid but I tried this,

SELECT * FROM customers WHERE name = *;

but that unfortunately did not work.

  • You mean like `name IS NOT NULL`, or something else? Please clarify. – Zircon Dec 27 '22 at 22:10
  • `name = ? OR 1=1` – Jens Dec 27 '22 at 22:16
  • @Zircon No I don't want not null values, I want any and all values returned. That is empty strings, null values, non-null values, everything. The caveat is having the "WHERE name = ?" be included in the query call. –  Dec 27 '22 at 22:23
  • @Tom If you have to have exactly "WHERE name = ?" in your query then you are bound to use some special value as marker for "give me them all". Can you use additional query parameters? – Leonid Dec 27 '22 at 22:32
  • @Leonid Yes you may use additional query parameters but I just need to know if there is a way to use the "WHERE name = ?" clause and still return everything. If not then I suppose my only choice is to remove that part of the string before making the query call. –  Dec 27 '22 at 22:41

2 Answers2

1

There is at least one solution:

SELECT * FROM customers WHERE name = coalesce(?, name);

However this method disables index search when ? is not null

EDIT: use coalesce instead of Oracle-specific nvl

EDIT2: this solution does NOT work for null values

Another option is just use second parameter as marker for "gimme more"

SELECT * FROM customers WHERE name = ? or ? = 1;
Leonid
  • 108
  • 1
  • 9
1

Ok, so I think I figured it out from another post. Essentially just call

SELECT * FROM customers WHERE name = name OR name IS NULL;

This worked for me.