Storing search criteria in a database

Question

I am working on an enhancement to a Flex dashboard that will allow the users to save searches. I'm using BlazeDS and Java/Spring/SpringJdbc on the server side.

My question is this: how would you model a search that has one or more criteria, such as:

• Value Date between 2009-10-01 and 2009-10-31
• Currency = 'USD'
• Bank Name starts with 'First'

My first stab at this is to divide the criteria into 3 types:

• Numeric criteria
• Date criteria
• String criteria

Each of the criteria types has a different set of comparison operators:

• Numeric operators: =, >, >=, <, <=, <>
• Date operators: Before, After, Between
• String operators: Starts with, Ends with, contains

I have codes to identify each of these operators.

My object model is a SearchCriteria interface and 3 classes that implement it: NumericCriteria, DateCriteria, and StringCriteria

All of these classes map to the same table with the following columns:

 - SAVED_SEARCH_ID: id of the saved search
 - SEQ_NUM: criteria order. We want to load the criteria in the same order each time
 - CRITERIA_TYPE: Operator code. I can use this later to determine what kind of criteria this is.
 - FIELD: currency, valueDate, bank, etc
 - FIRST_VALUE_NUMERIC
 - SECOND_VALUE_NUMERIC
 - FIRST_VALUE_DATE
 - SECOND_VALUE_DATE
 - FIRST_VALUE_STRING
 - SECOND_VALUE_STRING

Is there a cleaner way to do this? I'm not crazy about the data model, but I can't find any resources on this topic... All comments are appreciated, no matter how cruel :)

Answer 1

Is there any harm in storing the whole search criteria in SQL format itself. Not sure what are your other parameters to consider the approach you mentioned.

Answer 2

Maybe you can get some inspiration from the Hibernate Criteria API. It provides a full model for typesafe querying, which by itself could probably mapped into a database using JPA/Hibernate itself.

Answer 3

If you're using JDBC, you could literally store SQL statements as strings (or perhaps store just where clauses). The queries would need to be prepared each time they are run, which might be a concern if you have very high query volumes. But this sounds a lot easier than trying to break down the where clause into constituent tokens.

If you take this line of approach, be very careful to guard against SQL injection attacks (putting more than just a where clause in your query -- say a DROP TABLE).

Answer 4

You could serialize your criteria classes to XML and persist the XML in lieu of implementing a bulky schema.