How to setup multiple queries with slight differences

Question

I'm writing a page that will show the user statistical data about some of our products. Basically the user can get results between a specific time frame, and also add on other data to lookup by.

For instance, I want to grab all the user clicks between 5/5/16 and 5/10/16 for our product. Then I have the option of filtering by IP, user ID, web client, and product tier.

My issue is that I don't want to create a method for each query, because that would result in a method for each query without a time stamp, then one with a time stamp.

For instance, I need a query for each filter. So a query searching by time stamp where IP = ?, and a query where the time stamp isn't given, just the IP.

This has to be done in Java, so each time the parameter changes data types, I need to add logic for a different statement. IP and User_ID are ints, while product tier and web clients are varchar.

Regardless of the filter, I send back the results of 4 queries based on internal info. So I would need 4 queries for filters with just a timestamp, then I need 4 queries for each filter, since I grab 4 results per request. In total, I need 16 queries for the filters, and 4 for the timestamp. Below is an example of what I want to avoid:

 public int getTotalClicks(Timestamp date_from_ts, Timestamp date_to_ts, String lookup_type, String lookup_data) {
 Connection conn = DMConnector.getDMConnection();
 int total_tests = 0;
 try {
 PreparedStatement statement = null;
 if (!lookup_type.equalsIgnoreCase("user_id") && (date_from_ts == null || date_to_ts == null)) {
 statement = conn.prepareStatement("SELECT count(insert_id) AS total FROM " +
 "Click_Tracking WHERE " + lookup_type + " = ? AND ended_with_error = 'false'");
 statement.setInt(1, Integer.parseInt(lookup_data));
 } else if (lookup_type.equalsIgnoreCase("user_id")) {
 //statement.setString(1, lookup_data);
 //TODO Add a join statement to get the user_id lookup with date constraint
 } else {
 statement = conn.prepareStatement("SELECT count(insert_id) AS total FROM " +
 "Click_Tracking WHERE "+ lookup_type + " = ? AND timestamp BETWEEN ? AND ? " +
 "AND ended_with_error = 'false' ");
 statement.setInt(1, Integer.parseInt(lookup_data));
 statement.setTimestamp(2, date_from_ts);
 statement.setTimestamp(3, date_to_ts);
 }
 ResultSet res = statement.executeQuery();
 while (res.next()) {
 total_tests = res.getInt("total");
 }
 statement.close();
 conn.close();
 return total_tests;
 } catch (SQLException ex) {
 ex.printStackTrace();
 EmailUtility emailUtility = new EmailUtility();
 emailUtility.sendErrorEmail(ex.getMessage());
 }
 return total_tests;
}

Basically the lookup_type would be one of the four filters, while the lookup_data would be the value. For example, looking up by IP, the lookup_type would be IP while the lookup_data would be 192.168.0.0

This block of code would be replicated for each of the four results I need to return per request. So the amount of methods adds up. Should I just write each method out individually, or is there a "better practice" idea I should consider?

Thanks for the help!

Answer 1

One solution can be done this way if your code is going to be pretty static.

you check for each filter ( if it exists ) and then append to the statement

Pseudo Code follows

orig_sql = ' select ... from table where 1 = 1 ' // note space at end
if ( filter1 )
{
 filter1_sql = ' and filter1 = '%s' ; Put value of filter_1 in %s
 orig_sql = orig_sql + filter1_sql ;
}
if ( filter2 )
{
 filter2_sql = ' and filter_2 = %d' ; // Put value of filter_2 in %d
 orig_sql = orig_sql + filter2_sql 
}

Hope this makes sense. You can put checks to see if there is at least one filter or the original sql wil return everything.

• Colby: It does, thanks! I would upvote, but apparently my stack overflow reputation doesn't transfer XD.
• Learner_101: that is alright as long as it helped you solve your problem