Easiest way to use objects as filters for queries

Question

I'm just starting on a new little pet project of mine. I started the project using a familiar pattern for using objects as "filters" in SQL Queries. I've never been much happy about its easy of use, but eventually more or less accepted it after having it drilled into my head by colleagues.

The example below hopefully highlights my concern. Especially in regards of sheer size. However that being said, I also see the strengths of this pattern which may put it in favor of continued use.

I'm looking for some opinions if I should continue as I always have or if there indeed is an easier way to do this. Either by using a different pattern or somehow "automate" the process.

Please note that the below is only an example and isn't in use.

public static User getUser(User filter) throws UserControllerException {
    String sql, sqlFilter = "";
    User user = null;

    try {
        Connection con = DataSource.getInstance().getConnection();

        if (filter.getCorp() != null) sqlFilter += " AND corp_id = ?";
        if (!filter.isNew()) sqlFilter += " AND user_id = ?";
        if (filter.getUsername() != null) sqlFilter += " AND username = ?";
        if (filter.getPasswordHash() != null) sqlFilter += " AND password_hash = ?";
        if (filter.getFirstName() != null) sqlFilter += " AND first_name = ?";
        if (filter.getLastName() != null) sqlFilter += " AND last_name = ?";
        if (filter.getPhoneNumber() > 0) sqlFilter += " AND phone_number = ?";

        sql = "SELECT * FROM users WHERE 1 = 1 " + sqlFilter;

        PreparedStatement ps = con.prepareStatement(sql);
        int psPos = 0;

        if (filter.getCorp() != null) ps.setLong(++psPos, filter.getCorp().getCorpId());;
        if (!filter.isNew()) ps.setLong(++psPos, filter.getUserId());
        if (filter.getUsername() != null) ps.setString(++psPos, filter.getUsername());
        if (filter.getPasswordHash() != null) ps.setString(++psPos, filter.getPasswordHash());
        if (filter.getFirstName() != null) ps.setString(++psPos, filter.getFirstName());
        if (filter.getLastName() != null) ps.setString(++psPos, filter.getLastName());
        if (filter.getPhoneNumber() > 0) ps.setLong(++psPos, filter.getPhoneNumber());

        ResultSet rs = ps.executeQuery();

        if (rs.next()) {
            user = new User(
                    rs.getLong("user_id"),
                    new Corporation(rs.getLong("corp_id"), null),
                    rs.getString("username"),
                    rs.getString("password_hash"),
                    rs.getString("first_name"),
                    rs.getString("last_name"),
                    rs.getLong("phone_number")
            );
        }

    } catch (IOException e) {
        throw new UserControllerException(e.getMessage(), e);
    } catch (SQLException e) {
        throw new UserControllerException(e.getMessage(), e);
    }
    return user;
}

Answer 1

I'd start by building your filter SQL String within the filter object itself (i.e. writing a GetSqlFilter() method on the Filter class), so that you have access to Filter's internal members, without having to call all those member functions.

Answer 2

You can use reflection to get the various properties and fields into a list, cycle through the list, and determine the correct output for your filter statement. Reflection is slow compared to directly retrieving a property, accessing the internal members of your class, or accessing fields.