How do I use a prepared statement if I don't know how many parameters I'll be passing in?

Question

So I have a simple function to return something from the database. I can then modify this query by adding different parameters in the WHERE clause. What would be the most elegant and efficient way to handle this?

Example:

    public static getUsers(int id, string username, string email) {

        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "";

        sql = "SELECT * FROM users " .........

And that's where I'm confused about the where clause. If I do something like

"WHERE id = ? AND username = ? AND email = ?";

What happens if I call the method with only an Id, and no username or email? It'll break and I can't have that happening.

Also, it becomes hard to manage my indexes, becuase if I would do something like stmt.setInt(1, id), but what if I only wanted to call the method with the username, and that id would come in as null, wouldn't throw a NPE?

I'm sort of new to Java, sorry... but I'm thinking I should use overrides? should I build my where clause in a conditional statement? Any help would be appreciated.

Thanks

You could potentially check to see if the various parameters exist and only append them to the `WHERE` in those cases. Alternatively, the function could take an associative array that it uses to map the various `WHERE` clause criteria. You'd probably want to validate the keys, though, to ensure you're getting columns that exist. — jchapa, Mar 18 '13 at 18:01
it's not pretty, but you can modify the sql string with if/else statements for each parameter, then prepare the statement afterwards — Jeff Hawthorne, Mar 18 '13 at 18:01
@JeffHawthorne this is where I was going, although I thought there would be a more elegant solution. This handles the WHERE clause for the sql, but what about the indexes of the preparedstatement? How do I tell it to ignore `stmt.setInt(1, id)` and that it's actually `stmt.setString(1, username)`, because I'm searching by name this time rather than Id? I might be missing something :P — , Mar 18 '13 at 18:05
@MarcoLeblanc - you already have a good idea about how to fix this design. Break out each specific data access into its own dedicated method. Trying to do it all in one is messy, error-prone, and hard to maintain. — Perception, Mar 18 '13 at 18:07
@MarcoLeblanc - while I agree with Perception that you need to break these out, the answer to your question is that you need to change the first parameter to be of type Integer instead of int. Then you can pass NULL. — rbedger, Mar 18 '13 at 18:13

score 2 · Answer 1 · edited May 23 '17 at 12:21

I would create a SqlQuery class which implements the Builder Pattern. This is an excellent post explaining usage of the pattern.

Example:

public class SqlQuery {
    private StringBuilder tableQuery = new StringBuilder();
    private StringBuilder whereQuery = new StringBuilder();

    public SqlQuery(String selection, String table) {
        tableQuery.append("SELECT ").append(selection).append(" FROM ").append(table);
    }

    public SqlQuery addWhereClause(String parameter, String value) {
        if (whereQuery.length() == 0) {
            whereQuery.append(" WHERE ");
        }
        else {
            whereQuery.append(" AND ");
        }
        whereQuery.append(parameter).append(" = ").append(value);
        return this;
    }

    public String toString() {
        return tableQuery.toString() + whereQuery.toString();
    }
}

SqlQuery sqlQ = new SqlQuery("*", "users")
                  .addWhereClause("id", "2")
                  .addWhereClause("email", "test");
System.out.println(sqlQ);

This prints:

SELECT * FROM users WHERE id = 2 AND email = test

@ghdalum you can chain append() to preserve the performance of your StringBuilder: `whereQuery.append(parameter).append(" = ").append(value);` — rob, Mar 18 '13 at 21:30
Great that you showed an example. I added an answer based on yours, showing how to actually build a PreparedStatement (you are building an SQL String) — Adriaan Koster, Mar 19 '13 at 08:46

score 1 · Accepted Answer · answered Mar 19 '13 at 08:43

The excellent idea from @ghdalum does not actually involve a PreparedStatement. Here is my adaptation of his builder idea to produce a PreparedStatement:

public class UserQueryBuilder {

    private Connection conn;
    private StringBuilder query = new StringBuilder("SELECT * FROM users");
    private List<ValueSetter> valueSetters = new ArrayList<ValueSetter>();

    // callback interface for setting the column values
    private interface ValueSetter {
        void setValue(PreparedStatement ps);
    }

    // the caller is responsible for closing the connection afterwards
    public QueryBuilder(Connection conn) {
        this.conn = conn;
    }           

    public QueryBuilder byId(final Integer id) {
        appendSeparator();
        query.append("id = ?");
        valueSetters.add(new ValueSetter() {
            public void setValue(PreparedStatement ps) {
                ps.setInt(id);
            }
        });
        return this;
    }   

    public QueryBuilder byEmail(String email) {
        appendSeparator();
        query.append("email = ?");
        valueSetters.add(new ValueSetter() {
            public void setValue(PreparedStatement ps) {
                ps.setString(email);
            }
        });
        return this;
    }   

    public QueryBuilder byUsername(String username) {
        appendSeparator();
        query.append("username= ?");
        valueSetters.add(new ValueSetter() {
            public void setValue(PreparedStatement ps) {
                ps.setString(username);
            }
        });
        return this;
    }

    private void appendSeparator() {
        if (filterValues.size() == 0) {
            query.append(" WHERE ")
        }
        else {
            query.append(" AND ")
        }
    }

    public PreparedStatment build() {
        PreparedStatement ps = conn.prepareStatement(query.toString());
        for(ValueSetter valueSetter : valueSetters) {
            valueSetter.setValue(ps);
        }
        return ps;
    }
}

Usage:

PreparedStatement userQuery = new UserQueryBuilder(conn)
                              .byId("2")
                              .byEmail("test")
                              .build();
userQuery.execute();

(BTW I didn't test this code so there could be typos)

this is very similar to what I ended up implementing. Thanks and 1 up to both of you. — , Mar 19 '13 at 11:30

score 0 · Answer 3 · answered Mar 18 '13 at 18:03

0

write if else statement i.e.

 if(username!=null)
 query=query+"username=?";

and

if(username!=null)
stmt.setInt(2, username)

answered Mar 18 '13 at 18:03

Biswajit

2,434
2
28
35

score 0 · Answer 4 · answered Mar 18 '13 at 18:04

I would do something like this, create a method which accepts List<ColumnNames> and in that method loop through all the column names by appending the place holder to the column name.

public List<Something> method(List<Something> list){

String sql = "SELECT * FROM users WHERE "+ this.processPlaceHolders(list);

}

public String processPlaceHolders(List<Something> list) {
StringBuilder finalStr=new StringBuilder("");
for(int i=0;i<list.size(); i++){
if(i==list.size()-1){
finalStr.append(list.get(i)+"=" +"?"); 
}
else {
   finalStr.append(list.get(i)+"=" +"?,");
}
}
return finalStr.toString();
}

score 0 · Answer 5 · answered Mar 18 '13 at 18:06

You will need to dynamically build your SQL query to achieve this.

public static getUsers(int id, string username, string email) {

    Connection conn = null;
    PreparedStatement stmt = null;
    String sql = "";

    sql = "SELECT * FROM users where id=? ";
    if (username != null)
      sql += " AND username=? ";
    if (email !=null)
      sql += " AND email=?";

    stmt = conn.prepareStatement(sql);
    stmt.setInt(1,id);
    if (username != null && email !=null)
    {
      stmt.setString(2,username);
      stmt.setString(3,email);
    }
    else if (username != null)
      stmt.setString(2,username);
    else if (email != null)
      stmt.setString(2,email);

The problem with this implementation is that it assumes `id` is always provided, but that may not be the case. The question states that you may want to look up a user without `id` (you could assert that -1 is passed as the `id` in that case). — rob, Mar 18 '13 at 19:48

Vishal K · Answer 6 · 2013-03-18T19:08:12.893

The solution to your problem is something like this:

public void getRecord(String id, String username, String email)
{
  String sql = " select * from users ";
  boolean isID , isUserName, isEmail;
  boolean isFirst = true;
  if (id!=null)
  {
     if ( isFirst)
     {
       sql = sql + " where";
       isFirst = false;
     }
     sql =  sql + " id = ?";
     isID = true;
  }
  if (username != null)
  {
     if ( isFirst)
     {
       sql = sql + " where";
       sql =  sql + " username = ?";
       isFirst = false;
     }
     else
     sql =  sql + " and username = ?";
     isUserName = true;
  }
  if (email != null)
  {
     if ( isFirst)
     {
       sql = sql + " where";
       sql = sql + " email = ?";
       isFirst = false;
     }
     else
     sql = sql + " and email = ?";
     isEmail = true;
  }
  PreparedStatement pst = con.prepareStatement(sql);
  int counter = 1;
  if (isID)
  pst.setString(counter++,id);
  if (isUserName)
  pst.setString(counter++,username);
  if (isEmail)
  pst.setString(counter++,email);
  //Then execute the query
  ResultSet rs = pst.executeQuery();
  .......
}

score 0 · Answer 7 · answered Mar 18 '13 at 18:20

Try this:

public static getUsers(int id, String username, String email) {
        Connection conn = null;
        PreparedStatement stmt = null;
        String sql = "SELECT * FROM users WHERE id=? ";

        if (username != null && username.trim().length() != 0) sql = sql + " AND username=? "; 
        if (email != null && email.trim().length() != 0) sql = sql + " AND username=? ";

        stmt = conn.prepareStatement(sql);
        stmt.setInt(1, id);

        if (username != null && username.trim().length() != 0) stmt.setString(2, username);
        if (email != null && email.trim().length() != 0) stmt.setString(3, email);
//.....
    }

score 0 · Answer 8 · answered Mar 18 '13 at 19:08

Instead of writing one very complex method to dynamically construct a PreparedStatement, you should consider writing a separate method for each valid combination of inputs. Then each one can easily validate its input and will always use a specific PreparedStatement. Not only will this be easier to understand and maintain in the future, but it will be easier to test.

If you need backward compatibility with an existing API, you can then write a getUsers(int id, string username, string email) method that delegates to the simpler methods as appropriate.

How do I use a prepared statement if I don't know how many parameters I'll be passing in?

8 Answers8