How do I use a prepared statement if I don't know how many parameters I'll be passing in?
java, mysql
Solution
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)
Problem
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