在数据库中存储搜索条件

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.

我正在对Flex仪表板进行增强,以便用户保存搜索。我在服务器端使用BlazeDS和Java / Spring / SpringJdbc。

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

2009-10-01至2009-10-31之间的价值日期

Currency = 'USD'

货币='美元'

Bank Name starts with 'First'

银行名称以“First”开头

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

我的第一个尝试是将标准分为3种类型:

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

日期操作符:Before,After,Between

String operators: Starts with, Ends with, contains

字符串运算符:以,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

我的对象模型是一个SearchCriteria接口和3个实现它的类:NumericCriteria,DateCriteria和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 :)

有更清洁的方法吗?我并不是对数据模型的疯狂,但我找不到关于这个主题的任何资源...所有评论都表示赞赏,无论多么残忍:)

4 个解决方案

#1

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

您可以将条件类序列化为XML并保留XML,而不是实现庞大的模式。

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

也许你可以从Hibernate Criteria API中获得灵感。它为类型安全查询提供了一个完整的模型,它本身可能使用JPA / Hibernate本身映射到数据库。

#3

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.

以SQL格式本身存储整个搜索条件是否有任何危害。不确定您的其他参数是什么考虑您提到的方法。

#4

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.

如果您正在使用JDBC,那么您可以将SQL语句存储为字符串(或者可能只存储在where子句中)。每次运行时都需要准备查询,如果查询量非常高,这可能是一个问题。但这比试图将where子句分解为成分标记要容易得多。

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

如果采用这种方法,请小心防范SQL注入攻击(在查询中放置的不仅仅是where子句 - 比如说DROP TABLE)。

#1