0

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!

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.

  • It does, thanks! I would upvote, but apparently my stack overflow reputation doesn't transfer XD. – Colby May 24 '16 at 14:25
  • 1
    that is alright as long as it helped you solve your problem – Learner_101 May 24 '16 at 14:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.