CODEWITHSUNDEEP
javadatabase-design
Mike Sickler
Mike Sickler

Reputation: 34451

Storing search criteria in a database

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:

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

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

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 :)

Upvotes: 4

Views: 1701

Answers (4)

jbenckert
jbenckert

Reputation: 964

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

Upvotes: 4

Jim Ferrans
Jim Ferrans

Reputation: 31012

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).

Upvotes: 0

Peter Becker
Peter Becker

Reputation: 8855

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.

Upvotes: 2

Bhushan Bhangale
Bhushan Bhangale

Reputation: 10987

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.

Upvotes: 0

Related Questions