CODEWITHSUNDEEP
javasqljdbcprepared-statement
Dennis
Dennis

Reputation: 4017

Simple query with variable parameters

I have three queries which I would like to consolidate into one query which can accept a variable length of WHERE arguments; I cannot remember for the life of me how to do this. 

PreparedStatement queryOne = connection.prepareStatement
("SELECT columnOne, columnTwo, columnThree FROM tableOne WHERE columnOne = ?;" );

PreparedStatement queryTwo = connection.prepareStatement
("SELECT columnOne, columnTwo, columnThree FROM tableOne WHERE columnTwo = ?;" );

PreparedStatement queryThree = connection.prepareStatement
("SELECT columnOne, columnTwo, columnThree FROM tableOne WHERE columnOne = ? AND columnTwo = ?;" );

Upvotes: 1

Views: 317

Answers (2)

Dennis
Dennis

Reputation: 4017

Answered my own question, feel free to chime in on whether this is bad practice or not. 

String columnOne = getValue();
String columnTwo = getValue();
String queryString = "SELECT columnOne, columnTwo, columnThree FROM tableOne"

if (columnOne != null && columnTwo != null)
    queryString = queryString + "WHERE columnOne = ? AND columnTwo = ?"
else if (columnOne != null)
    queryString = queryString + "WHERE columnOne = ?"
else if (columnTwo != null)
    queryString = queryString + "WHERE columnTwo = ?"

PreparedStatement query = connection.prepareStatement(queryString);

Upvotes: 0

Wayne
Wayne

Reputation: 60424

All three queries select the same columns from the same table, so their union can be easily done in one statement:

SELECT columnOne, columnTwo, columnThree 
  FROM tableOne 
 WHERE columnOne = ?
    or columnTwo = ?
    or (columnOne = ? AND columnTwo = ?)

Upvotes: 1

Related Questions